# How do you test your data?

**URL:** <https://discourse.getdbt.com/t/how-do-you-test-your-data/149>\
**Category:** In-Depth Discussions\
**Tags:** testing\
**Created:** [October 24, 2018, 1:52pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149 "2018-10-24T13:52:42Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![joshtemple](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joshtemple/32/62_2.png) [@joshtemple](https://discourse.getdbt.com/u/joshtemple)\
**Post date:** [October 24, 2018, 1:52pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/1 "2018-10-24T13:52:42Z")

</div>

I’m curious to hear about approaches to testing data to ensure quality. Many of us in the data world are not trained software engineers and aren’t as familiar with things like CI/CD, unit or integration tests, separate testing environments, or containers.

If you use any creative dbt testing approaches, continuous integration/deployment, automation of SQL tests through a workflow tool like Airflow, or otherwise, I would love to hear about it! How do you test?

---

<div class="post-metadata">

**Author:** ![kriselle](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/kriselle/32/40_2.png) [@kriselle](https://discourse.getdbt.com/u/kriselle)\
**Post date:** [October 24, 2018, 3:20pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/2 "2018-10-24T15:20:18Z")

</div>

This is a really good question, and something that we’ve been asking ourselves more recently. We add the typical unique/not null test constraints, and some relationship tests between our models. A more recent “test type” we’ve incorporated are regression tests. This is especially useful for us when we find a bug in our code with unintended consequences. Before attempting to “fix” the issue we write a test that catches the bug and then attempt to fix the code, which is similar to test-driven development.

---

<div class="post-metadata">

**Author:** ![drew](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/drew/32/9_2.png) [@drew](https://discourse.getdbt.com/u/drew)\
**Post date:** [October 24, 2018, 3:57pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/3 "2018-10-24T15:57:22Z")

</div>

Hey @joshtemple - we use testing in a few different capacities.

1. During development

When creating or modifying a table, I like to add tests for some invariants, like the uniqueness or non-nullness of a given column. This helps me gain confidence that the code which I am writing works as intended. @kriselle makes a great point about TDD above too. For well-defined models, I like to write my tests _first_, then build a model around the tests. This flow helps reduce the friction around merging PRs… more on that below.

1. During PRs

We’ve built build-on-PR functionality into sinter. Whenever a Pull Request is made against our internal-analytics repo, Sinter will kick off a full run & test of our dbt project in a scratch schema. If any of the models or tests fail, then Sinter puts a big red X on the Pull Request. If everything works as intended, then Sinter shows a nice green check mark. The contextualized test success/failures help guide code review, and acts as a last check before code gets merged into production. In practice, it looks like this:

![46%20AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/83957c57b9ae03db18b1be1cc90a924d29e2d371.png)

1. In production

We also use Sinter to run our models on a scheduled basis. A typical deployment of dbt includes running:

```auto
dbt seed
dbt run
dbt test

```

So, after every invocation of `dbt run`, we also run all of the tests in the project. If any of these steps fail, we get email notifications from Sinter. This helps alerts us when bad/invalid data has arrived from a data source, or when some logic in a model doesn’t hold up over time.

There’s lots more to say about testing, deployment, CI, etc. I know many dbt users are doing pretty interesting/complex things. You should cross-post this to #general in Slack 🙂

---

<div class="post-metadata">

**Author:** ![emilie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/emilie/32/23_2.png) [@emilie](https://discourse.getdbt.com/u/emilie)\
**Post date:** [October 31, 2018, 10:52am UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/4 "2018-10-31T10:52:16Z")

</div>

I would like to echo Drew’s sentiments above. I would like to add two thoughts.

**During Development**

Before working on an existing model, I like to take a couple of key metrics about it- things like row count, sum of key columns (e.g. revenue), and count of distinct values in other columns. If the number of rows in the beginning and the end of the development process don’t match, that’s fine, but I need to be able to justify the change.

**After Development**  
_This is not yet something we’ve implemented, but it’s something we hope to implement soon._

I think a summary metrics table is a good idea. Pick 10 or so key business metrics from a bunch of different data sources and decide on some tolerance for change. If yesterday’s values are outside of that tolerance for change, have a test fail. A little bit of noise (too rapid growth? slow holiday weekend? etc) is better than a problem with data that your end-user discovers first.

I’ve spent a lot of time lately thinking about _what CI looks like for data_. Thanks for starting the conversation.

---

<div class="post-metadata">

**Author:** ![joshtemple](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joshtemple/32/62_2.png) [@joshtemple](https://discourse.getdbt.com/u/joshtemple)\
**Post date:** [October 31, 2018, 2:02pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/5 "2018-10-31T14:02:06Z")

</div>

Thank you all for your input!

We have a similar setup where we use GitLab CI to create and test the entire data model in a build schema before merging into master. We also trigger our GitLab pipeline for daily runs after our EL jobs finish, which executes `dbt seed/run/test` in a build schema, then `dbt seed/run` in production if the tests pass.

**This is helpful if issues with newly loaded source data cause a test to fail. The side effect of this is that if a single test fails, we don’t update our production schema at all, since we don’t have a good way to pass/fail specific models and promote the results to production.** I would be interested to hear if anyone else has thought of a better way to do that.

@emilie, I like your idea of checking a few “health” metrics during and after development. At a previous company, we had a daily email that displayed the difference in record count between today and yesterday for each table. Even that simple count helped us catch a lot of issues.

---

<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:** [November 6, 2018, 4:26pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/6 "2018-11-06T16:26:42Z")

</div>

@joshtemple – you might be interested in checking out [this issue](https://github.com/fishtown-analytics/dbt/issues/1054), there’s a fair bit of discussion about how dbt might handle this in the future! Feel free to contribute to the discussion there as well 🙂

---

<div class="post-metadata">

**Author:** ![gordonwong](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/gordonwong/32/135_2.png) [@gordonwong](https://discourse.getdbt.com/u/gordonwong)\
**Post date:** [May 13, 2019, 1:41pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/7 "2019-05-13T13:41:52Z")

</div>

I’ve thought about this a lot too and I’ve come to the conclusion that testing is a data analysis problem…something we are all familiar with! To that end, I like to think about instrumentation, storage and analysis as discrete problems with testing. Something gets the data, the data is stored and then you figure out what it means. Keeping it modular let’s us evolve each piece as we need to. So that noise you referenced earlier…over time our analysis should learn to deal with that

---

<div class="post-metadata">

**Author:** ![ian-whitestone](https://avatars.discourse-cdn.com/v4/letter/i/c6cbf5/32.png) [@ian-whitestone](https://discourse.getdbt.com/u/ian-whitestone)\
**Post date:** [May 3, 2021, 3:34pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/8 "2021-05-03T15:34:03Z")

</div>

Hey folks 👋 - I recently wrote a post on this topic sharing one approach I’ve used in the past. May be of interest to this group: [Testing SQL – Ian Whitestone](https://ianwhitestone.work/testing-sql/)

---

<div class="post-metadata">

**Author:** ![sris1986](https://avatars.discourse-cdn.com/v4/letter/s/97f17d/32.png) [@sris1986](https://discourse.getdbt.com/u/sris1986)\
**Post date:** [July 7, 2021, 6:09pm UTC](https://discourse.getdbt.com/t/how-do-you-test-your-data/149/9 "2021-07-07T18:09:57Z")

</div>

@joshtemple - Can you please share more details on the below?

We have a similar setup where we use GitLab CI to create and test the entire data model in a build schema before merging into master. We also trigger our GitLab pipeline for daily runs after our EL jobs finish, which executes `dbt seed/run/test` in a build schema, then `dbt seed/run` in production if the tests pass.