Medallion Hub

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 exactly as the source provided it, validated and correctly typed data in silver, and business-ready aggregates in gold. The constraint that gives the pattern its value is that each layer reads only from the layer below, which is what makes any number in a report traceable back through the transformations that produced it.

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.

The pipeline being replaced, and how it actually works

Understanding the existing pipeline properly turned out to be the most valuable early work on this project, because it is more sophisticated than its age suggests, and several parts of it are load-bearing in ways that are not obvious.

Data arrives from a cloud-hosted clinical database into an operational data store. Business logic then runs as a sequence of stored procedures, level zero through level seven, each reading the layer below and writing physical tables with indexes. Reports query those output tables directly.

The clever part is how the pipeline guarantees consistency, and it does it entirely through table renaming:

flowchart TD
    A[Source extract lands<br/>tables under plain names] --> B{All downloads<br/>confirmed complete?}
    B -->|Yes, rename| C[ZD_ prefix<br/>QUALITY GATE]
    B -->|No| X[Stop. Production untouched.]
    C --> D[Rename to ZP_<br/>PROCESSING LOCK]
    D --> E[L0-L7 procedures run<br/>read ZP_, write plain names]
    E --> F{All procedures<br/>complete?}
    F -->|Yes| G[Atomic switch]
    F -->|No| X
    G --> H[ZP_ becomes ZR_<br/>new production]
    G --> I[old ZR_ becomes ZO_<br/>yesterday's archive]
    H --> J[Rebuild reporting views<br/>pointing at new ZR_]
    J --> K[Reporting database ready]

The prefixes are not data tiers. They are states in a state machine. If any table fails to download, it never gets renamed, so the transformation phase never starts and production tables are never touched. The processing prefix isolates transformation from live downloads. The final rename is what makes the switch effectively atomic from a reporting perspective.

Anyone replacing this needs to reproduce that guarantee, not just the transformations. That is the actual requirement, and it is easy to miss if you look only at the SQL.

Choosing the transformation tool, and why the format mattered

The first real decision was what actually executes the transformations, and the institutional default was the Microsoft integration tooling already driving the existing pipeline. It is licensed, installed, understood by the team, and demonstrably capable of the job.

I chose dbt Core instead, and the deciding factor was the file format rather than the feature set.

A dbt model is a plain SQL file. Its configuration is YAML. There is no visual designer, no packaged binary artifact, and no proprietary serialization. Every part of the transformation layer is text that diffs cleanly.

That property is what made the choice, because of how this project is actually built. Given that measurement showed one hundred twenty-two of one hundred forty staging models to be mechanical pass-throughs, most of the work is generation followed by review, not authorship. A format that can be generated as text, reviewed in a diff, corrected, and regenerated is worth considerably more in that workflow than a format that requires opening a designer to inspect what a model does. The existing tooling stores logic in a packaged format that resists exactly that loop.

There is a secondary benefit that matters for a small team: dbt’s lineage and testing come from the same files as the transformations, so the dependency graph and the data tests are not a separate artifact that can drift out of sync with the SQL.

The honest tradeoff is that this introduces a tool the team did not previously run, with its own installation, scheduling, and failure modes, against tooling that was already in production. I judged the reviewability worth that cost. Someone weighing the same decision on a team with no AI-assisted workflow and deep existing expertise in the incumbent tool could reasonably land the other way.

Why this work matters, in the terms people actually experience it

There is a lament that circulates in every data team, and it goes something like this. Somebody asks for a quick data pull. It sounds like twenty minutes. Days later you are still working, and everyone involved is frustrated for reasons that are hard to explain to the person who asked.

I want to walk through why that happens here specifically, using the numbers I measured rather than the general complaint, because the answer is the entire justification for this project.

Nobody can tell you what a term means. A request for a straightforward figure has to resolve through around three hundred eighty-three business logic views before it reaches a number, and the definition of the term lives inside whichever of those views someone wrote for a particular purpose, possibly years ago. There is no catalogue. The definition is discoverable only by reading SQL.

The systems are undocumented at a scale nobody had counted. The existing documentation mentioned twenty-nine stored procedures. I counted two hundred ninety-five in the source database alone. That gap is not a documentation oversight, it is a measure of how much logic nobody currently has visibility into, and any estimate built on the documented figure would have been wrong by an order of magnitude.

Protected health information is genuinely in the way. This is a behavioural health context, so a large share of the interesting columns cannot simply be queried and shared. Handling that properly is not a formality: the drift detection tool I built for this project reads schema metadata only and never touches row data, specifically so that there is no masking step to get wrong, because there is nothing sensitive being read in the first place.

The schema does change, and silently. One pair of tables in the new bronze layer was missing eighty-nine columns, traced to an extraction query not selecting the full column list. Any report touching those columns would have failed quietly rather than loudly. Separately, several money columns had narrowed from eighteen integer digits to nine, which is an overflow risk rather than a cosmetic difference. Neither would announce itself.

Business logic really does live in people’s heads. Not because anyone hoards it, but because logic expressed as three hundred stored procedures cannot be diffed between versions, cannot be tested in isolation, and cannot be reviewed the way application code is reviewed. The only durable record of intent ends up being whoever remembers the conversation.

The unglamorous data problems are real. Time zones are the example I have the most direct evidence for. Timestamps in one application were converted correctly in application code and in column defaults, and were still wrong, because a stored procedure was writing several of them directly at the database level and bypassing both safeguards. The fix was a single shared conversion used by every write path, application and procedure alike.

Why replace it at all

The reason is not that it was built badly. It is that all the business logic lives inside stored procedures in a database, which means it cannot be meaningfully diffed between versions, cannot be tested in isolation, and cannot be reviewed the way application code is reviewed. Adding a new source means adding more procedures to a pattern that already has three hundred of them.

Moving the transformation layer into version-controlled SQL and configuration changes all three of those properties at once, and makes the logic legible to someone who was not there when it was written.

Which brings the argument to its actual point. When a request that sounds small turns out to cost days, the instinct is to treat it as an estimation problem, or a communication problem, or occasionally a willingness problem. It is none of those. It is a foundation problem, and it recurs on every request because the foundation does not change between them.

So the useful question is not whether a given pull can be done. It obviously can, and it will be, at whatever cost it takes. The question worth asking is whether the work should keep being done this way, and that question only gets answered by fixing what sits underneath rather than by getting faster at absorbing the cost.

That is the case for this project in one sentence. Everything else here, the layering, the transformation tooling, the switch design, the naming rules, is implementation detail in service of that.

The migration strategy, and the plan it replaced

The original committed plan called for building the new warehouse, then re-pointing all one hundred forty-nine reporting pointers in a single maintenance window. That document described the approach explicitly as all-or-nothing.

I replaced it with a strategy that inverts the sequence:

1. Build all of silver, gold, and the reporting marts in the development environment. Nothing migrates during the build.
2. Validate row-for-row against the existing production output tables.
3. Deploy to the future production server.
4. Migrate reports one at a time. The old pipeline retires last.

The reason is straightforward: nothing gets re-pointed underneath a working report at any stage, so every step is individually reversible. A big-bang swap has one moment where everything either works or does not, and no partial rollback.

There is a specific trap in this approach that I documented explicitly, because it would only reveal itself at the worst possible time. During the build, the reporting database’s pointers must stay aimed at the old pipeline. If they were re-pointed at the new warehouse early, the silver layer would end up reading from a pointer that resolves back to silver, a circular dependency that appears only at cutover. It would also destroy the parity reference that the entire validation depends on, since there would no longer be an independent set of numbers to compare against.

What measurement changed about the scope

Before writing a single transformation model, I measured the actual gap between the existing production tables and the new bronze layer, comparing column signatures across all one hundred forty tables.

Eighty-seven percent mechanical. That reframed the project entirely. The plan had described the staging layer as months of hand-written modelling; the measurement showed it as days of scripted generation plus focused attention on eighteen genuine exceptions.

The exceptions are where the real risk sits, and each needed a different kind of decision:

Two tables missing eighty-nine columns is an upstream problem, not a modelling one. Almost all the missing columns are code-and-value pairs and electronic data interchange fields. The cause is the extraction job’s custom query for those two tables not selecting the full column list. Any reporting view touching those columns would have failed silently rather than loudly, which is the worst category.

Money columns narrowed from eighteen integer digits to nine. That is a genuine overflow risk on charge and fee columns, not a cosmetic type difference, and it needs correcting before those tables are trusted.

Six columns changed from an auto-generated binary row-version type into plain text. In this database engine, that type means row version, not a timestamp. It is generated automatically and cannot be inserted. The silver layer therefore cannot reproduce these faithfully, and the string representation is arguably more correct. This needed an explicit decision plus confirmation that no reporting view depends on the row-version value, rather than a silent conversion either way.

Some apparent differences were false positives. Two type names that look different are identical in this engine. Recognizing that saved unnecessary work.

What else the measurement surfaced

Several findings had nothing to do with the original question and mattered anyway.

Five hundred thirty-one of five hundred thirty-two reporting views are structurally valid. The single broken one references an object that exists nowhere, no table, no pointer, confirmed dead and excluded from any deployment. The upstream extraction job also needs that table deactivated, or it will keep reappearing in bronze every run.

A naive dependency query initially reported one hundred seventy-six broken objects. The database’s dependency metadata reports cross-database references as unresolved, which is expected behavior rather than breakage. Filtering to same-database references gives the true answer of three. This is a good example of a query that produces a confident, alarming, wrong number.

The source database holds two hundred ninety-five stored procedures. The existing documentation mentioned twenty-nine, all in the reporting database. That gap is significant for any migration estimate built on the documented figure.

Only ninety-six indexes exist across two hundred eighty-five tables, so most are heaps. Index parity is a far smaller job than the plan implied.

Guarding against drift during the build

Because the build runs for months against a live source system, I wrote a metadata snapshot tool that captures column signatures, view and procedure source, pointer targets, indexes, and row counts, plus a hash per table to serve as a drift baseline.

Two design constraints shaped it. It reads schema metadata only and never row data, since several source tables hold protected health information, so there is no masking step to get wrong because there is nothing sensitive read in the first place. And it skips any database that does not exist in a given environment with a warning rather than failing the whole run, since not every environment has every database.

Its output is excluded from version control, since a full snapshot contains production view and procedure source text.

A documentation detail that would have broken production silently

One session was spent entirely on clarifying a naming rule, which sounds trivial and was not.

The convention prefixes table names in the production layer. Column names carry no prefix. The original wording described the rule at the table level without stating explicitly that it does not extend to columns, which left room for a transformation model to be generated with incorrectly prefixed column names.

The consequence of getting that wrong is specific: five hundred thirty-two reporting views reference those column names directly. A prefix error would not produce a build failure. It would produce reporting views that resolve against nothing, which surfaces as broken reports in production rather than as an error during development.

So the rule was rewritten as an explicit, separate statement with concrete column name examples. In a project where a large share of the work is generated from a documented convention, the precision of that convention is load-bearing.

Timeline and effort

Work began with the inventory analysis, and the repository scaffold followed the next day. Four distinct working sessions have occurred inside the first week, covering the scaffold, the naming clarification, the measurement and memory work, and the worklog convention. This is the newest project in the set and the least far along.

The blue/green switch: how two architectures run side by side

This is the part of the design I find most interesting, and it is the answer to a question that otherwise has no good answer: how do you replace an entire nightly pipeline that hundreds of live reports depend on, without rewriting a single report?

The answer is that the reporting database already contains the seam, and it has for years. It just was not built for this purpose.

The seam that makes it possible

The reporting database holds three distinct object layers, and the separation between them is what the whole migration turns on:

flowchart BT
    subgraph Green["GREEN — legacy pipeline, live today"]
        OLD[("ZR_ tables<br/>in the operational store")]
    end
    subgraph Blue["BLUE — new warehouse, built in parallel"]
        NEW[("Silver and Gold<br/>in the new warehouse")]
    end
    subgraph Reports["Reporting database, unchanged throughout"]
        SYN["149 synonyms<br/>THE SWITCH POINT"]
        ZT["~149 compatibility views<br/>for tools that cannot<br/>resolve synonyms"]
        BL["~383 business logic views<br/>NEVER TOUCHED"]
    end
    Tools["Reporting tools"]

    OLD ==>|"today"| SYN
    OLD ==>|"today"| ZT
    NEW -.->|"after the switch"| SYN
    NEW -.->|"after the switch"| ZT
    SYN ==> BL
    ZT ==> BL
    BL ==> Tools

Reading the diagram. Arrows point the way data travels, so they run upward from whichever store is live, through the synonym layer, into the business logic views, and out to the reporting tools. Thick arrows are the path in use today. Dotted arrows are the same path after the switch. Note that only the bottom two arrows change: everything above the synonym layer is untouched.

Roughly three hundred eighty-three business logic views hold the actual reporting logic. They never reference a physical table directly. They reference synonyms, which are database-level aliases, one hundred forty-nine of them, each pointing at a physical table in the legacy pipeline’s output.

That indirection means the business logic views do not know or care what is underneath them. Change where the synonyms point, and every view above them silently reads from somewhere else. No view changes. No report changes. No tool reconfiguration.

That is the blue/green switch. Green is the existing pipeline with its prefix-renaming state machine. Blue is the new warehouse. Both can hold a complete, valid copy of the same tables at the same time, and the synonym layer decides which one reports actually see.

Why the naming rule is load-bearing

This is where the naming clarification session described earlier stops being documentation hygiene and becomes the thing the entire migration depends on.

A synonym named client_admissions currently points at a table named ZR_client_admissions. For the switch to work, the new warehouse’s table must be named client_admissions, without the prefix, because the prefix is an internal convention of the legacy pipeline and is invisible to everything above the synonym layer. Get that wrong and the re-point simply fails.

The same rule applies one level deeper and matters more, because the failure is quieter. Every column inside each table must keep its original name and a compatible type, since the business logic views reference those column names directly. A renamed or dropped column does not fail at deployment. It fails when a report runs, which is to say in front of a user.

So there is a four-part compatibility contract, and all four have to hold simultaneously at the moment of the switch:

That last row is easy to overlook and is a genuine failure mode. A switch that is functionally perfect and quietly makes reports ten times slower is still a failed switch from a user’s point of view.

The switch is three actions, not one

The tempting simplification is that re-pointing one hundred forty-nine synonyms is the whole cutover. It is not, and the documentation is explicit about this because the gap would only surface after the window closed.

Three things must happen together:

1. Re-point the 149 synonyms to the new warehouse.
2. Rewrite the roughly 149 compatibility views, which exist for reporting tools that cannot resolve synonyms and therefore select directly from the physical tables. These do not benefit from the synonym indirection at all, so they need editing rather than re-pointing.
3. Update the procedure that regenerates those compatibility views, or the next time it runs it will helpfully rebuild every one of them pointing back at the legacy pipeline, silently reversing part of the migration.

That third item is the one I would expect a team to miss. It is a maintenance procedure that has been doing the right thing for years, and after the switch it becomes the mechanism that undoes the switch.

A second, independent switch at the extraction layer

The serving layer is not the only place a blue/green flip happens. The extraction engine that feeds the warehouse has its own mode flag controlling which target it writes to.

With the flag on, extraction writes into the legacy operational store using the legacy naming convention, and the entire existing downstream pipeline continues completely unchanged. That let the new extraction engine be proven in production against real data with zero downstream risk, before anything about the warehouse existed.

With the flag off, extraction writes into the new warehouse’s bronze layer instead, and the legacy prefix cycle stops.

Two independent switches, one at extraction and one at serving, mean the migration has two separately reversible stages rather than one large irreversible one.

The plan I changed, and why

The committed documentation describes this as a single pivot: all one hundred forty-nine synonyms re-pointed in one off-hours maintenance window, described in the plan as all-or-nothing.

I replaced that with an incremental approach, and the blue/green structure is what makes the incremental version possible. Because the synonym layer is per-object rather than global, reports can be migrated in groups, with some resolving against the new warehouse while others still resolve against the legacy pipeline. Both architectures stay live and correct throughout.

The tradeoff is honest. A single pivot is a shorter total migration and one window to schedule. It also has exactly one moment where everything either works or does not, with no partial rollback, against a reporting estate of five hundred thirty-two views where the failure mode for a naming mistake is a silently wrong number rather than an error. Incremental migration takes longer and requires holding both architectures correct for that whole period. I judged a longer migration with reversible steps to be worth more than a shorter one that has to be right the first time.

There is one trap in the incremental approach that I documented explicitly, because it would only appear at the worst possible moment. During the build, the synonyms must stay pointed at the legacy pipeline. If they were flipped early, the new warehouse’s silver layer would end up reading through a synonym that resolves back to silver, a circular dependency that surfaces only at cutover. It would also destroy the parity baseline the entire validation depends on, since there would no longer be an independent set of numbers to compare against.

Business logic in SQL, not in the reporting tool

One architectural stance governs how the gold layer is being designed: calculation logic belongs in SQL views that reporting tools read, rather than in measures written inside the reporting tool’s own expression language. This is a standing rule I carry from prior reporting work rather than something this project invented, and it is shaping the gold layer’s design as it gets built.

The reasoning is threefold. Logic in a SQL view is version-controlled, diffable, and testable through the same mechanisms as everything else in the warehouse. It is readable by anyone who knows SQL, which is a considerably larger group than those fluent in a specific reporting tool’s expression language. And it is portable, so replacing or adding a reporting front end does not require rewriting the calculations.

The cost is real and worth stating: some calculations are genuinely more natural to express in the reporting tool, particularly those that need to respond dynamically to a user’s own filter selections, and forcing those into SQL produces awkward results. So the rule is a strong default rather than an absolute, and the evaluation work on local language models reinforced it from an unexpected direction: filter-context expressions in reporting tools turned out to be one of exactly two categories where every local model I tested failed consistently. Logic that is hard for a model to generate correctly is usually logic that is hard for a person to review correctly too.

Designing now for a read replica later

One forward-looking decision shapes the gold layer’s structure rather than its contents, and it is the same indirection the blue/green slot design already requires: the gold schema will be exposed through a stable set of views rather than having reports bind directly to physical tables. Two requirements converging on the same design element is a good sign, and it means the indirection earns its cost twice.

That indirection is what makes it possible to later mirror the warehouse into a cloud analytics platform as a read replica, with SQL Server remaining the authoritative source of truth and the cloud copy serving read-only analytical workloads. Because reports resolve through views rather than tables, and because the transformation logic lives in dbt rather than in the reporting tool, that migration involves no transformation rewrite.

I have deliberately phased this after the current build rather than attempting it concurrently, and aligned it with the planned retirement of the legacy reporting platform. Two large migrations running at once share a failure surface, and the parity validation that this build depends on becomes much harder to reason about if the target is also moving.

Technical reference

Layer mapping. The transformation project’s folder structure maps directly onto warehouse schemas: staging models to the silver schema, intermediate and gold models to the gold schema, marts to reporting outputs.

Legacy prefix to schema mapping, needed by anyone tracing data from the old system into the new one:

Source abstraction. Every transformation model resolves its source through a single sources configuration file rather than referencing databases directly. The build currently reads from the existing operational store, since the newer extraction pipeline’s staging schema is not yet populated, while the newer pipeline remains the documented long-term target. Because the indirection exists, switching later is one file rather than one hundred forty-nine models.

Where it stands now

The repository is scaffolded with the transformation project structure, four planning documents, and the naming rules everything else depends on. The measurement work that reframed the scope is complete, with a regenerable snapshot baseline in place.

Model generation is the next phase. One architectural question is deliberately still open: where the reporting database should live relative to the new warehouse. It changes the cutover sequence, and I would rather carry an open question than commit to an answer that would be expensive to reverse.