Gate reference data by environment

Your seed data ships with your schema — one DataDelivery block, same rows to every database. Then dev wants a fat test catalog and a pile of throwaway orders, prod wants a lean curated set and none of that test data, and you’re back to hand-editing rows per environment or keeping a separate package per target. The versioned-data win you got in Course 2 quietly springs a leak.

Course 2 Module 5 attached reference data to a table and delivered it everywhere. This recipe gates that delivery on the target, so one package carries every environment’s data and the deploy hands each database exactly the rows it should have.

The lever: a gate on the delivery

Data delivery grows two fields:

ShouldApplyExpression — a SQL predicate run against the target at deploy time. True delivers, false skips (and says so), blank always delivers — the behavior you already have.
VariantName — a label for the gate’s intent. It lands in the deploy log whether the delivery fires or skips, so the run reads like a decision log instead of a black box.

And DataDelivery becomes either the single object you know or an array of independently-gated deliveries. The lab deploys one package to two databases — cookbook_r7_dev and cookbook_r7_main — in a single quench. The schema builds on both unconditionally; only the data is gated. Three tables, three patterns.

Environment-gated fixtures — one gate on one delivery

SampleOrder holds throwaway test orders. You want them in dev and test, never in prod. One gated object does it:

"DataDelivery": {
  "ContentFile": "data/dbo.SampleOrder.tabledata",
  "MergeType": "Insert/Update",
  "MatchColumns": "OrderId",
  "ShouldApplyExpression": "DB_NAME() LIKE '%_dev' OR DB_NAME() LIKE '%_test'",
  "VariantName": "Dev/test sample orders"
}

The table gets created everywhere; its rows only land where the database name ends _dev or _test. On cookbook_r7_main the log is blunt about it:

[localhost,11433].[cookbook_r7_main]    Skipping data delivery for dbo.SampleOrder [Dev/test sample orders] - ShouldApplyExpression evaluated false

Prod ends up with the SampleOrder table, empty — exactly what you want.

Per-environment variants — an array of gates that never both fire

ProductCatalog ships a rich six-row catalog to dev and a lean two-row set to prod. Same table, same MatchColumns, two content files, two gates that are mutually exclusive:

"DataDelivery": [
  { "ContentFile": "data/dbo.ProductCatalog.dev.tabledata",  "MergeType": "Insert/Update", "MatchColumns": "Sku", "ShouldApplyExpression": "DB_NAME() = 'cookbook_r7_dev'",  "VariantName": "Rich dev catalog" },
  { "ContentFile": "data/dbo.ProductCatalog.main.tabledata", "MergeType": "Insert/Update", "MatchColumns": "Sku", "ShouldApplyExpression": "DB_NAME() <> 'cookbook_r7_dev'", "VariantName": "Lean prod catalog" }
]

On dev, one fires and one skips:

[localhost,11433].[cookbook_r7_dev]     Delivering dbo.ProductCatalog [Rich dev catalog]
[localhost,11433].[cookbook_r7_dev]     Skipping data delivery for dbo.ProductCatalog [Lean prod catalog] - ShouldApplyExpression evaluated false

dev gets six rows, main gets two. The gate is the environment switch, and the VariantName in the log tells you which set won on each database.

Additive slices — an array where every gate fires

StatusCode builds its prod reference set from two slices that both apply: core global codes plus regional codes. This is the part worth internalizing — data delivery is not “one match wins.” Every delivery whose gate passes applies, in declared order. That’s what makes slices additive.

"DataDelivery": [
  { "ContentFile": "data/dbo.StatusCode.core.tabledata",     "MergeType": "Insert/Update", "MatchColumns": "Code", "ShouldApplyExpression": "DB_NAME() = 'cookbook_r7_main'", "VariantName": "Core status codes" },
  { "ContentFile": "data/dbo.StatusCode.regional.tabledata", "MergeType": "Insert/Update", "MatchColumns": "Code", "ShouldApplyExpression": "DB_NAME() = 'cookbook_r7_main'", "VariantName": "Regional status codes" }
]

[localhost,11433].[cookbook_r7_main]    Delivering dbo.StatusCode [Core status codes]
[localhost,11433].[cookbook_r7_main]    Delivering dbo.StatusCode [Regional status codes]

Both fire on main — three core rows plus two regional, five total. On dev both skip. Re-run the whole deploy and every count holds: gated delivery is as idempotent as ungated.

Check yourself: On cookbook_r7_main, the two ProductCatalog deliveries yield ONE variant's rows, but the two StatusCode deliveries BOTH land. Both tables use a two-element DataDelivery array — why the different outcome?

Because data delivery applies every delivery whose gate passes — it’s additive, not “one match wins.” So the outcome is decided by how many gates evaluate true on that target. ProductCatalog’s two gates are mutually exclusive (= 'cookbook_r7_dev' vs <> 'cookbook_r7_dev'), so exactly one passes on any database — you get one variant. StatusCode’s two gates are both = 'cookbook_r7_main', so on main both pass and both slices deliver, in declared order — core rows plus regional rows, additively. Same array shape; the gates decide whether it behaves as an either/or variant or an additive set.

Authoritative slices — full-sync behind a fence

Insert/Update only ever adds and changes. Pull a row out of StatusCode.core.tabledata, redeploy, and the old row’s still sitting in the table — the delivery never told it to leave. For a lookup set that’s meant to be the source of truth, that’s a slow leak: retired codes linger, and the table drifts from the file that defines it.

Insert/Update/Delete closes the gap. The slice goes authoritative — anything in the target that isn’t in the source gets removed. Ship three GLOBAL codes and the table holds exactly those three. Drop one from the file, redeploy, and it’s gone from the database too.

There’s a catch the moment two full-sync slices share a table. StatusCode is fed by two deliveries. Make both Insert/Update/Delete and the core slice — three GLOBAL rows — looks at the two regional rows, decides they don’t belong, and deletes them. Then the regional slice deletes the core rows right back. Each slice wipes the other’s work, every single deploy.

MergeFilter is the fence. It’s a predicate that scopes a slice’s authority to its own partition, so the delete only reaches rows that slice actually owns:

"DataDelivery": [
  { "ContentFile": "data/dbo.StatusCode.core.tabledata",     "MergeType": "Insert/Update/Delete", "MatchColumns": "Code", "MergeFilter": "Target.Region = 'GLOBAL'",        "ShouldApplyExpression": "DB_NAME() = 'cookbook_r7_main'", "VariantName": "Core status codes" },
  { "ContentFile": "data/dbo.StatusCode.regional.tabledata", "MergeType": "Insert/Update/Delete", "MatchColumns": "Code", "MergeFilter": "Target.Region IN ('EMEA','APAC')", "ShouldApplyExpression": "DB_NAME() = 'cookbook_r7_main'", "VariantName": "Regional status codes" }
]

Target is the row already in the table. Core governs Region = 'GLOBAL' and nothing else; regional governs EMEA and APAC and nothing else. Neither slice can see the other’s rows, so neither can delete them.

Prove it. Deploy the five rows, then pull HELD out of the core file and redeploy:

Core   (Target.Region = 'GLOBAL')       → OPEN, CLSD      HELD deleted — it left the source
Region (Target.Region IN ('EMEA','APAC')) → EMEA, APAC     untouched — different partition

Four rows, exactly the right four. Core retired HELD because core owned HELD; EMEA and APAC never flinched. Full-sync hands you the authority; the filter keeps that authority in its lane.

One portability note. MergeFilter is raw predicate text dropped straight into the merge, so the column reads in the target’s own dialect — but the alias is the same idea everywhere: Target is the row in the table. SQL Server and MySQL take Target.Region; PostgreSQL folds unquoted names to lowercase, so it’s "Target".region. One fence, three spellings.

Check yourself: Both StatusCode slices are now Insert/Update/Delete against the same table. Without the MergeFilter, what breaks on redeploy — and how does the disjoint filter fix it?

Without a filter, each full-sync slice treats every row it didn’t deliver as a stray to delete — so the core slice deletes the regional rows, the regional slice deletes the core rows, and they undo each other on every deploy. The disjoint MergeFilter scopes each slice’s delete to its own partition: core only governs Region = 'GLOBAL', regional only EMEA/APAC. A slice can insert, update, and delete freely inside its lane and can’t touch anything outside it. The insert/update behavior is unchanged — the filter only fences the delete.

Two things to hold onto

An array of two or more deliveries requires a gate on every entry — an ungated one beside gated ones would always apply and defeat the purpose, so loading it fails with a clear error before any deploy work starts. A single delivery can still be ungated (always applies).

And a DataDelivery.ShouldApplyExpression is not token-resolved — unlike a component or folder gate, it doesn’t substitute {{Token}} placeholders ({{SchemaName}} included). Write it against what you can query on the target itself: the database name — DB_NAME() on SQL Server, current_database() on PostgreSQL, DATABASE() on MySQL — or a catalog lookup. That per-engine predicate is the only thing that changes across the three; the gate mechanism, the array shape, the all-fire semantics, and the skip logging are identical everywhere.

Reference data isn’t one thing you stamp into every copy — it’s a set of dies, and the target decides which ones strike. The dev fire takes the full catalog and the test orders; the prod fire takes the lean set and the codes that matter, and refuses the rest. One package holds every die; the gate on each delivery is what tells the forge which to bring down on the metal in front of it. Same source, versioned together — and every database ends up with exactly the rows it asked for, nothing it didn’t.

Got reference data that drifts between environments, or a test-data set you’re scared will leak into prod? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may every row find the target that called for it, and none ever land where it wasn’t wanted.

— Forge

Mark this lesson complete