Building the Intelligence Layer for a Regulated Transit Operator

A one-and-a-half-person team can’t run enterprise analytics for a national transit operator. So I built a platform designed not to need me — one whose real value turned out to be giving very different parts of the organization a single picture they could all act on, including the parts that were uncomfortable to look at.

Three years ago that operator had scattered Power BI reports on SharePoint and no data architecture practice. What it had instead: dozens of data sources across many operational systems and several business domains, each with its own format, schedule, and definition of truth. A supervising authority that evaluates the company against contractual KPIs computed on its own methodology — numbers the company sensibly needed to understand and verify on an independent basis of its own. And users at every level, from the CEO to field operations, with no shared understanding of what the numbers meant or who owned them.

The platform got built. But the work underneath it was less the building than a series of decisions, all bound by the same constraint: a one-and-a-half-person team had to carry analytics that would normally take a department. That meant the architecture itself had to supply what the missing people would have — both the engineering muscle to run the pipelines and the trust that kept the numbers a common ground everyone could rely on. These are the decisions that shaped it.

Choosing the platform — and the patterns inside it

Before any of this, there was nothing to build on, which made the first decision the most consequential one. I ran a proof of concept on Azure Databricks, wrote up the evaluation, and recommended it — chosen not as a foregone conclusion but as the platform whose built-in engineering could do the most work on its own.

That criterion drove the choice that mattered even more than the product. Databricks offers declarative pipelines — DLT, now Lakeflow — that handle orchestration, dependency resolution, incremental processing, and data-quality enforcement as part of the framework, rather than as code a team has to write and maintain by hand. It wasn’t the conventional path. The advisory experts leaned toward the more familiar hand-built approach, for understandable reasons: it’s what most teams know and it looks lower-risk on paper. Today the declarative approach is well on its way to being the default; then it wasn’t, and choosing it meant going out ahead of the consensus rather than with it.

Going deep on the declarative model first, then making the case for it, came down to one idea the entire premise of a one-and-a-half-person operation rests on — the tooling has to carry enough engineering on its own that you don’t need a full team to run each part. I committed to the declarative pipelines, and they’re the reason a single person can run ingestion, transformation, and quality across dozens of sources without a pipeline engineer standing behind each one.

One table, not many

Four parties produce four versions of the same reality: the company’s operational numbers, the authority’s numbers, an estimate model’s figures, and manual government files on irregular schedules. Each is legitimate in its own terms, and none agree exactly.

The obvious move is a separate pipeline per source — clean, no conflict. I didn’t do that. I kept all versions in one structure with explicit source labeling, precisely so no single version got quietly privileged over the others. In a single structure, any report can show every version at once, each party can see their own numbers alongside everyone else’s, and the gap between them is visible rather than buried. That even-handed visibility became the platform’s most valuable property — and the next four decisions all follow from it.

Never overwrite a number

The primary system can’t capture every validation event — a known coverage gap. Each uncaptured validation is unrecognized revenue, so an estimate model fills the gap, implemented in the warehouse. Separately, a government portal publishes actuals on its own schedule, and the two figures don’t match exactly. There was a reasonable need from the finance side for confidence that the number was right — and a natural instinct, when two figures disagree, to resolve them into one.

That instinct made sense. The disagreement was only about how. So instead of collapsing the two into a single number, I built what served the underlying need: an ingestion flow for the government’s actual data, pulled in under its own source label. Estimate and actual now sit side by side, traceable, with clear lineage — finance gets the authoritative figure, and nothing gets erased to produce it. The reconciliation question resolved itself, the estimate is maintained independently, and I’m out of that loop. The principle held — new evidence enters as a new source, never as a silent correction — because I gave it somewhere to live, not because I kept insisting on it.

Build so they don’t need you

On a one-and-a-half-person team, being out of the operational loop isn’t a convenience — it’s survival. But it’s also a statement about who knows what: the person closest to a domain understands it better than I do, so the right design gives them infrastructure to act on that knowledge directly rather than routing through me. Revenue estimates get maintained at the source by the person who owns the model; operations managers track daily granularity on their own schedules; finance runs collection-pipeline views independently.

The catch is that self-service only works if it’s safe self-service. Governance — who can see and touch what — is what lets you push ownership to the edge without losing control of it; people run their own parts inside boundaries the platform enforces. That’s what lets the platform run itself without me standing behind each user: trust at the edge, held safe by governance — which frees the architecture function to build the next layer instead of maintaining the last.

A shared picture, including the uncomfortable parts

The outcomes that mattered most weren’t the reports confirming what people already knew. They were the ones that gave separate teams a common view of something that had been fragmented across them.

One category of operational time had been classified inconsistently across the organization — not from any bad intent, just because the data was scattered enough that it had never been seen in one place. Once it appeared in a shared view, the team that owned it refined how it was classified, on their own, without anyone having to make it an issue. Network timing data did something similar across three other functions: a shared picture of the slow segments, so each could ask “what can we improve from our side” without first having to litigate whether the problem was real — and could also demonstrate where they were holding steady, which in a regulated environment has genuine contractual value.

No one got put on the spot. The shared picture was built to exist, and the teams did the rest — which is the version of “surfacing hard things” that actually sticks, because the people closest to the data are the ones who chose to act on it.

Know when to stop

A revenue-collection workflow came to me as a reporting problem: the process spanned several operational systems and a separate case-management system, and no single system gave a complete view of its state. The first build was an operational report across those systems, and when Power BI hit its ceiling I rebuilt it as a Databricks app — four days from zero to a running development version, with no prior app experience.

Then the ask grew: automate the full collection circle with APIs feeding back into the operational systems. And then a bigger version — make the warehouse the single source of truth for the whole process. That one I declined. The warehouse is a read layer; it integrates, aggregates, surfaces. The moment it becomes a write layer — driving state changes in operational systems, owning a revenue-critical process — a one-and-a-half-person team owns something it was never designed to own, and a failure stops being a reporting problem and becomes a revenue problem for the whole company.

The healthier architecture keeps ownership where the expertise already lives: the systems that own each piece of state keep owning it, the warehouse reads from them, and the APIs get built where they belong. The case for that — technical, process, and organizational — went to leadership as a shared picture to work through together, including where the process itself, not the architecture, still needed definition. Naming all of that, openly, is the work that happens before the build.

What I had to unlearn

I started out certain the job was to implement the contract literally — translate each contractual KPI into the obvious calculation and move on, which is exactly how I designed the first version. What I learned, slowly, is that there is no single “literal” reading. The KPI definitions are mathematically ambiguous: the same clause supports more than one valid calculation, and a naive translation just hides that choice while quietly making one for you.

Getting it right wasn’t a problem I could solve alone. It took finance, legal, and domain people in the room, and it took the central users taking ownership of what their numbers actually meant. The implementation moved, over time, from my simplified first reading to an interpretation the company could stand behind and defend — explicit, agreed, grounded in more than one discipline. The lesson stuck: when a definition is contested, the architecture’s job isn’t to pick the easy reading. It’s to make the chosen interpretation visible and defensible.

The thread

Every decision traces to the same constraint: a one-and-a-half-person team carrying what a department normally would. The answer was always to make the architecture supply what the missing people would have — engineering leverage and trust, both.

Choose tooling intelligent enough to carry its own engineering, so no part needs a dedicated team. Don’t privilege one version of truth over another. Don’t erase a number to settle a disagreement — give the new evidence its own place instead. Don’t centralize what domain owners can run themselves. Don’t let a read layer take on a write layer’s responsibility for a process other teams should own.

None of these were one-off good calls; they were the same judgment applied repeatedly, in a way that left the organization more able to coordinate, not more dependent on me. That consistency is also why the architecture held up over time, through the ordinary friction of a busy organization figuring out how to share its data.

At scale, three years in

Users at every level of the organization, daily. Production reporting concentrated on the most vital flows — analytical reports where the decisions actually live, rather than a report for every source. Many business domains integrated. Dozens of source systems, many in conflict, all traceable.

Three years in, the structure has absorbed steady growth — new sources added to existing domains — without a redesign. The one domain that needed reworking needed it because the underlying contract changed substantially, not because the architecture gave way; the top-level design has never required a redesign at all, only incremental improvement as my own experience and the Databricks platform itself advanced. The self-service flows mean the platform runs without me, so I’m building the next layer rather than maintaining the last. And the teams that were most uncertain when their data first appeared in a shared view are now among the ones using it most.

What carries over

For anyone building a system other people have to trust: a small team’s leverage comes from what it chooses, not from how hard it works — pick tools and structures that carry the engineering and the governance themselves, and most of the remaining work is keeping the system an honest common ground, serving each party’s legitimate needs while refusing to let it lie or take on what it shouldn’t own. The judgment isn’t in any single decision; it’s in making the same one consistently, in a way that brings people in rather than routing around them.

And for anyone doing this in a regulated, multi-source data environment specifically: choose a platform whose built-in engineering does the heavy lifting — declarative pipelines over hand-built orchestration — so a tiny team isn’t maintaining each component by hand; model every version of truth as a labeled source in one structure rather than separate pipelines; treat new evidence as a new source instead of a correction; push ownership to the domain edge so the platform runs without you; and keep the warehouse a read layer so it never inherits operational ownership of a revenue process. Those are what let a one-and-a-half-person team carry enterprise analytics without drowning — and they’re the same foundations a closed-loop operational system would need next.