Databricks Data Engineer Associate Question 139
Select 3You are working on a Databricks project where you need to validate that the customer_id column in a Delta table is unique across all rows. Which of the following approaches can effectively help you ensure the uniqueness of the customer_id column?
- A
Use a SQL query with the COUNT function and GROUP BY
customer_idto identify duplicate values. - B
Create a Delta table constraint using the PRIMARY KEY keyword on the
customer_idcolumn. - C
Leverage the DISTINCT keyword in a SQL query to check if the total number of distinct
customer_idvalues matches the total number of rows in the table. - D
Write a PySpark job to compute the count of each
customer_idusing groupBy and filter for counts greater than 1. - E
Enable Z-Ordering on the
customer_idcolumn to ensure uniqueness.
Show answer and explanation
Correct answers: A, C, D
Explanation
To validate the uniqueness of a primary key such as customer_id in a Delta table, you need to programmatically or analytically identify duplicates. Approaches like using SQL queries with COUNT and GROUP BY, leveraging DISTINCT, or writing a PySpark job allow you to detect duplicates effectively. Direct primary key constraints are not supported in Delta Lake, and features like Z-Ordering are unrelated to data validation.
- A. Correct.
Correct: Using a SQL query with COUNT and GROUP BY can help you identify duplicates by grouping the
customer_idvalues and checking for counts greater than 1. - B. Incorrect.
Incorrect: Delta Lake does not currently support PRIMARY KEY constraints directly as of October 2023.
- C. Correct.
Correct: Using the DISTINCT keyword and comparing the count of distinct values to the total number of rows is a valid way to check for uniqueness.
- D. Correct.
Correct: A PySpark job can programmatically check for duplicates by grouping on
customer_idand filtering for counts greater than 1, making it an effective approach. - E. Incorrect.
Incorrect: Z-Ordering is a performance optimization feature in Delta Lake for improving query efficiency and does not enforce or ensure uniqueness.