What it is
Medallion Hub is a consolidated data warehouse built on the Medallion architecture, the now-common industry pattern where data moves through three named layers: raw data lands in bronze, validated and typed data in silver, and business-ready aggregates in gold. Each layer is only allowed to read from the one below it, which is what makes the lineage of any number traceable back to its source.
It replaces an existing nightly pipeline of stored procedures with transformation logic that lives in version control as plain SQL and configuration files, testable and reviewable like any other code.
Why it mattered
The pipeline it replaces works, and has worked for years. That is worth stating plainly, because the reason for replacing it is not that anyone built it badly. It is that all of the business logic lives in stored procedures inside a database, layered from level zero through level seven, each reading from the layer below and writing physical tables that reports then query directly. Logic in that form is difficult to review, nearly impossible to test in isolation, and cannot be diffed meaningfully between versions.
There is also a much larger reporting estate sitting on top of it than the pipeline’s own documentation suggested, five hundred thirty-two views across the reporting database, one hundred forty-nine pointers that reports resolve through, and, as I discovered by direct measurement, two hundred ninety-five stored procedures in the source database where the documentation mentioned only twenty-nine. Any migration approach that assumed the documented inventory would have been planning against the wrong numbers.
Choosing the transformation tool
The first real decision was what actually runs the transformations. The obvious institutional answer was the Microsoft integration tooling already used for the existing pipeline, which the team knows and which is already licensed and running.
I chose dbt Core instead, for a reason specific to how this work gets built. dbt models are plain SQL files with configuration in YAML, nothing else. There is no visual designer, no proprietary package format, and no binary artifact. That matters because it makes the entire transformation layer legible to an AI-assisted workflow: a model can be generated, reviewed in a diff, corrected, and re-generated as ordinary text. The existing tooling stores its logic in a packaged format that resists exactly that kind of iteration.
Given that one hundred twenty-two of one hundred forty models turned out to be mechanical pass-throughs, choosing the format that generates and reviews cleanly was worth more than choosing the one already installed.
The cost this is meant to remove
The reason for doing this is best explained through something that happens in every data team. Somebody asks for a quick data pull, it sounds like twenty minutes, and days later it is still going.
Here that happens for reasons I can put numbers to. A request has to resolve through roughly three hundred eighty-three business logic views before it reaches a figure, and the definition of any given term lives inside whichever one of them someone wrote for a specific purpose years ago. There is no catalogue. The existing documentation mentioned twenty-nine stored procedures; I counted two hundred ninety-five. Schemas change without announcing it, and one pair of tables turned out to be missing eighty-nine columns, which would have broken reports silently rather than loudly. Much of the interesting data is protected health information, so it cannot simply be queried and shared. And the business logic is expressed as three hundred stored procedures, which cannot be diffed, tested in isolation, or reviewed.
None of that is a failure of estimation or communication. It is a foundation problem, and it recurs on every request because the foundation does not change between them. The useful question is not whether a given pull can be done, but whether the work should keep being done this way.
The approach
The plan I settled on inverts the obvious one. Rather than migrating reports gradually onto a partially built warehouse, we build all of silver, gold, and the reporting marts in a development environment first, validate them row-for-row against the existing production tables, and only then migrate reports one at a time. Nothing gets re-pointed underneath a working report at any stage.
This matters more than it might sound. The original committed plan called for re-pointing all one hundred forty-nine reporting pointers in a single maintenance window, described in that document as all-or-nothing. Replacing that with an incremental approach means every step is individually reversible, and the existing pipeline stays intact as a parity reference for the entire build rather than being dismantled as we go.
What measurement changed
Before writing transformation models, I measured the actual gap between the existing production tables and the new bronze layer, comparing column signatures table by table across all one hundred forty tables. The result reframed the whole project: one hundred twenty-two of one hundred forty are pure pass-through, identical column names and types. That is eighty-seven percent mechanical, which turns what the project plan had framed as months of hand-written modelling into days of scripted generation plus focused attention on the genuine exceptions.
The exceptions were where the real risk sat. Two tables were missing eighty-nine columns entirely, traced not to a modelling problem but to the upstream extraction job not selecting the full column list, which would have broken reporting views silently rather than loudly. Several money columns had narrowed from eighteen integer digits to nine, a real overflow risk. A handful of columns had changed from an auto-generated binary row version type into plain text, which cannot be faithfully reproduced and needed an explicit decision rather than a silent conversion.
Two design decisions that shape everything downstream
Two architectures run side by side, and a switch decides which one reports see. The reporting database already contains the seam that makes this possible. Around three hundred eighty-three business logic views hold the actual reporting logic, and none of them reference a physical table directly. They reference one hundred forty-nine synonyms, database-level aliases that currently point at the legacy pipeline’s output tables.
Change where those synonyms point, and every view above them silently reads from the new warehouse instead. No view changes. No report changes. No tool reconfiguration. That is the blue/green switch: green is the existing prefix-renaming pipeline, blue is the new warehouse, both hold a complete valid copy of the same tables, and the synonym layer decides which is live.
This is also why the table and column naming rules are load-bearing rather than cosmetic. A synonym pointing at a table whose name carries the legacy prefix simply fails to re-point, and a renamed column inside a table does not fail at deployment at all, it fails when a report runs in front of a user.
Business logic belongs in SQL, not in the reporting tool. The gold layer exposes a stable set of views that reporting tools read directly, which deliberately keeps calculation logic out of the reporting layer’s own expression language. The reasoning is portability and reviewability: logic in a SQL view is version-controlled, testable, and readable by anyone who knows SQL, while the same logic written as reporting-tool measures is locked inside that tool and invisible to the warehouse. It also means a future move to a different reporting front end does not require rewriting the calculations.
That second decision has a specific forward-looking payoff. Keeping a stable view layer over the gold schema means the warehouse can later be mirrored into a cloud analytics platform as a read replica, with SQL Server remaining the authoritative source, without any transformation logic needing to move. That phasing is deliberately aligned with the planned retirement of the legacy reporting platform rather than being attempted at the same time.
The result
The project is early, four working sessions in, with the repository scaffolded, the transformation project structure established, and the naming rules that everything else depends on documented explicitly. The measurement work that reframed the scope is complete, and a metadata snapshot tool now captures a drift baseline so changes in the source system during the build are detectable rather than surprising.
Where it stands
Model generation against the confirmed inventory is the next phase. One architectural question remains genuinely open, where the reporting database should live relative to the new warehouse, and I have deliberately left it open rather than resolving it prematurely, since the answer changes the cutover sequence and the wrong choice would be expensive to reverse.