dbt-audit-helper: validating refactors and migrations

Refactoring a dbt model without a way to prove the output hasn’t changed is guesswork. You can eyeball the SQL, run a few spot checks, and hope for the best. Or you can use dbt-audit-helper to compare your new model against the original, row by row and column by column, and know exactly what changed.

dbt-audit-helper is maintained by dbt Labs, currently at version 0.13.0. It’s Fusion-compatible (require-dbt-version: >=1.2.0, <3.0.0), depends on dbt-utils, and supports Snowflake, BigQuery, Postgres, and Redshift. The hash-based quick macros are limited to Snowflake and BigQuery.

The progressive validation workflow

Audit-helper works best when you start broad and narrow down. Don’t jump straight to row-level comparison on a 100-million-row table. Instead, follow this sequence:

  1. Schema check with compare_relation_columns (are the columns the same?)
  2. Row count check with compare_row_counts (same number of rows?)
  3. Quick hash check with quick_are_relations_identical (fast pass/fail, Snowflake/BigQuery only)
  4. Row-level comparison with compare_relations or compare_queries (which rows differ?)
  5. Identify problem columns with compare_which_relation_columns_differ (where are the differences?)
  6. Column deep-dive with compare_column_values (what exactly changed in a specific column?)
  7. Full column audit with compare_all_columns (per-column summary across all columns)
  8. Row classification with compare_and_classify_relation_rows (is each row identical, modified, added, or removed?)

Each step is cheaper than the next. If schema or row count already shows a mismatch, there’s no point running a full row comparison. This saves both time and warehouse compute.

Macro reference

compare_relation_columns

Compares schema metadata between two relations: column names, ordinal positions, and data types. This catches missing columns, type changes, and reordering before you spend cycles on data comparison.

{{ audit_helper.compare_relation_columns(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    )
) }}

Run this first. If the schemas don’t match, you’ll need to resolve that before row-level comparisons make sense.

compare_row_counts

A simple row count comparison between two relations. Added in v0.11.0 (March 2024).

{{ audit_helper.compare_row_counts(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    )
) }}

If counts match, move on to data comparison. If they don’t, you have either a filtering issue or a join that’s fanning out rows.

quick_are_relations_identical

Uses hash comparison for a fast pass/fail. Snowflake and BigQuery only.

{{ audit_helper.quick_are_relations_identical(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    )
) }}

If it passes, you’re done. No need for deeper comparison. If it fails, move to compare_relations to find out why.

There’s also a query-based variant: quick_are_queries_identical.

compare_relations

The core macro. Performs row-by-row validation using a UNION-based approach.

{{ audit_helper.compare_relations(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    ),
    exclude_columns=["_fivetran_synced"],
    primary_key="revenue__id",
    summarize=true
) }}

With summarize=true (the default), the output shows counts and percentages:

  • True/True: rows present in both relations (matches)
  • True/False: rows only in A
  • False/True: rows only in B

Set summarize=false to see the actual row data with in_a/in_b flags, which helps identify the specific rows that differ.

compare_queries works identically but accepts raw SQL SELECT statements instead of relations. Use it when you need to filter, rename, or recast columns before comparison.

compare_which_relation_columns_differ

When compare_relations shows mismatches but you don’t know which columns are causing them, this macro identifies the culprits. Added in v0.12.0 (June 2024).

{{ audit_helper.compare_which_relation_columns_differ(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    ),
    primary_key_columns=['revenue__id']
) }}

Run this before compare_column_values to avoid manually checking columns one by one.

compare_column_values

Deep-dives into a single column’s values across two queries joined on a primary key.

{{ audit_helper.compare_column_values(
    a_query="SELECT revenue__id, revenue__amount FROM " ~ ref('mrt__finance__revenue'),
    b_query="SELECT revenue__id, revenue__amount FROM analytics_db.production.mrt__finance__revenue",
    primary_key="revenue__id",
    column_to_compare="revenue__amount"
) }}

Output categorizes each row as: perfect match, both null, missing from A, missing from B, null in A only, null in B only, or values do not match.

Gotcha: the .print_table() method is not compatible with dbt Cloud. Use a log() workaround to display results in the Cloud IDE.

compare_all_columns

Compares all columns across two relations, column by column. The summary output shows per-column counts of perfect_match, null_in_a, null_in_b, missing_from_a, missing_from_b, and conflicting_values.

This is the macro built for CI. Create a test that filters on conflicts:

models:
  - name: mrt__finance__revenue
    data_tests:
      - dbt_utils.expression_is_true:
          expression: "conflicting_values = 0"
          where: "1=1"

Or use --store-failures to persist mismatches for investigation.

compare_and_classify_query_results

Added in v0.12.0 and marked “v0, subject to change.” Classifies each row as identical, modified, added, or removed.

{{ audit_helper.compare_and_classify_relation_rows(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    ),
    primary_key_columns=['revenue__id'],
    columns=['revenue__amount', 'revenue__currency', 'revenue__booked_at'],
    sample_limit=20
) }}

The sample_limit parameter (default 20) caps the number of sample rows returned per classification. The relation wrapper is compare_and_classify_relation_rows.

Real-world workflows

SQL-to-dbt migration

When migrating legacy SQL to dbt, the question is always “does my new dbt model produce the same output as the old query?” Load your existing tables into the same warehouse as your dbt project, build the equivalent dbt models, then compare them systematically.

The workflow I recommend:

  1. Start with compare_relation_columns to verify the schemas match. Column names, types, and order should be identical. Fix any discrepancies here before moving forward.
  2. Run compare_row_counts as a quick sanity check. Mismatched counts usually point to filtering differences or JOIN fan-outs.
  3. Run compare_relations for a high-level match percentage. A 100% match means you’re done. Anything below means digging deeper.
  4. If below 100%, use compare_which_relation_columns_differ to isolate problem columns. This saves you from checking every column manually.
  5. Use compare_column_values to investigate each problematic column and understand the exact nature of the mismatches.

A practical tip from the Indicium Tech team: compare 5 columns at a time, confirm they match, then expand. This keeps the feedback loop tight and prevents you from chasing too many issues at once. Trying to debug all mismatches simultaneously usually leads to confusion about which change fixed what.

Refactoring validation

Refactoring is where audit-helper gets the most use day-to-day. You’re rewriting a CTE, changing a join strategy, or restructuring a model, and you need to verify that the output stays the same. Use compare_all_columns referencing your dev model against production:

{{ audit_helper.compare_all_columns(
    a_relation=ref('mrt__finance__revenue'),
    b_relation=api.Relation.create(
      database='analytics_db',
      schema='production',
      identifier='mrt__finance__revenue'
    ),
    primary_key_columns=['revenue__id']
) }}

In this setup, ref('mrt__finance__revenue') resolves to your dev schema while api.Relation.create points directly at production. Any differences surface immediately.

CI/CD integration

In dbt Cloud: include compare_all_columns tests in CI jobs triggered on pull requests. The ref() compiles to the PR schema automatically, so dev-vs-prod comparison happens on every PR.

In dbt Core with GitHub Actions: build modified models with --defer --state to target only changed models, then run audit tests with --store-failures to persist any mismatches for review.

For interactive investigation, Hex offers a notebook template that pairs well with audit-helper for visual PR validation workflows.

Large-scale ETL migrations

For enterprise-scale migrations (like Informatica to dbt), the dbt-audit-helper-ext package by Infinite Lambda extends audit-helper with a validation log table, summary view, codegen scripts for auto-generating validation macros, and threshold-based acceptance: 100% match = pass, 99%+ = warning, below 99% = fail. It’s available on the dbt package Hub as infinitelambda/audit_helper_ext (v0.8.0).

Limitations and gotchas

Primary key requirement. Most macros need a unique, non-null primary key to join the two relations. Make sure your models pass unique and not_null tests before running audit-helper comparisons. Without a reliable key, join-based macros produce misleading results.

Floating point precision. All comparisons use exact equality. There’s no epsilon tolerance for floating point values. If you’re comparing calculated fields with decimals, cast to a specific precision first:

{{ audit_helper.compare_queries(
    a_query="SELECT revenue__id, ROUND(revenue__amount, 2) AS revenue__amount FROM " ~ ref('mrt__finance__revenue'),
    b_query="SELECT revenue__id, ROUND(revenue__amount, 2) AS revenue__amount FROM analytics_db.production.mrt__finance__revenue",
    primary_key="revenue__id"
) }}

Performance on large tables. The macros use FULL OUTER JOIN and UNION ALL under the hood, which means full table scans. On large tables, this gets expensive. Use quick_are_relations_identical for an initial fast check, filter with WHERE clauses to reduce scope, or compare a date-bounded subset before running a full comparison.

One model at a time. Unlike tools like Datafold that automatically discover and compare all affected models, audit-helper requires manual setup per model. This is fine for targeted validation but tedious for migrations spanning dozens of models.

dbt Cloud compatibility. The .print_table() method doesn’t work in dbt Cloud’s IDE. Use {{ log(result, info=true) }} as a workaround.

When audit-helper isn’t enough

audit-helper excels at point-in-time comparison: “does my new model produce the same output as the old one?” For ongoing monitoring, you need different tools.

Elementary handles data observability: anomaly detection, schema change tracking, and volume monitoring over time. It’s complementary to audit-helper, not a replacement.

dbt-expectations provides 40+ generic tests for value ranges, patterns, and statistical checks. Use it for ongoing quality validation rather than one-time comparisons.

Datafold Cloud is the commercial alternative. The open-source data-diff is no longer actively maintained as of May 2024. Datafold’s strength is automatic model discovery across your entire DAG, which saves significant setup time on large migrations. If you’re migrating dozens of models and don’t want to manually set up audit-helper for each one, Datafold is worth evaluating.

Soda takes a YAML-based approach to data quality checks with broader scope than table comparison. It can serve as a complement to audit-helper for teams that want ongoing validation beyond migration windows.

For most teams, audit-helper covers the validation workflow well. It’s free, maintained by dbt Labs, and sits naturally inside your existing dbt project. The progressive approach (schema, then counts, then rows, then columns) catches issues early without burning through warehouse credits on unnecessary full-table comparisons.