Learning Objectives
- Decompose a messy, multi-workload system description into its independent pieces and route each piece to the correct one of this module's five frameworks — OLTP/search mismatch, warehouse fit, DuckDB fit, real-time-OLAP-siblings fit, and scale-up-vs-shard
- Recognize when a single real system legitimately needs several different data systems for different pieces, and defend that as deliberate architecture rather than indecision
- Build and defend a complete, multi-part architecture recommendation for a stated system, citing the specific named trade-off driving each piece's recommendation rather than a single company-wide tool preference
- Diagnose two composite failure modes this closing lesson targets — applying a familiar framework to a piece it doesn't fit, and picking one default tool for an entire system instead of decomposing it first
Why This Matters
Every lesson in this module handed you one clean comparison at a time: ClickHouse against warehouses, against DuckDB, against Druid and Pinot, against OLTP and search, against itself at a different scale. Real architecture reviews never arrive that clean. A real system is five workloads wearing one company name, and the failure mode this closing lesson exists to catch isn't "picked the wrong tool for a clear question" — every prior lesson in this module already drilled that. It's subtler: applying the one framework you remember best to a piece of the system it was never built for, or reaching for one company-wide default and calling that a decision, because decomposing a messy system into its separate pieces is real work and skipping it feels efficient right up until each piece starts failing for a different, entirely predictable reason. This lesson is one scenario, worked end to end, that needs all five frameworks at once — because that's what a real system actually looks like.
The trade-off
The trade here isn't between any two systems — every system this module covered has already been steelmanned on its own terms. It's between the discipline of decomposing a real, messy system into its independent pieces before recommending anything, versus the much faster shortcut of reading the whole system as one workload and reaching for whichever tool comes to mind first. Decomposition costs real time up front: naming each piece's actual access pattern, latency need, and audience, separately, before touching a recommendation. Skipping it costs more, later, once a piece whose real shape was OLTP or DuckDB-sized or genuinely sharding-worthy has been quietly forced into whatever the rest of the system already uses.
The module, recapped as one checklist
Five lessons, five questions, applied in this order to each piece of a system — not once to the system as a whole:
- Is this piece's access pattern OLTP-shaped (point lookups, single-row mutations, ACID transactions) or relevance-ranked search?
when-clickhouse-is-wrongestablished that neither is a tuning problem — ClickHouse's own documentation says plainly it "is not designed for" sub-millisecond point lookups and transaction semantics, and its GA text index is explicit that it isn't "a relevance engine." A piece that fails this check is routed to a row-oriented OLTP database or a dedicated search engine, full stop, before any of the next four questions are even asked. - If the piece is otherwise analytical: who's running the queries, and how latency-tolerant are they?
clickhouse-vs-warehousesestablished that internal analysts running ad hoc SQL who can wait several seconds are a fundamentally different workload from continuous, sub-second, product-facing serving — the vocabulary distinction Module 1 first drew, made into a real cost-and-fit decision. - Does this piece's data fit comfortably on one machine, for one person or a small team, without sustained concurrent access?
clickhouse-vs-duckdbestablished that data volume alone is the least informative signal — concurrency and continuous ingestion matter more, but a genuinely single-machine, exploratory, or embedded workload belongs there regardless of general reputation. - Does this piece need to serve a fixed, known query set to a massive, concurrent, often public audience — or does it need same-instant row-level correction?
clickhouse-vs-realtime-olap-siblingsestablished that ClickHouse, Druid, and Pinot share the "real-time OLAP" label while disagreeing sharply on join model, deployment topology, and mutability — and that it's normal for one pipeline to feed more than one of these systems for genuinely different audiences. - If this piece is already running on ClickHouse and struggling: which specific resource is actually exhausted?
sharding-vs-scaling-upestablished sharding as the last rung of an escalation ladder — vertical scaling first, then read replicas for concurrency alone, then sharding only once data volume or ingestion throughput has genuinely outgrown a single node, because a poorly chosen sharding key is close to a one-way door.
None of these five questions is new in this lesson. What's new is applying all five, separately, to one system with several pieces — which is the actual shape of the job.
Cold Start — No AI
You're a senior engineer doing an architecture review for Meridian Health, a telehealth scheduling platform. The review covers five requests that landed on your desk this quarter. Product, data science, and platform engineering each described their piece independently, in the language of their own team — none of them framed their request as "which OLAP database should we use," because none of them are thinking about it that way. That's deliberate: a real request rarely arrives pre-labeled with the framework that answers it.
A. Booking and support. Patients book appointments through the app — checking a clinician's availability, reserving a slot, cancelling or rescheduling — thousands of times a minute at peak, each operation needing to see the current state of that clinician's calendar to avoid double-booking two patients into the same slot. Separately, support agents need to search a patient's visit history using natural-language queries like "billing complaint about a cancelled telehealth visit last month" and get back the most relevant matching support tickets, not just ones containing those exact words.
B. Claims and finance reporting. The finance team reconciles insurance claims and reimbursements monthly, running ad hoc, exploratory SQL — joins across claims, payers, and providers that nobody can fully predict in advance — over two years of historical data. Nobody has ever complained about a report taking 10–20 seconds to run; what they do care about is governance and audit trails, since this data is subject to healthcare compliance review.
C. A one-time research question. A data scientist has a single afternoon to explore a 12GB CSV export of appointment no-show history, looking for patterns to justify (or not) a proposed reminder-SMS feature. Nothing about this is expected to be a recurring, production workload — it's one person, one file, one deadline.
D. The patient care timeline. Every patient, on logging into the app, sees a personalized timeline: upcoming appointments, recent lab results, prescription refill status — a fixed set of about a dozen queries, filtered by that patient's own ID. At full rollout, this will be hit by the platform's entire patient base, with genuine concurrency during weekday mornings. When a new lab result posts, it needs to appear on the patient's timeline within seconds, not after an overnight batch job.
E. The existing event log. Meridian already runs a single-node ClickHouse instance logging every appointment-state-change event (booked, confirmed, cancelled, completed) for internal audit and analytics. It's grown to 30TB on a large single node with plenty of headroom left on disk, but query latency has degraded noticeably during a specific two-hour window each weekday morning, which a platform engineer suspects is peak concurrent dashboard usage from the clinical operations team, not data volume.
Before reading further or asking an AI assistant anything: for each of the five pieces (A through E), name the specific detail in its description that determines the recommendation — not a general impression of the piece, the actual sentence or clause doing the deciding work — and state which system or approach you'd recommend for it. Note explicitly anywhere two pieces might tempt you toward the same system, and whether that's actually justified or just convenient. Write your reasoning down for all five before moving on.
Reflection: Where were you tempted to ask an AI "what's the best database architecture for a telehealth platform" as one question covering all five pieces? That framing invites a single, generic answer to a scenario that's actually five separable decisions wearing one company name. Would a quick AI answer have decomposed piece A's two sub-parts (booking vs. search) into different needs, caught that piece E's symptom (latency during a specific window, plenty of disk headroom) points away from sharding, or would it have handed you one recommended stack and left the actual decomposition — the skill this whole module has been building — undone? Check your own five answers against this lesson's checklist above and the case studies and quiz below — deliberately, this lesson doesn't hand you a reveal for Meridian the way <ReasoningPrompt> scenarios get a <ModelAnswer>; working out whether your own reasoning holds up is itself part of the practice.
Reasoning Prompt
A second team at Meridian is proposing a new internal tool: a live operational dashboard for the platform engineering team, showing real-time infrastructure health (API error rates, queue depths, database replication lag) across all of Meridian's services, refreshed continuously, viewed by roughly 15 on-call engineers at a time. Separately, that same team wants to retain 18 months of this telemetry for quarterly post-incident reviews, where an engineer might run an unplanned, exploratory join between an incident's error-rate spike and a specific deploy's changelog — a query shape nobody can fully predict in advance. Reason through whether these are the same workload or two different ones, and what you'd recommend for each, applying this module's checklist. State what assumption would change your answer.
Model Answer⌄
These read as one team and one dataset, but they're two workloads by the checklist's own questions, and treating them as one is the "one default for the whole piece" failure this lesson names directly.
- The live dashboard is real-time-OLAP-shaped, not warehouse-shaped. Continuously refreshed telemetry, viewed by a small, fixed internal audience — 15 on-call engineers is far smaller than
clickhouse-vs-realtime-olap-siblings's massive-fan-out examples, but the latency requirement (genuinely live, not "5-to-20-seconds-is-fine") is what routes it here, not audience size alone;clickhouse-vs-warehouses's decision framework names latency, not headcount, as the more determinative question. ClickHouse fits this cleanly: a small, fixed set of monitoring queries, continuous ingestion from the services being monitored, no massive-fan-out requirement that would push specifically toward Druid or Pinot's process-separated concurrency isolation. - The 18-month retrospective analysis is warehouse-shaped, not real-time-OLAP-shaped. Unplanned, exploratory joins against a large historical dataset, run by a handful of engineers a few times a quarter, with zero latency pressure — this is
clickhouse-vs-warehouses's canonical shape almost exactly, even though the data originated from the same telemetry pipeline as the live dashboard. - Why not force both onto one system: ClickHouse's join model would technically serve the retrospective analysis too — full SQL joins are ordinary in ClickHouse, per
clickhouse-vs-realtime-olap-siblings— so a defensible alternative answer keeps everything on one ClickHouse deployment rather than introducing a warehouse for infrequent, small-scale exploratory queries. This is where the assumption matters: if the 18-month retrospective queries are rare enough and small enough in absolute compute cost, running them against the same ClickHouse cluster that serves the live dashboard, accepting some analyst friction, may cost less in total operational surface than adding a second system for a once-a-quarter workload. What would tip the recommendation back toward a second, warehouse-shaped system: the retrospective queries growing frequent or heavy enough to compete with the live dashboard's own resource needs, or a governance/audit requirement (as in piece B of the main scenario) that ClickHouse doesn't provide as maturely as an established warehouse.
The shape of the reasoning matters more than the specific pick: the same underlying data feeding two workloads with genuinely different latency and query-predictability profiles is exactly the situation this module's checklist is built to pull apart — even when, unlike the main scenario, the two pieces turn out close enough that "one system, accepted trade-offs" is itself a defensible answer, provided that conclusion is reached by checking the questions, not skipped past them.
The whole module, as case studies
Every real case study this module has already cited is, in hindsight, one company applying exactly this decomposition, once, to a real system — worth seeing side by side rather than in isolation:
LinkedIn built Pinot from scratch for one specific piece: member-facing features like "Who Viewed My Profile" — massive, concurrent, product-facing serving, the shape clickhouse-vs-realtime-olap-siblings names as Pinot's founding design center, not a general-purpose analytics choice applied company-wide.
PostHog runs ClickHouse and DuckDB simultaneously, on purpose, for different jobs — ClickHouse for its sustained, high-volume, always-on core analytics product, DuckDB for an ad hoc, customer-facing data-warehouse exploration feature — the clickhouse-vs-duckdb case study's central point: "use both" is a deliberate architecture decision, not an unresolved one.
Cloudflare reached 36 nodes of sharded, replicated ClickHouse only once ingestion genuinely outgrew any single node — 6 million HTTP requests per second, a scale no realistic single machine was ever going to absorb, exactly sharding-vs-scaling-up's rung-3 trigger condition, not a default topology chosen up front.
ClickHouse's own engineering team publicly draws the line at what its architecture doesn't do — OLTP point lookups, relevance-ranked search — the when-clickhouse-is-wrong case study's point that a vendor naming its own product's limits, unprompted, is unusually trustworthy evidence for exactly those limits.
Four different companies, four different pieces of their systems, and in every case the recommendation traces back to one specific, nameable property of that piece's workload — never to "this is the tool our team already knows" as the whole justification.
Anti-pattern gallery
Applying the framework you remember best to a piece it doesn't fit. An engineer who just finished clickhouse-vs-realtime-olap-siblings and reflexively evaluates Meridian's booking system (piece A) by asking "ClickHouse, Druid, or Pinot?" has skipped the checklist's first question entirely — none of the three fit an OLTP workload, and the right framework for that piece is when-clickhouse-is-wrong's, not the most recently learned one.
Picking one company-wide default and calling it an architecture. "We're a ClickHouse shop" is a real, legitimate starting bias — existing operational investment is a fair input, as when-clickhouse-is-wrong's decision framework says directly — but it stops being legitimate the moment it's used to skip decomposing a new request into its own pieces, the same failure sharding-vs-scaling-up named as reaching for the most-discussed scaling topic instead of naming the actually-exhausted resource.
Mistaking "these two pieces both ended up on the same system" for "one system was the answer all along." Pieces D and E in this lesson's scenario legitimately land on the same technology family — but for two independently-reasoned reasons, not because checking was skipped. The anti-pattern is the reverse order: picking the system first, then constructing a reason.
Summary
This module taught five separate frameworks — OLTP/search mismatch, warehouse fit, DuckDB fit, real-time-OLAP-siblings fit, and scale-up-vs-shard — and this closing lesson exists because a real system is rarely one workload clean enough for a single framework. Meridian Health's five-part scenario needed all five: booking (OLTP) and support search (relevance-ranked search) routed away from ClickHouse entirely by when-clickhouse-is-wrong's first check; finance reporting routed to a warehouse by its latency tolerance and governance needs; a one-off research question routed to DuckDB by its single-machine, single-afternoon shape; the patient timeline routed to real-time OLAP by its fixed query set and massive concurrent audience; and the existing event log's degraded latency routed to more replicas, not sharding, once the actually-exhausted resource (concurrency, not data volume) was named. The skill this lesson closes on isn't remembering all five frameworks — it's decomposing a messy request into pieces small enough that each one clearly belongs to exactly one of them, resisting both the reflex to apply a familiar framework to the wrong piece and the reflex to pick one company-wide default and call decomposition unnecessary.
Quiz
1. A platform's booking system needs sub-millisecond lookups and guaranteed single-slot consistency, while its finance team runs unplanned exploratory joins over two years of history, tolerant of 15-second latency. Applying this lesson's checklist, what's the correct first move?
2. In the Meridian scenario, why are the patient care timeline (piece D) and the existing event log (piece E) both plausibly recommended toward the ClickHouse family, while this lesson insists that isn't a shortcut?
3. The existing event log (piece E) has plenty of disk headroom and flat ingestion, but query latency degrades during a specific two-hour window each weekday morning. A teammate proposes sharding to fix it. What does this lesson's checklist say about that proposal?
4. The data scientist's one-time exploration of a 12GB CSV export (piece C) and the patient care timeline (piece D) both involve ClickHouse-family or ClickHouse-adjacent tools being considered. Why does this lesson route C to DuckDB rather than ClickHouse, despite D being routed toward ClickHouse or a sibling?
5. This lesson's ReasoningPrompt describes a live infrastructure dashboard and a quarterly retrospective analysis, both fed by the same telemetry data. Why does the model answer treat these as two potentially different workloads rather than automatically recommending one system for both?