SnowPro Advanced: Data Engineer free question 9 / 10 · Domain 5: Data Transformation
In the VARIANT column src, the element middle_name is a JSON null in some rows and missing altogether in others. Filtering with WHERE src:middle_name IS NULL leaves out the rows with a JSON null. Which way of writing it, to treat the JSON null rows as SQL NULL as well, matches the documentation?
- Cast to VARIANT as in src:middle_name::variant and then compare with IS NULL
- Cast to a string as in src:middle_name::string and then compare with IS NULL
- Expand with FLATTEN using OUTER => TRUE and then compare VALUE with IS NULL
- Extract it with GET_PATH(src, 'middle_name') and then compare with IS NULL
Show the answer and explanation
Answer: B
Why this is correct
Semi-structured data has two kinds of NULL. SQL NULL means no value or an unknown value, and a row where the element is missing altogether is this. JSON null (VARIANT NULL) is stored in a VARIANT column as a string containing the word null and is distinguished from SQL NULL. For this reason IS NULL does not pick up a JSON null. The documentation says that to turn a JSON null into SQL NULL, you cast it as a string (TO_VARCHAR and the like also work).
Why the other options are wrong
"Extract it with GET_PATH(src, 'middle_name') and then compare with IS NULL" … GET_PATH is a function that does the same thing as the colon path syntax, and the extracted value remains a VARIANT JSON null. IS NULL does not become true.
"Cast to VARIANT as in src:middle_name::variant and then compare with IS NULL" … Casting to VARIANT does not change the type, and a JSON null stays a JSON null. What turns it into SQL NULL is casting to a string.
"Expand with FLATTEN using OUTER => TRUE and then compare VALUE with IS NULL" … OUTER => TRUE is a setting that outputs one row with KEY, INDEX, and VALUE as NULL for an input row that cannot be expanded. It does not turn a JSON null of a scalar element into SQL NULL.
The array case
When you mix SQL NULL and JSON null as elements while building an ARRAY, SQL NULL comes out as an undefined element and JSON null as a null element. The result of downstream functions changes depending on which kind of NULL is in there.