Databricks Data Engineer Associate Question 142
Single answerYou are working with a Delta table in Databricks containing transaction data. The table has two columns: customer_id and region. You need to validate that each customer_id is associated with only one unique region. Which of the following approaches will achieve this validation?
- A
Use a GROUP BY query on
customer_idand filter for rows where the count of distinctregionvalues is greater than 1. - B
Perform a LEFT JOIN of the table with itself on
customer_idand filter where theregionvalues are different. - C
Use the DISTINCT keyword on
customer_idandregionto ensure no duplicates exist. - D
Create a new column that concatenates
customer_idandregion, and check for duplicate values in the new column.
Show answer and explanation
Correct answer: A
Explanation
To validate that each customer_id is associated with only one unique region, you need to group by customer_id and count the distinct region values. If any customer_id has more than one distinct region, it violates the uniqueness condition. Other methods, such as self-joins or using DISTINCT, do not efficiently or directly solve the problem.
- A. Correct.
This is the correct approach because grouping by
customer_idand counting distinctregionvalues allows you to detect if anycustomer_idis associated with multipleregionvalues. - B. Incorrect.
This approach is incorrect because a self-join would not directly validate that each
customer_idis associated with only one uniqueregion. Instead, it would create unnecessary overhead and complexity. - C. Incorrect.
This is incorrect because using DISTINCT will only identify unique combinations of
customer_idandregion, but it will not validate if acustomer_idhas multipleregionvalues. - D. Incorrect.
This is incorrect because concatenating
customer_idandregiondoes not directly validate the uniqueness ofregionfor eachcustomer_id. It only checks for duplicate combinations.