Skip to main content

03. Handling Schema Drift with JSON

🔹 Handling Schema Drift with JSON​

JSON is a semi-structured format, which makes it incredibly resilient to schema drift. Snowflake offers two primary ways to handle evolving JSON payloads.

Method A: Using a VARIANT Column (Schema-on-Read)​

The traditional approach to handling JSON drift in Snowflake is to load the entire JSON document into a single VARIANT column. Since VARIANT enforces schema-on-read, any new fields added to the JSON payload are simply absorbed without breaking the pipeline.

-- 1. Create a table with a single VARIANT column
CREATE OR REPLACE TABLE raw_events (
event_data VARIANT
);

-- 2. Load JSON data directly into the VARIANT column
COPY INTO raw_events
FROM @my_internal_stage/json_events/
FILE_FORMAT = (TYPE = JSON);

Even if the upstream application completely changes the JSON structure, the COPY INTO statement will never fail. You handle the structural changes later using SQL views to parse the VARIANT column.

Method B: Automatic Table Schema Evolution​

Snowflake offers a native feature called Table Schema Evolution. When enabled, Snowflake can automatically run an ALTER TABLE to add new columns to your target table if it discovers them in the incoming JSON data.

-- 1. Create target table and enable schema evolution
CREATE OR REPLACE TABLE customers_json (
id INT,
first_name STRING
) ENABLE_SCHEMA_EVOLUTION = TRUE;

(Note: The role executing the load must have the EVOLVE SCHEMA privilege on the table).

-- 2. Load the JSON data
COPY INTO customers_json
FROM @my_internal_stage/json_customers/
FILE_FORMAT = (TYPE = JSON)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

What happens automatically?

  1. Snowflake reads the incoming JSON payload.
  2. It recognizes that new fields (e.g., last_name, email) exist in the JSON but not in the customers_json table.
  3. Because ENABLE_SCHEMA_EVOLUTION = TRUE, Snowflake automatically adds those columns to the table with the appropriate data types.
  4. The data is successfully loaded into the newly evolved schema.