What a missing exchange rate does to a sale
App Store revenue arrives in whatever currency the storefront settled in. Normalizing it is one ASOF JOIN against a daily rate table: take the most recent rate at or before the transaction date, multiply, produce revenue_usd. The ASOF part is deliberate — carry the last known rate forward across a day where the fetch failed, rather than losing the sale to a missing row.
The gap in that design is what happens when the table has no rate at all for the currency, on or before the date.
ASOF LEFT JOIN dim_fx_rate_deduped AS fx
ON e.currency = fx.currency
AND e.timestamp >= fx.rate_date
A LEFT ASOF JOIN that matches nothing yields NULL. revenue_usd becomes NULL. And here is the part that took us months to fully accept: a NULL in a revenue column is not a missing bar in a chart — it is a sale that no aggregate will ever count. sum() skips it. Every USD rollup, every dashboard, every LTV curve, every ROAS number — all of them are computed correctly over a set that quietly no longer contains that transaction. No error is raised. No job fails. The pipeline is green and the number is wrong.
A single 19.99 purchase in an uncovered currency was missing from our marts entirely. We found out because someone compared a subscription screenshot in the App Store console against our numbers and asked where it went. That is an acceptable way to discover it once and a completely unacceptable way to discover it forever, which is what the rest of this post is about.
The report that already knew
We were not blind. A coverage model had existed alongside the join since the rate table was introduced, and it reported missing and stale rates per day, per currency. It was also, structurally, too late.
A report answers "what went wrong" after the data has been committed, aggregated, and read by people making decisions. Its value is diagnostic. The value we needed was preventive, and the cheapest way to get it is to ask the question one stage earlier — at the moment money enters the system, while the transaction is still in hand and still attributable.
The criterion is the part that took the longest to get right, and it is the part worth stealing:
Coverage is whether a currency has a rate at or before the day the money was taken — never whether it has a rate today.
ASOF only looks backwards. A currency that first appears in a storefront in August, whose rate sync starts in October, has a rate today. Every naive coverage check — "do we have this currency", "is the sync job alive", "is the newest row recent" — answers yes. And the August revenue still joins to nothing, because nothing existed to its left. Backfilling today's rate does not travel back in time. The check that looks like a fix is the one that certifies the hole as filled.
Asking at the entrance
fxrate.CoverageGuard moves the question upstream. It loads one aggregate at startup — a couple of hundred currencies, negligible — and holds the earliest rate date for each:
SELECT currency, min(rate_date)
FROM dim_fx_rate
GROUP BY currency
Every revenue row then passes through one question, asked at ingestion: can this money be converted at the rate for the day it was taken? If the answer is no, the gap is recorded the day it happens, against the specific currency and date that caused it, while there is still a transaction to attach it to. The same idea later generalised to LLM credit pricing, where the question was identical and only the unit changed.
The keys are normalized upper+trim before the comparison, on both sides. This is unglamorous and it matters more than it looks: if the guard normalizes but the warehouse's join key does not (or vice versa), the guard reports coverage for a currency string the warehouse has never seen, alarms on it, and in the process teaches everyone to ignore it. A quality guard that cries wolf about casing is worse than no guard, because the next alert gets triaged with the same dismissiveness.
Two quieter traps underneath
Building the guard surfaced two bugs that had been sitting in the join itself, both of the same species: something that is wrong by accident and indistinguishable from correct.
Zero revenue is not unknown revenue. Free-trial and other $0 events were multiplied by the rate like everything else. In ClickHouse, 0 * NULL is NULL, not 0 — so a trial event in an uncovered currency came out of the join as NULL and got swept into every revenue_usd IS NOT NULL filter that meant "we know what this was worth." The fix is an explicit short-circuit, so that the amount is 0 and the coverage flag still tells the truth about whether a rate was ever found:
if(e.revenue_micros = 0, 0, e.revenue_micros / 1e6 * fx.usd_per_unit) AS revenue_usd,
if(fx.usd_per_unit IS NULL, NULL, dateDiff('day', fx.rate_date, toDate(e.timestamp))) AS fx_rate_age_days
A missing match must actually be a missing match. By default, ClickHouse fills unmatched columns with the type's zero value rather than NULL. A 0.0 rate would mean a transaction's revenue silently computed as zero — a different failure, in the opposite direction, with the same invisibility. Making the rate column Nullable is a schema migration with a one-line reason comment attached, and it is the only reason the NULL semantics of the join are the ones we intended.
Takeaways
In a warehouse, NULL is not an incomplete answer — it is an exclusion. Any aggregate that sums a column will skip it, forever, without complaint. If a NULL can mean "no answer yet," you need a separate column to say so, because the two are indistinguishable downstream and only one of them should be filtered.
Judge coverage by the earliest fact you need, not by the latest state you can observe. Backward-looking joins (time-travel queries, ASOF joins, effective-dated lookups) can only be satisfied by data that already existed, so a coverage check that asks about the present will happily certify a historical hole.
And ask the question at the entrance, not in the report. A report tells you what a pipeline produced; only a guard at ingestion can tell you what it should have refused. The difference is a week of dashboards versus a same-day trace attached to a transaction that is still in memory.