Forum Discussion
Shared data, one consistent view across teams.
Memorable MemberHi Anshul7878,
Echoing Hadi-0: a hybrid medallion approach, with the Lakehouse for ingestion and the Warehouse where it fits, is the pattern I see recommended most often for mixed sources like operational systems plus SharePoint files. I have not worked on the project you mention, so my comments below are general design points for the question of how to organize multi-source data before the reporting layer.
Lakehouse-first or Warehouse-first?
| Factor | Lakehouse-first | Warehouse-first |
|---|---|---|
| Source variety | Strong for raw files, semi-structured and messy inputs (including SharePoint exports) | Best when sources are already structured and relational |
| Team skills | PySpark and Spark SQL notebooks | T-SQL, stored procedures |
| Transformation style | Code-based, flexible, good for cleansing and reshaping | Set-based SQL, familiar to database developers |
| Serving to reports | Delta tables read by Direct Lake via the SQL analytics endpoint | Warehouse tables, also usable with Direct Lake |
| Typical use | Bronze and Silver layers | Gold layer with conformed dimensions and facts |
| Updates | Delta merge from notebooks | MERGE and UPDATE statements in T-SQL |
A layering that works for several teams
| Layer | Content | Rules |
|---|---|---|
| Bronze | Raw copies from operational systems, shortcuts to external stores, SharePoint file drops | Keep as received, add load date and source system columns, no business logic |
| Silver | Cleaned, typed and de-duplicated entities with consistent keys | Standard data types, conformed identifiers (for example patient and facility keys), data quality checks |
| Gold | Dimensional models per subject area with shared dimensions | One definition of each business measure, owned and documented |
For the "one consistent view" requirement
| Need | Approach |
|---|---|
| Same numbers across clinical, operations and management | Shared Gold dimensions and a certified semantic model that all reports reuse |
| Different views per team | Separate reports or perspectives on the same model, with row-level security where required |
| Sensitive data | Sensitivity labels, workspace and item permissions, and masking or restricted columns where needed; check your compliance requirements |
| Data quality | Checks at the Silver step (null keys, duplicate IDs, out-of-range values) that fail or flag rows before they reach Gold |
| Lineage and ownership | Document sources and owners per table; use lineage view and a catalog tool |
One practical test of the design: pick a single metric that two teams currently report differently, trace it from source to dashboard, and confirm both teams get the same number from the same model.
So my overall approach would be:
Land -> Lakehouse (raw, shortcuts for external stores)
Clean -> Silver tables with conformed keys and quality checks
Model -> Gold dimensional model, Lakehouse or Warehouse by team skills and workload
Serve -> one certified semantic model, RLS for team-specific views, sensitivity labels for protected dataAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.