Ensure data quality with dbt tests
Reliable data is the basis of every marketing decision. In this article, we show you how to use dbt tests and freshness checks to ensure that your dashboards are correct and up-to-date at all times.
In the first article of this series forBI Managers, Data Analysts, Analytics Engineers & Co., we explored how dbt simplifies complex SQL workflows, making marketing data pipelines easier to manage and scale. In this post, we’ll look at how dbt builds trust in your data through testing and freshness checks.
For marketing and advertising teams, nothing undermines trust faster than unreliable dashboards or inconsistent KPIs. Yet ensuring data quality across multiple platforms is incredibly difficult, especially when relying on custom SQL scripts and manual checks.
With dbt, you can take data quality to a new level and integrate it into standard processes. We will show you how the tests can become part of your transformation pipeline.
dbt test: How to successfully set it up
In dbt, you define data tests directly in .yml files next to your models. No need to write custom SQL or set up separate monitoring tools. These tests check for common issues like:
- unique: Ensures no duplicates in key columns (e.g., campaign IDs)
- not_null: Makes sure critical fields (like spend or conversions) are always populated
- accepted_values: Validates that a column contains only expected entries (e.g., channel = “facebook”, “google”)
- relationships: Enforces referential integrity across tables (e.g., campaign_id exists in both spend and impressions tables)
For more complex validation, dbt integrates with the dbt_utils package, which is a library of reusable test macros. This is especially powerful in marketing analytics, where multiple platforms feed into a single funnel or attribution model.
Finally, dbt can also track how “fresh” your data sources are. By setting freshness expectations in sources.yml, you can for example flag when a source table hasn’t updated (e.g., GA4 data missing for the last 48 hours) and trigger alerts if pipelines break or campaign data is delayed. This ensures that reporting and client presentations are not disrupted by unexpected data gaps.
Use freshness checks to check that everything is up to date
In addition to data quality, data timeliness is also crucial, for example when dashboards are regularly updated at short intervals or current results are expected in customer meetings.
dbt can track how “fresh” your data sources are. By setting freshness expectations in sources.yml, you can for example flag when a source table hasn’t updated (e.g., GA4 data missing for the last 48 hours) and trigger alerts if pipelines break or campaign data is delayed. This ensures that reporting is not disrupted by unexpected data gaps.
Here is an example code:
sources:
- name: data_stack_sources
database: db
schema: raw
tables:
- name: facebook_ads
freshness:
warn_after: { count: 24, period: hour }
error_after: { count: 48, period: hour }
loaded_at_field: updated_at
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- campaign_id
- date
- not_null:
column_name: campaign_idConclusion: With dbt you can ensure trust in your marketing data
Marketing and sales teams need reliable KPIs. With dbt’s integrated test and update functions, you create the basis for this: automated and scalable.
As a boutique consultancy with almost 20 years of experience in marketing analytics, we support our clients with exactly that: clean data, comprehensible transformations, better decisions. Do you want to know how well your marketing data is set up? We are happy to help you build robust, trustworthy data pipelines.