dbt test coverage: the missing SLI in your data platform

In SQL we still don’t have an equivalent to test coverage. Even with dbt tests, it’s hard to answer a simple question: How much of my SQL logic is actually tested? This article explains how you can approach the problem and use it as a valid SLI in your data platform.

Example of data quality monitor (image generated by Gemini)

Data platforms and SLIs

Data platforms are becoming more complex and more important every year. They help organisations manage, govern, and use their data more efficiently.

When a user searches for a data product in a data platform, tools like data catalogues expose useful metadata so they can understand the nature and reliability of the data before using it. One key aspect when assessing the trustworthiness of any data, are the SLIs (Service Level Indicators).

SLIs provide transparent and measurable data quality indicators like freshness, completeness, schema stability, or availability. They help data producers communicate the reliability of their data products, and data consumers decide whether a dataset is trustworthy enough for their use case.

dbt + unit tests

I started my career as a data engineer working with SQL pipelines. Back then, SQL code was buried inside massive stored procedures and multi-page queries. Maintaining these pipelines was painful: no structure, no modularity, no tests. Dbt improved the situation by bringing software engineering best practices.

Before switching to data engineering, I used to work as a backend developer. Unit tests and test coverage were simply part of my daily work. Having my code unit tested gave me enough confidence that I could make any refactor or update, and immediately know if everything still worked. This is key to ensure good code quality, something also critical in data projects. With dbt, I finally got the development tooling I was used to, except for one thing: How could I test my code now?

Fortunately, with dbt 1.8, unit testing became a native dbt feature. Now we could have our SQL code unit tested, but…

How do we ensure that all our SQL business logic is tested?

Or at least the parts that matter?

As a backend developer, I had multiple tools to measure test coverage. But not for SQL where is harder to extract the business logic. With the introduction of dbt Fusion I see a new step forward in the identification of the logic embedded in our models:

“The dbt Fusion engine can also render Jinja, but then it completes a second phase: producing and validating with static analysis a logical plan for every rendered query in the project.”

Fusion is not designed today to compute test coverage, but this underlying ability to generate logical plans makes it feasible. But until this becomes fully available, our team decided to build our own lightweight solution.

Our solution

While waiting for dbt to offer coverage out of the box, our team developed a simple solution. We added a step in our CI/CD pipeline that:

• Parses SQL models to extract important logical components
• Parses unit tests to identify mocked inputs and expected outputs
• Matches logic to unit tests to identify what parts of the SQL logic are covered by the mocked inputs within the tests
• Computes a coverage score
• Enforces a minimum coverage via a quality gate

Example of data pipeline (image generated by Gemini)

Benefits

• Quality gates: a minimum coverage threshold forced our team to write testable code. This discipline helped catch issues early, even in lower environments, before promoting changes to production.
• Documentation: unit tests serve as documentation of business rules. Over time, they become a living specification of how a KPI is expected to behave.
• Code maintainability: Good test coverage makes refactoring safer. This ensures code quality and helps reduce tech debt.

How did we compute test coverage scores on SQL?

When we analysed the problem, we realised that SQL test coverage should measure business logic coverage, not line coverage. We identified 4 dimensions that should be covered by tests:

1. Conditions or Filters

Filters determine which records contribute to a specific business metric. These rules frequently change as the business evolves. Validating this logic ensures that KPIs include exactly the intended subset of data and remain consistent as rules evolve.

Example A KPI like “active customers” depending on: status = ‘active’ AND last_login_date >= 30 days

Coverage criteria At least one unit test must include a mocked record that should be filtered out by the condition to verify that the logic behaves as expected.

2. Aggregations

Aggregations convert raw data into metrics. They are often the heart of a KPI and must be tested. A wrongly calculated metric silently pollutes dashboards and KPIs.

Example A KPI “total monthly revenue” relying on: SUM(amount) over all completed transactions

Coverage criteria An unit test must include at least two mocked input records with different values for the aggregated field, and the expected output must include at least one record with the aggregated result.

3. SQL Operations (Arithmetic, Expressions)

It’s not about testing SQL itself, but testing the business logic behind operations. They represent the core business formulas that define your KPIs.

Examples A KPI like “total order value” depending on: price quantity — discount*

Coverage criteria At least one unit test must include one mocked record with values for all columns involved in the operation, and the expected output must include a value for the resulting field

4. Joins

Joins are one of the most critical points in any pipeline. Using the wrong join condition can introduce new data quality issues that don’t come from the source at all, like duplicates or missing records.

Example A KPI “total revenue per customer” depending on joining orders with customers using: FROM orders INNER JOIN customers ON orders.customer_id = customers.id

Coverage criteria An unit test must include at least one mocked record from each of the joined inputs to confirm that the relationship is correctly implemented. The test should also validate the join type by including input records that do not match the join key, confirming that unmatched records behave as expected (e.g., excluded in INNER joins or retained in LEFT joins).

A working example

I have built a small pet project to show what SQL coverage looks like in practice. The complete example is available in this link.

Test coverage report (image by the author)

A new SLI: test coverage of business logic

How can you give potential consumers confidence that the KPIs produced by your data product are accurate? SLIs like uniqueness, completeness or anomaly detection cover important aspects of data quality, but they don’t verify whether the transformation logic behind a KPI is accurate.

Introducing test coverage as a new SLI adds a new metric to the data accuracy dimension. It quantifies how much of your business logic is validated through unit tests using mocked scenarios that can include corner cases and edge conditions.

Benefits

• Transparency and confidence: It provides a realistic view on how much of your transformation logic is tested, and makes it easier to trust that KPIs are not only well defined but also correctly implemented.
• Testing culture: Publishing test coverage at the platform level promotes a stronger testing mindset across data teams. As coverage becomes visible to consumers and stakeholders, it naturally encourages better engineering practices.

Conclusion

Test coverage isn’t just a nice-to-have. It’s a clear indicator of how reliable your data transformations really are. By exposing it as an SLI, you increase transparency, strengthen trust with consumers, and promote a healthier testing culture within your data organisation.

Thanks to my Thoughtworks colleague Alessandro for taking the time to review early versions of this article