Script & data failures: find the error fast

The tables built. The indexes took. The foreign keys closed. Every phase the engine computes for you went green — and then the deploy stopped on a line you wrote. A backfill script with a missing value. A data file pointing at a customer who doesn’t exist.

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

Modules 1 through 3 were about the mechanical engine — the DDL SchemaSmith figures out on its own. This one’s about the metal you bring to the forge: your Before and After scripts, and your data delivery. The forge doesn’t second-guess what you hand it. It strikes it. And when your input can’t take the heat, it fails in a place with its own evidence trail — different from anything you’ve read so far.

The triage split

When a quench stops, the first question is whose problem it is: the engine’s computed DDL, or your input? One line of SchemaQuench - Progress.log answers it.

A mechanical phase writes the engine error straight into the FAILED to quench: block, and its artifact is a copy-runnable EXEC/CALL of a proc. No batch marker.
A user script writes Unable to quench '<path>': <error> — naming the exact file — and drops a Failed <script> artifact.
A data delivery writes Error delivering <table>: <error>, and drops a Failed DataDelivery artifact.

Here’s the tell: both of your failures — script and delivery — name the item that broke. A mechanical failure never does; it just reports the phase. So read for the per-item line. That word — Unable to quench '<some file>' or Error delivering <some table> — means the metal was yours.

One honest catch: your failures also print a generic FAILED to quench: / Unable to quench all scripts at the very end. So FAILED to quench: on its own doesn’t prove a mechanical fault. The per-item line above it is what you read.

Beat 1 — a backfill script that forgot a required column

The lab arms this one in an After-script, 02_backfill_customers.sql — a run-once backfill onboarding customers from a legacy system. Two batches: an idempotent name-tidy, then one INSERT of the migrated rows. But the last row never got its email, and Email is NOT NULL:

INSERT dbo.Customer ([CustomerId],[Email],[FullName]) VALUES
  (10, 'devon.p@shop.test', 'Devon Price'),
  (11, 'erin.k@shop.test',  'Erin Knox'),
  (12, 'farah.n@shop.test', 'Farah Nasser'),
  (13, NULL,                'Gil Overton');   -- Email is NOT NULL

Deploy beat1-broken and it stops at the After slot — Quenching after database scripts, exit 2. The per-item line names your file:

Unable to quench '.\beat1-broken\Templates\Main\After Scripts\02_backfill_customers.sql':
  Cannot insert the value NULL into column 'Email', table 'diag_scripts.dbo.Customer';
  column does not allow nulls. INSERT fails.

Same failure, three dialects:

Engine	Error
SQL Server	`Cannot insert the value NULL into column 'Email'… column does not allow nulls.` (error `515`)
PostgreSQL	`23502: null value in column "email" of relation "customer" violates not-null constraint`
MySQL	`Column 'Email' cannot be null` (error `1048`)

Now open the artifact it named — artifacts/SchemaQuench - Failed 02_backfill_customers ….sql. Header repeats the error, and a -- >>> FAILING BATCH (#N) >>> marker sits right on the statement that broke. The whole script is in there, expanded and copy-runnable: paste the flagged batch into your client and it fails identically. That’s your reproduction, handed to you.

One detail worth knowing — the batch marker isn’t the same number everywhere

SchemaSmith splits a script into batches before it runs them, and how it splits depends on the engine:

SQL Server splits on GO. PostgreSQL splits on ;. Two batches each — the tidy-up, then the INSERT — so the marker reads (#2), pointing past the innocent UPDATE straight at the INSERT.
MySQL runs a plain ;-separated script as one batch. So the marker reads (#1) — the whole script is batch one, and you read the error text to find the line.

That’s a real per-engine difference, and a preview of Module 6. It also means one thing today: GO is a SQL Server separator. Put it in a Postgres or MySQL script and the engine hands it to the database as literal SQL — you’ll get a syntax error on the word GO, not the failure you were chasing. Native separators per engine.

Recover — fix the source, redeploy. Give row 13 a real address and deploy the same config again:

  (13, 'gil.o@shop.test',   'Gil Overton');

A script that failed is never marked complete, so a plain redeploy runs it again — no special flag. Exit 0, all seven customers land. And because that INSERT is a single atomic statement, the failed run committed nothing, so the retry never trips over half-inserted rows. Fix the metal, strike again.

Beat 2 — delivering a child that has no parent

beat2-broken hands SalesOrder a DataDelivery block — a MERGE-based load from a .tabledata file. Three orders. One of them, OrderId 103, is stamped for CustomerId 999 — and nobody by that number was ever seeded. An orphan.

Deploy it and the quench stops at the TableData slot, exit 2. Data delivery orders parents ahead of children and retries to resolve dependencies, so you’ll see it attempt SalesOrder, then the per-item line:

Error delivering SalesOrder: The MERGE statement conflicted with the FOREIGN KEY
  constraint "FK_SalesOrder_Customer". The conflict occurred in database
  "diag_scripts", table "dbo.Customer", column 'CustomerId'.

Engine	Error
SQL Server	`The MERGE statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_Customer"…` (error `547`)
PostgreSQL	`23503: insert or update on table "salesorder" violates foreign key constraint "fk_salesorder_customer"`
MySQL	`Cannot add or update a child row: a foreign key constraint fails (…FK_SalesOrder_Customer…)` (error `1452`)

Same foreign-key family you met in Module 3 — but it surfaces somewhere new. Module 3’s orphan was already resident and the FK phase caught it. This one arrives in the delivery itself: the MERGE that inserts the row is the thing the constraint rejects.

The artifact — artifacts/SchemaQuench - Failed DataDelivery SalesOrder#0 ….sql — is the generated MERGE, copy-runnable. On SQL Server it’s an OPENJSON shredding your .tabledata straight into a MERGE INTO [dbo].[SalesOrder]. No amount of parent-first ordering or retry saves a row whose parent simply isn’t there — the fix is in the data you shipped, not the engine.

Recover — fix the content, redeploy. Reparent the orphan in the .tabledata file — point 103 at a customer who exists:

{"OrderId":103,"CustomerId":1,"OrderDate":"2026-03-03T00:00:00","Status":"Open"}

Data delivery runs every deploy — the MERGE is idempotent — so the corrected file lands clean. Exit 0, three orders delivered, every one answering to a real customer.

What you can read now

One more artifact ties this together (PR #338, refined in #343; see Module 1): Failures.log flags a script or data-delivery failure too — and its roll-up reads just like a mechanical phase. The top-line Error: names the specific Unable to quench '<path>': <error>, and Debug SQL: points at the resolved-SQL artifact instead of n/a. The Context (last 25 lines) block still carries the phase trail, and that trail still ends in the generic FAILED to quench: / Unable to quench all scripts line — that’s the captured Progress.log tail, not a second diagnosis. Several failed scripts in one scope stack onto the Error: line with a (+N more) tail. So the roll-up tells you that a script broke, where, and which artifact to open — then you open it for the >>> FAILING BATCH detail this module is about.

Three failure shapes, and you can name which one you’re looking at from a single log line:

Mechanical phase — error in the FAILED to quench: block, artifact is a proc call, no item named.
User script — Unable to quench '<path>':, a Failed <script> artifact with the batch marker.
Data delivery — Error delivering <table>:, a Failed DataDelivery artifact with the copy-runnable MERGE.

Locate, read, recover — same method, now across every layer of the deploy.

Check yourself: A deploy fails, and Progress.log shows a `FAILED to quench:` block near the end. Your teammate says 'must be the engine's DDL — that's the mechanical-failure block.' Why is that not enough to conclude?

Because a user-supplied failure — a Before/After script or a data delivery — also prints that generic FAILED to quench: / Unable to quench all scripts line at the end of the run. It’s a summary, not a diagnosis. The real tell is the per-item line just above it: Unable to quench '<path>': means one of your scripts broke, and Error delivering <table>: means a data delivery broke. A true mechanical-phase failure names no item — it reports the phase and writes the engine error directly, and its artifact is a copy-runnable EXEC/CALL, not a Failed … file. Read for the item name before you decide whose problem it is.

What’s next

You’ve read the whole failure surface. Two modules left:

Module 5 · The recovery toolkit — --ResumeQuench, marking a script done in CompletedMigrationScripts, and the fix-and-continue playbook for when a plain redeploy from the top isn’t what you want.
Module 6 · Per-engine dialects & your runbook — how the same failure reaches you differently on each engine (you already caught a glimpse in the batch marker), and assembling everything into a team diagnostic runbook.

The forge shapes the metal it computes — tables, keys, indexes — and it hardens exactly to your model. But the scripts and the data are metal you carry in. When a quench stops on your INSERT or your delivery, it’s stopped at the exact statement where what you brought won’t hold, and it hands you that spot on a plate — the named line, the flagged batch, the copy-runnable artifact. Read it, fix the source, strike again.

Got a script or a data load that stopped a deploy cold? Email me at forgebarrett@schemasmith.com — tell me the per-item line and what the engine said, and we’ll trace it together.

Next up: Course 8 · Module 5 — The recovery toolkit, where a plain redeploy isn’t the only move.

Until then, may every script you carry to the forge be as sound as the steel it shapes.

— Forge

Mark this lesson complete