Understanding dbt Custom Tests (Without Losing Your Mind)

Ever wish your data warehouse came with its own early-warning detection system? Something that could spot a bad join, a rogue null, or a sudden spike in duplicates the instant they showed up?

Imagine stretching a web of ultra-sensitive tripwires across every table and column you care about: the moment anything steps out of line, an alarm goes off and points you straight to the problem.

That’s exactly what dbt custom tests do. dbt custom tests are user-defined checks in dbt that help you catch data issues specific to your business logic.

In this guide, we’ll break down the different types of dbt tests, walk through how to build your own, and show you why setting up these “tripwires” is totally worth it.

Types of dbt Tests: Built-In vs. Bespoke

dbt comes with some built-in tests ready to go, like unique, not_null, accepted_values, and relationships. These are great for quick sanity checks. For example, you can use unique to make sure customer IDs don’t repeat, or not_null to confirm that important fields like email or user_id are always filled in.

But here’s the thing: real-world data is messy. And your business logic is rarely one-size-fits-all.

That’s where dbt custom tests come in.

Custom tests are like DIY rules for your data. You write them when the built-in ones aren’t enough. Maybe you want to flag orders over $10,000 that don’t have a sales rep assigned. Or spot users who signed up but never activated their accounts. These are specific to your business, and super easy to write with custom tests.

So, when do you use which? Start with built-in tests to cover the basics. When you find edge cases or see the same issues pop up again and again, it’s time to write a custom one.

How to Create a Custom Test in dbt

Alright, so how do you actually make one of these? Let’s break it down—it’s not as intimidating as it sounds.

Start by heading to your dbt project’s tests directory. That’s where your custom test files live. There, create a new .sql file where you’ll write the logic.

Each test is just a SQL query that returns the bad rows. If the query returns results, the test fails. So think of it like: “Show me the data gremlins.” Here’s a basic example:

SELECT *
FROM orders
WHERE amount > 10000 AND sales_rep_id IS NULL

If any rows come back, dbt will flag it as a failure. No rows? You’re in the clear.

Now, if you find yourself writing this same test logic in multiple places, you’ll want to make it reusable. This is where Jinja (dbt’s templating language) and YAML (a data serialization language like JSON) come into play.

So instead of hardcoding everything, you can write a generic version of your test like this:

SELECT *
FROM {{ ref(model) }}
WHERE {{ args.amount_column }} > {{ args.threshold }}
  AND {{ args.rep_column }} IS NULL

Then, in your schema.yml, you plug in the specifics for each model:

models:
  - name: orders
    tests:
      - high_value_orders_without_reps:
          amount_column: amount
          threshold: 10000
          rep_column: sales_rep_id
  - name: enterprise_orders
    tests:
      - high_value_orders_without_reps:
          amount_column: total_amount
          threshold: 25000
          rep_column: account_manager_id

Same logic, different models. No copy-pasting required. Just clean, flexible testing that scales with your project.

Best Practices for Writing Custom Tests That Actually Help

Writing a test is easy. Writing a good test, one that’s useful, readable, and doesn’t make you regret your life choices at 2 a.m., takes a little more thought. Here are a few tips:

• Keep it simple. Test queries should be easy to read and easy to debug. If your query starts to look like a spaghetti mess of subqueries and CASE statements, consider breaking it up or simplifying the logic.
• Name it clearly. Descriptive names like high_value_orders_without_reps are way more helpful than test1.sql. Add a quick comment to explain why the test exists. Future-you will thank you.
• Avoid noisy failures. Some tests might fail because of upstream delays or late-arriving data, not because anything is actually broken. If that’s the case, think about how to reduce false positives.
• Treat failed tests like they matter. Don’t let failures pile up like unread emails. Make it a habit to check and resolve them regularly, so your test suite stays clean and useful.
• Talk to your team. Focus on tests that actually protect key workflows, metrics, or decisions. Just because you can write a test doesn’t mean you should.

Why dbt Custom Tests + Data Observability = A Dream Team

dbt custom tests are great for catching known issues. But they don’t catch everything.

That’s where data observability tools like Monte Carlo come in. dbt tells you what broke. Monte Carlo helps you figure out why—whether it’s a schema change, missing upstream data, or something else entirely.

Monte Carlo monitors all of your data quality metrics, from freshness to lineage. So when a test fails, you’re not left guessing. You can trace it back to the root cause and fix it before anyone downstream even notices.

Want to see how it all works? Drop your email to book a quick Monte Carlo demo and discover how it can level up your dbt workflow.

Our promise: we will show you the product.

Frequently Asked Questions