Databricks Data Engineer Associate Question 111
Select 3You are analyzing a sales dataset in a Delta table to identify rows with missing values in the 'discount' column. Which of the following options correctly describe how to use the count_if function and null checks to achieve this task?
- A
Use
count_if(discount IS NULL)to count rows where the 'discount' column is null. - B
Use
count_if(discount IS NOT NULL)to count rows where the 'discount' column has values. - C
Use
count(1) - count(discount)to count rows where the 'discount' column is null. - D
Use
count(discount)to count rows where the 'discount' column has non-null values. - E
Use
count(*)to directly count all rows where the 'discount' column is null.
Show answer and explanation
Correct answers: A, C, D
Explanation
To count rows with null values in the 'discount' column, you can use count_if(discount IS NULL) directly, or calculate the difference between the total row count (count(1) or count(*)) and the count of non-null values (count(discount)). The count_if function is particularly useful for explicit conditional checks.
- A. Correct.
Correct. The
count_iffunction can be used with a condition, such asdiscount IS NULL, to count rows where a specific condition is true, including null checks. - B. Incorrect.
Incorrect. While
count_if(discount IS NOT NULL)counts rows with non-null values, the question specifically asks about identifying rows with null values. - C. Correct.
Correct. The difference between
count(1)(total rows) andcount(discount)(rows with non-null values) gives the count of rows where the 'discount' column is null. - D. Correct.
Correct. The
count(discount)function counts rows with non-null values in the 'discount' column, which is essential for identifying missing values indirectly by subtraction. - E. Incorrect.
Incorrect. The
count(*)function counts all rows in the dataset, regardless of null values in any specific column. It does not directly count rows where the 'discount' column is null.