Return To Sender

Finance: the outbound shipping subsidy is running €31k a quarter against a €22k budget, and the VIP programme is meant to be 2% of customers. How many VIPs are there?

• Gold model: 2,174 VIP customers (2.42% of the base). Every dbt test green, six months running.
• SELECT count(*) FROM shop_customer_attributes WHERE vip_free_shipping — 5,304.

Both systems report their own contents honestly. The warehouse holds the right answer; the shop holds every answer the warehouse has ever given.

Lesson 11’s rule was that gold is the only layer allowed to leave the warehouse. Reverse ETL is what leaving looks like: a modelled table pushed into an operational tool (marketing audience, CRM field, entitlement flag, support-desk badge), so one tested definition drives every tool.

Every other consumer of gold produces a table — a dashboard is a query, a downstream model is a table, a test can read both. A reverse ETL sync produces an HTTP call, and its output lives in a system your warehouse cannot query. That is the whole lesson.

Source SELECT customer_id FROM gold.customer_vip_status WHERE is_vip; mode upsert; hourly.

One of four cells fails — the cell that fires every time somebody stops qualifying, which for a trailing-window definition is every day.

The error runs one way only, and provably: the shop’s flag set is the union of every daily result set, so it always contains today’s. On day 359, with 3,130 wrong flags in the system, missing_in_shop = 0. No genuine VIP ever loses their perk, so there is never a support ticket.

A sync that can only add is not a copy of your data. It is a copy of everything your data has ever said.

• Gap growth +17.49 customers/day (range −3 … +42; it shrank on 2 of 179 days).
• Day 102 after cutover: more than half the shop’s VIP flags are wrong.
• 7,755 parcels shipped free to non-members = €37,999.50 over 180 days (€211.11/day), 17.15% of the entire free-shipping subsidy (legitimate: 37,463 parcels, €183,568.70).

Cadence is not correctness. Run the old monthly full-replace over the same 180 days: 448 parcels, €2,195.20. The hourly automated sync leaked 17.31× more than the human it replaced while running 720× as often. And the pre-cutover baseline was not zero either — the manual process leaked €11.00/day against €211.11/day after, a 19.2× step change.

Revenue metrics could not see it, structurally. Free shipping is a cost, not a discount: the order total is unchanged and the €4.90 lands in a logistics line in another system’s ledger. Post-cutover GMV €25,962,431.88 across 161,765 orders is bit-identical to what a correct sync would have produced. (Lesson 12’s property: when the headline metric is mathematically untouched, “nobody noticed” is arithmetic, not carelessness.)

The data team’s own tests could not see it, by design. Every test points at gold.customer_vip_status, and that table is right.

One trace existed: the share of parcels shipped free, computable from the shop’s own shipping table, in no dashboard and no model. Lesson 06’s band instrument, aimed at it:

A single band crossing is noise — daily GMV, with nothing wrong with it, first crosses its band on day 181; the free-shipping share first crosses on day 186. The discriminator is the rate, and a 10-day run rule separates them cleanly.

A fourth shape of failure

Lesson 11 named the constant (invisible because it never changes). Lesson 12 named the drift (each increment inside the band, only the sum outside). This one is neither: it is loud, it is fast, it moves a measurable quantity by 12.6 percentage points — and it survived six months because it happened on the far side of a boundary nothing instruments. Not invisible. Unwatched.

Count equality is not set equality. Under the manual process the shop held a frozen list of roughly the right size and increasingly the wrong members: up to 668 stale flags and 708 missing at month-end, while the two counts never differed by more than 55 customers (2.50%). A1 is a false-positive machine; A2/A3 join row by row.

A2’s trick: monitor one number — of the flags the shop holds, what share is stale? Under any full-state write (a human with a CSV included) it returns to 0.00% on every run; its 30-day minimum was 0.00% for the whole manual era. Under an upsert it never returns to zero again.

The uncomfortable part: A2 and A3 would have been firing during the manual era too — correctly, because up to 31.08% of the shop’s flags were already wrong at month-end. The destination was never right. Reverse ETL did not introduce the error; it removed the ceiling on it, turning a sawtooth that reset every 30 days into a ramp that reaches 59.01%.

A2 is also the only check that needs nobody to have predicted this bug: it would equally catch a truncated batch, a silently rate-limited sync, a mis-mapped column, or an admin editing records by hand in the destination.

4,242 customers ever held a wrong flag. Disjoint four-way split (asserted to sum):

• 31.26% never ordered at all while wrongly flagged and cost nothing — falling out of a trailing-spend window is mostly caused by having stopped buying.
• 51.1% of the money (€19,428.50) went to customers who later re-qualified anyway.
• The tail is thin: heaviest single offender took 8 free parcels; the top decile is 21.72% of the wrongly-free volume. No account looks abnormal; no fraud rule fires. The loss is only visible in the total — which is exactly where finance found it.

Destination: 100 records/request, 60 requests/minute. The filter looks like a 16.7× saving over the mirror, and it is, and it is also the bug. The diff keeps the mirror’s correctness and beats the filter: ~57.56 changed rows/day (max 86), one small batch an hour, 900× fewer requests than the mirror. Most tools implement this and keep the sync-state table for you — the mistake is filtering the source query so the tool never learns a row went false.

The repair is one sync: 3,138 changed rows (3,130 flags to clear, 8 to set), 32 requests.

Three more things that bite on the way out:

• Rate limits, not bytes scanned. Lesson 10’s cost unit does not apply; the destination’s API quota is the scarce resource, and it is shared with every other integration.
• Idempotency needs a stable external ID. Sync on email and an address change makes two records; sync on an unindexed internal ID and every run duplicates. (Lesson 04’s question asked of somebody else’s database; Lesson 19 is the fuzzy version.)
• Deletion has to propagate. An append-only audience in a marketing tool is a copy of a customer your erasure job has never heard of → Lesson 14.

-- 01 · the gold model. Note what it does NOT have: a WHERE clause.
CREATE OR REPLACE VIEW gold.customer_vip AS
WITH win AS (
  SELECT customer_id, SUM(net_value) AS net_90d, COUNT(*) AS orders_90d
  FROM   orders
  WHERE  order_day BETWEEN :as_of - 89 AND :as_of
  GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(w.net_90d, 0)    AS net_90d,
       COALESCE(w.orders_90d, 0) AS orders_90d,
       COALESCE(w.net_90d, 0) >= 700.00
         AND COALESCE(w.orders_90d, 0) >= 3 AS is_vip
FROM   dim_customer c
LEFT JOIN win w USING (customer_id);
-- 90,000 rows, of which 2,174 is_vip. The row count IS the fix.

-- 02 · the boundary reconciliation. Run after every sync, against a snapshot
--      read back OUT of the destination — not the rows you believe you sent.
SELECT count(*) FILTER (WHERE w.is_vip AND s.customer_id IS NULL)   AS missing_in_shop,
       count(*) FILTER (WHERE w.is_vip AND s.vip_free_shipping)     AS agree_true,
       count(*) FILTER (WHERE NOT w.is_vip AND s.vip_free_shipping) AS stale_in_shop
FROM   gold.customer_vip w
FULL OUTER JOIN shop_customer_attributes s USING (customer_id);
-- 0 · 2,174 · 3,130  →  59.01% of the shop's flags are stale

-- 03 · the diff. Correct AND cheap: only rows whose state changed.
SELECT w.customer_id, w.is_vip AS vip_free_shipping
FROM   gold.customer_vip w
JOIN   sync_state p USING (customer_id)
WHERE  w.is_vip IS DISTINCT FROM p.vip_free_shipping;
-- 3,138 rows today (the repair); ~57.56 tomorrow

1. Which of our syncs run in upsert mode, and what happens to a row that leaves the source query?
2. Do we ever read the destination back — not the sync tool’s success log, the destination itself?
3. Which operational behaviour does each synced field switch on, and who owns the cost of it?
4. Is the source of every sync a gold model, or somebody’s ad-hoc query?
5. When we delete a customer, what deletes them from the seven tools we push to?

1. Run the generator; confirm dest.at_end = 5,304 and truth.at_end = 2,174.
2. Build the wrong sync in twenty lines (for c in query_result: shop[c] = True). Notice nothing you can write inside that loop could ever remove a key.
3. Run the reconciliation for every day 90 → 359. Plot stale_in_shop / shop_true: a sawtooth that returns to zero every 30 days, then a ramp that never does. Implement alarm A1 and watch it breach 27 times before the bug exists.
4. Compute post-cutover GMV under the buggy sync and a correct mirror. They must be equal to the cent (€25,962,431.88). No revenue test of any sophistication could have found this.
5. Implement mirror and diff; count rows/day for each (~90,000 vs ~57.56).
6. Break the fix: give the API a 1% per-batch failure rate and re-run the diff sync without updating the sync-state table on failure, then with it. One of the two silently loses the retraction forever. This is the bug you will actually ship.
7. Push to GitHub with a README stating in one sentence what the reconciliation guarantees and what it does not.

A reverse ETL sync is the only pipeline whose output you cannot query, so it is the only one where “the model is green” tells you nothing. Sync state rather than membership — one row per entity carrying the value, false included — and read the destination back after every run. The useful version of that check is not a row-count compare (counts agree while membership rots) but a row-level join whose stale share must return to zero after every run: €406.70 on day 31, against €37,999.50 on day 180 when a budget variance found it instead.