Historical Attribution in AI Analytics: Slowly Changing Dimensions, Corrections and Temporal Join Validation

Three legitimate questions need different attribution rules

One question asks how much revenue customers assigned to East China at the time of each transaction contributed. Another asks how much last year's revenue came from customers assigned to East China today. Both can be useful, but they support different decisions: assessing historical responsibility and planning for the current customer portfolio.

A third question asks what East China revenue was known to be when the previous monthly report was released. Answering it requires not only business-effective time but also when information entered the system and when it was corrected. A later discovery of a retroactive transfer should not silently change the original evidence behind an already released report.

The metric contract for AI analytics should declare the fact date, attribution time and revision basis. Invoice, receipt and order dates are not interchangeable. Even transaction-time attribution must specify whether it uses the latest corrected history or a preserved published version. When the question is ambiguous, offer explicit alternatives rather than letting the model conceal its choice.

This article assumes a customer belongs to exactly one region at any business instant. Joint responsibility or proportional allocation requires a separate relationship and weighting model. Do not treat legitimate multiple ownership as duplicate data or expect slowly changing dimensions to replace allocation rules.

A slowly changing dimension stores versions, not duplicate customers

Slowly changing dimensions, or SCDs, manage changes to attributes of entities such as customers and employees. Microsoft's star-schema guidance distinguishes Type 1, which overwrites values, from Type 2, which retains versions and supports historical joins using version identifiers and validity periods.[1] Decide which fields need history according to business semantics; not every telephone-number correction needs a new commercial ownership version.

Consider customer C17. Version V1 belongs to East China from January 1, 2026 until, but not including, July 1. Version V2 belongs to North China from July 1 onward. C17 remains the stable customer business key; V1 and V2 are distinct version keys. The chosen fact time determines which version an old order should reference.

Suppose an order on June 30 is worth 100 yuan and another on July 1 is worth 200 yuan. Transaction-time ownership produces East China 100 and North China 200. Recalculation using current ownership produces North China 300. Both totals are 300, so conservation of the overall amount alone cannot detect an attribution error.

Use a closed-open validity interval: include the start instant and exclude the end. At the July 1 boundary, only V2 then matches. Conditions inclusive at both ends can match adjacent versions simultaneously. Align time zones, timestamp precision and the source system's actual effective instant as well.

A minimum dimension record includes the customer business key, unique version key, attribution attributes, business-valid start and end, source and evidence for the change. An explicitly open end can represent the current version, but an absent end is not automatically valid. Distinguish continued validity from a missing closure record or a deleted entity.

Snapshots preserve observed changes, not history that was never captured

dbt snapshots retain Type 2 history for mutable tables. The timestamp and check strategies depend respectively on update times and changes to specified columns, and deletion handling needs explicit configuration.[2] Verify the actual version and adapter before implementation. A column named valid_from is not proof that its values represent business-effective time.

For example, a CRM may record a July 1 regional transfer only on July 3. The update timestamp describes entry or modification, while July 1 is the business-effective date. A collection system may first observe the change even later. Collapsing these three clocks into one column loses meaning needed for historical attribution or report reproduction.

dbt's selection guidance explicitly notes that scheduled snapshots capture values visible when they run, not intermediate changes overwritten between runs.[3] If an entity moves from A to B and then C between observations, a snapshot that sees only A and C provides no evidence for B's validity interval. Increasing frequency reduces the blind spot without guaranteeing complete capture.

When historical requirements are demanding, assess source change logs, change data capture (CDC) or events carrying business-valid periods. Check continuity, deletions and replay completeness. If only a current table is available, retain history from the earliest provable point and mark earlier ownership as unknown. Backfilling today's region across the past does not make it authentic history.

The costs differ. Periodic snapshots are straightforward to start but scanning and storage grow with the dataset. Complete change histories demand stronger integration and operations. A current view can serve a current customer list; a current-only table will generally not support auditing old reports.

Retroactive corrections separate business time from record time

Continue the constructed example. The system originally believed C17 moved to North China on July 1. On August 5, documented evidence establishes that the transfer was effective from June 15. Under the latest corrected transaction-time interpretation, the June 30 order for 100 yuan now belongs to North China. A report released in July may still have attributed it to East China.

Overwriting the start and end of V1 and V2 gives a revised interpretation of history but loses the explanation for the old report. Preserve both business-effective time and record versions: the former says when an attribution applies, the latter when the system adopted that interpretation. Depending on reproduction requirements, use bitemporal records or immutable input snapshots and version manifests for published reports.

To query the latest corrected history, select the currently accepted record version and then match transaction time to the business interval. To reproduce what was known before a report's information cutoff, first select the record version visible at that cutoff, then perform the business-time join. Neither filter substitutes for the other.

A correction should identify affected customers, dates, fact counts and changes in grouped amounts, together with its approval basis. Whether to restate published results is a business decision; pipelines should not silently overwrite external reports. If facts already contain fixed dimension-version keys, rebuild affected mappings or preserve report versions. Editing the dimension alone does not establish consistency across reports.

Bitemporal designs increase storage, query and explanation costs. For recent operational analysis that does not need every past state of knowledge, approved business history plus snapshots of important reports may suffice. Specify the questions to be answered before choosing the complexity.

Require a unique version for each fact and retain exceptions

First filter facts according to the metric's status, currency, permissions and fact date. Choose the attribution mode and revision cutoff, then find dimension versions by customer business key and valid time. Before producing the joined result, count matches per unique fact: one is a normal candidate, zero indicates missing history, and more than one indicates conflicting intervals or undefined multiple ownership.

An inner join must not silently discard unmatched facts. Depending on business requirements, place them in an explicit unknown-attribution group or block certified output. Do not hide multiple matches by choosing an arbitrary or latest row; preserve the conflicting records and stop the affected certification path. When using an unknown group, include it in total reconciliation and separately report its amount and reasons.

PostgreSQL 18 documents range exclusion constraints that can prevent overlapping intervals for the same identifier, subject to database configuration and field types.[4] Such constraints do not establish continuous coverage, correct provenance or trustworthy effective dates. A bitemporal implementation must also scope constraints to record versions so that legitimate historical corrections are not rejected as overlaps.

Persisting fact-to-dimension-version mappings can reduce the cost of frequent queries, but requires handling late facts and retroactive corrections. Joining by time at query execution is more flexible but adds computational work. Both approaches should record data cutoffs and mapping versions, so caches cannot continue returning old groups while the page describes a new interpretation.

Use cardinality and amount-conservation checks for multi-table joins to examine row expansion caused by historical versions. This article adds per-fact attribution and expected group results, because wrong attribution can leave the overall total completely unchanged.

Validate boundaries, late arrivals and corrections in AI analytics

Start with the two example orders. Ask separately for transaction-time and current ownership, checking East China and North China amounts rather than only the total of 300. Add an order exactly at the effective boundary and confirm it contributes once. Then move the transfer to an intraday instant to detect misattribution caused by truncating timestamps to dates.

Create gaps and overlaps in the validity intervals. Require an explicit unknown or conflict state with fact identifiers preserved. Do not make the test pass by filling in current values or arbitrarily removing duplicates. Add a late-arriving order too: an old transaction received today must use the selected historical time basis, not ingestion day, to choose its region.

Apply a retroactive correction. The latest corrected view should change under approved rules, the frozen old report should remain reproducible, and the difference should be explainable fact by fact. For deleted or merged customers, check historical identity mappings and retention policy; absence from current master data must not automatically erase past facts.

Test how language maps to attribution rules. Phrases such as 'ownership at the time,' 'recalculate under the current organization' and 'reproduce the last monthly report' should follow distinct controlled paths. Ambiguous requests should expose the chosen default or ask for confirmation. Answers should identify the metric, attribution mode, fact period, revision cutoff and unknown amount.

Useful acceptance measures include zero-match and multiple-match fact counts, affected amounts, group differences, mappings awaiting refresh after corrections and differences when reproducing old reports. Fix the scope and denominator for each measure. Where the business requires unique attribution, multiple matches are failures, not errors to dilute into overall accuracy. Report periods without known historical ground truth as unverified.

The deliverable is a dimension-version contract, evidence for changes, controlled join paths, exception details and counterexample results. The model interprets which kind of history the user asks about. The data layer demonstrates that the chosen history has evidence and a unique mapping, and explains why this result differs from the previous one.