---

dbt Unit Test Coverage 🧪🔣

This is a package to generate a code coverage metric for dbt models' unit tests.

This is not for public consumption (yet!), but rather to show off some Sainsbury's extensions. This also requires a specific fork of the dbt-unit-testing package, namely:

• https://github.com/Bilbottom/dbt-unit-testing

Installation ⬇️

This is currently only available on GitHub, so you'll need to supply the GitHub URL to pip:

pip install git+https://github.com/Bilbottom/dbt-unit-test-coverage@v0.0.2

Usage 📖

This package will generate a very rudimentary coverage metric for the dbt models' unit tests. We can't measure line hits like we can with Python code, but we can measure the number of models that have unit tests and which of their CTEs have unit tests.

To run the code coverage report, invoke the dbt-unit-test-coverage package after compiling your project:

# Compile your project
dbt clean
dbt deps
dbt compile

# Generate the coverage
dbt-unit-test-coverage

Help for the dbt-unit-test-coverage package can be generated using the --help flag thanks to the typer library:

dbt-unit-test-coverage --help

Run as a pre-commit hook

Rather than running this manually or as part of a CI workflow, you could choose to run this as a local pre-commit hook.

The exact configuration will depend on the size of your project and your developer workflow, but the following approach is a quick-start that should work for most projects.

# .pre-commit-config.yaml
default_install_hook_types: [pre-commit, post-commit]
default_stages: [commit]

repos:
  - repo: local
    hooks:
      - id: dbt-unit-test-coverage
        name: Generate dbt coverage report
        entry: dbt-unit-test-coverage  --compile-dbt
        language: system
        pass_filenames: false
        stages: [post-commit]
        always_run: true

Summary of extensions

CTE unit testing

This builds off of another fork, this one from the EqualExperts/dbt-unit-testing repo:

• https://github.com/Bilbottom/dbt-unit-testing

This fork adds the ability to unit test particular CTEs in a model, provided you follow the dbt-labs style guide and include the select * from final line at the bottom of the model.

Code coverage

With the CTE unit tests, we can then measure the number of (logical) CTEs that don't have tests which gives us the coverage metric.

Future plans

The v1.5 release of dbt-core introduced programmatic invocations:

• https://docs.getdbt.com/reference/programmatic-invocations

This would allow us to piggyback off of the dbt functionality even more, rather than hacking together our own solutions.

Additionally, the coverage metric is easy to manipulate in your favour, so we intend to improve the metric by:

• Validating the columns in the tests to make sure that each of the columns in the CTE are tested
• Penalising CTEs that are overcomplicated and should be split into multiple CTEs (e.g. a CTE that has large sub-queries)

This is currently based on the EqualExperts/dbt-unit-testing framework, but this package will be updated to also be able to use whatever native unit testing framework dbt ends up having -- see:

• dbt-labs/dbt-core#8275