Medallion Architecture on Hybrid project: Azure SQL to BI service

[GQ00]

Hi Braintrust,

The small org I work for (50 headcount) has decided it is time to redo their BI solution.

We currently have a bit of a mess, and I'm looking at starting from scratch while ensuring best practices are followed.

The business is cautious about going full fabric, which is why we landed on a hybrid approach, doing only the enrichment processes in the BI service. I was hoping you could help me understand the next best steps.

I plan to use our current Azure SQL for Raw ingestion and minimal cleanup (dropping unnecessary fields, doing heavy calculations such as adding COGS & profit to sales fact, & refreshing the date table)

I then aim to push that to the service where I would upload some Excel files from SharePoint to do some more enrichment,think customer & product enrichment with dataflows on DIM tables.

Do you see any issues with my approach?

I will need at least one fabric license, for DF right? would cost be a concern?

I'm no database expert by any means, is it feasible to get the sales fact table from Azure SQL, or is partitioning practices and having a staging environment advised?

Thanks for any guidance!

[v-karpurapud]

Hi @GQ00 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @GQ00 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

Hi @GQ00 

Welcome to the Microsoft Fabric Forum.

To modernize the organization's Business Intelligence (BI) platform, adopting a Medallion Architecture within a hybrid model provides a strategic, scalable solution. This approach supports long-term maintainability while aligning with modern data practices.

In the Bronze layer, raw data is ingested into an Azure SQL Database, where only minimal preprocessing occurs. This stage includes tasks like filtering out unused fields, calculating essential business metrics (e.g., cost of goods sold and profit) and maintaining an up-to-date date dimension.

The Silver layer moves into Microsoft Fabric, using Dataflows Gen2 for data enrichment. Here, the Azure SQL data is merged with reference data, such as customer and product master files stored in Excel documents on SharePoint Online. SharePoint is used as a reliable source for this kind of reference data, as long as schema validation is enforced to ensure data quality.

In the Gold layer, Power BI within Fabric is used to build a semantic model on top of the enriched data. This model supports the creation of business-friendly metrics using DAX, enabling interactive dashboards and reports that deliver actionable insights to end-users.

To implement this architecture effectively, your organization will need at least one Microsoft Fabric Capacity SKU (F2 or higher). This ensures efficient performance when using Dataflows Gen2 and OneLake storage, while offering a cost-effective path for scaling. At this stage, a formal staging environment or advanced partitioning isn't required. However, as your data volumes and transformation complexity increase, these can be introduced later. To improve performance and scalability, it's also recommended to implement incremental refresh in your Power BI datasets or Dataflows.

If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges. Also, appreciate your kudos.

Thank You!

[v-karpurapud]

Hi @GQ00 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.