Validate ETL Schema Drift With dbt (2026)

TL;DR

Declare the expected source columns and warehouse types, compare them with adapter metadata in a custom generic test, and enforce a contract on the downstream model. Run the source test and its descendants with dbt build in CI so breaking ETL drift stops the release.

Key Takeaways

• Compare the source relation against an explicit column and type contract before building downstream models.
• Use adapter.get_columns_in_relation inside a generic dbt data test so the same test can target sources, seeds, or models.
• Enforce a dbt model contract to stop transformed output from exposing an unexpected name, type, or column count.
• Keep structural tests separate from not_null, unique, accepted_values, and business-rule tests because they catch different defects.
• Store failing schema rows so a CI log shows exactly which column is missing, unexpected, or retyped.
• Prove the guard by injecting known drift, observing a nonzero dbt exit code, and restoring the baseline.
• Run the source schema test before dependent models to fail early and avoid publishing partially valid tables.

To validate etl schema drift with dbt, compare the columns that physically arrived in the warehouse with a version-controlled expectation, then enforce the output schema of the model that consumes them. A reliable guard must report missing, unexpected, and retyped columns before downstream users see a broken table.

This tutorial builds that guard with dbt Core and DuckDB. You will load a known source table, write a reusable generic data test with adapter.get_columns_in_relation, protect a staging model with a dbt contract, inject deliberate drift, and place the checks in CI. The SQL patterns also apply to Snowflake, BigQuery, Redshift, Databricks, and Postgres because dbt adapters expose relation metadata through the same interface.

Schema checks do not replace row-level reconciliation. Use them beside SQL checks for ETL validation so the pipeline proves both structure and content.

What You Will Build

By the end, your small project will contain:

• A repeatable load_raw_orders macro that creates either a stable source or a deliberately drifted source.
• A matches_schema generic data test that returns one failure row per missing, unexpected, or incorrectly typed column.
• A contracted stg_orders model with explicit casts and normal data-quality assertions.
• Stored failure records that make a schema incident diagnosable from CI.
• A GitHub Actions job that blocks a pull request when the source contract or model contract fails.

Before you automate the comparison, write down the compatibility policy for the source. A strict contract treats any structural difference as a release blocker. A compatibility contract may permit reviewed additions but still reject removal, rename, narrowing, or loss of precision. The test code must reflect that decision explicitly because the warehouse cannot infer whether a new column is useful, irrelevant, or sensitive.

Choose the drift policy

Use the producer-consumer boundary to classify each mutation:

This tutorial compares names and types but ignores ordinal position. That is appropriate for dbt SQL, which resolves named columns. If a downstream CSV export, bulk copy, or legacy connector maps fields by position, add ordinal position to the expectation and comparison instead of assuming order is cosmetic.

The important boundary is intentional. The generic test protects the producer-to-warehouse interface. The model contract protects the staging-model-to-consumer interface. A rename at the source and an accidental extra column at the model are different failures, so you want evidence from both layers.

Prerequisites

Use Python 3.12.7, dbt-core 1.12.0, and dbt-duckdb 1.10.1 for the exact lab below. dbt Core 1.12 requires Python 3.10 or newer. The DuckDB adapter gives you a local warehouse file, so no cloud account or credentials are required. You also need Git 2.43 or newer and a shell that can create files.

Create and activate an isolated environment:

python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install "dbt-core==1.12.0" "dbt-duckdb==1.10.1"
dbt --version

On Windows PowerShell, activate with .venv\Scripts\Activate.ps1. The version output should list Core 1.12.0 and the DuckDB plugin. Do not use a global dbt installation for this exercise, since an older executable may parse the current arguments: syntax for generic tests differently.

Verification command: run python --version && dbt --version. Confirm Python reports 3.12.7 and dbt reports Core 1.12.0 before continuing.

Step 1: Create the dbt Project and DuckDB Profile

Create the project directories from the folder where you want the lab to live:

mkdir -p etl_schema_guard/macros etl_schema_guard/models etl_schema_guard/tests
cd etl_schema_guard

Add dbt_project.yml:

name: etl_schema_guard
version: 1.0.0
config-version: 2
profile: etl_schema_guard

model-paths: ["models"]
test-paths: ["tests"]
macro-paths: ["macros"]
target-path: target
clean-targets: ["target", "dbt_packages"]

models:
  etl_schema_guard:
    +materialized: table

Keep the tutorial profile beside the project as profiles.yml:

etl_schema_guard:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ./etl_lab.duckdb
      schema: main
      threads: 2

A file-backed database lets later commands inspect the same relations. An in-memory DuckDB target would disappear when each dbt process exits, which makes a multi-command drift demonstration misleading. In production, keep credentials in environment variables or your CI secret store, never in the committed profile.

Verification command: run dbt debug --profiles-dir .. The final line should say All checks passed!, and etl_lab.duckdb should be available to subsequent commands.

Step 2: Load a Deterministic Raw ETL Table

A reproducible tutorial needs a source whose schema you control. Create macros/load_raw_orders.sql with a boolean switch that selects the baseline DDL or a breaking variant:

{% macro load_raw_orders(drift=false) %}
  {% do run_query("create schema if not exists raw") %}

  {% if drift %}
    {% set ddl %}
      create or replace table raw.raw_orders (
        order_id integer,
        customer_id integer,
        ordered_at timestamp,
        order_status varchar,
        total_cents varchar,
        discount_code varchar
      )
    {% endset %}
    {% set inserts %}
      insert into raw.raw_orders values
        (1001, 10, timestamp '2026-08-01 10:00:00', 'paid', '2599', 'SAVE10'),
        (1002, 11, timestamp '2026-08-01 10:05:00', 'shipped', '4100', null)
    {% endset %}
  {% else %}
    {% set ddl %}
      create or replace table raw.raw_orders (
        order_id integer,
        customer_id integer,
        ordered_at timestamp,
        status varchar,
        total_cents bigint
      )
    {% endset %}
    {% set inserts %}
      insert into raw.raw_orders values
        (1001, 10, timestamp '2026-08-01 10:00:00', 'paid', 2599),
        (1002, 11, timestamp '2026-08-01 10:05:00', 'shipped', 4100)
    {% endset %}
  {% endif %}

  {% do run_query(ddl) %}
  {% do run_query(inserts) %}
  {{ log("Loaded raw.raw_orders with drift=" ~ drift, info=true) }}
{% endmacro %}

Declare the relation in models/sources.yml without a test yet:

version: 2

sources:
  - name: shop
    schema: raw
    description: Raw tables produced by the shop ETL job.
    tables:
      - name: raw_orders
        description: One row per order emitted by the upstream loader.

Load the stable shape, then ask the warehouse catalog what it received:

dbt run-operation load_raw_orders --args '{drift: false}' --profiles-dir .
dbt show --inline "select column_name, data_type from information_schema.columns where table_schema = 'raw' and table_name = 'raw_orders' order by ordinal_position" --profiles-dir .

Verification: the second command must show five columns in order: order_id INTEGER, customer_id INTEGER, ordered_at TIMESTAMP, status VARCHAR, and total_cents BIGINT. This catalog result is the actual side of the contract, not documentation copied from the producer.

Step 3: Add a Test to validate etl schema drift with dbt

Create macros/test_matches_schema.sql. A generic data test succeeds when its query returns zero rows, so the macro emits only structural differences:

{% test matches_schema(model, expected_columns) %}
  {% if execute %}
    {% set actual_columns = adapter.get_columns_in_relation(model) %}
  {% else %}
    {% set actual_columns = [] %}
  {% endif %}

  with expected(column_name, data_type) as (
    values
    {% for column in expected_columns %}
      ('{{ column["name"] | lower }}', '{{ column["data_type"] | lower }}'){% if not loop.last %},{% endif %}
    {% endfor %}
  ),
  actual(column_name, data_type) as (
    values
    {% if actual_columns | length == 0 %}
      (cast(null as varchar), cast(null as varchar))
    {% else %}
      {% for column in actual_columns %}
        ('{{ column.name | lower }}', '{{ column.data_type | lower }}'){% if not loop.last %},{% endif %}
      {% endfor %}
    {% endif %}
  ),
  comparison as (
    select
      coalesce(expected.column_name, actual.column_name) as column_name,
      expected.data_type as expected_type,
      actual.data_type as actual_type,
      case
        when actual.column_name is null then 'missing_column'
        when expected.column_name is null then 'unexpected_column'
        when expected.data_type <> actual.data_type then 'type_mismatch'
      end as issue
    from expected
    full outer join actual using (column_name)
  )
  select *
  from comparison
  where issue is not null
{% endtest %}

The full outer join is what makes the result diagnostic. Expected-only rows become missing_column, actual-only rows become unexpected_column, and matched names with different types become type_mismatch. Returning those rows follows dbt's data-test contract directly: zero records means pass, one or more records means fail. The null placeholder in the actual CTE keeps the compiled SQL syntactically valid when parsing occurs without a live catalog call.

Keep the expectation at the same level of detail the adapter can reliably return. For ordinary scalar fields that means a canonical type such as integer or character varying(256). For decimals, timestamps, arrays, structs, and geography fields, first capture the exact adapter output in a nonproduction target. Then decide which attributes are compatible. A timestamp losing its time zone or a decimal losing scale should normally block even if both are casually described as the same family.

Case handling deserves the same deliberate choice. This macro lowercases unquoted identifiers because DuckDB and many warehouses fold them predictably. If your platform preserves quoted case and consumers distinguish CustomerID from customerid, remove the lowercase normalization. Test names using the same quoting rules as the real source rather than creating a falsely portable contract.

adapter.get_columns_in_relation(model) asks the active adapter for real catalog metadata. Lowercasing avoids cosmetic case differences, but type comparison remains strict. For warehouses that report aliases such as NUMBER(38,0) instead of BIGINT, put the adapter's canonical value in the expected list or normalize known equivalents in one reviewed macro. Do not broadly strip precision because decimal(12,2) changing to decimal(12,0) can destroy cents.

Replace models/sources.yml with the contracted source definition:

version: 2

sources:
  - name: shop
    schema: raw
    description: Raw tables produced by the shop ETL job.
    tables:
      - name: raw_orders
        description: One row per order emitted by the upstream loader.
        data_tests:
          - matches_schema:
              name: raw_orders_schema_contract
              arguments:
                expected_columns:
                  - {name: order_id, data_type: integer}
                  - {name: customer_id, data_type: integer}
                  - {name: ordered_at, data_type: timestamp}
                  - {name: status, data_type: character varying(256)}
                  - {name: total_cents, data_type: bigint}
              config:
                severity: error
                store_failures: true

Verification command: run dbt test --select raw_orders_schema_contract --profiles-dir .. The baseline produces PASS=1 because the comparison query returns no differences.

Step 4: Protect the Downstream Model With a dbt Contract

The source test catches arrival drift, while a model contract checks the exact dataset returned by your transformation. Create models/stg_orders.sql:

select
  cast(order_id as integer) as order_id,
  cast(customer_id as integer) as customer_id,
  cast(ordered_at as timestamp) as ordered_at,
  cast(status as varchar) as status,
  cast(total_cents as bigint) as total_cents
from {{ source('shop', 'raw_orders') }}

Then create models/stg_orders.yml:

version: 2

models:
  - name: stg_orders
    description: Typed order records safe for downstream analytics.
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: integer
        data_tests: [not_null, unique]
      - name: customer_id
        data_type: integer
        data_tests: [not_null]
      - name: ordered_at
        data_type: timestamp
        data_tests: [not_null]
      - name: status
        data_type: varchar
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [paid, shipped, cancelled]
      - name: total_cents
        data_type: bigint
        data_tests: [not_null]

Explicit selection matters. select * silently forwards additive drift, and a contract then fails without showing whether the extra field was intentional. Named columns document the boundary and make code review meaningful. Explicit casts stabilize the model output, but they do not make every source change safe. Casting a newly textual amount to BIGINT may fail at runtime or conceal a producer mistake if all current strings happen to be numeric.

A dbt model contract validates column names, data types, and column count before materialization. It complements not_null, unique, and accepted_values; those data tests run queries against built data and answer different questions.

Verification command: run dbt build --select stg_orders --profiles-dir .. Expect one table model and seven data tests to pass. If the contract and SQL disagree, dbt stops with a contract mismatch before replacing the valid table.

Step 5: Run the Complete Baseline Gate

Select the source and every downstream node with dbt's graph operator:

dbt build --select source:shop.raw_orders+ --profiles-dir .
dbt show --select stg_orders --limit 3 --profiles-dir .

The first command runs the source schema test before building and testing stg_orders. This ordering is safer than separate unchecked scripts: a failing upstream test causes dependent nodes in the build graph to skip. The second command should display two typed rows with integer IDs, timestamps, allowed statuses, and numeric cents.

Use each dbt feature for the defect class it actually covers:

If you need deeper row checks, the data migration testing guide explains counts, aggregates, sampling, and rollback evidence. Keep those assertions separate so an incident immediately tells you whether the problem is structure, content, or both.

Verification: confirm the build summary contains no warnings, errors, or skips. Treat a skipped downstream model as a failed gate in CI, even when the original cause is an upstream test.

Step 6: Prove You Can validate etl schema drift with dbt

A guard you have never seen fail is only an assumption. Replace the baseline table with the drifted variant:

dbt run-operation load_raw_orders --args '{drift: true}' --profiles-dir .
set +e
dbt test --select raw_orders_schema_contract --profiles-dir .
status=$?
set -e
test "$status" -ne 0

The drift makes three independent breaking changes: status is renamed to order_status, total_cents changes from BIGINT to VARCHAR, and discount_code appears unexpectedly. The generic test therefore returns four rows: one missing column, two unexpected columns, and one type mismatch. status and order_status are reported separately because guessing that one rename compensates for the other would be unsafe.

Because store_failures: true is configured, inspect the evidence table:

dbt show --inline "select column_name, expected_type, actual_type, issue from main_dbt_test__audit.raw_orders_schema_contract order by column_name" --profiles-dir .

Now try the graph gate:

dbt build --select source:shop.raw_orders+ --profiles-dir .

The source test fails and stg_orders is skipped. That is the desired result: the last known valid model remains in place instead of being overwritten during a bad load. If you ran only dbt run, the model would instead fail later when SQL referenced the missing status column, producing a less precise diagnosis.

Restore the fixture so the project ends green:

dbt run-operation load_raw_orders --args '{drift: false}' --profiles-dir .
dbt build --select source:shop.raw_orders+ --profiles-dir .

Verification: the intentional test must exit nonzero, the stored failures must classify all four differences, and the final restored build must return exit code 0. This positive-negative-positive sequence proves that the test detects the intended mutation without permanently poisoning the lab.

Step 7: Put the Schema Gate in GitHub Actions

Create .github/workflows/dbt-schema-drift.yml:

name: dbt schema drift

on:
  pull_request:
    paths:
      - "models/**"
      - "macros/**"
      - "dbt_project.yml"
      - "profiles.yml"
      - ".github/workflows/dbt-schema-drift.yml"
  workflow_dispatch:

jobs:
  validate:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@v6
      - uses: actions/setup-python@v6
        with:
          python-version: "3.12.7"
          cache: pip
      - name: Install pinned dbt packages
        run: |
          python -m pip install --upgrade pip
          python -m pip install "dbt-core==1.12.0" "dbt-duckdb==1.10.1"
      - name: Check connection
        run: dbt debug --profiles-dir .
      - name: Create tutorial source
        run: >-
          dbt run-operation load_raw_orders
          --args '{drift: false}'
          --profiles-dir .
      - name: Block schema drift and downstream regressions
        run: dbt build --select source:shop.raw_orders+ --profiles-dir .

The fixture-loading step belongs only in this self-contained lab. In a real project, point the CI profile at an isolated QA warehouse containing the latest ingestion output. Give the CI identity read access to raw schemas and create access only in a disposable dbt schema. Never aim pull-request jobs at production targets.

A production gate also needs a trustworthy observation point. Test the relation after ingestion has committed the complete batch, not while files or partitions are still arriving. If the loader swaps tables atomically, point dbt at the published name. If it mutates a table in place, coordinate the check with an orchestration completion signal so a transient half-written schema does not create noise.

Prevent concurrent pull requests from sharing one schema. Generate a target schema from the pull-request number, or serialize jobs that must inspect the same QA source. Shared model schemas can make one branch pass against another branch's table, which defeats reproducibility. Raw-source access may remain shared and read-only when the upstream fixture is immutable for the duration of the run.

For rolling producer deployments, test both the old and new shapes during the compatibility window. A catalog check sees one relation at one moment, but partitioned object stores can contain mixed file schemas. Add a pre-ingestion file-schema check or query representative partitions when heterogeneous files are possible. Promote the new expectation only after every active producer version emits a compatible shape and backfill plans cover old data.

For larger DAGs, keep the source contract in the first job and publish target/run_results.json as an artifact. A second job can build modified descendants after the contract succeeds. Start with the whole source:shop.raw_orders+ selector here because clarity is more valuable than premature selection optimization.

Verification command: locally run dbt run-operation load_raw_orders --args '{drift: false}' --profiles-dir . && dbt build --select source:shop.raw_orders+ --profiles-dir .. After pushing, the dbt schema drift check should finish green on the pull request.

Troubleshooting

Problem: adapter.get_columns_in_relation returns no columns -> Confirm the source exists in the same target used by the test. Run dbt debug --profiles-dir ., reload the baseline, and inspect information_schema.columns. A parse-only command may set Jinja's execute flag to false, which is why the macro includes a safe parse branch.

Problem: every type is reported as different -> Use the canonical names returned by your warehouse adapter. Snowflake may expose NUMBER(38,0) where DuckDB exposes BIGINT. Add narrow, reviewed alias normalization for equivalent types, but preserve length, precision, scale, time zone, and nested-type details that affect consumers.

Problem: the model contract fails although the source test passes -> Compare the select list with models/stg_orders.yml. The source interface can be valid while a SQL edit changes an output alias, drops a cast, or adds a column. Fix the transformation or deliberately version the model contract.

Problem: the failure table cannot be found -> Verify store_failures: true is under the generic test's config. dbt normally combines the target schema with dbt_test__audit; a custom generate_schema_name macro can produce another name. Read the failing test log for the exact relation.

Problem: drift is detected after models already ran -> Use one dbt build command with a source-plus selector instead of an independent dbt run followed by dbt test. Build respects graph order and skips descendants whose upstream test failed.

Problem: an added nullable column should be allowed -> Decide that policy explicitly. Either update the expected contract in the same reviewed change or extend the macro with a documented allowlist. Do not globally ignore additions, since an accidental sensitive column could then flow into a permissive select *.

Interview Questions and Answers

Q: What is ETL schema drift?

It is an unplanned difference between the structure a data consumer expects and the structure a producer delivers. Examples include a removed field, a renamed key, a numeric column becoming text, or a new nested attribute. The important word is unplanned: a reviewed, versioned schema evolution is change management, while drift is an uncontrolled compatibility risk.

Q: Why are dbt model contracts insufficient for raw sources?

Model contracts apply to supported model materializations, not to source resources. They protect the dataset produced by transformation SQL, but they do not independently certify the physical source table. A catalog-based generic data test closes that gap before the source feeds the model.

Q: Why not detect drift with select * and a downstream failure?

That approach discovers the problem late and can leak additive columns without an error. Explicit source expectations identify the producer boundary, while explicit model columns prevent accidental propagation. Together they produce a precise failure instead of relying on whatever query happens to break first.

Q: Should a new source column always fail CI?

For a strict interface, yes, because additions can expose sensitive fields, change generated artifacts, or feed wildcard selections. Some teams allow additive drift through a reviewed allowlist. The acceptance policy should be encoded and auditable, not decided silently by adapter behavior.

Q: How do you handle legitimate breaking evolution?

Coordinate producer and consumer changes, update the source expectation in version control, and version public downstream models when consumers need migration time. Run both schemas in parallel where the warehouse allows it. A green test should then represent an approved contract, not an exception that someone disabled.

Q: What evidence should a drift test preserve?

Store the column name, expected type, actual type, issue classification, dbt invocation ID, and target environment. Retain manifest.json and run_results.json with the CI run so reviewers can connect the failure to code and lineage. Avoid dumping sample values when metadata alone proves the defect.

These questions also appear in concise model-answer form below. Practice explaining the producer boundary, transformation boundary, and failure order at /practice rather than describing dbt tests as one undifferentiated layer.

Best Practices

• Put the source expectation beside the dbt source declaration so code owners review both together.
• Compare names and types strictly by default; document every normalization rule with the platform behavior it addresses.
• Select columns explicitly in staging models. A contract plus select * is noisy protection, not a clean interface.
• Run a mutation test for each supported change class: remove, add, rename, and retype a column.
• Keep production raw schemas read-only for dbt CI identities and build models in isolated schemas.
• Preserve failure relations and dbt artifacts long enough to investigate intermittent producer rollouts.
• Treat accepted schema changes like API changes. The contract testing guide provides the same compatibility mindset for service boundaries.

Avoid weakening the test to clear a red pipeline without producer confirmation. If an ingestion job deployed gradually, two partitions may carry different structures even when the latest catalog looks correct. For file-based lakes, validate file schemas before unioning them and test representative partitions, not only the newest object.

Conclusion

The dependable way to validate structural ETL behavior is to test both interfaces. Compare the physical raw relation against an explicit source expectation, then enforce the exact output of the dbt staging model. Add row-level assertions only after those boundaries are stable.

The lab's deliberate failure is as important as its green run. It proves the test distinguishes missing, unexpected, and retyped fields, stops descendants, and leaves evidence that another engineer can act on.

Where To Go Next

Extend the lab with production-grade validation in this order:

1. Add reconciliation queries from writing SQL to validate ETL to compare keys, totals, and duplicate behavior.
2. Apply rollback and historical checks from testing data migrations when a schema change also rewrites stored records.
3. Review conditional schema migration test blocks for changes that depend on flags or staged rollout state.
4. Strengthen your SQL fundamentals with the SQL for QA tutorial before adding warehouse-specific metadata queries.
5. Use senior database testing scenarios to rehearse incident triage and explain trade-offs in interviews.

Your next practical move is to copy matches_schema into a QA dbt project, replace the five tutorial columns with one real source contract, and inject one safe drift mutation in an isolated environment. When the job fails for the exact expected reason and recovers after restoration, wire that selector into the release gate.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Validate GraphQL Deprecations with Schema Diffing
• Validate Trace Propagation With OpenTelemetry (2026)
• Writing SQL to validate ETL (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)