Databricks Data Engineer Associate Question 135
Select 2You are working with a Delta Lake table named customers, which contains the columns customer_id, name, and email. The customer_id column is intended to be the primary key. To ensure that the customer_id is unique across all rows, which of the following approaches can you use in Databricks?
- A
Use the Delta Lake
MERGE INTOoperation to enforce uniqueness oncustomer_id. - B
Perform a group-by operation on
customer_idand filter rows with a count greater than 1 to identify duplicates. - C
Leverage a Databricks SQL query with
SELECT DISTINCToncustomer_idand compare the result count with the total row count. - D
Enable Delta Lake's built-in primary key constraint feature.
- E
Use a
DROP DUPLICATESoperation oncustomer_idto automatically enforce uniqueness.
Show answer and explanation
Correct answers: B, C
Explanation
To validate that a primary key is unique, you need to identify duplicates in the customer_id column. A group-by operation with filtering or a SQL query with SELECT DISTINCT are effective methods to ensure uniqueness. Delta Lake does not provide built-in primary key constraints or automatic enforcement mechanisms, so manual validation approaches are required.
- A. Incorrect.
The
MERGE INTOoperation is used to upsert data but does not inherently enforce primary key uniqueness. It cannot validate thatcustomer_idvalues are unique across all rows. - B. Correct.
Performing a group-by operation on
customer_idand filtering rows with a count greater than 1 is a valid approach to identify duplicates and ensurecustomer_iduniqueness. - C. Correct.
Using a SQL query with
SELECT DISTINCToncustomer_idand comparing the distinct count to the total row count is a valid way to validate thatcustomer_idis unique across all rows. - D. Incorrect.
Delta Lake currently does not have a built-in primary key constraint feature as of October 2023, so this option is not applicable.
- E. Incorrect.
The
DROP DUPLICATESoperation eliminates duplicate rows but does not validate or enforce primary key uniqueness on a specific column.