DAA-C01 Question 68
Single answer1.6 Given a scenario, prepare data and load into Snowflake.A retail analytics team receives a daily CSV extract from a third-party system in an Amazon S3 bucket. The file contains a header row, occasionally includes blank strings for numeric fields such as QUANTITY and UNIT_PRICE, and sometimes contains malformed rows at the end of the file. Analysts need the valid records loaded into a Snowflake table as quickly as possible for downstream reporting, while malformed rows must not prevent the load from completing. The team also wants to minimize manual preprocessing outside Snowflake. Which approach best meets these requirements?
- A
Create a file format for CSV with SKIP_HEADER=1 and EMPTY_FIELD_AS_NULL=TRUE, then use COPY INTO the target table with ON_ERROR=CONTINUE.
- B
Use Snowpipe with the default CSV file format and ABORT_STATEMENT so that malformed rows are captured and analysts can reload the file manually after correcting it in S3.
- C
Load the file into a VARIANT column with PARSE_JSON in a COPY statement, then cast the data into relational columns after the load completes.
- D
Use COPY INTO with TRUNCATECOLUMNS=TRUE and FORCE=TRUE so malformed rows are truncated and blank numeric values are automatically converted to zero.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define an appropriate CSV file format and use COPY INTO with error-handling options suited to partial-load scenarios. In Snowflake, file formats are the standard mechanism for parsing staged data, including CSV options such as SKIP_HEADER and EMPTY_FIELD_AS_NULL. COPY INTO supports ON_ERROR behaviors like CONTINUE, which is commonly used when the business goal is to ingest all valid rows without failing the entire load because of a small number of bad records. This approach aligns with Snowflake best practices for preparing and loading delimited files with minimal external preprocessing. Relevant Snowflake documentation includes the sections for CREATE FILE FORMAT, especially CSV file format options, and COPY INTO
, including ON_ERROR behavior and staged file loading patterns.- A. Correct.
Correct. This approach addresses all stated requirements using native Snowflake loading features. SKIP_HEADER=1 handles the CSV header row. EMPTY_FIELD_AS_NULL=TRUE ensures empty fields are interpreted as NULL rather than empty strings, which is important for numeric columns such as QUANTITY and UNIT_PRICE. ON_ERROR=CONTINUE allows valid rows to load even if some rows are malformed, which matches the requirement that bad records must not block the load. This also minimizes external preprocessing because the handling is done within Snowflake through a file format and COPY INTO options.
- B. Incorrect.
Incorrect. Snowpipe can automate ingestion from S3, but the scenario's main requirement is to load valid rows while allowing malformed rows to be skipped. Using ABORT_STATEMENT does the opposite: the load stops when an error occurs. Also, relying on manual correction and reload in S3 increases operational effort rather than minimizing preprocessing outside Snowflake. The default CSV file format would also not explicitly address the header row or empty fields for numeric columns.
- C. Incorrect.
Incorrect. PARSE_JSON is for JSON data, not CSV files. Loading a CSV extract into a VARIANT column with PARSE_JSON is not an appropriate or valid solution for this scenario. While VARIANT can be useful for semi-structured formats such as JSON, Avro, ORC, or Parquet, it does not solve standard CSV parsing requirements like skipping headers or handling malformed CSV rows.
- D. Incorrect.
Incorrect. TRUNCATECOLUMNS=TRUE only truncates string data that exceeds the target column length; it does not repair malformed rows. FORCE=TRUE reloads files even if Snowflake has loaded them before, which is unrelated to the stated problem and could create duplicate-load risks if not carefully managed. Blank numeric values are not automatically converted to zero by these settings. Treating empty fields as NULL requires appropriate file format handling such as EMPTY_FIELD_AS_NULL=TRUE.