Forum Discussion
Shared data, one consistent view across teams.
Healthcare analytics is a good example of why this matters.
Clinical teams, operations teams, and management may need different reports, but those reports still depend on reliable, consistent patient data. If information sits across operational systems and SharePoint files, preparing it for reporting can become a challenge of integration and data quality before visualization even begins.
One detail I found interesting in a Microsoft Fabric healthcare analytics project we recently took for datatobiz is the use of OneLake alongside automated data processing and Power BI dashboards.
The architectural point is worth discussing: bringing data into a common environment is only the first step. The next is making sure the data is appropriately prepared, governed, and structured for the people using it.
For those working with Fabric, how are you organizing data from multiple sources before it reaches the reporting layer? Do you prefer a Lakehouse-first approach, a Warehouse-first approach, or a combination based on the workload?
2 Replies
jubinsoniSolution Sage
Hi 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.
hello!
This is a phenomenal real-world scenario. Healthcare analytics perfectly highlights the exact pain points that Microsoft Fabric was built to solve-specifically the nightmare of reconciling clinical compliance, operational metrics, and management dashboards over fragmented data.
In our architectural blueprints, we heavily lean toward a Hybrid Medallion Architecture (Combination based on the workload) using both the Lakehouse and the Warehouse layers. Here is a breakdown of how we structure this progression before it hits the Power BI Direct Lake reporting layer:
1. The Ingestion & Bronze Layer (Lakehouse First)
For data coming from EMR/EHR systems, operational tracking, and messy SharePoint files, we always ingest into a Lakehouse first. We leverage OneLake Shortcuts to connect to external operational file stores instantly without duplicating the raw data. This acts as our Bronze tier-storing raw Delta Parquet files securely.
2. The Transformation & Silver Layer (Spark Notebooks)
Healthcare data requires rigorous data quality, schema enforcement, and absolute compliance handling. We run Synapse Spark Notebooks (PySpark) directly over the Lakehouse tables to clean strings, handle null values, mask PII data to maintain HIPAA compliance, and structure the records.
3. The Serving & Gold Layer (The Lakehouse vs. Warehouse Decision)
This is where the workload split happens based on the audience you mentioned:
• For Clinical Teams and Data Scientists: We keep the Gold tables inside the Lakehouse. This allows data scientists to build predictive AI models directly over highly optimized Delta tables using PySpark without any ingestion lag.
• For Operations and Management Teams: We expose these Gold tables through a Fabric Data Warehouse. The Warehouse engine provides native SQL capabilities, cross-database JOINs, row-level security (RLS), and object-level permissions that enterprise database administrators and business intelligence developers expect.
The Magic Link: Power BI Direct Lake Mode
Regardless of whether the data sits in the Lakehouse or Warehouse compute tier, it all lands in the unified open-source Delta Parquet format inside OneLake. This allows our Power BI dashboards to query millions of rows of patient and operational records instantly via Direct Lake mode-completely bypassing legacy import refresh bottlenecks and ensuring everyone looks at the exact same truth.
For a multi-stakeholder domain like healthcare, a combination framework is definitely the gold standard! Curious to hear if your current setup at datatobiz is leaning more toward native Spark pipelines or T-SQL orchestration for your Silver-to-Gold transformations?
