Derived Compatibility Table
also called Legacy View on New Fact, Backward-Compatible Rollup
Keeping the old fact table alive as a rollup computed from its finer-grained replacement, so consumers migrate on their own schedule and the two can never disagree.
A fact table at order grain feeds 140 dashboards. Finance needs order-line analysis, which requires a finer grain. Editing the table in place would multiply every row count by four and silently inflate sums across every consumer. The pattern is to build the new fine-grained fact, then regenerate the old table as an aggregation of it, so the old name keeps its meaning while the new one carries the detail.
Why it matters
Changing a table's grain is a semantic change, not a schema change: a column added to a fact can multiply rows, and every SUM and COUNT(*) downstream is now wrong without any error. Many organisations discover this during a quarter-end review. The compatibility table separates the decision to introduce detail from the schedule on which hundreds of consumers adapt.
Implementation patterns
- Declare the grain of both tables in a sentence and enforce it with a uniqueness test.
- Build the new fact first, populate it, and verify it against source.
- Generate the old table from the new one (a view for small volumes, a scheduled materialisation for large ones), so it is never an independent copy.
- Run a parity check between the old table (as originally built) and the derived one, by day, on counts and sums.
- Allocate order-level measures (shipping, discounts) to lines, or keep them in an order-level fact, to avoid double counting.
- Deprecate with evidence: log reads of the old table, announce a date, and remove only after a period of zero reads.
Industry example
This is an archetype: a ride-hailing or marketplace platform with a few hundred dashboards on one orders table. The sequence of announce, dual-run, measure reads and remove is the same deprecation practice public API teams use, and the rollup-from-detail form is what keeps parity provable; no single company's version is cited here.
Failure scenarios
- Double counting when a consumer joins line-level facts to order-level measures and sums once per line.
- Count inflation: a dashboard that counted rows as orders now counts lines.
- Derived table lag: the rollup refreshes after consumers read it, so morning numbers lag.
- Permanent coexistence because nobody owns the deprecation, and two tables diverge in meaning.
Trade-offs
| Choose | Gains | Pays |
|---|---|---|
| Derived compatibility table | Safe, gradual consumer migration | Extra pipeline and a deprecation process |
| In-place change | Fast | Silent breakage of unknown consumers |
| Hard cut-over date | Clear deadline | Breaks the long tail on the day |
When not to use it
If the table has a few known consumers owned by one team, change it with a fixed parity query and a single release. Use the pattern when consumers are numerous or unknown.
Interview question
Q: You must change the grain of a widely used fact table. Walk through the migration.
What a strong answer covers: consumer inventory from query logs; a new table rather than an edit; the old table as a rollup of the new; parity checks; waves of consumer migration; deprecation based on measured zero reads; and the double-counting traps.
Quick check
Quiz: What guarantees the old and new tables agree? The old one is computed from the new one.
Flashcard: Why not add a column to change the grain? — Row counts multiply and every aggregate silently inflates.