DAA-C01 Question 1
Single answerDomain 1.0: Data Ingestion and Data Preparation (17%)A retail analytics team receives daily CSV files from multiple regional stores in an internal stage. The files contain a header row, occasionally include malformed records, and must be loaded into a curated SALES_RAW table before analysts can build dashboards. The team wants to preserve as many valid rows as possible during ingestion while also making rejected rows available for investigation. Which approach best meets these requirements with the least operational overhead?
- A
Use COPY INTO SALES_RAW with a file format that skips the header row and set ON_ERROR = CONTINUE; then query the staged files later using VALIDATE() to identify rejected rows
- B
Use COPY INTO SALES_RAW with ON_ERROR = ABORT_STATEMENT so the entire file fails if any bad row is found; then reprocess files manually after correcting source data
- C
Load the files into a temporary table first using INSERT ... SELECT from the stage, because COPY INTO cannot continue loading when malformed CSV rows exist
- D
Use Snowpipe Streaming to ingest the CSV files directly from the internal stage, because it automatically stores malformed rows in a separate error table
Show answer and explanation
Correct answer: A
Explanation
The most appropriate solution is to use COPY INTO with a properly defined CSV file format and ON_ERROR = CONTINUE. This aligns with Snowflake best practices for staged file ingestion when the objective is to maximize successful row loads while tolerating some bad records. A file format can address structural parsing settings such as SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, and delimiters. After the load, Snowflake provides validation support to inspect loading errors for rows that were rejected. In contrast, ABORT_STATEMENT sacrifices valid data by failing the entire load, and Snowpipe Streaming is intended for streaming records rather than staged CSV files. This pattern is consistent with Snowflake documentation for COPY INTO
, file formats, ON_ERROR behavior, and load validation/validation functions.- A. Correct.
Correct. This is the best fit for the scenario. A COPY INTO command can load data from staged CSV files into a table while using a file format to handle the header row (for example, SKIP_HEADER = 1). Setting ON_ERROR = CONTINUE allows Snowflake to load valid rows and skip problematic records instead of failing the entire load. Rejected-row details can then be investigated using Snowflake's validation capabilities, such as the VALIDATE function against the target table after a COPY operation. This approach minimizes operational overhead while preserving good data and making bad rows discoverable.
- B. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT causes the entire load statement to fail on the first encountered error, which conflicts with the requirement to preserve as many valid rows as possible. Although this approach may enforce stricter source quality, it increases manual intervention and delays downstream analytics.
- C. Incorrect.
Incorrect. COPY INTO is specifically designed for efficient bulk loading from stages and does support error-handling behaviors such as continuing past malformed rows. Using INSERT ... SELECT from staged files is generally not the preferred low-overhead bulk ingestion pattern for this scenario and does not solve the malformed-row handling requirement better than COPY INTO.
- D. Incorrect.
Incorrect. Snowpipe Streaming is not the right mechanism for loading CSV files directly from an internal stage. It is designed for row-based streaming ingestion from client applications, not staged file ingestion. Also, Snowflake does not automatically place malformed CSV rows into a built-in separate error table in the way described here.