How to Handle Ad-hoc Database Data Changes?

Schema change is the planned kind. New columns, new tables, new constraints — they ride through a pull request, a migration tool, a deployment pipeline.

Data change is messier. Some of it is planned too — backfills, ETL jobs, scheduled cleanups — and runs through the same pipeline. The rest is ad-hoc: an incorrect tax rate on yesterday's invoices, a stuck row blocking a job, an ANALYZE during an incident. It starts as a Slack message and ends as a query against production.

This post walks the ad-hoc data change workflow end-to-end — where it usually goes wrong, the four phases that fix it, and the tools that fit.

What counts as ad-hoc data change#

The ad-hoc lane is for one-time exceptions:

• Correcting wrong values on a small set of rows.
• Unsticking a row that an application can't get past.
• Backfilling a single missing record.
• Running ANALYZE or a session-level tweak during an incident.
• Removing test data that leaked into production.

What it is not: a backfill across millions of rows (that's a data migration), an ALTER TABLE (that's schema change), a recurring cleanup (that's an automated job).

Common mistakes#

The mistakes that erode the audit trail are different from the schema-change ones, because the change starts in a SQL client rather than a pull request.

1. No row-count preview. The UPDATE runs without first running the SELECT from the same WHERE clause. The blast radius is whatever the database happens to match.
2. No before-image. The new value is captured (it's in the statement); the old value isn't. The change is one-way by the time anyone notices.
3. No transaction. A typo in the WHERE clause runs unwrapped. There is nothing to roll back.
4. No record of why. The query reaches the database log; the support ticket, the approver, and the justification do not. Six months later, the audit has the act without the motive.
5. Recurring "ad-hoc." The same fix appears twice. After the second occurrence, it should have become a migration, a constraint, or an admin tool.
6. DDL riding along with DML. An ALTER TABLE slips into the same session because it felt urgent. Drift created by hand-edited DDL is harder to reconcile than a missed deadline.

The workflow#

The shape that closes these gaps has four phases. Request. Authorize. Execute. Record. The gate moves from pre-merge to pre-execution, because there is no merge.

Request#

A ticket captures the SQL, the reason, and the blast radius before execution. Not "fix billing for customer 12345" — the actual UPDATE statement, the rows it will touch, the expected count. Without the SQL, there is nothing to review.

Authorize#

An approver sees the statement, a row-count preview from the same WHERE clause, and the requester's justification. Routine fixes against low-tier data — a peer approves. Production DML — two approvers, one a DBA. The approval lives on the same record as the execution.

Execute#

The SQL runs through a gated session, not a personal client. The session is bound to the approved statement; the row count is verified before commit; the transaction is wrapped automatically. The executor is the platform's service account — the requester's identity is on the record, not on the connection.

Record#

Every executed statement is logged with text, rows affected, executor, requester, approval chain, and timestamp. Exports — the read-path equivalent — are logged the same way. The record has the same shape as a migration record and lands in the same audit log.

A fix through the workflow#

Support reports that 137 invoices on 2026-05-09 show the wrong tax rate — 8.875% applied where 8.625% was correct. The fix is a single UPDATE against production.

Request. Engineer files a change ticket with the statement:

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

Attached: the support ticket ID, a row count from the same predicate (SELECT count(*) … → 137), and the financial impact ($412.50 refund total).

Authorize. Backend lead reviews the SQL. Finance lead reviews the impact. SQL review flags one issue: the UPDATE does not capture the previous tax_rate or tax_amount — no audit-side reversibility. The engineer prepends an INSERT into invoice_corrections. Re-submitted, re-approved.

Execute. The platform opens a gated session. Both statements run in one transaction. Row count returns 137 — matching the preview. The engineer confirms; the transaction commits.

Record. The change log captures both statements, the row count, the two approvers, the support ticket reference, and the financial-impact attachment. Three months later, an auditor reconstructing the correction needs one query: pull the change record. The before-image lives in invoice_corrections; the after-image lives in invoices.

The same fix without this workflow: a DBA opens a SQL client, pastes the UPDATE, runs it. The audit trail is whatever bash history survives.

Where it stops short#

The ad-hoc workflow gates one class of work: unplanned, low-volume, time-sensitive data change. Several others fall outside.

• Recurring fixes. If the same fix appears twice, it stops being ad-hoc. The pattern belongs in a migration — fix the constraint, fix the application, or write a backfill. The ad-hoc lane is for exceptions, not workarounds that hardened into routine.
• Large backfills. A statement that touches millions of rows should not run through interactive approval. Wrap it in a migration script, batch it, run it through the pipeline.
• Schema repair. An ALTER TABLE does not belong in the ad-hoc lane even when the situation feels urgent. Drift created by hand-edited DDL 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.

The ad-hoc workflow gates urgent, scoped DML. It does not replace migrations, and it is not a substitute for fixing the underlying problem.

Tools that fit#

Three approaches cover the shape. Comparison sits at the category level — there is no Liquibase-or-Flyway for ad-hoc DML.

• Bastion + SQL client. Engineer connects through a jump host and runs the statement in a local client. The bastion captures the session log. Approval — if any — happens in Slack. Audit is bash history plus the chat thread.
• PAM + ticketing. A PAM tool issues a time-bound credential after the ticket system records an approval. Execution still runs in the engineer's local client. Audit is split: PAM has the credential, the database has the query log, the ticket has the approval, no system has all three.
• Change-management platform. Request, approval, execution, and audit share one record. The platform holds the credential; the engineer never sees it. SQL review runs at request time and again before execute. Row-count preview and before-image capture happen automatically.

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.

Summary#

Schema change has a mature lane — version control, migration tools, deployment pipelines. Ad-hoc data change has historically run outside that lane: SQL client, bash history, Slack approval. The mistakes that erode the audit trail come from the missing pre-execution gate, not from carelessness.

The four-phase shape — request, authorize, execute, record — closes the gap. The planned counterpart, How to Handle Database Migration / Schema Change, covers the other lane. Together they map both classes of change that touch production data.

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 means in practice.