Forum Discussion
Post ProdigyFabric Data Factory Push Back Questions i need to clarify
Hi icassiem ,
Great question , and honestly, this is exactly the kind of architectural discussion many teams are having right now when moving to Fabric.The confusion you’re hitting (Lakehouse vs Warehouse, dbt vs Python, orchestration) is completely normal because Fabric intentionally gives multiple valid options depending on use case.Problem From what you described, the uncertainty comes from a few overlapping concerns--Mixing storage + serving roles (Lakehouse vs Warehouse in the Gold layer)
-Mixing orchestration and transformation tools (Data Factory, dbt, Python)
-Concerns about long-term maintainability vs legacy SSIS stability
-Not being sure where AI / Copilot / ML workloads should liveStep-by-Step Recommended Architecture-1. Medallion Structure-
Bronze → Lakehouse(Raw ingestion (API, JSON, Jira, etc.)
Silver → Lakehouse( Cleansed + standardized data (Delta tables)
Gold → Split depending on use case ( Warehouse → BI / Power BI / Copilot), ( Lakehouse → ML / Data Science (optional))
This hybrid approach is widely used because:
Warehouse is optimized for SQL + reporting performance
Lakehouse is better for Spark + Python + ML workloads2. Ingestion & Orchestration
Use Fabric Data Factory Pipelines:API / JSON ingestion using Copy activity + ForEach loop
Land raw data into Bronze Lakehouse
Trigger downstream transformationsPipelines are the modern replacement for SSIS and handle scheduling, retries, and dependencies.
3. Transformation Layer (Choose One Primary Approach)
Keep it simple and consistent:
Option A (Recommended for most teams):-Use dbt (SQL-based transformations)Option B (for complex logic / ML): -Use PySpark / Python notebooks
Note- Avoid mixing:
Data Factory + dbt + Python all randomly in one flow
This increases maintenance unnecessarily4. Gold Layer Design (Important for your use case)
For your goal (Copilot, narratives, forecasting):-Build curated/star schema in Warehouse
-Create semantic model (Power BI) on top
Use this for:-Copilot insights
-Business storytelling
-dashboardsKeep ML features (forecasting etc.) in Lakehouse if needed, but serve results through Gold/Warehouse.
5. Querying (SSMS vs Alternatives)
Warehouse → query via T-SQL (SSMS or Fabric SQL endpoint)
Lakehouse → query via:-SQL endpoint
-notebooks
-Power BI DirectLakeSSMS still works fine, but Fabric-native SQL endpoints are typically preferred
Alternative Approach (Simpler for Small Environments) :-
If the environment is small, you can simplify:-Skip dbt initially
Use: - Pipelines + Dataflows Gen2 or Pipelines + NotebooksThen evolve later into dbt when complexity grow
Resources:-
1. Microsoft Fabric Lakehouse vs Warehouse decision guide:-https://learn.microsoft.com/en-us/fabric/fundamentals/decision-guide-lakehouse-warehouse
2. Medallion architecture in Fabric:https://learn.microsoft.com/en-us/fabric/onelake/onelake-medallion-lakehouse-architecture
3. Fabric Data Factory overview:https://learn.microsoft.com/en-us/fabric/data-factory/
4. dbt in Fabric:- https://learn.microsoft.com/en-us/fabric/data-factory/dbt-job-overview
For Copilot and AI scenarios, ensure your Gold layer is modeled as a clean semantic layer (star schema), as this significantly improves narrative quality and insights generation.Overall, your proposed architecture is on the right track. I just refine it by clearly separating responsibilities: Lakehouse for engineering, Warehouse for serving, and Pipelines for orchestration.Hope this helps! If it solved your issue/query , please feel free to mark it as a solution. it helps the community and improves discoverability for similar scenarios.Thank youSunita1. AFAIK I'd try to use spark and sql to transform data between silver lh to gold wh.
it's likely you'll see bursting above 100% of your capacity but it'll be smoothed over 24 hours since they're background operations. use pipelines just to orchestrate (if I remember well high concurrency doesn't work well or it's very limited in F2 therefore you should probably stay "as sequential as possible").
2. if you go cloud there's a risk things may change but you must accept it but if you "stay simple" with notebooks, sql and data factory pipelines the risk of breaking changes is lower.
my 2 cents: instead of using many artifacts focus on what it works and "costs less". less is more.
in data pipelines you have activities that represent a task to do.
most of the time when you configure the activity you setup it to call something that has already been created in the workspace (notebooks, sql stored procedures and dataflows).
Notebook activity - Microsoft Fabric | Microsoft Learn
Use the Stored Procedure Activity - Microsoft Fabric | Microsoft Learn
Dataflow activity - Microsoft Fabric | Microsoft Learn
there're many ways to call api in notebooks, here's a post from Dennes that this a great professional
Microsoft Fabric: Ingesting from API’s and the JSON result | Simple Talk
Super Userhave you carefully checked that all the stuff you want to run in F2 won't burn the entire capacity?
copilot can eat CUs very fast for example.
my advise is to carefully choose what you want to use focusing on what uses less CUs. dataflows for example are way
more expensive than notebooks.
patching that happens behind the scenes is done in a way that doens't impact already written code.
in case there're breaking changes Microsoft will notify like deprecation or removal of stuff.
the difference with on-prem software like SSIS is that you can't control the roll-out, I mean on-prem you know a feature is missing in the next version and you may choose not to install that at all or to drive the change. in Fabric the environment is aligned according to microsoft's roadmap but you can't do anything about that.
icassiemR1k91 Thank You
1. What is the smallest lightweight method i should follow
> lh_silver
> wh_gold
> shedule pipeline
> ingest to silver and tranform to gold only notebooks python with SQL?
2. So the middleware is not an issue, or is there a maintain risk that cant be ignored?
Regards
- R1k91
1. AFAIK I'd try to use spark and sql to transform data between silver lh to gold wh.
it's likely you'll see bursting above 100% of your capacity but it'll be smoothed over 24 hours since they're background operations. use pipelines just to orchestrate (if I remember well high concurrency doesn't work well or it's very limited in F2 therefore you should probably stay "as sequential as possible").
2. if you go cloud there's a risk things may change but you must accept it but if you "stay simple" with notebooks, sql and data factory pipelines the risk of breaking changes is lower.
my 2 cents: instead of using many artifacts focus on what it works and "costs less". less is more.
- icassiem
1. Can you provide a link or example on how to do this please "spark and sql" for source to silver or is this gold only and how/example please to learn and follow?
2. is this python notebook step within the pipeline orch using the data frame for sql, sorry im not too clued up?
3. and how do i do this in one transform as i dont have bronze its directly to silver so i cant downlaod then again transform pipe?