Best Practice - Weekly Archives

Hi Byzza,

You're on the right path using T-SQL notebooks, they’re faster and give you more control than Dataflows, especially for large-scale archiving. To make your process efficient, maintainable, and scalable over 8 years, here's a refined approach:
Split Forecasts and Actuals

Actuals don’t change, so archive them once in a permanent history table. Forecasts change weekly, so only archive the forecast data each week, adding an ArchiveDate. This avoids copying static data and keeps storage lean.

One-time actuals archive INSERT INTO LPE_Actuals_History (...)SELECT ... FROM LPE_ExFactory_Week WHERE Actual IS NOT NULL UNION ALL SELECT ... FROM LPE_ExFactory_Month WHERE Actual IS NOT NULL;

Weekly forecast snapshot INSERT INTO LPE_Forecast_ArchiveSELECT GETDATE(), Week, Month, 'Week', Forecast, 'ExFactory' FROM LPE_ExFactory_WeekWHERE Forecast IS NOT NULL UNION ALL SELECT GETDATE(), Week, Month, 'Month', Forecast, 'ExFactory' FROM LPE_ExFactory_MonthWHERE Forecast IS NOT NULL;
Automate with Notebooks and Pipelines

Continue using T-SQL notebooks for performance. Schedule them weekly with a Fabric pipeline. Add a logging table to track row counts and run times:

INSERT INTO Archive_LogSELECT GETDATE(), 'LPE_Forecast_Archive', @@ROWCOUNT;
Use Delta Tables and Partitioning

Store your forecast archive in a Fabric Lakehouse as a Delta table and partition it by ArchiveDate. This improves performance and keeps queries efficient as data grows.
Power BI Setup

In your Semantic Model, create separate fact tables for Actuals and Forecasts. Use a shared Dim_Date table. Add a slicer for ArchiveDate so users can choose which forecast to compare against actuals. Set up incremental refresh to only update recent data.

daxCopyEditForecast Selected =
CALCULATE(
    SUM('Fact_Forecast_Archive'[Forecast]),
    'Fact_Forecast_Archive'[ArchiveDate] = SELECTEDVALUE('Dim_ArchiveDate'[ArchiveDate])
)
Plan for Growth

After two years, consider summarizing older forecast data (e.g., monthly averages) and moving it to cold storage. You can also archive actuals by year if needed, and monitor storage growth through your log table.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.