Databricks Data Engineer Associate Question 136
Select 2You are a data engineer tasked with ensuring the uniqueness of a primary key column, order_id, in a Delta table named sales_transactions. Which of the following approaches can you use to validate that the primary key is unique across all rows in the table?
- A
Use a groupBy operation on the
order_idcolumn, count the occurrences, and filter where the count is greater than 1. - B
Use the DISTINCT keyword to count unique
order_idvalues and compare it with the total row count of the table. - C
Use the DROP DUPLICATES function on the
order_idcolumn and compare the resulting row count with the original table row count. - D
Use Z-Ordering on the
order_idcolumn to automatically enforce uniqueness during data processing.
Show answer and explanation
Correct answers: A, B
Explanation
To validate the uniqueness of a primary key column, you need to either check for duplicates explicitly (groupBy and count) or compare the count of distinct values with the total row count. DROP DUPLICATES modifies the dataset and is not intended for validation, while Z-Ordering is unrelated to enforcing or verifying uniqueness.
- A. Correct.
This is a valid approach because counting the occurrences of
order_idusing groupBy and filtering for duplicates (count > 1) will help identify any non-unique keys. - B. Correct.
This is a valid approach because comparing the count of unique
order_idvalues against the total row count will confirm if allorder_idvalues are unique. - C. Incorrect.
This is incorrect because DROP DUPLICATES can eliminate duplicate rows but does not validate if the original table had duplicate primary key values. It modifies the data without verifying uniqueness.
- D. Incorrect.
This is incorrect because Z-Ordering is used for optimizing data storage and query efficiency, but it does not enforce or validate uniqueness of a column.