ARA-C01 Question 285
Single answerIncremental updates compared to full updatesA retail company loads a 2 TB SALES_FACT table into Snowflake every night from an operational source system. Today, the source system can provide both a full extract and a change feed containing inserted and updated rows since the last successful load. The target table is heavily used by downstream BI dashboards starting at 6 AM, and the current nightly full refresh frequently exceeds the available batch window. The architect wants to reduce load time and compute cost while keeping the table current. Which approach is the MOST appropriate?
- A
Load the change feed into a staging table and use a MERGE statement to apply inserts and updates to SALES_FACT, processing only changed rows each night.
- B
Continue using the nightly full refresh, but increase the virtual warehouse size so the entire 2 TB table can be reloaded faster.
- C
Replace the target table each night with a CREATE OR REPLACE TABLE AS SELECT from the full extract to avoid row-level operations.
- D
Use a standard view on top of the full extract and the existing SALES_FACT table so downstream queries automatically see the latest version without any update process.
Show answer and explanation
Correct answer: A
Explanation
For large fact tables with limited nightly change volume, incremental updates are usually preferable to full refreshes because they reduce compute consumption, shorten load windows, and limit unnecessary data processing. In Snowflake, a common implementation is to ingest changed data into a staging table and then use MERGE to synchronize the target table by applying inserts and updates. This aligns with Snowflake best practices for ELT design when a trusted delta feed exists. A full refresh may still be appropriate in some situations, such as when the source cannot reliably identify changes, when logic requires a complete recomputation, or when the table is small enough that full reloads are simpler and operationally acceptable. However, in this scenario, the combination of a large table, a constrained batch window, downstream SLA pressure, and an available change feed makes incremental loading with MERGE the best architectural choice. Relevant Snowflake documentation includes guidance on MERGE for upsert patterns and general performance/cost considerations for warehouse sizing and data loading.
- A. Correct.
Correct. When the source provides a reliable change feed, the most efficient pattern is to stage the changed records and use MERGE to perform incremental updates. This minimizes data movement and compute by processing only inserted and updated rows rather than reloading the entire 2 TB table. It is also a practical design for meeting a constrained batch window while keeping downstream BI tables current.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time for a full refresh, but it does not address the underlying inefficiency of reprocessing unchanged data every night. This option typically increases compute cost and may still be less efficient than an incremental pattern for a large table with a relatively small daily change volume.
- C. Incorrect.
Incorrect. CREATE OR REPLACE TABLE AS SELECT from a full extract is still a full rebuild approach. Although it can simplify loading logic, it rewrites the full table and is generally not the best choice when the source can provide incremental changes and the goal is to reduce both load time and compute cost.
- D. Incorrect.
Incorrect. A standard view does not apply data changes to the base table and does not eliminate the need for a loading strategy. This option confuses logical query abstraction with physical data maintenance. Downstream queries would also still depend on how the underlying full extract is loaded and maintained.