# How to not lose your mind when auditing data

**URL:** <https://discourse.getdbt.com/t/how-to-not-lose-your-mind-when-auditing-data/445>\
**Category:** Show and Tell\
**Tags:** data-quality\
**Created:** [July 11, 2019, 12:05pm UTC](https://discourse.getdbt.com/t/how-to-not-lose-your-mind-when-auditing-data/445 "2019-07-11T12:05:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![claire](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claire/32/522_2.png) [@claire](https://discourse.getdbt.com/u/claire)\
**Post date:** [July 11, 2019, 12:05pm UTC](https://discourse.getdbt.com/t/how-to-not-lose-your-mind-when-auditing-data/445/1 "2019-07-11T12:05:52Z")

</div>

TL:DR; We’ve got a new package, [audit\_helper](https://hub.getdbt.com/fishtown-analytics/audit_helper/latest/) to help with data audits.

# Auditing data: my least favorite task in analytics

As a data analyst, there’s one task that used to instill more dread in me than any other – _auditing data_ 😩. This task generally pops up in one of two situations:

- I’m refactoring the SQL of an existing dbt model and want to make sure I haven’t introduced a regression
- I’m creating dbt models that replace a data transformation that’s happening somewhere outside of dbt (and sometimes not in SQL!), and need to show my stakeholder that my new model is a like-for-like replacement.

Sure, you get to feel a bit like a detective, but in reality, I felt this dread for two reasons:

1. The SQL involved in auditing data is often _harder_ than the SQL for building models – writing a query to compare two relations record-by-record uses `intersect` and `except`, a query to compare relation structures requires querying `information_schema.columns` (unless you’re using a Redshift [late binding view](https://discourse.getdbt.com/t/validating-the-sql-in-a-redshift-late-binding-view/193), in which case you also need to `select pg_get_late_binding_view_cols()` 🤦‍♂️) .
2. And it’s also _tedious_, bordering on frustrating – if your audit doesn’t match up, it can be really hard to track down exactly why that is – finding the exact records that don’t match can be difficult, and finding the SQL that has introduced the regression can be even more so! (Especially if you leave it to the end.)

But it’s also a _really important_ skill. When I’ve opened up a PR that has a “validation query”, it demonstrates to my reviewer (and to me!) that my work is rock solid, meaning we can get the work merged in more quickly. Here’s a screenshot from a recent PR that I opened:

 ![53%20AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/5fb971e58d6fc36d395c72023709d0b4d95c3cd7.png)  
(I was also able to demonstrate _exactly why_ those 45 records didn’t match.)

# Zen and the art of data auditing

I recently changed my workflow around data audits, borrowing one of the best practices from software engineering development – test driven development.

When refactoring an existing dbt model, rather than making all my changes, and then checking _at the end_ that my models matched up, I did the following:

1. Write an audit query (my test!) _first._
2. Run a development version of your model without making any changes, and check that the dev and prod versions of your model match up (they should! You haven’t made any changes yet!)
3. Refactor a small piece of the model.
4. Run the model.
5. Run the audit query.
6. If the query doesn’t pass, debug. Once it passes, commit.
7. Repeat steps 3 through 6.

I felt Zen as I did this, I worked quickly, and I got the tricky part (writing the audit query) out of the way first! This tackled the “tedious” aspect of the problem, but that audit query was still more difficult than it needed to be!

# Introducing the `audit_helper` package

I’ve packaged up my most used audit queries into a new dbt package, [audit\_helper](https://hub.getdbt.com/fishtown-analytics/audit_helper/latest/) (new to dbt packages? Check the docs [here](https://docs.getdbt.com/docs/package-management)!).

There’s two macros in here that are super useful:

- `compare_relations`: This macro generates SQL that can be used to do a row-by-row validation of two relations.
- `compare_queries`: This macro generates SQL that can be used to do a row-by-row validation of two _queries_ – sometimes when refactoring, I change a column name, or intentionally change the values in the column (e.g. coalescing NULLs with 0s). By comparing the results of two queries, I can take these changes into account!

Importantly, both of these macros are written in a way that means they can be reused across warehouses and projects, and the SQL in them is written in a way that helps you debug! 🧘‍♀️🕉

In the past, we’ve written lots of macros that are intended to become part of your project; whether they are used in models, custom schema tests, hooks, or operations, they end up living in the version controlled code. Here, we’re writing a _new kind_ of macro; a macro that you should use while developing, but not past that. And the cool thing here is that we recently released a great interface for these class of macros – [dbt Develop](https://docs.getdbt.com/docs/cloud-developer-ide).

Here’s a quick demo of how you can use it in dbt Develop!

In this video, you can see:

1. My project has the `audit_helper` package in the `packages.yml` file.
2. I used the `audit_helper.compare_relations` macro to generate a query to compare an existing table, with a model (yup! I could `ref`!).

```auto
{% set old_etl_relation=adapter.get_relation(
      database=target.database,
      schema="old_etl_schema",
      identifier="stg_jaffle_shop__orders" )%}

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

{{ audit_helper.compare_relations(
    a_relation=old_etl_relation,
    b_relation=dbt_relation,
    exclude_columns=["loaded_at"],
    primary_key="order_id"
) }} 

```

1. Two of my records didn’t match perfectly:

| in\_a | in\_b | count |
| --- | --- | --- |
| true | true | 97 |
| true | false | 2 |
| false | true | 2 |

1. Fortunately I could copy/paste/uncomment the compiled SQL to find those exact records that didn’t match to help me debug!

| order\_id | customer\_id | order\_date | status | in\_a | in\_b |
| --- | --- | --- | --- | --- | --- |
| 44 | 66 | 2018-02-16 | completed | true | false |
| 44 | 66 | 2018-02-17 | completed | false | true |
| 56 | 79 | 2018-02-28 | returned | true | false |
| 56 | 79 | 2018-02-28 | completed | false | true |

# Planned changes

This package is very much a `v0.0.1` – we intend to polish up the existing macros, and to add to it over time, creating macros that [compare the columns of two relations](https://github.com/fishtown-analytics/dbt-audit-helper/pull/5), the relations within a schema, and more! If you’ve got an audit query that you find useful, PRs are very welcome 😃

Happy auditing!

---

<div class="post-metadata">

**Author:** ![mzaccaron](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mzaccaron](https://discourse.getdbt.com/u/mzaccaron)\
**Post date:** [September 16, 2019, 10:12am UTC](https://discourse.getdbt.com/t/how-to-not-lose-your-mind-when-auditing-data/445/2 "2019-09-16T10:12:04Z")

</div>

Love this, thanks to the contributors!