The Gold Standard of Information · Specialist guide
How to preserve changing facts, reconstruct what was believed at a past moment, and answer historical questions without quietly rewriting the record.
The central test is transfer: can the method survive a changed setting, changed evidence and changed consequence without losing its logic?
Chapter contents
Foundations
Model and method
Implementation
Chapter 1 of 12
The question comes before the table
Historical data work begins with a question that ordinary current-state tables cannot answer. A row that says a learner belongs to programme B today does not tell us which programme was shown on a report issued six months ago, which programme was valid on the assessment date, or when the organisation learned that an earlier entry was wrong. Those are different questions. If they are collapsed into one “last updated” field, later users can obtain precise-looking answers that are historically false.
A slowly changing dimension is a modelling pattern for attributes that change less frequently than transactional events but still matter to interpretation: address, customer tier, school, product category, account manager or regulatory classification. The gold standard is not to keep every change merely because storage is cheap. It is to preserve the changes needed to explain decisions, join facts to the right context and reconstruct a defensible past.
Write the intended question in ordinary language before designing columns. “Show the current category” points toward overwrite or a current view. “Reproduce the category used when this invoice was assessed” requires history and a clear time basis. “Show what the system believed on 1 June about the category valid on 15 May” requires two time axes.
Practical check
- Current-state question: what value should a user see now?
- Valid-time question: during which real-world period did the value apply?
- System-time question: during which period did the database store this version?
- Decision-time question: which version was actually available to the process that acted?
Chapter 2 of 12
Four patterns, not a ladder of maturity
The familiar Type 0, Type 1, Type 2 and Type 3 labels describe different policies, not stages through which every dataset should progress. Type 0 treats an attribute as fixed. Type 1 overwrites the old value and keeps only the corrected or latest value. Type 2 creates a new versioned row for each relevant change. Type 3 keeps limited previous values in extra columns. Hybrid designs are common because different attributes have different historical value.
Type 1 is appropriate when the prior value has no legitimate analytical meaning, such as correcting a harmless spelling error. It is dangerous when an overwrite changes the interpretation of already recorded facts. Type 2 is appropriate when reports, decisions or comparisons must use the attribute that applied at a particular time. It costs more because keys, intervals, late data and corrections must be governed.
Do not label a whole dimension “Type 2” and assume the work is done. Decide at attribute level which changes create a new version, which are in-place corrections, which are ignored and which trigger a review. A customer’s marketing segment may require history while a display-capitalisation change does not.
| Pattern | What survives | Best fit | Main risk |
|---|---|---|---|
| Type 0 | Original value | Truly immutable attribute | Real changes are suppressed |
| Type 1 | Latest value | Correction or current truth | Past reports silently change |
| Type 2 | Versioned rows | Reconstruction and time-aware joins | Interval and key defects |
| Type 3 | Limited previous value | Small, known comparison need | History depth is fixed |
Chapter 3 of 12
Identity stays stable while versions change
A Type 2 table usually carries two kinds of identity. A durable business identity says which real-world entity the versions describe. A surrogate row key identifies one particular version. Facts that must preserve historical context join to the version key or use an as-of rule to locate it. Current applications may instead resolve the durable identity to the current version.
Suppose customer C104 moves from Jurong East to Clementi and later changes membership tier. The customer is still C104, but the dimension may contain three rows with three surrogate keys. If an order fact is rewritten to point at the newest row, the historical report no longer shows the context present when the order was accepted. If a changed business identifier is mistaken for a new entity, the history fragments.
Identity resolution therefore precedes versioning. Define the natural identifier, matching rules, merge and split policy, and what happens when an upstream system reuses identifiers. Slowly changing dimensions preserve change only after the system knows what is changing.
Practical check
- One durable entity identifier across versions
- One unique surrogate key per stored version
- Explicit merge, split and identifier-reuse rules
- Facts protected from accidental re-keying to the current row
Chapter 4 of 12
Intervals are a contract
A Type 2 row normally has an effective start and end, or a start plus an open-ended marker. Choose one interval convention and enforce it. Half-open intervals, written [start, end), are usually easiest: the start is included and the end is excluded. When one version ends at 2026-04-01 and the next starts at 2026-04-01, there is neither overlap nor ambiguity.
Inclusive end dates look friendly but become awkward when timestamps, time zones or sub-day changes appear. A sentinel such as 9999-12-31 can simplify some tools but must be documented and handled consistently. A separate current flag can speed common access, yet it is derived state and can disagree with the dates unless checked.
For each durable entity, require at most one current row and no overlapping valid intervals. Decide whether gaps are allowed. A gap can mean “unknown”, “not applicable” or a load defect; those meanings should not be conflated.
Practical check
- Use one time zone and precision policy
- Enforce start < end for closed versions
- Reject or quarantine overlapping versions
- Define whether gaps mean absence, uncertainty or error
- Derive current state from dates, or continuously reconcile any current flag
Chapter 5 of 12
Worked example: reconstructing a decision
A scholarship team records learner L27 in school A from 1 January, then school B from 15 March. The change is loaded on 18 March. On 10 April the source reports that the transfer actually took effect on 12 March. A current-state table can show only school B. A single Type 2 timeline can show A until 12 March and B afterwards, but it cannot by itself show that the system believed 15 March was the boundary between 18 March and 10 April.
If the audit question is “which school was valid on 14 March?”, the corrected valid-time answer is B. If the question is “what would an analyst running the report on 20 March have seen?”, the answer may be A because the correction was not yet known. Reconstructing both requires valid time and system time, sometimes called a bitemporal design.
The design stores a version for the assertion “B valid from 15 March” during the system-time period when that assertion was believed, then closes it when the correction arrives. A corrected assertion “B valid from 12 March” is inserted with a new system-time start. Nothing is erased; the record distinguishes real-world validity from database knowledge.
| Question | Time predicate | Answer in the example |
|---|---|---|
| What is valid now? | Current valid interval | School B |
| What was valid on 14 March? | Valid time as of 14 March | School B after correction |
| What did we store on 20 March? | System time as of 20 March | Boundary at 15 March |
| What did we believe on 20 March about 14 March? | Both time axes | School A |
Chapter 6 of 12
As-of joins make history usable
An as-of join attaches each fact to the dimension version whose interval contains the fact’s relevant timestamp. In a half-open model, the predicate is dimension.start <= fact.time and fact.time < dimension.end. The relevant timestamp might be order acceptance, service delivery, assessment, settlement or another business event. Ingestion time is not a substitute unless the question is explicitly about system arrival.
The join must return exactly one applicable version when the domain expects complete coverage. Zero matches reveal gaps, identity failure or facts that precede known history. Multiple matches reveal overlapping intervals or an underspecified key. Hiding these outcomes with DISTINCT, MAX or a “latest row” shortcut converts a data-quality failure into a plausible but untrustworthy answer.
Many platforms offer temporal query syntax that can reconstruct table state at a system time. That feature is valuable, but it does not choose the business timestamp or repair a weak validity model. Query syntax cannot resolve a semantic question the design never recorded.
Practical check
- Name the fact timestamp used by the join
- Test zero-match and multi-match rates
- Keep boundary cases in automated tests
- Explain whether the result is valid-time, system-time or bitemporal
- Record the query and data snapshot for high-stakes reproductions
Chapter 7 of 12
Late-arriving changes and early-arriving facts
History rarely arrives in order. A change effective last week may be delivered today. A fact may arrive before its dimension entity. A replay may send an older message after a newer one. The load process therefore needs an ordering rule based on effective time, source version, event identifier and, when necessary, ingestion sequence.
When a late change splits an existing interval, close the earlier version at the late change’s effective start, insert the new version, and repair the following boundary without overwriting unrelated history. If facts store a version surrogate key, decide whether they should be restated to the newly discovered historical version. Financial or regulated reporting may require a controlled restatement; operational dashboards may retain the originally available context and publish a correction separately.
For early-arriving facts, a provisional “unknown” member can preserve referential integrity, but it must not become permanent camouflage. Track provisional use, reconcile it when the entity arrives and distinguish “not yet known” from genuinely missing classification.
Practical check
- Idempotent event or batch identifier
- Deterministic conflict rule for duplicate changes
- Quarantine path for impossible time sequences
- Documented restatement policy
- Metrics for provisional and unresolved members
Chapter 8 of 12
Corrections are different from changes
A change says the world became different. A correction says an earlier record was wrong. Treating every correction as a new current change preserves the error in the valid-time story; overwriting every error destroys evidence of what the system previously believed. The correct handling depends on which audit questions matter.
For a spelling correction with no analytical consequence, Type 1 may be sufficient. For a retroactive eligibility correction, close the obsolete system-time assertion and add the corrected one while retaining the old assertion for audit. Capture a correction reason, source, operator or process, and an immutable load identifier so reviewers can trace why the history changed.
Never infer intent solely from an older effective date. Some sources legitimately send backdated real-world changes; others send corrections. If the source cannot distinguish them, the uncertainty is part of the information and should trigger a review or an explicit default policy.
Practical check
- Change, correction, cancellation and merge are separate operations
- Reason codes describe why history was revised
- Restatement scope is approved before reports are changed
- Original assertions remain discoverable when accountability requires them
Chapter 9 of 12
Validation turns rows into evidence
A successful load is not evidence that history is coherent. Validate structural invariants after every batch and publish their results. For each entity, check unique surrogate keys, non-overlapping intervals, valid boundaries, permitted gaps, one current version and stable required attributes. Reconcile inserted, closed, corrected and rejected counts to the source.
Then test meaning. Sample a change chain and narrate it in ordinary language. Re-run representative as-of queries at boundaries, just before and after a correction, and across time-zone transitions. Compare an important historical report with its archived output. These checks catch semantic errors that column constraints cannot see.
Evidence should be reviewable: batch identifier, source extract or manifest, rule version, validation results, exception disposition and query used for reconstruction. A dashboard without retained evidence can show that today’s run looks healthy but may not explain last quarter’s answer.
Practical check
- Zero overlapping intervals by durable entity
- Expected count of current versions
- As-of join cardinality of exactly one where required
- Traceable exceptions, not silent drops
- Reproducible sample from source event to historical report
Chapter 10 of 12
Performance without corrupting meaning
Historical tables grow faster than current-state tables, and as-of predicates can be expensive. Optimisation should preserve the temporal contract. Index the durable entity and interval boundaries used by queries. Partition or cluster by time when it matches access patterns. Maintain a current-state view or materialisation for common operational reads rather than weakening the history table.
Do not remove closed versions merely because most users want the present. Apply a retention policy tied to legal, analytical and operational needs, and preserve the evidence required for reports already issued. If old history is moved to colder storage, test that authorised reconstruction still works and record the archive boundary.
Precomputed snapshots can speed recurring month-end reporting, but they are derived products. Keep the rule and source history that created them. A snapshot alone cannot answer a new date or reveal a later correction unless its lineage is intact.
Practical check
- Index for actual as-of predicates
- Separate current convenience views from historical truth
- Test archive retrieval and reconstruction
- Retain derivation metadata for snapshots
- Measure query latency and semantic error separately
Chapter 11 of 12
Misconceptions and design limits
A Type 2 table is not automatically an audit log. It may preserve business-effective versions while omitting who made a change, the original request, the system-time sequence or deleted assertions. A database system-versioned table is not automatically a valid-time model. It records when rows existed in the database, not necessarily when their claims applied in the world.
More history is not always better. Versioning every cosmetic edit creates noise, storage cost and difficult joins. Conversely, a current flag plus last-updated timestamp is not meaningful history when past state must be reconstructed. Choose the least complex model that answers the defined questions, then protect its invariants.
The model also cannot create certainty absent from the source. If a learner’s transfer date is disputed, store the claim, provenance and confidence or review status; do not convert uncertainty into a crisp date merely to satisfy a schema.
Practical check
- Type 2 history is not the same as change-data capture
- System time is not the same as valid time
- A current flag does not replace interval checks
- Temporal precision does not prove source accuracy
- Bitemporal complexity is justified only by real reconstruction needs
Chapter 12 of 12
Transfer check: can the model survive a new context?
Move the method from customer data to a curriculum catalogue. A course changes department, credit value and prerequisite rules. Ask which attributes need current truth, which must be reconstructed for a student’s enrolment date, and which corrections should revise earlier claims. Choose the event timestamp used to join an enrolment to the catalogue.
Now change the conditions: the prerequisite correction arrives two months late; the department name changes cosmetically; a student appeals using the rule that staff could see at the time. If the design answers only the first question, it is not yet transferable. Add system time or retained decision snapshots only where the appeal requirement makes them necessary.
A final evidence check should let another analyst reproduce one historical answer without private knowledge. They should identify the entity, time basis, interval convention, version-selection rule, correction history, source and validation result. If they cannot, the system may store history but still fail to communicate it.
Practical check
- State the historical question in one sentence
- Name the entity and both kinds of key
- Choose the relevant event time
- Test boundaries, late changes and corrections
- Show the evidence needed to reproduce one answer
Advanced cases and boundary conditions
When attributes change independently
A wide Type 2 dimension creates a new row whenever any tracked attribute changes. If address, risk tier and account manager change at different rates, version counts can explode and unrelated histories become coupled. Consider splitting attributes into coherent subdimensions or satellite-style structures, but preserve an understandable reconstruction path.
The trade-off is join complexity. A narrower model is not automatically better if every report must perform several temporal joins with slightly different clocks. Choose boundaries by business meaning and query need, then test end-to-end reconstruction.
Deletes, closures and reactivation
A source deletion may mean correction, legal erasure, entity closure or transmission error. Do not translate it automatically into a physical delete. Represent closure with effective dates where history must remain, and route legally required erasure through a governed process that records non-sensitive evidence of compliance.
Reactivation needs policy too. Decide whether it resumes the same durable identity, creates a new lifecycle or requires review. Identifier reuse can otherwise connect a new entity to an old history.
Time zones and precision
A date-only effective value may be sufficient for monthly categories but dangerous for intraday pricing or eligibility. Store the source precision and normalise time zones without pretending greater precision than the source supplied. A midnight boundary needs a named zone.
When systems disagree on precision, define deterministic ordering and surface ties. Hidden truncation can create overlaps or move facts across a boundary even though every individual timestamp appears valid.
A reviewer’s reconstruction drill
Select one changed entity and one fact. Ask a reviewer unfamiliar with the pipeline to recover current value, valid value at the fact time, system belief at an earlier date and the reason for the latest correction. Record each query and result.
Any answer that depends on oral explanation reveals missing metadata or ambiguous policy. Repair the contract, not merely the demonstration.
Field workbook: from concept to accountable practice
Use this section to turn the chapter method into a reviewable piece of work. Each prompt produces evidence that another person can inspect, question and transfer to a changed context.
A minimum history specification
Write a short contract for each historical attribute: business meaning, stable entity key, change source, valid-time precision, whether corrections are distinguishable, and how long history is retained. Add the exact as-of questions the dataset promises to answer. This prevents downstream teams from assuming that a versioned table supports questions it was never designed to resolve.
Turn the contract into examples at the boundaries. Show the value one instant before a change, at the change time and one instant after. Show a late correction and a disputed date. Reviewers should be able to predict the returned row before reading SQL.
Loading a Type 2 change safely
A controlled load first resolves the durable entity, rejects ambiguous identity, compares tracked attributes and classifies the input as no change, new change, correction or duplicate. Only then does it close or split an interval and insert a version. Wrap related changes in a transaction or another atomic mechanism so readers never see two current rows or a temporary gap.
Idempotency is essential because pipelines retry. A source event identifier, source version or deterministic fingerprint can prevent the same change from creating repeated versions. A rerun should produce the same history and validation counts, not a longer table.
Designing an as-of test set
Build a small fixture containing adjacent intervals, a deliberate gap, an overlap that should be rejected, a late event, a retroactive correction, a daylight-saving or time-zone boundary where relevant, and a fact before known history. Tests should state whether each case returns one row, no row or an error.
Add bitemporal cases only if the contract includes system-time reconstruction. Test the same valid date from two different system dates and confirm that the answer changes when knowledge changed. This makes the second time axis visible rather than ceremonial.
Report restatement and lineage
When corrected history changes a published figure, decide whether to restate, annotate or preserve the original publication. Record the report version, extraction time, rule version and dimension snapshot or reproducible system-time query. Readers need to know whether two different numbers represent error, correction or different knowledge dates.
A lineage record should connect source assertion, transformation, version row, fact join and report. It need not expose private data, but it should allow an authorised reviewer to follow one sampled value end to end.
Operational ownership
Historical quality crosses teams. Source owners know what a change means; data engineers protect keys and intervals; analysts define as-of use; governance owners decide retention and correction; report owners handle restatement. Assign each decision instead of leaving the database administrator to infer policy from malformed inputs.
Create service measures that matter: unresolved identity matches, late-change age, overlap rejections, provisional-member duration, restated reports and as-of join failures. Row count alone says little about historical trust.
Practice task: rebuild a price list
Model a product whose list price changes on 1 January and 1 March, then receives a correction saying the March price should have begun on 20 February. Produce the current view, valid-time history and, if required, the system-time sequence. Join orders on 19 February, 20 February and 1 March using the business event time.
Change the context so a tax rate rather than a price is corrected. Ask whether previously issued invoices should be restated or preserved as originally calculated. The storage pattern may be similar, but governance and remedy differ.
Decision card
Before approving a design, answer seven questions: What past question must be reproduced? Which identity survives changes? What clock defines validity? What clock records knowledge? Which attributes create versions? How are corrections and late events handled? What evidence proves interval integrity?
If any answer is “whatever the pipeline currently does”, the design is not ready. Historical state is a public promise about meaning, not a side effect of update code.
Reading routes across the eduKate ecosystem
Begin with The Gold Standard of Information. Place this guide inside The Gold Standard of Education: Learning Transfers into Life, then use the How X Works hub to compare methods across domains.
Related specialist guides
Frequently asked questions
Should every dimension use Type 2?
No. Use it only for attributes whose past values materially affect interpretation, accountability or analysis. Type 1 is often better for harmless corrections and present-only needs.
What is the difference between valid time and system time?
Valid time describes when a claim applies in the represented world. System time describes when that version existed in the database. They answer different historical questions.
Can I join facts to the current row and filter by date later?
Usually not. That first step has already attached the wrong context. Use the fact’s relevant business timestamp to locate the applicable dimension interval.
How should end dates be represented?
Half-open intervals are usually simplest: include the start and exclude the end. Whatever convention is chosen must be documented and tested at boundaries.
What if a late correction changes an already published report?
Follow a defined restatement policy. Preserve the original evidence, record the correction and make the revised status visible rather than silently replacing the past.
Do temporal database features solve the whole problem?
They help preserve and query system history, but they do not choose valid-time semantics, identity rules, correction policy or the business timestamp used by an as-of join.
Authoritative sources
- Microsoft Learn: Temporal table usage scenarios
- Microsoft Learn: Query data in a system-versioned temporal table
- Microsoft Learn: Slowly changing dimension Type 2
Source links support the technical and institutional foundations of this guide. The worked examples and transfer checks are original teaching constructions; they are not reports of real people or cases.