Skip to main content

The dbt-audit-helper Package

When a Data Engineer joins a company, they rarely get to build a greenfield project from scratch. Usually, they are tasked with migrating legacy SQL procedures (e.g., thousands of lines of old Stored Procedures) into a clean, modular dbt project.

When you refactor thousands of lines of code, how do you mathematically prove to your boss that your new dbt model produces the exact same numbers as the old legacy system?

You use the dbt-audit-helper package.

Topics Covered​

  • The Refactoring Workflow
  • Installing audit-helper
  • The compare_relations macro

1. Installing the Package​

Like all packages, you must add it to your packages.yml file.

# packages.yml
packages:
- package: dbt-labs/audit_helper
version: [">=0.11.0", "<0.12.0"] # Check dbt Hub for the latest version

(Run dbt deps to download it).

2. The compare_relations Macro​

Let's assume an Indian eCommerce company has a legacy table called LEGACY_DB.FINANCE.fct_daily_sales.

Rohan has spent the last week refactoring this logic into a beautiful dbt model called fct_sales, which is currently sitting in his development sandbox: BEEMS_DEV.DEV_ROHAN.fct_sales.

Before Rohan merges his code into Production, he wants to compare his new dbt table against the old legacy table. He can do this by executing the compare_relations macro.

He creates a new Snowflake Worksheet, or runs a dbt run-operation with the following Jinja:

-- Put this inside a scratch file or run-operation
{% set old_relation=adapter.get_relation(
database="LEGACY_DB",
schema="FINANCE",
identifier="fct_daily_sales"
) -%}

{% set new_relation=adapter.get_relation(
database="BEEMS_DEV",
schema="DEV_ROHAN",
identifier="fct_sales"
) -%}

{{ audit_helper.compare_relations(
a_relation=old_relation,
b_relation=new_relation,
primary_key="order_id"
) }}

3. Interpreting the Output​

When Rohan runs that macro, audit_helper generates a massive, complex SQL query behind the scenes that joins the two tables on the order_id and mathematically hashes every single row to check for exact parity.

The output looks like this:

in_ain_bcountpercent_of_total
TrueTrue99,99899.99%
TrueFalse20.01%
FalseTrue00.00%

What does this mean?

  • in_a: True, in_b: True: 99,998 rows match perfectly between the legacy system and Rohan's new dbt model.
  • in_a: True, in_b: False: There are 2 rows that exist in the old legacy table, but are missing from Rohan's new dbt model.

Rohan now knows his refactor is 99.99% correct, but he has a bug causing 2 rows to drop. He can now investigate those specific 2 rows, fix his WHERE clause, and run the audit again until he hits 100% parity!

4. Other Audit Macros​

The audit_helper package has several other macros for deep-diving into discrepancies:

  • compare_queries: Instead of comparing two physical tables, you can write two raw SQL queries and compare their outputs.
  • compare_column_values: If you know that the two tables have the exact same number of rows, but one specific column (e.g., tax_amount) is slightly off, you can use this macro to find exactly which rows have different values for that specific column.

Summary​

  • The dbt-audit-helper package is critical for migrating legacy SQL into dbt.
  • It allows you to mathematically prove parity between two datasets.
  • Use compare_relations to do a full table-to-table comparison based on a Primary Key.
  • Use the output grid to identify missing rows or mismatched logic before deploying to production.