Data Infrastructure & Backfill
The dashboards everyone saw were sitting on top of something quieter: one warehouse that pulled five separate operational systems into a single place, with years of history loaded in behind it. This is that backbone. For a national 3PL, I built the pipelines, the schema, and the historical backfill that everything else read from.
The problem
The company's data lived in five different tools that did not talk to each other. The warehouse management system held operations and worker activity. A help desk tool held support tickets. The CRM held deals and accounts. A workforce tool held labor and scheduling. A project tracker held engineering work. Each one had its own login, its own export format, and its own idea of what a date or an account even was. Asking a single question that crossed two of them meant pulling exports by hand and stitching them together in a spreadsheet, which nobody had time to do or trust.
As one stakeholder put it, they had never really had good reporting. The reason was not a lack of dashboards. It was that there was no single, clean place for the data to live.
What I built
I stood up one PostgreSQL warehouse (on Neon) as the single source of truth, and built Python pipelines to feed it from each system on a nightly schedule.
- One warehouse, five sources. Pipelines pull from the warehouse management system, the help desk, the CRM, the workforce tool, and the project tracker, each one normalized into a shared schema so the data lines up.
- A modeled schema, not a dump. Fact tables for the events, dimension tables for accounts and dates, plus an ETL log table so I could see exactly what loaded and when. SQL views sit on top so the dashboards read clean shapes instead of raw source quirks.
- Versioned migrations. Schema changes ship as numbered SQL migrations, so adding a new module later is a controlled step, not a rewrite.
- A bulk-load path for big history. The warehouse system's live API was rate-limited and too slow for backfill, so I loaded history from its bulk data export (parquet files) instead, and kept the API for smaller incremental pulls. Same loader, two speeds.
The backfill
A warehouse with only today's data is not much use. The harder part was loading the past. When I extended the loader to bring in worker activity and operational events, a single backfill drop carried roughly 19 months of history into the warehouse retroactively, tens of thousands of activity records and the alerts tied to them.
That history is what makes the numbers mean something. You cannot spot a trend, set a baseline, or tell a good week from a bad one with a week of data. Loading the past is what turned a live feed into something you could actually reason about.
One careful call: where labor cost comes from
Two of the systems both reported on the same workers, but only one of them held trustworthy pay data. The warehouse system technically had an hourly-rate field, but in this company's instance it was barely maintained and full of junk values, so I deliberately did not load it. Instead I used the workforce tool as the source of truth for labor cost, and used the warehouse system only for the link between a worker and what they actually did. The two systems join on worker identity, so you get reliable cost from one and reliable activity from the other, without ever trusting a bad field.
That is the kind of decision this work is full of. Not every field that exists is a field you should use.
Why it matters
This is the layer nobody sees and everything depends on. The KPI dashboards, the executive views, the operational drill-downs, all of them are just queries against this warehouse. None of them could exist if the data were still scattered across five tools in five formats. Consolidating the systems, modeling the schema, and loading the history is the unglamorous part, and it is the part that made the rest possible.