Forum Discussion
Historical Backlog Snapshots in Microsoft Fabric - SQL vs PySpark
Recently, I worked on a historical backlog snapshot solution in Microsoft Fabric to support:
- Backlog aging (30/60/90+ days)
- PM and Manual Work Orders
- Historical trend reporting
- Large-scale datasets
Our 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 validation
- Better visibility into joins and filters
- Easier maintenance of business rules
- Improved scalability for larger datasets
Key Takeaway
SQL 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_snapshot
ZORDER 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
Hi v-dineshya,
Thank you for following up. The issue has been resolved, and the PySpark Notebook approach is working well for our historical snapshot implementation. I appreciate your guidance and support.
10 Replies
Hi Pavanadamar,
PySpark is always my goto when I need to do anything involving snapshoting data. It's the easiest way to do it for me and it works consistently.
Hi Pavanadamar ,
Thank you for reaching out to the Microsoft Community Forum. For your backlog + aging + trend reporting, Please keep PySpark for snapshot generation and Delta Lake for storage, add Partitioning (snapshot_date),
Incremental snapshot logic and Optimization (OPTIMIZE + ZORDER). Use SQL only for downstream serving layer and Power BI consumption. SQL is good for structured, stable transformations. And PySpark is superior for evolving, complex, large-scale snapshot logic.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Thank you for the feedback. Our implementation follows a Medallion Architecture (Bronze → Silver → Gold), with data loaded into Fabric Warehouse using Copy Job activity. The historical snapshot and aging logic were initially built using SQL Stored Procedures in the Warehouse and later migrated to a PySpark Notebook, which significantly improved debugging, maintainability, and scalability while retaining the Warehouse as the reporting layer.
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_snapshot
ZORDER 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
hlo india
