Dimensional Modeling and Historical Restatement

A finance analyst runs “revenue by region for March” on the 12th of April and gets one table. A colleague reruns the same query on the 26th and gets a different table, although nobody changed the query and the total is identical. The only thing that moved is which region some orders are credited to. Whether that movement is a defect or the system working as designed depends on decisions made when the tables were modeled: what one row means, how a customer’s region is stored over time, which version of the customer a sale points at, and what happens when the warehouse learns that an earlier fact about the customer was wrong.

This post covers those decisions in the order they have to be made. It starts with grain and keys, then the fact and dimension tables built on them, then slowly changing dimensions as the mechanism for keeping history. It then covers late facts, late dimension rows and retroactive corrections, and finishes with reconciliation, which is how you demonstrate that a restatement changed what you intended and nothing else. All data in the examples is invented for illustration, and the calculations were checked by running the queries shown against a small in-memory SQLite database; no output here comes from a real system.

The post assumes you can read SQL joins and aggregates, and that you have seen a star schema before. Data Modeling for Operational and Analytical Systems introduced facts, dimensions, grain, and the type 1, type 2, and snapshot patterns; here those ideas are worked through to their consequences rather than introduced. Loads are assumed to be rerunnable without changing the result, the property described in Building Incremental and Idempotent Pipelines. The previous post in this series, Warehouse Workload Isolation and Concurrency, is about the compute these queries run on; nothing in this post depends on how that compute is partitioned, but a restatement is a heavy write that you will want to schedule on compute where it does not compete with dashboards.

Grain and keys: the two decisions everything else inherits

Declare the grain before choosing anything else

The grain of a fact table is the exact statement of what one row represents. The Kimball Group’s design guidance calls declaring it the pivotal step, because every candidate dimension and every candidate measure must be consistent with it, and it advises against mixing grains in one fact table. A usable grain statement is a sentence that someone else can test against data: “one row per order line, identified by order number and line number” can be checked with a uniqueness query. “Orders, with some detail” cannot.

The same guidance recommends starting from atomic grain, the lowest level at which the business process captures data, because it survives questions nobody anticipated. Summary grains are valuable for performance, but they presuppose the common questions. That advice has a direct consequence for restatement. If you keep only a daily regional total, a correction to a customer’s region cannot be applied at all, because the information needed to move individual orders between regions is gone. If you keep order lines, the correction is a reassignment of known rows.

A grain statement should be accompanied by its uniqueness rule, because the rule is what makes the declaration enforceable. The check is a single query:

-- Grain test for a fact table declared as "one row per order line".
-- Any row returned is a violation of the declared grain.
SELECT order_id, line_no, COUNT(*) AS n
FROM fact_order_line
GROUP BY order_id, line_no
HAVING COUNT(*) > 1;

Declared keys in many analytical engines are informational: the engine accepts them but does not reject a duplicate. Treat the grain test as a scheduled data test, not as something the table definition guarantees. The behavior depends on the engine, so check the documentation of the system you use before relying on a declared constraint.

Three kinds of fact table, three kinds of grain

Kimball’s techniques distinguish three fact table patterns, and the choice determines what a row is and whether it is ever updated.

Pattern	A row is	Updated after insert?	Typical measures	Restatement consequence
Transaction	A measurement event at a point in time, such as one order line	No; a correction is a new row or a deliberate rewrite	Quantities and amounts, fully additive when units and currency agree	Re-attribution changes the dimension keys on existing rows, not the measures
Periodic snapshot	A state or summary for a standard period, such as an account balance at the end of a day	No; each period gets its own rows	Balances and levels, semi-additive across time	A late correction to a past period means rewriting that period’s rows
Accumulating snapshot	One instance of a process with predictable steps, such as one order line from placement to delivery	Yes, revisited as milestones complete	Milestone dates and lag durations	The latest row cannot show earlier states; keep events or versions if you need them

The descriptions in the table follow the Kimball Group’s definitions of each pattern. The important point for this post is the last column. A transaction fact table has the most favorable property for restatement: the measure is a recorded event, so history changes only in how events are grouped, not in what they measured. The other two patterns store derived or revisited state, so a correction usually means recomputing rows.

Additivity decides what a total can mean

Every measure in a fact table needs a stated rule for how it combines. Kimball’s classification has three classes: fully additive measures can be summed across every dimension of the fact table; semi-additive measures, with account balances as the standard example, can be summed across some dimensions but not time; non-additive measures such as ratios cannot be summed at all. For non-additive facts, the recommended approach is to store the additive components, sum those, and compute the ratio afterward. The glossary entries on semi-additive facts and non-additive facts give worked cases, including why averaging a column of ratios weights each row equally regardless of its size.

Additivity matters to restatement for a specific reason: the reconciliation checks later in this post rely on it. A check of the form “the grand total must not change when only attributes were corrected” is valid for an additive amount and meaningless for a distinct count or a balance summed across time. Decide which measures participate in each check when you declare them, not when a check fails.

Natural, surrogate, and durable keys

A dimension row needs a primary key that the warehouse controls. The natural key, the identifier the source system uses, cannot play that role for a dimension that keeps history, because several rows will describe the same member. It is also outside the warehouse’s control: the Kimball Group’s example is an employee number that changes when someone resigns and is rehired. Natural keys from different sources can also collide. The Kimball guidance is to assign an anonymous integer surrogate key to each dimension row, with the date dimension as the stated exception because it is predictable and stable.

Surrogate keys identify rows, and with type 2 history each row is a version. Following one real-world entity across all its versions needs a third key. The Kimball Group calls it the durable key: persistent, independent of the original business process, and unchanged when the profile changes. The three keys answer different questions.

Key	Identifies	Changes when	Used for
Natural key	A record in a source system	The source reissues or migrates it; another source uses a different one	Matching incoming records, tracing back to the source, reconciliation
Surrogate key	One version of a dimension member	Each time a tracked attribute gets a new version	The fact table’s foreign key, so a fact points at the version in effect
Durable key	The member itself, across versions and source identifiers	Never	Counting members, as-is reporting, re-attributing facts after a correction

A fact table’s foreign key to a type 2 dimension is therefore a statement of intent. If it holds the surrogate key, the fact is permanently attached to a version, which gives an “as it was” view without any date logic in the query. If it holds only the durable or natural key, the fact is attached to the member and the query must say which version applies. Many designs store both, and that is what the type 7 technique described later relies on.

One more key type comes up at the grain boundary. A degenerate dimension is an identifier, such as an invoice or order number, that has no descriptive attributes of its own. It is stored directly in the fact table with no dimension table behind it, and it is useful for grouping order lines back into orders and for tracing a fact to its source record during reconciliation.

Fact and dimension models

The design sequence and the star it produces

The Kimball Group’s four-step design process orders the decisions: select the business process, declare the grain, identify the dimensions, identify the facts. The order is deliberate. Dimensions are chosen after the grain because each must take a single value for every row at that grain. A customer is a legitimate dimension of an order line, since one line belongs to one customer. A support case is not, because one order line can relate to several cases, and putting case attributes on the line would repeat or lose rows.

The result is a star schema. The text diagram below is the model used in the rest of this post; it is invented for illustration.

dim_customer (type 2)                         dim_date
 customer_key  PK (surrogate)                  date_key  PK
 customer_id   (natural key, source)           calendar_date, month, quarter, ...
 durable_id    (durable key)                          |
 region, segment                                      |
 valid_from, valid_to                                 |
 recorded_at, superseded_at                           |
        \                                             |
         \                                            |
          fact_order_line   grain: one row per order line
           order_id (degenerate), line_no, date_key, customer_key,
           amount (additive), currency, loaded_at

Two columns in the fact table are easy to omit and expensive to lack later. loaded_at records when the warehouse received the row, which is the only way to tell later how late data really was and to reproduce what a report contained on a given day. The business timestamp that determines which dimension version applies, here the order date, is separate and must never be replaced by the load time.

Unknown members, role-playing dates, and conformed dimensions

A fact row whose dimension value is missing must still join, or an inner join silently drops it and totals shrink. The usual remedy is a reserved “unknown” row in each dimension, with a flag so reports can count how many facts landed there. A late arriving dimension is the case where this placeholder is created automatically; it is covered below.

A single date dimension commonly plays several roles in one fact table: order date, ship date, due date. Each role is a separate foreign key to the same dimension, exposed under role-specific names. This matters for metric definitions, because “revenue by month” is ambiguous until you say which of those dates is meant, a point the next post in this series develops.

When two fact tables are to be compared by a shared attribute, such as orders and shipments by region, the attribute must mean the same thing in both. A conformed dimension has matching column names and domain contents across fact tables. Conforming is mostly an agreement problem: the technical structure is simple, but deciding what “customer” or “region” means across sales, billing, and support is a negotiation. It also interacts with history. If the orders and shipments models use different effective dates for the same region change, a comparison by region can disagree even though both are internally consistent.

Fan-out: the join that silently multiplies

The most common way a dimensional model produces a wrong total is a join that matches more than once. A fact joined to a type 2 dimension on the natural key alone matches every version of the member and repeats the fact once per version. The join cardinality and double counting glossary entries explain the mechanism. The calculation example below uses the invented customer used throughout this post.

Customer C-100 has two dimension versions: North until 10 March 2026 and South from that date. Four orders exist (the data are in the worked example later). Joined on the surrogate key stored in each fact, the amounts total 250, split North 130, South 50, West 70. Joined on customer_id alone, each of C-100’s three orders matches two versions, the amounts of 100, 30, and 50 are each counted twice, and the total becomes 430. Nothing errors, and the row count is the only visible symptom: seven joined rows where four facts exist. Equal row counts would not even be a reliable alarm, because one fact that matches no version and another that matches two leave the count unchanged. The check that works is a match count per fact key, which appears in the reconciliation section.

Slowly changing dimensions

The techniques, chosen per attribute

A slowly changing dimension technique specifies what happens to a dimension row when a descriptive attribute changes in the source. The Kimball techniques define the following, and the choice is made per attribute rather than per table.

Type	What happens on change	History kept	Suitable when	Cost or limit
0	Nothing; the original value is retained	Original only	The attribute is labeled “original” or is a durable identifier	Reports can never see the new value
1	Overwrite the value	None	The change is a correction, or only the current value matters	Destroys history; aggregate tables and cubes built on the old value must be recomputed
2	Add a new row with a new surrogate key	Full, at the capture resolution	Facts must be grouped by the value in effect when they occurred	More rows; the key must be generalized beyond the natural key; needs effective and expiration dates and a current indicator
3	Add a column holding the previous or alternate value	Limited	A fixed alternate view is needed, which the Kimball Group notes is used relatively infrequently	Cannot represent more than the columns provided
4	Move a rapidly changing group of attributes to a mini-dimension	Through the mini-dimension’s keys in the fact table	A few attributes change much faster than the rest of a large dimension	The fact table carries two foreign keys
6, 7	Hybrids that deliver both the historical and the current value	Full plus current	Both as-was and as-is reports are required	More columns or two access paths to maintain

The Kimball description of type 2 says it requires “a minimum of three additional columns”: a row effective date, a row expiration date, and a current row indicator. That minimum is the basis for the dimension in this post. Type 2 is the technique that makes historical restatement a question, because it creates the version rows that facts attach to. The remaining sections concentrate on it.

How a type 2 load works

A type 2 load compares the incoming record for each member with the member’s current row. If no tracked attribute differs, nothing happens. If one differs, the load closes the current row by setting its expiration to the change’s effective time, and inserts a new row whose effective time equals that same instant. Both steps must commit together, or readers see a member with no current row or two.

The rules that make this reliable are small, and each one prevents a specific failure.

• Choose which attributes are tracked. A change to an untracked (type 1) attribute overwrites it in place on every version, while a change to a tracked attribute creates a version. Tracking every column turns a typo fix into a new version and multiplies rows.
• Use half-open intervals. A version applies to a date t when valid_from <= t < valid_to, and one version’s end equals the next version’s start exactly. With closed intervals both versions match on the change date and every fact on that day is duplicated, the failure described in the glossary entry on late arriving facts.
• Detect change on the tracked columns only, in a way that survives reruns. Comparing a hash of the tracked columns against the stored hash on the current row means a rerun of the same batch compares equal and writes nothing. A load that compared timestamps without tracking content would create a new version on every rerun.
• Order changes within a batch. If the source delivers two changes for one member in one batch, apply them in order of their effective times, producing two versions, not one that skips the intermediate state.
• Keep the sequence, not the distinct values. A member that goes from segment A to B and back to A has three versions and only two distinct segment values. Deduplicating versions by attribute values collapses the sequence and breaks the intervals.

An open-ended current row needs a convention. Some teams store NULL in valid_to, others a far-future date such as 9999-12-31. The dbt documentation for snapshots, which implement type 2 over mutable source tables, describes both: by default the end column is null for current records, and a configuration option sets a chosen value instead, with the stated benefit of simpler date-range filtering. The far-future date is a storage convention, not a claim that the value will remain true.

-- Resolve a fact to the dimension version in effect on its business date.
-- Half-open interval: the start is inclusive, the end is exclusive.
SELECT d.customer_key
FROM dim_customer d
WHERE d.customer_id = :source_customer_id
  AND :order_date >= d.valid_from
  AND :order_date <  d.valid_to
  AND d.superseded_at = DATE '9999-12-31';  -- current knowledge; see the bitemporal section

The query should return exactly one row for every fact. Zero rows means missing history for that member and date, and more than one means overlapping intervals; both are integrity failures, not data to be handled by taking the first match.

Where history comes from, and what it cannot recover

A type 2 dimension can only record changes that the pipeline observed. When the source is a mutable table that overwrites values without keeping history, the warehouse sees only the state at each extraction. dbt’s snapshot feature illustrates the pattern: on each run it compares the current source rows with its stored history, using either an updated_at column (the timestamp strategy, the one the dbt documentation recommends where a reliable column exists) or a comparison of listed columns (the check strategy). It closes changed rows and inserts new versions, and the documentation notes that snapshots cannot be rebuilt, which is why it suggests keeping them in a separate schema with distinct privileges.

Three limits follow from how capture works, and they hold for any implementation of the idea, not only that tool. First, a value that changed and changed back between two extractions leaves no trace. A daily snapshot comparison reveals only states at its observation times, whereas log-based change data capture records each committed change but depends on the source log being retained. Second, the effective time recorded for a version is often the time the pipeline noticed, not the time the change happened in the world, unless the source supplies a trustworthy change timestamp. Third, a hard delete in the source is invisible to a comparison that only looks at rows that exist. The dbt documentation lists a hard_deletes option with ignore, invalidate, and new-record behaviors for this case, which shows that the question has to be decided explicitly.

Because the history cannot be recreated once lost, a snapshot-capture table is an asset to protect: backed up, restricted from casual rebuild, and excluded from any “full refresh” that drops and recreates derived tables. The dbt documentation states that snapshots ignore the full-refresh flag for exactly this reason.

As-was and as-is

Type 2 gives the as-was view: a sale is grouped by the region the customer belonged to when it happened. Business users also ask the as-is question: group all of last year’s sales by the customer’s region today, for example when a sales territory is being reorganized. Both are legitimate, and a model should provide each deliberately rather than leave users to discover which one a query returns.

The Kimball type 7 technique supports both from one dimension. The fact table carries the surrogate key and the durable key. The as-was perspective joins on the surrogate key with no restriction on the current flag. The as-is perspective joins on the durable key and constrains the dimension to its current rows. The two perspectives are published as separate views so a dashboard author chooses by view name instead of by query logic. Type 6 achieves a similar result by embedding the current value of an attribute in every historical row of the member, overwriting it on all rows whenever it changes, which works but rewrites many rows on each change.

-- As-was: the version in effect when the order happened (join on the stored version key).
CREATE VIEW v_order_region_as_was AS
SELECT f.order_id, f.amount, d.region
FROM fact_order_line f
JOIN dim_customer d ON d.customer_key = f.customer_key;

-- As-is: the member's current knowledge (join on the durable key, current rows only).
CREATE VIEW v_order_region_as_is AS
SELECT f.order_id, f.amount, d.region
FROM fact_order_line f
JOIN dim_customer d
  ON d.durable_id = f.durable_id
 AND d.valid_to = DATE '9999-12-31'
 AND d.superseded_at = DATE '9999-12-31';

Late facts, late dimension rows, and late corrections

Sources do not deliver everything on time or in the right order, and each way of being wrong has a different repair. The three cases below are easy to confuse because all of them are described as “late data.”

Case	What arrives out of order	The problem	Repair	Does published history change?
Late arriving fact	A fact whose event time is older than a dimension change already loaded	“Current” dimension context is the wrong version for this event	Look up the version effective at the event time	Only for the period the fact belongs to, because the fact itself is new to the warehouse
Late arriving dimension	A fact whose dimension row does not exist yet	No row to point at	Insert a placeholder row with the natural key and unknown attributes; overwrite it when the real data arrives	Attributes of already-loaded facts change from unknown to known
Retroactive change	A dimension change that turns out to have taken effect in the past	Existing facts are attached to versions whose intervals were wrong	Split or adjust the version intervals and re-point affected facts	Yes, for every period the corrected interval touches

Late arriving facts

The Kimball definition of a late arriving fact is one where the most current dimensional context does not match the incoming row, so the dimensions must be searched for the keys effective when the event happened. Lateness in days is not what matters. What matters is whether a version change lies between the event and the load. A sale at 09:59:59 that arrives at 10:00:01, after a change at 10:00, is wrong with current context, while a fact weeks late is fine if no change fell in between.

The lookup is the interval query shown earlier, driven by the event’s business date. The loaded timestamp stays on the row, so the warehouse can later measure how late data tends to be, which tells you how long to keep reprocessing windows open (see late data and lookback). If the period the fact belongs to has already been published, you must decide between restating the period and recording an adjustment in the current period. Neither is automatically right: restating keeps period totals true to events, while an adjustment keeps published numbers stable. The decision belongs in the model’s documented rules and must be applied consistently.

Late arriving dimension rows

Kimball’s treatment of a fact that arrives before its dimension context is to create a special dimension row holding the natural key and generic unknown values, load the fact against it, and later update that row with type 1 overwrites when the real attributes arrive. The fact already points at the row, so it picks up the correct attributes with no reload. Two details determine whether the approach is safe. The placeholder must carry the natural key, because that is the only link to the record that eventually arrives. And it should be flagged, with a count and an age tracked for unresolved placeholders, since otherwise they become a permanent “Unknown” bucket that nobody investigates. A fact arriving before its dimension also needs a timeout rule: a customer record that never arrives should surface as an alert, not as silently aging placeholder rows.

The type 1 overwrite is correct for a placeholder, because the unknown values were never true. It would be wrong for a real version, where overwriting discards history.

Retroactive changes and the two clocks

The third case is the hard one. Kimball notes that a retroactive change to a type 2 attribute means inserting a new dimension row and restating the associated fact rows. Doing that without losing the ability to say what earlier reports showed requires tracking two kinds of time, which Martin Fowler’s article on bitemporal history calls actual history and record history, noting that they are also known as valid time and transaction time. Actual history is what is true in the world; record history is what the system knew, and when.

Fowler’s example is payroll. Sally’s salary is raised on 15 February, but the company learns of it on 15 March, after running payroll on 25 February at the old rate. Asked on 25 March what her salary was on 25 February, the answer depends on whether you mean what is now known to be true or what was believed when the payroll ran. Both are valid questions, and a model that stores only one clock can answer only one. A single loaded_at column is not enough, because it cannot tell you which dimension rows were in force when an old report ran, once those rows have been overwritten or closed.

For a dimension this means two pairs of columns. valid_from and valid_to bound the period the attribute value applies in the world. recorded_at and superseded_at bound the period during which the warehouse believed that row. A correction never deletes or edits a row’s attribute values. It closes the old rows by setting superseded_at, and inserts new rows with the corrected intervals, stamped with the time of the correction.

A worked example with numbers

This is an invented, small dataset to make the arithmetic visible. Customer C-100 is recorded as moving from North to South on 10 March 2026. The warehouse learned of the move on 12 March. C-200 is in West throughout. On 25 March the source corrects the move date: the customer actually moved on 1 March.

customer_key	customer_id	region	valid_from	valid_to	recorded_at	superseded_at
1	C-100	North	2026-01-01	2026-03-10	2026-01-01	2026-03-25
2	C-100	South	2026-03-10	9999-12-31	2026-03-12	2026-03-25
3	C-200	West	2026-01-01	9999-12-31	2026-01-01	9999-12-31
4	C-100	North	2026-01-01	2026-03-01	2026-03-25	9999-12-31
5	C-100	South	2026-03-01	9999-12-31	2026-03-25	9999-12-31

Rows 1 and 2 were the warehouse’s belief until 25 March. They are kept, closed by superseded_at. Rows 4 and 5 are the corrected belief. The four orders below are loaded under the original belief, with order O3 arriving late on 20 March.

order_id	customer_id	order_date	loaded_at	amount	customer_key before correction	customer_key after correction
O1	C-100	2026-03-05	2026-03-06	100	1 (North)	5 (South)
O3	C-100	2026-03-08	2026-03-20	30	1 (North)	5 (South)
O2	C-100	2026-03-12	2026-03-13	50	2 (South)	5 (South)
O4	C-200	2026-03-06	2026-03-07	70	3 (West)	3 (West)

Order O3 shows the late-fact rule at work: loaded on 20 March, after the move had been recorded, it still resolves to version 1 (North), because its order date of 8 March falls in the interval then believed to be North. Before the correction, revenue by region is North 130 (O1 and O3), South 50 (O2), West 70 (O4), total 250. After the correction, the move date of 1 March puts all three of C-100’s orders in South: North 0, South 180, West 70, total 250. The differences are North minus 130, South plus 130, West 0, and they sum to zero. This is a restatement that moved revenue between regions without creating or destroying any.

Because the superseded rows were kept, the question “what did the March report show on 20 March?” is answerable: join each order to the version whose valid interval contains its order date and whose recorded interval contains 20 March, considering only orders loaded by then. That query returns North 130, South 50, West 70, the numbers that were published at the time. The corrected view and the as-published view both exist, and the difference between them is exactly the restatement.

-- Reproduce what the warehouse reported as of a past date.
-- Only rows loaded by then, and only dimension rows believed at that time.
SELECT d.region, SUM(f.amount) AS revenue
FROM fact_order_line f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from AND f.order_date < d.valid_to
 AND d.recorded_at   <= DATE '2026-03-20'
 AND d.superseded_at >  DATE '2026-03-20'
WHERE f.loaded_at <= DATE '2026-03-20'
GROUP BY d.region;

The facts need the same treatment if they can change. The example assumes facts are never edited, so only their dimension keys move. If amounts can be corrected, apply the same principle: append a row with the delta and a reference to the original row, or keep fact versions with their own recorded interval, and do not overwrite a published amount in place without recording that you did.

Two ways to propagate a correction to facts

Once the dimension rows are corrected, facts must be attached to the right versions. There are two designs, and the choice is a trade-off between load cost and query cost.

Design	How it works	Advantage	Cost and failure mode
Stored version key, repointed	Facts store the surrogate key resolved at load; a correction runs an update that re-resolves keys for the affected members and date range	Queries are plain joins with no date logic	The update touches fact rows; it must be limited to the affected range, committed atomically, and rerunnable. Downstream aggregates built on the old keys must be rebuilt
Durable key and event date, resolved at query time	Facts store the durable key and event date; a view performs the interval join	A correction touches only the small dimension; no fact rows change	Every query pays for an interval join; the join must be proven to match exactly one version per fact; engines differ in how well they optimize range joins

The Kimball guidance for a retroactive change takes the first route, restating the fact rows. Either design works if the failure behaviors are controlled. For the first, the key repoint is the step to make idempotent: re-resolving a fact whose key is already correct changes nothing, so a rerun after a partial failure is safe. For the second, the main risk is that someone writes a query that joins on the durable key alone and reintroduces fan-out.

Restatement as a managed event

A restatement changes numbers people have already used. Applying it correctly in the tables is half the work; the other half is making the change visible and controllable. The glossary entry on delivery revision and restatement lists what a consumer needs: what changed and by how much, which periods, why, when it takes effect, who to ask, and what the consumer must do, such as rerunning reports or refreshing caches. Each of these needs data in the warehouse, so build it into the correction process, not into an email afterward.

• Compute the delta before publishing. Run the reproduce-as-published query and the corrected query side by side and store the difference by period and attribute. In the example, North minus 130 and South plus 130 is the delta, and the grand-total difference of zero is the evidence that the correction was an attribute move.
• Recompute dependent aggregates. The Kimball description of type 1 warns that aggregate fact tables and cubes affected by an overwrite must be recomputed. The same applies to any summary built on keys or attributes that a correction changed.
• Keep a durable record of restatements. A small table with the correction time, affected members, affected date range, count of facts re-pointed, delta, reason category (source error, late arrival, or changed definition), and the approver lets someone answer, months later, whether a period was ever restated. A chat message is not that record.
• Separate data corrections from definition changes. A corrected move date changes data under an unchanged definition. Redefining what a region means changes the rule. The second needs its own approval and versioning, a topic the next post takes up for metrics.

Whether to restate at all is a decision rather than a default. Trend analysis usually wants history restated so that periods compare on one basis. A figure reported to an outside party may need to stay as published, with the correction recorded as an adjustment in the period when it was discovered. Because the bitemporal columns retain both versions, the model does not force that choice; it lets each report pick the clock deliberately.

Reconciliation: proving the model and the restatement

Reconciliation compares data with an agreed reference and investigates differences. For a dimensional model there are four useful layers, each catching a different class of error, and each stating what it does not prove.

Layer	Question	Check	What a pass does not show
Source to fact	Did every source event arrive once?	Per-period counts and totals compared with the source at the same cutoff, followed by key-level comparison when totals differ	Equal counts can hide a missing record offset by an unexpected one; an error shared with the source stays invisible
Fact to dimension	Does each fact resolve to exactly one dimension version?	Match count per fact key equals one; unmatched facts listed	That the resolved version is the right one, only that it is unique
Dimension integrity	Are history rows well formed?	One current row per member; no gaps, overlaps, or reversed intervals; no member without history	That the attribute values themselves are true
Restatement	Did the correction change only what was intended?	Grand total unchanged for additive measures; per-attribute deltas sum to zero; only facts in the corrected range have new keys	That the correction input was itself accurate

The source cutoff matters in the first layer. Orders loaded after the source extract was taken are not discrepancies, and comparing a warehouse total taken at 09:00 with a source total taken at 08:00 produces false alarms. Align the cutoff, the time zone, the currency, and the treatment of refunds before comparing, and record any tolerance with the reason it is acceptable.

The second and third layers are queries that should return no rows. The per-fact match count is the check that exposes both missing history and fan-out, which a row count cannot:

-- Layer 2: every fact must resolve to exactly one current version.
SELECT f.order_id, COUNT(d.customer_key) AS matches
FROM fact_order_line f
LEFT JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from AND f.order_date < d.valid_to
 AND d.superseded_at = DATE '9999-12-31'
GROUP BY f.order_id
HAVING COUNT(d.customer_key) <> 1;

-- Layer 3: members with more than one current row, or with overlapping intervals.
SELECT a.customer_id, a.customer_key AS key_a, b.customer_key AS key_b
FROM dim_customer a
JOIN dim_customer b
  ON a.customer_id = b.customer_id
 AND a.customer_key < b.customer_key
 AND a.superseded_at = DATE '9999-12-31'
 AND b.superseded_at = DATE '9999-12-31'
 AND a.valid_from < b.valid_to
 AND b.valid_from < a.valid_to;

The second query lists any pair of rows for the same member that are both currently believed and whose valid intervals overlap. Adjacent intervals that share an endpoint do not overlap under the half-open rule, which is another reason to adopt it. Gaps need a separate check, comparing each row’s end with the next row’s start in valid-time order.

The fourth layer is the one specific to restatement. For the worked example, the check is that total revenue is 250 before and after, and that the per-region deltas are minus 130, plus 130, and 0. If the total moved, something other than re-attribution happened: a fan-out, a dropped fact, or a late fact included in only one of the two runs. This identity only holds for additive measures and attribute-only corrections. A correction that changes the amount of a fact, or a late fact that legitimately adds revenue to a published period, changes the grand total by a known, explainable amount, which the delta record should state.

Reconciliation detects differences; it does not repair them. A discrepancy is evidence to investigate, and the repair is a separate decision with its own hazards: reversing an effect already acted on downstream is not the same as editing a row.

Failure modes and limits

Failure	Symptom	Cause	Control
Totals inflate after a dimension change	Revenue higher than the source, often by the revenue of members that changed	Join on natural or durable key alone to a type 2 dimension	Per-fact match count equals one; views that hide the raw join
Facts disappear	Totals lower than the source	Inner join drops facts with no matching version or no dimension row	Unknown member row; list unmatched facts in reconciliation
Report cannot be reproduced	Cannot say what an earlier report contained	Corrections overwrote or deleted rows; only one clock is stored	Record both valid and recorded intervals; never edit attribute values on a published version
Double versions on the change date	Facts on the boundary day appear twice	Closed intervals, or end not equal to next start	Half-open intervals; overlap check
History lost for good	No way to attribute older facts	Dimension overwritten; source kept no history; snapshot table rebuilt	Capture before overwrite; protect the history table from rebuild; accept the loss explicitly for attributes where history was not needed
Intermediate states missing	A change and its reversal never appear	Capture by periodic comparison rather than by change log	Use log-based capture where intermediate states matter; state the capture resolution in the model’s documentation
Placeholders never resolved	Growing “Unknown” group in reports	Late dimension data never arrived or the overwrite step failed	Flag, count, and age placeholders; alert on a timeout
Stale aggregates after correction	Summary tables disagree with detail	Aggregates built on old keys or attributes were not rebuilt	Rebuild dependents as part of the restatement; reconcile aggregate to detail

Two broader limits are worth stating. A dimensional model attributes facts to versions of context, but it does not decide which context is true; a wrong source value is faithfully modeled. And bitemporal history adds columns, rules, and query complexity. For attributes where nobody will ever ask what was believed when, a type 1 overwrite with a recorded correction note is a reasonable and much simpler choice. Decide per attribute using the questions people actually ask.

Choosing the technique for each question

The business asks	Model choice	Because
Sales by the region the customer was in at the time	Type 2 with the surrogate key in the fact	The fact is permanently attached to the version in effect
Sales by the region the customer is in now	Type 7 as-is view, or type 6 current column	Uses the durable key and current rows without losing history
Only the current value matters, or the change is a correction	Type 1	No history to maintain; recompute dependents
What the March report showed on 20 March	Bitemporal dimension and a loaded timestamp on facts	Needs both what was true and what was believed
Distinct customers over a year	Count durable keys	Surrogate keys count versions, natural keys may count one person twice
A balance at month end	Periodic snapshot fact, point-in-time aggregation	The balance is semi-additive across time

With grain declared, keys assigned, history captured, and corrections applied through closed and inserted rows rather than overwrites, the opening puzzle has an answer. The 12 April and 26 April tables differ because a source correction reached the warehouse between them, the model recorded both beliefs, the delta can be listed by region, and the grand total is unchanged. That is the difference between a restatement and an unexplained change. The next post, Semantic Layers and Consistent Metric Definitions, builds on this model by defining the metrics that run over it, so that every tool computes a number like “March revenue” the same way.

References

• Kimball Group, Grain
• Kimball Group, Four-Step Dimensional Design Process
• Kimball Group, Transaction Fact Tables
• Kimball Group, Periodic Snapshot Fact Tables
• Kimball Group, Accumulating Snapshot Fact Tables
• Kimball Group, Additive, Semi-Additive, and Non-Additive Facts
• Kimball Group, Dimension Surrogate Keys
• Kimball Group, Natural, Durable, and Supernatural Keys
• Kimball Group, Degenerate Dimensions
• Kimball Group, Type 0: Retain Original
• Kimball Group, Type 1: Overwrite
• Kimball Group, Type 2: Add New Row
• Kimball Group, Type 3: Add New Attribute
• Kimball Group, Type 4: Add Mini-Dimension
• Kimball Group, Type 6: Add Type 1 Attributes to Type 2 Dimension
• Kimball Group, Type 7: Dual Type 1 and Type 2 Dimensions
• Kimball Group, Late Arriving Facts
• Kimball Group, Late Arriving Dimensions
• Martin Fowler, Bitemporal History
• dbt Documentation, Snapshots (read on 3 October 2026)