Historical Backlog Snapshots in Microsoft Fabric - SQL vs PySpark

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