Data Engineering · Topic 6 of 16
Schema Detection, Schema Evolution and Handling Source Changes
Handle changing sources in Snowflake with INFER_SCHEMA, USING TEMPLATE, ENABLE_SCHEMA_EVOLUTION, MATCH_BY_COLUMN_NAME and architecture or source changes.
- 5 min
INFER_SCHEMAplusCREATE TABLE ... USING TEMPLATEbuilds tables from files; CSV needsPARSE_HEADER = TRUE.ENABLE_SCHEMA_EVOLUTION = TRUE,MATCH_BY_COLUMN_NAMEand theEVOLVE SCHEMAprivilege (or ownership) work together.- Evolution adds columns and drops
NOT NULL; it does not rename, drop columns or change types.
What it is
Sources change. A producer adds a column, renames a field, switches from CSV to Parquet, or the team moves from batch COPY to streaming. An Architect designs ingestion so these changes do not break loads or silently corrupt data.
Snowflake gives two tools for schema: schema detection designs a table from staged files, and schema evolution lets a table add columns as new files arrive.
How it works
Schema detection
INFER_SCHEMA is a table function that reads staged files and returns column names, types and nullability. It supports Parquet, Avro, ORC, CSV and JSON files. For CSV, the file format needs PARSE_HEADER = TRUE so header names become column names.
CREATE TABLE ... USING TEMPLATE builds the table from that result, so you do not write the DDL by hand.
CREATE FILE FORMAT pq TYPE = PARQUET;
CREATE TABLE orders
USING TEMPLATE (
SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(INFER_SCHEMA(
LOCATION => '@raw_stage/orders/',
FILE_FORMAT => 'pq'))
);
GENERATE_COLUMN_DESCRIPTION returns column definitions as text if you want to review or edit them first. Detection samples the files, so check types such as numeric precision and timestamps before using them in production.
Table schema evolution
With ENABLE_SCHEMA_EVOLUTION = TRUE on a table, a COPY or pipe can change the table while loading.
- New columns found in the files are added.
NOT NULLconstraints are dropped from columns that are missing in a file.- Loading must map columns by name, so use
MATCH_BY_COLUMN_NAME(or a pipe or Kafka connector that sets it). CSV files read withPARSE_HEADERalso needERROR_ON_COLUMN_COUNT_MISMATCH = FALSE. - The role needs the
EVOLVE SCHEMAprivilege on the table, or ownership of it.
ALTER TABLE orders SET ENABLE_SCHEMA_EVOLUTION = TRUE;
COPY INTO orders
FROM @raw_stage/orders/
FILE_FORMAT = (FORMAT_NAME = 'pq')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
Schema evolution does not remove columns or change an existing column's type. It supports COPY, Snowpipe auto ingest and the high performance Snowpipe Streaming architecture, with limited support through the Kafka connector on classic Snowpipe Streaming; INSERT statements and tasks do not evolve the schema. One COPY can add at most 100 columns by default. Changes are recorded in the SchemaEvolutionRecord field, visible in INFORMATION_SCHEMA.COLUMNS, DESCRIBE TABLE and SHOW COLUMNS.
Schema evolution needs two things together: the table property and name based column mapping. If a question mentions positional CSV loads, evolution is not the answer.
Data source changes
Plan for the changes you can predict and detect the rest.
| Source change | Safe response |
|---|---|
| New column added | Schema evolution, or MATCH_BY_COLUMN_NAME and an ALTER TABLE ... ADD COLUMN |
| Column removed or renamed | Update the mapping or COPY transformation; schema evolution cannot rename |
| Field order changes in CSV | Positional mapping breaks; use files with headers and name mapping |
| Column count differs | ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE for known cases |
| Format changes (CSV to Parquet) | New file format, new stage path or pipe; keep the old pipe for old files |
| Same file name re-sent with new content | Rejected by load metadata; send a new name or truncate and reload |
| Encoding or delimiter change | Update ENCODING, FIELD_DELIMITER or REPLACE_INVALID_CHARACTERS |
A resilient design lands semi-structured data in a raw VARIANT table first. New fields never break the load, and a second step (a view, a dynamic table or a task) projects the columns the business needs. Views with explicit column lists protect downstream users from added columns.
Architecture changes
Architecture changes are moves such as batch to streaming, one cloud or region to another, or an external stage to Iceberg tables. The design questions are the same each time.
- Co-locate storage and account. Loading from storage in a different region or cloud adds transfer cost and latency, so keep stages in the same region as the account where possible.
- Switch load paths cleanly. When moving from COPY to Snowpipe, make sure the new pipe and the old job do not both load the same files, because they keep separate load histories and would duplicate rows.
- Keep the raw layer stable. If the raw table and its format stay the same, downstream models survive a change in how data arrives.
- Re-check access. A new stage, region or account needs its own storage integration, notification wiring and grants.
Common mistakes
Enabling schema evolution and expecting renames to work. It adds columns; it never renames or drops them.
Using schema evolution with positional loads. It requires mapping by column name.
Trusting INFER_SCHEMA blindly. It samples files, so a column that is whole numbers in the sample may later hold decimals.
Running two loaders on one stage. A pipe and a COPY job each keep their own history and can load the same file twice.
What to remember for the exam
INFER_SCHEMAplusCREATE TABLE ... USING TEMPLATEbuilds tables from files; CSV needsPARSE_HEADER = TRUE.ENABLE_SCHEMA_EVOLUTION = TRUE,MATCH_BY_COLUMN_NAMEand theEVOLVE SCHEMAprivilege (or ownership) work together.- Evolution adds columns and drops
NOT NULL; it does not rename, drop columns or change types. - A raw
VARIANTlanding table absorbs source changes safely. - During migrations, keep one loader per file set and the stage near the account's region.
Check yourself
Which three things does table schema evolution need to work with COPY?
The table property
ENABLE_SCHEMA_EVOLUTION = TRUE, mapping by column name, and theEVOLVE SCHEMAprivilege or ownership.How do you create a table from Parquet files without typing the DDL?
Use
CREATE TABLE ... USING TEMPLATEoverINFER_SCHEMAon the staged files.A producer renames a field. Does schema evolution handle it?
No. It would add the new column but not rename the old one, so update the mapping.
Topic 6 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.