In a one-big-table mart, how do you serve both the as-of-event and the current value of a dimension attribute?

## Two legitimate questions about the same attribute "Revenue by customer segment" is ambiguous, and both readings are valid business questions: - **As of the event.** Which segment was this customer in *when the order was placed*? This is what you want for historical trend reporting — last year's report must still say what it said last year. - **As of today.** Attribute all of a customer's historical orders to their *current* segment. This is what you want when analysing your present-day book of business, or comparing cohorts under today's classification. In a star schema the choice is a join away. Joining the fact's stored dimension key to a versioned dimension gives the as-of value, because the fact was loaded pointing at the version that was current at the time. Joining instead on the durable business key restricted to the current row gives today's value. The consumer picks per query. ## What a wide mart does to that choice Flattening materialises **one** of those answers into a column. Whatever the transform joined is what every consumer gets, forever, and the column name usually does not say which it was. This is the single most common cause of two dashboards disagreeing about the same wide mart: one team assumed frozen, the other assumed current. The answer is not to pick a side. It is to make the choice explicit, per attribute, in the column name, and to serve the other reading deliberately. ## Building the as-of column The as-of value comes from a point-in-time join: match the event timestamp against the effective range of the dimension version: ```sql SELECT f.order_id, f.order_ts, f.order_amount, c.customer_id, -- durable key, keep it c.customer_segment AS customer_segment_at_order FROM fact_orders f JOIN dim_customer c ON c.customer_id = f.customer_id AND f.order_ts >= c.effective_from AND f.order_ts < c.effective_to; ``` Two properties matter. The join must be **many-to-one** — exactly one dimension version can be effective at any instant, or the fact fans out and the mart double-counts. And the ranges must be gapless and non-overlapping over the whole history, or events fall through and rows vanish from the mart entirely. Once built, that column is **immutable by contract**. Rebuilding the mart re-derives the same value from the same versioned dimension, so a rebuild is idempotent and last year's report reproduces. If a rebuild changes an as-of column, either the upstream history was corrected on purpose or you have a bug — there is no third case. ## Serving the current value Two workable designs, and one to avoid. **Keep the durable key and join at query time.** Every wide row carries `customer_id`. Consumers who want today's classification join to a small current-attribute table: ```sql SELECT cur.customer_segment, SUM(w.order_amount) FROM orders_wide w JOIN customer_current cur ON cur.customer_id = w.customer_id GROUP BY cur.customer_segment; ``` This reintroduces one join, but against a tiny table, and it means the current view is never stale and never needs a rewrite of history. It is the design most teams land on. **Materialise a second column.** Add `customer_segment_current`, refreshed on every rebuild. Reads stay join-free, at the cost of rewriting that column across all history on each refresh — affordable if the mart is partitioned and regenerated routinely, painful otherwise. **What to avoid:** one unqualified `customer_segment` column whose meaning lives only in the transform's SQL. That is the shape that produces irreconcilable numbers. ## Deciding per attribute Not every attribute deserves both. Ask what the attribute *is*: - **Facts about the event** — price paid, discount applied, plan at signup, tax rate charged. These are as-of by nature; refreshing them rewrites history and is simply wrong. Freeze them, and consider whether they belong on the fact as measures rather than as dimension copies at all. - **Descriptions of the entity now** — current country, current owner, current tier. Current-value reporting is the common need; the as-of version may not be worth materialising. - **Attributes both audiences argue over** — segment, region, category. These earn both columns, or the frozen column plus the durable key. The economy here is real: every extra frozen column enlarges the restatement surface, and every refreshed column adds rebuild work. Two named columns for the three attributes people fight about beats twelve columns nobody can explain. ## Naming and documentation Suffix conventions do most of the work: `_at_order` / `_at_event` for frozen values, `_current` for refreshed ones, and no bare attribute names in a wide mart at all. Document, next to each, the rule that produced it. A consumer who reads only the column list should be able to tell which question they are answering — because in a wide mart the join that used to make it obvious is gone.