ARA-C01 Question 294
Single answerSchema detection and table schema evolutionA data engineering team ingests daily partner CSV files from an external stage into a Snowflake table named SALES_RAW. The partner occasionally adds new columns to the files, and the team wants those new columns to be added automatically to the target table during loads without manually issuing ALTER TABLE statements. Existing columns should continue to load by name even if the partner changes column order in the files. The architects also want to inspect the staged files first to understand the incoming schema before enabling the automated process. Which approach best meets these requirements?
- A
Use INFER_SCHEMA on the staged files to inspect the detected column definitions, create or update SALES_RAW accordingly, and load with COPY INTO using MATCH_BY_COLUMN_NAME and ENABLE_SCHEMA_EVOLUTION on the target table.
- B
Use INFER_SCHEMA on the staged files and then run COPY INTO with FORCE = TRUE; Snowflake will automatically add new columns to the target table when the source files contain extra fields.
- C
Create SALES_RAW as a temporary table with ENABLE_SCHEMA_EVOLUTION = TRUE and load with COPY INTO using positional mapping; schema evolution will handle both new columns and changes in source column order.
- D
Create a file format with ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE and load with COPY INTO; Snowflake will ignore column order differences and append any newly detected source columns to SALES_RAW.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to first inspect incoming staged files with INFER_SCHEMA and then configure the production load to support schema evolution properly. In Snowflake, schema detection is commonly done with INFER_SCHEMA to understand column names and inferred data types from staged files before creating or adjusting target objects. For automatic table schema evolution during ingestion, architects should enable schema evolution on the target table and use COPY INTO with MATCH_BY_COLUMN_NAME so columns are mapped by header name instead of file position. This is especially important for CSV or similar structured files where providers may reorder columns over time. Options mentioning FORCE = TRUE or ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE reflect common misconceptions: those settings affect reload behavior and parsing tolerance, not automatic DDL evolution of the target table. This aligns with Snowflake guidance for evolving structured file loads and using schema inference utilities to reduce manual administration.
- A. Correct.
Correct. INFER_SCHEMA can be used against staged files to inspect inferred column names and data types before finalizing the ingestion design. For automatic table schema evolution during COPY INTO, the target table must have schema evolution enabled, and COPY INTO must use MATCH_BY_COLUMN_NAME so incoming file columns map by name rather than position. This combination supports practical ingestion when files evolve by adding columns or reordering existing ones.
- B. Incorrect.
Incorrect. INFER_SCHEMA is appropriate for inspection, but FORCE = TRUE only reloads files that Snowflake would otherwise skip because they were already loaded. It does not trigger automatic schema changes. Automatic addition of new columns requires schema evolution support on the target table and a compatible load pattern such as COPY INTO with MATCH_BY_COLUMN_NAME.
- C. Incorrect.
Incorrect. Positional mapping does not satisfy the requirement to continue loading correctly when source column order changes. MATCH_BY_COLUMN_NAME is the relevant capability for handling reordered columns in files. Also, making the table temporary is unrelated to schema evolution and would usually be a poor architectural choice for persistent raw ingestion data.
- D. Incorrect.
Incorrect. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE is a file format setting that can help tolerate certain row-level column count inconsistencies, but it does not provide table schema evolution. It also does not ensure semantic mapping when columns are reordered; that requirement is addressed by loading with column-name matching rather than by this file format setting.