DAA-C01 Question 71
Single answerLoad data from external/internal stages into a tableA data analyst needs to load daily CSV files from an Amazon S3 external stage into an existing Snowflake table named SALES_RAW. The files have a header row, use commas as delimiters, and should be loaded only once even if the same COPY INTO command is run multiple times. The analyst also wants malformed rows to be skipped so valid rows continue loading. Which COPY INTO statement best meets these requirements?
- A
COPY INTO SALES_RAW FROM @daily_sales_stage FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1) ON_ERROR = CONTINUE;
- B
COPY INTO SALES_RAW FROM @daily_sales_stage FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1) FORCE = TRUE ON_ERROR = CONTINUE;
- C
COPY INTO SALES_RAW FROM @daily_sales_stage FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1) VALIDATION_MODE = RETURN_ERRORS ON_ERROR = CONTINUE;
- D
COPY INTO SALES_RAW FROM @daily_sales_stage FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' PARSE_HEADER = TRUE) ON_ERROR = ABORT_STATEMENT;
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it uses the standard Snowflake bulk loading pattern for staged files into a table with the correct CSV file format settings and error-handling behavior. In Snowflake, COPY INTO
from an internal or external stage records load metadata so that successfully loaded files are not loaded again by default into the same table unless the user explicitly overrides this behavior, such as with FORCE = TRUE. For CSV files with a header row, SKIP_HEADER = 1 is the appropriate file format setting. To continue processing despite bad records, ON_ERROR = CONTINUE is commonly used, though teams should review rejected rows afterward for data quality purposes. Snowflake documentation for COPY INTO- A. Correct.
Correct. COPY INTO tracks load metadata for staged files and, by default, avoids reloading files that were already loaded successfully into the target table. The file format settings correctly identify CSV input, comma delimiter, and one header row to skip. ON_ERROR = CONTINUE allows Snowflake to skip problematic rows and continue loading valid records. This matches the scenario requirements.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded previously, which directly violates the requirement to load each file only once when the same COPY INTO command is rerun. Although ON_ERROR = CONTINUE is appropriate for malformed rows, FORCE = TRUE makes this option unsuitable.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS validates files instead of performing the data load. It is useful for testing and troubleshooting, but not for actually loading data into the table. Also, combining VALIDATION_MODE with a normal loading expectation is a common misconception; validation mode returns errors rather than inserting rows.
- D. Incorrect.
Incorrect. PARSE_HEADER = TRUE is used in specific contexts for interpreting header rows as column names, but the requirement here is simply to skip the first row of CSV data, which is handled by SKIP_HEADER = 1. In addition, ON_ERROR = ABORT_STATEMENT stops the load on the first error, which contradicts the requirement to continue loading valid rows when malformed rows are encountered.