DAA-C01 Question 131
Single answerHandle nullsA retail analytics team stores order events in a Snowflake table named ORDER_FACT. The column DISCOUNT_CODE is nullable because many orders do not use a promotion. An analyst must build a report that groups orders into two categories: actual discount codes and a single bucket labeled 'NO_CODE' for orders where DISCOUNT_CODE is NULL. The analyst also wants to avoid changing rows where DISCOUNT_CODE contains an empty string (''). Which SQL expression should be used in the SELECT and GROUP BY clauses to meet the requirement?
- A
COALESCE(DISCOUNT_CODE, 'NO_CODE')
- B
NVL(NULLIF(DISCOUNT_CODE, ''), 'NO_CODE')
- C
IFF(DISCOUNT_CODE = NULL, 'NO_CODE', DISCOUNT_CODE)
- D
ZEROIFNULL(DISCOUNT_CODE)
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to replace only SQL NULL values while preserving non-NULL values exactly as stored, including empty strings. In Snowflake, COALESCE(expr1, expr2, ...) returns the first non-NULL expression, making COALESCE(DISCOUNT_CODE, 'NO_CODE') the most appropriate solution. NVL can also replace NULLs, but the specific option using NULLIF changes empty strings into NULL first, which is not desired. Another common mistake is using '= NULL' instead of 'IS NULL'; in SQL's three-valued logic, '= NULL' does not work for null detection. Snowflake documentation for conditional expression functions and null handling supports using COALESCE/NVL for substitution and IS NULL for null checks.
- A. Correct.
Correct. COALESCE returns the first non-NULL expression, so it replaces only true SQL NULL values in DISCOUNT_CODE with 'NO_CODE'. If DISCOUNT_CODE contains an empty string, that value is not NULL in Snowflake and will remain unchanged, which matches the requirement. This expression can be used consistently in both SELECT and GROUP BY.
- B. Incorrect.
Incorrect. NULLIF(DISCOUNT_CODE, '') converts empty strings to NULL, and then NVL replaces those NULLs with 'NO_CODE'. That means both actual NULL values and empty strings would be grouped into 'NO_CODE', which violates the requirement to leave empty strings unchanged.
- C. Incorrect.
Incorrect. In SQL, comparing a value to NULL with '=' does not evaluate to TRUE; it results in NULL/unknown. In Snowflake, proper null checks use IS NULL or functions such as COALESCE/NVL. As written, this expression will not correctly identify NULL discount codes.
- D. Incorrect.
Incorrect. ZEROIFNULL is intended for numeric expressions and returns 0 when the input is NULL. DISCOUNT_CODE is a string column, and the requirement is to produce the label 'NO_CODE', not a numeric fallback.