How to Handle Ad-hoc Database Data Changes?

Schema change is the planned kind. New columns. New tables. New constraints. Each one rides through a pull request, a migration tool, a deployment pipeline.

Ad-hoc data change is the other kind. You get the Slack message at 3pm: 137 invoices billed at the wrong tax rate, finance needs it fixed before EOD. You open a SQL client, write the UPDATE, and pause. Your hand is on the run button. The WHERE clause looks right. You've done this a hundred times. The hundred-and-first is the one you remember.

This post is for the engineer with their hand on that button. The mistakes that put it there. The four phases that take it away. The controls that enforce them.

What counts as ad-hoc#

You know it when you see it. One-time fixes that don't fit anywhere else:

• Correcting wrong values on a small set of rows.
• Unsticking a row your application can't get past.
• Backfilling a single missing record.
• Removing test data that leaked into production.

Where it breaks down#

The mistakes here are different from the schema-change ones. They start in a SQL client, not a pull request — and they share a shape.

1. You don't know the blast radius. You wrote the UPDATE, but you didn't run the SELECT from the same WHERE clause. You're about to find out how many rows match by running it.
2. You're capturing the new value, not the old one. The new value is in your statement. The old one isn't anywhere. The moment you commit, the change is one-way.
3. You're running without a transaction. A typo in the WHERE clause runs unwrapped. Nothing to undo. You'll be reading the backup window timestamp out loud to your manager.
4. The approval is whatever Slack you can get. A one-row fix in staging and a thousand-row update in prod move through the same chat thread. The thread is either over-gated for the small fix or under-gated for the big one.
5. You're tabbing between eight databases. A multi-tenant fix means eight psql sessions and eight pastes. The seventh is where the typo lives, and you won't notice until customer support emails about tenant 7.
6. The "why" lives somewhere else. The query reaches the database log. The support ticket, the approver, your reasoning — they don't. Six months later, the audit has the act without the motive.
7. The same fix is back. You ran this one last quarter too. After the second occurrence, the lane isn't ad-hoc — it's a missing migration, a missing constraint, or a missing admin tool.

Request. Authorize. Execute. Record.#

Four phases close the gaps. The gate moves from pre-merge to pre-execution, because you're not merging anything.

Request#

Before you run anything, the statement goes in front of you. Not "fix billing for customer 12345" — the actual SQL, the row count from the same WHERE clause, the target environment, the table touched. Automated SQL review runs as you submit: missing WHERE, missing LIMIT, forbidden table, destructive pattern — all flagged before you page anyone.

Authorize#

Your approval matches your risk. A 5-row fix in staging routes to a peer. A 5,000-row UPDATE against invoices in prod routes to the table owner and a DBA. Same lane. Different gates. The request reaches your approver through IM — a direct message, not a chat thread they'll scroll past.

Execute#

You run through a gated session bound to the approved statement — no personal client, no free-form SQL. The statement is wrapped in a transaction. The row count is checked against your preview before commit. A before-image is captured. The platform's service account holds the credential; your identity stays on the record, not on the connection. Run it now or schedule it into a maintenance window. For multi-tenant fixes, one approved statement fans out across the target databases in a single batch.

Record#

Every executed statement is logged: text, rows affected, executor, requester, approval chain, risk score, timestamp. The before-image is retained for the rollback window. Exports — the read-path equivalent — log the same way.

A fix through the workflow#

Back to the 137 invoices. 8.875% applied where 8.625% was correct. The fix is a single UPDATE against production.

Request. You file a change ticket with the statement:

UPDATE invoices
SET tax_rate   = 8.625,
    tax_amount = subtotal * 0.08625,
    updated_at = NOW()
WHERE billing_date = '2026-05-09'
  AND tax_rate     = 8.875
  AND region_id    = 14;

You attach the support ticket ID, the row-count preview (137), the financial impact ($412.50 in refunds), the environment (prod), and the table (invoices, flagged as financial).

Authorize. The platform scores it as high risk — prod, financial table, three-digit row count — and routes it to the backend lead and the finance lead. If you'd submitted the same shape against staging, it would have gone to a single peer. SQL review flags a missing LIMIT; you dismiss the warning because your predicate is bounded.

Execute. The platform opens a gated session and snapshots the matched rows before running. Row count returns 137 — matching your preview. You confirm. The transaction commits.

Record. The change log captures the statement, the row count, the snapshot, the two approvers, the support ticket, and the financial-impact attachment. Twenty minutes later, finance comes back: the rate should have been 8.5%, not 8.625%. One click on the change record restores the prior values from the snapshot. You file a new ticket with the correct rate and walk it through the same lane.

The same fix without this workflow: you opened a SQL client, pasted the UPDATE, ran it. The rollback is whoever can reconstruct the old values from last night's backup. You spend the rest of the afternoon reading retention policy.

One statement. Every tenant.#

The single-database case is the easy one. The hard one: you have to land the same fix in eight shards, or thirty regional databases, or one schema per customer.

This is where most of the accidents happen. Eight tabs. Eight psql sessions. Eight pastes. The typo on the seventh — and you find out from customer support, not from yourself.

One approved statement closes it. One batch execution across your database group. One consolidated record. Each target gets its own row-count check and its own before-image. If three of eight return unexpected counts, those three halt and the rest commit — the record shows you which ran, which didn't, and what each touched.

Where the lane ends#

The ad-hoc workflow gates one class of work: unplanned, low-volume, time-sensitive data change. Some things look like ad-hoc but aren't.

• Recurring fixes. If you've run this fix before, you're not in the ad-hoc lane anymore — you're in a missing migration, a missing constraint, or a missing admin tool. Build the right thing. Stop paying interest.
• Large backfills. A statement that touches millions of rows does not belong in interactive approval. Wrap it in a migration script, batch it, run it through the pipeline.
• Schema repair. Your ALTER TABLE does not belong in the ad-hoc lane, even when the situation feels urgent. Drift you create by hand is harder to reconcile than a missed deadline.
• Read-path investigation. Querying production to investigate is its own workflow — same audit log, different approval shape. Briefer review. Narrower window. No commit.

Tools that fit#

Three approaches cover the shape. Pick the one that matches your audit tolerance.

• Bastion + SQL client. You connect through a jump host and run the statement in a local client. The bastion captures your session log. Approval — if there is one — happens in Slack. Risk evaluation, before-image capture, multi-database fan-out: all on you.
• PAM + ticketing. A PAM tool hands you a time-bound credential after the ticket system records an approval. You still run from a local client. Your audit is split across three systems: PAM has the credential, the database has the query log, the ticket has the approval. Rollback is whatever your backup policy gives you.
• Change-management platform. Request, risk evaluation, approval, execution, and audit share one record. Row count and environment drive the approval chain. A before-image snapshot is captured at execute time; rollback is one click. One approved statement fans out across a database group in one batch.

Bastion-led teams trade audit completeness for setup speed. PAM-led teams trade integration completeness for credential safety. Platform-led teams pay the cost of consolidation and get one record per change — with a rollback path attached.

Summary#

Schema change has a mature lane: version control, migration tools, deployment pipelines. Ad-hoc data change has historically run outside it: your SQL client, your bash history, your Slack thread. The gap is the missing pre-execution gate — the moment your hand is on the run button with nothing between you and prod.

Four phases close it. Request. Authorize. Execute. Record. Risk routes the approval to the right person. A snapshot makes the change reversible. Batch execution holds multi-tenant fixes together. The planned counterpart, How to Handle Database Migration / Schema Change, covers the other lane.

The 6 levels of database automation places this gap on the maturity curve. Most teams plateau at Level 3 — planned change automated, ad-hoc change still done by hand. Closing the ad-hoc gap is what Level 4 looks like for you.