ARA-C01 Question 348
Single answerStaging layers and tablesA retail company ingests hourly CSV files from multiple suppliers into Snowflake. File quality varies, and downstream consumers require both fast availability of valid rows and the ability to investigate rejected records later. The architecture team wants to minimize reprocessing effort, preserve the original file contents for audit, and separate ingestion concerns from business transformations. Which design is the MOST appropriate?
- A
Load each file directly from the external stage into dimensional target tables using COPY INTO with ON_ERROR=CONTINUE, and rely on the COPY history to reconstruct rejected rows when needed.
- B
Create a raw landing table that stores the ingested file contents with metadata such as source filename and load timestamp, then load validated data into a separate staging/transformation layer before populating curated tables.
- C
Skip Snowflake stages and have suppliers insert directly into transient staging tables so invalid rows can be corrected in place before loading to production tables.
- D
Use a single permanent staging table for both raw ingestion and transformed data, truncating it after each successful load cycle to reduce storage costs.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to implement distinct ingestion layers: stage files, persist raw data in a landing table with load metadata, then transform and validate in a separate staging layer before loading curated targets. This approach supports common architectural goals in Snowflake: replayability, auditability, data quality handling, and separation between ingestion and business modeling. Snowflake stages are designed to manage data loading from internal or external file locations, and COPY INTO is typically used to load from stages into tables. Best practice is to avoid loading directly into final dimensional structures when source quality is variable, because doing so complicates data quality management and lineage. Using raw/landing tables also helps teams retain source fidelity while allowing downstream staging tables to enforce typing, deduplication, and validation rules. Relevant Snowflake documentation includes guidance on data loading with stages and COPY INTO, use of load metadata such as METADATA$FILENAME for staged files, and architectural patterns that separate raw, staging, and curated layers for robust ingestion pipelines.
- A. Incorrect.
Incorrect. Loading directly into dimensional target tables mixes ingestion with business-serving structures and makes recovery and auditing harder. Although COPY INTO supports ON_ERROR handling and Snowflake retains load history, COPY history is not a substitute for preserving original file contents or rejected record details for later analysis. This design also increases reprocessing complexity because you may need to reconstruct source data rather than reload from a controlled raw layer.
- B. Correct.
Correct. A layered design with a raw landing table and a separate staging/transformation layer aligns with Snowflake best practices for decoupling ingestion from downstream modeling. Storing raw ingested data along with metadata such as filename, row number when available, and load timestamp supports auditability, replay, and investigation of bad records. A subsequent staging layer can standardize types, apply validation rules, and isolate business transformations before loading curated tables, minimizing reprocessing effort and improving operational resilience.
- C. Incorrect.
Incorrect. Snowflake stages are a core mechanism for loading files into Snowflake and should not be bypassed in this scenario. Having external suppliers insert directly into tables weakens control over ingestion patterns, increases security and governance complexity, and does not preserve the original file artifact in the same way a stage plus raw landing process does. Correcting invalid rows in place also compromises lineage because the original incoming record is altered.
- D. Incorrect.
Incorrect. Combining raw and transformed data in one table undermines clear separation of concerns and makes lineage, troubleshooting, and replay more difficult. Truncating the table after each cycle removes the audit trail and original source state, which conflicts with the requirement to investigate rejected records later. While storage optimization matters, Snowflake storage is generally cheaper than the operational cost of losing traceability and reload capability.