top of page

Background

This case study tackles a performance and reliability challenge in a multinational finance division's accounts receivable pipeline. The daily process, which joins 45 million transaction records against a 15,000-row customer master table, had degraded to a 2-hour runtime and was silently allowing corrupted data into executive dashboards, with no way to audit or reverse the damage. Using Microsoft Fabric, I redesigned the pipeline with a broadcast join to eliminate the network shuffle, Delta Lake schema enforcement to reject bad data before it lands, and Time Travel with RESTORE to recover from any bad load in a single command. 

Problem

  • ​The daily accounts receivable pipeline took over two hours to run, well beyond the acceptable window, delaying morning financial reporting for the entire finance division.

  • The pipeline merged 45 million daily transaction records with a much smaller 15,000-row customer master table, but the join process forced massive, unnecessary data movement across the network.

  • The system had no data validation in place, allowing invalid text values to silently overwrite numeric currency fields. 

  • Corrupted data was reaching executive dashboards, eroding confidence in financial reporting at the board level. 

  • There was no audit trail or version history, meaning that once bad data landed, the only fix was a manual, time-consuming recreation of the table from source files.​​

Solution

Uploaded and organized the source datasets in the Fabric Lakehouse, including the 45M-row AR_Transactions file, the Customer_Master reference table, and additional files for testing schema enforcement and error handling.

Created a dedicated Fabric notebook (Notebook_CS05) to write all the PySpark logic for diagnosing, optimizing, enforcing and recovering the pipeline in one place.

  • Imported the required PySpark libraries to handle data transformations and defined explicit schemas for both the transactions and customer master tables, setting clear rules for each column's data type. 

  • Loaded the AR_Transactions and Customer_Master CSV files into DataFrames using these enforced schemas, ensuring the data was structured correctly right from the point of ingestion.

Checked the row counts of both DataFrames to confirm the full 45 million transaction records and the 15,000 customer master data loaded correctly before any transformation began.

Set a Spark broadcast join threshold, telling the engine that any table under this size limit should be automatically broadcast to every worker node instead of being shuffled across the network.

Joined the transactions and customer master tables on CustomerID and used explain() to view the query plan, confirming the broadcast join was applied and the network shuffle was eliminated.

Saved the customer master table in Delta format as a managed table, laying the groundwork for schema enforcement and version tracking in later stages.

Queried the newly saved Delta table using SQL to confirm the data was stored correctly and accessible for further validation.

Loaded a sample file containing bad schema records to test how the pipeline handles data that doesn't match the expected structure.

Simulated a bad data update by intentionally overwriting the Credit_Limit column with an invalid value, creating a "corrupted" version of the table for testing recovery.

Queried the customer master table to confirm the bad update had actually landed and changed the live data.

Used DESCRIBE HISTORY to view the Delta table's full version log, identifying every change made to the table and pinpointing the version just before the bad update.

Used Time Travel to query the table exactly as it existed at Version 0, confirming the original, correct data was still recoverable from the transaction log.

Ran the RESTORE command to roll the customer master table back to Version 0 in a single step, instantly undoing the bad Credit_Limit update.

Queried the live table again to confirm the restore worked, with the original correct data back in place and the corrupted values fully reversed.

Business Impact

  • Faster reporting: Eliminating the network shuffle through a broadcast join cut the daily AR pipeline runtime from two hours down to minutes, giving the finance team timely access to accurate cash flow numbers each morning. 

  • Reliable data: Delta Lake's storage format and structured schemas ensured data was loaded consistently, reducing the risk of invalid or corrupted values reaching executive dashboards. 

  • Full recoverability: With Delta Lake's transaction history, any bad data update could be identified and reversed in a single command, removing the need for manual, time-consuming table recreation. 

  • Stronger auditability: Every change to the customer master table is now tracked with DESCRIBE HISTORY, giving the business a clear, timestamped record for compliance and audit purposes. 

  • Increased trust in reporting: With a faster, safer, and fully recoverable pipeline, the CFO and finance leadership can rely on the daily AR aging report to guide credit decisions and board-level reporting with confidence.​​

bottom of page