02. Handling Schema Drift with CSV
🔹 Handling Schema Drift with CSV
CSV is one of the most common file formats, but it is also one of the most rigid. Snowflake provides two main ways to handle CSV schema drift depending on whether your file has a header row.
For the following examples, assume we are loading data from a Snowflake Internal Stage:
-- Setup: Creating an internal stage
CREATE OR REPLACE STAGE my_internal_stage;
1️⃣ CSV Drift With Headers (Automatic Schema Evolution)
When your CSV files have headers, Snowflake can automatically adapt your target table's schema when new columns appear in the CSV.
To use automatic schema evolution with CSV files, three things are required:
- The table must be created (or altered) with
ENABLE_SCHEMA_EVOLUTION = TRUE. - The file format must have
PARSE_HEADER = TRUE. - The
COPY INTOstatement must useMATCH_BY_COLUMN_NAME = CASE_INSENSITIVE.
The Problem: Data Loss Without Schema Evolution
If you do not enable schema evolution, Snowflake uses the headers to map the data, but it will ignore any new columns that don't exist in the table.
-- 1. Create target table (Schema Evolution is FALSE by default)
CREATE OR REPLACE TABLE customers_csv_static (
id INT,
first_name STRING
);
-- 2. Create file format
CREATE OR REPLACE FILE FORMAT csv_header_format
TYPE = CSV
PARSE_HEADER = TRUE;
-- 3. Load data
COPY INTO customers_csv_static
FROM @my_internal_stage/csv_files/
FILE_FORMAT = (FORMAT_NAME = csv_header_format)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
The Data Loss Issue: If the CSV contains a new email column, the COPY INTO command reports a "successful" load, but the email data is completely discarded. From a business perspective, this is a silent failure because you are losing valuable data.
The True Solution: Capturing the Columns WITH Schema Evolution
To achieve a truly successful load where all new data is captured, you must enable schema evolution.
-- 1. Create target table WITH Schema Evolution
CREATE OR REPLACE TABLE customers_csv_evolve (
id INT,
first_name STRING
) ENABLE_SCHEMA_EVOLUTION = TRUE;
-- (Note: The role executing the COPY must have the EVOLVE SCHEMA privilege)
-- 2. Load data
COPY INTO customers_csv_evolve
FROM @my_internal_stage/csv_files/
FILE_FORMAT = (FORMAT_NAME = csv_header_format)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
True Success: When the CSV contains a new email column, Snowflake automatically runs an ALTER TABLE to add the email column to customers_csv_evolve. Every piece of data is successfully captured and nothing is discarded.
2️⃣ CSV Drift Without Headers (Enterprise Architecture)
If your CSV files do not have headers, automatic schema evolution cannot add new columns because Snowflake has no way to know what to name the new columns. It must rely strictly on positional order (Column 1, Column 2, etc.).
If an upstream system adds a new CITY column to the end of a header-less CSV, a standard COPY INTO command will fail because the number of columns in the file (3) no longer matches the target table (2).
Because Snowflake only sees Column1, Column2, and Column3, it cannot invent a meaningful column name like CITY.
The Anti-Pattern: Ignoring the Mismatch
Technically, you could set ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE to force the load to succeed. However, this is an anti-pattern in enterprise data engineering. If you do this, Snowflake simply ignores the new column, resulting in silent data loss. Even worse, if the upstream system inserted the new column in the middle of the file, your data would load into the completely wrong columns.
[!WARNING] Never blindly load unknown columns or ignore missing headers. We cannot ignore header-less CSV drift. We must detect it, stop the pipeline, and handle it gracefully.
The Production Solution: Validation & Alerting
This is a real production architecture problem. In enterprise data engineering, you should not try to "guess" the schema. Instead, you detect the drift, stop the load, alert the team, and proceed only after validation.
Best Practice Architecture Flow:
Source System
│
▼
Header-less CSV
│
▼
Landing Stage
│
▼
Validation Framework
(Count Columns, Validate Size, etc.)
│
┌────────────┴────────────┐
│ │
Valid Invalid
│ │
▼ ▼
COPY INTO Reject File
│ │
▼ ▼
Target Table Alert Service
(Email/Slack)
Audit Table
Common Implementation Patterns
- Metadata Control Tables: Maintain a metadata table tracking the
EXPECTED_COLUMNSfor each source file. Before executing theCOPY INTO, read the first record of the staged file, count the columns, and compare it to the metadata. If there is a mismatch, reject the file. - Python/Airflow Validation: Use an orchestration tool like Python or Airflow to read the first row (
len(row)) of the incoming CSV and compare it to expected counts before ever calling Snowflake. - Schema Registries: Enterprises maintain versions of file structures. If an incoming file doesn't match an approved version in the registry, the pipeline halts and alerts the team to update the metadata.
💡 Interview Answer
Question: How do you handle schema drift for header-less CSV files?
A strong, enterprise-grade answer is:
"Snowflake cannot automatically evolve the schema for header-less CSV files because there are no column names to map. In our ingestion framework, we first validate the file structure before executing
COPY INTO. We compare the incoming column count against metadata stored in a control table. If the counts don't match, we reject the file, log the error to an audit table, and notify the upstream application team. Once the source schema is approved and the metadata is updated, we alter the target table if required and rerun the load. This prevents silent data corruption and ensures governance over schema changes."
This approach is what you will find in most enterprise data platforms because failing fast with a clear alert is far safer than loading data into the wrong columns or silently discarding data.