An interviewer says — design the data model behind a subscription business's revenue reporting. Finance restates prior periods when a contract is amended and the board pack must be reproducible months later. Where do you take this?

What the interviewer is testing

Whether you notice that "reproducible" and "restated" are in direct conflict, and that resolving them is a modelling decision rather than a reporting one. A candidate who starts drawing a star schema has already missed it. The star schema is fine; it is the time dimension that is the problem.

The clarifying questions that change the answer

• Does an amendment change the past or only the future? Both, and differently by amendment type. A mid-term upgrade changes future revenue; a corrected start date changes revenue already reported. The model must express both.
• Is the board pack a live query or a saved artefact? If the answer is "we re-run the notebook", reproducibility does not currently exist.
• Is there a close calendar — a date after which a period is frozen, and who may unfreeze it?
• What reconciles to the general ledger, and how often?

A strong answer's arc

Two time axes, carried on the fact. The effective date is when the world changed. The knowledge date is when we learned it. Every revenue row records both, as ranges.

The grain is one row per contract line per revenue period per version. A restatement is an insert, never an update: the superseded row keeps its knowledge-date range and stops being current. Nothing is ever overwritten, so the board pack becomes a query with a fixed knowledge date — "as we knew it on the close date" — and is reproducible by construction, because it cannot read rows recorded afterwards.

Freezing a period is then a knowledge-date cut rather than a lock on a table, which means a frozen period and a restatement can coexist without anyone having to choose.

What it costs

Every query carries two predicates and analysts will forget one. The semantic layer has to supply them, with "as reported" as the default and "as now" as an explicit opt-in, or the model's guarantee lasts until the first ad-hoc query. Storage grows with amendment volume: a business amending 3% of 200,000 contract lines a month adds about 6,000 rows a month, which is nothing. Reconciliation to the ledger becomes a scheduled test rather than a hope, and it is the test that fails first when someone writes an update where the model expects an insert.

Common weak answers

• Type 2 on the customer dimension and nothing else. That tracks attribute change. It does not let you reproduce a number that has since been restated, because the fact was overwritten.
• Recomputing the board pack from current data. It will differ from the one presented, and the discovery usually happens in front of the board.
• An audit table nobody joins to. History that is not on the query path is history that does not exist.

What a strong answer adds

A monthly job that recomputes the frozen snapshot and alerts on any difference, so drift is found by the platform rather than by an auditor. A stated retention for superseded versions, since "never delete" meets a regulator eventually. And the organisational point: the restatement policy is a finance decision that the architecture encodes, and getting it written down — which amendment types restate, who approves, how far back — is most of the work and none of the code.