Forum Discussion

icassiem's avatar
icassiem
Icon for Post Prodigy rankPost Prodigy
2 months ago
Solved

Fabric Data Factory Push Back Questions i need to clarify

Good day, Apologies i know this is more a fabric data factory question, but i need an aswer before monday morning I porposed the F2 ecosystem for a small enviornment, and my thoughts are datafactor...
Need Help
  • trivedisunita's avatar
    trivedisunita
    2 months ago

    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 live

     

    Step-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 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 

     

    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 you
    Sunita
  • R1k91's avatar
    R1k91
    2 months ago

    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.

     

  • R1k91's avatar
    R1k91
    2 months ago

    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

     

R1k91's avatar
R1k91
Icon for Super User rankSuper User
2 months ago

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's avatar
icassiem
Icon for Post Prodigy rankPost Prodigy
2 months ago

R1k91 

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?

  • R1k91's avatar
    R1k91
    Icon for Super User rankSuper User
    2 months ago

    1. I suggest to use notebooks with pyspark to load and transform data in lakehouses. SQL in another option in notebooks and it's the only one if you decide to load data into warehouse (views and stored procedures). these two methods are the cheapest from a CUs perspective most of the times.

    2. I don't get your question. I suggested to use fabric data pipeline to orchestrate the notebooks. if you create multiple notebooks to manage different stages of the transformations you need to orchestrate them and data pipelines is great on that.

    3. don't get the question.