Databricks Data Engineer Associate Question 138
Select 2You are working with a large dataset stored in a Delta table in Databricks. The table is expected to have a unique primary key column named id. As a data engineer, you need to validate that the id column contains unique values across all rows in the table. Which of the following approaches can be used to ensure this validation?
- A
Use the
COUNTfunction to compare the total number of rows in the table with the count of distinct values in theidcolumn. - B
Create a new Delta table and enforce a primary key constraint on the
idcolumn. - C
Use the
dropDuplicatesfunction on theidcolumn and check if the resulting DataFrame has the same number of rows as the original table. - D
Run a query with the
GROUP BY idclause and filter for groups where the count ofidis greater than 1. - E
Enable Delta table constraints and define a UNIQUE constraint on the
idcolumn within the Delta table.
Show answer and explanation
Correct answers: A, D
Explanation
To validate the uniqueness of a primary key in a Delta table, you can use SQL-based approaches such as comparing the total row count with the count of distinct values or identifying duplicates using GROUP BY. Delta Lake does not currently support enforcing primary key or UNIQUE constraints directly at the table level, so workarounds like SQL queries or programmatic checks are required for this validation.
- A. Correct.
This is a valid approach. Comparing the total row count with the count of distinct
idvalues will help determine if all values in theidcolumn are unique. - B. Incorrect.
Delta Lake does not currently support enforcing primary key constraints directly within a Delta table.
- C. Incorrect.
Using the
dropDuplicatesfunction to check for uniqueness is not an efficient or reliable method for validating primary key uniqueness due to potential performance issues with large datasets. - D. Correct.
This approach is valid. Running a query with
GROUP BY idand filtering for groups with a count greater than 1 will help identify duplicateidvalues, ensuring primary key uniqueness. - E. Incorrect.
Delta Lake does not have built-in support for enabling UNIQUE constraints directly at the table level.