Skip to main content

One post tagged with "SCD"

View All Tags

SCD2 is a product question: point-in-time deployed state with MLVs

· 6 min read
Sai Prudhvi Neelakantam
Senior Consultant, Data Engineering & AI at Evidi

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.

Slowly changing dimension type 2 diagram

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.

Materialized lake view lineage sample

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.