CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Define denormalization (15 minutes)

Explain, in one sentence, what denormalization means.

Done when: the explanation correctly describes deliberately storing redundant or precomputed data to avoid a join or recalculation.

Hint

Denormalization deliberately stores redundant or precomputed data to avoid an expensive join or recalculation on every read.

Easy 2: Name the real cost (15 minutes)

Name the ongoing cost every denormalization decision introduces.

Done when: the consistency-keeping obligation is correctly named.

Hint

The ongoing cost of keeping the duplicate copy consistent with its source.

Easy 3: Spot a good candidate (15 minutes)

State whether caching a book's total review count, updated by a trigger, is a reasonable denormalization candidate, given reviews are read far more often than added.

Done when: reasonable is correctly judged, with the read-to-write ratio cited as the reason.

Hint

Reasonable, since reads (viewing a book's review count) vastly outnumber writes (adding a review).

Medium

Medium 1: Propose the sync mechanism (25 minutes)

For a denormalized total_reviews column on books, propose how it should stay in sync whenever a review is added or removed.

Done when: a correct trigger-based (or equivalent) sync mechanism, firing on both insert and delete of reviews, is proposed.

Hint

A trigger on review inserts and deletes that increments or decrements books.total_reviews accordingly.

Medium 2: Judge a bad candidate (25 minutes)

Explain why caching a customer's current address directly on every order row, for fast receipt printing, is a questionable denormalization choice.

Done when: the explanation correctly notes addresses change relatively often, and old order receipts rarely need the live current address rather than the address at the time of the order.

Hint

Addresses change over time, and an order's receipt should typically show the address valid at the time of that specific order, not whatever the customer's address happens to be today.

Medium 3: Materialized view as an alternative (25 minutes)

Explain why a materialized view might be a better fit than a hand-maintained denormalized column for a monthly_revenue summary.

Done when: the explanation correctly notes a materialized view's refresh schedule is explicit and visible, rather than relying on scattered application code or an easily-forgotten trigger.

Hint

A materialized view's REFRESH schedule is an explicit, documented operation, unlike a denormalized column whose update logic might be scattered across application code or an easily-overlooked trigger.

Hard

Hard 1: A denormalization that silently went wrong (30 minutes)

Describe a realistic incident where a denormalized column drifted out of sync with its source, and identify what was missing from the original denormalization decision.

Done when: a plausible drift scenario is described, with the missing piece correctly identified as an incomplete or absent sync mechanism.

Hint

A missing or incomplete trigger, perhaps covering updates but not deletes, or vice versa, is a common cause of silent drift; the fix is a complete, tested sync mechanism covering every way the source data can change.

Hard 2: Accepting staleness on purpose (35 minutes)

Describe a realistic case where a denormalized value is allowed to be slightly stale on purpose, rather than kept perfectly in sync, and explain why that trade-off is acceptable there.

Done when: a realistic case is described, such as a "trending books" count refreshed hourly, with a reason the staleness window is acceptable for that specific use.

Hint

An hourly-refreshed "trending books" count accepts staleness because exact real-time precision adds little value for a feature that is inherently approximate and browsed casually.

Hard 3: Reversing a denormalization decision (40 minutes)

A denormalized author_name_snapshot column, added years ago, is now causing real consistency bugs after several author-name corrections were missed. Describe the steps to safely remove it and switch back to a join.

Done when: a safe migration plan is described, including verifying the join-based query produces correct results, updating application code to use it, and only then dropping the denormalized column.

Hint

Verify a join-based replacement query returns correct results against current data, switch all reading code to use the join instead of the column, monitor for any missed usage, and only then drop the denormalized column.