dbt Tests And Packages Basics

Why this matters

As an Analytics Engineer, your models feed dashboards and decisions. Tests catch broken joins, null keys, and unexpected values before they hit stakeholders. Packages let you reuse trusted macros and tests instead of reinventing the wheel. Together, they make your dbt project reliable and faster to maintain.

• Real task: Ensure every fact table has valid foreign keys to dimensions.
• Real task: Alert when a status column receives an unexpected value after a deployment.
• Real task: Use community macros (like dbt-utils) to write fewer lines and reduce bugs.

Concept explained simply

In dbt, tests are SQL queries that should return zero rows if everything is OK. Fewer rows returned = better data quality. Packages are bundles of macros, seeds, and sometimes models you can install and reuse.

Mental model

• Tests: safety rails. You describe what must be true (e.g., id is unique). dbt runs queries to prove it.
• Packages: toolkits. You install them once, call their macros everywhere.

Common built-in tests you will use daily

• not_null: Column must not have nulls
• unique: Column values are unique per row
• accepted_values: Column values are in a defined list
• relationships: Column values exist in a parent table (foreign-key-like check)

How tests work in dbt

• Generic tests: Declared in YAML (schema.yml) and applied to columns or models.
• Singular tests: Custom SQL files in the tests/ folder; any rows returned are failures.
• Run tests: dbt test or select specific ones with dbt test -s model_name, dbt test -s test_name, or tags.
• Severity: Configure tests to error or warn. Warns show as failures but do not stop the run.
• Store failures: Configure store_failures: true so failing rows are persisted for review.

Worked examples

Example 1 — Generic tests in schema.yml

Goal: Ensure order_id is unique and not null, status is known, and every customer_id exists in dim_customers.

# models/staging/schema.yml
version: 2
models:
  - name: stg_orders
    description: "Raw orders cleaned for downstream use"
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: status
        tests:
          - accepted_values:
              values: ["placed", "paid", "shipped", "refunded", "cancelled"]
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Run: dbt test -s stg_orders

Example 2 — Singular test SQL

Goal: No negative revenue.

-- tests/no_negative_revenue.sql
select *
from {{ ref('fct_orders') }}
where revenue < 0

Run: dbt test -s test_name:no_negative_revenue (zero rows = pass).

Example 3 — Severity and tags

# models/marts/schema.yml
version: 2
models:
  - name: fct_orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_id, order_line]
        config:
          severity: error
          tags: [critical]
  - name: dim_customers
    columns:
      - name: email
        tests:
          - unique:
              config:
                severity: warn
                tags: [quality]

Run just critical tests: dbt test -s tag:critical

Packages basics

Packages are defined in packages.yml and installed with dbt deps. The most common is dbt-utils, which adds helpful macros and tests.

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.1.0,<2.0.0"  # pin compatible versions

Use a macro from a package:

-- Example usage of dbt_utils.star in a model
select
  {{ dbt_utils.star(from=source('raw', 'orders'), except=['_loaded_at']) }}
from {{ source('raw', 'orders') }}

Use a package-provided generic test:

# models/schema.yml
version: 2
models:
  - name: fct_orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_id, order_line]

Package hygiene tips

• Pin versions to avoid breaking changes.
• Read package docs to know available macros and configuration.
• Keep dbt deps in your project setup steps (e.g., CI).

Step-by-step: Add tests and a package

1. Create or update schema.yml for your model with built-in tests.
2. Add packages.yml with dbt_utils.
3. Run dbt deps to install the package.
4. Reference a macro or test from the package in your YAML or SQL.
5. Run dbt test; review and fix any failures.

Self-checklist

• [ ] Every primary key has unique and not_null tests.
• [ ] Foreign keys use relationships to parent models.
• [ ] Important enums use accepted_values.
• [ ] At least one singular test guards a critical business rule.
• [ ] packages.yml exists and versions are pinned.

Hands-on exercises

Mirror of the exercises below. Do them in order and re-run tests until they pass.

1. Exercise 1: Create schema tests for a staging model, including a relationships test.
2. Exercise 2: Install dbt_utils and add a unique_combination_of_columns test.

Common mistakes and how to self-check

• Forgetting to ref() the correct model in relationships tests. Self-check: Does the referenced model exist and build before the child?
• Typos in column names inside YAML. Self-check: Run dbt compile; dbt will surface invalid columns.
• Not pinning package versions. Self-check: Ensure version constraints in packages.yml.
• Overusing severity: warn. Self-check: Do critical tests actually fail the build when they should?
• Ignoring failing rows. Self-check: Enable store_failures and review failure tables.

Who this is for

• Analytics Engineers who ship models and care about data reliability.
• BI Developers transitioning to dbt and wanting guardrails.

Prerequisites

• Basic dbt project set up with at least one model.
• Comfort with SQL SELECT statements.

Learning path

1. Start with built-in generic tests (not_null, unique, accepted_values, relationships).
2. Add one singular test for a key business rule.
3. Install dbt-utils; replace custom checks with package tests/macros where possible.
4. Introduce tags and severity for test prioritization.

Practical projects

• Harden a star schema: Add PK and FK tests across all fact/dim tables.
• Data contract: Enforce accepted_values for important status and type fields.
• Anomaly tripwire: Singular test that flags sudden drops to zero in daily orders.

Mini challenge

Create a new singular test that fails if any customer in dim_customers has a null email but is marked as is_active = true. Run it and fix the data issue in upstream logic.

Next steps

• Add test selection to your CI (e.g., run dbt test -s tag:critical on pull requests).
• Explore more dbt-utils macros and consider other vetted packages that fit your stack.

Note about saving your progress

You can take the exercises and the quick test for free. If you are logged in, your progress will be saved.