Recently, I worked on a historical backlog snapshot solution in Microsoft Fabric to support:Backlog aging (30/60/90+ days)PM and Manual Work OrdersHistorical trend reportingLarge-scale datasetsOur initial implementation used a SQL Stored Procedure with CROSS JOINs, multiple CASE conditions, and status-based filtering. While it delivered the required results, debugging and maintaining the logic became increasingly challenging as requirements evolved.After extensive troubleshooting, we moved the transformation logic to a PySpark Notebook. This allowed us to validate each transformation stage independently and quickly identify issues related to joins, filters, and business rules.Benefits observed:Step-by-step debugging and validationBetter visibility into joins and filtersEasier maintenance of business rulesImproved scalability for larger datasetsKey TakeawaySQL Stored Procedures are effective for many ETL scenarios. However, for historical snapshot reporting involving complex business logic and large datasets, PySpark provided greater flexibility, maintainability, and debugging capabilities.I'd be interested to hear how others have approached similar challenges in Fabric.How are others handling historical snapshot reporting in Microsoft Fabric? Are you using SQL, Spark Notebooks, Dataflows Gen2, or a hybrid approach?

Hi Pavanadamar , Please try below Enhancements. 1. You are generating snapshots but you can standardize it for long-term maintainability. Use append-only Delta snapshot table. Please refer below sample code. df_final \.withColumn("snapshot_date", current_date()) \.write \.format("delta") \.mode("append") \.partitionBy("snapshot_date") \.saveAsTable("gold.backlog_snapshot") 2. Instead of recomputing everything daily, identify changed or new work orders and Only process those. You can use last_modified_date or Delta Change Data Feed (CDF). 3. Optimize Delta Tables, For large backlog datasets use below sample code. OPTIMIZE gold.backlog_snapshotZORDER BY (work_order_id); Note: It will give Faster filtering by Work Order and reduced scan time for reports. 4. Create Separate Business Logic Layer, break notebook into logical modules like load_data, apply_business_rules, calculate_aging andgenerate_snapshot. 5. Aging Logic Optimization: Instead of multiple CASE statements, try below sample logic. from pyspark.sql.functions import datediff, when df = df.withColumn("aging_bucket",when(datediff(current_date(), col("created_date")) <= 30, "0-30").when(datediff(current_date(), col("created_date")) <= 60, "31-60").when(datediff(current_date(), col("created_date")) <= 90, "61-90").otherwise("90+")) I hope this information helps. Please do let us know if you have any further queries. Regards, Dinesh

Historical Backlog Snapshots in Microsoft Fabric - SQL vs PySpark

Forum Discussion

Related content

Visualizing Historical Data

Dynamic WoW Variance against a Persistent Historical Snapshot in Power BI

Historical Log support

Power BI embedded in PowerPoint – Live /Snapshot/Public Snapshot buttons not working (recent regr?)

Exchange Rates - Historical