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.
City of Metropolis
Solving the Last Mile Problem using Microsoft Fabric
Client
City of Metropolis — Municipal Data Engineering Division
Industry
Government & Public Sector
Duration
3 phases (Design → Build → Govern)
The City of Metropolis operates three high-volume municipal data streams — Police incident reports, Parking violation records, and 311 service requests — each arriving as daily flat files in a shared Landing Zone. The Data Engineering Division was tasked with building a single, scalable ingestion framework capable of routing every file to its correct Bronze Delta table without hardcoded pipelines, manual triage, or duplicated logic. The solution: a Metadata-Driven Ingestion Framework built on Microsoft Fabric Data Factory, using dynamic expressions to evaluate filenames at runtime and route each file through a reusable Switch-based architecture.
The Problem
The existing ingestion process required analysts to manually identify each incoming file, determine its department of origin, and trigger the appropriate pipeline. With three departments submitting files on overlapping schedules — and occasional unknown or malformed files arriving without warning — the manual triage process introduced processing delays, compliance gaps, and operational risk.
Failure Modes
| Failure Mode | Technical Manifestation | Business Consequence |
|---|---|---|
| Manual File Triage | Analysts manually inspect filenames and route files to department pipelines | Processing delays of 2–4 hours per batch; analyst bottleneck on every ingestion cycle |
| Duplicate Processing | No deduplication check; re-submitted files processed multiple times | Bronze tables contain duplicate records; downstream Silver and Gold layers corrupted |
| Ghost Files | Unknown or malformed files silently ignored; no quarantine mechanism | Naming violations go undetected; compliance audit gaps; no operational visibility |
| Reporting Delays | Manual routing means files processed sequentially, not in parallel | City leadership receives stale data; SLA commitments to departments missed |
| Compliance Exposure | No audit trail on file processing; no archive of raw source files | Unable to reconstruct ingestion state for regulatory review or incident investigation |
| Operational Inefficiency | Each new department requires a new hardcoded pipeline and analyst training | Onboarding a fourth department (e.g. Fire, Transit) requires weeks of pipeline development |
Quantified Pain Points
- 2–4 hours manual triage latency per daily ingestion batch
- 3 separate hardcoded pipelines — one per department — with duplicated logic
- Zero quarantine mechanism for unknown or malformed files
- No archive of processed source files; zero audit trail for compliance
- Sequential file processing; no parallel execution capability
- New department onboarding requires full pipeline rebuild from scratch
Architectural Shift
A single Dynamic Router Pipeline replaces all three department-specific pipelines. The architecture is metadata-driven: a Get Metadata activity reads the Landing Zone at runtime, a ForEach activity iterates over every file discovered, and a Switch activity evaluates each filename against known department patterns. Matched files are routed to their Bronze Delta table, archived, and deleted. Unmatched files are quarantined. No hardcoded file paths. No duplicated pipeline logic. One reusable framework for all current and future departments.
| Dimension | Current State | Future State |
|---|---|---|
| Pipeline Count | 3 hardcoded pipelines — one per department | 1 dynamic router pipeline handles all departments |
| File Discovery | Manual analyst inspection of Landing Zone contents | Get Metadata activity reads childItems at runtime; zero manual intervention |
| Routing Logic | Analyst determines department and triggers correct pipeline | Switch activity evaluates @item().name; routes dynamically based on filename pattern |
| Unknown Files | Silently ignored; no record of receipt | Default branch routes to Quarantine Zone; full operational visibility |
| Archive & Audit | No archive; processed files remain in Landing Zone indefinitely | Every processed file copied to Archive Zone then deleted from Landing Zone |
| Scalability | New department requires new pipeline, new analyst training, weeks of development | New department added as a new Switch case; no structural pipeline changes required |
Implementation
Pipeline Name: PL_Dynamic_Router · Trigger: Daily schedule or event-based. Single pipeline. Five activity types. Zero hardcoded file paths. The pipeline discovers files at runtime, iterates in parallel, routes by filename, writes to Bronze, archives, and cleans up — all in one execution.
Pipeline Activities
| Activity | Type | Purpose |
|---|---|---|
| ACT_GetMetadata | Get Metadata | Reads LandingZone folder; returns childItems array of all files present at execution time |
| ACT_ForEach | ForEach | Iterates over @activity('Get Metadata').output.childItems; processes each file independently; parallel execution enabled |
| ACT_Switch | Switch | Evaluates @item().name; routes to Police, Parking, 311, or Default (Quarantine) branch |
| ACT_CopyToBronze | Copy Data (per branch) | Copies matched file from LandingZone to corresponding Bronze Delta table with schema enforcement |
| ACT_Archive | Copy Data (On Success) | Copies processed file from LandingZone to ArchiveZone for immutable audit record |
| ACT_Delete | Delete (On Success) | Removes processed file from LandingZone; ensures Landing Zone is empty after every successful run |
| ACT_Quarantine | Copy Data (Default branch) | Routes unmatched files to QuarantineZone; flags for operational review; no data loss |
Target Schema
| Column | Type | Role |
|---|---|---|
| LandingZone | Lakehouse Files Layer | Ingestion entry point — all raw department files arrive here |
| ArchiveZone | Lakehouse Files Layer | Immutable post-processing archive — compliance and reprocessing source |
| QuarantineZone | Lakehouse Files Layer | Isolation zone for naming violations and unknown files — operational visibility |
| Bronze_Police | Delta Table | Police incident records — ACID guarantees, schema enforcement, foundation for Silver |
| Bronze_Parking | Delta Table | Parking violation records — same reusable pattern as Police branch |
| Bronze_311 | Delta Table | 311 service request records — identical architecture, zero duplicated pipeline code |
Results Delivered
The City of Metropolis Data Engineering Division now operates a single, self-managing ingestion framework. Every file arriving in the Landing Zone is automatically discovered, evaluated, routed, written to Bronze, archived, and removed — with zero manual intervention. Unknown files are quarantined rather than silently dropped. The Landing Zone is empty after every successful run.
Before vs. After
Manual Effort
File Routing Accuracy
Landing Zone State
Unknown File Handling
Pipeline Count
New Dept. Onboarding
Key Outcomes
- Automation — Single dynamic router pipeline eliminates all manual file triage; 95% reduction in operational effort
- Accuracy — Switch activity evaluates filenames at runtime; 100% routing accuracy with zero misrouted files
- Governance — Every processed file archived before deletion; full audit trail for compliance and regulatory review
- Visibility — Unknown and malformed files quarantined rather than silently dropped; operational team alerted immediately
- Scalability — New municipal departments onboarded by adding a single Switch case; no structural pipeline changes required
- Reusability — Police, Parking, and 311 branches share identical Copy → Archive → Delete pattern; zero duplicated pipeline logic
Design Reasoning & Edge Cases
Get Metadata Returns childItems — Not File Content
The Get Metadata activity with the "Child Items" field argument returns an array of file name objects, not file contents. The correct dynamic expression to access each filename inside ForEach is @item().name — not @item().path or @item().content. This distinction is critical for Switch routing logic.
Switch Cases Are Exact String Matches — Use Contains() for Partial Matching
Switch activity cases perform exact equality checks by default. If filenames include timestamps or version suffixes (e.g. police_2026_07_19.csv), use @contains(item().name, 'police') in the Switch expression rather than exact match. Exact match will route every timestamped file to Default (Quarantine).
Archive Before Delete is Non-Negotiable — Same as CS01
The On Success dependency from Delete → Archive must never be reversed. Deleting before archiving creates an irrecoverable data loss scenario if the archive step fails. The Archive activity is the safety gate; Delete is the cleanup step.
ForEach Parallel Execution Has a Batch Size Limit
Microsoft Fabric ForEach activity supports parallel execution with a configurable batch count (default: 20). For high-volume Landing Zones with hundreds of files, set the batch count explicitly and test under load. Unbounded parallelism can exhaust Fabric capacity units.
Quarantine is a Feature, Not a Failure
The Default branch routing unknown files to Quarantine is a deliberate governance control, not an error state. A mature architecture treats quarantine as operational signal — every file in QuarantineZone is a naming convention violation that requires a process fix upstream, not a pipeline fix downstream.
One Pipeline for All Departments is a Maintenance Advantage
When the ingestion schedule changes, a bug is discovered, or a new logging requirement is added, the fix is applied once to the single dynamic router. With three hardcoded pipelines, the same change requires three separate deployments — tripling the risk of inconsistency and deployment error.
Have a Similar Data Challenge?
Let's discuss how Microsoft Fabric and Azure can modernize your data platform.