Databricks Data Engineer Associate Question 143
Select 2You are working with a Delta Lake table named 'transactions' in Databricks. The table has two columns: 'customer_id' and 'region'. You are tasked with validating that each 'customer_id' is associated with only one unique 'region'. Which of the following methods would correctly ensure this validation?
- A
Use a groupBy on 'customer_id', aggregate the distinct count of 'region', and filter for counts greater than 1.
- B
Run a SQL query that selects 'customer_id' where the count of distinct 'region' is greater than 1.
- C
Create a new column showing the count of distinct 'region' for each 'customer_id' and filter where the count is equal to 1.
- D
Use the DISTINCT keyword in a SQL query to count the number of unique (customer_id, region) pairs and compare it to the count of unique 'customer_id'.
Show answer and explanation
Correct answers: A, B
Explanation
To validate that each 'customer_id' is associated with only one unique 'region', you need to identify cases where the count of distinct 'region' values for a given 'customer_id' is greater than 1. Both grouping and filtering or using SQL queries to identify these cases are valid approaches. Incorrect approaches either misinterpret the uniqueness requirement or fail to properly validate the relationship between the fields.
- A. Correct.
This approach correctly groups the data by 'customer_id', calculates the distinct count of 'region', and filters for cases where the count is greater than 1, which identifies violations.
- B. Correct.
This SQL-based approach achieves the same result as the first option by directly identifying 'customer_id' entries associated with more than one unique 'region'.
- C. Incorrect.
This approach incorrectly validates the data because it assumes that filtering for rows with a count equal to 1 ensures uniqueness, which is not the case when there are multiple regions for a 'customer_id'.
- D. Incorrect.
While counting unique (customer_id, region) pairs and comparing this to unique 'customer_id' could be insightful, it does not directly validate that a 'customer_id' is associated with only one unique 'region'.