ARA-C01 Question 176
Single answerELT/ETLA retail company is modernizing its nightly ETL pipeline into Snowflake. Raw order files land every 15 minutes in cloud object storage as compressed CSV files. The business wants a near-real-time ELT design that minimizes operational overhead, preserves all raw data for reprocessing, and loads only newly arrived files into a curated ORDERS table after basic standardization. Which design best meets these requirements?
- A
Create an external stage on the cloud storage location, use Snowpipe to continuously load files into a raw landing table, retain the raw table for replay/reprocessing, and use Streams and Tasks to transform new rows into the curated ORDERS table.
- B
Use a scheduled client-side script to run PUT commands every 15 minutes into an internal stage, followed by COPY INTO the curated ORDERS table, because Snowpipe requires internal stages and cannot load from cloud storage directly.
- C
Load files directly from cloud storage into the curated ORDERS table with Snowpipe, and rely on Time Travel instead of keeping a raw landing table because Snowpipe already stores the original file contents for reprocessing.
- D
Use a materialized view on top of the external stage files to standardize the CSV data in place, then use a Task to insert changed rows into the curated ORDERS table every 15 minutes.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it combines Snowflake-native ingestion and transformation services into a practical ELT architecture. Snowpipe is designed for continuous data loading from staged files, including files in external stages that reference cloud storage. A raw landing table is a common architectural pattern because it preserves source data after ingestion, supports replay/reprocessing, and decouples ingestion from downstream transformation logic. Streams provide change data capture on Snowflake tables by tracking inserts, updates, and deletes since the last consumption point, and Tasks orchestrate SQL-based transformations on a schedule or dependency chain.
Key best-practice ideas reflected in this question:
- Use Snowpipe for automated, near-real-time file ingestion from cloud storage.
- Keep a raw landing table for traceability, auditing, and reprocessing rather than loading only curated tables.
- Use Streams and Tasks to implement incremental ELT inside Snowflake with minimal external orchestration.
- Do not confuse Time Travel with long-term raw data retention; Time Travel is for table history within retention limits, not a replacement for a raw ingestion layer.
- Do not assume Snowpipe requires internal stages; it supports external stages as well.
These patterns are consistent with Snowflake documentation on data loading with Snowpipe, staged data concepts, and incremental processing with Streams and Tasks.
- A. Correct.
Correct. This is a standard Snowflake ELT pattern for near-real-time ingestion with low operational overhead. An external stage can point directly to cloud object storage, and Snowpipe can continuously ingest newly arrived files from that stage into a raw landing table. Keeping the raw table preserves ingested source data for auditability and reprocessing. Streams can track row-level changes in the landing table, and Tasks can schedule downstream SQL transformations into the curated ORDERS table. This aligns with Snowflake best practices for separating ingestion from transformation and using Snowflake-native automation for ELT.
- B. Incorrect.
Incorrect. PUT is used to upload local files into an internal stage and is typically executed from SnowSQL, drivers, or connectors, which increases operational overhead compared with native continuous ingestion from cloud storage. Also, Snowpipe does not require internal stages; it supports loading from both internal and external stages. This option also skips the raw landing layer, which reduces reprocessing flexibility.
- C. Incorrect.
Incorrect. Although Snowpipe can load directly into a target table, relying only on the curated table does not satisfy the requirement to preserve all raw data for reprocessing in a practical ELT design. Time Travel retains historical table states only for the configured retention period and is not a substitute for a persistent raw landing layer containing ingested source records. In addition, Snowpipe does not serve as long-term storage for original file contents for replay purposes.
- D. Incorrect.
Incorrect. Materialized views are created on tables, including external tables in supported scenarios, not directly on raw staged files in an external stage. To query files in place, Snowflake uses external tables, but that approach is not the best fit here for low-overhead continuous ingestion into Snowflake-managed tables. The requirement is to load only newly arrived files and preserve raw ingested data for downstream ELT, which is better addressed with Snowpipe plus a landing table, then Streams and Tasks.