Exercises: NoSQL and Specialized Stores

Most of these are decisions rather than code, because that is what this chapter is for. Part D is the exception and it is the one that changes how you approach the next adoption proposal you see.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

12.1 † State the one honest reason to add a store to a platform, and the three bad reasons.

12.2 For each access pattern, name the store class: single-key lookup at sub-millisecond latency · read and write a whole entity · huge writes with range queries in a partition · metrics with bounded label cardinality · typo-tolerant full-text search · semantic similarity.

12.3 † Why should a key-value store never be the system of record for anything analytics needs? Give two reasons.

12.4 What makes DynamoDB not quite a key-value store, and what modelling discipline does that imply?

12.5 † What kills a time-series database — volume or cardinality? Give the arithmetic for adding customer_id as a label on a metric with 4 methods × 6 statuses × 200 endpoints.

12.6 Why are search indexes and vector stores described as "derived data," and what follows for extraction?

12.7 † Give the three reasons a vector store is a data engineering problem rather than an ML one.

12.8 Name the three most over-adopted stores and the one question that tests each.

Part B — Standard ⭐⭐

12.9 Kestrel's Redis holds sessions, carts, rate-limit counters, and a product cache. For each, say whether it needs an analytical path, and if so, how you would get one without scanning Redis.

12.10 † §12.3 says document sources should be landed whole in bronze and given an explicit schema in silver, with a quarantine. Design the quarantine: where mismatched documents go, what metadata travels with them, who looks at them, and how often. Then state what happens when the quarantine grows and nobody looks.

12.11 A team proposes Cassandra for Kestrel's clickstream, arguing correctly that it handles the write volume. Write the response. It must concede the write-volume point, raise the extraction question from §12.9, and propose an alternative — and it must not simply say "use the lakehouse," because that does not address their concern.

12.12 † §12.7's ⚠️ callout describes 6,000 products silently missing from a vector index for five weeks. Write the three defenses as concrete artifacts: the index-versioning procedure, the schema change that stores the model version, and the coverage assertion as SQL. Then state which one you would build first and why.

12.13 For each Kestrel store in §12.1, look up (or reason about) its change feed and fill in the §12.9 table row. Then rank the five by how much work each will be for the data team over five years, and justify the ranking.

12.14 † §12.10's "absorb before you adopt" table claims PostgreSQL is "good enough for longer than people expect" at seven workloads. Pick three and find the actual threshold: what does the documentation or the community say about where JSONB, tsvector, and pgvector stop being sufficient? Cite what you find.

12.15 The 🏭 callout found 45% of zero-result searches were solvable. Design the pipeline that produces that analysis at Kestrel: what event you emit, what fields it carries, where it lands, and what the analysis query looks like. Then state one thing your event design will make impossible to analyze later.

Part C — Deeper ⭐⭐⭐

12.16 Write the adoption proposal you would want to receive for a new store — the template. It must force the author to answer: the measured deficiency in the existing store, the change feed, the second operator, the five-year operational estimate, and the removal condition. Then apply it to one of Kestrel's five existing stores retroactively and see whether it would have passed.

12.17 † §12.9's third rule is "when the store cannot tell you what changed, make the application tell you." Design the outbox pattern for a Cassandra-backed service: what is written, in what transaction, what reads it, and what happens when the outbox reader falls behind. Name the guarantee you get and the one you do not. (Chapter 36 §36.4 covers this properly — attempt it first.)

12.18 Construct the strongest case for a dedicated vector database over pgvector for a company that is not Kestrel. State the volumes, the query pattern, and the specific pgvector limitation that decides it. Then state what would have to change for the answer to flip back.

12.19 † The chapter claims most "graph" problems in a data platform are two or three joins deep and better served by SQL. Find a genuine counterexample — a question that a recursive CTE handles badly and a graph traversal handles well — and explain what makes the difference. Chapter 18 §18.5 covers recursive CTEs; you may need to read ahead.

Part D — The Kestrel Platform ⭐⭐⭐

12.20 — Increment 12: the store inventory.

Create platform/docs/store-inventory.md. For every store the Kestrel platform touches — PostgreSQL, the warehouse, object storage, Redis, Elasticsearch, MongoDB, the metrics TSDB, pgvector — record:

Then the part that makes it worth doing: count the systems, divide by four engineers, and compare to Chapter 5 §5.1's ceiling. Write one paragraph on what you would do about the result.

12.21 † Add platform/docs/adr/adr-005-vector-store.md, deciding between pgvector and a dedicated vector database for Kestrel's similarity search. Use Chapter 3 §3.7's template.

Your "what would reverse this" section must contain at least two observable conditions — a vector count, a query-latency percentile, or a feature requirement — not "if requirements change."

Reflection

A. §12.1 says nobody chooses six databases and everybody ends up with six, and that no individual decision was wrong. Where else does this pattern appear — a good outcome at every step and a bad one in aggregate? What structural fix works, other than counting?

B. The chapter's most repeated advice is "extract from the source, not the derived store." Where in your own experience has someone extracted from a derived store, and what did it cost?