Databricks-Spark-Assoc Using Spark SQL Practice Question
Which TWO of the following are true concerning Spark SQL's handling of NULL values?
⚠ Common exam trap
Test-takers frequently assume COUNT(*) and COUNT(column_name) treat NULL values identically, leading to incorrect calculations when missing data is present.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The COUNT(column_name) function excludes NULL values from the count.
Correctly managing NULLs is crucial for data accuracy. Spark SQL treats NULLs as unknown values. Aggregate functions like SUM and COUNT(col) skip NULLs, while count(*) includes them. Understanding these nuances is essential for developers to write robust SQL queries that correctly interpret missing data, avoiding common pitfalls in reporting and data quality validation processes within Databricks production environments.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
The COUNT(column_name) function excludes NULL values from the count.
Why this is correct
In Spark SQL, aggregate functions that target a specific column, such as COUNT(col), ignore NULL values. This behavior is standard ANSI SQL and is vital to understand when calculating metrics like non-null record counts, as failing to account for this can lead to significant errors in business reporting.
- ✗
NULL values are treated as zero in all mathematical operations.
Why it's wrong here
In Spark SQL, any mathematical operation involving a NULL value results in NULL. Treating NULL as zero would lead to incorrect calculations, such as summing an empty value into a total. Developers must explicitly use functions like COALESCE or IFNULL if they intend to treat missing values as zero.
- ✓
The COUNT(*) function includes rows where all columns contain NULL values.
Why this is correct
The COUNT(*) function is designed to count the total number of rows in the dataset, regardless of the content of those rows. It does not check for NULLs in any specific column, making it the correct choice for determining the total record count in a table or partition.
- ✗
The 'IS NULL' condition is not supported in Spark SQL; use '== NULL' instead.
Why it's wrong here
Spark SQL supports the standard 'IS NULL' and 'IS NOT NULL' syntax for checking missing values. Using '== NULL' will fail to return the expected results because NULL is not equal to anything, including itself. Proper usage of IS NULL is a fundamental requirement for filtering or handling missing data.
- ✗
Joining on columns that contain NULL values will always result in an inner join match.
Why it's wrong here
In SQL, NULL is not equal to NULL. Therefore, an inner join on a column containing NULLs will not match the rows from both sides, as the comparison results in UNKNOWN. This behavior can lead to data loss if developers do not handle NULLs in join keys using COALESCE or specific join types.
About these practice questions
This Databricks-Spark-Assoc question is part of Courseiva's 295-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.