dbt-audit-helper

Since .print_table() is incompatible with dbt Cloud, use this pattern in a new macro file to iterate over columns and rows, logging them explicitly to the console. You can run this macro using dbt run-operation <macro_name>().

{% macro print_audit_output() %}
{%- set columns_to_compare=adapter.get_columns_in_relation(ref('fct_orders'))  -%}

{% set old_etl_relation_query %}
    select * from public.dim_product
{% endset %}

{% set new_etl_relation_query %}
    select * from {{ ref('fct_orders') }}
{% endset %}

{% if execute %}
    {% for column in columns_to_compare %}
        {{ log('Comparing column "' ~ column.name ~'"', info=True) }}
        {% set audit_query = audit_helper.compare_column_values(
                a_query=old_etl_relation_query,
                b_query=new_etl_relation_query,
                primary_key="order_id",
                column_to_compare=column.name
        ) %}

        {% set audit_results = run_query(audit_query) %}

        {% do log(audit_results.column_names, info=True) %}
            {% for row in audit_results.rows %}
                  {% do log(row.values(), info=True) %}
            {% endfor %}
    {% endfor %}
{% endif %}
{% endmacro %}

You can wrap audit helper macros (like compare_all_columns) inside a custom test in your tests/ directory to protect against data regressions.

Requirements: The model being tested must have a primary key that is reliably unique and not_null. It is recommended to use standard dbt tests to enforce these constraints first.

Failure Logic:

• Use where not perfect_match to fail if any rows have nulls in a column, missing primary keys, or conflicting values.
• Use where conflicting_values to fail only when values between the two relations differ.

You can also left join the model being tested to the audit macro output to include model attributes in the test results for easier debugging.

{{ 
  audit_helper.compare_all_columns(
    a_relation=ref('stg_customers'),
    b_relation=api.Relation.create(database='dbt_db', schema='analytics_prod', identifier='stg_customers'), 
    exclude_columns=['updated_at'], 
    primary_key='id'
  ) 
}}
where not perfect_match

The compare_which_relation_columns_differ macro is a wrapper for compare_which_query_columns_differ that accepts dbt Relations instead of raw SQL queries.

Key Behaviors:

• Relations must have the same column names, but column order does not matter.
• If columns is set to None, the macro automatically finds all intersecting columns.

Arguments:

• a_relation: The first relation.
• b_relation: The second relation.
• primary_key_columns (required): A list of primary key column(s).
• columns (optional): A list of columns to compare. Pass None for automatic intersection.

{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
)
-%}

{% set dbt_relation = ref('fct_orders') %}

{{ audit_helper.compare_which_relation_columns_differ(
    a_relation = old_relation,
    b_relation = dbt_relation,
    primary_key_columns = ["order_id"],
    columns = None
) }}

The compare_and_classify_query_results macro generates a row-by-row comparison between two SQL queries. It provides summary statistics for records that are added, removed, identical, or modified.

This macro is useful for seeing exactly which records changed and getting a high-level count of differences in a single result set.

Output Columns:

• dbt_audit_in_a / dbt_audit_in_b: Boolean indicating presence in query A or B.
• dbt_audit_row_status: The classification (identical, modified, added, or removed).
• dbt_audit_num_rows_in_status: The count of primary keys in that status (counts each PK only once).
• dbt_audit_sample_number: A sample index for the records returned.

{% set old_query %} 
  select id as order_id, amount, customer_id from old_database.old_schema.fct_orders 
{% endset %}

{% set new_query %} 
  select order_id, amount, customer_id from {{ ref('fct_orders') }} 
{% endset %}

{{ 
  audit_helper.compare_and_classify_query_results(
    a_query=old_query, 
    b_query=new_query, 
    primary_key_columns=['order_id'], 
    columns=['order_id', 'amount', 'customer_id']
  )
}}

The compare_and_classify_relation_rows macro is a wrapper around compare_which_query_columns_differ that accepts two dbt Relations instead of raw SQL queries.

Requirements:

• Both relations must have the same column names (though order does not matter).

Arguments:

• a_relation and b_relation: The two relations to compare.
• primary_key_columns (required): List of columns used to join the relations.
• columns (optional): List of columns to compare. If None, the macro automatically finds all intersecting columns.

{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
) -%}

{{ audit_helper.compare_and_classify_relation_rows(
    a_relation = old_relation,
    b_relation = ref('fct_orders'),
    primary_key_columns = ["order_id"],
    columns = None
) }}

Use compare_column_values to understand the nature of discrepancies in a specific column after identifying that a column differs. It provides a summary of how many rows match, differ, or have null/missing values.

Output Summary includes:

• Perfect matches
• Both are null
• Missing from a or b
• Null in a only or b only
• Values that do not match

Arguments:

• a_query: The first query.
• b_query: The second query.
• primary_key (required): A unique, non-null key used for joining. Must be unique in both sets.
• column_to_compare (required): The specific column to analyze.
• emojis (optional): Boolean (defaults to true) to include visual indicators (✅, 🤷, ❌).
• a_relation_name / b_relation_name (optional): Custom labels for the output (defaults to a and b).

{% set old_query %}
    select * from old_database.old_schema.dim_product
    where is_latest
{% endset %}

{% set new_query %}
    select * from {{ ref('dim_product') }}
{% endset %}

{{ audit_helper.compare_column_values(
    a_query = old_query,
    b_query = new_query,
    primary_key = "product_id",
    column_to_compare = "status"
) }}

The compare_all_columns macro compares the values of all columns across two relations. This is useful for diagnosing widespread discrepancies found by compare_queries.

Modes of Operation:

• Summary Mode (default): Returns a count of rows for each column categorized by match status (perfect match, null in a, null in b, etc.).
• Detailed Mode: If summarize is set to false, it returns a row-by-row breakdown of match statuses for specific primary keys.

Arguments:

• a_relation (required): The first relation.
• b_relation (required): The second relation.
• primary_key (required): A unique, non-null key (or concatenated SQL) used for joining.
• exclude_columns (optional): Columns to skip during validation.
• summarize (optional): Boolean (defaults to true) to toggle between summary and detailed views.

{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
) -%}

{% set dbt_relation = ref('fct_orders') %}

{{ audit_helper.compare_all_columns(
    a_relation = old_relation,
    b_relation = dbt_relation,
    primary_key = "order_id"
) }}