DOCSEARCHbyAlgolia
Submit Docs

dbt-unit-testing

repository·master·Indexed 19 days ago

https://github.com/equalexperts/dbt-unit-testing

A dbt package that enables unit testing for dbt models by allowing developers to mock dependencies such as models, sources, snapshots, and seeds using SQL and Jinja macros. It supports multiple mocking strategies (Pure, Simplified, and Full), CSV-based input formats, and column transformations. The framework integrates with the dbt CLI via the `dbt test` command and provides specialized support for incremental models and model versioning.

Tokens
6K
Snippets
20
Records
25
Agent score
66%

What's inside dbt-unit-testing

  1. Use 'include_missing_columns' to simplify mocks

    master

    By setting include_missing_columns: true in your mock options, the framework inspects your models and sources to automatically calculate which columns are missing from your mock and infers them. This makes tests easier to maintain but requires that your sources/models are defined and updated in your test/dev environment.

    {% set options = {"include_missing_columns": true} %}

{% call dbt_unit_testing.test('customers', 'test name') %}
  {% call dbt_unit_testing.mock_ref ('stg_customers', options) %}
    select 1 as customer_id
  {% endcall %}

  {% call dbt_unit_testing.expect() %}
    select 1 as customer_id
  {% endcall %}
{% endcall %}

  2. Avoid recursive inspection with 'use-database-models'

    master
    The framework normally builds SQL recursively down to sources to infer missing columns. For complex models where this is non-performant, use the use-database-models option to use the model definition directly from the database. Warning: this creates a dependency on the underlying model definition which must be kept in sync.

  3. How dbt-unit-testing works

    master

    dbt-unit-testing uses Jinja macros to define mocks and test scenarios. It generates a large SQL query representing the test and executes it against your development environment.

    There are two primary execution strategies:

    1. Without artifact dependencies: The test runs without needing the actual models, sources, or snapshots to exist in your database. You must mock all dependencies and all columns manually. This is the recommended approach for most cases.
    2. With artifact dependencies: The framework uses your existing model/source definitions to simplify mocking. If you only mock a subset of columns, the framework can pull the remaining columns from your existing definitions. This requires the dependencies to be refreshed in the database.

  4. Install dbt-unit-testing

    master

    To install the package, add it to your packages.yml file using the git source.

    Warning: If you are upgrading from version 0.1.3, note that version 0.2.0 introduced breaking changes by removing previous mocking strategies. You must update your tests to use the new options.

    packages:
  - git: "https://github.com/EqualExperts/dbt-unit-testing"
    revision: v0.4.12

  6. Build mock values using CSV format

    master

    Instead of standard SQL, you can define input values using a tabular CSV format within mock_ref and expect macros. This is controlled by the input_format option. You can also specify column types using the :: separator (e.g., column_name::type).

    {% call dbt_unit_testing.test('customers', 'should sum order values') %}
  
  {% call dbt_unit_testing.mock_ref ('stg_customers', {"input_format": "csv"}) %}
    customer_id, first_name, last_name
    1,'',''
  {% endcall %} 
  
  {% call dbt_unit_testing.expect({"input_format": "csv"}) %}
    customer_id,customer_lifetime_value
    1,20
  {% endcall %}
{% endcall %}

  7. Improve test feedback and readability

    master

    When a test fails, dbt-unit-testing provides a visual comparison of expectations vs actuals. You can improve this feedback in two ways:

    Sorting Output

    Use the output_sort_field option in the test macro to sort the results by a specific field. This makes it easier to compare adjacent lines in the diff report.

    {% call dbt_unit_testing.test('some_model', 'smoke test', {"output_sort_field": "business_id"}) %}
  ... 
{% endcall %}

    Handling Trailing Spaces

    If your data contains trailing spaces that make diffs hard to read, use the last_spaces_replace_char option to replace them with a visible character (like a dot) in the test report. This is configured in dbt_project.yml under vars.unit_tests_config.

    vars:
  unit_tests_config:
    last_spaces_replace_char: "."

  8. Write unit tests for incremental models

    master

    To test incremental models, you must first enable the functionality in your project by overriding the is_incremental macro.

    By default, tests run in full-refresh mode (simulating the model without the is_incremental block). To specifically test the is_incremental logic, you must pass options={"run_as_incremental": "True"} to the dbt_unit_testing.test macro and you must also mock the model being tested itself.

    {# 1. Enable in your project #}
{% macro is_incremental() %}
  {{ return (dbt_unit_testing.is_incremental()) }}
{% endmacro %}

{# 2. Test the incremental logic #}
{% call dbt_unit_testing.test('my_model', 'inc test', options={"run_as_incremental": "True"}) %}
  {% call dbt_unit_testing.mock_ref ('source_model') %}
    select 10 as c
  {% endcall %}
  
  {# You must mock the model being tested to simulate existing data #}
  {% call dbt_unit_testing.mock_ref ('my_model') %}
    select 5 as c
  {% endcall %} 

  {% call dbt_unit_testing.expect() %}
    select 10 as c, 5 as c
  {% endcall %}
{% endcall %}

  9. Compare different dbt-unit-testing mocking strategies

    master

    The repository provides three different implementations of the same test suite to demonstrate various mocking strategies available in dbt-unit-testing. You can inspect these files to understand the trade-offs between simplicity and detail:

    • Pure mocking strategy: The simplest strategy. Located at ./tests/unit/tests_using_pure_mocking_strategy_and_sql_input.sql.
    • Simplified mocking strategy: A middle-ground strategy. Located at ./tests/unit/tests_using_simplified_mocking_strategy_and_sql_input.sql.
    • Full mocking strategy: The most detailed strategy. Located at ./tests/unit/tests_using_full_mocking_strategy_and_sql_input.sql.

    Note on behavior: When using the Full or Simplified mocking strategies, the framework relies on the documentation of seeds and models rather than actual database dependencies.

  11. Apply Column Transformations to Unit Tests

    master

    Column transformations allow you to alter column data (standardization, conversion, or formatting) before unit tests execute. This is useful for handling floating-point precision or complex types like BigQuery structs.

    Local Test Level

    You can define transformations as a JSON structure and pass them into the options argument of the dbt_unit_testing.test macro.

    Global Project Level

    You can define transformations globally in your dbt_project.yml file under vars.unit_tests_config.column_transformations.

    The ##column## Token

    Use the special token ##column## in your transformation strings. It will be replaced by the actual column name, properly quoted for your specific database adapter.

    Example: Rounding for Precision

    To prevent floating-point mismatches, round a column to a specific decimal place:

    {% set column_transformations = {
  "avg_revenue": "round(##column##, 5)"
} %}

{% call dbt_unit_testing.test('financial_model', options={"column_transformations": column_transformations}) %}
  {% call dbt_unit_testing.mock_ref ('raw_financial_data') %}
    select 5.0 as revenue
    UNION ALL
    select 2.0 as revenue
    UNION ALL
    select 3.0 as revenue
  {% endcall %}
  {% call dbt_unit_testing.expect() %}
    select 3.33333 as avg_revenue
  {% endcall %}
{% endcall %}

  12. Enable mocking for models and sources

    master

    To allow the framework to mock dependencies, you must use dbt_unit_testing.ref and dbt_unit_testing.source in your dbt models.

    Alternatively, you can override the standard dbt ref and source macros in your project to redirect them to the unit testing versions:

    {% macro ref() %}
   {{ return(dbt_unit_testing.ref(*varargs, **kwargs)) }}
{% endmacro %}

{% macro source() %}
   {{ return(dbt_unit_testing.source(*varargs, **kwargs)) }}
{% endmacro %}