Three Ways a Measurement Layer Lies to You

Most data quality problems announce themselves. A pipeline fails, a dashboard goes blank, someone files a ticket. Those are the easy ones.

The expensive failures are quiet. The number is wrong, nothing is broken, and the tests are green. I want to walk through three of these, because they share a structure: each one produces a plausible number, each one survives the tests people normally write, and each one is fixable once you see the shape.

The setting is a usage-based SaaS company. Free tier, paid tier, a cloud bill that dominates cost of goods. Substitute your own nouns; the shapes are the same.

1. The cost allocation that cannot fail

You want to split your cloud bill three ways: cost caused by paying customers, cost caused by free users, and cost caused by nobody. That third bucket is the whole reason to build the thing. It is where idle capacity and over-provisioning live, and it is the number a CFO actually wants.

So you compute a unit cost from what you know:

rate      = monthly_bill / total_recorded_seconds
allocated = rate × (paid_seconds + free_seconds)
residual  = monthly_bill − allocated

Put numbers on it. A $1,000 bill, 10,000 recorded compute-seconds, 6,000 from paying customers and 4,000 from free users. The rate is $0.10. Paid absorbs $600, free absorbs $400, and the residual is zero.

Change the numbers and the residual is still zero. Change them again. It is zero in every month, for every input, before any data arrives.

The reason is that paid and free exhaust the population. Their seconds sum to the total, and the rate was defined by dividing by that same total. Multiply back and you recover the bill exactly. You divided by ten thousand and then multiplied by ten thousand.

This is worse than a useless number. If half that bill were idle machines running nothing, the method cannot show it. The idle cost gets absorbed into an inflated rate and smeared across the workloads you can see, so every paying customer looks more expensive than they are and the waste never appears. In cost accounting this is called full absorption. It is a legitimate technique. It is not legitimate when your column headers promise three buckets and the third is structurally empty.

And the test people write for this is worse still:

-- looks rigorous, is not
abs(paid_cost + free_cost + residual_cost - total_bill) < 0.01

That reduces to abs(0) < 0.01. It passes forever. It is a green check mark attached to nothing.

The fix is one move: the price cannot come from your own bill. Take it from the contract, the rate card, the committed-use agreement. Say the contracted rate is $0.06 per second. Now allocated cost is 10,000 × $0.06 = $600, and the residual is $400 of capacity that no workload explains. That number moves month to month. Someone can act on it.

It can also come out negative, which is informative rather than broken: either you are paying below rate card, or you are recording more usage than actually ran. Both are worth knowing.

The test that works asserts the residual is a sane share of the bill, say between −10% and 60%, and it can genuinely fail. When it does, either the rate is wrong or your capacity planning has drifted. Either way you learn something, which is the entire point of a test.

2. The identity join that merges two customers

Every company past a certain age has several identifiers for the same human: an auth user id, a billing customer id, an email, a device id, maybe a machine id from a desktop client. None of them span all your tables.

So you build a bridge. Where the billing id is missing, you match on email, because email is what you have.

The naive version is a join. The slightly better version adds a uniqueness check on one side. Both are dangerous, because they can attach one billing customer to two different people, and when they do, revenue double-counts and nothing complains. The email on an invoice is frequently not the email on the account. Someone signed up with a personal address and pays with a corporate card. That is normal, and it is the exact condition that breaks the join.

What I have found works is four guards, all required:

1. The email maps to exactly one billing customer.
2. That billing customer maps to exactly one email.
3. No second account shares that email.
4. That billing customer is not already claimed by a deterministic link.

The fourth is the one people miss, and it is the one that stops the double-count.

The deeper principle matters more than the guards. Confidence belongs in a column, not in a filter.

You have two options for an uncertain match. You can decide once, at build time, and either keep it or drop it. Either way the uncertainty is destroyed and no downstream consumer can tell which links were guesses. Six months later “how solid is our revenue attribution?” becomes unanswerable, because the evidence was thrown away.

Or you keep the match and keep the doubt, side by side, as data:

coalesce(link_confidence, 'unresolved') as identity_confidence
-- 'deterministic' | 'probabilistic' | 'unresolved'

Now every consumer decides for itself. A weekly signup count can use everything. A margin number going in front of the board can ask what share of its revenue rests on probabilistic links, and that is a one-line query rather than an impossible one. If the bridge starts degrading, the drift is visible in a monitoring query instead of silently changing your tables.

The identifiers that resolve to nothing do not get deleted either. They go to a table with a reason code, and they get monitored in dollars rather than in row counts, because ten orphaned trial accounts matter less than one orphaned enterprise customer.

One last thing on direction of error. This design deliberately under-resolves. It refuses matches a looser method would accept. That is the correct direction, because an orphan is visible and fixable, and a wrong merge is silent and permanent.

3. The fact table that changes months

Some facts settle late. A job completes, gets billed, and then reconciliation corrects it three weeks later. The correction is legitimate. The problem is what your model does with it.

Almost every source table has an updated_at that moves when a row is corrected. If you bucket a fact by updated_at, or by any column that moves, a July event corrected in August silently becomes an August event. July’s usage shrinks. July’s bill does not. Both months are now wrong, and the difference is small enough to look like noise.

The obvious fix is to bucket by creation time instead, since that never changes. It is the wrong fix. The cloud bill arrives when work runs, not when it is requested, so a job submitted July 31 and executed August 1 puts its usage in July and its dollars in August. You have traded a moving bucket for a permanently misaligned one.

What actually works is pinning the settle time on first observation and never overwriting it. The warehouse becomes the history that the source system does not keep:

{{ config(materialized='incremental', unique_key='job_id',
          incremental_strategy='merge') }}
-- settled_at is stamped the first time a row arrives in a
-- terminal state and is never updated again, even when
-- updated_at moves

This matters twice. It keeps the fact in its correct month. And if you impute anything from duration, it prevents a corrected row from inflating a two-hour job into a three-week one.

Then there is the consequence nobody plans for. If facts keep correcting for thirty days, a monthly aggregate genuinely restates for thirty days. Finance needs a frozen close, and analysts need to know which months are still moving. So the mart carries a flag:

dateadd(day, 35, dateadd(month, 1, month)) <= current_date
    as is_final

That converts “the number changed” from an incident into a report.

It also has an operational consequence people discover the hard way. Anomaly detection on a metric that legitimately restates will page you every night. So the alerting runs against the final series, not the live one, and provisional months are excluded until they close.

What these have in common

None of these produce an error. Each produces a number that looks right, in a pipeline that runs green, guarded by a test that cannot fail.

That is the pattern worth internalizing. When you write a test, ask whether there is any state of the world in which it fails. If there is not, it is decoration. The allocation test that sums buckets back to the bill, the uniqueness check on the wrong side of a join, the freshness check on a table that always lands: all decoration.

The tests that earn their place assert something that could be false. That the residual is a plausible share of spend. That orphaned revenue stays under a threshold. That a closed month reconciles to its source. Those fail sometimes, and when they do, you learn something about the business rather than about your code.