Modeling contracts, fact design, and semantic parity.
View All Tags
Teams ask how good the data needs to be before conversational analytics helps. The honest answer: good enough that a careful analyst would trust the same tables without a chat UI.
Prompts cannot rescue ambiguous grain, cryptic column names, or missing relationships.
Figure 1. Conversational tools sit on top of curated analytical structures. Source: Microsoft Learn: lakehouse overview.
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.
Helpful gold views that "just join service and device" hide many-to-many grain until a report page disagrees with itself. Filters double count. Role-playing dims break. Two pages that should reconcile never do.
Deploy bridge keys as a first-class artifact and remodel the service-device fact before you polish visuals. Matching logic must be shared with telemetry enrichment or the numbers diverge by construction. Semantic role-playing dims need matching foreign key types. Remodel first. Visual polish second.
Figure 1. Many-to-many grain leaks into blank or double-counted visuals. Remodel the fact before polishing pages. Source: Microsoft Learn: understand relationships.
Figure 2. Bridge keys and filter direction are part of the remodel. Dashboards inherit whatever grain you leave in gold. Source: Microsoft Learn: understand relationships.
A gold device table that looks clean in Spark can still fail as a platform asset. Optional links to contracts drop unmatched devices. Free-text site fields hide business keys behind tribal regex. Captions and folders are treated as polish. Stakeholders open a report and half the estate is missing without an error.
Gold becomes useful when unmatched keys, captions, and relationships ship as one semantic contract with the table. "All devices visible regardless of match" is an acceptance test, not a slogan.
Figure 1. Gold only becomes a platform asset when keys and relationships behave as a semantic contract the model can trust. Source: Microsoft Learn: understand relationships.
Figure 2. Row counts on the SQL endpoint can look healthy while reports hide unmatched keys. Test the contract, not only Spark success. Source: Microsoft Learn: lakehouse SQL analytics endpoint.