Part 1 · A single truthful copy

Query Languages and Access-Pattern Design

Lesson 01 gave us a shape for the data — relational, document, or graph. But a shape on disk is inert until something asks it a question. This lesson is about the asking: a declarative language lets you state what you want and hands the engine freedom to decide how; an imperative one makes you spell out the steps. The twist for systems at scale is that the asking-style is not a syntax preference — it decides who owns performance, and at the far end it inverts the whole problem: instead of querying the data you happened to store, you store the data already shaped for the query you must serve.

Source grounding

Original synthesis inspired by Martin Kleppmann, Designing Data-Intensive Applications, Chapter 2 (query languages: declarative SQL vs imperative traversal, the MapReduce middle ground, and schema-driven access on document/key-value stores). Worked numbers and the ML-serving framing are ours.

Linear position

Prerequisite: Lesson 01 (data models) — you can pick relational, document, or graph for a given set of relationships, and you know joins are work.
New capability: You can decide who chooses the execution plan — your code, the database's optimizer, or the schema itself — and design a storage layout backwards from the queries it must answer, trading query-time flexibility for predictable serving latency.

The plan

Four moves. (1) Make the declarative/imperative split concrete: the same query written both ways, and exactly which freedoms the planner exercises that the hand-coded loop forfeits. (2) Watch a planner pick a plan — index lookup vs full scan, and a reordered join — with a worked cost so you feel why its freedom is worth having. (3) Place MapReduce as the middle ground between the two, foreshadowing lesson 18. (4) The track's central principle: at scale the access pattern is part of the contract — design the layout around the queries, contrast "query the data you stored" with "store the data shaped for the query," and tie it to the ML split between low-latency feature lookups and analytical training-set scans.

1 · Two ways to ask: declarative vs imperative

The split DDIA points to is the one every web developer already knows from the browser. A CSS selector — li.selected > p — declares which elements you mean and lets the browser find them however it likes; if the browser ships a faster matcher next year, your stylesheet gets faster untouched. Walking the DOM by hand in JavaScript — loop over children, check each class, recurse — pins down how, and freezes that procedure into your code. Database query languages have the same fork.

A declarative query states the result you want and leaves the procedure unspecified. SQL is the canonical example: you name the rows, the conditions, and the shape of the output, but never the loop. An imperative query, by contrast, is ordinary application code — you write the loop, the lookups, and the branches yourself, step by step. Take one question — "the 50 most recent orders for user 42, newest first" — and write it both ways.

Declaratively, in SQL:

SELECT order_id, placed_at, total
FROM   orders
WHERE  user_id = 42
ORDER  BY placed_at DESC
LIMIT  50;

Notice what this does not say. It does not say whether to read every row in the table and discard the ones that fail user_id = 42, or to jump straight to user 42's rows through an index. It does not say whether to sort all matching rows or to read them already sorted. It does not say where to stop. Every one of those is a decision the engine makes. The same question written imperatively pins all of them down:

results = []
for row in orders_table:              # walk every row on disk
    if row.user_id == 42:
        results.append(row)
results.sort(key=lambda r: r.placed_at, reverse=True)   # sort all matches
return results[:50]                   # then keep 50

The imperative version commits to a full scan — reading every row in the table from start to finish — then an in-memory sort, then a truncation. That is a fixed procedure. If the table grows from ten thousand rows to ten billion, the loop still reads all ten billion, even though only 50 survive. The code is the plan, frozen at the moment you wrote it.

The vocabulary, defined once

Declarative — you describe the result; the system picks the procedure. Imperative — you describe the procedure step by step. Query planner (a.k.a. optimizer) — the database component that, given a declarative query plus what it knows about the data, chooses a concrete execution plan. Index — an auxiliary, sorted/lookup structure (often a B-tree or hash, lesson 03) that lets the engine find matching rows without reading them all. Full scan — reading every row in a table because no better access path exists or is chosen.

The declarative version's silence is the whole point. Because it never named the procedure, the engine is free to choose a better one — and to change its choice later as the data, the indexes, or the engine itself change, without you editing a line.

2 · Why the planner's freedom is worth having

Suppose orders holds 100,000,000 rows and user 42 has 200. Watch the planner weigh two execution plans for that same SQL:

QUERY: orders for user 42, newest 50 planner sees: WHERE user_id = 42, ORDER BY placed_at DESC, LIMIT 50 catalog says: table = 100,000,000 rows; index on (user_id, placed_at) exists? +-- PLAN A: full scan + sort | read 100,000,000 rows ── filter to ~200 ── sort 200 ── take 50 | cost ≈ 100,000,000 row reads | +-- PLAN B: index range scan [chosen if index exists] seek index to (42, +∞) ── walk back 50 entries ── fetch 50 rows cost ≈ 50 index steps + 50 row reads planner picks B: ~100 I/Os vs ~100,000,000. Same SQL. No app change.

The index on (user_id, placed_at) stores user 42's orders already grouped and already sorted by time, so Plan B seeks to the newest and walks back exactly 50 — about 100 reads instead of 100,000,000, a factor of a million. The decisive fact: you did not rewrite the query to get this. Someone added an index; the planner noticed and switched plans. The imperative loop from §1 cannot benefit — it still reads every row, because its procedure is hard-coded. To speed it up you must edit and redeploy code.

The planner has two other freedoms the loop forfeits. It can pick the access path per query (index here, scan there, depending on how selective the predicate is — a query for all orders would correctly choose the scan). And it can reorder joins. Asked to join orders to a 500-row premium_users table, the planner can start from the 500 premium users and look up their orders, rather than scanning 100,000,000 orders and checking each against the user table — same declarative query, an orders-of-magnitude-different amount of work, chosen by cost estimate. A hand-written nested loop fixes the join order at authoring time and is stuck with it.

The abstraction sends a bill

The planner's freedom is not free. It relies on statistics — row counts, value distributions — that can go stale, and on a cost model that assumes predicates are independent. When estimates are wrong (correlated columns, skewed values, a freshly loaded table), the optimizer can confidently pick the million-read plan. The senior move when latency surprises you is to ask the engine for its chosen plan (EXPLAIN) — the language tells you what; only the plan tells you why it was slow.

3 · MapReduce: the middle ground

Between "state the result and trust the planner" and "write every step yourself" sits a third style, worth naming because the ML world lives in it. MapReduce asks you to supply two small imperative functions — a map that emits key/value pairs from each record, and a reduce that combines all values sharing a key — while the framework owns the genuinely hard, distributed parts: splitting the input across machines, the shuffle that groups every record by key across the cluster, retries on failure, and writing the output.

declarative ────────────── MapReduce ────────────── imperative (SQL: what) (you: map+reduce; (you: every step, framework: shuffle, one machine) parallelism, retries) you write the per-record logic; the engine still owns parallelism + data movement.

So MapReduce is partly imperative (you write the per-record logic) and partly declarative (you do not write the distribution, grouping, or fault handling — the framework decides how to spread the work across thousands of machines). It is the seam where a single-machine loop becomes a distributed dataflow. Higher-level engines then put a declarative face back on top: you can write SQL over Spark, and the planner compiles it down to map/shuffle/reduce stages. The catch for the designer is that the expensive operation underneath is usually the shuffle — moving data across the network to group by key — not the select. We build this out fully in lesson 18 (batch processing); for now, just hold the position: a third option exists between the two extremes, and many ML "queries" (join labels to features, rebuild an embedding table, compute offline metrics) are really batch dataflows wearing a declarative mask.

Datalog: declarative by rules, not by result-shape

SQL and MapReduce are not the only declarative shapes. A third — older than both — is Datalog, where you do not describe the result you want but write rules that derive new facts from existing ones. A rule has the form predicate(X, Y) :- body, read as "predicate(X, Y) is true IF the body is true"; the engine runs the rules to a fixed point, deriving every fact it can. The power is that a rule can refer to itself, so a single recursive rule expresses traversals of unknown depth:

depends(X, Y) :- edge(X, Y).                 # base: a direct edge
depends(X, Z) :- edge(X, Y), depends(Y, Z).  # recursive: follow one more hop

Those two lines compute the entire transitive closure — the variable-depth lineage query that lesson 01 showed is a clumsy recursive self-join in SQL. Rules also compose: depends can be the body of yet another rule, and the engine figures out the evaluation order, so complex derived relationships build up from small reusable pieces with no execution plan written by hand. This is why the genealogy matters: Datalog's ideas — facts, rules, recursion — are the lineage behind modern graph query engines (the Cypher/SPARQL/Datalog family introduced in lesson 01). When a graph database makes "friends of friends to depth N" a one-liner, it is standing on Datalog's recursive-rule model.

4 · The access pattern is part of the contract

Everything so far assumes a planner you can lean on. Many systems built for scale and for low-latency serving deliberately give that up. The move is to design the storage layout backwards from the queries you must answer, so that the query becomes a near-trivial lookup and there is little left to optimize. Two phrasings name the whole choice:

Stance	Buys	Costs
Query the data you stored (declarative, ad-hoc)	Ask new questions any time; planner adapts as indexes/stats change; one normalized copy of the truth	Latency depends on a plan you don't control; a bad estimate or missing index silently degrades the hot path
Store the data shaped for the query (access-pattern-first)	Predictable, flat latency at scale; the key is the plan; no optimizer to second-guess	Rigid — a genuinely new access pattern needs a new table, index, or backfill; flexibility moves from query time to pipeline time

This is why a key-value store can feel alien to a relational user: it offers no ad-hoc query at all. You can fetch, put, and delete by key — that is the entire interface. There is nothing for a planner to choose, because the key design is the query plan. If you must serve "get the last 50 events for a user," you make the key user_id and store the events as a time-ordered value (or make the key (user_id, timestamp) in a sorted store). You decided the access path when you designed the key, not when the query ran. The same logic drives wide-column stores (Cassandra, Bigtable-style): you start not from entities but from questions — "events by user, newest first," "model versions by registry state" — and each hot question may earn its own table or its own ordering. Lesson 11 (partitioning) shows how the key also decides which machine the data lives on.

Worked number — point lookup vs the scan it replaces

A feature store must serve an online model: given an entity (say a user) at request time, return its 200 features in under 10 ms so the model can score. Designed access-pattern-first, the row is keyed by entity_id and the 200 features are stored contiguously as one value. Serving the request is one key lookup — roughly one I/O, single-digit milliseconds, and the latency does not move whether the table holds one million entities or one billion, because a key lookup does not scan. Now serve the same need with a declarative query against a normalized warehouse — join a dozen feature tables on entity_id, filter to the latest value of each. Even with indexes, that is a dozen lookups plus a join the optimizer must plan, and a cold or mis-estimated plan can spike into tens of milliseconds — fatal inside a request budget. The access-pattern design bought a flat, predictable single-digit-ms read; it paid for it with a pipeline that must pre-assemble and write that wide row ahead of time, and with rigidity: a feature the model didn't ask for at design time isn't in the row.

That same feature store, viewed from the offline side, wants the opposite. To build a training set you scan all entities over a time range and read a few columns each — an analytical query, not a point lookup. The right layout there is the mirror image: column-oriented storage that reads just the needed columns across billions of rows and never pays for a per-row seek. The lesson is not "key-value good, SQL bad." It is that one logical dataset typically needs both layouts — a serving copy shaped for point lookups and an analytical copy shaped for scans — because the two access patterns are genuinely different contracts. We build the storage engines behind each (B-trees for lookups; LSM-trees and columnar for the rest) in lessons 03 and 04.

Failure modes

Nobody owns the plan. With a declarative query and no one reading EXPLAIN, the app blames the DB and the DB blames the app while a million-read plan hides in between. Make plan inspection part of the perf workflow.
Stale statistics, surprise scan. A query that used the index yesterday flips to a full scan after a bulk load skews the stats. Predictable until it isn't — re-analyze after large loads and pin critical plans where the engine allows.
An access-pattern store asked an ad-hoc question. Someone needs "all users in city X" from a store keyed by user_id; with no secondary index that is a full-table scan, the one thing the design optimized away. New questions need new tables or backfills, by design.
One layout serving both contracts. Running heavy analytical scans against the low-latency serving store (or point lookups against a columnar warehouse) — each is the worst case for the other's layout. Separate the serving copy from the analytical copy.
Hidden shuffle in a declarative batch job. A one-line SQL GROUP BY over Spark compiles to a cluster-wide shuffle; the cost is in moving bytes, not in the select. Read the physical plan, not just the SQL.

Decision checklist

For this query, who should own the execution plan — the optimizer (flexible, ad-hoc) or the schema (predictable, rigid)?
What is the latency budget, and is it a point lookup or an analytical scan? They want opposite layouts.
If access-pattern-first: have you enumerated every hot query, and does each map to a key/index that serves it without a scan?
If declarative: which indexes does the hot query depend on, and who keeps statistics fresh? Have you read EXPLAIN on it?
Does this logical dataset need two physical copies — one shaped for serving, one for analytics?
For batch jobs: where is the shuffle, and how many bytes cross the network?
When a genuinely new access pattern arrives, what is the migration path — a new index, a new table, a backfill?

Checkpoint exercise

Try it

You are designing storage for a recommendation system. Two queries must be served: (a) online — "give me this user's 200 features in < 10 ms" on every page load, and (b) offline — "for all users active in the last 90 days, pull these 30 columns to build a training set." Sketch the key/layout you would choose for each. For (a), write the key and say why there is no plan for an optimizer to choose. For (b), say which access pattern (point lookup vs scan) it is and therefore which storage orientation you want. Then state the one fact that justifies keeping them as two physical copies rather than one.

Where this points next

We can now choose a data shape (01) and decide who owns the plan that reads it (02). But the next move stays inside Part 1's storage layer: before we can talk about who owns the plan at scale we need the structures that make a lookup or a scan cheap on disk. Lesson 03 opens the storage engine from its cheapest primitive — the append-only log — and builds up through hash indexes to the B-tree, the structure under the index lookups this lesson kept leaning on.

Where is truth?

A query language does not hold truth — it reads it. The system of record is the authoritative, usually normalized store the declarative query runs against. The copies / derived views are the access-pattern-first shapes you build backwards from the hot query: the wide entity-keyed serving row, the secondary index, the precomputed key whose layout is the plan. Freshness budget: how far behind the system of record each derived layout may lag (tight for a serving row, looser for an analytical copy). Owner: the team that owns the pipeline materializing each derived layout, and the engine statistics the optimizer leans on. Deletion path: delete in the system of record, then re-materialize or evict the derived rows that referenced it. Reconciliation / repair: rebuild a derived layout from the source rather than editing it in place; re-run ANALYZE so the planner's statistics match reality. Evidence it is correct: EXPLAIN confirms the chosen plan, and row-count / checksum comparison between the source and each derived layout confirms the copy still matches truth.

Takeaway

A declarative query (SQL) states the result and lets the query planner choose the procedure — which is why it can switch from a 100,000,000-row full scan to a ~100-read index lookup, or reorder a join by a thousandfold, with no application change; an imperative loop freezes one procedure at authoring time and forfeits all of that. MapReduce is the middle ground: you write per-record map/reduce logic, the framework owns the distributed shuffle and parallelism. At scale the deepest choice is who owns the plan at all: query the data you stored (declarative, flexible, latency you don't control) versus store the data shaped for the query (access-pattern-first, where the key is the plan and latency is flat and predictable but new questions need new tables). A key-value store has no ad-hoc query because its key design already is the query plan. One logical dataset usually needs both layouts — a point-lookup serving copy and a scan-oriented analytical copy — because online feature reads and training-set generation are genuinely different contracts.

Interview prompts

What is the difference between a declarative and an imperative query, and why does it matter for performance? (§1 — declarative states the result and lets the engine pick the procedure; imperative spells out the steps and freezes one plan. Declarative lets the engine improve the plan as data/indexes change, with no code edit.)
The same SQL query was fast yesterday and slow today with no code change. What happened and what do you check? (§2 — the planner switched plans, likely a stale-statistics flip from index to full scan after a bulk load; run EXPLAIN to see the chosen plan, then re-analyze stats or pin the plan.)
Give a concrete case where the planner's freedom saves orders of magnitude. (§2 — adding an index on (user_id, placed_at) lets the planner switch a 100,000,000-row scan to ~100 reads; or reordering a join to start from the 500-row table instead of the 100,000,000-row one.)
Why is MapReduce called a middle ground? (§3 — you supply imperative map/reduce per-record logic, but the framework owns the declarative-ish hard part: distribution, the shuffle that groups by key, and retries.)
A key-value store gives no query language. So where did the "query plan" go? (§4 — into the key design. With only get/put by key, the access path is decided when you choose the key, so the key layout IS the plan; new access patterns need new tables/indexes.)
"Query the data you stored" vs "store the data shaped for the query" — when do you pick each? (§4 — declarative/ad-hoc when questions change and latency is forgiving; access-pattern-first when you have a known hot query and need flat, predictable low latency at scale.)
Why might one feature dataset need two completely different storage layouts? (§4 — online serving is a point lookup wanting a wide row keyed by entity_id for single-ms reads; training-set generation is an analytical scan wanting column-oriented storage. Opposite contracts, so two physical copies.)