ARA-C01 Question 295
Single answerData source changesA retail company loads order data from an OLTP system into Snowflake every 5 minutes using a Snowpipe auto-ingest process. The source team plans a release that will add several nullable columns to the CSV files next week. They cannot guarantee that all upstream systems will be updated at the same time, so for several days Snowflake may receive a mix of files with the old column layout and files with the new layout. The analytics team wants to avoid load failures and preserve all available data with the least operational overhead. Which approach should the architect recommend?
- A
Configure the file format with ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE and load into a landing table using MATCH_BY_COLUMN_NAME after changing the ingestion to a columnar format such as Parquet.
- B
Keep the CSV files and rely on MATCH_BY_COLUMN_NAME in COPY INTO so Snowflake can align new columns by header name during Snowpipe loads.
- C
Create a second Snowpipe for the new file version and use a task to merge the two target tables after the release is complete.
- D
Suspend Snowpipe until all source systems are updated, then alter the target table and replay the files from cloud storage.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to recommend an ingestion design that tolerates source schema changes without relying on fragile positional parsing. In Snowflake, evolving sources are easier to manage when the data contract uses formats that support column-name-aware processing, such as Parquet, rather than CSV where column order and count changes are more problematic. This is especially important when old and new file shapes coexist during a phased release. Architects should favor stable landing patterns, schema evolution strategies, and low-touch operational models over temporary dual-pipeline workarounds or ingestion pauses. Relevant Snowflake documentation includes COPY INTO behavior, file format options such as ERROR_ON_COLUMN_COUNT_MISMATCH, Snowpipe ingestion patterns, and guidance around schema evolution and MATCH_BY_COLUMN_NAME support.
- A. Correct.
Correct. Snowflake supports schema evolution and column matching by name for supported use cases, but MATCH_BY_COLUMN_NAME is not intended to solve mixed-layout CSV ingestion the same way as self-describing or columnar formats. For CSV, position-based parsing creates operational risk when files with different column counts arrive during the transition. A practical architecture is to move the ingestion contract to a format such as Parquet and use column-name-based loading into a landing table. Setting ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE can also help tolerate row shape differences in delimited files, but the key architectural improvement is using a format and loading pattern that preserves columns by name rather than position. This minimizes failures and captures newly added nullable attributes with low ongoing overhead.
- B. Incorrect.
Incorrect. A common misconception is that MATCH_BY_COLUMN_NAME can be used with CSV headers to seamlessly handle evolving schemas during Snowpipe loads. In practice, CSV loading is fundamentally position-oriented, and MATCH_BY_COLUMN_NAME is not the right mechanism to safely absorb mixed old/new CSV layouts in this scenario. Relying on it for header-based alignment in CSV can lead to incorrect assumptions about how Snowflake parses incoming files.
- C. Incorrect.
Incorrect. This could work operationally, but it introduces more complexity than necessary: duplicate pipelines, branching logic, and post-load reconciliation. The requirement is to avoid load failures and preserve data with the least operational overhead. Running parallel ingestion paths for a temporary schema transition is usually not the best architectural recommendation unless format changes or source contracts cannot be improved.
- D. Incorrect.
Incorrect. Suspending ingestion prevents immediate load failures, but it increases latency and operational risk, and it does not meet the goal of preserving near-real-time ingestion during the transition. Although replaying files from cloud storage is possible if retention and event handling are managed correctly, pausing Snowpipe for a multi-day source rollout is generally a less resilient and less business-friendly design.