Use expand/contract for shared reference data

🧪

Hands-on lab

Ship a reference-data change across three independent services — expand in Catalog alone, each consumer adopts on its own cadence, contract in Catalog alone. No lockstep. No coordination window.

Demos/Learn/course9-module-05 →

Three services. A reference table they all depend on. And you need to retire one value and add another — without scheduling a deployment window that requires all three teams to move at the same time.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

This is the capstone. Everything you’ve built in this course — native packages on three engines, file-less connection config, organized package structure, independent CI pipelines — comes together here on the hardest problem in multi-service schema work: shared reference data with no coordination tax. The answer is expand / contract, and you’re about to deploy it.

The problem: shared reference data, independent services

Catalog (PostgreSQL) is the canonical source for public.shipping_region. It’s the list of valid shipping regions every service trusts. Orders (SQL Server) keeps its own copy in dbo.valid_region. Sessions (MySQL) keeps its own copy in valid_region. No cross-engine foreign key. No shared database. Just the same reference data, declared and delivered independently per service.

That structure is intentional — and it’s what makes the pattern work.

Right now the reference list carries three regions: NA, EMEA, and LEGACY. The LEGACY region is being retired. APAC is the replacement. Here’s what that looks like as independent deploys:

Step	Who	What	MergeType
Baseline	All three	Initial state — NA, EMEA, LEGACY	Insert/Update/Delete
Expand	Catalog alone	Add APAC, keep LEGACY	Insert/Update
Adopt	Orders, then Sessions	Re-sync own copy to NA, EMEA, APAC	Insert/Update/Delete
Contract	Catalog alone	Remove LEGACY	Insert/Update/Delete

Four steps. One hard rule. Let’s walk through every one of them.

The DataDelivery block — and why MergeType is the whole game

Before the deploy sequence, you need to understand the one piece of config that controls all of this. In public.shipping_region.json, the DataDelivery block is what tells SchemaQuench how to converge the table’s data:

Baseline and contract use the converging form:

"DataDelivery": {
  "ContentFile": "data/public.shipping_region.tabledata",
  "MergeType": "Insert/Update/Delete",
  "MatchColumns": "region_code"
}

Expand uses the additive form:

"DataDelivery": {
  "ContentFile": "data/public.shipping_region.tabledata",
  "MergeType": "Insert/Update",
  "MatchColumns": "region_code"
}

"Insert/Update/Delete" converges the table to exactly what the file contains — it inserts missing rows, updates changed rows, and deletes anything not in the file. "Insert/Update" is additive — it inserts and updates, but leaves everything else alone.

That single field difference is the entire mechanism behind expand / contract. "Insert/Update" is what makes expand safe to ship at any time. "Insert/Update/Delete" is what makes contract a precise, controlled removal. Everything else — the tabledata content, the deploy cadence, the per-service independence — flows from that distinction.

DataDelivery convergence is idempotent. Run any of these deploys twice and the second run finds nothing to do. The table already matches the file. That’s the same guarantee SchemaQuench gives you on schema — data delivery inherits it.

Step 1 — Baseline all three services

Start with every service at the initial state. Three deploys, independent of each other, in any order.

Catalog (PostgreSQL)

cd postgres
schemaquench --ConfigFile:quench.settings.baseline.json --LogPath:"$PWD/logs"

The baseline tabledata for public.shipping_region:

[
  {"region_code":"NA","name":"North America","active":true},
  {"region_code":"EMEA","name":"Europe, Middle East & Africa","active":true},
  {"region_code":"LEGACY","name":"Legacy Region","active":false}
]

DataDelivery Insert/Update/Delete converges the table to exactly those three rows. The canonical list is established.

Orders (SQL Server)

cd sqlserver
schemaquench --ConfigFile:quench.settings.baseline.json --LogPath:"$PWD/logs"

Orders’ dbo.valid_region DataDelivery:

"DataDelivery": {
  "ContentFile": "data/dbo.valid_region.tabledata",
  "MergeType": "Insert/Update/Delete",
  "MatchColumns": "RegionCode"
}

The baseline tabledata for dbo.valid_region:

[
  {"RegionCode":"NA","RegionName":"North America"},
  {"RegionCode":"EMEA","RegionName":"Europe, Middle East & Africa"},
  {"RegionCode":"LEGACY","RegionName":"Legacy Region"}
]

Sessions (MySQL)

cd mysql
schemaquench --ConfigFile:quench.settings.baseline.json --LogPath:"$PWD/logs"

MySQL’s valid_region DataDelivery — note the backtick-quoted MatchColumns, which is MySQL’s native identifier quoting:

"DataDelivery": {
  "ContentFile": "data/valid_region.tabledata",
  "MergeType": "Insert/Update/Delete",
  "MatchColumns": "`RegionCode`"
}

Same three rows as Orders. Same data, different engine, independently owned.

Three baselines. Three separate deploys. Every service starts at the same known state.

Step 2 — Expand: Catalog alone adds APAC

This is a release for one service only. Orders and Sessions are not touched. They don’t coordinate. They don’t even know it happened.

cd postgres
schemaquench --ConfigFile:quench.settings.expand.json --LogPath:"$PWD/logs"

The expand tabledata — APAC added, LEGACY still present:

[
  {"region_code":"NA","name":"North America","active":true},
  {"region_code":"EMEA","name":"Europe, Middle East & Africa","active":true},
  {"region_code":"LEGACY","name":"Legacy Region","active":false},
  {"region_code":"APAC","name":"Asia-Pacific","active":true}
]

With MergeType: "Insert/Update", DataDelivery inserts the APAC row, updates the others, and leaves LEGACY exactly where it is. After this deploy, shipping_region has four rows. APAC is now official. LEGACY is still valid for anyone who hasn’t migrated off it yet.

This is what makes expand safe to ship at any time. Nothing is removed. Nothing breaks. The window is open for consumers to migrate whenever they’re ready — whether that’s tomorrow or three weeks from now.

Step 3 — Adopt: each consumer on its own cadence

Each consumer re-syncs its own valid_region copy when it’s ready. Orders and Sessions don’t coordinate with each other or with Catalog. Independent teams, independent timelines.

Orders adopts (SQL Server)

cd sqlserver
schemaquench --ConfigFile:quench.settings.adopt.json --LogPath:"$PWD/logs"

The adopt tabledata for dbo.valid_region — LEGACY gone, APAC in:

[
  {"RegionCode":"NA","RegionName":"North America"},
  {"RegionCode":"EMEA","RegionName":"Europe, Middle East & Africa"},
  {"RegionCode":"APAC","RegionName":"Asia-Pacific"}
]

Insert/Update/Delete adds APAC and removes LEGACY. Orders has migrated off LEGACY. Re-run it immediately and it’s a clean no-op — the copy already matches.

Sessions adopts (MySQL)

cd mysql
schemaquench --ConfigFile:quench.settings.adopt.json --LogPath:"$PWD/logs"

Same change to Sessions’ own copy. NA, EMEA, APAC. LEGACY removed. Sessions migrated off LEGACY independently of Orders — same pattern, same result, zero coordination.

The sequence within consumers is completely flexible. Orders can adopt before Sessions, or after, or on the same day. What matters is that both have adopted before anyone runs the contract.

Step 4 — Contract: Catalog removes LEGACY

Only after both consumers have adopted. Once no service’s copy carries LEGACY, Catalog can safely retire it from the canonical list.

cd postgres
schemaquench --ConfigFile:quench.settings.contract.json --LogPath:"$PWD/logs"

The contract tabledata — LEGACY gone:

[
  {"region_code":"NA","name":"North America","active":true},
  {"region_code":"EMEA","name":"Europe, Middle East & Africa","active":true},
  {"region_code":"APAC","name":"Asia-Pacific","active":true}
]

Insert/Update/Delete deletes the LEGACY row. The retire is complete. This is a release for one service only — Catalog. Orders and Sessions are unaffected. Their copies already dropped LEGACY at adopt time.

Certification note: All four steps ran exit 0 on the real sandbox. Catalog baseline: NA/EMEA/LEGACY delivered. Expand (Insert/Update): APAC added, LEGACY kept — four rows verified as additive. Contract (Insert/Update/Delete): LEGACY removed, three rows remain. Orders dbo.valid_region and Sessions valid_region: each went baseline (NA/EMEA/LEGACY) → adopt (NA/EMEA/APAC). Re-running any delivery after it lands is a clean no-op. Every step was a separate, independent deploy against one service’s database.

The one hard rule

Never contract before consumers adopt. That’s it. That’s the entire discipline.

If Catalog runs the contract while Orders still carries LEGACY in its copy, nothing breaks at the database level — the copies are independent. But the moment Orders’ application code tries to use LEGACY as a valid value, Catalog’s canonical list says it doesn’t exist anymore. The contract step is Catalog’s declaration that LEGACY is no longer a valid region. Every consumer has to make that same declaration, in their own copy, before Catalog makes it in the canonical one.

Everything else is flexible. The window between expand and contract can be a day or a month. Orders can adopt before Sessions or after. The pipelines can each have a different reviewer, a different cadence, a different deploy frequency. None of that coordination matters. The sequence is the constraint: expand, then adopt, then contract. In that order. That’s the whole protocol.

Why this is different from a coordinated migration

The alternative is a deployment window. Three teams, one night, every service deploying in a specific order with a rollback plan for each combination of partial-failure states. Anyone who’s been in that room knows how it goes.

Expand / contract eliminates the deployment window entirely. Catalog ships expand on Tuesday and nobody else is involved. Orders ships adopt when their next sprint closes. Sessions ships adopt a week later. Catalog ships contract when both teams confirm they’ve adopted. Four separate pipeline runs, four independent deploys, zero coordination overhead, zero shared release windows.

The independence you built in this course — native packages that speak each engine’s native DDL, file-less credentials per service, organized packages per team, path-filtered pipelines that only fire when their service changes — that independence is what makes this pattern safe. You can’t do expand / contract without independent deploy tracks. If your services share a release window, you’ve already broken the pattern before you write a single config.

Each service’s copy is its declaration. Catalog doesn’t control when consumers migrate off LEGACY. It controls when LEGACY disappears from the canonical list. The consuming team controls their own copy and their own deploy timeline. That separation of authority is what makes the whole thing work without a coordinator.

---

Here’s the arc you’ve forged across this course. Module 1: the same change applied to native packages on three engines, each speaking the DDL of its own database. Module 2: where native fidelity diverges — identity, native types, the things each engine does its own way, embraced rather than hidden. Module 3: organized package structure and file-less connection config, so any team member can find anything and credentials never touch source control. Module 4: one pipeline shape, three independent instantiations — path-filtered, WhatIf-gated, releasing on separate cadences. And here, Module 5: expand / contract across those independent services, sharing reference data without a shared release window.

That’s not a set of features. That’s a complete discipline for running polyglot schema management at scale. You built the whole thing, piece by piece, from a raw ingot to a hardened tool.

Subscribe and stick around. More’s coming from the forge.

Until then, may your expand land clean, your consumers adopt on their own terms, and your contract always find the table ready.

— Forge

Check yourself: Catalog's expand deploy uses `MergeType: 'Insert/Update'` while the baseline, adopt, and contract deploys use `MergeType: 'Insert/Update/Delete'`. What is the practical difference, and why does expand specifically need the additive form?

"Insert/Update/Delete" converges the table to exactly the rows in the file — it inserts missing rows, updates changed rows, and deletes anything in the table that isn’t in the file. "Insert/Update" is additive — it inserts and updates the rows in the file but leaves everything else alone. Expand uses the additive form because it needs to add APAC while keeping LEGACY intact. If expand used "Insert/Update/Delete", the expand tabledata (which includes LEGACY) would still work — but any consumer still on LEGACY would be exposed the moment Catalog decided to drop it from the file. The additive form makes expand safe to ship at any time, regardless of consumer state, because nothing is removed. Consumers still carrying LEGACY continue to work normally — the window stays open until they’re ready to adopt.

Check yourself: Why must the contract step come after every consumer has adopted — and what happens to the independent deploy principle if you skip that ordering?

The contract step removes LEGACY from Catalog’s canonical list using "Insert/Update/Delete". Once that runs, Catalog’s shipping_region no longer has a LEGACY row. If a consumer’s application code tries to validate an order or session against LEGACY and their own valid_region copy still carries it, the data inconsistency is contained to that service — but the moment the consumer’s application reaches Catalog’s API or canonical list, LEGACY is no longer a recognized value. The expand / contract protocol is designed so each consumer’s adopt step is their own declaration that they’ve migrated off the old value. Contract is Catalog’s declaration that the old value is gone from the canonical source. The rule is sequencing, not coordination: each team moves on its own schedule, but the order is always expand → adopt (all consumers) → contract. Skip that order — contract before a consumer adopts — and you’ve introduced a data integrity gap that the independent deploy architecture was specifically built to avoid.

Mark this lesson complete