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
In a nutshell
  • INFER_SCHEMA plus CREATE TABLE ... USING TEMPLATE builds tables from files; CSV needs PARSE_HEADER = TRUE.
  • ENABLE_SCHEMA_EVOLUTION = TRUE, MATCH_BY_COLUMN_NAME and the EVOLVE SCHEMA privilege (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.

Staged filesINFER SCHEMAColumn listnames and typesUSING TEMPLATENew tableload withCOPY or pipeENABLE SCHEMA EVOLUTIONaddsNew columns

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 NULL constraints 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 with PARSE_HEADER also need ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE.
  • The role needs the EVOLVE SCHEMA privilege 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.

Exam tip

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 changeSafe response
New column addedSchema evolution, or MATCH_BY_COLUMN_NAME and an ALTER TABLE ... ADD COLUMN
Column removed or renamedUpdate the mapping or COPY transformation; schema evolution cannot rename
Field order changes in CSVPositional mapping breaks; use files with headers and name mapping
Column count differsERROR_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 contentRejected by load metadata; send a new name or truncate and reload
Encoding or delimiter changeUpdate 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.
Old pathCOPY jobstop and note last fileCutoverone owner for each filestartNew pathSnowpipe or streamingsameRaw tableunchanged downstream

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_SCHEMA plus CREATE TABLE ... USING TEMPLATE builds tables from files; CSV needs PARSE_HEADER = TRUE.
  • ENABLE_SCHEMA_EVOLUTION = TRUE, MATCH_BY_COLUMN_NAME and the EVOLVE SCHEMA privilege (or ownership) work together.
  • Evolution adds columns and drops NOT NULL; it does not rename, drop columns or change types.
  • A raw VARIANT landing 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 the EVOLVE SCHEMA privilege or ownership.

  • How do you create a table from Parquet files without typing the DDL?

    Use CREATE TABLE ... USING TEMPLATE over INFER_SCHEMA on 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.