Architecture Advice Needed : Manage historical Data

Hi everyone! 😊

We’re in the process of migrating our existing Azure SQL-based data warehouse to Microsoft Fabric, and I’d really appreciate some advice or suggestions from this amazing community. I’ve learned a lot from the posts and guidance shared here already, and I’m truly thankful for all the insights and support people offer.

Current Architecture:

We use Azure SQL DB as our data warehouse.
Data is staged from IBM DB2, then typed and incrementally loaded into SQL.
We have dim and fact views built on top, which are consumed by SSAS models and Power BI.
For historical data, we archive older views into tables (refreshed yearly), while the most recent 2 years remain live for easier access.

Planned Migration to Microsoft Fabric:

We're moving to a Lakehouse model in Fabric, with the following structure:

Bronze: Raw files stored as Parquet.
Silver: Cleaned and type-casted tables.
Gold: Business logic applied tables— targeting Direct Lake(not sure) for Power BI reporting.

Challenges and Questions:

Some of our business logic involves updates and merges, and we’re concerned about performance with large tables (some with 48 million rows). The business rules we need to apply include:

Converting all sales values to GBP
Finding and applying the latest product cost
Updating rebate percentages, which often change retroactively over the past 7–8 months

Also, we’re trying to figure out how to best handle historical data in Fabric — our current method of archiving into tables and keeping only recent data live (via views) has worked well.

What I’d love your input on:

What’s the best way to handle these kinds of business rules in Fabric without performance bottlenecks?
Should we move this logic to Dataflows Gen2, Notebooks, Lakehouse SQL, or something else?
Any best practices for managing historical data in Microsoft Fabric?

Any advice, war stories, or examples would be hugely appreciated! Thanks again to everyone who shares their knowledge here — it really helps people like me navigate these big changes. 🙏

1 comment