Single wide table and dimensional schema

SamyAbdul​ 

I strongly suggest go with option one and follow the medallion architecture.

1. Bronze – Raw Data

• Preserve source data as received, including source release and ingestion metadata.
• bronze.raw_dataset_a
• bronze.raw_dataset_b
• bronze.raw_dataset_c
• bronze.raw_dataset_d

2. Silver – Cleansed and Standardized Data

Maintain separate tables, preserving each dataset's original grain while standardizing column names, data types, formats, and common business attributes.

• silver.dataset_a
• silver.dataset_b
• silver.dataset_c
• silver.dataset_d

Avoid consolidating datasets with different granularities into one wide table.

3. Gold – Dimensional Model (Warehouse)

Build the dimensional model, business logic, and reporting structures.

Dimension Tables:

• dim_hotel
• dim_date
• dim_geography (if required)
• dim_source_release
• dim_observation_period
• Fact Tables:
• fact_dataset_a
• fact_dataset_b
• fact_dataset_c
• fact_dataset_d

Each fact table should preserve its respective business grain and connect to shared/conformed dimensions where applicable.

If consolidated hotel-level reporting is required, create a Gold-level reporting view or aggregated table rather than introducing unnecessary consolidation in Silver.

My recommendation: Keep Silver independent of reporting requirements and perform dimensional modelling in Gold. This provides better scalability, reusability, and maintainability while preserving the original granularity and release context of each dataset.

If this helps, ✓ Mark as Kudos | Help Other