Data Warehouse (Medallion Hub)

Medallion Hub is a consolidated data warehouse built on the Medallion architecture, the industry pattern where data moves through three named layers, raw bronze, validated silver, and business-ready gold, with a dimensional mart above them, and each layer reads only from the one beneath it, so any number in a report stays traceable to its source. It replaces an existing nightly chain of stored procedures, and the new transformation logic sits in version control as ordinary SQL files with configuration alongside. One line describes the whole design: extraction brings the data in, dbt turns it into the warehouse, and the orchestrator decides when everything runs and reports how the night went. Half of the replacement has already happened, since the extraction leg now runs in production through Sluice in a compatibility mode; the transformation leg is what this project replaces.

The pipeline being replaced works and has worked for years. The reason for replacing it is that every piece of business logic sits inside stored procedures, and logic in that form cannot be compared version to version, tested on its own, or reviewed like application code. The cost is concrete: a quick data pull resolves through around 383 business logic views with no catalog, the documentation mentioned 29 stored procedures where I counted 295, and one pair of tables turned out to be missing 89 columns, which would have broken reports with no error at all.

Before any transformation model was written, I measured how far the existing production tables sit from the new bronze layer across all 140 tables: 122 are pure pass-through, 87 percent mechanical, which turned months of planned hand modeling into a few days of scripted generation, with real attention reserved for the 18 genuine exceptions. The committed migration plan had called for re-pointing all 149 reporting aliases in one all-or-nothing window; I replaced it with the inverse, build and validate first, then migrate reports one at a time through the alias layer, both pipelines running in parallel throughout. The same blue/green principle will run nightly inside the warehouse: dbt builds into an inactive slot, and only passing tests fire the transactional switch, so a failed night leaves consumers reading yesterday’s complete data instead of wrong data.

AI does measured work here, in two tiers. The 140 staging models will be generated by a rule-based script, never by a language model, since a contract of 532 views leaves no room for one invented column name. Judgment work goes to the frontier cloud model, bulk reading to locally hosted models, clinical rows never reach the cloud, and the router fails closed.

The extraction engine runs nightly in production. The repository’s main branch is protected, and its continuous-integration checks are live and passing. The development environment decisions are made, and the next phase is mechanical: one frozen extraction, the toolchain, and the generated staging models. One question stays deliberately open, where the reporting database belongs relative to the new warehouse, because the wrong answer would be expensive to reverse.

The technical detail

Medallion Hub is the consolidated data warehouse replacing a nightly pipeline of stored procedures with transformation logic that lives in version control as plain SQL and configuration files. The extraction engine pulls the data, dbt builds the warehouse, and the orchestrator decides when to run and reports how it went. The extraction leg is in production, and the transformation leg is in progress.

The layers, and what each one is for

The constraint that gives the Medallion 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. The pipeline is ELT, not ETL: raw data lands first, and transformation happens afterward inside the database engine, where set-based SQL is at its best.

The layer names mean increasing refinement, like ore being processed. Bronze’s job is to be a faithful, replayable record, exact copies of source tables as they arrived, test records and raw date strings included. Silver is the source tables made trustworthy, with names and columns kept exactly compatible with what the existing reporting estate expects. Gold is the small set of derived output tables that the legacy pipeline’s final procedures used to produce, reproduced column for column. And the mart is the star schema built for self-service analytics, new modeling, free of legacy constraints, where the actual analytical value lives.

The pipeline being replaced, and the part that has already changed

Understanding the existing pipeline 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 0 through level 7, 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[Quality-gate prefix<br/>DOWNLOAD CONFIRMED]
    B -->|No| X[Stop. Production untouched.]
    C --> D[Rename to processing prefix<br/>PROCESSING LOCK]
    D --> E[L0-L7 procedures run<br/>read the lock, write plain names]
    E --> F{All procedures<br/>complete?}
    F -->|Yes| G[Atomic rename switch]
    F -->|No| X
    G --> H[Processing set becomes<br/>the new production set]
    G --> I[Old production set becomes<br/>yesterday's archive]
    H --> J[Reporting views rebuilt<br/>against new production]
    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 pipeline 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.

None of the prefixes survive into the new system. The new warehouse expresses the same states through schemas, a slot mechanism, and pointer re-points, all described below, and no prefixed object is ever created in it. The legacy prefixes appear in this project in exactly two roles: as the validation yardstick the new tables must match, and as the source of the naming contract, since the new table names must equal the legacy names minus the prefix.

One half of the replacement has already happened. The extraction leg, which was previously run by the incumbent Microsoft integration tooling, is now run in production by Sluice, the extraction engine described in its own project page, operating in a compatibility mode that lands the same tables and drives the same prefix state machine, so the entire downstream is unchanged and unaware. That flag was designed as a low-risk proving ground, and it worked as intended: the new engine was validated against real production data for weeks before anything about the warehouse itself existed, and then kept the job.

What remains of the old tooling is the transformation leg. The level-0 through level-7 procedure chain into the reporting outputs is still driven by the incumbent integration tooling. That remaining half is what this project replaces: dbt takes the transformations, and the orchestration platform takes the scheduling and sequencing. When the last report migrates, the old tooling retires, in two halves that were replaced years and months apart, each independently reversible.

Why replace it at all

The reason is not that anyone built it badly. It is that all of the business logic lives in stored procedures inside a database, and logic in that form 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 300 of them.

The cost is best explained through something that happens in every data team. Somebody asks for a quick data pull, it sounds like 20 minutes, and days later it is still going. Here that happens for reasons I can put numbers to.

Nobody can tell you what a term means. A request for a straightforward figure must resolve through around 383 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 catalog. The definition is discoverable only by reading SQL.

The systems are undocumented at a scale nobody had counted. The existing documentation mentioned 29 stored procedures. I counted 295 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 in the way. This is a behavioral health context, so a large share of the interesting columns cannot be queried and shared. Handling that is not a formality, and it shaped several tools in this project, as described below.

The schema changes, and silently. One pair of tables in the new bronze layer was missing 89 columns, traced to an extraction query not selecting the full column list. Any report touching those columns would have failed quietly instead of loudly. Several money columns had narrowed from 18 integer digits to 9, which is an overflow risk, not 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 300 stored procedures cannot be diffed, tested in isolation, or reviewed the way application code is reviewed. The only durable record of intent ends up being whoever remembers the conversation.

None of that is an estimation problem or a communication problem. It is a foundation problem, and it recurs on every request because the foundation does not change between them. The useful question, then, is not whether a given pull can be done, since it obviously can, at whatever cost it takes but whether the work should keep being done this way, and that question only gets answered by fixing what sits underneath, not by getting faster at absorbing the cost. Everything else in this write-up, the layering, the tooling, the switch design, the naming rules, and the enforcement machinery, is implementation detail in service of that answer.

Choosing the tools: one job each

Three tools run the system, and the boundary between them is deliberate. Each knows one thing.

The SQL lives in dbt Core. 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. That format is what made the choice, because of how this project is built. Given that measurement showed 122 of 140 staging models to be mechanical pass-throughs, most of the work is generation followed by review, not authorship, and a format that can be generated as text, reviewed in a diff, corrected, and regenerated is worth more than the tooling already installed. The honest trade-off is a new tool with its own installation, scheduling, and failure modes, against tooling already in production. I judged the reviewability worth that cost, and someone weighing the same decision on a team with no AI-assisted workflow and deep incumbent expertise could reasonably land the other way.

dbt brings three structural benefits beyond the file format. It derives the build order from the SQL itself, so a model that references another model builds after it, with no separately maintained dependency list to drift out of date. Its data tests are declared in the same files as the models, so the tests cannot drift from the code. And it generates a browsable catalog with lineage from those same files, which means the documentation regenerates with every run and can never go stale the way a hand-maintained spreadsheet does. A model without tests and column descriptions is, by project rule, not done.

To make the mechanics concrete, here is how one table will travel the whole system. The extraction engine copies the raw clinical client table into bronze overnight, raw codes, raw date strings, test records and all. One dbt model file, a single SELECT statement, casts the dates, standardizes the codes, drops the test records, and materializes the silver table. A second model file shapes that into a client dimension in the mart, ready to join to an episode fact table, and because it references the silver model, dbt builds silver first without being told. A few lines of YAML declare that the client identifier is never null and always unique, and every nightly run will enforce it. One orchestrator flow will also tie the night together: confirm extraction finished, build, test, switch, refresh the BI layer, and alert on failure. The same five steps repeat for every table in the warehouse, and each is a small SQL file plus a few YAML lines, all in git.

Two dbt-specific design choices will matter to anyone building similarly. Every model resolves its source through a single sources configuration file instead of referencing databases directly, so re-pointing the entire warehouse at a different source database is a one-file change instead of a 140-model change. And model filenames are decoupled from deployed table names through an alias mechanism, which lets the files follow code conventions while the deployed objects carry the exact names the compatibility contract requires.

The orchestration platform knows the clock and the sequence. The planned nightly flow, which launches the extraction, polls it to completion, runs and tests the dbt build, switches the serving layer, regenerates the catalog, refreshes the BI platform, and alerts on failure, is fixed-sequence work, and it belongs to a dedicated orchestrator whose flow definitions are plain YAML in version control. Git is the source of truth; the platform’s UI is a viewer. The dashboard is also a deliverable. Leadership will see, every morning, whether the night ran green and, when it did not, which step broke, which is a kind of visibility the old pipeline never offered. One boundary rule keeps the platform honest: transformation logic never lives inline in a flow. Flows call dbt or SQL; they do not become a second place where business logic hides.

The database engine’s own agent keeps the housekeeping. Backups, index maintenance, and integrity checks are database-internal work that runs with the engine’s own service context, and that a database administrator expects to find in the standard tooling. The rule of thumb that draws the line: if the job would still need to run even if the warehouse project did not exist, it is not the orchestrator’s job.

Downstream refreshes fire only when every upstream step succeeded. If anything fails, the run stops and alerts, and consumers keep reading yesterday’s complete data. Stale by a day is a far better failure mode than wrong. The nightly batch cadence is a feature, not a limitation: every consumer sees one consistent daily snapshot, which resolves the classic tension between wanting freshness and needing stable, reproducible datasets. Two people running the same report on the same day get the same numbers.

On the road not taken, I ruled out the visual ETL tool category as a whole, not any one product. The disqualifying property is any tool whose native artifact is a proprietary blob: no real diff, no readable review, and debugging by clicking through nested components. Escaping that is the point of this project, and a replacement that reintroduced it would be a lateral move at best.

Three orchestration layers, divided by one question

As the project grew an AI-assisted development process alongside the data pipeline, a second kind of orchestration appeared, and keeping the two kinds separate turned out to be one of the most clarifying decisions in the architecture. The dividing question is simple: does the workflow execute a known, fixed sequence, or does it make judgment calls?

flowchart LR
    subgraph D["Deterministic, fixed sequence"]
        K["Orchestration platform<br/>nightly data pipeline:<br/>extract, build, test,<br/>switch, refresh"]
        A["Database agent<br/>backups, index maintenance,<br/>extraction scheduling"]
    end
    subgraph J["Decision-driven, judgment calls"]
        L["LangGraph<br/>the development<br/>lifecycle graph<br/>plus AI model routing"]
    end
    L -.->|"hands off deployed code;<br/>the nightly run picks it up"| K

The scheduled data pipeline is a fixed sequence: run step A, then B, then C, and retry on failure. That belongs to the orchestration platform. The development lifecycle and AI model routing are decision-driven: which model handles this task, did the tests pass and what should change if they did not, and is this change ready for human review. That belongs to LangGraph, a graph framework whose explicit state, nodes, and conditional edges, plus a first-class interrupt-and-resume primitive for human approval gates, match the shape of a development lifecycle.

The temptation this framing resists is common. Once an orchestrator is installed, everything starts to look like a flow, and once an agent framework is installed, everything starts to look like an agent. Expressing agentic loops in an orchestrator’s YAML means fighting the tool with conditionals it was never designed for, and expressing a nightly schedule as an agent graph means paying judgment-machinery costs for work that has no judgment in it. One question, fixed sequence or judgment calls, sorts every workflow in the system into the right layer, with no overlap.

The two systems touch at exactly one point, as a handoff, not an overlap. When the lifecycle graph completes a deployment, meaning code merged and serving switch done, the nightly pipeline picks up the new code on its next scheduled run. In the simplest form the handoff is implicit: the pipeline runs whatever is in the production branch. Optionally the deployment step could trigger a pipeline run as a smoke test, but that is plumbing, not architecture.

One more structural idea in this layer pays for itself. The model-routing logic is built once, as a subgraph, meaning a compiled graph used as a node inside other graphs, and reused everywhere an AI call happens. The development lifecycle’s planning and coding steps route through it, and a future question-answering workflow would use it as its front door. One platform, one routing brain, and the compliance-sensitive routing rules get defined exactly once, which is the right property for rules whose failure mode is a privacy incident.

What measurement changed

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 140 tables.

That made 87 percent of the tables mechanical, and it reframed the project. What the plan had described as months of hand-written modeling became days of scripted generation plus focused attention on 18 genuine exceptions, and each exception needed a different kind of decision.

Two tables missing 89 columns is an upstream problem, not a modeling one. Almost all the missing columns are code-and-value pairs and electronic-interchange fields, and 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 where it should have failed loudly, which is the worst category of failure this project has.

Money columns narrowed from 18 integer digits to 9. 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 is a row version, not a timestamp: generated automatically, and impossible to insert. The silver layer therefore cannot reproduce these faithfully, and the question needed an explicit decision plus confirmation that no reporting view depends on the value, not a silent conversion either way.

Some apparent differences were false positives. Two numeric type names that look different are identical in this engine and recognizing that saved real work. The general form of this lesson repeats below: a comparison can return a confident, alarming, wrong number, and the discipline is to understand the metadata before trusting the diff.

The measurement also surfaced findings nobody had asked for, each of which changed scope or estimates. 531 of 532 reporting views are structurally valid; the single broken one references an object that exists nowhere, no table and no pointer, confirmed dead and excluded from any deployment, with its source table deactivated upstream so it stops reappearing every run. A naive dependency query reported 176 broken objects, but the true number is three, because the engine’s dependency metadata reports cross-database references as unresolved, which is expected behavior, not breakage. And only 96 indexes exist across 285 tables, so most tables are heaps, which means index parity, which the plan had budgeted as a significant workstream, is a small job.

The transferable lesson is to measure before you estimate. The documented inventory and the real one differed by an order of magnitude in both directions: 10 times more procedures than documented, and a tenth of the modeling effort the plan assumed.

The migration strategy: two pipelines in parallel, one switch

The original committed plan called for re-pointing all 149 reporting pointers in a single maintenance window, described in that document as all-or-nothing. I replaced it with a strategy that inverts the sequence:

1. Build all of silver, gold, and the marts in a development environment. Nothing migrates during the build.
2. Validate 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 trade-off is honest. A single pivot is a shorter total migration with one window to schedule, and it also has exactly one moment where everything either works or does not, with no partial rollback, against a reporting estate of 532 views where the failure mode for a naming mistake is a silently wrong number. Incremental migration takes longer and requires holding both architectures correct for the whole period. I judged a longer migration with reversible steps worth more than a shorter one that must be right the first time.

An earlier version of this plan described two independent switches, one at extraction and one at serving. The extraction switch has since happened in production, as described above, which leaves exactly one switch in the whole migration: serving, per report. During the migration window both pipelines run in parallel, the legacy pipeline keeps its feed and the new warehouse gets its own, and the per-object nature of the pointer layer is what allows some reports to resolve against the new warehouse while others still resolve against the legacy one, both live and correct throughout.

Per-report cutover is three actions, not one, and the third is the one I would expect a team to miss. Re-point the pointers for that report’s tables; rewrite the corresponding compatibility views, which select from physical tables directly and get no benefit from the pointer layer; and update the maintenance procedure that regenerates those compatibility views, because that procedure has been doing the right thing for years, and after the switch its right thing becomes silently reversing the migration on its next run.

There is one trap in this approach that the documentation states as a hard invariant, because it would only reveal itself at the worst possible moment. During the build, the reporting database’s pointers must stay aimed at the old pipeline. Re-point them early and the new silver layer ends up reading through a pointer that resolves back to silver, a circular dependency that surfaces only at cutover, while simultaneously destroying the independent set of numbers the entire validation depends on.

One architectural question in this area remains deliberately open: where the reporting database should live relative to the new warehouse. Pointer re-pointing only works cheaply when both sit on the same server, and across servers it degenerates into linked-server queries, and 532 views querying across a network link violates the performance half of the compatibility contract. The recommended answer is to deploy the reporting database’s views and pointers onto the new warehouse’s server, code only, no data movement, and individually reversible per report, but I would rather carry the question openly than commit prematurely, because the answer changes the cutover sequence and the wrong choice would be expensive to reverse.

The blue/green serving switch, refined

The reporting database already contains the seam that makes per-report migration possible, and it has for years. It just was not built for this purpose.

flowchart BT
    subgraph Green["GREEN, legacy pipeline, live today"]
        OLD[("Legacy production 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. Only the bottom two arrows change, and everything above the synonym layer is untouched.

Roughly 383 business logic views hold the actual reporting logic, and none of them reference a physical table directly. They reference 149 synonyms, which are database-level aliases, plus a set of compatibility views for older tools that cannot resolve synonyms. Change where the synonyms point, and every view above them silently reads from the new warehouse. No view changes, no report changes, and no tool reconfiguration.

Four things must hold simultaneously for that to work, and together they form the compatibility contract the whole project is built around:

That last row is easy to overlook and is a genuine failure mode. A switch that is functionally perfect and quietly makes reports 10 times slower is still a failed switch from a user’s point of view. The naming rows are why one early session was spent on clarifying a naming rule. The convention prefixes table names but not column names, and the original wording left room for generated models to carry incorrectly prefixed columns, an error that produces no build failure and surfaces only as broken reports in front of users. In a project where a large share of the work is generated from a documented convention, the precision of that convention is load-bearing.

The same blue/green principle will also run inside the new warehouse every night, because a naive rebuild-in-place would drop tables out from under a running report. The mechanics:

flowchart TD
    S[Extraction lands bronze] --> B["dbt builds the full warehouse<br/>into the INACTIVE slot"]
    B --> T{"dbt tests pass?"}
    T -->|Yes| W["Transactional pointer switch:<br/>inactive slot becomes CURRENT,<br/>old current becomes ARCHIVE"]
    T -->|No| X["Switch never fires.<br/>Consumers keep reading<br/>yesterday's complete data."]
    W --> R["Catalog regenerates,<br/>BI refresh fires"]

A small control table tracks which slot is live. The switch step reads it, flips the pointers inside a transaction, and updates it. Because the flip is a pointer re-point and not object renames or schema transfers, it is instant, atomic from a consumer’s view, and trivially revertible, and it reuses machinery the reporting estate already depends on instead of introducing new machinery to trust. The archive needs no populating and no retention policy, since it is whichever slot is not live, so retention is one cycle by construction.

Two refinements emerged from working the mechanics through, and both are the kind of detail that only surfaces when you rehearse the night in your head at two in the morning. First, pointing the compatibility views at the synonyms instead of at base tables means the nightly flip touches only the 149 synonyms, so the compatibility views never change, and the procedure that regenerates them drops out of the nightly path. Without that, every flip alters a further 149 objects: heavier, slower, and more to go wrong.

Second, the two tables that cannot be rebuilt from bronze, append-only history whose past rows exist nowhere else to rebuild from, are not exempted from the slot rotation but seeded into it. Before each build, the inactive slot is cleared and the live copies of those two tables are copied in, indexes included, and the incremental models then see an existing table and append the night’s rows to the copy. This keeps one rule for every object, which is to build in the inactive slot and flip when green, with no stateful exception to reason about. It also makes the night retry-safe by construction: a failure leaves the live slot untouched, and a re-run clears the copy and starts clean, so there is no partial-append state to unwind. Two guards protect it. The copy step must clear before copying, which is what makes a retry safe to repeat, and the two incremental models are configured so a full rebuild is impossible, because a full rebuild would discard the history that cannot be rebuilt.

Building against a frozen snapshot

One decision reshaped the build phase: the development warehouse is loaded once and then frozen. There are no nightly re-downloads during the build.

The reasoning starts from an ownership boundary. Bronze belongs to the extraction engine and everything above it belongs to dbt, two different processes on two different cadences, meeting at one read-only boundary. dbt never writes, alters, or cleanses bronze. The build only ever reads it, so a frozen input costs the build nothing, and buys determinism: every rebuild of silver and gold against the same frozen bronze produces identical outputs, which means any discrepancy found during validation is a logic bug by definition, never data drift. It also keeps the build off the production network window, since the one extraction that matters is scheduled once and never competes with the live pipeline at night.

The freeze creates one obligation and one scheduled event. The obligation is that the reference data validation compares against must correspond to the same point in time as the frozen snapshot, because the live source moves nightly and a moving target can never reconcile row-for-row with a frozen input. Where the build’s source is a test copy of the clinical system and not the live one, which is the current plan, since the test copy barely changes and the data is frozen afterward anyway, the same logic applies with a twist. Production output built from live data is not a valid comparison for a warehouse built from test data, so either the comparison runs against legacy output built from the same test source, or the row-for-row proof waits for the parallel-run phase, where both pipelines process the same live nights on real infrastructure. That phase was always going to be the final word, and the choice only affects how strong the earlier gates can claim to be.

The scheduled event is that the freeze ends at the parallel run. Before unfreezing, a schema-drift check runs against a metadata baseline captured at the start, so that source changes accumulated during the frozen months are detected, not discovered. That drift baseline is its own small tool, with two deliberate design constraints. 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 nothing sensitive is read in the first place. And its output stays out of version control, since a full snapshot contains production view and procedure source text.

A related lesson generalizes beyond this project: development environments are dbt targets, not servers. The lifecycle wants develop, test, stage, and deploy, and the instinct is to buy a server per stage. Here, development and testing share one box separated by profile targets and schemas; staging is the blue/green inactive slot, which gives every production night a full build-and-verify against real data before anything is exposed; and deployment is a merge plus the next scheduled run. A third physical environment would triple the extraction load and the patching surface while buying nothing the inactive slot does not already provide. The one requirement that is truly physical, not logical, is that the development server must run the same database engine version as production, because a warehouse designed around a specific engine’s security and feature set cannot be faithfully developed on an older one.

The development lifecycle, and the machinery that enforces it

The most transferable idea in this project is not a data structure. It is that a development process only works if skipping its steps is structurally difficult, not just discouraged. Rules in a document solve a knowledge problem; they do not solve an enforcement problem, and the difference shows up on the days discipline is weakest.

The lifecycle follows the Analytics Development Lifecycle, a framework published by dbt’s creators that adapts software engineering discipline to analytics work, modeled as a loop, not a line: plan, develop, test, deploy, operate, observe, discover, analyze, and back to plan. Its phases map onto this project cleanly:

What makes the loop real is the enforcement chain, built in deliberate layers from dumb to smart, and the order matters as much as the layers.

Layer one is mechanical and already live. The main branch is protected, so there are no direct pushes, even for administrators. Every change goes through a pull request whose template carries the compatibility checklist, and a continuous-integration job must pass before merge. The job validates every model’s deployed name against the authoritative inventory of 149 names, rejects the legacy prefix outright, refuses net-new models that shadow a compatibility name from the wrong folder, parses the dbt project, checks the YAML, and scans for committed credentials. A solo developer self-merges after green checks, because the pull request exists to force the stop, the checklist, and the green build, not to simulate a reviewer who is not there. When a second developer joins, the required approval count rises from zero to one, which is a single setting, not a process change. This layer took one day to build and delivers most of the enforcement value of everything below it.

Layer two is the nightly gate. The blue/green switch will fire only after the build and the tests succeed, which means an untested or broken build physically cannot reach a consumer. This is the strongest guarantee in the system, and it comes from the pipeline design, not from any review process, which is a useful reminder that the best enforcement is often architectural rather than procedural.

Layer three is the lifecycle graph, and it is deliberately last. The plan is to encode plan, develop, test, review, and deploy as a LangGraph graph with a human approval gate. The build order, however, inverts the obvious one, on the strength of a lesson from the ecosystem survey described below. A production research pipeline I studied ran a six-agent workflow with no orchestration framework at all: a shared data store, per-agent logs, and a human at the judgment gates. The framework formalizes those three things, with graph state as the shared store, a checkpointer as the durable log, and interrupt-and-resume as the human gate, and it does so with better ergonomics, but it formalizes a working pattern, and it should never precede one. Version 1 of the lifecycle machinery is therefore a state table in the warehouse’s own database, a plain script that walks plan, develop, test, approve, and deploy in a straight line, and a manual approval stop that the script cannot pass without a human action. That is a legitimate production architecture, not a placeholder, and the later migration into the graph framework is a refactor, not a rescue. The trigger for migrating is concrete: when the script’s branching, retry, or resumability logic becomes awkward to hand-roll, the framework earns its place.

One division of state within that machinery is settled regardless of framework. Receipts live in the relational warehouse database, and runtime state lives wherever the workflow engine wants it. The receipts, meaning what task each model received, what context it was given, what the router decided, what tests ran, and who approved, are queryable audit data in a regulated environment, and they belong in the store that already carries the encryption, backup, and retention regime. The workflow engine’s checkpoint state, meaning where a paused run resumes from, is runtime plumbing, and it can live in the small relational database that ships inside the orchestration platform’s own deployment. Evaluating a popular in-memory cache for either role produced a clean lesson in matching stores to state: the properties a cache sells, which are speed, expiry, and volatility, are the exact opposite of what receipts need, and the system’s write volume is thousands of operations per month, not per second. This state is the audit trail, and audit trails live where the backups and the SQL are.

The AI layer: routing by data classification, gated by a human

The development process uses AI models in two tiers, and deciding which tier handles which work is itself an architectural decision with a compliance dimension.

The division of labor is measured, not assumed. Locally hosted open-weight models, running on an isolated, non-domain-joined server with no cloud egress, were evaluated head-to-head on this project’s real work before any role was assigned, and the results drew the map:

The general rule that falls out is that local models are for work where volume is high and a mistake is cheap, the cloud model is for work where a mistake is expensive, and anything where a mistake is unacceptable is generated by rules or written by hand. One useful heuristic hides in the measured failures: logic that is hard for a model to generate correctly is usually logic that is hard for a person to review correctly, which is an argument for keeping that logic in the plainest possible form regardless of who writes it.

The privacy ranking is inverted from intuition. The phrase “no data leaves the building” applies to the local models, not the cloud one. The local models are also just text generators, so they cannot open a socket or touch a database. The boundary rules are therefore explicit and asymmetric. Content-level clinical rows never go to the cloud model under any circumstance, while schema, column names, and aggregate counts are fine anywhere. The local models may read content rows, but only through a dedicated view layer that strips direct identifiers, so the demographics tables are excluded, and name, government identifier, and birth-date columns are dropped from the tables that carry them, while an internal linking key persists so relationships across tables remain analyzable. That makes this identifier removal, not full de-identification, and the documentation says so instead of glossing it: the real safeguard is that the identifier-bearing columns never reach the local model or its logs at all.

The working pattern layered on top is that the local model reads, and the cloud model builds. A local model may read the identifier-stripped content and describe what it finds, meaning relationships, patterns, and distributions, and the cloud model writes the actual code from that description. The relay rule is the subtle part. The description must stay at the pattern level, such as “these two tables join one-to-many on the client key” or “these two codes co-occur across many episodes”, and it must never cite an individual record, because a specific row reaching the cloud model through a relay defeats the boundary as surely as sending the row directly.

Routing between the tiers is designed to be boring and auditable. Classification is by data source, meaning whether this task’s data path touches a table classified as clinical, and never by content inspection of the question, because a source-classification check is tractable and auditable where content inspection is neither. The router fails closed: an unclassifiable data path routes to the local model, never to the cloud. Ambiguity halts or falls safe; it never falls convenient. And every routing decision is recorded in state, so the audit trail of which model saw which task is a queryable table instead of a memory.

flowchart TD
    Q["Task arrives with its<br/>data-path classification"] --> C{"Touches a table<br/>classified clinical?"}
    C -->|Yes| LM["Local model<br/>isolated server, no egress"]
    C -->|No| CM["Cloud frontier model"]
    C -->|"Cannot determine"| LM
    LM --> R["Routing decision recorded<br/>in state, auditable"]
    CM --> R

The enforcement design later moved from behavioral to structural, on paper first. Everything above still carries one uncomfortable dependency: the guarantee that protected data stays local holds only as long as classification is right every time. The design change removes the dependency instead of defending it. A separate gateway service sits between any AI and the data, and it refuses to serve any object that is not registered and classified, before a query executes at all, regardless of what the router concluded. Getting the documentation honest about that change cost 10 corrected statements across 5 files, 4 more than the plan anticipated, which says something about how far a single architectural sentence spreads. The gateway is designed and not built, and this page will say so until that changes. It belongs to the wider private AI program, which carries its full design.

Classification grew up in the same session. A per-table yes-or-no flag for protected content could not answer real tables, since a guarantor table mixes identity columns with harmless plan codes, so sensitivity is now declared per column, across five categories: clinical, HR-confidential, financial, operational, and de-identified. The view layer the local models read through also moved under a single schema whose name states its purpose, so every AI-facing view now presents on one clean surface instead of being scattered by history.

The framework choice survived contact with its alternatives’ own makers. Before committing to LangGraph I surveyed the agent-orchestration ecosystem: the two major alternative frameworks plus a half-dozen smaller tools from a community discussion, each evaluated by reading the maker’s claims, then the repository, then the product site, checking whether the three agree. One major alternative is effectively deprecated, absorbed into a larger vendor framework and in maintenance mode. The other is actively maintained but built for conversational multi-agent collaboration, not the fixed branching a development lifecycle needs, so it was ruled out on fit, not health. The smaller tools were mostly desktop cockpits for juggling coding assistants, which are good products for a different problem, and, tellingly, two of their own makers pointed back at graph frameworks for backend use cases. One architecturally relevant tool failed the honesty check outright: telemetry on by default, the organizational features reserved for a paid cloud tier, and a repository license that contradicted the maker’s “completely open source” pitch, which is collectively disqualifying for a healthcare nonprofit’s dependency regardless of feature fit. The survey method is the reusable part: claims, then code, then marketing, and the gaps between them are the signal.

The survey’s real deliverable was five design rules, harvested from tools that did not themselves fit, each now part of this architecture:

1. Independent verifier with fresh context. The review step receives the specification, the diff, and the test results, and never the implementing session’s conversation history. A reviewer that shares the implementer’s context inherits the implementer’s blind spots. This turns what was previously manual discipline into structure.
2. Ship the baton, not the transcript. Every handoff between steps passes structured state, meaning goal, decisions made, current status, and open questions, and never accumulated chat history. Context bloat is the failure mode every practitioner in the survey had hit, and the fix is to define what each step needs and pass only that.
3. Fail closed on routing. Described above; unclassifiable is treated as clinical until proven otherwise.
4. Idempotent mutations everywhere. Every step that changes anything, which includes version-control operations, builds, table writes, and external calls, must be safe to re-execute, because durable-workflow resume will re-run steps. Upserts not inserts, check-before-create, and idempotency keys on external calls. This is a coding standard for every step written from the script version onward, not a hardening pass added later.
5. The human owns structural judgment. Anything that changes structure, meaning schema, taxonomy, routing rules, or the graph itself, always goes through the human gate, because the production experience in the survey showed agents overreaching without evidence and oscillating, changing, reversing, and repeating, when allowed to close the loop on judgment calls. Automated loop-closing is reserved for low-stakes preference learning, never structure.

Three supporting rules round out the set. Roles, not vendors, in every step, so provider mapping lives in one configuration point, no step names a model vendor, and swapping providers is a config change, not a rewrite. Vendor-neutral prompts with schema-validated outputs, so no provider’s formatting habits get baked in. And a periodic swap test in the graph’s own test suite, which points a role at a different provider and runs the tests, so portability is proven, not assumed. The procurement reality is that organizations end up buying multiple AI products over time for reasons outside any data team’s control, and vendor portability is an architecture property, not a framework feature. If prompts and review rules are silently shaped around one vendor, switching later is a partial rewrite no matter what the framework promises.

One more filter governs the whole layer, learned from experience, not reading. Every additional agent is another thing to supervise, and supervision does not parallelize well for one person. Any design that increases the number of things demanding human attention has failed regardless of its features. One graph, one agent working inside it at a time, and gates where the human decides. The point of encoding the lifecycle is to spend less attention, not more.

Two grounding notes on the isolated model server, both of which generalize. First, “non-domain-joined” isolates directory-level trust, not network reachability. The inference API answers anyone on the subnet who can reach its port, so the isolation story is a firewall scope and an interface binding, not the domain membership, and confirming actual exposure beat assuming it. Second, the hosting decision for the workflow framework followed the data. Its managed cloud runtime defaults to the vendor’s infrastructure, and orchestration state that is even adjacent to protected health information does not transit a third-party managed cloud, so the framework runs self-hosted, which the core library fully supports.

What the clinical system’s own AI cannot see

The clinical system’s vendor is building AI features inside its own product, including documentation assistance, summarization, and clinical workflows. That is their lane: their data, their system, and their regulatory burden, and there is no sense competing with it.

The warehouse’s unique value is cross-system. The vendor will never see the payroll system, the training platform, the general ledger, or the organizational directory. The questions that only exist across systems are the ones only the warehouse can answer: actual program cost against service delivery volume, meaning cost per unit of service, by program; payroll against program budgets and utilization; training completion correlated with service delivery patterns; and revenue and billing performance tied to encounter data. Eventually it can answer cost per outcome, the question boards ask and almost no one can answer, which requires the finance-plus-clinical join that only a warehouse can do. That last one depends on genuine outcomes analysis, not just how much service was delivered but whether it helped, which is what a mature, governed warehouse with well-modeled clinical measures makes possible, and which stands as this project’s long-term reason for existing, beyond the utilization work in scope today.

This scoping also cleans up the AI posture. A question-answering layer targets the integrated, curated gold and mart layers, not raw clinical workflows, so there is no competition with the vendor’s roadmap, and a smaller compliance surface, since the mart is already governed. One sentence for leadership: the vendor gives us AI inside the clinical system, and the warehouse gives us AI across the whole organization.

A first analytical track is already scoped to run ahead of the full build, because it needs only existing reporting views. It is a service-delivery and utilization analysis using classical interpretable machine learning: feature-importance analysis to identify which service-delivery factors most associate with the metrics leadership already reviews, clustering for peer-group tiering, and outlier detection for review flags. Interpretable methods only, deliberately, because the output feeds a business decision process this project does not own, so transparency beats accuracy at the margin. And because the underlying rows are clinical even when the rollup is staff-level, the aggregates are checked for minimum group sizes, since a small enough slice can indirectly reveal an individual even without a name column, and the data is handled under the same rules as everything else clinical. The work doubles as a live data-quality test of an existing reporting view, which is useful evidence for the warehouse business case itself.

Dimensional design, briefly but completely

The mart follows the Kimball method, and four decisions are made, in sequence, for every subject area: business process, grain, dimensions, and facts.

The grain is declared before building and never mixed within a fact table. Of the three fundamental grains, transaction, periodic snapshot, and accumulating snapshot, the last fits behavioral-health episodes naturally: one row per episode, with milestone dates filling in from admission through discharge. Dimensions are the soul of the warehouse, since they drive filtering, grouping, and the whole self-service experience, so they carry an outsized share of design and governance attention, and changes to shared dimensions are deliberate, reviewed decisions.

Every dimension and fact carries a surrogate key, and natural keys become attributes. Source systems recycle keys, keys collide across sources, and history tracking requires one client to have many versions, and all three problems dissolve with surrogates, while fact rows translate natural keys to surrogates at load, so a fact never lands holding an unresolved reference. Dimensions are conformed, meaning built once and reused by every fact, so two reports cannot disagree about what a program is. One date dimension serves every date role through aliases, not one dimension per role. Transaction identifiers that would otherwise be single-attribute dimensions live on the fact as degenerate dimensions. Audit metadata, meaning load time, batch, and job, lives as columns on the facts, not in a separate audit dimension that would grow as large as the facts themselves.

History is tracked through the transformation tool’s built-in snapshot mechanism instead of hand-rolled merge logic: type 2 where history matters, with validity ranges and a current flag; type 1 for corrections; and type 3 not used at all. Late-arriving data is normal in a clinical context, and the rule is that late facts join to the dimension version that was true at the event date, not the current one. Clinical codes carry both the raw source value, for fidelity, and a standardized mapping, for comparability, with the mapping maintained as versioned seed data, never ad hoc in report queries. Many-to-many relationships resolve through bridge tables, and referential integrity is enforced by the transformation tool’s relationship tests instead of physical foreign keys, since the tool’s rebuild ownership makes enforced constraints impractical.

Two rejections are as informative as the adoptions. The database engine’s temporal tables are not used for warehouse history, because they version indiscriminately, conflict with the transformation tool’s drop-and-rebuild ownership, pollute history with rebuild noise, and charge continuous storage for a rarely used need, and a restored backup is the cleaner audit artifact anyway. And normalization is applied where it belongs and deliberately violated where it does not: silver aims for third-normal-form correctness, while mart dimensions are deliberately denormalized, because normalizing a dimension “because it is cleaner” defeats the star.

One deferred pattern has an explicit trigger. Wide, denormalized one-big-table reporting tables are not built speculatively. Instead, the signal to build one is someone bypassing the mart to join silver directly because the star feels like too many joins, and that request supplies the grain and the columns. When it happens, such tables go in a separate reporting schema above the mart, built from the dimensional tables and not from silver, so they stay derived instead of becoming a parallel modeling layer.

Data quality as a system, not a hope

Data quality follows the same discipline as the modeling, and most of it maps onto the transformation tool’s native test mechanism.

Profile before modeling: column lengths, null rates, distinct values, and out-of-range dates, and root-cause anomalies instead of silently filtering them, because an anomaly is evidence about the source system. Cleansing lives in the intermediate and mart layers, bronze is never cleansed, and the compatibility-bound silver stays faithful to the legacy shapes it must match. The classic quality-screen taxonomy maps directly onto declared tests: column screens, which are never null, unique, accepted values, and ranges; structure screens, which are relationships between tables; and business-rule screens, which are custom assertions like a discharge date never preceding an admission.

Beyond per-row tests, reasonability checks catch what row tests cannot, such as today’s row count sitting within an expected band of yesterday’s, and distributions not shifting wildly. These are the signature of an upstream extraction failure, which per-row tests pass right through, because every row can be individually valid while half of them are missing. And rows that fail hard rules are quarantined to an error table recording what failed and when, and never dropped invisibly, because a silently shrinking dataset is its own data-quality failure.

Every model ships with tests and column descriptions or it is not done, which is a project rule, not an aspiration, and the catalog those descriptions feed regenerates in the pipeline, so the data dictionary is a build artifact, not a document someone maintains. Business definitions belong in it alongside the technical notes, because the catalog serves report authors, not just developers. The heavier governance apparatus, meaning a cataloguing platform, a formal glossary, master data management, and data contracts, is deferred until the warehouse is live, on purpose, not by neglect: the generated catalog already delivers most of the value, and the rest is an enterprise data office’s roadmap being resisted by a two-person team.

Security, and reconstructing the past

Behavioral health data is among the most sensitive healthcare data, and “compliant” means specific controls, not a disclaimer. Encryption at rest for the whole database, with the certificate backed up separately because backups encrypted with a lost key are not backups. Encryption in transit everywhere. Column-level encryption reserved for the narrowest, highest-sensitivity set, and display masking used for what it is, a display control and never a security boundary. Row-level security is available at the database if program-level or site-level segregation is required, and role-based filtering in the BI layer’s semantic model covers end users. Least privilege throughout: the extraction, transformation, and orchestration services each get exactly what their job needs, never administrative rights and never shared logins, and report accounts are read-only on the serving layers and can never see bronze. Access rights are reviewed periodically, never granted and forgotten, and access to protected tables is audit-logged.

Consolidating every source into one database is itself a security decision: one access surface and one consistent policy, instead of fragmented controls across systems. And credentials never live in committed files, any credential that so much as touches a transcript or log is rotated, not tolerated, and the continuous-integration job scans for the mistake anyway.

Three mechanisms answer “show me the warehouse as it was on date X”, and nothing new needed building. Dimension snapshots answer point-in-time questions live, through their validity ranges, with no restore at all. Verified database backups answer full-warehouse state, verified because an unverified backup is not a compliance asset, restored to scratch and never over production, and retained to the regulatory documentation window. Version control also answers which logic produced the numbers on a given date. Backups give data-as-of-then, git gives code-as-of-then, and snapshots give history-in-place.

The working rules underneath all of it

A set of working rules carried from project to project governs how this gets built day to day, and they matter more than any individual tool choice. They exist because the same mistakes were made, corrected, and written down, so each rule has a scar behind it. The same rules live in the shared skills library, so they travel between projects instead of being relearned.

Discuss before touching anything, and this is non-negotiable. Describe the plan in plain English and wait for explicit approval before editing any file, no matter how small or obvious the change seems. A question is a question, not a decision to be recorded. Changes happen in batches, on request, not reflexively after every discussion. The rule resets every session, because context from a prior conversation is not consent in this one.

Plan mode by default, and re-plan when things go sideways. Any substantial task gets a plan before it gets an edit, and when something breaks mid-task, the discipline is to stop and re-plan instead of pushing harder on a course that already bent.

Read before you respond. Never describe how code, a model, or a pipeline behaves without reading the actual implementation, because the source is the truth, and inference from convention or memory is how confident wrong answers happen. The corollary bit this project directly: a stale claim in a document or a memory file gets re-verified against the live system before acting on it, especially before deactivating or dropping anything.

Verify before done. Nothing is complete because it compiles or runs once. Tests run, logs get checked, and for warehouse work the bar is parity, so the question is never “does it build”, it is “does it produce the same numbers”.

Capture every correction. After any correction, the pattern gets written down as a rule with a why and a how to apply, committed to the repository’s own memory folder so every future session, on any machine, starts with the accumulated lessons instead of relearning them. The repository, not a personal machine, is the shared memory.

Keep a worklog. Every session ends with an entry: what was worked on, what was hard, what was decided and why, and what changed. Written in full sentences, at the moment the context is fresh, it becomes a running institutional memory that has already paid for itself several times over, including reconstructing why a decision was made months after the conversation that made it.

The deeper principle behind the whole set is the one from the enforcement chain: knowledge and enforcement are different things. The rules solve the knowledge problem. The protected branch, the CI checks, the test-gated switch, and eventually the interrupt-gated graph solve the enforcement problem. A rule that depends on someone remembering it on a rushed Tuesday is a wish. The machinery exists, so the rules hold on the days discipline does not. The wider method these belong to has its own write-up, in how these projects were built and in what building with AI actually costs.

Honest sizing: where the difficulty lives

For anyone using this write-up to plan similar work, the effort distribution is the most useful thing I can share, because it is nothing like the intuitive one.

The 140 staging models look like the mountain and are the flattest part, since they are scripted generation against a measured inventory, days not months. The real weight sits in three places. First, reverse-engineering the 9 derived output models to value-for-value parity: judgment work with zero tolerance, where every discrepancy must be explained rather than shrugged at, and where the two append-only history tables, whose past exists nowhere else, carry the single riskiest step in the whole project, a one-time history migration. Second, the per-report migration: technically trivial per report, organizationally dominated by a number that took embarrassingly long to ask for, which is how many reports sit on the estate, and that is the single biggest unknown in the plan and the first thing anyone doing this should enumerate. Third, sustained discipline: nothing here is technically exotic, and the hard part is holding parity validation honest for months while a day job competes for attention. The blue/green design is what makes that tractable for a small team, because a failed night costs nothing, so there is no two-in-the-morning pressure anywhere in the plan.

The calendar has a floor that no amount of effort compresses: validation windows. Weeks of clean nightly runs before trusting the extraction, weeks of clean parallel runs before trusting the warehouse, and a month of clean operation before decommissioning anything. Those windows are the point, not the delay, since they are what “proven” means.

Where it stands now

The extraction engine runs production extraction nightly through its compatibility mode, with the legacy downstream unchanged. The repository has a protected main branch with the continuous-integration checks live and passing. The architecture, the build plan, the compatibility inventory, and the working rules are consolidated into a single documentation set with one source of truth, and the operational rule set that an AI assistant loads during work declares the architecture document authoritative on any conflict, which is a small line that exists because the one drift between them was caught in review, not in production.

The development environment decisions are made: a dedicated development server on the same database engine version as production, loaded once from a test-environment extraction and frozen for the duration of the build, with the two warehouse databases restored across from the previous environment and upgraded in place.

The next phase is mechanical and well-bounded: run the one frozen extraction, install the transformation toolchain, and generate the 140 staging models from a rule-based script, never from a language model, because name-exactness under a 532-view contract cannot tolerate a fabricated name. After that comes the judgment work: the 9 derived output models to value-for-value parity, the dimensional mart, and the lifecycle machinery, script first and graph later, each gated by the enforcement chain described above.

One architectural question remains deliberately 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.

References

The patterns this project follows are all published, and the sources are named here, not paraphrased:

• dbt Core documentation, for models, tests, sources, snapshots, and lineage.
• The Analytics Development Lifecycle, Tristan Handy, dbt Labs, September 2024. The plan, develop, test, deploy, operate, observe, discover, and analyze loop this project’s lifecycle follows.
• LangGraph, for the state, node, and edge model, subgraphs, interrupt-and-resume human gates, and checkpointer persistence used by the planned lifecycle graph and model router.
• Medallion architecture, the bronze, silver, and gold layering pattern.
• Blue-green deployment, Martin Fowler. The general pattern that both the serving switch and the nightly slot rotation adapt.
• The Data Warehouse Toolkit, third edition, Ralph Kimball and Margy Ross. The dimensional method used for the mart: grain declaration, conformed dimensions, surrogate keys, slowly changing dimensions, and the quality-screen taxonomy the tests implement.
• Healthcare data warehousing, Knezevic Ivanovski and others, Frontiers in Digital Health, December 2025. The governed warehouse, meaning schema-on-write, star schemas, and auditable ELT with lineage, as the trusted foundation for healthcare analytics under HIPAA.