Databricks Certified Data Engineer - Associate

Explanation

Option C (SELECT count_if(member_id IS NULL) FROM my_table;) is the correct answer because:

1. count_if() function in Databricks SQL is specifically designed to count rows that satisfy a given condition.
2. member_id IS NULL is the correct condition to identify null values in the column.
3. The count_if() function returns the number of rows where the condition evaluates to true.

Why other options are incorrect:

• Option A (SELECT count(member_id) FROM my_table;): The count() function counts only non-null values. According to the Databricks documentation, when a column is specified (not *), count() only counts rows where the expression is not NULL.

• Option B (SELECT count(member_id) - count_null(member_id) FROM my_table;): There is no built-in count_null() function in Databricks SQL. This would result in an error.

• Option D (SELECT null(member_id) FROM my_table;): The null() function doesn't exist for counting null values. This would either return null values or result in an error.

• Option E (SELECT count_null(member_id) FROM my_table;): As mentioned, there is no count_null() function in Databricks SQL. This is not a valid SQL function.

Key Learning Points:

• Use count_if(column IS NULL) to count null values in a column
• count(column) counts only non-null values
• count(*) counts all rows including those with null values
• Always refer to official Databricks documentation for function behavior