TransformGENERALWAREHOUSE-SPECIFICSIMPLIFIED

SQL Transformations

The five-line aggregate everyone writes, and the thirty-line one that is still correct after duplicates, refunds, currency and late data exist.

What actually happens How to build it Can I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Why does the obviously correct SUM(revenue) GROUP BY customer_id stop being correct, and what does the correct version actually have to account for?

Who needs this

A growth team deciding how much to spend acquiring a customer, using lifetime value. If the number is inflated by duplicates or by unrefunded refunds, the company overspends on acquisition for a quarter before anyone notices, and the person who notices is in finance.

What one row is

Input grain is one delivered change record per order — not one order. Output grain is one row per customer. Two grain changes happen in one statement: change records collapse to orders, then orders collapse to customers. Neither is visible in the SELECT clause (Grain: What Does One Row Represent?).

The obvious build

Write the aggregate the question asks for. SELECT customer_id, SUM(revenue) AS lifetime_value FROM orders GROUP BY customer_id. It is the correct translation of the English sentence, it is what any competent engineer writes first, and it is right for as long as orders contains one settled, single-currency, final row per order.

Why it breaks

The pipeline delivers at-least-once, so orders holds the same order more than once after any connector restart or job retry. SUM counts each copy. Lifetime value rises for exactly the customers whose orders happened to be redelivered (Duplicate Rows).

How it breaks with real data

The pipeline delivers at-least-once, so orders holds the same order more than once after any connector restart or job retry. SUM counts each copy. Lifetime value rises for exactly the customers whose orders happened to be redelivered (Duplicate Rows).
Refunds live in a separate table nobody mentioned. Revenue is gross, the business means net, and the gap is largest for exactly the customers the growth team is most interested in.
Orders arrive in several currencies. SUM(revenue) adds numbers with different units and returns a number with no unit at all — a value that is precise, plausible and meaningless.
A CDC update to an order lands after the model ran. The order is in the table twice at two different states, and SUM takes both — the pre-update amount and the post-update amount (Late-Arriving Data).
The status filter is missing entirely, so cancelled and test orders are included. Adding WHERE status = 'completed' then excludes 'completed_partial', which the application introduced last month.
The model is re-run to fix a bug and appends rather than replaces, so every customer's lifetime value doubles and the fix is now the incident (Idempotent Data Pipelines).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

SQL is a declaration of a result, not of an assumption. GROUP BY customer_id declares the output grain; nothing declares the *input* grain, so the statement is silently asserting that one row of orders is one order — which is a claim about your pipeline, not about your SQL (Grain: What Does One Row Represent?).
SUM ignores NULL. This is correct by the standard and is the mechanism behind more wrong dashboards than any other single behaviour: one bad cast, one missing exchange rate, one unmatched left join, and the total falls by the affected share with no error and no row-count change.
Deduplication in SQL is a window function, not a DISTINCT. ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY <sequence> DESC) picks one row per key, and the ORDER BY inside it is the entire correctness of the operation — order by arrival time and an out-of-order update wins (Window Functions).
Refunds, currency and status are not edge cases bolted onto a revenue query. They are the *definition* of revenue. The five-line version is not a simpler implementation of the same metric; it computes a different metric that happens to agree while the data is clean (The Metrics Layer).
Every correction above adds a join, and every join added is another chance to change the grain. The correct query is longer *and* has more ways to be wrong, which is why it needs tests rather than review (Data Tests).

The query everyone writes

Here is lifetime value, written the way the question was asked. It is a faithful translation of "total revenue per customer" into SQL, it is what a reviewer would approve, and there is nothing to criticise about it in isolation.

What makes it wrong is not the SQL. It is a set of assumptions the SQL is silently making on your behalf: that one row of orders is one order, that every amount is in one currency, that revenue is gross, and that no order will ever be updated after this ran. Each assumption is true of a small clean system and false of every system that has been in production for a year.

Before reading the corrected version, it is worth writing those assumptions down as a list, because the corrected version is nothing more than that list expressed as clauses.

Lifetime value, as first written

1SELECT
2  customer_id,
3  SUM(revenue) AS lifetime_value
4FROM orders
5GROUP BY customer_id

Four silent assumptions: one row per order, one currency, gross equals net, and nothing arrives late. None of them is stated, so none of them is tested, and each one fails independently.

The same query once the data is real

ENGINE-SPECIFICThe nested ROW_NUMBER subquery is the portable form. Warehouses with QUALIFY collapse it to a single clause, and engines with ARG_MAX-style aggregates express latest-per-key without a window at all — the semantics are identical, the plan is not, and the shuffle cost differs between them.

The version below computes the same business concept against a pipeline that delivers at least once, a business that issues refunds, a company that sells in several currencies, and a source that updates orders after the fact. It is not a different metric — it is the first query with its assumptions made explicit.

Read it clause by clause against the list. The ROW_NUMBER CTE deduplicates to one row per order, ordered by the source's own commit sequence rather than by when the row happened to arrive. The refund join subtracts settled refunds. The rate join converts to one currency using the rate that applied on the day of the order. The status filter is explicit about what counts. The ingestion boundary makes the whole thing a function of a stated range instead of of the wall clock.

The exchange-rate join is deliberately an inner join. A left join here would be the more forgiving choice and the more dangerous one: an order in a currency the rate table does not cover would multiply by NULL, and SUM would skip it silently. An inner join makes the row disappear too — which is why it is paired with a completeness test that fails the build rather than moving the number.

Two queries, one metric

SUM over the source table

`SELECT customer_id, SUM(revenue) FROM orders GROUP BY customer_id`, run nightly, appending to a target table.

Deduplicate, net, convert, bound, replace

Deduplicate to one row per `order_id` by the source's commit sequence, subtract settled refunds, convert with a dated rate joined inner, filter status against a tested accepted-values list, bound the read by an ingestion boundary, and replace the target for that boundary.

At-least-once delivery means the source table legitimately contains a given order more than once, and SUM has no way to know that two rows describe one event — so the naive query overstates by exactly the redelivery rate, which is invisible because the row count grew for a reason that looks legitimate. The corrected version collapses to one row per business key before aggregating, which is the only point in the statement where duplication can still be seen. The remaining clauses fix the same class of problem in different places: NULL from an unmatched rate join vanishing inside SUM, gross being silently substituted for net, and an append re-run adding rows rather than converging.

Lifetime value, correct against duplicates, refunds, currency and late updates

1WITH bounded AS (
2  SELECT *
3  FROM raw_orders
4  WHERE ingested_at < :run_boundary
5),
6 
7deduped AS (
8  SELECT *
9  FROM (
10    SELECT
11      b.*,
12      ROW_NUMBER() OVER (
13        PARTITION BY b.order_id
14        ORDER BY b.source_commit_seq DESC, b.ingested_at DESC
15      ) AS version_rank
16    FROM bounded b
17  ) ranked
18  WHERE version_rank = 1
19    AND op <> 'delete'
20),
21 
22settled_refunds AS (
23  SELECT
24    order_id,
25    SUM(amount_minor) AS refunded_minor
26  FROM raw_refunds
27  WHERE status = 'settled'
28    AND ingested_at < :run_boundary
29  GROUP BY order_id
30)
31 
32SELECT
33  d.customer_id,
34  SUM(
35    (d.amount_minor - COALESCE(r.refunded_minor, 0)) * fx.rate_to_eur
36  ) / 100.0                                   AS lifetime_value_eur,
37  COUNT(DISTINCT d.order_id)                  AS order_count,
38  MAX(d.order_ts)                             AS last_order_at,
39  CAST(:run_boundary AS DATE)                 AS as_of_date
40FROM deduped d
41LEFT JOIN settled_refunds r
42       ON r.order_id = d.order_id
43JOIN dim_fx_daily fx
44       ON fx.currency = d.currency
45      AND fx.rate_date = CAST(d.order_ts AS DATE)
46WHERE d.status IN ('completed', 'completed_partial', 'shipped')
47GROUP BY d.customer_id

Notice as_of_date in the output. Lifetime value is never final — a refund can settle months later — so the model publishes the boundary it was computed under instead of pretending to a finality it does not have.

The tests that make the query trustworthy

The corrected query is longer, which means it has more places to be wrong, not fewer. What makes it trustworthy is not its length but the assertions attached to it — each one corresponding to an assumption the query is still making.

Note where the tests sit. A uniqueness test on the *output* is worthless here, because GROUP BY customer_id cannot produce a duplicate customer no matter how broken the input is. The test that catches duplication has to sit on the deduplicated intermediate, which is an argument for materialising that CTE as its own model rather than hiding it inside one statement (Model Layering).

Read the misses column carefully. Every one of these tests is a statement about structure, and the failure that most often reaches an executive is a statement about meaning. No amount of structural testing will tell you that the business means net-of-shipping and the model computes net-of-refunds.

What to assert, and what each assertion cannot see

Check	Expresses	Catches	Still misses
Uniqueness of `order_id` in the deduplicated model	One row per order — the input grain the aggregate assumes.	A deduplication key that is wrong, a window `ORDER BY` that ties, a source that changed its identifier semantics.	The same order redelivered under a new `order_id`, which is two orders as far as any key-based test can tell.
Zero unmatched rows against `dim_fx_daily`	Every currency-date pair in the input has a rate.	A new market launching, a rate feed that stopped, a weekend or holiday gap in the rate table.	A rate that exists and is wrong. Coverage is not accuracy, and a stale rate joins perfectly.
Accepted values on `status`	The set of statuses is the set the filter knows about.	A new status introduced upstream — the change most likely to move this metric (Semantic Changes).	An existing status whose meaning changed, which is the same string and a different thing.
Refund total in the model versus refund total in the source	The refund join neither dropped nor multiplied.	A fan-out from refunds being per line rather than per order; refunds excluded by a status filter that is too narrow.	Refunds that were never ingested at all — reconciliation against a table only proves you agree with that table (Reconciliation).
Re-run a closed period and compare with the published result	The model is a function of its inputs, not of when it ran.	A mutable dimension read as current state, a `now()` buried in logic, an append that should have been a replace.	Determinism is not correctness. A model can reproduce the same wrong number forever, which is exactly what makes it convincing.

Four structural tests and one determinism test. Together they cover every failure in this lesson except the one that matters most — a definition nobody wrote down — which is what the metrics layer exists to address (The Metrics Layer).

How to build it

Most important first.

Deduplicate to the declared grain first, in its own CTE or its own model, with an explicit ordering key that comes from the source's own sequence — a commit LSN, a binlog position, a monotonic version column — never from arrival time.
Handle money as integer minor units and convert currency with an explicit rate table joined on the *date of the transaction*, not on today. Floating point and implicit rates are how two teams get two totals that both look right.
Make the currency join an inner join with a completeness test, not a left join. A left join with a missing rate multiplies by NULL, SUM skips it, and the row vanishes from the total while remaining present in every row count.
Express exclusions positively where the set is stable and negatively where it is not, and in either case add an accepted-values test on the column so a new status fails the build instead of moving the number.
Bound the read explicitly — an ingestion-time or event-time boundary passed in as a parameter — so the model is a function of a range rather than of the moment it ran (The High-Water Mark).
Write the result by replacing the target rather than appending, so a re-run converges instead of accumulating (Full Refresh vs Incremental).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

The corrected query guarantees one row per customer, because GROUP BY customer_id makes that structurally true. It guarantees nothing about whether every customer is present.
It guarantees at-most-one row per order in the aggregate — but only for duplicates that share an order_id. A source that re-emits the same order under a new business key defeats it entirely, and no SQL can detect that.
It guarantees a single currency in the output, conditional on the rate table covering every currency-date pair in the input. That condition is a test, not a property.
It guarantees nothing about completeness of any open period. Every late-arriving change for a period still open will change the answer, and that is correct behaviour rather than a bug (Late-Arriving Data).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Uniqueness on customer_id in the output, and uniqueness on order_id in the deduplicated CTE materialised as its own model so it can be tested. Testing the intermediate is what localises the failure.
A completeness test on the exchange-rate join: zero rows in the input whose (currency, date) pair is absent from the rate table. This is the test that stops the silent-null-drop mechanism.
An accepted-values test on status, so the introduction of a new category fails the build rather than moving the metric.
All of them together still miss the definitional question. If the business means net-of-refunds-and-shipping and the model computes net-of-refunds, every test passes and the number is wrong by shipping (Two Dashboards, Two Numbers).

Freshness

The bounded read is what makes freshness explicit: the model computes a result as of a stated boundary, and a consumer can be told what that boundary was rather than guessing from a run timestamp.
Deduplication forces a wait. Picking the latest version of an order means being reasonably sure no later version is in flight, which converts freshness into a lateness decision rather than a scheduling one.
The refund join makes lifetime value *permanently* provisional — a refund can arrive months after the order. A model like this is never final, and the honest treatment is to publish it with an as-of date rather than to pretend otherwise.

When the schema or meaning changes

A new status value is the change most likely to break this query, and it will arrive as a perfectly valid application feature with no schema change at all (Semantic Changes).
A new currency appears the day the company launches in a new market. Without the completeness test, that market's revenue is silently zero on the day it starts mattering most.
A widened amount type — integer minor units becoming a decimal, or a formatted string from a new export path — meets the cast and either raises or nulls. Decide which before it happens (Nullability & Defaults).

How to re-run this safely

Because the corrected query is bounded and replacing, repairing a period is re-running it for that period. That is the entire payoff of the extra thirty lines.
Repairing the naive version is harder than it looks: you cannot tell from the output which customers were inflated, because the inflation is invisible in the result. The only route is to rebuild from raw (Keeping Raw History: The Recovery Position and the Liability).
When the definition itself changes — net becomes net-of-shipping — the recomputation is a full-history rebuild and a communication problem, not a backfill. Every previously published number changes, and consumers must be told before it happens (Planning a Backfill).

What can go wrong

Failure modes

Duplicates inflate the total, and the inflation is concentrated in whichever customers were redelivered, so it does not look like a systematic error.
A missing exchange rate silently removes rows from the sum while leaving the row count intact.
The deduplication window function orders by arrival time, so an out-of-order update loses to an older one and the order is frozen in a stale state.
The refund join fans out because refunds are per line, not per order, multiplying every order it touches.
The mitigation fails too: a uniqueness test placed on the final output passes trivially, because GROUP BY customer_id cannot produce a duplicate customer. The test that matters is on the intermediate.

Misreads

"The corrected query is over-engineered." Every clause in it corresponds to a real property of the data. Remove one and name which property you are asserting does not hold — that is the test of whether it is over-engineered.
"SELECT DISTINCT deduplicates." It removes rows identical in every selected column. Two deliveries of the same order that differ in ingestion timestamp are not identical, so DISTINCT removes nothing and appears to work.
"A LEFT JOIN is the safe choice." It is safe against dropping rows and unsafe against dropping *values*: an unmatched left join produces nulls, and nulls disappear inside aggregates without a trace (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
"We can fix this in the BI tool." The BI tool sits downstream of every check you wrote and is the one layer with no tests at all. Logic there is invisible to lineage, to review and to everyone else (The Metrics Layer).

Operating it

How you see it in production

Distinct order_id count versus row count in the input to the aggregate. Any gap is duplication, and it is one query (Duplicate Rows).
Count of rows where the exchange-rate join found no match, emitted as a metric even when it is zero, so the day it stops being zero is visible (Data Observability).
The set of status values observed per run, so a new one is noticed on the day it appears rather than at quarter end.
The distribution of lifetime value, not just its sum. Duplication moves the tail long before it moves the total enough to notice (Distribution Tests).

What changes at 10x and 100x

At 10x, the deduplication over all history stops fitting and must run over a bounded window with a durable record of what was already emitted (Incremental Processing).
At 100x, one customer with an outsized order count decides the runtime of the whole window function, because that key's partition lands on one worker (Data Skew).
Customer count scales the output linearly and harmlessly. It is *order* count and *change record* count that decide the cost, and they grow faster.

What drives cost here

The window function used for deduplication requires a sort or a hash partition by key, which is a shuffle — usually the most expensive operation in the model (The Shuffle).
Bounding the read by an ingestion boundary is the single largest cost reduction available here, because it turns a full-history scan into a partition scan (Partition Pruning).
Each additional join adds shuffled bytes. The refund and rate joins are small enough to broadcast in most warehouses; the deduplication is not (Broadcast Joins).

What this approach costs

The corrected query is six times longer and materially harder to read. It buys a number that stays correct when the data stops being clean, which is the only condition under which it will ever be looked at closely.
Deduplicating in SQL costs a shuffle on every run. Deduplicating at ingestion costs state and a different failure mode. Neither is free, and doing it in both places is how a pipeline gets slow without getting more correct (Deduplication).
Publishing lifetime value with an as-of date is honest and unpopular. Publishing it as a settled number is popular and wrong, and the disagreement surfaces during a board meeting rather than during a review.

SQL correctness lab

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

SQL correctness lab

Seven queries that run, return a number, and are wrong. None of them raise an error, and every one of them has been shipped.

The trap

What the query returns

430sim

What was meant

200sim

Raises an error

Returns no rows

The query as written

SELECT sum(o.amount)
FROM orders o
JOIN order_lines l ON l.order_id = o.id

The join multiplies each order by its line count, and sum faithfully adds the order total once per line. The measure is additive at order grain and the query is evaluating it at line grain.

How it looks in production: The number is a plausible multiple of the truth, and it moves when a product catalogue changes rather than when sales do.

SIMULATEDBoth numbers are computed from a four-order dataset defined in this component, so the wrong answer is genuinely what the wrong query returns rather than a claim about it.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALDeduplication by window function, currency conversion via a dated rate table and net-of-refund revenue are portable patterns. The syntax varies slightly — QUALIFY replaces the nested subquery in some warehouses — but the shape does not.
WAREHOUSE-SPECIFICSome warehouses support QUALIFY, which lets the row-number filter be written inline rather than as a nested subquery, and some support ANY_VALUE or ARG_MAX-style helpers that make latest-per-key a single expression. Where they exist the query is shorter; where they do not, the nested form shown here works everywhere.
SIMPLIFIEDThe example treats an order as having one amount and one currency. Real order models carry line items, discounts, tax and shipping, each of which is a separate decision about what belongs in revenue and each of which produces a differently defensible number.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasewindow-functions aggregation-and-grouping joins subqueries-and-ctes query-planner-internals

Backendjob-idempotency

Observabilityslow-query-workflow

DSAhash-set

Domains that do not exist yet

— Distributed Systems owns why the delivery underneath this query is at-least-once in the first place, and what it would cost to make it stronger. The deduplication here is the data plane paying for that guarantee downstream.