Databricks Certified Data Engineer - Associate

Explanation

Let's analyze each option:

Option A: SELECT count(member_id) FROM my_table;

• The COUNT() function in SQL counts only non-null values by default.
• This returns the number of non-null values in the member_id column, not the null values.

Option B: SELECT count(member_id) - count_null(member_id) FROM my_table;

• There is no built-in count_null() function in standard SQL or Spark SQL.
• This would result in a syntax error.

Option C: SELECT count_if(member_id IS NULL) FROM my_table;

• ✅ CORRECT: The COUNT_IF() function in Spark SQL counts rows where the condition is true.
• member_id IS NULL returns true for null values, so this correctly counts the number of null values.
• Alternatively, SELECT COUNT(*) FROM my_table WHERE member_id IS NULL; would also work, but COUNT_IF() is more concise.

Option D: SELECT null(member_id) FROM my_table;

• There is no NULL() function in SQL.
• This would return an error or unexpected results.

Option E: SELECT count_null(member_id) FROM my_table;

• As mentioned in option B, there is no count_null() function in standard SQL or Spark SQL.
• This would result in a syntax error.

Additional Notes

In Spark SQL, you can also use:

1. SELECT SUM(CASE WHEN member_id IS NULL THEN 1 ELSE 0 END) FROM my_table;
2. SELECT COUNT(*) - COUNT(member_id) FROM my_table; (This counts total rows minus non-null values)

However, COUNT_IF(member_id IS NULL) is the most direct and readable approach for counting null values in Spark SQL.