Data Analyst Associate · Domain 2: Managing Data · 8% of the exam
Task 2.2: Perform data cleaning on Unity Catalog Tables in SQL, including removing invalid data or handling missing values
NULL semantics, coalesce, nullif and try_cast, trimming and replacing values, deleting or updating bad rows in Delta tables, and constraints that keep invalid data out.
Study it
Cleaning data in SQL: NULLs, invalid values and constraints
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
Before cleaning a contacts table, an analyst profiles the email column. Its five rows hold 'a@x.com', NULL, '' (an empty string), ' ' (one space) and 'a@x.com'. She runs SELECT count(*), count(email), count(DISTINCT email) FROM contacts. What does the query return?
A5, 2, 1
B5, 4, 3
C4, 4, 3
D5, 4, 4
Show the answer and why
A5, 2, 1
Incorrect
Only the NULL is left out of count(email); the empty string and the space are values, so they are counted.
B5, 4, 3
Correct
count(*) counts all rows, count(email) counts the four non-NULL values, and the distinct non-NULL values are 'a@x.com', '' and ' '.
C4, 4, 3
Incorrect
count(*) counts every retrieved row, including the one whose email is NULL.
D5, 4, 4
Incorrect
DISTINCT counts unique values, and the two 'a@x.com' rows are one value.
count(*) returns the number of rows, including those with NULLs. count(expr) counts rows where the expression is not NULL, and count(DISTINCT expr) returns the number of unique non-NULL values. An empty string or a space is not NULL, so profiling with count alone does not reveal blank values; they need their own check before cleaning.
In the Delta table main.iot.readings, some sensors wrote their two values into the wrong columns, so min_temp is greater than max_temp in those rows. An analyst runs: UPDATE main.iot.readings SET min_temp = max_temp, max_temp = min_temp WHERE min_temp > max_temp. What is the result for a row that had min_temp 30 and max_temp 10?
Amin_temp 10 and max_temp 10
Bmin_temp 30 and max_temp 30
Cmin_temp 10 and max_temp 30
DAn error, because a column cannot appear on both sides of SET
Show the answer and why
Amin_temp 10 and max_temp 10
Incorrect
The second assignment does not see the new min_temp; column references in SET use the row's values from before the update.
Bmin_temp 30 and max_temp 30
Incorrect
Assignments are not applied one after another; both use the values the row had before the update.
Cmin_temp 10 and max_temp 30
Correct
In UPDATE, references to the table's columns represent the state of the row prior to the update, so the two values are swapped.
DAn error, because a column cannot appear on both sides of SET
Incorrect
Each column may be referenced at most once as a target, which this statement does; using columns in the expressions is allowed.
UPDATE changes column values for rows that match the predicate and is supported for Delta Lake tables. In the SET expressions, references to the table's columns represent the state of the row prior to the update, so SET a = b, b = a swaps the two values in each matching row.
In a sensor table, reading is a DOUBLE column, and failed measurements were loaded as NaN instead of NULL. SELECT device_id, max(reading) FROM readings GROUP BY device_id shows NaN for every device that has a failed measurement. Which aggregate returns the highest valid reading per device?
Amax(coalesce(reading, 0))
Bmax(reading) FILTER (WHERE reading IS NOT NULL AND reading >= 0)
Cmax(reading) FILTER (WHERE reading = reading)
Dmax(reading) FILTER (WHERE NOT isnan(reading))
Show the answer and why
Amax(coalesce(reading, 0))
Incorrect
coalesce replaces only NULL values, so the NaN readings stay in the group.
Bmax(reading) FILTER (WHERE reading IS NOT NULL AND reading >= 0)
Incorrect
NaN is not NULL, and NaN counts as larger than any other numeric value, so these rows pass the filter.
Cmax(reading) FILTER (WHERE reading = reading)
Incorrect
In Databricks SQL, NaN = NaN returns true, so the NaN rows pass this filter.
Dmax(reading) FILTER (WHERE NOT isnan(reading))
Correct
isnan returns true for NaN, so the FILTER clause drops the invalid readings before max is computed.
In Databricks SQL, NaN values go last in ascending order, larger than any other numeric value, so max returns NaN for any group that holds one. isnan(expr) returns true if expr is NaN, and the optional FILTER (WHERE cond) of max limits the rows used for aggregation, so max(reading) FILTER (WHERE NOT isnan(reading)) returns the highest valid reading.
To find rows with a missing email before cleaning, an analyst runs SELECT * FROM contacts WHERE email = NULL. Some emails are NULL, but the query returns no rows. What should the condition be?
AWHERE email == NULL
BWHERE email IS NULL
CWHERE email = 'NULL'
DWHERE NOT email = NULL
Show the answer and why
AWHERE email == NULL
Incorrect
Any equality comparison with NULL yields NULL, so no row passes.
BWHERE email IS NULL
Correct
IS NULL tests for NULL values and returns true for them.
CWHERE email = 'NULL'
Incorrect
That matches the text 'NULL', not missing values.
DWHERE NOT email = NULL
Incorrect
NOT of NULL is still NULL, so no row passes.
Comparison operators return NULL when an operand is NULL, and WHERE keeps only rows where the condition is true. Use IS NULL (or IS NOT NULL) to test for missing values.