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
Continued ContributorHi icassiem ,
-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 live
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 workloads
2. Ingestion & Orchestration
Use Fabric Data Factory Pipelines:
API / JSON ingestion using Copy activity + ForEach loop
Land raw data into Bronze Lakehouse
Trigger downstream transformations
Pipelines 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 unnecessarily
4. 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
-dashboards
Keep 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 DirectLake
SSMS 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 + Notebooks
Then 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
icassiemtrivedisunita Thank you so much
the main ques is the Middleware, where if python or pipeline or datafactory patches gets rolled out, does it imtpact the pipes/code and requires uipdate and there's amaintain dependence/ risk as my cto a bit heistant , i dont recall this with SSIS?
0. Is this similar to sql job agent and ssis?
1. so i am thinking, i dont know if there will be future doamins, so "Product" > lhs_silver and whs_gold > then lhs_gold if ML correct?
2. do i plan as enterprise or only work with the fact that i know this domain "product" there could be in future additional domain which breaks my arch trsutre of "product" worksapce hten lh_silver etc what is best practise but i dont want to go uneccessary broad
3. becuase its small scale with only silver directly from API loops that require unpivot etc on the ingest i dont know if dataflow gui does this or only orch or do i use pythin in datafllow?
4. for silver to gold whs i want to use dbt core sql tranfrom from silver, is this executed directly from dataflow etc?
Please Help
- trivedisunita
Hi icassiem,
I completely understand this concern , especially coming from an SSIS background. the confusion now is not tools, it’s how to design something simple that won’t break later.Let me answer this very directly so you can move forward confidently-The confusion is coming from 3 things:-1. Trying to design for future unknown domains
-2. Mixing too many tool options (dataflow, python, dbt, pipelines)
3. Comparing everything with SSIS modelIn Fabric, the key is- separate responsibilities and keep it simple initially.
Answer to Your Questions:-
1. Is this similar to SQL Agent + SSIS?
Yes , conceptually you can say-
Pipelines = SQL Agent (scheduling, orchestration)
dbt / Python = SSIS data flow logicFabric is more modular and loosely coupled compared to SSIS, so components are more independent. However, changes in one part (like schema or transformation logic) can still affect downstream steps, so validation is still important.2. Product" > lhs_silver and whs_gold > then lhs_gold
You are close but recommended Bronze --> Lakehouse Silver --> Lakehouse Gold --> Warehouse (for BI)Optional: Gold (ML) --> Lakehouse (separate if needed)Don’t create multiple gold layers upfront
-Keep one clear Gold layer first
-If ML comes later --> you extend, not redesign3.. Enterprise planning vs small scale :- For your case (small setup and single domain)You can do this-
-One workspace (Product)
-Bronze / Silver / Gold layersLater (if new domains come): Then split into domain workspaces
Your current design will NOT break later You will just extend it.
4. Dataflow vs Python
For your case: Use Python / PySpark notebooks . API , JSON , unpivot are complex transformation
Dataflows are more for: simple joins, light transformations.Dataflows are NOT ideal for heavy JSON reshaping so Pipeline --> call Notebook (Python), Notebook --> load Silver.
5. dbt from Silver --> Gold is good approach
dbt performs SQL transformations .
Runs on: Warehouse (T-SQL) OR Lakehouse SQL endpoint.Best practice: Trigger dbt from Pipeline , NOT from Dataflow.
A set up for you like -
Pipeline (orchestration) -> Bronze - Lakehouse (raw API data) - Python Notebook --> Silver (clean / unpivot) -> dbt (SQL) --> Gold (Warehouse) -> Power BI / CopilotThis is clean, scalable, and low maintenance.If dbt feels heavy initially Use Notebook or SQL directly in Warehouse add dbt later when complexity grows.
Pick ONE transformation style per layer
Silver --> Python
Gold --> dbtYou don’t need to solve everything upfront build for what you know now, and evolve it as new requirements come in. this approach will stay stable and easy to maintain even as you scale later.Hope this helps clarify things.