Anjali

← All case studies

  • Microsoft Fabric
  • Delta Lake

Logistics Data Modernisation

A watermark-based ingestion pipeline, and the operational discipline of letting the pipeline remember what the analyst no longer needs to.

Role
Independent case study
Year
2026
Stack
Microsoft Fabric · Data Factory · Delta Lake · SQL · Notebooks

Designed and implemented an incremental data ingestion pipeline using Microsoft Fabric to modernise logistics data processing for a global freight company. The solution automated ingestion of high-volume JSON shipment logs using a watermark-based state management approach, eliminating manual file selection, reducing reporting latency, and ensuring reliable, auditable data ingestion into a Delta Lake architecture.

Project scope

Developed an end-to-end incremental data ingestion pipeline that automates JSON file processing, tracks ingestion state using watermarks, stores data in Delta Lake, and delivers reliable, scalable, and auditable data for downstream analytics.

Objectives

  • Automate incremental data ingestion.
  • Process only new shipment files.
  • Eliminate duplicate and missed records.
  • Improve data reliability and auditability.
  • Reduce reporting latency through automated pipelines.

Challenges

  • The Post-Trigger File
  • The Silent Column
  • The Contaminated Table

Solution

Data pipeline architecture and workflow

  • Initialize State: looked up the previous watermark timestamp to identify the exact starting point for data extraction.
  • Incremental Load: filtered and extracted only newly arrived data based on the watermark, appending unique records to the master file.
  • Advance Watermark: automated a query to update the watermark log with the pipeline’s trigger timestamp post-execution.
Fabric pipeline canvas showing a Lookup activity named Lookup_prev_watermark chained on success to a Copy data activity named PL_Incremental_Shipping, chained on success to a Notebook activity named Update_Watermark.View full size
The three activities in order. Read the watermark, load what is newer than it, then write the new watermark back.

Steps involved in setting up the pipeline architecture and workflow

The whole design rests on one idea: the pipeline keeps a durable record of how far it has read, so no human has to remember which files were already processed.

1. Create the watermark log table (SQL)

Designed a SQL table to track the last processed timestamp and manage the pipeline’s incremental state.

Notebook cell containing CREATE TABLE IF NOT EXISTS CaseStudy02.watermarketable with columns Tablename of type string and watermarkvalue of type timestamp.View full size
Two columns are all the state the pipeline needs: which table, and how far it has read.

2. Seed the baseline watermark

Executed a SQL INSERT statement to populate the initial timestamp value, establishing the starting baseline for the incremental load.

Notebook cell containing INSERT INTO CaseStudy02.watermarketable with values 'ShippingLogs' and TIMESTAMP('2026-07-10 00:00:00').View full size
The starting line. Everything dated after this first run gets picked up.

3. Automate watermark updates

Configured an UPDATE query to execute at the end of each successful pipeline run, advancing the timestamp to log the new state.

Notebook cell containing a spark.sql UPDATE statement setting watermarkvalue to TIMESTAMP of the PipelineRunTimeStamp parameter where Tablename equals 'ShippingLogs'.View full size
The update runs only at the end of a successful run, so a failure leaves the old watermark in place and the same files are retried.

4. Configure the Lookup activity

Set up a Lookup activity against the watermark log table to retrieve the last successfully processed watermark timestamp. This establishes the starting point for the current pipeline run.

Fabric Lookup activity settings: connection AnjaliLakehouse, root folder Tables, table CaseStudy02.watermarketable, with First row only checked.View full size
The Lookup reads a single row from the watermark log table, the pipeline's entire memory of where it left off.

5. Configure the incremental data flow

Set up the core extraction logic, via a Copy or Data Flow activity, to filter the source data using the retrieved watermark timestamp. This ensures the pipeline pulls only newly arrived files and cleanly appends them to the main Delta Lake table without duplication.

Fabric Copy data activity named PL_Incremental_Shipping, source tab set to file path CaseStudy02 with Recursively checked and file format JSON.View full size
The incremental copy activity reading JSON shipment logs recursively from the landing path, filtered by the watermark retrieved upstream.

6. Configure the watermark update (Notebook activity)

Set up a Notebook activity parameterised to receive the dynamic pipeline trigger time. Upon successful data ingestion, the notebook executes a script to update the watermark log table with this new timestamp, resetting the baseline for the next run.

Fabric Notebook activity settings: workspace Group 5B, notebook AnUpdate_watermark, and a base parameter named PipelineRunTimeStamp of type String set to @pipeline().TriggerTime.View full size
The trigger time is passed in as a parameter rather than read inside the notebook, so the watermark records when the run started, not when the notebook happened to finish.

The scenarios that test the design

Challenge 01

The Post-Trigger File

It is 6:03 AM. The pipeline has run. At 8:47 AM, a carrier uploads a new file containing the status update David needs for his 9:00 AM report. What happens?

No, it will not be picked up by today’s pipeline. Assuming the pipeline operates on a daily morning schedule, it already completed its run at 6:03 AM. Because the pipeline is triggered by a schedule rather than by an event such as a file landing, it is currently inactive. The file will sit safely in the landing zone and will be ingested during tomorrow’s scheduled run, at 6:00 AM.

This is a real limitation, not a defect: the fix is an event-based trigger, and the trade-off is cost and run frequency against reporting freshness. Naming it explicitly is what lets the business choose.

Challenge 02

The Silent Column

The carrier integration team adds an InsuranceValue field to all JSON files over the weekend. Monday's pipeline succeeds without error. The field is nowhere in the table.

The pipeline succeeded because it was never told to expect the field. The write matched the existing schema and quietly dropped the rest. The recovery is to update the schema: execute a SQL command against the destination Delta table to add the missing InsuranceValue field, then backfill from the archived source files.

The broader lesson is that a green pipeline run is not the same as correct data. Schema drift is silent by default, which is exactly why it needs an explicit check.

Notebook cell containing ALTER TABLE AnjaliLakehouse.CaseStudy02.ShippingLogs ADD COLUMN InsuranceValue FLOAT.View full size
The schema fix. One statement adds the column, after which the archived files can be reprocessed to fill it in.

A third scenario, The Contaminated Table, covers recovery once bad data has already been appended to the Delta table.