Rollup complexity score

Rollup complexity score is a measure of how complicated the chain of aggregations and dependencies behind a rolled-up metric is, combining its depth, breadth, and the number of transformations along the way. A revenue figure that sums one clean table scores low. A blended-margin figure that joins six sources across four aggregation layers with currency conversion and deduplication scores high. The number captures, in one figure, how much can go wrong on the way to the value.

It matters because complexity is where trust quietly leaks. Every join is a chance to double-count, every layer is a place a definition can drift, and every transformation is a line of logic someone has to maintain. A metric nobody can explain is a metric nobody can defend in a meeting. Scoring complexity makes the hidden cost visible, so the most fragile numbers get attention before they break.

The score combines three measurable properties of a rollup into one figure. Depth and breadth multiply because their effect compounds: a deep chain over many sources is far harder to reason about than either alone. Transformation count is added on top, because each join, filter, and calculation adds risk roughly in proportion to its number.

The exact weights are a team choice, and consistency matters more than precision. Pick a formula, apply it to every metric, and compare the scores against each other. The value of the number is relative, telling you which of your metrics is the riskiest, not whether it crosses an absolute threshold.

1. 1

   Measure dependency depth

   Count the aggregation layers from raw source to the final metric. A metric built on another rolled-up metric inherits its depth plus one.

2. 2

   Measure source breadth

   Count the distinct source tables or feeds combined into the metric. More independent sources means more ways the inputs can disagree.

3. 3

   Count transformations

   Count the joins, filters, deduplications, and calculations applied along the chain. Each is a maintainable, breakable piece of logic.

4. 4

   Combine into the score

   Multiply depth by breadth, then add the transformation count. Apply the same formula across every metric so the scores are comparable.

A complexity score on its own tells you a metric is risky but not where the risk concentrates. A metric tree decomposes the score into its drivers, so a high number resolves into a specific cause: too many layers, too many sources, or too much transformation logic. That is the difference between knowing a metric is fragile and knowing which join to simplify.

The drivers map directly to the formula. Depth, breadth, and transformation each break into smaller, fixable causes, and each one can be owned by the person who maintains that part of the pipeline.

Because the formula and weights are set per team, the ranges below are guidance for a typical depth-times-breadth-plus-transformations scoring. What matters is how a metric ranks against your own portfolio. A score that sits in the top decile of your metrics deserves a test suite and an owner, whatever its absolute value.

Lowering the score means simplifying the path from source to value, not hiding the complexity behind a longer query. Each driver in the tree suggests a different move, and the largest contributor is where to start.

1. 1

   Driving the score to zero

   Some metrics are inherently complex. Forcing the number down by hiding logic elsewhere moves the risk rather than removing it.

2. 2

   Using inconsistent weights

   Scoring metrics with different formulas makes the numbers incomparable. The value of the score is relative, so the method must be uniform.

3. 3

   Ignoring inherited depth

   A metric built on another rolled-up metric carries all of its complexity plus more. Counting only the final query understates the real score.

4. 4

   Scoring without acting

   A complexity score that nobody owns is a number that flags risk and then sits idle. Pair each high score with a test, a document, or an owner.