Databricks Data Engineer Associate Question 137
Single answerYou are working on a Delta Lake table in Databricks that contains customer data. Each customer is expected to have a unique 'customer_id' column, which serves as the primary key. To validate that the 'customer_id' values are unique across all rows, which approach would you use?
- A
Use the DISTINCT function on the 'customer_id' column and compare its count against the total row count in the table.
- B
Use the DROP DUPLICATES operation on the 'customer_id' column and verify if the result matches the original table.
- C
Apply a GROUP BY on the 'customer_id' column and filter for counts greater than 1 to identify duplicate values.
- D
Enable Delta Lake's built-in primary key constraints to automatically validate uniqueness.
Show answer and explanation
Correct answer: A
Explanation
To validate the uniqueness of a primary key column, comparing the count of distinct values with the total row count provides an efficient and non-intrusive method. This avoids modifying the data while ensuring that no duplicates exist.
- A. Correct.
This is the correct approach. By comparing the count of distinct 'customer_id' values with the total row count, you can determine if duplicates exist in the column.
- B. Incorrect.
This approach may seem valid, but it modifies the original table by dropping duplicates, which is not suitable for validation. The goal is just to check uniqueness, not alter the data.
- C. Incorrect.
While this option can help you identify duplicates in the 'customer_id' column, it doesn't directly validate overall uniqueness. It focuses only on detecting duplicates rather than confirming no duplicates exist.
- D. Incorrect.
Delta Lake currently does not support built-in primary key constraints. Therefore, this option is incorrect.