DAA-C01 Question 5
Single answerRetrieve data from a sourceA retail analytics team needs to analyze daily sales data that is delivered as compressed CSV files to an external stage in Snowflake. The files include occasional malformed rows caused by upstream system issues, but analysts want to load as many valid rows as possible into a staging table before building dashboards. They also need visibility into rows that failed during the load so the source team can fix them later. Which approach should the data analyst use?
- A
Run a COPY INTO staging table command with ON_ERROR = CONTINUE, then review the rejected-row details from the load results and validation functions/history.
- B
Run SELECT directly from the external stage without loading, because staged files automatically skip malformed rows and expose rejected records in the query result.
- C
Run COPY INTO staging table with ON_ERROR = ABORT_STATEMENT to ensure all valid rows are loaded first, then inspect the partially loaded data for failures.
- D
Create a view on top of the external stage and use that view for dashboards, because views on stages retain row-level error metadata for malformed records.
Show answer and explanation
Correct answer: A
Explanation
The best choice is to load from the staged source using COPY INTO with ON_ERROR = CONTINUE. This aligns with Snowflake best practices for ingesting semi-clean source files when the business wants to preserve good data while isolating bad rows for later investigation. In Snowflake, COPY INTO is the standard mechanism for loading from internal or external stages into tables. Error-handling options such as ON_ERROR let teams control whether a load stops or continues. For post-load investigation, Snowflake provides load history and validation capabilities that help identify rejected rows and file-level issues. By contrast, using ON_ERROR = ABORT_STATEMENT would fail the load on the first encountered error, which conflicts with the scenario. Querying files directly from a stage may help with exploration, but it is not the right operational pattern for resilient ingestion into analytics tables. Relevant Snowflake documentation includes the COPY INTO
command, staged data querying, and COPY history/validation functions.- A. Correct.
Correct. COPY INTO supports loading data from staged files into a Snowflake table, and ON_ERROR = CONTINUE allows valid rows to load while skipping problematic records. Snowflake also provides mechanisms such as copy/load history and validation of loaded files to investigate errors and rejected rows. This is the practical pattern when the goal is to maximize successful ingestion while preserving visibility into bad records for remediation.
- B. Incorrect.
Incorrect. Querying staged files can be useful for inspection, but it is not a substitute for a controlled load process when analysts need durable table data for downstream dashboards. Also, malformed rows are not simply 'automatically skipped' in a way that provides the same load auditing and rejected-row tracking expected from COPY INTO operations.
- C. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT stops the load when an error is encountered. It does not continue loading valid rows first and therefore does not meet the requirement to ingest as many good records as possible. This option reflects a common misunderstanding of COPY error-handling behavior.
- D. Incorrect.
Incorrect. Snowflake does not use a standard view-on-stage pattern to manage malformed file records for dashboard consumption. Even if files can be queried from a stage in some cases, views do not provide a built-in row-level rejected-record management workflow for malformed data. The requirement is specifically about controlled ingestion and auditing of failures, which COPY INTO addresses.