Decision Framework: Which Engine, and Why

Learning Objectives

Decompose a real, multi-part workload into its independent requirements — full-row replacement, pre-aggregation, retraction, and durability — rather than pattern-matching a single engine to a vague description
Apply the Replicated* prefix as an axis orthogonal to every other engine choice, and recognize when a real pipeline needs more than one engine working together rather than one engine chosen for the whole system
Distinguish "netting many independent delta events into a running total" (SummingMergeTree's shape) from "retracting one specific row's full prior state" (the Collapsing family's shape) — two patterns that can look alike on the surface but carry different ordering requirements
Build and defend a full, composed engine recommendation for a multi-requirement workload, and diagnose "engine-shopping" — picking an engine because its name matches a keyword in the requirements rather than because its mechanics match the workload's real invariant

Why This Matters

By now you know all five shapes: ReplacingMergeTree for full-row replacement, SummingMergeTree/AggregatingMergeTree for pre-aggregation, CollapsingMergeTree/VersionedCollapsingMergeTree for retraction, and Replicated* for durability. The failure mode at this point in the module isn't "doesn't know CollapsingMergeTree exists" anymore — it's an engineer facing a real workload that needs several of these at once (a distinct-count metric, that must survive a node dying, fed by writers that don't agree on order) and treating "which MergeTree engine" as one categorical pick instead of several separable questions. That produces two opposite failures with the same root cause: landing on a single engine that fits one requirement and quietly ignores the others, or stacking every feature "to be safe" — a fully replicated, fully versioned, fully aggregating table for a workload that needed a fraction of that machinery. This lesson is the synthesis: how the axes from the last four lessons combine, in what order to interrogate them, and how to catch yourself matching an engine's name to a keyword in the requirements instead of matching its mechanics to the workload's real invariant.

The trade-off

Every lesson in this module taught one axis in isolation, which is the right way to learn each one but the wrong way to use them: real workloads don't arrive pre-sorted into "this is a ReplacingMergeTree problem" or "this is a CollapsingMergeTree problem." They arrive as a paragraph of requirements that touches several axes at once, and the trade this lesson is about is time spent decomposing that paragraph into its independent, separately-answerable questions, versus the much faster shortcut of pattern-matching the first engine whose name echoes a word in the requirements — "it has counters going up and down, so CollapsingMergeTree" — a shortcut that's fast and often accidentally right, and exactly wrong whenever the workload's real shape doesn't match its surface description. The cost of decomposing properly is a few extra minutes of asking questions this lesson gives you in order. The cost of skipping it is committing to an engine — and the pipeline built around it — that turns out to solve the wrong problem, discovered only once production data exposes the mismatch.

The axes, revisited

Five shapes, each with the workload description that makes it sound like the obvious first choice, and the deeper check that either confirms or corrects that first impression.

"A row's complete state changes over time, and I always know the whole new version when it changes." That's ReplacingMergeTree's shape — a single writer, or a source system with its own well-defined notion of recency, restating the full row on every change. The deeper check: does every update genuinely carry the complete row, or only a partial change? A writer that only knows "this one field changed" doesn't have a full row to insert, and ReplacingMergeTree needs one.

"I need a running total, and it only ever grows or only ever nets from independent events." That's SummingMergeTree (pure sums) or AggregatingMergeTree/SimpleAggregateFunction (anything beyond a sum — distinct counts, averages, quantiles) territory. The deeper check, and the one engineers skip most often: does "nets from independent events" really mean summing numeric deltas, or does it actually mean retracting one specific row's full prior contribution? Those are different problems that sound similar — the next section exists specifically to pull them apart, because getting this one wrong is the most common miscalibration in this whole module.

"A specific record's contribution needs to be corrected or withdrawn, not just added to." That's the Collapsing family's shape — a matched Sign = 1/Sign = -1 pair replacing one entity's prior state. The deeper check: can every writer that might cancel a row guarantee it inserts the cancel after the state row it targets, for that same key? If not, VersionedCollapsingMergeTree's explicit Version column removes that requirement; if the workload doesn't actually need row-level retraction at all (see above), a SummingMergeTree over signed delta rows may be simpler than either.

"None of the above — this table just needs to answer queries correctly, fast enough, today." That's the case for plain MergeTree with query-time aggregation, and it's the default this whole module has been implicitly arguing for: every specialized engine in this family earns its complexity only against a specific, present need, not a hypothetical future one. The summing-and-aggregating-mergetree lesson's "bad fit" case study — a team building AggregatingMergeTree machinery for a query that was already returning in well under a second — is the general shape of what happens when this axis gets skipped.

"This table can't be allowed to go down or lose data if one node fails." That's Replicated* — and it's not a sixth alternative to the four above, it's a separate axis entirely. ClickHouse ships a Replicated* counterpart for every engine this module has covered — ReplicatedMergeTree, ReplicatedReplacingMergeTree, ReplicatedSummingMergeTree, ReplicatedAggregatingMergeTree, ReplicatedCollapsingMergeTree, and ReplicatedVersionedCollapsingMergeTree are all named explicitly in ClickHouse's own documentation. Answer the durability question independently of the other four — it changes nothing about which base engine fits the workload, only whether that engine's name gets a Replicated prefix.

Netting deltas vs. retracting a row: the distinction that trips engineers up

This is worth its own section because it's the single most common miscalibration once a learner already knows all five engines individually: mistaking a "net a running total from many independent events" problem for a "retract one row's prior state" problem, or vice versa. They can describe the same-sounding workload — "a counter that goes up and down" — and still need different engines.

Take a live count of open support tickets per agent, fed by independent create, reassign, and close events from three uncoordinated services — the exact scenario the collapsing-mergetree lesson's Cold Start posed. If what the dashboard needs is only the net number — how many tickets is each agent holding right now — every event can be written as a signed delta row (+1 on assignment, -1 on close or reassignment-away), and SummingMergeTree sums those deltas per agent on merge. Summation is commutative: it doesn't matter what order the +1 and -1 rows arrive in, which sidesteps the Collapsing family's ordering requirement entirely, for uncoordinated writers, for free. The summing-and-aggregating-mergetree lesson's own caveat still applies — sum()/GROUP BY are needed at query time to account for not-yet-merged rows — but that's a much smaller ask than guaranteeing insertion order across three independent services.

Reach for CollapsingMergeTree here instead, and the workload's actual shape doesn't need what that engine actually offers: exact-row cancellation, which exists to retract a specific previously-inserted row, including every other column on it, not just to net a number. If the dashboard also needs to know each ticket's currently assigned agent, priority, or status as of right now — not just a count — that is a full-row-replacement problem, and the right shape is closer to ReplacingMergeTree on a per-ticket table (or genuine Collapsing-style retraction if partial, non-full-state corrections are involved), kept separate from a SummingMergeTree rollup answering the aggregate count. The diagnostic question that actually separates these two patterns: does anything besides a single number need to be "the current value" — a full row, or a specific field's current state — or is a net numeric total the entire requirement? A net total → sum deltas. Anything more than a number needs to persist as current state → replacement or retraction, not summation.

Decision framework

The order a senior engineer actually asks these, for one table's worth of requirements. Numbering it is a convenience, not a claim that every question depends on the one before it — the durability question in particular is listed first only because it's easiest to get out of the way early, not because it has to be answered before the rest:

Does this table need to survive a single node failing, without downtime or loss? Answer this independently of the questions below — it's orthogonal, and could just as easily be asked last. Note the answer (Replicated or not) and set it aside; it attaches to whatever engine steps 2–5 land on, unchanged.
Is plain MergeTree with query-time aggregation actually too slow or too duplicate-prone, measured, not assumed? If there's no concrete performance or correctness problem yet, stop here — every specialized engine below is complexity you pay for a problem you don't have.
Does a single writer (or a source system with its own recency ordering) always know a row's complete current state when it changes? ReplacingMergeTree, with a version column tied to that source's own notion of "current," not insertion time.
Is the remaining need a running numeric total netted from independent delta events, with no other column needing to reflect "current state"? SummingMergeTree for pure sums; AggregatingMergeTree (or SimpleAggregateFunction for min/max/sum-shaped functions) for anything needing real aggregate state, like a distinct count.
Is the remaining need instead retracting one specific row's full prior contribution, where more than a number has to reflect the corrected state? CollapsingMergeTree if every writer can guarantee per-key insertion order; VersionedCollapsingMergeTree if it can't.
Does this workload actually need more than one of the above, for different questions asked of the same source data? That's not a failure of the framework — it's the common case at real scale, and the next section shows it in production. A raw MergeTree table commonly feeds two or three materialized views into two or three differently-engined rollup tables, not one engine chosen for the whole pipeline.

Cold Start — No AI

You're designing the data layer for a food-delivery platform's live operations dashboard, watched continuously by the whole company during peak hours. It needs three things: (1) each driver's current status — available, on a delivery, or offline — updated by the driver's own app, which always sends its full current status; (2) a live count of active deliveries per city, where "delivery started" and "delivery completed" events arrive independently from a dispatch service and a completion service that don't coordinate with each other on ordering; (3) the whole system must keep serving this dashboard without interruption if a single database node fails, because leadership watches it during exactly the peak-traffic windows when a node is most likely to be under strain.

Before reading further or asking an AI assistant anything: sketch the table(s) and engine(s) you'd use for (1) and (2), whether they should be the same table or different ones, and how requirement (3) attaches to whatever you land on. Write down your reasoning for each piece before moving on.

Reflection: Where were you tempted to ask an AI "what ClickHouse engine should I use for a live operations dashboard" as a single question? A prompt shaped like that invites a single-engine answer to what's actually three separable questions — full-row state (1), delta-netting (2), and durability (3) applied to both. Would a quick AI answer have caught that (1) and (2) are different problems needing different tables, or would it have reached for one engine that handles neither particularly well?

Where were you tempted to reach for AI just now? What made you resist, or give in?

Reasoning Prompt

A warehouse-management team is designing a table for SKU stock levels. Requirements: (a) stock changes arrive as independent scan events from barcode scanners at multiple warehouse docks — "5 units received," "3 units shipped" — with no single scanner ever knowing the SKU's total current stock, and no coordination on insert order between docks; (b) a separate report needs the count of distinct warehouses that have ever stocked each SKU, updated as new warehouses come online; (c) both feed pricing decisions made automatically within seconds of a stock change, so the system can't tolerate extended unavailability from a single node failing. Reason through what table(s) and engine(s) you'd recommend, and defend the composition against a simpler alternative someone might propose instead.

Write your reasoning before revealing the model answer.

Model Answer⌄

A reasonable design: two engines working together, both replicated, not one engine covering everything.

Stock level (requirement a): this is a net-numeric-total problem, not a full-row-replacement or retraction problem — no scanner ever knows or needs to state the SKU's complete current stock, only its own delta, and there's no requirement that any other column reflect a single "current state" beyond the number itself. That's SummingMergeTree's shape exactly: +5/-3-style signed delta rows, summed per SKU on merge, with no ordering requirement between docks because summation is commutative. A learner tempted toward CollapsingMergeTree here should ask what, specifically, is being retracted — nothing is; each scan is new information to add, not a correction to a specific prior row.
Distinct warehouse count (requirement b): a plain sum can't express this — "distinct count of warehouses" is a genuinely stateful aggregate, not addable numbers. This needs AggregatingMergeTree with a uniqState/uniqMerge column (or a SimpleAggregateFunction if the exact function used turns out to be one of the state-free ones, which uniq is not). Trying to force this into the same SummingMergeTree table as the stock-level rollup would mean picking one engine that can express sum() correctly and uniq() not at all — exactly the limitation Cloudflare's own pipeline hit and split into two tables for, cited below.
Why two tables instead of one "do everything" engine: no single engine among the five covered in this module both sums arbitrary deltas and tracks distinct counts with the same mechanism — SummingMergeTree can't do the second, and forcing everything into AggregatingMergeTree-with-sumState would pay the -State/-Merge tax on the simple sum column for no benefit, per the summing-and-aggregating-mergetree lesson's own guidance on SimpleAggregateFunction as the proportionate middle ground.
Durability (requirement c): applied last, and identically, to both tables — ReplicatedSummingMergeTree for the stock rollup, ReplicatedAggregatingMergeTree for the distinct-warehouse rollup. This is the orthogonality point from the decision framework: the durability requirement doesn't change which base engine fits each table, it just prepends Replicated to whichever one already does.

The shape of the reasoning matters more than this specific pick: name which requirement is a sum, which is a genuinely stateful aggregate, and which is a cross-cutting durability need — and resist collapsing three different questions into "which one engine."

Case studies

A good fit: Cloudflare's pipeline, as the composability case study. The same production pipeline discussed across this module didn't pick one MergeTree engine for its entire analytics stack — it used SummingMergeTree for sum-shaped metrics specifically because it "allowed us to significantly reduce the number of tables required," and a separate materialized view using ReplicatedAggregatingMergeTree specifically for unique-visitor counts, because "SummingMergeTree... will not perform aggregation on it for records with same primary keys" for that kind of metric. That ReplicatedAggregatingMergeTree name, used directly in a real production pipeline, is itself the concrete evidence for this lesson's composability point: durability and pre-aggregation aren't competing choices, they're two axes answered together in a single engine name.

A bad fit: engine-shopping by keyword. A team building the ticket-counter dashboard from this lesson's central example reaches for CollapsingMergeTree because a requirements doc says "a counter that goes up and down" and that phrase reads as a match for "collapsing." They then have to solve a problem they didn't need to have: coordinating insertion order for cancel/state pairs across three independent services that were never going to naturally provide it, eventually reaching for VersionedCollapsingMergeTree and a Version column to route around the very ordering problem a SummingMergeTree-over-signed-deltas design would never have created. The fix wasn't a better collapsing implementation — it was noticing, per the distinction this lesson draws out, that nothing was actually being retracted; every event was new information to sum.

Anti-pattern gallery

Picking an engine because its name echoes a word in the requirements, not because its mechanics match the workload. This is the general form of the "bad fit" case study above, and it's the specific failure mode this lesson exists to name: "counter" doesn't automatically mean Collapsing, "current state" doesn't automatically mean Replacing, and "aggregation" doesn't automatically mean AggregatingMergeTree over the simpler SimpleAggregateFunction or plain SummingMergeTree. Every one of those words describes multiple engines' actual behavior; only tracing the workload's real invariant — through the decision framework's questions, not its vocabulary — disambiguates.

Treating durability as competing with the other four axes instead of composing with them. A team that debates "should we use ReplicatedMergeTree or AggregatingMergeTree" as though they're mutually exclusive options has missed that the real ClickHouse engine name for "durable and pre-aggregating" is ReplicatedAggregatingMergeTree — both, not either.

Building one engine to cover requirements that actually need two tables. Forcing a distinct-count requirement into a SummingMergeTree table (which can't express it) or forcing a simple sum into unnecessary AggregateFunction ceremony (which SimpleAggregateFunction or plain SummingMergeTree would handle for free) both come from treating "one table, one engine" as a constraint the workload has to fit, rather than letting the workload's distinct requirements determine how many tables and engines it actually needs.

Summary

Every engine in this module answers one of four independent questions — does a writer always know a row's complete state (ReplacingMergeTree), does the need reduce to netting a numeric total from independent deltas (SummingMergeTree, or AggregatingMergeTree/SimpleAggregateFunction for non-sum aggregates), does a specific row's prior contribution need retracting rather than netting (CollapsingMergeTree, or VersionedCollapsingMergeTree when insertion order can't be guaranteed) — plus a fifth, orthogonal question about durability, answered by prepending Replicated to whichever of the first four fits. The distinction most often missed once all five are individually known is netting versus retracting: a "counter that goes up and down" can be a commutative sum of signed deltas (SummingMergeTree, no ordering requirement) or a genuine row-level retraction (the Collapsing family, which does require ordering, unless versioned) — and confusing the two means either building unneeded ordering machinery or losing the ability to correct a row's full state. Real pipelines routinely need more than one of these engines together, feeding different rollup tables from the same raw source, exactly as Cloudflare's own production pipeline does — "one engine for the whole system" is itself a symptom of skipping the decomposition this lesson teaches, not a simpler answer.

Quiz

1. A table needs both pre-aggregation (a distinct-count metric) and the ability to survive a single node failing without downtime. What's the correct way to think about combining these two requirements?
2. A live counter tracks 'items currently in a shopping cart,' fed by independent add-to-cart and remove-from-cart events from a web app and a mobile app, with no coordination between the two on insertion order. Nothing besides the count itself needs to reflect a 'current' value. What does this lesson's netting-vs-retracting distinction suggest?
3. A team picks CollapsingMergeTree for a workload because the requirements doc describes 'a counter that goes up and down,' without checking whether anything besides the count needs to reflect a specific row's current state. What's the risk this lesson highlights?
4. A workload needs both a simple sum(revenue) rollup and a uniqExact(user_id) distinct-count rollup from the same raw events table. According to this lesson, what's the most proportionate design?
5. This lesson's decision framework lists the durability question ('does this table need to survive a node failing?') as step 1, but says it could just as easily be asked last. Why is its position in the list not meaningful the way the other steps' order is?