Snowflake DEA-C02: Data Movement Practice Questions

STRIP_OUTER_ARRAY = TRUE tells the JSON parser to remove the outermost array brackets and load each array element as a separate row — this is the correct option for top-level JSON arrays. STRIP_NULL_VALUES removes null key-value pairs from JSON objects but does not affect array handling. ENABLE_OCTAL controls interpretation of octal numbers in JSON and is unrelated to array structure. TRIM_SPACE removes leading/trailing whitespace from string fields and has no effect on JSON array parsing.

Option C is correct: in a standard stream, UPDATEs are represented as two rows — a DELETE row for the old value and an INSERT row for the new value, both with METADATA$ISUPDATE=TRUE. The current query keeps the INSERT half (new value) but discards the DELETE half, so the target will accumulate duplicate rows instead of updating them. Option E is correct: to properly apply CDC (handle inserts, update-new-values, and deletes), a MERGE statement matching on a primary key is required rather than a plain INSERT. Option A is false: standard streams DO capture updates, just as DELETE+INSERT pairs. Option D is false: there is no METADATA$ACTION = 'UPDATE' value; updates appear as DELETE and INSERT pairs. Option B is incorrect: filtering METADATA$ISUPDATE=FALSE would isolate true inserts from update-inserts, but the problem description is about updates not being reflected — this filter would make things worse, not better.

Correct (A): Snowflake natively understands Parquet's columnar format, including nested structs. When the target column is VARIANT, Snowflake automatically maps the nested struct to a VARIANT value during COPY INTO without any extra options. | Wrong (B): While a FILE FORMAT of type PARQUET is recommended and needed for Snowflake to parse the file, it does not require a FORMAT_NAME clause specifically—inline FORMAT options work too, and the automatic VARIANT conversion is not gated on using a named format. | Wrong (C): Flattening is not required. A SELECT transformation in COPY INTO can be used to reshape data but is not mandatory for loading nested Parquet structs into a VARIANT column. | Wrong (D): STRIP_OUTER_ARRAY = TRUE is a JSON-specific option used to remove the outer array wrapper in JSON documents; it has no applicability to Parquet files and Parquet nested structs are not 'rejected' by default.

Option D is correct: FIELD_DELIMITER = '|' specifies the pipe character, SKIP_HEADER = 0 means no header row is skipped (correct since there is no header), and FIELD_OPTIONALLY_ENCLOSED_BY = '"' handles double-quoted strings. Option A is wrong because SKIP_HEADER = 1 would skip the first data row, which is incorrect when there is no header. Option B is wrong because 'PIPE' is not a valid value for FIELD_DELIMITER — you must use the literal character. Option C is wrong because COLUMN_SEPARATOR is not a valid Snowflake CSV file format parameter (the correct parameter is FIELD_DELIMITER), and SINGLE_QUOTE is not valid syntax for FIELD_OPTIONALLY_ENCLOSED_BY.

Option A is correct: Snowflake tracks loaded files in a 64-day metadata window. If the file was previously loaded (or if a file with the same name and checksum was loaded before), COPY INTO skips it by default. To reload such a file, the engineer must use FORCE = TRUE. This is a very common operational issue. Option C is incorrect: named internal stages are schema-level objects persisted in the Snowflake metadata layer, not session-scoped; any authorized user in any session can access and load from them after a PUT. Option D is a plausible distractor — PUT does compress files with gzip by default — but if the compression format were truly mismatched, COPY INTO would error out or produce a parsing failure, not silently process zero files. Option B is incorrect: PURGE = TRUE is an option that deletes staged files AFTER a successful load; it has no effect on whether COPY INTO reads newly uploaded files.