top of page
Data Pipeline.png

Streamlining Departmental Data Flows for Metropolis: A Smart Lakehouse Pipeline in Microsoft Fabric

Project Overview​

The City of Metropolis launched a digital transformation initiative to unify public sector data across departments like Police, Transportation, and Citizen Services. A shared “data inbox” was introduced as a central drop zone for all reports and files — aiming to simplify data collection and analysis.

However, rapid adoption led to data chaos. The inbox became cluttered with hundreds of files in varying formats (CSV, JSON, Excel, PDF) and inconsistent naming conventions. Analysts spent hours manually identifying and loading data, undermining the promise of automation.

The tipping point came when a critical enforcement report went unnoticed for days due to naming inconsistencies, exposing a major gap in the city’s data ingestion process — the “last mile” where data enters the system.

​

Objective

The data engineering team faced a deceptively complex challenge that goes to the heart of modern data platform design. They needed to architect a solution that could operate autonomously, handling the unpredictable nature of real-world data ingestion without constant human supervision. The core requirements were clear but technically demanding:

​

Solution Overview​

To overcome the ingestion challenge, I designed a Dynamic Router Pipeline using Microsoft Fabric — an intelligent, no-code workflow that automated the classification, routing, and processing of incoming files.

The solution leveraged Fabric’s orchestration capabilities through a seamless combination of Get Metadata, ForEach, and Switch activities to achieve end-to-end automation:

  • Discovery: The pipeline scans the inbox, retrieves file details, and prepares them for processing.

  • Iteration: Each file is handled independently, enabling parallel and fault-tolerant execution.

  • Classification & Routing: Files are matched to departments using naming patterns and routed to their respective Lakehouse tables.

  • Archiving & Quarantine: Processed files are archived automatically, while unrecognized ones move to quarantine for review.

This approach eliminated manual sorting, reduced human dependency, and ensured reliable data governance through built-in audit trails, monitoring, and version control — all within a visual, maintainable, and extensible pipeline design.

​

2.4.png

​​​

Impementation
​

2.1.png

Discovery Phase

The Get Metadata activity scans the inbox folder and retrieves a comprehensive list of all files present, along with critical metadata including filename, size, modification timestamp, and file extension.​

​

Iterative Processing

A ForEach loop processes each discovered file individually, ensuring that failures in processing one file don't impact others and allowing for parallel processing when appropriate.

Pattern Matching

Within the loop, the pipeline uses filename pattern recognition based on regular expressions and naming conventions to classify each file by source department and data type.

​

​

The Intelligent Routing

The Switch activity evaluates the classification and routes the file to the appropriate Copy Data activity, which loads it into the correct Lakehouse table with proper schema validation.

​

Cleanup and Archiving

Successfully processed files are automatically moved to an archive folder with date-based organization, while unrecognized files are sent to quarantine for manual review.

2.3.png

​

3.

​

3.5.png

Result

The project resulted in the creation of department-specific Delta tables within the Lakehouse, automatically generated and updated through the Dynamic Router Pipeline. Successfully processed files are seamlessly archived for record-keeping, while unrecognized files are moved to quarantine, ensuring the Inbox remains clean, organized, and always up to date.​

Business Impact​​

The deployment of the Dynamic Router Pipeline transformed data operations across Metropolis — turning a manual, error-prone process into a fully automated and trusted data workflow.

Key Outcomes:

  • Hours Saved: Manual file processing completely eliminated, freeing analysts to focus on analytics and insights.

  • Accuracy: Zero missed or duplicate files since implementation, ensuring complete data integrity.

  • Scale: Over 500 files processed daily across departments without human intervention.

  • Speed: Most files are available for analytics within 15 minutes of arrival.

  • Governance & Transparency: Built-in monitoring, audit trails, and version control enhance trust and compliance.

  • Beyond efficiency gains, the system proved its real-world value when it automatically quarantined a misnamed parking enforcement file — preventing critical data loss and enabling the city to act swiftly on a major traffic issue.

bottom of page