Databricks Data Engineer Associate Question 144
Single answerYou are working with a dataset in a Delta table that contains customer transaction records. The table has two fields: customer_id and primary_email. You need to validate that each customer_id is associated with only one unique primary_email. Which approach will help you achieve this validation?
- A
Use a GROUP BY on
customer_idand check if the count of distinctprimary_emailis 1 for each group. - B
Filter the dataset where
customer_idhas more than oneprimary_emailand count the number of records. - C
Perform a JOIN between the table and itself on
customer_idand check for mismatchedprimary_emailvalues. - D
Use a DISTINCT operation on
primary_emailand count the number of records to ensure uniqueness.
Show answer and explanation
Correct answer: A
Explanation
To validate that each customer_id is associated with exactly one unique primary_email, you need to group the data by customer_id and ensure that the count of distinct primary_email values for each group is 1. This approach directly verifies the one-to-one relationship between the two fields.
- A. Correct.
Correct: Grouping by
customer_idand checking if the count of distinctprimary_emailis 1 ensures that eachcustomer_idis associated with only one uniqueprimary_email. - B. Incorrect.
Incorrect: While filtering for records with multiple
primary_emailvalues could help identify duplicates, it does not directly validate that allcustomer_idvalues are unique to oneprimary_email. - C. Incorrect.
Incorrect: A self-join can be used to compare records, but it is not an optimal or straightforward way to validate uniqueness in this scenario.
- D. Incorrect.
Incorrect: Using DISTINCT on
primary_emailonly ensures uniqueness of email values but does not validate the relationship betweencustomer_idandprimary_email.