How do you report historical facts by a customer's current region when facts point at old dimension versions?

## Two legitimate answers to the same question "Show me last year's revenue by sales region" has two defensible results when the dimension is versioned. **As-was** (historical truth): each order counts toward the region the customer was in *at the time of the order*. If a customer moved from EMEA to APAC in August, their March orders count as EMEA. This is what the region manager who owned that territory in March will recognise, and it is what an auditor expects, because it is what the report said when it was first produced. **As-is** (current truth): every order that customer ever placed counts toward APAC, because that is where they are now. This is what a customer-lifetime-value analysis wants, and what someone building cohorts by current segment wants — they need one consistent attribute per entity, not a mixture. Both are correct. The failure mode is not choosing the wrong one; it is producing one while the stakeholder assumed the other, and finding out when two dashboards disagree. ## As-was is free Because the fact stores the surrogate key of the version in force at event time, as-was is simply the natural join: ```sql SELECT d.region, SUM(f.order_amount) FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk GROUP BY d.region; ``` No dates, no flags. This is the default the model gives you, and it is why Type 2 exists. ## As-is needs a hop through the durable key The surrogate key on the fact identifies a version; the business key identifies the entity. To get from a fact to the entity's *current* attributes you go version → entity → current version: ```sql SELECT cur.region, SUM(f.order_amount) FROM fct_order f JOIN dim_customer ver ON ver.customer_sk = f.customer_sk JOIN dim_customer cur ON cur.customer_id = ver.customer_id AND cur.is_current GROUP BY cur.region; ``` The second join is guaranteed to produce exactly one row per entity — provided the invariant that only one row per business key is current actually holds. If a broken load left two current rows, this query fans out, which is one more reason that assertion belongs in the pipeline. ## Three ways to make it cheaper **Carry the durable business key on the fact.** Storing `customer_id` alongside `customer_sk` costs a column and removes the first hop entirely: join straight from the fact to the current row. Many teams do this precisely because as-is reporting is common. It also helps reconciliation against the source system. **Expose a current-dimension view.** Define `dim_customer_current` as the rows where the flag is true, keyed on the business key, and let analysts join to it by name. This turns "which region convention am I using?" into a visible choice of which object you joined, rather than an invisible property of your predicate. **Carry a current-value column on every version.** Some designs keep both a historical attribute and a current attribute on each dimension row, overwriting the current one across all of that entity's versions whenever it changes — so a single join yields both perspectives. This is the hybrid SCD approach and it belongs to the discussion of SCD types; the point here is that it exists as an alternative to the second join, and it trades a heavier dimension load for a simpler query. ## Naming is most of the fix The biggest practical improvement is not technical. Name the columns so the perspective is visible at the point of use: `region_at_order` versus `current_region`, or `region_historical` versus `region_current`. A column simply called `region` in a versioned model is an ambiguity waiting to produce two conflicting slides in the same deck. Whatever convention you pick, apply it everywhere — a semantic layer that exposes one "Region" attribute across a warehouse where different marts mean different things by it is worse than no semantic layer. ## Where it gets genuinely hard **Mixed perspectives in one query.** "Revenue by the region at time of sale, filtered to customers currently in APAC" is a legitimate request and needs both joins at once. It works, but the result must be labelled carefully or nobody can reproduce it. **Attributes that are not stable identities.** If the current value of an attribute is itself frequently rewritten upstream, as-is reporting is a moving target: the same query run twice a week apart gives different numbers with no change in the underlying facts. That is not a bug, but it must be stated, or people will report it as one. **Restating past periods.** As-is reporting means a closed accounting period's numbers change whenever a dimension attribute changes. Finance usually forbids that, which is why the financial reporting layer is nearly always as-was and the analytical layer is where as-is lives. ## The short version The fact's surrogate key is the as-was answer, already resolved. The durable business key is the bridge to the as-is answer. A well-built mart offers both, names them so they cannot be confused, and is explicit about which one each published report uses.