AnjaliData Analyst → Data Engineer

← All case studies

  • Microsoft Fabric
  • Data Factory

The Global Listing Intelligence Initiative

Replacing nightly spreadsheet consolidation with a pipeline that onboards a new office without a code change.

Role
Independent case study
Year
2026
Stack
Microsoft Fabric · Lakehouse · Data Factory · Copy Data · Incremental upsert

Designed and implemented an end-to-end Microsoft Fabric data engineering solution for a global luxury real estate company to automate property listing ingestion from multiple regional offices. The solution replaced manual spreadsheet consolidation with a scalable, governed, and auditable pipeline, delivering reliable, near real-time data for business intelligence and executive decision-making.

Project scope

  • Designed a Lakehouse-centric data engineering architecture for centralised property listing management.
  • Automated ingestion of nightly CSV files from London, Dubai, and New York using wildcard-based pipelines.
  • Built an end-to-end ETL pipeline to validate, transform, and load data into a governed Silver layer.
  • Implemented upsert logic to maintain a single authoritative record per property.
  • Added audit timestamps, PII exclusion, automated file archival, and landing zone cleanup to ensure governance and data quality.
  • Delivered a production-ready pipeline with automated scheduling, monitoring, and scalability to support future office onboarding without structural changes.

Pipeline architecture

The pipeline executes three activities sequentially:

  1. Data_factory (Copy data) — ingests and updates data from the Landing folder into the Main Data File.

  2. Archive (Copy data) — moves the processed files from the Landing folder to the Archive folder for historical tracking.

  3. Delete data (Delete) — clears all remaining files out of the Landing folder to reset it for the next run.

Fabric pipeline canvas showing Copy data 'Data_factory' chained on success to Copy data 'Archive', chained on success to a Delete data activity. Each activity displays a green success indicator.
The three activities chained on success. Ingest, archive, then clear the landing zone — so a failed ingest never leaves files half-processed or the folder in an ambiguous state.
Fabric Copy data configuration: source tab set to a wildcard file path of CaseStudy01/Landing with pattern office_*.csv in DelimitedText format, and destination tab writing to CaseStudy01/Archive.
Source configured with the wildcard path office_*.csv against the landing folder, and the archive copy writing to CaseStudy01/Archive. The wildcard is the single design decision that makes the next four scenarios non-events.
Copy data destination tab with Table action set to Upsert and property_id chosen as the key column, alongside the mapping tab listing source-to-destination column pairs including property_id, listing_price, office_code and ingestion_timestamp.
Table action set to Upsert with property_id as the key column. This is what makes a re-dropped or corrected file update the existing property rather than duplicate it — and the explicit column mapping is what keeps a renamed source column from silently landing in the wrong place.

The scenarios that test the design

Anyone can build a pipeline that works on the data it was written for. These are the questions I used to check whether this one would survive contact with a real business.

Challenge 01

The Singapore Expansion

A new office begins dropping files into the landing zone next Monday. Does the pipeline need to be modified before then? You have not changed it since go-live.

No, the pipeline does not require any modifications. The architecture was designed to be fully dynamic and scalable from day one.

  • Wildcard file matching. The source configuration in the copy activity uses a wildcard file name pattern (e.g. office_*.csv). When the new office drops its data into the landing zone, the pipeline detects and ingests the new file without manual code changes.
  • Upsert logic. The sink configuration uses an upsert mechanism. When the new data is processed the next morning, the pipeline cleanly inserts the new property records and updates any existing ones in the Main Data File without creating duplicates.
  • Automated cycle management. The downstream Archive and Delete activities process, back up, and purge the new file sequentially, exactly like the existing office files.
Challenge 02

The Late Correction

Dubai contacts you at 11:30 PM to report an incorrect listing price already sitting in Silver. They drop a corrected file with a different filename. What happens when you run it?

The pipeline picks up the corrected file and updates the records seamlessly, without manual intervention.

  • Wildcard file matching. Because the source uses a dynamic wildcard pattern, the pipeline is not locked to a specific, rigid filename. It recognises and ingests the newly dropped correction file during the next execution cycle.
  • Target upsert mechanism. The sink uses upsert logic keyed on the unique Property ID. Instead of creating duplicate rows or failing on a naming mismatch, the pipeline matches the keys and cleanly overwrites the incorrect listing price with the corrected values.
  • Clean state maintenance. After successful ingestion and archival, the Delete activity purges the correction file from the landing zone, resetting the directory for the next scheduled run.

Three further scenarios shaped the same design — The Vanishing Column, The Broken Chain, and The Missing Office — each pushing on schema drift, partial failure, and silent absence of expected input respectively.

Technologies used

Microsoft Fabric · Lakehouse · Data Factory pipelines · Copy Data activity · Incremental upsert · Pipeline scheduling · Data validation and audit logging