Background
This case study documents the design and build of a Microsoft Fabric pipeline for the City of Metropolis, a fictional municipal data platform struggling with a messy, manual file intake process. Every day, incoming files from departments like Police, Transport, and 311 Services had to be manually checked and sorted, a slow process that often led to files being missed, duplicated, or lost entirely. As the Lead Data Engineer on this project, I built a single automated pipeline that reads incoming files, figures out where each one belongs based on its name, and routes it to the right destination — while automatically filing away anything it doesn't recognize for review instead of letting it disappear. The result is a system that keeps the intake folder clean after every run and gives the team a clear, traceable record of exactly what happened to every file that came in.
Problem
-
Files from different departments (Police, Transport, 311 Services) arrived in a shared folder with no automatic way to sort them — every file had to be manually checked and routed by hand.
-
Simple naming mistakes, like an extra word added to a filename, caused files to be skipped entirely or accidentally processed twice.
-
There was no system in place to track which files had already been handled, so processed files just piled up in the same folder with no clear record of what was done and what wasn't.
-
Files that didn't match any expected naming pattern were simply left behind with no process to flag or review them, meaning they were silently ignored and never made it into the data platform.
-
Each data source had its own separate, manually built process, so adding a new department to the system meant building an entirely new pipeline from scratch rather than just updating a configuration.
Solution
The starting point was a shared "inbox" folder holding 28 files from different departments, all mixed together with names like 311_Service_Requests_20251002_7.csv and Parking_Violations_20251003_8.json, showing the raw, unsorted state of the data before the pipeline sorts and processes it.
Building Data Pipeline
The pipeline starts with a Get Metadata activity pointed at the CS03/inbox folder to pull the list of "Child items" (all the file names sitting inside it), and that list is then fed into a ForEach activity so every single file in the inbox can be checked and routed one by one.
The ForEach activity is set up to loop through the list of files found by the Get Metadata step, using the expression @activity('Get Metadata1').output.childItems, running one at a time in order (Sequential) so each file gets checked and routed on its own before moving to the next.
Inside the loop, a Switch activity checks each file's name and sends it down one of four paths: matching files go to 311_Service_Requests, Police_Incidents, or Parking_Violations (each copying the file to its correct folder, archiving a copy, then deleting it from the inbox), while any file that doesn't match a known pattern falls into the Default case, which archives it to quarantine and removes it from the inbox — making sure every single file is dealt with, with nothing left behind.
The exact rule behind the sorting is a set of nested checks on each file's name and file type — a file starting with "Police_Incidents" and ending in ".csv" is labeled Police_Incidents, one starting with "Parking_Violations" and ending in ".json" is labeled Parking_Violations, one starting with "311_Service_Requests" and ending in ".csv" is labeled 311_Service_Requests, and anything that doesn't match any of these patterns falls through to Default, which is what sends it to quarantine.
-
The "Copy 311 Service Request" step picks up matching files from the inbox using the wildcard pattern 311_Service_Requests_*.csv, so any file with that name pattern in the CS03/inbox folder gets picked up automatically, no matter what date or number follows in the name.
-
The file's data is then copied straight into the CS03.ServiceRequest table (in the Bronze layer), using "Append" mode so each new load simply adds on to the existing records instead of overwriting what's already there.
-
The "Archive 311 Service Request" step takes the same matching files from CS03/inbox (using the same 311_Service_Requests_*.csv pattern) so nothing gets missed between the two copy steps.
-
This time, instead of loading the data into a table, the files themselves are copied as-is into a CS03/archive/311ServiceRequest folder, keeping a permanent backup copy of every original file that was successfully processed.
The final "Delete 311 Service Request" step runs only after the file has been successfully loaded into the table and archived, and it targets the same matching files in CS03/inbox (again using the 311_Service_Requests_*.csv pattern) to remove them from the inbox — completing the cycle so the inbox stays clean and nothing is deleted before it's been safely copied and backed up.
PLEASE NOTE -> Above operations (copy data, archive and delete) are similarly implemented for 'Police_Incidents.csv' files as well
-
The "Copy Parking Violations" step picks up matching files from CS03/inbox using the wildcard pattern Parking_Violations_*.json, reading them in as JSON format since this department's data comes in a different file type than the other two sources.
-
The data is then loaded into the CS03.ParkingViolations table using "Append" mode, adding each new batch of records onto the existing table without overwriting anything already there.
-
The "Archive Parking Violations" step picks up the same matching files from CS03/inbox using the Parking_Violations_*.json pattern, running right after the data has been loaded into the table.
-
Instead of loading the data anywhere, the original JSON files themselves are copied as-is into a CS03/archive/ParkingViolation folder, keeping a backup of every original file that was successfully processed.
The final "Delete Parking Violations" step runs only after the matching files have been safely loaded into the table and archived, then clears the same Parking_Violations_*.json files out of CS03/inbox — completing the cycle for this file type and keeping the inbox empty.
-
This is the Default path of the Switch, used for any file that doesn't match one of the three known naming patterns — the "Archive Quarantine Files" step takes that specific file (using @item().name to grab its exact name from the loop) straight from CS03/inbox and copies it into a CS03/quarantine folder for review.
-
Right after that, the "Empty Inbox folder" step deletes that same unmatched file from CS03/inbox, so even files the pipeline doesn't recognize still get moved out and safely stored rather than being left behind or lost.
Lakehouse Environment
After the pipeline runs, the CS03/archive folder shows three neatly separated subfolders — 311ServiceRequest, ParkingViolation, and PoliceIncident — proving that every successfully processed file was correctly sorted and backed up by domain, exactly as designed.
The CS03/quarantine folder shows exactly what the Default branch was built for — files like analysis_v2.docx, department_summary_notes.txt, meeting_agenda.pdf, and temp_data.zip that don't match any known naming pattern were automatically caught and moved here instead of being lost or left cluttering the inbox, proving the safety net works as intended.
After the pipeline finishes running, the CS03/inbox folder is completely empty, confirming the end goal of the whole design: every single file that started in the inbox was correctly routed, archived, and either loaded into a table or sent to quarantine, with nothing left behind.
Outcome
-
Removed the need for manual file checking every day, freeing up time that was previously spent sorting files by hand so it can go toward more valuable work.
-
Cut down on data errors by making sure files are only loaded once and never skipped, since the pipeline recognizes and processes each file consistently every time.
-
Made the system easy to audit, since every file's journey — whether it was successfully processed or sent to quarantine — is tracked and stored, giving a clear record of what happened to every file.
-
Built in a safety net for unexpected files, so instead of being lost or ignored, anything unrecognized is automatically set aside for review rather than silently disappearing.
-
Created a scalable foundation, since new file types or departments can be added by simply updating the routing rules rather than rebuilding the whole pipeline from scratch.