dbt Test

dbt tests are defined declaratively using YAML configuration files or simple SQL SELECT statements. Instead of writing procedural code to check data, you specify what condition the data must satisfy (e.g., not_null, unique). The dbt test execution engine interprets this configuration and runs the appropriate validation query. This approach separates the test logic from the execution engine, making tests portable, easy to version control, and less error-prone than custom scripting.

• Example: A YAML block defining a not_null test on a user_id column.
• Key Benefit: Enforces data quality as code, allowing tests to be reviewed, shared, and automated alongside transformation logic.

dbt provides two primary test types. Generic tests (like unique, not_null, accepted_values, relationships) are reusable validations applied to columns via YAML configuration. Singular tests are one-off, custom SQL queries that return failing rows; they are ideal for validating complex business logic that generic tests cannot cover.

• Generic Test Example: relationships test ensures referential integrity between a orders.customer_id column and a customers.id column.
• Singular Test Example: A SQL file that checks if a derived profit_margin column is always below 100%.
• Separation of Concerns: This duality allows teams to standardize common checks while retaining flexibility for domain-specific validation.

Tests are nodes within dbt's Directed Acyclic Graph (DAG) of data transformations. When you run dbt test, the framework understands the dependencies between your models and tests, ensuring tests run on the correct, materialized data. Tests can be executed on specific models, directories, or tags, providing granular control.

• Pipeline-Gated Execution: Tests can be configured to run immediately after a model is built, acting as a quality gate that fails the pipeline if data assumptions are violated, preventing bad data from propagating downstream.
• Dependency-Aware: The test for a staging model will run before the test for a downstream mart model that depends on it, ensuring validation occurs in the correct order.

All dbt tests are compiled into standard SQL SELECT statements that are executed directly in your data warehouse (e.g., Snowflake, BigQuery, Redshift). The test "passes" if the query returns zero rows; any returned rows are considered failures. This warehouse-native approach leverages the power and scalability of modern cloud platforms.

• Performance: Testing happens where the data lives, eliminating costly data movement.
• Transparency: The generated SQL is inspectable, making it easy to debug why a test failed by examining the failing rows.
• Consistency: Uses the same connection and compute credentials as your dbt models, ensuring a unified environment.

dbt writes test results to a table in your warehouse (typically dbt_test__audit), turning validation outcomes into queryable data. This enables building custom dashboards, tracking test failure rates over time, and setting up alerts. The results structure includes the test name, status, execution time, and the specific failing rows.

• Observability: Facilitates integration with broader data observability platforms by exposing test results via a clean data interface.
• Historical Analysis: Teams can analyze trends, such as which models or tests fail most frequently, to prioritize data quality investments.
• Alerting: Can be integrated with tools like Slack or PagerDuty by querying the results table after a run.

While dbt tests are excellent for validating the output of transformations defined within dbt, they are often part of a larger data quality posture. They typically serve as unit and integration tests for the dbt-managed layer of the data stack.

• Scope: Best for testing business logic and structural integrity after transformation. They are less suited for monitoring data drift in source systems or validating raw, untransformed data at ingestion.
• Synergy: Often used alongside specialized tools like Great Expectations or Soda Core for profiling, monitoring, and testing non-dbt pipelines or raw data sources. dbt tests act as the quality gate for the "trusted" data layer.

dbt provides four core, pre-built tests that validate fundamental data properties without custom SQL. These are the most commonly used tests for basic data hygiene.

• unique: Ensures all values in a specified column (or combination of columns) are distinct. Essential for primary keys.
• not_null: Verifies that a column contains no NULL values, enforcing completeness for critical fields.
• accepted_values: Confirms that all data in a column matches a predefined list of allowed values (e.g., status IN ('active', 'inactive', 'pending')).
• relationships (Referential Integrity): Validates that every value in a child column exists in a parent column, enforcing foreign key constraints.

Example YAML definition:

yamlCopy
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

Singular tests are bespoke SQL queries where a result set indicates a test failure. They are used for complex, one-off validation logic that generic tests cannot cover.

• Structure: A .sql file in the tests/ directory containing a SELECT statement. Any rows returned by the query are interpreted as failing records.
• Use Cases:
  • Validating multi-column business logic (e.g., discount_amount <= order_total).
  • Checking for orphaned records across multiple tables.
  • Enforcing sophisticated date logic (e.g., ship_date >= order_date).
  • Identifying statistical outliers beyond simple thresholds.

Example (tests/ship_date_after_order_date.sql):

sqlCopy
SELECT order_id, order_date, ship_date
FROM {{ ref('orders') }}
WHERE ship_date < order_date
-- If this returns rows, the test fails.

Custom generic tests are parameterized SQL macros that can be reused across multiple columns or models, promoting consistency and reducing code duplication.

• Structure: Defined as a Jinja macro in a .sql file within the macros/ directory. They accept arguments like column_name and model.
• Use Cases:
  • Financial Data: Ensuring debits and credits sum to zero for a ledger.
  • ID Format Validation: Checking that a column matches a specific regex pattern (e.g., UUID, email).
  • Freshness Thresholds: Testing if a timestamp column is within an acceptable range of the current time.
  • Percentage Bounds: Verifying a column's value is between 0 and 100.

Example macro (macros/test_value_between.sql):

sqlCopy
{% test value_between(model, column_name, min_value, max_value) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} < {{ min_value }}
   OR {{ column_name }} > {{ max_value }}
{% endtest %}

Usage in YAML:

yamlCopy
columns:
  - name: satisfaction_score
    tests:
      - value_between:
          min_value: 0
          max_value: 10

These tests validate the structure and formatting of data, ensuring it aligns with expected contracts and downstream consumption needs.

• Schema Tests: While not a native dbt test type, schema validation is achieved via dbt build or dbt run coupled with tests. The compilation and execution of models inherently validates SQL syntax and references. Formal schema contracts are often enforced via pipeline-gated tests on column existence and data type.
• Data Type Validation: Implemented using custom generic tests or singular tests.
  • Checking that a numeric column contains only integers.
  • Ensuring a VARCHAR column does not exceed a defined maximum length.
  • Verifying a DATE column has no invalid or out-of-range dates.
• Integration with Data Contracts: These tests operationalize the guarantees of a data contract, acting as the enforceable validation layer for schema stability and type safety.

This category encompasses the most valuable tests, which encode critical domain knowledge and ensure data accurately reflects real-world rules.

• Aggregate Logic: Testing that summed totals in summary tables match granular transaction tables.
• Derived Column Accuracy: Validating that a calculated column (e.g., revenue = quantity * unit_price) is computed correctly.
• Hierarchical Integrity: Ensuring roll-up relationships are maintained (e.g., all city-level records map to a valid country).
• Uniqueness Across Sources: Confirming there is no overlap or duplication of key identifiers when data is merged from multiple systems.
• Temporal Consistency: Checking that slowly changing dimensions (SCD) are applied correctly and history is preserved.

These tests move beyond basic hygiene to protect the business correctness of data products. They are often the most complex to write but provide the highest return on investment by preventing costly decision-making errors.

Understanding how and when tests run is crucial for integrating them into a Continuous Testing paradigm.

• Command-Line Execution:
  • dbt test: Runs all tests on all models in the project.
  • dbt test --select test_type:singular: Runs only singular tests.
  • dbt test --select model_name+: Runs tests for a specific model and all its downstream dependencies.
• Pipeline Integration:
  • As a Quality Gate: Tests are executed after dbt run in a CI/CD pipeline. A single test failure can be configured to fail the entire job, blocking the promotion of bad data.
  • In Production: A subset of non-destructive smoke tests or canary tests can be run on production data to monitor health.
• Test Severity: Tests can be configured as ERROR (fails the run) or WARN (logs a warning but allows the run to proceed).
• Result Handling: Results are logged to the command line and stored in the data warehouse, enabling aggregation in dashboards for test coverage and trend analysis.

A declarative or programmatic statement that defines a condition data must satisfy to be considered valid. While a dbt test is a specific implementation within the dbt framework, a data quality rule is the abstract concept it enforces.

• Examples: A column must be non-null, values must fall within a specified range, a foreign key must reference an existing primary key.
• Implementation: Can be codified in SQL, YAML (like dbt), Python (e.g., Great Expectations), or other configuration formats.
• Purpose: Provides the machine-readable specification for what "good data" means for a given asset.

A programmatic check within a data pipeline that actively verifies a specific condition about the data and raises an error or warning if the condition is false. A dbt test is a type of data assertion.

• Key Mechanism: Executes a query; if it returns any rows (representing violations), the assertion fails.
• Runtime Context: Can be run as a pipeline-gated check (blocking) or as a monitoring alert (non-blocking).
• Contrast with Rule: An assertion is the execution of a rule. The rule defines the condition; the assertion tests it.

An approach to data quality where tests are specified as configuration stating what the data condition should be, rather than how to check it programmatically. dbt tests are a prime example of declarative testing.

• Syntax: Uses YAML or SQL-based DSLs (Domain-Specific Languages) to define constraints (e.g., not_null, unique).
• Advantage: Separates the test definition from the execution engine, making tests more portable, readable, and maintainable.
• Framework Examples: dbt, Soda Core, and the expectation suites in Great Expectations all employ declarative paradigms.

A software development methodology applied to data engineering where data quality tests are written before the data pipeline code is built. dbt tests are a foundational tool for implementing this practice.

• Workflow: 1. Define expected output schema and quality rules (as dbt tests). 2. Write the SQL transformation model. 3. Run tests to validate the model meets specifications.
• Benefit: Ensures the pipeline is built to satisfy explicit quality requirements from the start, reducing defects and clarifying intent.
• Outcome: Produces a documented, verifiable data asset with built-in quality guarantees.

A data quality test whose failure prevents a data pipeline from proceeding to the next stage. When a dbt test is configured to run as part of a model's materialization and set to fail fast, it acts as a pipeline gate.

• Function: Serves as a quality gate to block bad data from propagating downstream to consumers and dependent models.
• Placement: Typically executed immediately after a model is built or loaded, before any downstream processes begin.
• Contrast: Differs from monitoring tests run in production, which alert on issues but do not halt pipeline execution.