From Chaos to Clarity: How a dbt Testing Strategy Transforms Data Operations
Testing is often overlooked in data modeling, but it plays a crucial role in ensuring long-term success. By implementing a robust testing strategy, stakeholders can better manage operations and reduce future technical debt. This blog explores the importance of testing within the Analytics Development Lifecycle (ADLC) and offers practical insights on creating effective test plans.
When adding tests, consider what value they add to the data pipeline. Tests that run in production should always lead to a specific action to take if they fail.
Why Testing?
While working on data modeling, it’s easy to treat testing as an afterthought. But creating a testing strategy can set up stakeholders for longer term success, giving them the ability to handle operations and reducing future tech debt.
dbt Labs recently published a whitepaper on the Analytics Development Lifecycle (ADLC). This model borrows software engineering principles to consider the workflow of designing analytics systems.
We typically spend most of our time in the Develop phase. Thinking about the other phases of the ADLC can help us to consider the longevity of our code, anticipating what might arise in the Ops phases (where we may be less involved as developers). Some of the phases that are helpful to consider for testing are:
- Plan - Create a testing plan
- Test - Testing as part of development
- Observe - Testing in production
Avoiding Test Bloat
Because tests are easy to add in dbt, it can be tempting to add a lot of them. But having too many tests can lead to bloat, ignoring test failures, tech debt, and increased warehouse computer costs.
Having fewer, more meaningful tests will ensure that any failures identify issues - and that resolving the issues provides value. Or as Faith McKenna and Jerrie Kenney point out, “data testing should drive action. The more alerts you have in your pipeline, the less action you will take.”
Plan
Identifying Use Cases
During a planning phase or before diving into development, consider the different use cases that testing can cover. What do the stakeholders care most about?
Use cases might include:
- Data Validation
- Checking assumptions about source data
- Source freshness
- Testing during development
- Validating complex logic
- Preventing regressions while refactoring or migrating code
- Developing for edge cases
- Operations, or testing in production
- Catching edge cases or other changes in source data
- Business focused anomaly detection
Understanding the test use case can help you to think about 1) what type of test to implement and 2) which tests to prioritize.
Most use cases will focus on either data hygiene or validating transformation logic. Some use cases check for business focused anomalies, like especially high and low values for key KPIs. Tests centered on business focused anomalies should typically be set to warn instead of error, since out of range values shouldn’t cause the pipeline to fail. There should still be action to take if the business-centered test triggers, like notifying a stakeholder. Anomaly detection may be part of a separate data observation strategy, rather than part of your testing plan; this depends on your tool setup.
Creating a Test Plan
A test plan could start very simply by creating some guidelines for adding tests to the project, similar to a project style guide. A high-level plan can get developers on the same page and call out what to check for in PR reviews. Every type of test should have an action to take if the test fails.
If the project involves a lot of different types of tests, like singular tests and unit tests, the test plan could include individual tests, their use case, what environment they run in, and what to do if they fail.
In dbt Cloud version less or 1.9, we can add test descriptions. This can be a great place to include debugging information in the event that the test fails.
Test
Unit Tests
dbt Cloud and version 1.8+ now include out of the box unit tests. Unit tests validate programming logic within a single element within a project, like a model, a column, or a macro. Unit tests take a small number of input rows and verify that the code produces the expected output. The unit test will compare our expected output with the actual output of running the code––if it matches, the test will pass. Otherwise, it will fail. Unit tests will most often help us test logic in the intermediate, or composition, layer.
Unit tests can prevent code regressions while refactoring a model or during a migration. Once we have a good unit test for a model, we can keep running it when making changes to the model to ensure that we continue to see the correct results.
Unit tests also allow us to use test-driven development. When sitting down to work on a model with complex logic, we can write a unit test first before starting any code. Then, we can write the simplest code that will make the test pass. Once that works, we can continue to refactor the code, making sure that the unit test continues to pass.
Unit tests work best when the input rows cover a wide range of situations. If the data contains different edge cases, be sure to include an example of each in the unit test. You can also include inputs that don’t exist in the source data to ensure that the code is robust against future changes.
Data Tests
Because data tests are simple to add, it’s important to make sure that our tests are meaningful. Each test that we add should drive a certain action if it fails.
The use cases for data tests typically fall into the categories of source data hygiene, detecting anomalies caused by data transformation, and business driven anomaly detection.
Sources - Data Hygiene
The tests against the source definitions should validate the assumptions about the source data that our code relies on, including checking:
- Primary keys - unique and not_null
- Foreign keys - not_null
- Source freshness
Preparation (or Staging) Layer - Hygiene and Light Transformation
The preparation layer should:
- Validate any changes that our code makes
- Catch data hygiene issues that can arise in the preparation layer
This may include checking light transformations that adjust dates, unpack JSON, or use data cleaning functions like regexp_replace().
Composition (or Intermediate) Layer - Validate Data Transformation
Tests should validate that our code works as expected.
- If there are joins that change the table’s granularity, re-test the primary key for unique and not_null.
- Look for data anomalies that might be caused by errors in code (e.g. introducing nulls)
Presentation (or Mart) Layer - Business Driven Testing
If we add tests to the presentation layer, they may check that any metrics that we surface to use in dashboards uphold the expected level of data quality. Another use case might be writing singular tests for business-driven anomaly detection.
For more guidance on where to add tests, see Faith McKenna and Jerrie Kumalah Kenney’s blog post on where tests should go in a pipeline.
Observe
Addressing Failing Tests
Once all of the development tests are in place, it’s time to revisit the test plan and decide what to test in production. The tests that run in production should each lead to a specific action to take if they fail, whether that is investigating source data, debugging, or notifying a stakeholder of anomalies.
We may decide not to run every test in CI or production. For example, we may choose to only run data tests in production and reserve unit tests for validating future code changes during the development process.
Test Plans Change Over Time
The test plan is a living document - the testing strategy can change at any time! For example, if the business identifies an important KPI, we might add data tests to the upstream columns that create that key metric. Some tests may become less important over time - for example, if the definitions of key KPIs change or if models become deprecated. We might also notice that tests exist without a concrete action for engineers to take if they fail. In either case, we might set them to warn or remove them.
Happy testing!