My Incremental dbt Model Silently Dropped a Column -Here’s How I Caught It and Fixed It Without a Full Refresh

A $0 schema change that could have cost hours of debugging and wrong dashboards and the production pattern that prevents it.

The Setup

In the previous phase of my FinSight Analytics project, I had a clean three-layer ELT pipeline running on dbt and Snowflake raw banking transactions flowing through staging models into finance KPI marts. 28 tests passing. Lineage graph looking beautiful.

Then I tried something that every data engineer eventually has to deal with in production: I changed the schema of the raw table.

What happened next surprised me and taught me one of the most important lessons in building reliable data pipelines.

What Are Incremental Models and Why Do They Exist?

Before I get to the problem, let me explain why incremental models exist in the first place.

Every time dbt run executes against a standard model, it rebuilds the entire table from scratch. On a small dataset that's fine. In production with 100 million rows, that means:

Full refresh on 100M rows → 45 minutes
                          → significant Snowflake compute cost
                          → downstream marts also need rebuilding
                          → potential SLA breach

The reality is that in most data pipelines, 99% of your data doesn’t change between runs. You’re processing yesterday’s transactions, not reprocessing every transaction since the beginning of time.

Incremental models solve this:

Run 1 → process all rows         (first time, full load)
Run 2 → process only NEW rows    (incremental, fraction of data)
Run 3 → process only NEW rows    (incremental, fraction of data)

The result in our pipeline was immediately visible:

Full load:        16 rows processed in 2.82 seconds
Incremental run:  3 new rows processed in 4.14 seconds

At scale, that difference becomes hours vs seconds.

Terminal showing first dbt run SUCCESS 16 rows

Terminal showing second run SUCCESS 3 rows

How I Converted stg_transactions to Incremental

The conversion from a standard model to incremental required three additions to stg_transactions.sql:

{{
    config(
        materialized='incremental',
        unique_key='transaction_id',
        on_schema_change='sync_all_columns'
    )
}}

with source as (

    select * from {{ source('raw', 'transactions') }}

    {% if is_incremental() %}
        where transaction_date > (
            select max(transaction_date)
            from {{ this }}
        )
    {% endif %}

),

Let me break down each piece:

**materialized='incremental'** — tells dbt to build this as a persistent table and only append/merge new rows on subsequent runs.

**unique_key='transaction_id'** — if a transaction ID already exists in the table, UPDATE it instead of inserting a duplicate. This is how dbt handles late corrections to existing records.

**{% if is_incremental() %}** — this is dbt's Jinja templating. is_incremental() returns True if the table already exists. On the first run it processes everything. Every run after that, it only processes rows newer than what's already in the table.

**{{ this }}** — refers to the model's own table in Snowflake. The filter where transaction_date > (select max(transaction_date) from {{ this }}) is how dbt knows the watermark — the last point it already processed.

Under the hood, dbt translates this into a MERGE statement in Snowflake — not a simple INSERT. It checks each incoming row against the existing table, updates matches, inserts new ones. That’s why there’s slight overhead per row compared to a bulk INSERT, but the savings from processing a fraction of total data completely outweigh that overhead at scale.

The Silent Failure That Changed Everything

With the incremental model running cleanly, I simulated something that happens constantly in real production environments: an upstream schema change.

A payment API starts sending a new field. The raw table gets a new column:

ALTER TABLE finsight_db.raw.transactions
ADD COLUMN payment_method VARCHAR(50);

-- New transactions now arrive with payment_method populated
INSERT INTO finsight_db.raw.transactions VALUES
('T027', 'C001', 'M002', 22.50, '2024-03-12', 'debit', 'completed', 'credit_card'),
('T028', 'C002', 'M001', 88.50, '2024-03-16', 'debit', 'completed', 'debit_card');

I ran dbt run --select stg_transactions without changing anything in the model.

The result:

1 of 1 OK created sql incremental model staging.stg_transactions [SUCCESS 2 in 4.44s]
Completed successfully

SUCCESS. Zero errors. Zero warnings.

I went to Snowflake to check the staging table:

SELECT column_name
FROM finsight_db.information_schema.columns
WHERE table_name = 'STG_TRANSACTIONS'
AND table_schema = 'STAGING';

payment_method was nowhere in the staging table. The column existed in raw, new transactions carried the data, and the pipeline had silently thrown it away.

Snowflake information_schema query showing STG_TRANSACTIONS columns — payment_method missing

Snowflake query showing T027, T028 rows — only TRANSACTION_ID, STATUS, LOADED_AT visible, no payment_method

In production this means: analysts never see payment_method. Nobody gets alerted. Data is permanently lost for those rows. A dashboard gets built without a critical dimension. Six months later someone asks why you can’t filter transactions by payment method and nobody knows why.

This is the most dangerous kind of failure in data engineering — the one that succeeds silently.

Why This Happens — The Root Cause

The default behavior of incremental models in dbt is on_schema_change='ignore'. When the raw table has columns that your SELECT statement doesn't include, dbt ignores them. No error. No warning. Just silent omission.

We had on_schema_change='sync_all_columns' in our config — but that only kicks in when your dbt model's SELECT statement includes new columns. If you haven't updated the model to select the new column, there's nothing for sync_all_columns to sync.

The sequence of failures:

Raw table adds payment_method
    ↓
dbt model SELECT doesn't include payment_method
    ↓
on_schema_change has nothing to sync
    ↓
dbt runs successfully
    ↓
payment_method silently dropped from staging
    ↓
marts never see payment_method
    ↓
nobody knows

The Fix — Part 1: Proactive Detection with a Singular Test

The first thing I needed was a way to know when schema drift happens before it silently corrupts my pipeline.

I created tests/assert_no_schema_drift.sql:

-- This test FAILS if raw.transactions has columns
-- not present in stg_transactions
-- In CI/CD, this gates deployments and forces
-- engineers to handle new columns explicitly

with raw_cols as (
    select lower(column_name) as column_name
    from finsight_db.information_schema.columns
    where table_name = 'TRANSACTIONS'
    and table_schema = 'RAW'
),

stg_cols as (
    select lower(column_name) as column_name
    from finsight_db.information_schema.columns
    where table_name = 'STG_TRANSACTIONS'
    and table_schema = 'STAGING'
),

new_columns as (
    select r.column_name
    from raw_cols r
    left join stg_cols s
        on r.column_name = s.column_name
    where s.column_name is null
)

-- Returns rows = schema drift detected = TEST FAILS
-- Returns 0 rows = schemas in sync = TEST PASSES
select * from new_columns

Running dbt test --select assert_no_schema_drift after the schema change:

1 of 1 FAIL 1 assert_no_schema_drift [FAIL 1 in 2.85s]
Got 1 result, configured to fail if != 0

Terminal showing dbt test FAIL on assert_no_schema_drift

The test caught the drift. In a CI/CD pipeline, this test runs on every PR. If it fails, the deployment is blocked. A Slack alert fires. The engineer who added the new column is notified. Schema changes become conscious decisions, not silent accidents.

The Fix — Part 2: Updating the Model

With drift detected, the fix is to update stg_transactions.sql to explicitly handle the new columns:

final as (select
        transaction_id,
        customer_id,
        merchant_id,
        amount,
        transaction_date,
        upper(transaction_type)                  as transaction_type,
        upper(status)                            as status,
        coalesce(payment_method, 'unknown')      as payment_method,
        coalesce(transaction_channel, 'unknown') as transaction_channel,
        current_timestamp()                      as _loaded_at
from deduplicated
where customer_id is not null
and merchant_id is not null)

Two things worth noting here:

**coalesce(payment_method, 'unknown') — this is critical for historical records. Rows that were processed before payment_method existed will have NULL for that column. coalesce replaces NULL with 'unknown', maintaining a consistent schema across all rows without requiring a full rebuild. This is historical backfill without full refresh.**

Explicit column selection — instead of select *, every column is named explicitly. This is a production best practice. select * in a dbt model means schema changes in raw automatically propagate downstream — which sounds convenient but means unexpected columns can silently appear in your marts.

The Fix — Part 3: Zero-Downtime Schema Evolution

Here’s where on_schema_change='sync_all_columns' actually earns its place.

After updating the model SELECT statement to include payment_method and transaction_channel, I ran:

dbt run --select stg_transactions

No --full-refresh. No manual ALTER TABLE. Just a standard incremental run.

dbt detected that the SELECT statement now references columns that don’t exist in the staging table yet, and automatically issued:

ALTER TABLE finsight_db.staging.stg_transactions
ADD COLUMN payment_method VARCHAR;

ALTER TABLE finsight_db.staging.stg_transactions  
ADD COLUMN transaction_channel VARCHAR;

Then it MERGEd the 5 new rows with both columns populated.

The result:

1 of 1 OK created sql incremental model staging.stg_transactions [SUCCESS 5 in 5.18s]

dbt run output showing SUCCESS 5 rows incremental with new columns

Running dbt test --select assert_no_schema_drift after:

1 of 1 PASS assert_no_schema_drift [PASS in 3.47s]

dbt test output showing assert_no_schema_drift PASS

Schemas in sync. No full refresh. No downtime. No manual DDL on dbt-managed tables.

Why Not Just Use --full-refresh?

The obvious question: why go through all this instead of just running dbt run --full-refresh?

On our 26-row dataset, full refresh takes seconds. But the pattern you build with small data is the pattern you’ll use with big data. Let’s look at the real numbers:

Table size       Full refresh cost    ALTER TABLE cost
──────────────   ──────────────────   ────────────────
26 rows          ~3 seconds           ~0 seconds
1M rows          ~2 minutes           ~0 seconds
100M rows        ~45 minutes, $$$     ~0 seconds
1B rows          hours, $$$$          ~0 seconds

ALTER TABLE is a metadata operation in Snowflake. It doesn't touch the data. It doesn't spin up compute. It takes milliseconds regardless of table size. Combined with coalesce for historical defaults, you get full schema evolution without touching a single existing row.

The --full-refresh flag absolutely has its place — breaking schema changes, corrupt data requiring complete rebuilds, first-time environment setup. But for additive column additions, which represent the majority of real-world schema changes, ALTER TABLE via on_schema_change is always the right answer.

The Two-Layer Defense System

What emerged from this experience is a pattern I’ll use in every incremental pipeline going forward:

Layer 1: assert_no_schema_drift test
→ PROACTIVE — detects drift before any data is processed
→ runs in CI/CD on every PR
→ blocks deployment, fires alert
→ forces engineer to handle new columns consciously

Layer 2: on_schema_change='sync_all_columns'
→ REACTIVE — automatic safety net during dbt run
→ issues ALTER TABLE when model SELECT has new columns
→ zero manual intervention, zero downtime
→ handles the actual schema evolution

Together:
Schema change in raw
    ↓
Layer 1 catches it → engineer notified → model updated
    ↓
Layer 2 syncs it   → staging updated   → data flows correctly
    ↓
Pipeline healthy, marts accurate, analysts happy ✅

Neither layer alone is sufficient. Layer 1 without Layer 2 means you detect the problem but have no automatic fix path. Layer 2 without Layer 1 means changes happen without anyone knowing — which is exactly the silent failure we started with.

The Golden Rule That Came From This

Raw layer — you manage. Staging and marts — dbt manages exclusively.

I almost made the mistake of manually running ALTER TABLE on the staging table directly in Snowflake. That would have fixed the immediate problem but created a bigger one: dbt's state would be out of sync with the actual table, team members cloning the repo would have a different schema, and CI/CD would behave unpredictably.

dbt-managed tables are dbt’s responsibility. The moment you start manually modifying them, you’ve broken the contract that makes the whole system reliable. Let on_schema_change handle schema evolution. Let dbt own what dbt created.

Key Learnings from Milestone 6

1. Incremental models process new data only — but they have failure modes. The watermark filter (where transaction_date > max(transaction_date)) is simple and effective, but schema changes, data corruption, and late-arriving data each require specific handling strategies.

2. Silent success is sometimes worse than loud failure. A pipeline that fails loudly tells you exactly what’s wrong. A pipeline that succeeds silently while dropping business-critical data is far more dangerous — it erodes trust in your data without anyone knowing why.

3. Tests are CI/CD gates, not just quality checks. assert_no_schema_drift isn't just a test — it's a deployment gate. In production, dbt test runs before any PR merges to main. Schema drift fails the test, blocks the deployment, and forces a conscious decision. Data quality becomes a code review concern, not an afterthought.

4. coalesce is how you handle historical data during schema evolution. When new columns are added to an existing incremental table, old rows have NULL for those columns. coalesce(payment_method, 'unknown') provides a meaningful default without requiring expensive backfills or full refreshes.

5. The right tool for schema changes is ALTER TABLE, not --full-refresh. Full refresh is a sledgehammer. ALTER TABLE is a scalpel. Reach for the scalpel first.

What’s Next

This was the first of three incremental model failure modes I worked through in this milestone. Coming up: data corruption requiring targeted partition refresh, and late-arriving data being silently skipped by watermark logic — with production fixes for both.

The full project is open source on GitHub: https://github.com/SriGayathri06/finsight-dbt

Let’s Talk

Schema drift is one of those problems that every data engineer hits eventually usually in production, usually at the worst possible time. If you’ve dealt with this before, I’d love to know how your team handles it. Different warehouse? Different dbt version? A completely different approach?

Drop your thoughts in the comments whether it’s a question, a better pattern, or just a “yes, this burned me too.” Every comment helps someone else avoid the same silent failure.

If this saved you some debugging time or gave you a pattern you’ll actually use a clap means a lot and helps this reach engineers who need it 👏

Built with dbt Core 1.11.9, Snowflake Standard Edition. Full project: github.com/SriGayathri06/finsight-dbt