DAA-C01 Question 73
Single answerLoad different types of dataA retail analytics team needs to load daily clickstream data into Snowflake for downstream analysis. The source system writes newline-delimited JSON files to an external stage in cloud storage. The files are not perfectly clean: some records contain an extra trailing field, and a small number of rows are malformed. Analysts want to preserve all successfully parsed records, keep malformed rows from aborting the load, and capture any extra unmatched columns when loading into a target table that has a VARIANT column named RAW_EVENT plus a few relational columns extracted during load. Which approach best meets these requirements?
- A
Use COPY INTO with a JSON file format, set ON_ERROR = CONTINUE, and enable MATCH_BY_COLUMN_NAME to load JSON fields into the target columns while preserving extra data in RAW_EVENT.
- B
Use COPY INTO with a JSON file format and STRIP_OUTER_ARRAY = TRUE, set ON_ERROR = ABORT_STATEMENT to ensure schema consistency, and rely on automatic schema evolution to capture malformed rows in RAW_EVENT.
- C
Load the files with COPY INTO into a staging table containing a single VARIANT column, using a JSON file format and ON_ERROR = CONTINUE; then transform from the staged VARIANT data into the final table, retaining unparsed or extra attributes inside the VARIANT structure.
- D
Use COPY INTO with a CSV file format and ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE so trailing JSON attributes are ignored, while malformed JSON rows are stored as NULL values in RAW_EVENT.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to first load newline-delimited JSON into a staging table with a VARIANT column, then transform into the final schema. This is a common Snowflake best practice for semi-structured ingestion because JSON records can contain variable attributes that do not map cleanly to a fixed relational schema. Using COPY INTO with a JSON file format and ON_ERROR = CONTINUE allows Snowflake to load valid records while skipping malformed ones, which satisfies the requirement to avoid aborting the entire load. After landing the data in VARIANT, analysts can use SQL to extract standard fields into relational columns and still preserve the original JSON payload for auditability and late-binding schema needs. Snowflake documentation on loading semi-structured data, COPY INTO behavior, and VARIANT usage supports this pattern.
- A. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME is used with loading structured data such as CSV/Parquet into table columns and is not the right mechanism for mapping semi-structured JSON fields directly into relational columns while also preserving the raw record. For JSON, a common best practice is to first load into VARIANT and then transform. ON_ERROR = CONTINUE would help skip bad rows, but the rest of the approach is not appropriate for this scenario.
- B. Incorrect.
Incorrect. STRIP_OUTER_ARRAY applies when a JSON document contains an outer array that should be flattened into separate rows during load; it does not address malformed records or extra fields in newline-delimited JSON. ON_ERROR = ABORT_STATEMENT conflicts with the requirement to continue loading valid records. Snowflake does not use automatic schema evolution in this way to capture malformed JSON rows into a VARIANT column during COPY.
- C. Correct.
Correct. For semi-structured JSON, especially when records may vary and some rows are malformed, the safest and most flexible pattern is to load into a staging table with a single VARIANT column using COPY INTO and ON_ERROR = CONTINUE. This preserves successfully parsed JSON documents, skips malformed records instead of failing the batch, and allows downstream SQL to extract relational attributes into the final table while keeping the original payload in VARIANT for any extra fields.
- D. Incorrect.
Incorrect. JSON data should not be loaded with a CSV file format. ERROR_ON_COLUMN_COUNT_MISMATCH applies to delimited text formats such as CSV, not JSON parsing behavior. Malformed JSON rows are not converted to NULL in a VARIANT column simply by using CSV settings; this reflects a misunderstanding of how Snowflake parses semi-structured files.