ARA-C01 Question 175
Select 2ELT/ETLA retail company is modernizing its nightly ETL process in Snowflake. Source files arrive continuously in cloud storage throughout the day, and the company wants curated reporting tables to reflect new data within 15 minutes of file arrival. The current process uses an external orchestration tool to load files into staging tables and then run a series of SQL transformations into dimensional tables, but operations are complex and failures are hard to troubleshoot. The architect wants to simplify the pipeline using Snowflake-native ELT capabilities while preserving data lineage and minimizing custom code. Which TWO design choices best meet these requirements?
- A
Use Snowpipe to continuously load raw files into staging tables, then use streams on the staging tables and triggered tasks to transform only newly arrived rows into curated tables.
- B
Replace the current process with materialized views on the external stage so Snowflake automatically transforms files into dimensional tables as new objects arrive.
- C
Load source data into raw tables first, retain the raw layer, and apply SQL-based transformations inside Snowflake to populate curated dimensional models.
- D
Use a single large nightly COPY INTO command followed by a stored procedure that truncates and reloads all target tables to ensure consistency.
- E
Skip the raw ingestion layer and load files directly into dimensional tables with Snowpipe to reduce latency and simplify lineage.
Show answer and explanation
Correct answers: A, C
Explanation
The best answer is to combine Snowflake-native continuous ingestion with in-platform incremental transformation. Snowpipe supports automated file loading from cloud storage, which fits the requirement for data freshness within minutes of arrival. Streams capture CDC-style changes at the table level, and tasks can orchestrate SQL transformations without relying on an external scheduler for every step. This is a common Snowflake ELT pattern because it keeps ingestion and transformation inside Snowflake, reduces custom orchestration code, and improves operational visibility.
Retaining a raw layer is also a Snowflake architectural best practice for ELT pipelines. It preserves source data for lineage, replay, debugging, and downstream model changes. In contrast, directly loading dimensional tables or relying on nightly full reloads sacrifices resilience and does not meet the stated SLA.
Relevant Snowflake documentation and best-practice areas include Snowpipe for continuous data ingestion, Streams and Tasks for change-driven orchestration, and Snowflake architectural guidance favoring ELT patterns that load raw data first and transform within Snowflake.
- A. Correct.
Correct. Snowpipe is designed for continuous file ingestion from cloud storage into Snowflake tables with low operational overhead. Pairing Snowpipe with streams and triggered tasks is a strong Snowflake-native ELT pattern: streams track table changes, and tasks can execute downstream SQL transformations based on new data rather than rescanning everything. This reduces complexity, supports near-real-time processing, and improves troubleshooting because ingestion and transformation stages are separated and observable.
- B. Incorrect.
Incorrect. Materialized views cannot be defined directly on an external stage to ingest and transform files into dimensional tables. External stages reference files, but files must first be loaded or queried through supported mechanisms such as COPY INTO or external tables. Also, materialized views are not a general-purpose orchestration or file ingestion mechanism for dimensional ELT pipelines.
- C. Correct.
Correct. This reflects a core ELT best practice in Snowflake: ingest first into a raw or landing layer, preserve source fidelity, and perform transformations inside Snowflake using SQL. Keeping the raw layer improves lineage, auditability, replay/reprocessing capability, and troubleshooting. It also aligns with Snowflake's architecture, where compute can scale independently for transformation workloads.
- D. Incorrect.
Incorrect. A nightly batch process with full truncation and reload does not satisfy the requirement to reflect new data within 15 minutes of file arrival. It also increases compute usage unnecessarily by reprocessing unchanged data and makes failure recovery more disruptive. While full reloads can be appropriate in some cases, they are not the best fit for this near-real-time ELT requirement.
- E. Incorrect.
Incorrect. Loading directly into dimensional tables may reduce one step, but it weakens lineage, makes reprocessing difficult, and couples ingestion tightly to business transformations. Raw source preservation is generally recommended for operational resilience and auditability. Direct loading into dimensional models is usually not the best architecture when the goal is simplification, observability, and maintainability.