Your team keeps adding precomputed columns and summary tables to the transactional database. How do you govern that so the schema stays trustworthy?
answer
- five artefacts: justification, mechanism, staleness, reconciliation, owner
- cumulative drift, not one bad column
- make derived columns legible in the DDL
- review the register; remove when unjustified
- heavy reporting leaves OLTP for a replica or analytical store
basics
~20 sTreat every redundancy as a contract, not a column: named owner, sync mechanism, stated staleness, reconciliation query with alerting, and the measurement that justified it. Keep a register of them, review it periodically, and remove ones whose justification no longer measures. Undocumented copies are how a schema stops being trustworthy.
solid answer
~50 sThe failure mode is not any single denormalization — it is a schema where nobody can say which columns are derived, what maintains them, or how stale they may be. Readers then trust everything equally, including values that have been wrong for months. So I require every deliberate redundancy to ship with five things: the **query and measurement** that justified it; the **sync mechanism** (trigger, same-transaction write, asynchronous job); the **staleness contract** — exact, or bounded by N minutes; a **reconciliation query** that recomputes truth and alerts on mismatch; and a named **owner**. I make derivation visible in the schema — naming conventions and column comments — so a reader can tell a stored fact from a computed one without reading service code. I keep a register of these and re-review it, because most exist to serve a query that has since changed. And I draw a hard line: heavy analytical reporting belongs outside the transactional schema, not as one more summary table competing with OLTP writes.
go deeper
Know that redundant columns need documenting and a way to detect drift; do not attempt to set policy.
Describe what a single redundancy should ship with — sync mechanism, staleness, reconciliation query — and why each matters.
Turn it into a review standard applied to every change, and show how you would detect and repair drift in an existing one.
Speak to cumulative schema trust: a register with owners, periodic re-justification, legibility in the DDL, and a boundary that keeps analytical reporting out of the transactional store.
## The real risk is not per-change, it is cumulative Any single precomputed column, reviewed properly, is a reasonable engineering trade. What degrades a schema is accumulation without a record. After two years you have a dozen derived values, several maintained by mechanisms nobody remembers, some maintained by nothing at all because the service that updated them was retired. Now no reader can distinguish a stored fact from a cached one, and the schema's central promise — that a value in a column is true — has quietly lapsed. That is a trust problem, and trust is repaired much more slowly than latency. ## Make each redundancy a contract I ask for five artefacts with every one, at review time: **Justification.** The query it serves and the measurement showing the join or aggregate dominated. Without this, the redundancy is permanent by default, because no future engineer can prove it is safe to remove. **Mechanism.** Exactly what maintains it: a trigger (covers all write paths, transactional, hides cost), a same-transaction application write (explicit, but only as good as the discipline of every writer), or an asynchronous job (cheap and self-healing, but stale by construction). Naming it forces the guarantee to be chosen rather than assumed. **Staleness contract.** Either exact-with-the-transaction, or bounded by a stated window. This is the single most useful thing to write down, because it tells every future reader whether the value may be used for a decision or only for display. A value that enforces a limit or drives money movement cannot be asynchronous. **Reconciliation.** The query that recomputes the truth and reports mismatches, scheduled, with an alert. Drift is a matter of when. A redundancy without a detector is an assertion that no crash, bug, or unusual write path will ever happen. **Owner.** A team that gets the alert. Ownerless invariants decay. ## Make derivation legible in the schema itself A reader browsing the DDL should be able to tell derived columns from source ones. Conventions help — a consistent suffix, a column comment naming the maintaining mechanism and the reconciliation query. Summary tables should be named so nobody mistakes one for a base table. The goal is that a newcomer writing a report picks the right table without needing tribal knowledge, and that anyone writing a new write path can see what else they must update. ## Review the register, and be willing to remove Redundancies are usually added to serve one screen. Screens change. I re-review the register periodically and ask, for each: is the justifying query still hot; would removing this now be absorbed by an index the schema has since gained; is the sync mechanism still running. Removing a stale denormalization is one of the highest-leverage cleanups available, because it deletes a column, a trigger, a job, an alert and an entire class of bug at once. ## Prefer mechanisms that cannot drift Where the platform offers a maintained view or an incremental materialisation that the engine refreshes, prefer it to hand-rolled synchronisation: the guarantee comes from the database rather than from every writer's memory. Likewise prefer a covering index over a copied column whenever it delivers the same read reduction, and a cache with an expiry over a permanent column whenever the read tolerates seconds of staleness — an expiring cache is self-correcting, while a stored value is wrong until a human notices. ## Draw the boundary at reporting The most common way this governance fails is scope creep: summary tables begin serving genuine analytical reporting, growing wider and more numerous, refreshed by increasingly heavy jobs that contend with transactional writes. At that point the right move is not another table but a separate destination — a read replica for heavy read-only queries, or a dedicated analytical store. Keeping the OLTP schema focused on transactional correctness is what preserves the ability to reason about it at all. ## What I say in the room I do not block denormalization; it is a legitimate and often necessary tool. I block *undocumented* denormalization. The column is the easy part — the contract around it is the engineering, and demanding that contract is what keeps a schema comprehensible after the people who wrote it have moved on.
- What is the single most useful thing to document about a derived column?Its staleness contract — exact within the writing transaction, or stale by up to a stated window. That one line tells every future reader whether the value may drive a decision or only a display, and it implicitly reveals the sync mechanism. Everything else can be reconstructed with more effort; this cannot be guessed safely.
- When would you refuse a proposed summary table and suggest something else?When it serves analytical reporting rather than a transactional read path: wide, growing, refreshed by heavy jobs that contend with OLTP writes. That work belongs on a read replica or a separate analytical store, so the transactional schema keeps its focus and its write throughput. Adding one more summary table is a short-term fix that compounds.
saying these in an interview costs you the question
- Banning denormalization outright rather than governing it, which just pushes the copies into application caches nobody reviews.
- Approving derived columns with no reconciliation job, assuming the sync mechanism is infallible.
- Leaving derived and source columns indistinguishable in the DDL, so readers cannot tell what is authoritative.
- Never recording the measurement that justified the redundancy, making removal impossible to argue for later.
- Letting summary tables grow into a reporting warehouse inside the transactional database.