Databricks Data Engineer Associate Question 141
Single answerYou are working with a Databricks Delta table named 'sales_data' that contains the 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 would correctly validate this requirement?
- A
Use a GROUP BY on 'customer_id' and count distinct 'region' values, then filter for counts greater than 1.
- B
Use a DISTINCT query on 'customer_id' and 'region', then compare the count of the result with the count of distinct 'customer_id'.
- C
Use a WINDOW function with ROW_NUMBER partitioned by 'customer_id' and ordered by 'region', and check for rows where ROW_NUMBER is greater than 1.
- D
Perform a LEFT JOIN of the table with itself on 'customer_id' and check for mismatched 'region' values.
Show answer and explanation
Correct answer: A
Explanation
To validate that each 'customer_id' is associated with only one unique 'region', you need to calculate the distinct count of 'region' values for each 'customer_id'. If any 'customer_id' has more than one distinct 'region', it violates the uniqueness requirement. Using GROUP BY and filtering on the count of distinct 'region' values is an efficient and straightforward way to achieve this.
- A. Correct.
This is the correct approach because grouping by 'customer_id' and counting distinct 'region' values will reveal if any customer_id is associated with more than one region. Filtering for counts greater than 1 highlights invalid cases.
- B. Incorrect.
This approach is incorrect because comparing the count of distinct (customer_id, region) pairs with the count of distinct customer_id does not directly validate uniqueness of region per customer_id. It may yield false positives.
- C. Incorrect.
Using a WINDOW function with ROW_NUMBER might help identify duplicate rows or ordering patterns, but it is not relevant to validating unique associations between fields.
- D. Incorrect.
A LEFT JOIN on the same table could be used to check for mismatched data, but it is unnecessarily complex and not the most efficient approach for this scenario.