Databricks Data Engineer Associate Question 140
Select 2You are working with a Databricks Delta table named orders that contains the columns order_id and customer_id. You need to validate that each customer_id is associated with only one unique order_id. Which of the following approaches would achieve this validation?
- A
Use a GROUP BY query on
customer_idand check that the maximum count oforder_idpercustomer_idis equal to 1. - B
Use the DISTINCT keyword on both
customer_idandorder_idand validate that the record count matches the total number of rows in the table. - C
Use the COUNT function with a window partitioned by
customer_idand filter rows where the count oforder_idis greater than 1. - D
Use the DROP DUPLICATES function on the
customer_idcolumn and ensure the resulting dataset has the same row count as the original table.
Show answer and explanation
Correct answers: A, C
Explanation
To ensure that each customer_id is associated with exactly one unique order_id, you can either group by customer_id and verify that no order_id count exceeds 1 (Option 1), or use a window function to count order_id values for each customer_id and identify violations (Option 3). Both approaches ensure data integrity for this validation.
- A. Correct.
Correct. Grouping by
customer_idand ensuring the maximum count oforder_idpercustomer_idis equal to 1 effectively validates that eachcustomer_idis associated with only one uniqueorder_id. - B. Incorrect.
Incorrect. Using DISTINCT on both columns does not confirm that each
customer_idmaps to exactly one uniqueorder_id; it only ensures that duplicate rows are removed. - C. Correct.
Correct. Using a window function to count
order_idoccurrences percustomer_idand filtering for counts greater than 1 will help identify violations of the one-to-one relationship. - D. Incorrect.
Incorrect. Dropping duplicates on
customer_iddoes not validate the uniqueness oforder_idpercustomer_id, as it only removes duplicatecustomer_identries.