Practical Diff Validation for Migrations with dbt-audit-helper

In a migration, quantitatively proving that the new system produces the same results is critical. The dbt-audit-helper package compares two relations and returns a summary of counts, missing rows, and mismatched rows, making it easy to automate migration acceptance criteria.

This article distills diff-validation patterns you can safely use in production, framed around concepts commonly tested on the Analytics Engineer certification (testing vs diagnostics, reproducibility, CI integration). The details below stick to the stable parts of the official dbt documentation, with notes added where version differences may matter.

Why dbt-audit-helper for Migration Diff Validation

During migrations you frequently need to quickly visualize, for legacy (old) vs new tables, which primary keys are missing, where column values differ, and where duplicates or type mismatches occur. dbt-audit-helper provides macros that compare two relations and return a summary of counts, matches/mismatches, and duplicates. This lets you wire up reproducible validation faster than handwritten full-outer-join SQL.

From an exam perspective, a recurring theme is whether you can express production-grade QA declaratively and reproducibly in CI. By incorporating dbt-audit-helper as a model (or a dedicated diagnostic model), diffs are summarized automatically with every dbt run, and the result can drive pass/fail criteria.

• Row-level diff summaries (e.g., only in old, only in new, value mismatch) out of the box
• Strong support for primary-key-based comparison, simplifying duplicate and missing-row detection
• Combined with column exclusion and normalization preprocessing, you can absorb noise from timestamps, floats, and tracking columns

Abstract diff-validation flow during migration

packages.yml (minimal)

packages:
  - package: dbt-labs/dbt-audit-helper
    version: ">=0.9.0,<1.0.0"

Setup and Prerequisites

The basic flow is to add dbt-audit-helper to packages.yml and fetch it with dbt deps. Run it with a profile that has SELECT permission on both relations (old/new). Because the comparison may become a large full outer join, also watch the warehouse size and the quota for temporary/intermediate space.

A stable dbt best practice is to create a dedicated diff-summary model (e.g., audit_orders), build it in CI, and gate on zero rows (or a row count below an accepted threshold). Macro argument names and detailed output may vary by package version, so consult the docs for your version. The stable concept is 'compare and summarize two relations'.

• Required permissions: SELECT on the old/new relations, and CREATE on the schema that stores the comparison result
• Compare large tables incrementally via date partitions or sampling
• Agree up front on exclusions (audit columns, load timestamps, etc.)

Diff summary model skeleton (audit_orders.sql)

{{ config(materialized='table') }}

-- Reference the old and new relations
{% set old_rel = ref('orders_legacy') %}
{% set new_rel = ref('orders') %}

-- Representative call. Available arguments depend on the package version.
-- primary_key: the primary key used to match rows for the diff (single or multiple columns)
-- exclude_columns: columns to exclude from the comparison (e.g., load timestamp, tracking columns)

{{ audit_helper.compare_relations(
    a_relation=old_rel,
    b_relation=new_rel,
    primary_key=['order_id'],
    exclude_columns=['_ingested_at', '_updated_at']
) }}

Common Diff-Validation Patterns

Rather than chasing an exact match from day one, it's more realistic to converge while controlling noise. Combining the following patterns lets diffs shrink steadily.

Rounding floats, normalizing time zones, trimming and case-normalizing strings, and excluding audit columns (load timestamps, metadata) are basic moves that work reliably on any DWH.

• Start with a row-count check, then primary-key presence parity, and finally column-value equality — increasing granularity step by step
• Exclude audit columns and nondeterministic columns early via exclude_columns
• Round monetary or ratio fields (e.g., to 2 decimal places) in preprocessing to align them
• Align timestamps on time zone and precision (seconds / milliseconds / microseconds)

Comparison through rounding and normalization (via a preprocessing model)

-- Preprocessing: prepare normalized views for old/new and have the comparison reference those views
-- models/stg_orders_legacy_norm.sql
select
  order_id,
  upper(trim(customer_name)) as customer_name,
  round(amount, 2) as amount,
  to_timestamp_ntz(order_ts) as order_ts
from {{ ref('orders_legacy') }}

-- models/stg_orders_norm.sql
select
  order_id,
  upper(trim(customer_name)) as customer_name,
  round(amount, 2) as amount,
  to_timestamp_ntz(order_ts) as order_ts
from {{ ref('orders') }}

-- models/audit_orders.sql (comparison)
{{ config(materialized='table') }}
{{ audit_helper.compare_relations(
    a_relation=ref('stg_orders_legacy_norm'),
    b_relation=ref('stg_orders_norm'),
    primary_key=['order_id'],
    exclude_columns=[]
) }}

CI/CD Integration and Pass/Fail Gating

Build the diff summary model in CI and gate the pipeline on the result. If you can't enforce zero-row exact match, define an accepted threshold (e.g., 0 missing rows, <=10 value mismatches) so the pass/fail condition is explicit and avoids disputes in real-world operation.

In dbt, you can express arbitrary pass/fail logic just by creating a singular test that expects zero rows from a single query. Reference the diff summary model and write a SELECT that returns rows for the NG (failure) condition.

• Group diff summaries under models/audit and run dbt run -s models/audit in CI
• Place singular tests under tests/ and define SELECTs that return the NG condition
• Branch acceptance thresholds via vars or environment variables (toggle between dev and prod)

Singular test example (require 0 mismatches and 0 missing rows)

-- tests/no_diff_orders.sql
-- audit_orders (summary output) is expected to surface rows when there are diffs
select *
from {{ ref('audit_orders') }}
where (mismatch_count > 0 or only_in_a > 0 or only_in_b > 0)
-- If the SELECT above returns 1 or more rows, the test fails (CI blocks)

# Reference: GitHub Actions (excerpt)
# - run: dbt deps
# - run: dbt run -s models/audit
# - run: dbt test -s tests/no_diff_orders.sql

Performance and Scaling Strategy

For large tables, it's more practical to advance step by step using partition windows or sampling rather than comparing everything at once. First, compare only the last N days to confirm the pipeline is healthy, then widen the backfill window over time. This controls cost and time while still ensuring quality.

Clustering or sort keys on the primary key also reduce full-outer-join cost. Before comparing, consider refreshing statistics or optimizing the table (e.g., Snowflake clustering, Databricks OPTIMIZE).

• Start with a last-N-days window and grow N step by step
• Prepare normalized views with WHERE clauses that narrow the date / partition
• Introduce sample-based comparison (percentile-based sampling) early for visual inspection

Comparison narrowed by date window (configurable via vars)

{{ config(materialized='table') }}
{% set days = var('compare_days', 7) %}

with legacy as (
  select * from {{ ref('orders_legacy') }}
  where order_date >= dateadd(day, -{{ days }}, current_date)
),
newer as (
  select * from {{ ref('orders') }}
  where order_date >= dateadd(day, -{{ days }}, current_date)
)

select * from (
  {{ audit_helper.compare_relations(
      a_relation=adapter.get_relation(database=target.database, schema=this.schema, identifier='legacy'),
      b_relation=adapter.get_relation(database=target.database, schema=this.schema, identifier='newer'),
      primary_key=['order_id']
  ) }}
)

Common Pitfalls and How to Fix Them

Float rounding errors, string collation differences, and gaps in time zones or sub-second precision are classic reasons diffs refuse to shrink. Normalize before comparing, and explicitly exclude columns that simply can't be stabilized.

If the primary key isn't unique, duplicate detection comes first. Building a surrogate key (a hash of multiple columns) before comparing works well. The surrogate_key macro in dbt-utils is widely used and stable.

• Align float precision with round (e.g., 2 decimals for currency)
• Normalize timestamps to NTZ / UTC and align precision
• If primary keys aren't ready, introduce a surrogate key temporarily for comparison
• Leave repro steps for each diff (queries, sample rows) as comments in the model

Preprocessing example with a surrogate key and rounding

-- Build a surrogate key with dbt-utils and unify rounding
with base as (
  select
    {{ dbt_utils.surrogate_key(['order_id', 'warehouse_id']) }} as sk,
    round(amount, 2) as amount,
    to_timestamp_ntz(order_ts) as order_ts,
    *
  from {{ ref('orders') }}
)
select * from base

Check Your Understanding

Frequently Asked Questions