SCD2 is a product question: point-in-time deployed state with MLVs
Current-state dimensions lie the moment a device moves. Dashboards that say "where is it now" are fine for operations. History questions ("where was it when the alarm fired?") need slowly changing dimension type 2, not a hope that someone kept yesterday's extract.
For measuring points and similar deployed assets, I put location and classification history in SCD2 Materialized Lake Views before anyone builds "devices as of" pages. The change feed is the source of truth. The MLV is the productized history contract.
Figure 1. Point-in-time placement is an SCD2 product question. Model effective dating before you ask dashboards to invent history. Source: Microsoft Learn: dimensional modeling dimension tables.
Figure 2. MLVs make history transforms operable. Lineage shows which change feed feeds the point-in-time contract. Source: Microsoft Learn: get started with materialized lake views.
I learned this when point-in-time reports disagreed with ops screens that only knew current placement. The grain looked right until someone asked for last quarter's deployment map. Current dims answered today. The business asked for then.
The core idea
Point-in-time deployed state is a product contract between gold grain, history tables, and semantic relationships. SCD2 MLVs are a good home when the change feed is trustworthy.
Activation history and location history are not the same story. If the upstream change table is incomplete for one axis, 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.
A model that stays explainable
1. Admit current-state dims are not history
A dim that updates in place answers "now." The day a measuring point moves building, site, or classification, 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 lakehouse, 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.
3. Split activation SCD from location SCD when upstream is uneven
Activation (in service versus retired) may arrive cleanly while location moves arrive 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 activation should not wait on location quality, and vice versa.
4. Carry event-type and classification flags with the history
If "moved," "reclassified," and "corrected" are different business events, store the flags beside the SCD rows. Otherwise every semantic model invents CASE logic that drifts. History without event semantics becomes a timeline people argue about in meetings.
5. Define the point-in-time join contract 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 "devices 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.
Failure modes I design against
Type 1 forever. Ops is happy. Audit and incident review are not.
One SCD catching every attribute change. Noisy history, unclear grain, impossible backfills.
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. Business valid time versus pipeline load time mixed without saying so.
Shipping the visual before the contract. Stakeholders believe the page. You discover the dim was current-only in hypercare.
Fixtures that prove as-of behavior
Keep a small fixture set: a measuring point that moves once, one that is reclassified, one with a correction event, and one that never moves. 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.
Trade-offs
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.
What I would put on an ADR
- Measuring-point location and classification history are SCD2 before history reports ship.
- MLVs (or named equivalent) own the SCD transform over the change feed.
- Activation and location 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 contract.
- "Devices as of" acceptance tests include known move and reclassification fixtures.
Primary references: Microsoft Learn on Materialized Lake Views and lakehouse modeling guidance for slowly changing dimensions in analytical models.
Closing
If your dim only knows where the device is today, you do not have point-in-time analytics. You have a live inventory view with a reporting costume.
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.