Implementing Data Auditing with dbt Tests

In this lesson, you will learn how to implement data auditing by using dbt tests to compare row counts and checksums between your source systems and your final warehouse models.

The Auditing Challenge

When moving data from a source (like an operational database) to a target warehouse model, data can sometimes be lost or corrupted due to filter logic, joins, or unexpected transformations. To guarantee data parity, we perform "Reconciliation Auditing." Instead of just checking if a column is not_null, we verify that the total volume and value of the data remain consistent across the pipeline.

Implementing Row Count Audits

The simplest way to audit is to ensure the number of records matches. We can create a singular test in your tests/ directory that joins the source count with your model count.

-- tests/assert_row_counts_match.sql
with source_count as (
    select count(*) as cnt from {{ source('raw', 'orders') }}
),
model_count as (
    select count(*) as cnt from {{ ref('stg_orders') }}
)
select *
from source_count
cross join model_count
where source_count.cnt != model_count.cnt

Implementing Checksum Audits

Row counts can be deceptive—what if you have the right number of rows, but the values inside are wrong? We use checksums to validate the data. By summing a critical numeric column (like order_amount) in both the source and the transformed model, you create a "fingerprint" of the data's integrity.

-- tests/assert_amounts_match.sql
with source_sum as (
    select sum(order_amount) as total from {{ source('raw', 'orders') }}
),
model_sum as (
    select sum(order_amount) as total from {{ ref('stg_orders') }}
)
select *
from source_sum
cross join model_sum
-- Allow for a small float precision difference
where abs(source_sum.total - model_sum.total) > 0.01

If these tests fail during dbt test, it acts as an alarm, signaling that your pipeline has introduced data loss or transformation errors that need immediate investigation. This ensures that the stakeholders relying on your reports can trust that the data in the warehouse is a faithful reflection of the source.

Key Takeaway: Data auditing using row counts and checksum tests provides a mathematical guarantee of data parity between your raw sources and your final analytical models.