Bitemporal Dimension History: Effective Time and Assertion Time

A customer’s region was Oslo from January to June, then Bergen. That much is the world. The warehouse learned of the change on the fourteenth of June, and on the twentieth of July a data steward discovered that the change had actually occurred on the twenty-eighth of May and corrected the record. The world did not change on the twentieth of July; the warehouse’s belief about the world did.

A single validity interval can represent either the world’s timeline or the warehouse’s timeline, not both. Every type 2 dimension that accepts retroactive corrections is implicitly choosing one and losing the other, usually without saying which.

The two axes

The effective axis records when an attribute value was true in the world. It is the axis that “as of 3 March, what region was this customer in” queries. Its values come from the source’s own timestamps: a change record’s effective date, an order’s placement time, an account’s closure date.

The assertion axis records when the warehouse came to believe a version of the facts. It is the axis that “what did the report say on 1 July” queries, and it is the axis that audit and restatement questions depend on. Its values come from load timestamps, and they are always known exactly, because the warehouse writes them.

A bitemporal table carries both, as two half-open intervals per row. The effective interval is (valid_from, valid_to), and the assertion interval is (asserted_from, asserted_to) — sometimes named transaction_from/transaction_to in the temporal-database literature, following Snodgrass.

A row is then a statement: “between effective time A and B, the attribute had value V, and the warehouse asserted this between assertion times C and D”. A correction that changes only the belief closes the assertion interval of the old row and opens a new row with the same effective interval and the corrected value. The effective history is unchanged; the belief history records that the earlier assertion was superseded.

Why one interval is not enough

Without the assertion axis, a correction has only one place to go, and every option loses something.

Overwrite the effective row. The old value disappears. A report run before the correction cannot be reproduced, because the row it read no longer exists. This is what a type 1 correction inside a type 2 table does, and it converts the dimension from a history into a current-best-estimate with a plausible-looking interval.

Close the effective interval early and insert a new version. The effective history now records a change at the correction date, which is false. Facts between the true change date and the correction date resolve to the wrong version, and the error is invisible because the intervals are contiguous and well formed.

Append a corrected row with an overlapping effective interval. The effective history now has two rows claiming the same instant with different values. The as-of query returns two rows or an arbitrary one, depending on the engine’s handling of ties, and the dimension has lost the property that made it queryable.

The assertion axis gives the correction a home that does not disturb the effective timeline: the old row stays, with its assertion interval closed at the moment of correction; the new row carries the corrected effective interval and an open assertion interval starting at the correction. A query as of the earlier assertion time reads the old row; a query as of the current assertion time reads the new one.

Keys and the correction

The surrogate key must be unique per row, and in a bitemporal table a row is a (effective interval, assertion interval) pair. A correction therefore produces a new row and a new key, even though the effective interval may be identical to the row it supersedes.

That is the point at which the pattern collides with the fact table. A fact loaded before the correction points at the superseded row. After the correction, the fact’s effective time falls in the corrected row’s interval, and the correct join would find the corrected row. But the fact’s stored key still points at the old one, and the old row is now marked as no longer asserted.

Three resolutions exist, and the choice is architectural rather than technical:

Resolve at query time. The fact table stores the natural key and the event timestamp, and every query joins through the effective interval restricted to the desired assertion time. Correct under any number of corrections, at the cost of a range join on every query and a filter that every report must remember to apply. The join predicate becomes two interval comparisons:

SELECT f.order_id, d.region, SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer_bitemporal d
  ON d.customer_id = f.customer_id
 AND f.ordered_at >= d.valid_from
 AND f.ordered_at <  d.valid_to
 AND :as_of      >= d.asserted_from
 AND :as_of      <  d.asserted_to
GROUP BY f.order_id, d.region;

Resolve at load time and rewrite on correction. The fact stores the surrogate key, and a correction triggers a backfill of the affected fact rows. Correct, expensive, and it makes the fact table mutable in a way that accumulating snapshots already are but transaction tables are not.

Resolve at load time and never correct. The fact stores the key and the correction is recorded only in the dimension. Queries answer “what the warehouse believed at load time”, which is a coherent question, but the dimension’s two axes then disagree with the facts’ single axis and the disagreement is permanent.

The first is the only option that keeps facts immutable and history correct. Its cost is concentrated in query complexity rather than in data volume, which is usually the better trade, but it requires every consumer to know about the assertion filter — and a consumer that forgets it reads every superseded row, which produces duplicated facts rather than wrong attributes. That failure mode is loud if the duplicates are counted and silent if the query aggregates with a SUM over both versions of a fact, which is the common case.

Closing intervals without gaps

The invariant for each axis is that the intervals for a given entity partition time with no overlaps and no gaps. On the assertion axis this is maintained by the warehouse itself: a new assertion closes the previous open assertion at the load timestamp, and the two meet exactly. The write must be atomic with the insert, or a crash between the two leaves either an open pair of assertions or a gap.

On the effective axis the invariant is maintained only if the corrections themselves are consistent. A correction that says “the change was effective on 28 May” must also close the previous version at 28 May and any intervening version’s interval, which means the correction may rewrite more than one row’s effective bounds. A correction that moves the effective date later leaves a hole between the previous version’s end and the corrected version’s start; the previous version’s valid_to must be extended to fill it, or the hole becomes a period where no version is valid and facts in that period join to nothing.

The hole is the characteristic defect of retroactive effective-date correction, and it is detectable by a check that every entity’s effective intervals are contiguous and cover the span of its facts. That check is cheap to run per load and catches the defect before the reports do.

A subtler defect is the correction that changes an attribute’s value without changing its effective date. Then the effective intervals of old and new row are identical, and the two differ only on the assertion axis. That is legal and it is the reason the key must include the assertion interval: without it, the two rows collide on the effective key.

What bitemporality costs

The row count multiplies by the number of corrections. A dimension with a million versions and an average of one correction per version has two million rows. A source that restates frequently, such as a financial classification that is revised quarterly for a rolling window, can multiply by a much larger factor.

The query cost multiplies too, because the effective-interval join is already a range join and the assertion filter adds two more comparisons. On a large fact table this is the difference between a hash join and a sort-merge or a nested loop with an index range scan per row, and the plan may not be stable across data volumes.

The maintenance cost is the highest of the three. Every correction is an insert plus a close plus, often, an effective-interval adjustment on a neighbouring row, and all of it must be atomic. The loader is no longer a merge; it is a small transaction with an invariant to preserve.

Those costs are justified when the questions are genuinely temporal on both axes: financial restatements, regulated reporting where the as-of-the-report-date view must be reproducible, and any dimension whose attributes are themselves the subject of disputes. They are not justified when the only temporal question is “what was true then”, because a plain type 2 dimension answers that at a fraction of the complexity, and a plain type 1 answers “what is true now” at almost no complexity at all.

The middle case — corrections happen but the assertion history is not needed — is served by a type 2 dimension with an audit column recording the load that last touched each row. That is not bitemporal, because the superseded values are gone, but it records enough to answer “when did this row last change” and to identify rows that were corrected after their facts were loaded. The distinction between that and a full second axis is the distinction between knowing that a correction happened and being able to reconstruct what the table looked like before it.