Code is written once, but it is read, understood, and changed many times. The largest cost of software therefore rarely lies in its initial development. It lies in everything that happens afterwards.
This applies to SQL and dbt as much as it does to traditional application code. A SQL model may run correctly today without being easy to understand, test, or change six months from now. When another engineer takes ownership of it, the important questions are:
- Can the engineer explain the logic?
- Can changes be validated automatically?
- Can the model be changed without unexpected side effects?
Maintainability cannot be reduced to a single number. Metrics can, however, provide useful signals that make potential risks visible. One such signal is Cyclomatic Complexity, which measures the number of independent logical paths through a piece of code.
The interesting question is whether this principle can also be applied to declarative SQL, and whether it can provide automated feedback on code written by both humans and AI.
AI Moves the Bottleneck
AI can generate SQL, dbt models, and code changes faster than most engineers can write them manually. This does not necessarily mean that AI produces worse code. It means that AI accelerates the engineering system that already exists.
If a team has strong tests, clear standards, and effective reviews, AI can amplify those strengths. If the team has insufficient tests, unclear quality requirements, and large, complex models, AI can make it possible to produce technical debt faster.
The bottleneck therefore moves from writing code to trusting code.
People must still understand, validate, and take responsibility for the logic that reaches production. If code production increases, automated quality mechanisms must scale with it. Tests, reviews, and complexity measurements do not become less relevant because of AI. They become more important.
The Theoretical Foundation: McCabe
Thomas J. McCabe introduced Cyclomatic Complexity in 1976. Its formal definition is based on a graph representing a program's control flow:
M = E - N + 2P
Where:
Eis the number of edges in the graph.Nis the number of nodes.Pis the number of connected components.
In practice, a simpler rule of thumb is often used:
Cyclomatic Complexity = 1 + number of decision points
A decision point can, for example, be an if statement. Each decision introduces another possible path through the program.
Consider this example:
def discount(customer, amount):
discount = 0
if customer.is_vip:
discount += 10
if amount > 1000:
discount += 5
return discount
The function starts with a base complexity of 1. The two if statements each add one decision point:
CC = 1 + 2 = 3
The important factor is not the number of lines. It is the number of independent logical paths that an engineer must understand and potentially test.
More paths mean more behaviour that the team must reason about.
Complexity Is Not the Same as Bad Code
High complexity is not automatically a problem. Some business rules are naturally complex and cannot be removed without changing the required behaviour.
The risk becomes more significant when complex code is insufficiently tested.
This is the idea behind the Change Risk Anti-Patterns metric, commonly known as CRAP. Introduced by Alberto Savoia and Bob Evans, it combines Cyclomatic Complexity with test coverage:
CRAP = CC² × (1 - coverage)³ + CC
For code with a complexity of 12, the result changes significantly depending on its test coverage:
| Test coverage | CRAP score |
|---|---|
| 0% | 156 |
| 50% | 30 |
| 80% | 13.2 |
| 100% | 12 |
The point is not that complex code must always be rewritten. The point is that complexity should be matched by tests.
Complex code can be manageable. Complex and insufficiently tested code is risky.
But SQL Does Not Have Traditional Control Flow
Cyclomatic Complexity was developed for imperative programming languages. In those languages, control flow can be followed through constructs such as if, else, for, and while.
SQL is declarative. We describe the result we want and largely leave the execution strategy to the database platform's query optimiser. SQL therefore does not necessarily have a traditional control-flow graph that can be analysed directly using McCabe's original model.
That does not mean SQL has no branching behaviour.
SQL contains data-dependent decisions:
WHEREdetermines which rows continue.CASE WHENdetermines which result is selected.JOINdetermines which rows are matched, retained, or removed.UNION,INTERSECT, andEXCEPTcombine data sets in different ways.- Recursive queries can perform a data-dependent number of iterations.
MERGE WHENselects an action based on matched or unmatched data.
Imperative code branches through control flow. SQL branches through data-dependent relational logic.
SqlCC: Cyclomatic Complexity for SQL and dbt
SqlCC is a practical adaptation of complexity principles to SQL and dbt.
The tool does not simply process a SQL file as text and count keywords. It parses the SQL with sqlglot and represents it as an Abstract Syntax Tree, or AST. This makes it possible to identify the actual structure of the SQL expressions.
At a high level, the calculation has four stages:
- Parse the SQL into an AST.
- Start the complexity score at 1.
- Count filters, branches, relations, and loops.
- Sum the complexity of the statements in the file.
The approach is deterministic. Given the same rules, the same SQL produces the same score. The rules can therefore be explained, tested, and automated.
What Increases the SqlCC Score?
SqlCC counts constructs when their result can follow different logical paths depending on the data.
Filtering
WHERE,HAVING,QUALIFY,AND,OR,IN,BETWEEN,LIKE,GROUP BY
Branching
CASE WHEN,IF,IFF,COALESCE,NVL,IFNULL,TRY_functions,SAFE_functions
Relations and Iteration
JOIN,UNION,INTERSECT,EXCEPT,WITH RECURSIVE,CONNECT BY,MERGE WHEN
This does not claim that a SQL JOIN is identical to an imperative if statement. The adaptation is based on the observation that these constructs introduce additional data-dependent outcomes that an engineer must understand and validate.
What Does SqlCC Deliberately Not Count?
It is equally important to define what SqlCC does not count.
A transformation does not automatically increase complexity when the same operation is applied regardless of the input. Common transformations are therefore not counted by default:
CAST,LOWER,TRIM,CONCAT,SUBSTRING,COUNT,SUM,MAX
Structural elements are not counted automatically either. A Common Table Expression or subquery is not complex simply because it exists. Its contents determine its complexity.
Likewise, ROW_NUMBER, RANK, and unknown ordinary functions do not necessarily count as separate branches.
SqlCC does not merely measure the amount of syntax. It attempts to measure the amount of data-dependent logic.
A Worked SQL Example
Consider the following model:
SELECT
customer_id,
CASE
WHEN claims > 3 AND premium > 10000 THEN 'high'
WHEN claims > 0 THEN 'medium'
ELSE 'low'
END AS risk_level
FROM customer
LEFT JOIN claims USING (customer_id)
WHERE status IN ('active', 'pending')
The SqlCC calculation can be explained as follows:
| Construct | Contribution |
|---|---|
| Base complexity | 1 |
Two WHEN branches |
+2 |
One AND |
+1 |
One JOIN |
+1 |
One WHERE |
+1 |
One IN |
+1 |
| Total | 7 |
The CASE expression and ELSE clause are not counted separately. The individual WHEN branches introduce the alternative outcomes.
From SqlCC to a CRAP-Like Metric for dbt
Traditional branch coverage is not normally available for a dbt model. A dbt test result does not show which logical paths in the compiled SQL were exercised.
A CRAP-like calculation for dbt must therefore be treated as an estimate.
One possible model is:
Estimated coverage = min(100, tests / complexity × 100)
If a model has a SqlCC score of 7 and two dbt tests, the estimated coverage is:
2 / 7 × 100 = 28.6%
Using that value in the CRAP formula gives a score of approximately 24.9.
This calculation must be interpreted carefully. It assumes that one dbt test covers one basis path. It measures the quantity of tests relative to complexity, not documented branch coverage.
For example, a generic not_null test and a domain-specific test of a complex CASE WHEN rule both count as one test, even though their ability to validate business logic may be very different.
The score should therefore be used as a signal for further investigation, not as proof of actual test coverage.
Use the Metric as Feedback, Not as a Verdict
A complexity metric becomes problematic if it is treated as an absolute definition of good or bad code.
SqlCC does not tell us:
- Whether the business rules are correct.
- Whether the model uses meaningful names.
- Whether the documentation is sufficient.
- Whether the query plan performs well.
- Whether the model follows the intended architecture.
- Whether the tests cover the most important scenarios.
- Whether the complexity is necessary.
The metric indicates how much data-dependent logic an engineer may need to understand and test.
It must not be used to evaluate individual developers. A high score may reflect a complex business domain, inherited technical debt, or a model that deliberately consolidates several rules. The purpose is to identify risk in the code, not to judge the people who wrote it.
A Pragmatic Adoption Strategy
A common mistake when introducing a quality metric is to make it a hard release gate immediately. If an existing repository contains many highly complex models, a fixed threshold will either block development or quickly be ignored.
A more pragmatic adoption can be divided into five stages.
1. Establish a Baseline
Measure every model, but block nothing.
Review metrics such as:
- Number of analysed models.
- Median SqlCC.
- 90th percentile.
- Maximum score.
- Distribution across relevant score ranges.
- Models with both high complexity and few tests.
The purpose is to understand the repository's current profile before setting thresholds.
2. Review Hotspots Manually
Select the ten models with the highest combination of complexity and insufficient testing.
Check whether the results align with the engineers' experience. If the tool highlights models that the team already finds difficult to understand and change, that is evidence that the metric provides a useful signal.
3. Add Pull Request Feedback
Show the change in SqlCC and the estimated CRAP score in the pull request:
Before: SqlCC 18
After: SqlCC 24
Change: +6
Tests: unchanged
This gives the engineer actionable information without automatically blocking the change.
4. Introduce a No-Regression Policy
Block only new or worsened violations.
Existing models retain their baseline, while new changes must not make them materially worse. This avoids requiring the team to resolve all existing technical debt at once.
5. Improve Models When They Change
When a complex model already needs to be modified, the pull request can also reduce its complexity or add targeted tests.
This follows the Boy Scout Rule: leave the code a little better than you found it.
Thresholds should be based on the team's own SQL and dbt data. A known CRAP threshold from Java or another imperative language should not be copied directly to SQL. SqlCC uses a different interpretation of decision points, and dbt tests are not equivalent to traditional branch coverage.
SqlCC as a Feedback Loop for AI
The most interesting application emerges when the metric becomes part of the AI development loop.
A possible workflow is:
- A developer or AI agent generates a dbt model.
dbt compileconverts Jinja and macros into concrete SQL.- SqlCC analyses the compiled SQL.
- The result is compared with existing tests and the team's baseline.
- The AI agent receives the feedback.
- The agent proposes either refactoring or additional tests.
- A quality policy determines whether the change can be approved.
If complexity increases from 12 to 27 without any new tests, the feedback could be:
This change introduces 15 additional data-dependent decision points. Consider splitting the model or adding tests for the new
CASE WHENbranches and filtering conditions.
AI should not only generate code. It should also receive measurable feedback on the code it generates.
SqlCC can therefore become part of an automated quality system in which faster code production does not automatically become faster technical debt.
Conclusion
McCabe gave software engineering a method for measuring independent paths through code. The CRAP metric connected those paths with test coverage and demonstrated that complexity becomes especially risky when it is not matched by tests.
SqlCC brings these principles to SQL and dbt by measuring data-dependent relational logic. It is not a direct implementation of classical control-flow analysis. It is a transparent and reproducible adaptation to the declarative nature of SQL.
The metric cannot stand alone. It cannot determine whether the code is good, whether the business logic is correct, or whether an engineer has done good work. It can, however, make risk areas visible and provide a concrete basis for discussions about understandability, testing, and maintainability.
That becomes particularly important when AI can produce code faster than before.
As code production accelerates, feedback must accelerate with it. Otherwise, faster development may simply become a faster way to accumulate technical debt.
Measure risk in the code, not the people who wrote it.
References
- Thomas J. McCabe, “A Complexity Measure,” IEEE Transactions on Software Engineering, 1976.
- Alberto Savoia and Bob Evans, Change Risk Anti-Patterns metric, 2007.
- SqlCC project documentation and measurement rules.