ARA-C01 Question 293
Single answerSchema detection and table schema evolutionA retail company ingests daily product catalog files from hundreds of suppliers into a Snowflake stage. The files are mostly CSV, but new suppliers occasionally introduce additional columns or omit optional ones. The architecture team wants to automate onboarding of these files so that new columns can be incorporated into the target table with minimal manual intervention, while preserving existing data loads. Which approach best meets this requirement?
- A
Create the target table with all columns defined as VARIANT, load the CSV files into a single VARIANT column, and rely on downstream views to expose relational columns when needed.
- B
Use schema inference on the staged files to generate the initial table definition, then load with COPY INTO using schema evolution enabled on the target table so newly detected source columns can be added automatically during supported loads.
- C
Enable automatic clustering on the target table and use MATCH_BY_COLUMN_NAME in COPY INTO so Snowflake can infer and add any new source columns during load without additional table settings.
- D
Use a stream on the stage and a task that runs ALTER TABLE ADD COLUMN before each load, because Snowflake does not support adding columns automatically during data loading.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to combine schema detection/inference for initial onboarding with table schema evolution for ongoing file changes. In Snowflake, schema inference can examine staged files and help derive a table structure, which is useful when supplier file formats are not fully known in advance. For continued ingestion, table schema evolution is designed to reduce manual maintenance by allowing supported loads to automatically add new columns when incoming files contain fields not yet present in the table. This is particularly valuable in multi-supplier ingestion patterns where columns may be added over time. MATCH_BY_COLUMN_NAME can be relevant for aligning source and target columns, but it is not sufficient on its own to evolve the schema. Similarly, automatic clustering has no role in schema management. Best practice is to use relational columns when the downstream analytics model expects them, and to use schema evolution judiciously with governance around supplier-driven schema changes. This aligns with Snowflake documentation on schema detection/inference and automatic schema evolution for tables during data loading.
- A. Incorrect.
This is not the best answer for the stated requirement. Loading everything into VARIANT can handle changing structures, but it avoids relational schema evolution rather than using it. The scenario specifically asks for incorporating new columns into the target table with minimal manual intervention. Using a single VARIANT column would shift complexity downstream and is more appropriate for semi-structured ingestion patterns than for evolving relational CSV loads.
- B. Correct.
This is correct. Snowflake supports schema detection/inference to help derive a table definition from staged files, and table schema evolution can automatically add new columns during supported data loads when properly configured. This approach aligns with the requirement to onboard files with changing columns while minimizing manual DDL changes. It preserves a relational target table and reduces operational overhead for supplier file changes.
- C. Incorrect.
This is incorrect because automatic clustering is unrelated to schema detection or schema evolution. MATCH_BY_COLUMN_NAME helps align source fields to target columns during loading, but by itself it does not enable automatic schema changes. Schema evolution requires the appropriate table-level capability/configuration and supported loading behavior; clustering does not contribute to column addition.
- D. Incorrect.
This is incorrect because it assumes Snowflake cannot add columns automatically during loading. Snowflake does support table schema evolution for supported scenarios, so a custom stream-plus-task workflow to issue ALTER TABLE statements before each load is unnecessary as the primary recommended approach here. A candidate might choose this option because it sounds operationally safe, but it adds avoidable complexity.