🚦Data Quality Framework with dbt: Tips, Tricks & Examples

Elite data quality doesn’t happen by accident—it’s the result of intentional testing, smart monitoring, and ruthless automation. Thankfully, with dbt, you can build an airtight data quality framework. And with Elementary, you can take that framework to a whole new level of visibility and trust.

Here’s how to level-up your pipelines with dbt + Elementary: built-in tests, custom tests, real-world examples, and advanced observability.

🧱 Built-in dbt Tests – Your First Line of Defense

These are your foundational guards against dirty data.

1. not_null

columns:
  - name: user_id
    tests:
      - not_null

2. unique

columns:
  - name: order_id
    tests:
      - unique

3. accepted_values

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['active', 'inactive', 'pending']

4. relationships

columns:
  - name: customer_id
    tests:
      - relationships:
          to: ref('dim_customers')
          field: id

🛠️ Custom Tests for Business Logic

When the built-ins don’t cut it, you roll your own.

✅ Positive Values Test

{% test positive_values(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0
{% endtest %}

columns:
  - name: price
    tests:
      - positive_values

🗓 Dates Must Be in the Past

{% test past_date(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} > current_date
{% endtest %}

🔁 Status-Churn Logic

{% test churned_users_have_churn_date(model) %}
SELECT *
FROM {{ model }}
WHERE status = 'churned' AND churn_date IS NULL
{% endtest %}

🔍 Using Elementary to Monitor & Visualize Tests

Elementary is a powerful open-source tool that analyzes dbt artifacts (run_results.json, manifest.json) and generates a full data observability report.

🔥 What it does:

• Tracks test failures over time

• Groups failures by model/test/type

• Highlights freshness issues, row count anomalies, and schema changes

• Lets you drill into failed rows

• Sends Slack/Email alerts on failures

• Automatically publishes a sleek web UI or pushes to dbt Cloud/S3

🚀 Tips for Going Pro

• Use severity: warn for soft failures:

tests:
  - unique:
      severity: warn

• Run in CI/CD pipelines (GitHub Actions, GitLab, dbt Cloud).

• Track test coverage using manifest.json and visualize it in Elementary.

• Integrate with Airflow or orchestrators to auto-monitor tests daily.

🎯 Final Thoughts

Combining dbt’s test framework with Elementary’s observability layer creates a best-in-class data quality system:

 ✅ Built-in tests
 ✅ Custom macros
 ✅ Alerts and dashboards
 ✅ Stakeholder visibility

Whether you're just getting started or scaling out a mature data platform, this duo will help you deliver clean, reliable, and trusted data—consistently.