ARA-C01 Question 296
Single answerData source changesA retail company ingests order data from an operational PostgreSQL system into Snowflake using a nightly COPY INTO process from staged CSV files. The source team announces two upcoming changes: a new nullable column DISCOUNT_CODE will be added next week, and three months later the data type of ORDER_TOTAL will change from INTEGER cents to NUMBER(12,2) dollars. The analytics team needs to minimize pipeline disruption, preserve historical loads, and avoid silent data corruption. Which approach should the architect recommend?
- A
Load files directly into the existing target table and rely on MATCH_BY_COLUMN_NAME so Snowflake automatically handles both the new column and the future data type change without any pipeline changes.
- B
Introduce a raw landing table with permissive column types, load source files there first, and use a transformation step into the curated target table where the new column can be added safely and the ORDER_TOTAL conversion can be explicitly versioned and validated.
- C
Enable schema evolution on the existing target table so Snowflake adds DISCOUNT_CODE and automatically converts ORDER_TOTAL from integer cents to decimal dollars during COPY INTO.
- D
Keep the current target table unchanged and create a view that derives DISCOUNT_CODE as NULL and divides ORDER_TOTAL by 100 once the source type changes, because views are the safest way to absorb source schema changes.
- E
Replace the COPY INTO process with a dynamic table on top of the stage, because dynamic tables are designed to automatically adapt to source file schema and data type changes.
Show answer and explanation
Correct answer: B
Explanation
The best recommendation is to separate ingestion from business transformation by introducing a raw landing layer and then transforming into curated tables. This is especially important when source changes include both an additive schema change and a semantic change in data representation. Adding DISCOUNT_CODE is relatively straightforward, but changing ORDER_TOTAL from integer cents to decimal dollars alters the meaning of the data and should be handled explicitly with versioned transformation logic, data quality checks, and possibly parallel columns or controlled cutover logic. In Snowflake, features such as COPY INTO, MATCH_BY_COLUMN_NAME, and schema evolution can help with some structural ingestion changes, but they do not replace architectural controls for semantic changes. Best practice is to preserve raw source fidelity, then implement curated transformations with clear lineage and validation. This aligns with Snowflake guidance around layered data architecture, controlled schema evolution, and minimizing downstream impact from upstream source changes.
- A. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME can help align incoming file columns to target columns by name in supported loading scenarios, but it does not solve the broader architectural problem here. A newly added nullable column may still require target schema management, and a semantic source change from integer cents to decimal dollars should not be handled implicitly. Relying on automatic behavior risks mixing old and new meanings of ORDER_TOTAL and causing silent data corruption.
- B. Correct.
Correct. A raw landing layer is a common Snowflake architectural best practice for handling evolving source systems. By first loading data into a raw table with flexible definitions, the team can preserve the source record as delivered and isolate ingestion from downstream semantic changes. Then a controlled transformation step can add DISCOUNT_CODE when available and explicitly manage the ORDER_TOTAL conversion based on source version or load date. This minimizes disruption, protects historical data, and avoids silently changing metric meaning.
- C. Incorrect.
Incorrect. Snowflake schema evolution can help with certain additive column changes during loads under supported conditions, but it is not intended to automatically manage business-semantic data type changes such as converting integer cents to decimal dollars. That conversion requires explicit logic and validation. Treating this as simple schema evolution confuses structural evolution with semantic transformation.
- D. Incorrect.
Incorrect. A view can mask some downstream impacts, but keeping the base table unchanged while the source meaning changes is risky. Once ORDER_TOTAL changes from cents to dollars, a single expression like dividing by 100 would be wrong for new records unless the logic branches by source version or date. Also, views do not resolve ingestion robustness or raw data preservation requirements.
- E. Incorrect.
Incorrect. Dynamic tables materialize query results incrementally from upstream objects, but they are not a replacement for staged file ingestion or a mechanism that automatically adapts file schemas. They do not eliminate the need to design for source schema and semantic changes in the ingestion pipeline.