Single wide table and dimensional schema

Hi SamyAbdul,

I'd go with your first option: keep the four Silver tables at their native grain, and build a shared Hotel dimension plus separate fact tables in the Gold Warehouse. The different observation periods and independent source releases are actually the main reason not to consolidate early.

Why I'd avoid the wide Silver table

• Pivoting four datasets into one row per hotel per snapshot forces a single grain onto data that doesn't have one. You end up deciding inside the ETL which release "wins", and you get nulls or repeated values wherever the periods don't line up.
• Every new release of any one source means re-pivoting the whole table, and you lose the ability to say exactly which release a number came from.
• It's hard to undo: going from separate facts to a combined view later is easy, the reverse isn't.

Suggested design

1. Silver: one cleaned table per dataset. Standardize column names, data types and the hotel business key, and add lineage columns such as source_release, observation_period_start, observation_period_end and load_ts. No pivoting and no business-level aggregation.
2. Gold dimensions: a conformed DimHotel keyed on the standardized hotel key (SCD Type 2 if hotel attributes change between releases), a DimDate, and optionally a DimRelease/DimSnapshot describing each source release.
3. Gold facts: one fact per dataset at its own grain (e.g. admissions per hotel per period, another dataset per hotel per month). Each fact links to DimHotel and to DimDate at the grain it actually has. Snapshot-style datasets fit the periodic snapshot fact pattern.
4. "One row per hotel per snapshot": if the business needs this, build it as a Gold view or aggregate fact on top of the facts, using a small mapping table such as ReportingSnapshot(snapshot_id, dataset, source_release) that records which release of each dataset belongs to each snapshot. Release selection then becomes data, not hard-coded ETL logic.

In the semantic model, the shared DimHotel and DimDate let users slice across all the facts together, so most reports won't need the wide table at all.

Useful Microsoft Learn references:

• Dimensional modeling in Fabric Data Warehouse: https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-overview
• Fact tables (incl. periodic snapshot facts): https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-fact-tables
• Star schema guidance (fact tables should load at a consistent grain): https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Hope this helps!