Key takeaways
- Dim attribute history is SCD2 before as-of reports ship.
- MLVs (or named equivalent) own the SCD transform over the change feed.
- Two histories may be separate when upstream readiness differs.
- Event-type flags travel with history rows; DAX does not reinvent them.
- Gold grain and semantic as-of relationships are documented as one spec.
- As-of acceptance includes known change and correction fixtures.
Current-state dimensions lie the moment an attribute changes. Dashboards that say "what is it now" are fine for current-state screens. History questions ("what was it when the fact happened?") need slowly changing dimension type 2, not a hope that someone kept yesterday's extract.
For any dim table whose attributes change over time, I put the history in SCD2 before anyone builds as-of pages. The change feed is the source of truth. The MLV is the productized history spec.
Named model: SCD2 as a product spec
- Change feed: trustworthy changes and corrections (not a hope that someone kept yesterday's extract).
- SCD2 MLV: declarative effective dating over that feed, with event-type flags beside the rows.
- As-of spec: gold grain, keys, and semantic relationships share one written clock and join predicate.
- As-of reports: history pages ship only after the MLV exists and fixtures pass.
- Spec: point-in-time state is a product decision between gold, history tables, and the model, not a dashboard costume on Type 1 dims.
Figure 1. SCD2 as a product spec. Change feed truth, MLV history, shared as-of grain, then gated reports. Current dims answer today. History needs SCD2.
Context and constraint
A common failure: point-in-time reports disagree with current-state screens that only know the latest row. The grain looked right until someone asked for last quarter's view. Current dims answered today. Users asked for then.
Two histories are not always the same story. If the upstream change table is incomplete for one attribute set, separate the SCDs instead of forcing a single wide history that invents certainty. Event-type flags belong next to history so classification is not reinvented in DAX every sprint.
Figure 2 is the Learn framing for Type 2 effective dating. Model dating before you ask dashboards to invent history.
Figure 2. Point-in-time state is an SCD2 product question. Model effective dating before you ask dashboards to invent history. Source: Microsoft Learn: dimensional modeling dimension tables.
Figure 3 is why MLVs are a good home when the change feed is real. Lineage shows which feed feeds the point-in-time spec.
Figure 3. MLVs make history transforms operable. Lineage shows which change feed feeds the point-in-time spec. Source: Microsoft Learn: get started with materialized lake views.
Design
1. Admit current-state dims are not history
A dim that updates in place answers "now." The day an attribute on a dim row changes, every past fact that joined only on current keys inherits the new attributes unless you modeled effective dating. Pretending a Type 1 dim is "good enough until we need history" is how you rebuild gold under deadline.
2. Prefer MLVs when the change feed is the truth
Materialized Lake Views work well when you can express SCD2 logic declaratively over a change or snapshot feed and rematerialize on a schedule you control. Keep the logic close to the , versioned, and reviewable. Notebooks that hand-roll merges are fine for prototypes; they become tribal when every engineer has a different effective-date edge case. Figure 1 is that ownership split drawn as four jobs.
3. Split one history into two when upstream is uneven
Attribute set A may arrive cleanly while attribute set B arrives late or in a different table. Model two histories with clear grains rather than one sparse mega-SCD full of null effective ranges. Reports that only need history A should not wait on history B quality, and vice versa. Figure 4 is the split.
Figure 4. Split uneven histories. Two clear grains beat one sparse mega-SCD that invents certainty. More objects to document is cheaper than one nobody trusts.
4. Carry event-type and classification flags with the history
If "changed," "reclassified," and "corrected" are different change events, store the flags beside the SCD rows. Otherwise every invents CASE logic that drifts. History without event semantics becomes a timeline people argue about in meetings.
5. Define the point-in-time join spec in gold and in the model
Name the keys, the as-of predicate (transaction time versus valid time if you distinguish them), and how facts attach. Semantic relationships must match that grain. A bridge or role-playing relationship that ignores effective dates will look connected and still return wrong attributes.
6. Gate as-of reports on the SCD existing
Do not ship a report page that implies history while the model only has current dims. Make the MLV (or equivalent SCD table) a dependency in the delivery checklist. Demo current-state on current dims. Demo history only when SCD2 is live and tested. Figure 5 is the gate.
Figure 5. Gate as-of reports on the SCD. Fixture pack green before the history page. A current-only dim wearing an as-of costume fails at release.
Fixtures that prove as-of behavior
Keep a small fixture set: a dim row that changes once, one that is reclassified, one with a correction event, and one that never changes. For each, assert attributes at timestamps before and after the change. If your SCD logic cannot pass those fixtures, do not argue about dashboard colors. Fix history first. Fixture packs also document the product meaning of event types for the next engineer.
Failure modes
Type 1 forever. Current-state screens are happy. Historical review is not.
One SCD catching every attribute change. Noisy history, unclear grain, impossible backfills. Figure 4 names the anti-pattern.
DAX recreating effective dates. Slow, inconsistent, and invisible to data engineers.
Assuming change feed completeness. Late corrections without a correction event type silently rewrite the story.
As-of joins on the wrong clock. Valid time versus pipeline load time mixed without saying so.
Shipping the visual before the spec. Readers believe the page. You discover the dim was current-only after release. Figure 5 is the checklist that prevents that.
What to copy / what not to copy
Copy
- Dim attribute history is SCD2 before as-of reports ship.
- MLVs (or named equivalent) own the SCD transform over the change feed.
- Two histories may be separate when upstream readiness differs.
- Event-type flags travel with history rows; DAX does not reinvent them.
- Gold grain and semantic as-of relationships are documented as one spec.
- As-of acceptance tests include known change and correction fixtures.
Do not copy blindly
SCD2 storage and rematerialization cost more than Type 1. You pay that to answer honest history questions. MLVs add platform constraints and refresh semantics you must learn; the trade is less bespoke merge code. Splitting SCDs means more objects to document. The alternative is one confusing object nobody trusts. Point-in-time semantics also force product owners to define which clock matters. That conversation is uncomfortable and necessary.
Primary references: Materialized Lake Views overview, get started with Materialized Lake Views, and dimensional modeling dimension tables.
Build SCD2 as a product decision. Put it in MLVs when the change feed is real. Separate uneven histories. Then, and only then, build the as-of reports people keep requesting.