8.5 Schema Detection, Schema Evolution & Source-Change Architecture

Why Source Changes Are an Architecture Problem

The ARA-C01 blueprint (objective 3.1) asks architects to design loading solutions that handle architecture changes: schema detection and table schema evolution and data source changes. In practice this means deciding what should happen when tomorrow's files contain a column that today's table does not have.

Schema Detection with INFER_SCHEMA

INFER_SCHEMA is a table function that reads staged files and returns the detected column definitions: COLUMN_NAME, TYPE, NULLABLE, EXPRESSION, FILENAMES, and ORDER_ID. It supports Apache Parquet, Apache Avro, ORC, JSON, and CSV files.

CREATE FILE FORMAT raw.formats.parquet_ff TYPE = PARQUET;

-- Inspect what Snowflake detects in the staged files
SELECT *
FROM TABLE(INFER_SCHEMA(
  LOCATION => '@raw.stages.orders_stage/2026/09/',
  FILE_FORMAT => 'raw.formats.parquet_ff',
  MAX_FILE_COUNT => 10));

-- Create a table whose columns come straight from the detected schema
CREATE TABLE raw.orders
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(
      LOCATION => '@raw.stages.orders_stage/2026/09/',
      FILE_FORMAT => 'raw.formats.parquet_ff')));

Useful details:

• USING TEMPLATE also works with CREATE EXTERNAL TABLE and CREATE ICEBERG TABLE.
• For CSV, the file format needs PARSE_HEADER = TRUE so column names come from the header row.
• GENERATE_COLUMN_DESCRIPTION turns INFER_SCHEMA output into a column list you can paste into DDL.
• MAX_FILE_COUNT and MAX_RECORDS_PER_FILE limit how much data is scanned, which matters for very large prefixes.

Table Schema Evolution

Schema evolution lets a load change the target table automatically:

• Adds new columns found in the incoming files.
• Drops the NOT NULL constraint from columns that are missing in new data files.

All of these must be true for a file load to evolve the table:

1. The table has ENABLE_SCHEMA_EVOLUTION = TRUE (set at CREATE TABLE or with ALTER TABLE).
2. The COPY INTO <table> uses MATCH_BY_COLUMN_NAME (CASE_SENSITIVE or CASE_INSENSITIVE).
3. The role running the load has EVOLVE SCHEMA or OWNERSHIP on the table.
4. For CSV, the file format uses PARSE_HEADER = TRUE and ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE.

ALTER TABLE raw.orders SET ENABLE_SCHEMA_EVOLUTION = TRUE;
GRANT EVOLVE SCHEMA ON TABLE raw.orders TO ROLE loader_role;

COPY INTO raw.orders
  FROM @raw.stages.orders_stage
  FILE_FORMAT = (FORMAT_NAME = 'raw.formats.parquet_ff')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Limits and scope:

• Evolution applies to COPY INTO <table> and Snowpipe loads; INSERT statements never evolve a schema.
• By default a single COPY can add at most 100 columns or evolve one schema; Snowflake Support can raise this.
• Snowpipe Streaming (high-performance architecture) supports schema evolution for standard tables and Snowflake-managed Iceberg tables (adding top-level columns), and the Kafka connector with Snowpipe Streaming Classic supports schema detection and evolution.
• Evolution is additive: it does not rename columns, change types, or drop columns. Renames and type changes still require planned DDL.

Designing for Data Source Changes

A robust layered design absorbs change at the edge and exposes stable contracts to consumers:

1. Landing layer: either evolve automatically (typed columns) or land raw records in a VARIANT column plus metadata (METADATA$FILENAME, load timestamp). VARIANT landing never fails on new attributes.
2. Transformation layer: dynamic tables, streams and tasks, or dbt models project the fields you actually support, with explicit casts. New source fields do not reach consumers until someone models them.
3. Consumption layer: secure views or curated tables keep column names and types stable, so BI tools and shares do not break.
4. Monitoring: compare INFER_SCHEMA output with the table's columns in a scheduled task or alert, and review LOAD_HISTORY/COPY_HISTORY errors after source releases.

Exam Trap: Enabling ENABLE_SCHEMA_EVOLUTION alone does nothing if the COPY statement maps columns by position. Without MATCH_BY_COLUMN_NAME, the load cannot evolve the table.

Worked Example: Evolving a CSV Partner Feed

A partner sends daily CSV files with a header row. In March they add a loyalty_tier column, and in June a middle_name column is dropped from their export.

CREATE FILE FORMAT raw.formats.partner_csv
  TYPE = CSV
  PARSE_HEADER = TRUE                     -- read column names from the header row
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE; -- required for CSV schema evolution

CREATE TABLE raw.partner_customers
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(LOCATION => '@raw.stages.partner/2026/01/',
                            FILE_FORMAT => 'raw.formats.partner_csv')))
  ENABLE_SCHEMA_EVOLUTION = TRUE;

COPY INTO raw.partner_customers
  FROM @raw.stages.partner
  FILE_FORMAT = (FORMAT_NAME = 'raw.formats.partner_csv')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

What happens:

• March: the load adds LOYALTY_TIER to the table automatically; earlier rows hold NULL in it.
• June: files no longer contain MIDDLE_NAME. If that column was NOT NULL, schema evolution drops the NOT NULL constraint so loads keep working; the column stays in the table with NULLs for new rows.
• The load history (COPY_HISTORY, LOAD_HISTORY) and the table's DDL show the change, so downstream owners can decide whether to model the new field.

Changes Schema Evolution Does Not Handle

For external tables and Iceberg tables, USING TEMPLATE can create the initial definition from INFER_SCHEMA, but later changes are handled by refreshing or altering those tables (and, for Iceberg, by the table format's own schema evolution) rather than by ENABLE_SCHEMA_EVOLUTION.