dbt-utils Documentation

The dbt-utils package provides a suite of generic tests that can be applied to models and columns in your dbt project. These tests allow you to validate data quality using reusable logic. Available generic tests include:

• equal_rowcount: Compares row counts between relations.
• fewer_rows_than: Ensures a relation has fewer rows than a specified threshold.
• equality: Compares columns or entire tables for equality, with options to compare specific columns or ignore certain ones.
• expression_is_true: Validates that a specific expression evaluates to true for all rows.
• recency: Checks if data is recent based on a timestamp column.
• at_least_one: Ensures at least one value in a set meets a condition.
• not_constant: Checks that a column is not composed of a single constant value.
• not_empty_string: Ensures columns do not contain empty strings.
• cardinality_equality: Compares the cardinality of different sets.
• not_null_proportion: Validates the proportion of non-null values.
• not_accepted_values: An alternative to standard accepted values tests.
• relationships_where: Validates relationships with additional filtering logic.
• mutually_exclusive_ranges: Ensures values do not fall into overlapping ranges.
• sequential_values: Checks for sequential ordering of values.
• unique_combination_of_columns: Validates uniqueness across a combination of columns.
• accepted_range: Ensures values fall within a specific range.

If you are building an adapter plugin or need to override a dbt_utils macro with a custom implementation, you can use dbt's dispatch mechanism.

1. Configure Search Order: In your dbt_project.yml, define a dispatch config to control which packages are searched first for a macro in the dbt_utils namespace.

2. Naming Convention: To override a macro (e.g., dbt_utils.safe_add), define a macro in your own project using one of these prefixes:

   • default__<macro_name>
   • <adapter_name>__<macro_name> (e.g., postgres__safe_add)

When dbt dispatches a macro, it follows the order defined in your search_order and checks for the adapter-specific or default implementation in each package.

dispatch:
  - macro_namespace: dbt_utils
    search_order:
      - first_package_to_search    # likely the name of your root project
      - second_package_to_search   # could be a "shim" package, such as spark_utils
      - dbt_utils                  # always include dbt_utils as the last place to search

To improve stability and ease of use, dbt_utils is migrating its cross-database macros (macros that provide compatibility across different database engines) to be defined in dbt-core and implemented within specific database adapters.

What this means for you:

• Namespace changes: Instead of calling dbt_utils.dateadd(...), you can often use the more direct dateadd(...) or dbt.dateadd(...) once the macro is promoted to Core.
• Backward Compatibility: dbt_utils will maintain "passthroughs" for migrated macros. This ensures that existing code calling dbt_utils.hash (or similar) will not break; the call is simply redirected to the new Core/adapter implementation.
• Extensibility: If a specific cross-database macro is not yet available in your version of dbt-core, you can still use dbt's dispatch mechanism to shim or extend the package to your specific adapter.

Integration tests in this repository typically follow a pattern of adding a seed file, a model file, and a generic test to assert behavior.

Workflow

1. Add a seed file: Create a .csv file in integration_tests/data/ with fake data.
2. Add a model file: Create a .sql file in integration_tests/models/ that uses the macro you are testing.
3. Add a generic test: Update a schema.yml file in integration_tests/models/ to include a test that asserts the expected outcome.

Running your new test

Navigate to the integration_tests folder and run the following commands (replacing {your_target} and {your_model_name} with your actual values):

Option 1: Step-by-step

dbt deps --target {your_target}
dbt seed --target {your_target}
dbt run --target {your_target} --model {your_model_name}
dbt test --target {your_target} --model {your_model_name}

Option 2: Using dbt build

dbt deps --target {your_target}
dbt build --target {your_target} --select +{your_model_name}

To run integration tests for dbt-utils, you must first configure your environment.

Prerequisites

• python3
• Docker

1. Configure Credentials

Edit the environment file for your specific target located at integration_tests/.env/[TARGET].env. These variables are used to populate your profiles.yml.

To load the environment variables into your shell, use:

set -a; source integration_tests/.env/[TARGET].env; set +a

2. Setup Postgres (Optional but recommended)

Postgres is the fastest and easiest way to test most functionality. If you choose to use Postgres, you must set up the test database using one of the following commands:

make setup-db

or via docker-compose:

docker-compose up --detach postgres

3. Setup Virtual Environment

It is strongly recommended to use a virtual environment created in the root of the dbt-utils repository:

python3 -m venv env
source env/bin/activate