?? Dynamic Schema Evolution in Snowflake – A Practical Example

In today's fast-paced data environments, schemas change frequently, especially when working with files like CSVs. Snowflake makes handling these changes seamless with INFER_SCHEMA and USING TEMPLATE.

Recently, I implemented a solution to dynamically ingest a CSV file with an evolving schema:

Here’s the Example:

-- Step 1: Define a file format for CSV  
CREATE OR REPLACE FILE FORMAT CSV_FORMAT  
  TYPE = CSV  
  FIELD_DELIMITER = ','  
  NULL_IF = ('NULL', 'null')  
  EMPTY_FIELD_AS_NULL = TRUE  
  PARSE_HEADER = TRUE  
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;  

-- Step 2: Create a table dynamically using INFER_SCHEMA  
CREATE OR REPLACE TABLE ca_test  
USING TEMPLATE (  
    SELECT ARRAY_AGG(object_construct(*))  
    FROM TABLE (  
        INFER_SCHEMA(  
            LOCATION => '@my_stage_python/ca.csv',  
            FILE_FORMAT => 'CSV_FORMAT'  
        )  
    )  
);  

-- Step 3: Query the dynamically created table  
SELECT * FROM ca_test;          

What’s happening here?

1?? INFER_SCHEMA dynamically inspects the CSV file located in the Snowflake stage and infers its schema on the fly.

2?? Using ARRAY_AGG and object_construct(*), the schema details are aggregated into a template for table creation.

3?? The table ca_test is created dynamically—no manual column definition required!

Why is this powerful?

? Handles evolving schemas: If columns change, are added, or removed, Snowflake adapts automatically

? No column mismatch errors: Flexible ingestion with ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE

With Snowflake's dynamic schema capabilities, data ingestion becomes smarter and more resilient—allowing you to focus on insights, not manual fixes. ?