top of page
for_landscape_copy.png

Background

This case study documents the design and delivery of a production-grade data ingestion pipeline in Microsoft Fabric for Grandeur Properties International, an ultra-luxury real estate firm operating across London, Dubai, and New York. Faced with a fragmented, spreadsheet-driven process that left leadership making high-stakes portfolio decisions on data up to 48 hours stale, the project set out to build a fully automated Lakehouse pipeline: wildcard-based ingestion across regional offices, upsert logic to guarantee one authoritative record per property, immutable audit timestamps for compliance, PII exclusion by design, and automated archival to keep the landing zone clean.

Problem

  • Manual, spreadsheet-based consolidation — Analysts downloaded nightly CSV exports from three separate office CRMs (London, Dubai, New York) and manually merged them into master spreadsheets, introducing a 24–48 hour latency before leadership saw a unified view of portfolio activity. 

  • No duplicate detection — A London analyst duplicated rows during copy-paste consolidation, artificially inflating viewing counts and skewing capital allocation decisions. 

  • No version control — The New York file was silently overwritten by an older version in a shared drive, reverting listing status without anyone noticing until after the deal meeting had concluded. 

  • No automated alerting for late files — A delayed Dubai CRM export left leadership walking into the 7:00 AM meeting with an unknown offer status on a major listing, with no retry or notification mechanism in place. 

  • Zero audit trail — No ingestion timestamp existed on any record, making it impossible to reconstruct the information landscape at the moment a portfolio decision was made — creating legal and compliance exposure.

Solution

​Set up the Fabric workspace with a Lakehouse named LH1_Grandeur and a Pipeline named PL_GrandeurListingIngestion to handle the ingestion, upsert, archive, and delete logic.

Built the pipeline as three connected activities running in sequence — IngestListingFiles (Copy data) → ArchiveListingFiles (Copy data) → DeleteFiles (Delete data) — where each step only runs after the previous one succeeds.

Ingest Listing Files

  • Source: Configured the IngestListingFiles activity to read from the Lakehouse Files section using a wildcard path (CSO1/Landing/office_*.csv) with recursive search enabled, and added an extra column Ingestion_Timestamp set to @utcNow() to timestamp every record on arrival.

  • Destination: Set the output to the CSO1.Grandeur_Properties table with Upsert as the table action, using property_id as the key column to ensure records are updated in place rather than duplicated.

  • Mapping: Mapped all 12 source columns directly to matching destination columns — with PII fields like agent email and CRM reference left out entirely.

  • Source: Configured ArchiveListingFiles to read the same wildcard-matched CSV files (CSO1/Landing/office_*.csv) that were just processed by the ingest step.

  • Destination: Set the output path to CSO1/Archive as files (not tables), preserving the original CSV format so processed files are kept as an immutable historical record.

  • Result: This activity effectively copies each night's raw files from the Landing folder into the Archive folder, right before the Delete step clears them out of Landing.

Configured the DeleteFiles activity to remove all CSV files (*.csv) from the CSO1/Landing/ folder using a wildcard file path, so the landing zone is cleared out and ready for the next night's files after archival is confirmed.

Lakehouse Environment

Landing folder: After a successful run, the Landing folder in the Lakehouse is empty, confirming the Delete activity worked as expected and the zone is clean for the next day's files.

Archive folder: All six source files (Day 1 and Day 2 for London, Dubai, and New York) are safely preserved in the Archive folder, proving the immutable historical record is intact.

Silver table: The Grandeur_Properties table shows exactly 9 rows (one per property, no duplicates) with every record carrying a populated Ingestion_Timestamp, confirming the upsert logic and audit trail are both working correctly.

Business Impact

  • Eliminated decision latency — Replaced a 24–48 hour manual consolidation delay with an automated pipeline that delivers a single, trusted view of all listings by 7:00 AM daily, so that the stakeholder can walk into the deal meeting with current data every morning.

  • Guaranteed data accuracy — Upsert logic on property_id ensures corrections from any office update the existing record instantly, eliminating the duplicate rows and stale overwrites that previously skewed portfolio decisions.

  • Built audit-ready compliance — Every record now carries an immutable Ingestion_Timestamp, giving the firm the ability to reconstruct exactly what leadership saw at the moment any decision was made — closing the legal and compliance gap.

  • Freed up analyst capacity — Removed the 10+ hours per week analysts spent on manual downloading, aligning, and reconciling files, redirecting that time toward higher-value portfolio analysis.

  • ​Future-proofed for growth — New offices like Singapore and Paris can be onboarded simply by dropping files into the landing folder, with zero pipeline changes required, making the architecture scalable by design.

bottom of page