DAA-C01 Question 2
Single answerDomain 1.0: Data Ingestion and Data Preparation (17%)A retail analytics team loads daily point-of-sale files from an external stage into a Snowflake table named SALES_RAW. The source system occasionally resends previously delivered files with the same filename after correcting a few rows. Analysts report that corrected data is not appearing in SALES_RAW when the existing load process runs. The current process uses a scheduled COPY INTO SALES_RAW FROM @pos_stage FILE_FORMAT = (TYPE = CSV) ON_ERROR = 'CONTINUE'.
The team wants a solution that allows Snowflake to reload corrected files when needed, while minimizing unnecessary duplicate ingestion of unchanged files. Which approach should the data analyst recommend?
- A
Modify the COPY INTO command to use FORCE = TRUE for every scheduled run so all files are reloaded each time.
- B
Keep the current COPY INTO process and rename corrected files before placing them in the stage so Snowflake treats them as new files.
- C
When corrected files must be reprocessed, run COPY INTO with FORCE = TRUE only for that targeted reload, combined with a process to remove or reconcile previously loaded rows from those files.
- D
Set ON_ERROR = 'ABORT_STATEMENT' so Snowflake retries files that were previously loaded with corrected contents.
Show answer and explanation
Correct answer: C
Explanation
Snowflake COPY INTO maintains load metadata to prevent the same staged files from being loaded repeatedly under normal operation. This is generally desirable for incremental ingestion, but it also means that if a source system resends a corrected file with the same name, Snowflake will typically skip it unless the load is explicitly forced. The most appropriate recommendation is to use FORCE = TRUE selectively for controlled reprocessing, not for every scheduled run, and pair that with downstream reconciliation logic such as deleting previously loaded rows for the affected file, loading into a staging table and MERGEing, or using metadata columns to identify the source file. This aligns with Snowflake best practices for balancing idempotent ingestion with exception-based reprocessing. Relevant Snowflake documentation areas include COPY INTO
, file load history behavior, and options such as FORCE and ON_ERROR.- A. Incorrect.
This is incorrect because FORCE = TRUE causes COPY INTO to load files regardless of load history. Using it on every scheduled run would repeatedly reload all staged files and likely create duplicates unless additional deduplication logic exists. That does not meet the requirement to minimize unnecessary duplicate ingestion of unchanged files.
- B. Incorrect.
This is plausible, but it is not the best recommendation in this scenario. Renaming corrected files can make Snowflake treat them as new files because COPY INTO tracks load metadata by filename and related file loading state. However, this approach depends on upstream file management conventions and still risks duplicate business records unless prior rows are handled. It is more of a workaround than a controlled reprocessing strategy.
- C. Correct.
This is correct. By default, Snowflake uses load metadata to avoid reloading files that were already loaded. If a file with the same name is corrected and needs to be ingested again, FORCE = TRUE can be used intentionally for that reprocessing event. Because reloading can duplicate rows already loaded from the original version of the file, the process should also include removing, merging, or otherwise reconciling data associated with that file. This targeted approach supports corrected-file ingestion without forcing unnecessary reloads of every file.
- D. Incorrect.
This is incorrect because ON_ERROR controls behavior when parsing or loading encounters row-level or file-level errors during the COPY operation. It does not change Snowflake's file load history behavior and does not cause previously loaded files with the same name to be retried just because their contents changed.