Real-World Data Engineering Case Studies
Documentation-grade case studies covering business challenges, solution architecture, pipeline design, technology stack, outcomes, and lessons learned — built on Microsoft Fabric and Azure.
Grandeur Properties International
The Global Listing Intelligence Initiative
Client
Ultra-luxury real estate firm — London, Dubai, New York
Industry
Real Estate & Property
Duration
3 phases (Foundation → Integrity → Production)
Grandeur Properties International, founded 1987, is an ultra-luxury real estate firm with flagship offices in London (Mayfair HQ), Dubai, and New York. The firm represents buyers and sellers of properties such as Georgian townhouses in Belgravia, sky villas on the Palm Jumeirah, and full-floor residences above Central Park. A single converted viewing can represent tens of millions of dollars — making timely, accurate portfolio intelligence mission-critical.
The Problem
Analysts manually downloaded nightly listing reports from each office's local CRM, then consolidated them into a master spreadsheet each morning. This introduced 24–48 hours of latency before leadership received a unified portfolio view. A March crisis exposed three compounding failure modes that risked financial loss and legal exposure.
Failure Modes
| Failure Mode | Technical Manifestation | Business Consequence |
|---|---|---|
| Late File | Dubai CRM export delayed two hours; no automated retry or alert existed | Portfolio meeting proceeded without Dubai data; offer status on Palm Jumeirah Sky Villa unknown at decision time |
| Manual Duplication | London analyst duplicated two rows during copy-paste consolidation | Viewing count artificially inflated; capital allocation decision skewed by a phantom engagement signal |
| Version Conflict | New York file overwritten in shared drive by an older version; no version control | New York listing status reverted to prior day's data; not discovered until after the meeting |
| No Audit Trail | No ingestion timestamp on any record; impossible to determine when data entered the system | Firm unable to reconstruct the information landscape at decision time; legal and compliance exposure |
Quantified Pain Points
- 24–48 hours latency on portfolio intelligence
- 10+ hours/week per analyst absorbed in download, alignment, and reconciliation
- 0 audit-ready timestamp coverage on any record
- Two portfolio decisions made on incorrect data during the March crisis
- A £30M listing's offer signal potentially missed due to stale data
Architectural Shift
A Lakehouse-centric architecture within Microsoft Fabric replaces manual consolidation with a three-activity automated pipeline. Data Engineering assumes full accountability for ingestion, validation, and archival. Raw file handling is fully decoupled from business consumption.
| Dimension | Current State | Future State |
|---|---|---|
| Process Ownership | Junior analysts manually download, align, and consolidate three files every morning | Microsoft Fabric Pipeline automatically ingests all office files via wildcard at 6:00 AM UTC daily |
| Data Integrity | No duplicate detection; copy-paste errors go undetected | Upsert on property_id ensures one authoritative record per property |
| Audit Trail | No ingestion timestamp; impossible to determine when data entered the system | Every record stamped with a UTC ingestion timestamp at pipeline execution time |
| File Hygiene | Processed files remain in shared drives indefinitely, creating version conflicts | Processed files automatically archived then deleted from landing zone after every successful run |
| Scalability | Each new office requires a new manual download step and analyst training | New offices onboarded by dropping files into the landing folder; zero pipeline changes required |
Implementation
Pipeline Name: Global Listing Intelligence Pipeline · Schedule: Daily at 6:00 AM UTC. Three sequential activities with On Success dependencies ensure data integrity at every stage.
Pipeline Activities
| Activity | Type | Purpose |
|---|---|---|
| ACT_IngestListingFiles | Copy Data | Wildcard ingest from Files/raw/office_*.csv, upsert to silver_listing_pipeline, add ingestion_timestamp, exclude PII columns |
| ACT_ArchiveListingFiles | Copy Data (On Success) | Copy all processed files from Files/raw/ to Files/archive/ for immutable historical record |
| ACT_DeleteProcessedFiles | Delete (On Success) | Remove all processed files from landing zone to keep it clean for the next run |
Target Schema
| Column | Type | Role |
|---|---|---|
| property_id | String | Upsert Key |
| property_name | String | Mapped |
| listing_price | Decimal | Mapped (local currency) |
| currency | String | Mapped (GBP / AED / USD) |
| enquiries_received | Integer | Mapped |
| viewings_scheduled | Integer | Mapped |
| viewings_completed | Integer | Mapped |
| offer_received | String | Mapped (Y or N) |
| last_refreshed | Date | Mapped (CRM export date) |
| office_code | String | Mapped (LON / DXB / NYC) |
| agent_id | String | Mapped |
| ingestion_timestamp | DateTime | Audit — pipeline-generated via @utcnow() |
| agent_personal_email | EXCLUDED | PII — never enters governed layer |
| internal_crm_ref | EXCLUDED | System artefact — never enters governed layer |
Results Delivered
Victoria Ashworth (Head of Global Portfolio) receives a single, accurate, current view of every active listing across all offices every morning before the 7:00 AM deal meeting — with zero manual intervention.
Before vs. After
Reporting Latency
Analyst Effort
Audit Timestamp Coverage
Duplicate Records
PII in Governed Layer
Key Outcomes
- Reliability — Elimination of manual consolidation errors and missed file incidents via automated wildcard ingestion
- Accuracy — Upsert logic ensures corrected listing data from any office is reflected immediately without duplication
- Compliance — Full audit traceability through an immutable ingestion timestamp on every record
- Scalability — Plug-and-play architecture capable of onboarding new offices (Singapore, Paris) without pipeline changes
- Hygiene — Automated archival and deletion of processed source files prevents reprocessing of stale data
- Governance — PII columns explicitly excluded at the mapping stage; personal contact details never enter the governed layer
Design Reasoning & Edge Cases
Wildcard Specificity is a Defensive Design Choice
Using office_*.csv rather than *.csv prevents non-listing files (archive artefacts, temp files) from corrupting the Silver table. Specificity in the pattern is intentional.
Upsert vs. Append — Snapshot vs. Event Model
Append write mode would result in unbounded row growth with duplicate records per property. Upsert is correct for a nightly snapshot model where the final daily state is the authoritative record.
Two Timestamps Serve Different Audit Purposes
ingestion_timestamp (when the pipeline processed the file) is distinct from last_refreshed (when the CRM exported the record). The pipeline timestamp reconstructs when the system received and acted on the data — critical in legal disputes over offer timelines.
Archive Before Delete is Non-Negotiable
Deleting before archiving would result in permanent data loss if the archive step failed. The On Success dependency from Delete → Archive is the safety gate. Reversing the order creates an irrecoverable scenario.
PII Exclusion at Mapping Stage is the Strongest Control
Excluding columns at the mapping stage means they never enter the governed layer under any circumstance. A downstream filter approach is less reliable — columns could still be written temporarily or cached.
Silent Failures Require Pre-Flight Checks
If London never submits a file, the pipeline succeeds with no error — the wildcard only picks up what exists. A mature architecture requires a missing-file alert or pre-flight check to distinguish "no activity" from "file never arrived."
Global Freight Forwarders
Logistics Data Modernization
Client
Market leader in international logistics — multi-continent operations
Industry
Logistics & Supply Chain
Duration
4 weeks (April 3 – April 28, 2026)
Global Freight Forwarders (GFF) is a market leader in international logistics managing high-velocity supply chains across multiple continents. The Operations Department relies on daily raw JSON shipment logs — one file per shipment event — to monitor carrier performance and delivery timelines. In February 2026, a single missed file triggered a confirmed SLA breach and formal client escalation, exposing the fragility of the existing manual ingestion process.
The Problem
In February 2026, a Customs Hold status update for shipment b174b575 arrived 47 minutes after the analyst had completed the morning consolidation. The update went undetected for 18 hours, causing a confirmed SLA breach and a formal client escalation from Oceanic Freight — resulting in reputational and contractual exposure.
Failure Modes
| Failure Mode | Technical Manifestation | Business Consequence |
|---|---|---|
| No State Management | No watermark exists; every run is a manual judgement call | Duplicate records inflate counts; retrospective corrections undermine credibility |
| Manual Triggering | An analyst must initiate processing each morning; no schedule or dependency management | SLA breaches occur when analyst is delayed, absent, or processes in wrong order |
| File Timestamp Fragility | Ingestion logic relies on OS file modification timestamps, which reset on re-upload | Re-uploaded files silently reprocessed; late-arriving files silently missed |
| No Audit Trail | No record of when files were ingested or which pipeline run processed them | Errors cannot be traced to source vs. ingestion origin |
Quantified Pain Points
- 4–6 hours processing latency from file arrival to report refresh
- 12–15% estimated manual error rate per daily processing run
- 15 hours per week lost to analyst file-shuffling and reconciliation
- 18-hour detection lag during the February Oceanic Freight incident
- 47 minutes — the post-cutoff arrival window that triggered the SLA breach
- 1 confirmed SLA breach with formal client escalation and contractual exposure
Architectural Shift
An automated, incremental data ingestion pipeline within the Microsoft Fabric ecosystem, built around three architectural principles: Incremental (watermark-based state tracking detects only net-new JSON files), Append (Delta Lake preserves every shipment status event as a distinct record), and ACID-backed (Delta Lake guarantees Atomicity, Consistency, Isolation, and Durability on every write).
| Dimension | Current State | Future State |
|---|---|---|
| Ingestion Method | Manual file selection by analyst using OS timestamps | Automated watermark filter; only files modified after the last run |
| State Tracking | None | Watermark table records last successful pipeline trigger time per source |
| Write Mode | Manual copy-paste into spreadsheets; no governance | Append to Delta table; every shipment event preserved as a distinct record |
| Auditability | No ingestion history | Delta Lake transaction log records every write; table restorable to any prior version |
| Scalability | Each new carrier adds a new manual step | New carrier files automatically absorbed within the existing watermark window |
Implementation
Pipeline Name: PL_Incremental_Shipping · Target: ShippingLogs Delta table (Bronze Layer). The pipeline reads the watermark, filters net-new files, appends to Delta, then advances the watermark only on success — ensuring idempotent retry.
Engineering Requirements
| Requirement | Configuration | Justification |
|---|---|---|
| Automated Ingestion | Runs on schedule without manual trigger | Ingests only net-new files since the last successful run |
| State Persistence | Watermark advances only on success | Persists across runs in a durable Delta table |
| Append Semantics | Writes to ShippingLogs use Append mode | Status events preserved as distinct records; full event timeline maintained |
| Idempotency on Retry | A re-run after a failed write must not duplicate rows | The watermark acts as the safety net; failed writes do not advance the marker |
Target Schema
| Column | Type | Role |
|---|---|---|
| ShipmentID | String | Identifier (e.g. eb6ddaad-acdc-47c1-8c8f-8d82f0bb93f5) |
| OriginCity | String | Mapped (e.g. New York) |
| DestinationCity | String | Mapped (e.g. Hong Kong) |
| CarrierName | String | Mapped (e.g. NextDay Air) |
| Status | String | Mapped (e.g. Delayed, In Transit, Delivered) |
| LogTimestamp | Timestamp | Event Time (e.g. 2026-04-03T06:00:00Z) |
Results Delivered
David Rodriguez (Operations Manager) receives a current, trusted view of all shipment statuses backed by an immutable Delta Lake audit trail — with zero manual intervention and full recoverability via time travel.
Before vs. After
Processing Latency
Manual Error Rate
Analyst Effort
Audit Trail
SLA Breach Risk
Volume Scalability
Key Outcomes
- Reliability — Eliminates human error in file selection and duplication via automated watermark ingestion, targeting the 12–15% manual error rate
- Speed — Reduces time-to-insight from hours to minutes; David Rodriguez's 7:00 AM review backed by current data
- Auditability — Full traceability via immutable Delta Lake transaction logs and watermark history; errors traceable to specific runs
- Scalability — Architecture handles 10× growth in daily log volume without pipeline restructure; new carriers absorbed automatically
Design Reasoning & Edge Cases
Append Not Upsert — Preserving the Event Timeline
A shipment may legitimately appear multiple times (In Transit → Delayed → Delivered). Each status change is a distinct event that must be preserved. Upsert would collapse this history into a single row, erasing the operational signal.
Scheduled Pipelines Have an Inherent Latency Floor
A file arriving after the 6:00 AM watermark window will not be picked up until the next scheduled run. This trade-off must be communicated transparently to non-technical stakeholders — the honest answer matters more than an optimistic one.
"Green Status" Does Not Mean Every Byte Arrived
When a new InsuranceValue field was silently added to JSON files, the pipeline showed green in Monitoring — but the field was dropped because it had no corresponding column in the schema. Row-count guardrails and schema drift checks are essential.
RESTORE TABLE Requires a Watermark Reset — In That Order
When test data contaminated the production table, two SQL operations were required in sequence: first RESTORE TABLE to the pre-contamination version, then UPDATE the watermark table to the pre-contamination timestamp. Restoring without resetting the watermark causes legitimate files to be permanently skipped.
Watermark Advancement Only on Success is the Idempotency Guarantee
A failed write must not advance the watermark. If the marker advances before the write completes, a retry will skip the failed files permanently. The watermark is the pipeline's memory — it must only record what was successfully committed.
The Pipeline is the Platform
The closing architectural philosophy: "The pipeline is the platform. Everything else is a small extension on top of it." Schema evolution, row-count guardrails, Silver layer promotion, and missing-file alerts are all extensions — the watermark-based incremental pipeline is the foundation.
Have a Similar Data Challenge?
Let's discuss how Microsoft Fabric and Azure can modernize your data platform.