ARA-C01 Question 349
Single answerStaging layers and tablesA retail company loads partner-delivered CSV files into Snowflake every 15 minutes. The files first land in cloud object storage and are then used to populate a raw staging layer before curated tables are updated. The architect must design the staging layer to meet these requirements: preserve the original file contents for replay, support auditing of which files were loaded, and minimize the risk of duplicate ingestion during normal operations. Which approach BEST meets these requirements?
- A
Create an external stage on the object storage location, load the files into a transient raw staging table with COPY INTO, and use Snowflake load metadata/history to track processed files before transforming into curated tables.
- B
Create a temporary table as the raw staging layer, load files directly from the external stage with INSERT ... SELECT, and rely on Time Travel to recover original files when a replay is needed.
- C
Skip the raw staging table and load files directly from the external stage into curated tables, because Snowflake retains enough file-level history to reconstruct the original raw layer later.
- D
Create an internal named stage, copy partner files from cloud storage into the internal stage, and then delete the original cloud files immediately so the internal stage becomes the system of record.
Show answer and explanation
Correct answer: A
Explanation
For a robust Snowflake ingestion architecture, a common best practice is to separate the external landing zone, raw staging layer, and curated layer. When files already arrive in cloud object storage, defining an external stage over that location allows Snowflake to read the files in place. Using COPY INTO to load a raw staging table is preferred for batch file ingestion because Snowflake maintains load metadata that helps avoid reloading the same files unintentionally and supports operational auditing through load history views and functions. A transient table is often appropriate for raw staging when the data can be recreated from source files and long-term Fail-safe storage costs are unnecessary. By contrast, temporary tables are too short-lived for shared ingestion pipelines, and direct loading into curated tables weakens replay and troubleshooting capabilities. Relevant Snowflake guidance includes documentation on stages, COPY INTO
, load history/metadata, and the behavior of temporary versus transient tables.- A. Correct.
Correct. An external stage is appropriate when source files already land in cloud object storage. Loading into a transient raw staging table is a common architectural choice for a landing/raw layer because it avoids unnecessary Fail-safe costs while still supporting short-term recovery through Time Travel. Using COPY INTO is important because Snowflake tracks load metadata for staged files, helping prevent duplicate loads under normal operations and providing an audit trail of loaded files. Keeping the original files in object storage preserves replay capability, while the raw staging table supports downstream transformations into curated tables.
- B. Incorrect.
Incorrect. A temporary table is session-scoped and is not suitable for a persistent raw staging layer shared across scheduled ingestion processes. Also, INSERT ... SELECT from staged files does not provide the same file load tracking behavior as COPY INTO for bulk ingestion. Finally, Time Travel applies to Snowflake tables and schema objects, not to the original source files in external storage, so it cannot be relied on to recover the original files for replay.
- C. Incorrect.
Incorrect. Loading directly into curated tables removes the durable raw landing layer that is commonly needed for replay, troubleshooting, and auditability. Although Snowflake records load history for COPY operations, that metadata is not a substitute for preserving raw source data. Reconstructing the original raw layer later may be difficult or impossible if source files change, are removed, or if transformations lose fidelity.
- D. Incorrect.
Incorrect. An internal named stage can be useful in some patterns, but this option conflicts with the requirement to preserve the original file contents for replay in the source object storage landing area. Deleting the original cloud files immediately increases operational risk and makes the internal stage the only retained copy. Internal stages are not generally intended to replace the external landing zone as the authoritative source-of-truth archive in this type of ingestion design.