Global Freight Forwarders

Background

This case study explores a Microsoft Fabric–based data engineering solution built for Global Freight Forwarders (GFF), a multi-continent logistics operator whose Operations team was manually sifting through hundreds of raw JSON shipment logs every morning to spot new files — a process that consumed 15 hours of analyst time weekly, introduced a 4–6 hour reporting lag, and ultimately caused a confirmed SLA breach after a critical status update went undetected for 18 hours. The project centers on designing and reasoning through PL_Incremental_Shipping, a watermark-based incremental ingestion pipeline that automatically detects net-new files, appends them into a governed Lakehouse, and advances its own state on each successful run — eliminating manual triggering, duplicate records, and blind spots in the audit trail.

Problem

Manual, unscalable ingestion process — Analysts at Global Freight Forwarders(GFF) spent hours every morning manually inspecting file timestamps to identify net-new shipment log files among hundreds of historical JSON records, with no automated way to distinguish new arrivals from re-uploads.
No state management — The pipeline had no memory of which files had already been processed, forcing every morning run to rely entirely on human judgment rather than a persistent, auditable record.
Fragile timestamp dependency — Ingestion logic relied on OS-level file modification timestamps, which reset on routine operations like copying or re-uploading — making a duplicate file indistinguishable from a genuinely new one.
Significant latency — Time-to-insight ran 4–6 hours from file arrival to report refresh, meaning the daily 7:00 AM operations review was always built on stale, previous-day data.
No audit trail — There was no record of when files were ingested or which run processed them, making it impossible to trace downstream data errors back to their source.

Solution

Fabric workspace setup — Built three core components in Microsoft Fabric to power the pipeline: a Create_Watermark notebook to initialize the watermark tracking table, a PL_Incremental_Shipping pipeline to orchestrate the end-to-end ingestion, and an Update_Watermark notebook to advance the watermark after each successful run.

Create_Watermark notebook — Uses Spark SQL to create the CS02.watermarktable (if it doesn't already exist) with TableName and WatermarkValue columns, then inserts the initial starting watermark for Shipping_Logs so the pipeline has a baseline to filter from on its very first run.

Update_Watermark notebook — Runs a Spark SQL UPDATE statement to set WatermarkValue to the pipeline's trigger time for Shipping_Logs after each successful run, and also includes an ALTER TABLE command adding the InsuranceValue column to Shipping_Logs — the fix applied to resolve the "Silent Column" schema drift issue.

Pipeline flow — The PL_Incremental_Shipping pipeline runs three steps in sequence: a Lookup activity reads the current watermark value, a Copy Data activity ("Incremental Load") uses that value to pull only the net-new shipment log files, and the Update_Watermark notebook then updates the watermark value so the next run knows exactly where to pick up from.

Reading the watermark — The Lookup1 activity connects to the CS02.watermarktable table in the Lakehouse and retrieves the single latest watermark value (using "First row only"), which tells the pipeline exactly where the last successful run left off.

Source (filtering net-new files) — The Incremental Load Copy Data activity reads JSON files from the CS02 folder, using "Filter by last modified" to pull only files between the watermark value from Lookup1 (Start time) and the current pipeline trigger time (End time) — this is the actual mechanism that isolates net-new files.
Destination: (writing to the table) — The filtered files are written into the CS02.Shipping_Logs table using Append mode, ensuring each shipment status update is added as a new row rather than overwriting or merging existing records.

Updating the watermark — The final step calls the Update_Watermark notebook and passes it the pipeline's trigger time as a parameter (PipelineRunTimeStamp), so the notebook can save this as the new watermark value — ensuring the next run only picks up files that arrive after this point.

Lakehouse Environment

Source files in the Lakehouse — Raw shipment log JSON files (both Day 1 and 2) land in the CS02 folder, each named with a unique log ID, timestamped by upload time, and ready to be picked up by the pipeline's incremental filter.

Watermark table result — The watermarktable confirms the mechanism is working: it stores a single row for Shipping_Logs with the latest WatermarkValue (2026-07-11T07:22:32.505Z), the exact cutoff the next run will filter from.

Final Shipping_Logs table — The destination Delta table shows the ingested records with all six core schema columns populated correctly, plus an InsuranceValue column already visible — tying directly into the "Silent Column" challenge in the case study.

Business Impact

Incremental Ingestion Logic: Designed a "new-only" detection logic to only process and analyze the daily set of JSON shipping logs. This helps that older data is not re-loaded again since the total data volume grows, therefore ensuring high performance.
Removed Manual Bottlenecks: Completely automated the manual file identification, with no human error in the logistics process.
Near Real-Time Operational Awareness: Slashed the time taken to arrive at the file and analyze it to enable quicker identification of in-transit and delayed shipments.
Scalable Data Engineering: Developed an "ingestion pattern" for the production environment while keeping the system performing well as the daily shipping log grows in volume for GFF.
Unified Source of Truth: Delivered a single, queryable source of truth for all the world's logistics data to the operations manager in Delta Lake format.