Databricks Data Engineer Associate Question 145
Select 2You are working on a Databricks Delta table named 'sales_data' that contains millions of records. You want to validate that no records in the 'customer_id' column have the value NULL. Which of the following approaches can you use to achieve this validation?
- A
Use a filter operation with the condition
customer_id IS NULLand count the resulting rows. - B
Use the
isNull()function on the 'customer_id' column and collect the results. - C
Run a SQL query using
SELECT * FROM sales_data WHERE customer_id IS NULLto check if any rows exist. - D
Use the
distinct()method on the 'customer_id' column to ensure NULL is not a unique value.
Show answer and explanation
Correct answers: A, C
Explanation
To validate that a specific value (in this case, NULL) is not present in a column, you can use filtering or a SQL query to identify rows with that value. Both approaches are efficient and conform to best practices for handling large datasets in Databricks. The distinct() method and isNull() combined with collect() are either less practical or inefficient for this purpose.
- A. Correct.
Correct: Filtering rows with
customer_id IS NULLand counting them is a valid approach to check whether NULL values are present in the column. - B. Incorrect.
Incorrect: While the
isNull()function can identify NULL values, using it withcollect()can be inefficient for large datasets as it brings data to the driver. - C. Correct.
Correct: Running a SQL query to look for rows where
customer_id IS NULLis an effective and scalable way to validate that no NULL values are present in the column. - D. Incorrect.
Incorrect: The
distinct()method identifies unique values in a column but does not directly validate the absence of NULL values unless further processing is done.