One effective date cannot explain history

A contract can be effective August 1 but entered September 1. Overwriting dates silently changes published August reports, while updated_at cannot reconstruct business applicability.

valid_from/to model business truth; system_from/to model warehouse knowledge. Query either latest restatement or what was known at a historical publication.

Apply two clocks selectively

Contracts, prices, organization, policy, and corrected mappings often need bitemporal history. Immutable events may need only event and ingestion time.

Start from questions: event-time truth, knowledge-time audit, original release, or current restatement. Avoid complexity without a real requirement.

Declare half-open intervals

Use [from,to) to prevent duplicate boundary matches. Standardize timezone, precision, and infinity. Day-level policy must not imply second-level certainty.

At one system snapshot, valid intervals for a key should not overlap unless the domain explicitly permits multiple states.

Close system versions instead of overwriting

A late correction closes the current system interval and creates versions for affected valid segments. Store source, reason, actor, run, and approval.

Database temporal support captures system history but does not automatically implement business-valid interval splitting.

Name the temporal view used by a fact join

Event facts join the dimension by event time for latest-restated analysis. Reproducing a release also limits system time to the release cutoff.

Publish explicit as_was, as_is, and restated semantic views rather than asking report authors to invent range joins.

Govern late change, withdrawal, and deletion

Whether a closed period is restated is a business policy. Persist detection, approval, and restatement IDs.

Business withdrawal, correction, and privacy deletion have different semantics. Privacy action may intentionally reduce replay fidelity.

Snapshot releases and explain differences

Each release retains release ID, system cutoff, data watermark, and policy version. Old reports default to original release with an optional clearly labeled restatement.

Categorize differences as late fact, valid-period correction, mapping, or calculation policy.

Optimize without discarding meaning

Index keys and intervals, partition versions, materialize current state and popular cutoffs, but retain the authoritative history.

Test boundary and snapshot equivalence after optimization. A current-only flag is not bitemporal history.

Accept with timeline edge cases

Test retroactive, future, repeated same-day, adjacent boundaries, timezone, withdrawal, late fact, and privacy deletion.

Draw known timelines and verify point queries. Amounts should conserve across interval splitting unless the correction explicitly changes value.

Standardize query and release operations

Provide tested templates for current-known, valid-time restated, historical-known, and release-difference queries. Fail when a key resolves multiple rows rather than hiding overlap with an arbitrary row number.

Use atomic release IDs in caches, APIs, exports, and catalogs. Monitor overlap, missing matches, late corrections, and restatement across closed periods.

BuildTable.ai boundary

Pilot one audit-sensitive entity, freeze interval rules, replay history, and publish original and restated views.

BuildTable.ai may be evaluated for temporal tables and result views. Automatic splitting, release snapshots, regulatory deletion, scheduling, and approval require verification.

A contract was effective last month but entered today. Should historical reports change?