Hi everyone,

I just started using the Databricks and wanted to evaluate the reading speeds when using the Databricks warehouse.
So I've generated the dataset of 100M records, which contains name, surname, date of birth, phone number and an address. Dataset is saved to delta table.

Query response needs to reach speeds below 200ms when querying for specific person. Name, surname and date of birth columns are used for identifying the person.

I tried parititioning by year of date of birth, first letter of surname, then z ordering, bloom indexing, hashing, and combinations of it, but I never reached needed speeds.
The closest I got was with hashing name, surname and date of birth columns through pyspark.sql.funcitions.hash() function and then z ordering hash values. With that I managed to get an average around 270ms on sample of 200 records.
(Records are randomly sampled from a different delta table which has same data.)
This was measured using smallest warehose.

Does anyone know what other methods are available to improve the query time?
Where can I find concrete materials how to optimize querying and how can I recognise in which case which optimization method is better?
If you have any questions or need more information I will gladly ask.