Databricks Generative AI Engineer Associate Question 93
Select 2You are tasked with retrieving customer transaction data from a large dataset stored in a Delta Lake. The dataset is partitioned by 'year' and 'region', and the business requirement specifies that you need to extract data for the year 2022 and the region 'North America'. Which of the following approaches ensures optimal data retrieval performance?
- A
Use a SQL query with filters on 'year = 2022' and 'region = North America' directly on the Delta Lake table.
- B
Load the entire dataset into a Spark DataFrame and then filter for 'year = 2022' and 'region = North America' in memory.
- C
Leverage Delta Lake's data skipping feature by specifying the partition filters in the query.
- D
Use Databricks Auto Loader to stream the entire dataset and then filter for 'year = 2022' and 'region = North America'.
- E
Create a materialized view in the Delta Lake containing pre-filtered data for 'year = 2022' and 'region = North America'.
Show answer and explanation
Correct answers: A, C
Explanation
The best approach for efficient data retrieval from a partitioned Delta Lake table is to use query filters that leverage partition pruning and Delta Lake's data skipping capabilities. This avoids scanning irrelevant data and ensures optimal performance, aligning with the principles of efficient data engineering in Databricks.
- A. Correct.
This is correct as applying filters at the query level directly takes advantage of partition pruning and avoids unnecessary data loading.
- B. Incorrect.
This is incorrect because loading the entire dataset into memory is inefficient and negates the benefits of partitioning.
- C. Correct.
This is correct as Delta Lake's data skipping feature optimizes queries by scanning only the relevant partitions, improving performance.
- D. Incorrect.
This is incorrect because Databricks Auto Loader is designed for streaming new data into Delta Lake, not for querying existing datasets.
- E. Incorrect.
This is incorrect because creating a materialized view is unnecessary for a one-time query and adds extra complexity.