dbt unit tests and test driven design

[jillasophical]

I found some really interesting content on test driven development using dbt from some developers at a software consultancy called equal experts. What's even more fascinating is their approach to a language agnostic pipeline architecture. It's exciting to see the way dbt fits in so nicely to their paradigm.

Most relevant to this discussion is the test driven design approach, but I've also included some links to additional context. It would be cool if the original authors would be into contributing.

• Bringing TDD to the warehouse
• Writing unit tests for dbt
• Language agnostic data pipeline

[gnilrets]

I've been a huge fan of TDD since I learned about it while building a small web app for ETL job management many years ago. I liked it so much I ended up writing dtspec as a means of doing TDD for data transformation work. It was originally meant to be agnostic to what the data transformation system was (e.g., it could work for pandas as well as dbt). As a user of it myself, I'd say it's "ok".

I recently tried out the equal expert dbt-unit-testing approach for a new BQ dbt project I'm working on and I really like the approach. It's got several distinct advantages over dtspec:

• It works as a dbt package. No need to know anything about Python and it can work with dbt-cloud.
• With dtspec, you only run dbt once, and therefore you can really only mock out the dbt sources, and not the immediate parents of a model -- there are some workarounds, but they're not really practical. With dbt-unit-testing, it's easy to mock out the sources or just the immediate parents of a model. This makes writing tests much simpler.
• With dbt-unit-testing, you don't have to run the full dbt pipeline in order to run tests. This makes it easier to write isolated tests that don't have a lot of extra dependencies just to get dbt to run without error.

I did run into a few problems while implementing dbt-unit-testing:

• Running on BigQuery, I encountered the dreaded "Query too complex" issue. We were able to get around this issue by modifying the package so that if a model already exists in the database, it can use that to derive the table schema so that it doesn't have to build out a complete DAG via CTEs and results in a simpler query that BQ can digest.
• I only have few tests right now, but each one is taking almost 30 seconds to run. I am concerned that if I have 100's of tests then it may become prohibitive. A big time suck seems to be that it runs introspection queries on each model that a test depends on. Maybe if there was a way to cache that result, it could be sped up.... Or maybe Slim CI solves this....

Anyway, I really think this is an excellent package and would love to see it developed further and adopted as a central piece to building a robust dbt data pipeline.