# DBT Test : How to avoid missing values in source data ?

**URL:** <https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891>\
**Category:** Help\
**Tags:** testing, dbt-cloud\
**Created:** [February 5, 2024, 10:46am UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891 "2024-02-05T10:46:36Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![anaec](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@anaec](https://discourse.getdbt.com/u/anaec)\
**Post date:** [February 5, 2024, 10:46am UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/1 "2024-02-05T10:46:36Z")

</div>

## The problem I’m having

I am trying to implement a test that can check if there is missing values in the source table.

## The context of why I’m trying to do this

I am currently implementing new test to my dbt models that going to feed my dashboards. My users need kpis in order to get an overview of the company performance.  
Last week, I got a missing value issue. All the kpis from the dashboards did not break but the kpis were wrong (because there was values in the model). Hopefully, I manage to see the error, but I want to avoid this missing value error without having to check the data everyday.

## What I’ve already tried

I tried to build custom singular test that checks if all the ids from my stg table are in my source. Unfortunately, dbt run the test after running the stg model.  
Here is the test I was trying to do :

```auto
select id from {{ ref('stg_table') }}
where id not in (select id from {{ source('source_table') }})

```

Long story short, I want to check if all my id from the stg table are in my source table before running the stg model. So, if my ids are not in the source table, I know that I have missing values in the source table.

How can I check that there is no missing values in my sources tables ?

Thank you for your time ! 😁

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [February 5, 2024, 1:22pm UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/2 "2024-02-05T13:22:00Z")

</div>

HI @anaec, I see the following options

1 - You can simply run `dbt test` before `dbt run`, so you test before running

2 - You can create a snapshot for the source table with the `invalidate_hard_deletes` config enabled ([invalidate\_hard\_deletes | dbt Developer Hub](https://docs.getdbt.com/reference/resource-configs/invalidate_hard_deletes)). Then your stg model can reference the snapshot

Does any of these options make sense for your problem?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [February 6, 2024, 5:55am UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/3 "2024-02-06T05:55:11Z")

</div>

I think you should consider the audit\_helper package. It has a compare\_queries test that can show you if you are missing records either on query A or query B. You can set a ‘error’ on the test so it will not continue when the test fails. You can even store the missing values in a table if needed.

[https://hub.getdbt.com/dbt-labs/audit\_helper/latest/](https://hub.getdbt.com/dbt-labs/audit_helper/latest/)

example:

Note: `@Maikel Korff` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1707198902077259?thread_ts=1707130017.911929&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![anaec](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@anaec](https://discourse.getdbt.com/u/anaec)\
**Post date:** [February 9, 2024, 2:15pm UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/4 "2024-02-09T14:15:01Z")

</div>

Thank you so much for your different options !  
I used the simple trick of running the dbt test before dbt run for my specific generic test (using tags). It was easy to make an it works well !  
And I will probably try to implement audit helper later in my dbt project because it seems to be a great option.

Have a great day !

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [February 9, 2024, 6:23pm UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/5 "2024-02-09T18:23:20Z")

</div>

Awesome, @anaec ! Could you mark the answer as solution? Thanks!! 😄

---

<div class="post-metadata">

**Author:** ![shivaramp](https://avatars.discourse-cdn.com/v4/letter/s/c89c15/32.png) [@shivaramp](https://discourse.getdbt.com/u/shivaramp)\
**Post date:** [February 11, 2024, 10:01pm UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/6 "2024-02-11T22:01:25Z")

</div>

I think if you check all those id’s before data load trigger using run\_query() and create if condition to check all id present. If so then start data load trigger else raise error

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [February 18, 2024, 10:02pm UTC](https://discourse.getdbt.com/t/dbt-test-how-to-avoid-missing-values-in-source-data/11891/7 "2024-02-18T22:02:08Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.