Study guide
8.5 Schema Detection, Schema Evolution & Source-Change Architecture
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
8.5 Schema Detection, Schema Evolution & Source-Change Architecture
Key Takeaways Explain This Topic AI
INFER_SCHEMA reads staged Parquet, Avro, ORC, JSON, or CSV files and returns column names, types, and nullability, and CREATE TABLE ... USING TEMPLATE turns that output into a table definition.
Schema evolution adds new columns and drops NOT NULL from columns missing in new files when the table has ENABLE_SCHEMA_EVOLUTION = TRUE, the COPY uses MATCH_BY_COLUMN_NAME, and the loading role has EVOLVE SCHEMA or OWNERSHIP on the table.
Schema evolution applies to COPY INTO <table> and Snowpipe (not INSERT); by default one COPY can add at most 100 columns or evolve one schema, and CSV evolution also needs PARSE_HEADER and ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE.
Snowpipe Streaming's high-performance architecture and the Kafka connector can also evolve table schemas, while VARIANT landing columns absorb source changes without any DDL.
Architects choose between strict schemas (fail fast on drift), automatic evolution (accept additive changes), and VARIANT landing plus typed downstream models (absorb any change, type later).
Last updated: September 2026
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.
Strategy Behavior on a new source column Best for
Strict schema Load fails or the column is ignored; engineers change DDL deliberately Regulated or contract-driven feeds
Automatic schema evolution Snowflake adds the column during the load Additive, trusted feeds from many producers
VARIANT landing Whole record lands in a VARIANT column; nothing breaks Highly variable JSON/event data; typing happens downstream
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:
The table has ENABLE_SCHEMA_EVOLUTION = TRUE (set at CREATE TABLE or with ALTER TABLE ).
The COPY INTO <table> uses MATCH_BY_COLUMN_NAME ( CASE_SENSITIVE or CASE_INSENSITIVE ).
The role running the load has EVOLVE SCHEMA or OWNERSHIP on the table.
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:
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.
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.
Consumption layer: secure views or curated tables keep column names and types stable, so BI tools and shares do not break.
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
- Source change What happens Architect's response
- New column Added automatically (if evolution is enabled) Decide whether downstream layers expose it
- Column missing in new files NOT NULL dropped; column kept Confirm with the source whether it is deprecated
Column renamed Treated as a new column; the old one stops receiving data Map old and new names in the transformation layer
Type changed (for example number to text) Not evolved; values that no longer convert cause load errors Land raw values (VARIANT or VARCHAR) or coordinate a planned DDL change
New file format or delimiter Not detected by evolution Version the file format object and route files by prefix
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 .