Databricks Data Engineer Professional Question 111
Single answerYou are working on a Databricks project where a Delta table is used to store user activity logs. The table has a large volume of data, and the business requirement is to archive or delete data older than 1 year efficiently. Which approach should you take to meet this requirement?
- A
Partition the table by the 'timestamp' column and use the
VACUUMcommand to remove data older than 1 year. - B
Partition the table by the 'user_id' column and use the
DELETEcommand to remove data older than 1 year. - C
Partition the table by the 'date' column (extracted from the timestamp) and use the
DELETEcommand to remove data older than 1 year. - D
Do not partition the table and use the
MERGEcommand with a condition to delete data older than 1 year.
Show answer and explanation
Correct answer: C
Explanation
Partitioning by the 'date' column ensures that data is stored in a way that aligns with the requirement to archive or delete data based on time. This approach minimizes data scanning during operations like DELETE, making the process efficient. Using the DELETE command with a partitioned table allows you to target specific partitions for removal, while the VACUUM command can then clean up the physical files.
- A. Incorrect.
Partitioning by 'timestamp' column can help in efficient filtering, but it is not ideal here because 'timestamp' values are highly granular, leading to too many small partitions. Additionally, the
VACUUMcommand is used for cleaning up deleted files, not for deleting data based on a condition. - B. Incorrect.
Partitioning by 'user_id' is not relevant for archiving or deleting data based on time. This would result in inefficient operations since you cannot directly filter data by time using the 'user_id' partition.
- C. Correct.
Partitioning by the 'date' column (extracted from the timestamp) allows efficient archiving and deletion of data because it ensures that data is organized by time. The
DELETEcommand can then target specific partitions for removal, minimizing the amount of data scanned. - D. Incorrect.
Not partitioning the table would result in inefficient operations since the
MERGEcommand would require scanning the entire dataset to identify data older than 1 year.