Every successful analytics platform is built on trust. Trust in your data being accurate, consistent, and ready to drive decision-making. At Stellans, we’ve spent years working with analytics teams who rely on dbt to transform, test, and deliver business-critical insights. Even mature dbt projects can encounter brittle tests, unclear documentation, or quality issues sneaking into production.

This guide showcases practical, production-ready dbt testing patterns for null values, accepted values (domain constraints), and relationships (referential integrity), all paired with actionable code templates. By the end, you’ll not only understand the why behind each pattern, but excitedly apply the how: robust YAML and macro-driven solutions, tips for scaling tests in CI, and guidance for real-world auditability and compliance.

Prioritizing data quality and reliable analytics will help your dbt project become a well-oiled data machine. Unlock faster time-to-insight and reduce firefighting for your team.

dbt provides several built-in generic tests (official docs):

• not_null: Ensures a column contains no nulls.
• unique: Confirms all values in a column are distinct.
• accepted_values: Checks that column values fall within a specified list (enum/domain checks).
• relationships: Validates that foreign key references exist in the parent table, preserving referential integrity.

You can also define custom tests in SQL or as reusable macros for nuanced business logic (for example, “order_date can’t be in the future” or “revenue must be positive”).

Why Test? Technical Integrity vs. Business Logic

Testing in dbt goes beyond technical errors. By enforcing business contracts, you create rules that must always be true for your data to be trusted. Teams benefit by:

• Catching data pipeline issues before dashboards break.
• Satisfying compliance regimes like GDPR, CCPA, or SOX.
• Improving data team velocity thanks to immediate feedback on code changes.
• Enabling automated deployments and safer, scalable analytics workflows.

Missing or unexpected nulls are among the most notorious data quality issues and can cause broken joins, inaccurate aggregations, or failed analytics downstream.

Built-in not_null Test Example

Start with the basics: dbt’s not_null test in a schema.yml file (applies to any model column).

dbt schema.yml null test example

Tip: Keep schema files with each model for discoverability and audit purposes. This consistent structure reduces debugging time by 40% (See organization tips).

Custom Not Null Patterns

Sometimes a value should be non-null only when another field matches certain criteria (for example, cancelled_at should be non-null only if status = ‘cancelled’). Here’s a custom test for that—a macro in your tests/generic/ folder:

Conditional not null dbt macro and YAML usage

Running Null Checks in CI/CD

Catch schema drift and nulls before production. Always run dbt test in your CI pipeline.

Example GitHub Actions snippet (official guide):

Running dbt tests in CI GitHub Actions workflow

Fail fast, fail early, and protect business-critical models from bad nulls.

Business rules often require that certain columns use a valid set or numeric range (order statuses, rates, regulatory codes). dbt makes domain enforcement repeatable and auditable.

Enumerated Value Test

The built-in accepted_values test is perfect for enumerations:

Accepted values custom test YAML snippet

Pro-tip: In audits or regulatory reviews, these tests show your data only contains valid domains (learn about compliance).

Ranges and Conditional Values

For continuous data (numeric ranges, dates), custom tests from dbt_expectations make enforcement modular:

Or a custom macro for more complex logic:

And reference in your model’s schema as needed.

Documenting Accepted Values for Audits

Document domain constraints in your YAML model docs:

Well-documented tests provide clear data contracts and speed up compliance audits.

When models reference other models (join keys, surrogate keys, foreign keys), referential integrity maintains trust and accuracy in reporting.

Built-in relationships Test Example

The relationships test ensures, for example, every order.customer_id exists in customers:

dbt relationships test YAML example

Cross-Table Relationships & NULL Handling

Sometimes, references are mandatory only for certain rows. Example: affiliate_id is required if is_affiliate=true. Use where conditions (from dbt 1.5+):

Conditional relationships test in dbt YAML

Common Pitfalls and Best Practices

• Null FKs: Combine not_null with relationships when a join key can’t be null.
• Performance: Limit expensive cross-table checks; focus on critical models.
• Custom logic: Macros help handle unusual business constraints.

See more robust patterns in “dbt consulting best practices” (see Stellans dbt conventions).

At the enterprise level, you need automation, monitoring, and enablement for ongoing data quality.

CI/CD Integration Patterns

Automate test runs on every pull request, branch, or deployment (GitHub Actions official docs). Use granular selection:

Run only impacted models to speed feedback and reduce costs.

Test Severity, Triage, and Ownership

• Use severity configs (warn, error) to control flow.
• Integrate test results with Slack, email, or ticketing (PagerDuty/ServiceNow).
• Assign test ownership in YAML for better accountability over key domains.

Templates and Modularity for Team Scaling

• Store custom tests in /tests/generic/ for easy reuse.
• Use packages (dbt_utils, dbt_expectations) for advanced logic.
• Document every test’s purpose, expected values, and failure case.

Modular tests mean faster onboarding and consistent coverage as your team grows. Stellans clients have seen 40% fewer critical data issues with this approach (see DataOps case study).

YAML and Macro Snippets for All Patterns

Use these snippets in your own projects:

Using dbt_expectations & dbt-utils for Advanced Cases

dbt_expectations: Create expressive checks (percent null, regex, percentile checks).
dbt-utils: Use common macros (unexpected values, cross-model checks).

Audit-Ready Documentation Generation

Generate docs with dbt docs generate so test coverage and model details are visible to non-engineers and auditors.

Comparative table of dbt test practices and Stellans features

Data Contracts & Regulatory Considerations

As teams treat data as a product, test coverage and clear domain contracts are essential for legal compliance (GDPR/CCPA), audit trails, and customer trust.

Read more about data governance and compliance here.

Integrating Observability Tools with dbt

By adding data observability platforms, automated anomaly detection, and end-to-end lineage, you create a robust analytics foundation. Strong dbt test design gives you a dependable first line of defense.

How do dbt tests work?
dbt tests run assertions on your data models, sources, and snapshots. These produce pass/fail results and can be automated in CI/CD workflows to catch errors ahead of production. Learn more.

Which types of tests are built into dbt?
dbt includes not_null, unique, accepted_values, and relationships as generic tests. You can supplement these with custom business tests.

How do you run dbt tests in CI/CD?
Add a step to your pipeline to execute dbt test on every code change or pull request. Integration with GitHub Actions and similar tools is straightforward.

What is the row count test in dbt?
A row count test ensures a table/model contains the expected number of rows, so you can catch missing or duplicated data after transformations.

How to unit test dbt macros?
You can unit test macros by building a test SQL model that uses your macro with sample input data, then verify outputs with dbt’s test framework.

Delivering reliable analytics starts with solid dbt testing patterns. Null checks, accepted values, and relationships act as your first defense against broken pipelines and reporting errors. Focus on modularizing tests, running them in CI/CD, documenting ownership, and aligning with compliance for peace of mind in scale.

Ready to operationalize world-class dbt testing and data quality? Discover our analytics engineering solutions or contact Stellans for expert consulting. Unlock your data’s full potential and eliminate costly surprises.