Databricks Data Engineer Associate Question 149
Single answerA data engineering team is tasked with validating that a specific value, 'null', is not present in the 'customer_id' field of the Delta Lake table 'customers'. Which of the following options is the correct way to validate this requirement?
- A
SELECT * FROM customers WHERE customer_id IS NULL
- B
SELECT * FROM customers WHERE customer_id IS NOT NULL
- C
SELECT COUNT(*) FROM customers WHERE customer_id IS NULL
- D
SELECT DISTINCT customer_id FROM customers WHERE customer_id = 'null'
Show answer and explanation
Correct answer: C
Explanation
To validate that NULL is not present in a specific field, you need to count the rows where the field is NULL. If the count is 0, it confirms that there are no NULL values in that field. In this case, using 'SELECT COUNT(*) FROM customers WHERE customer_id IS NULL' will provide the correct validation, as it explicitly checks for rows with NULL values.
- A. Incorrect.
This option checks for rows where 'customer_id' is NULL, but it does not validate that NULL is not present. Instead, it retrieves rows with NULL values.
- B. Incorrect.
This option retrieves rows where 'customer_id' is NOT NULL, but it does not confirm that NULL values are entirely absent in the 'customer_id' field.
- C. Correct.
This option counts the number of rows where 'customer_id' is NULL, which is the correct approach to validate whether NULL values are present. If the count is 0, it confirms that NULL is not present.
- D. Incorrect.
This option incorrectly assumes the 'null' value is a string literal, which is not the same as the NULL value in SQL. Hence, it cannot validate the absence of NULL in 'customer_id'.