ARA-C01 Question 286
Single answerIncremental updates compared to full updatesA retail company loads a 4 TB SALES_FACT table into Snowflake every night from an operational source. The current process truncates the target table and reloads all rows, causing long warehouse run times and delaying downstream reporting. The source system can provide a daily extract containing only inserted and updated rows, but deletes are not included. Architects want to reduce compute cost and load time while preserving accurate reporting. Which approach is the MOST appropriate?
- A
Continue using the full reload, but increase the virtual warehouse size so the nightly refresh completes faster.
- B
Load the daily changed rows into a staging table, then use a MERGE statement to apply inserts and updates to SALES_FACT. Handle deletes through a separate process if delete information becomes available.
- C
Replace the table with a materialized view on top of the source extract so Snowflake automatically applies all incremental changes, including deletes not present in the extract.
- D
Use a COPY INTO command directly against SALES_FACT with ON_ERROR=CONTINUE so only new and changed rows are loaded and existing rows are updated automatically.
Show answer and explanation
Correct answer: B
Explanation
For large tables, incremental update patterns are usually preferred over full reloads when the source can provide change data. In Snowflake, a common best practice is to land changed records in a staging table and use MERGE INTO to apply matched updates and unmatched inserts to the target. This reduces compute consumption, shortens load windows, and minimizes unnecessary data churn compared to truncating and reloading the full table.
However, incremental processing is only as complete as the change data provided. If deletes are not included in the source extract, Snowflake cannot reliably identify deleted records just because they are missing from the daily file. In that case, architects should plan a separate delete-capture or reconciliation strategy, such as a CDC feed, delete flag from the source, Streams/Tasks on Snowflake-managed upstream tables, or periodic full reconciliation if business requirements allow.
This aligns with Snowflake documentation and best practices around MERGE for applying incremental changes, and with the architectural principle of processing only changed data where possible instead of repeatedly rebuilding large tables.
- A. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time for the full reload, but it does not address the core inefficiency of reprocessing all 4 TB each night. A full truncate-and-reload pattern consumes more compute and can increase downstream disruption. The scenario specifically indicates that the source can provide only changed rows, which is a strong indicator that an incremental pattern should be adopted instead of simply scaling up compute.
- B. Correct.
Correct. This is the standard Snowflake pattern for incremental updates when the source provides changed rows. Loading changes into a staging table and then using MERGE allows inserts and updates to be applied efficiently to the target table without rebuilding the entire dataset. Because the source extract does not include deletes, deletes cannot be inferred safely from the absence of records in the change file; they must be addressed through a separate delete feed, CDC mechanism, or periodic reconciliation process if required.
- C. Incorrect.
Incorrect. A materialized view does not solve this problem. Materialized views in Snowflake are used to precompute query results on top of base tables in Snowflake, not to ingest and reconcile source-system changes from external extracts. Also, a materialized view cannot infer deletes that are absent from an incremental extract. This option confuses query acceleration features with data loading and synchronization patterns.
- D. Incorrect.
Incorrect. COPY INTO loads data files into Snowflake tables, but it does not perform row-level upserts into existing records automatically. COPY INTO appends loaded data unless additional transformation logic is used elsewhere. ON_ERROR=CONTINUE only controls load behavior when encountering problematic rows; it does not compare incoming rows to existing target rows or update them. A MERGE statement is the appropriate mechanism for insert/update synchronization.