How ClickHouse's ReplacingMergeTree Let a Webhook Retry Storm Double-Count $118,440 in Revenue

08:14 UTC. #finance-ops: "today's GMV dashboard says $930,740 before 9am, that can't be right, yesterday's full day was $780K." The dashboard pulls straight from ClickHouse, refreshes every 60 seconds, and nothing about its query had changed in months. Orders were flowing normally. Checkout wasn't slow. Nothing was on fire anywhere an on-call engineer would normally look.

the scramble

First theory: duplicate events from Kafka. The order pipeline publishes a payment_captured event per status change, a materialized view consumes it into a ClickHouse table called orders_rt, and if the consumer somehow processed the same Kafka offset twice, every re-read would look exactly like this, revenue inflated with no corresponding spike in real orders.

That theory died fast. The consumer group's committed offsets matched the topic's log end offsets exactly, no rebalance in the last 24 hours, no lag, no gap. The producer had idempotent writes enabled and the broker confirmed zero duplicate sequence numbers for the window in question. Whatever was inflating the dashboard wasn't Kafka re-delivering anything. The events arriving were each legitimately new.

the hunt

If the events were legitimate, the next place to look was how many rows actually existed per order in ClickHouse itself:

SELECT order_id, count() AS versions
FROM orders_rt
WHERE created_at >= today() - 1
GROUP BY order_id
HAVING versions > 1
ORDER BY versions DESC
LIMIT 5;

│ order_id   │ versions │
│ ord_9f21c8 │        6 │
│ ord_7a40e1 │        5 │
│ ord_c88b02 │        5 │
│ ord_1de934 │        4 │
│ ord_5c7a90 │        4 │

14,900 distinct order_id values had more than one row in the last 24 hours, 31,460 extra rows in total. orders_rt is a ReplacingMergeTree keyed on order_id and versioned by updated_at, which is supposed to mean the latest row wins and old ones eventually disappear. They hadn't disappeared. That pointed at the merge process itself, not the data arriving:

SELECT table, count() AS active_parts
FROM system.parts
WHERE table = 'orders_rt' AND active
GROUP BY table;

│ table       │ active_parts │
│ orders_rt   │         2180 │   -- baseline is 40-60

SELECT count() FROM system.merges WHERE table = 'orders_rt';
│ 212 │   -- merges actively queued, not yet applied

ReplacingMergeTree only collapses duplicate keys when a background merge actually runs. A plain SELECT sum(total_cents) against the table reads every active part as-is, duplicates and all, unless the query uses FINAL or the rows have already merged. The dashboard query did neither. It had been running the same unguarded sum() for months and it had always been fine, because merges normally kept the duplicate count low enough that nobody noticed a few extra cents here and there. 2,180 active parts was not normal merge backlog. Something had just fed the table far more row versions per order than usual, faster than the merge scheduler could work through.

Tracing ord_9f21c8's six rows back through the ingestion logs showed all six originated from the same payment intent, each one a separate webhook delivery from the payment provider, 40 to 90 seconds apart. Our webhook handler had no idea they were retries, it just saw six "payment captured" calls and faithfully published six Kafka events, each correctly stamped with a newer updated_at. At the business layer, idempotency was fine, the order's final status was correct either way. At the row layer, it meant six physical rows instead of one, and ClickHouse doesn't dedupe anything until it gets around to merging.

the find

The payment provider's own status page confirmed a 23-minute API degradation starting at 07:48 UTC. During that window their webhook sender retried on every slow or ambiguous acknowledgment, pushing retry volume to roughly 4.1x normal for the accounts affected. Our webhook endpoint kept responding fast, so from our side nothing looked wrong, we had no retry behavior to notice. We just received more webhooks than usual and emitted more events than usual, each one individually correct. The revenue dashboard's query had been implicitly depending on an assumption nobody had written down: that duplicate versions of the same order would always merge away before anyone summed the table. That assumption held for months. A spike in upstream retries was all it took to break it.

the fix

Waiting for merges to catch up, or forcing them with OPTIMIZE TABLE ... FINAL, fixes the symptom for one table at one moment and says nothing about the next retry storm. The dashboard query needed to stop trusting merge timing at all. argMax picks the value associated with the highest version per key, inside the query, regardless of how many unmerged rows currently exist:

-- before: sums every active row, duplicates included
SELECT
  toDate(created_at) AS day,
  sum(total_cents) / 100.0 AS revenue
FROM orders_rt
WHERE created_at >= today() - 1
GROUP BY day;

-- after: collapse to the latest version per order_id first, then sum
SELECT
  day,
  sum(total_cents) / 100.0 AS revenue
FROM (
  SELECT
    order_id,
    toDate(argMax(created_at, updated_at)) AS day,
    argMax(total_cents, updated_at) AS total_cents
  FROM orders_rt
  WHERE created_at >= today() - 1
  GROUP BY order_id
)
GROUP BY day;

The rewritten query added roughly 140ms of latency on a normal day, a reasonable trade for a number finance actually trusts regardless of what the merge backlog looks like at query time. We also added a check on system.parts active part count per table, alerting when it crosses 5x its 7-day median, since that's the earliest signal that something upstream is producing far more row versions than usual, well before any dashboard goes visibly wrong.

the aftermath

Actual revenue for the window was $812,300. Nobody got double-charged and no downstream billing system touched the bad numbers, the corruption lived entirely in one read path over one table. Finance paused that morning's reporting for under four hours while the query fix shipped and the backlog of unmerged parts cleared on its own by early afternoon.

Nobody touched the orders pipeline that morning. The only thing that changed was how fast someone else's retries arrived, and that was enough to turn an assumption nobody had written down into a number finance stopped trusting for half a day.