data_sets
A data correction is a SQL file that changes rows rather than structure. Each statement declares how many rows it should touch, and the correction is refused if reality disagrees. This page covers the file, its directives, and the policy that governs a set of them.
A correction file
Every directive appears here at least once. Read the comments as instructions to DBLift, not notes to the next person.
corrections/2026-08-11__restore_lapsed_trials.sql
-- dblift:formatted
-- Ticket OPS-4417. Trials cancelled by the 08-09 billing retry.
-- dblift: expect=138
UPDATE subscriptions
SET status = 'trialing',
cancelled_at = NULL
WHERE status = 'cancelled'
AND cancellation_reason = 'billing_retry_exhausted'
AND cancelled_at >= '2026-08-09';
-- dblift: expect=>=1 onfail=warn
UPDATE billing_attempts
SET retry_state = 'pending'
WHERE subscription_id IN (
SELECT id FROM subscriptions WHERE status = 'trialing'
);
-- dblift: expect=any onfail=skip
DELETE FROM dunning_queue
WHERE reason = 'billing_retry_exhausted';
-- dblift: no-undo
INSERT INTO ops_audit (ticket, note, recorded_at)
VALUES ('OPS-4417', 'Restored trials cancelled in error', CURRENT_TIMESTAMP);The directives
| Directive | Goes | What it does |
|---|---|---|
-- dblift:formatted | File header | Activates directive parsing for the file. Without it, no directive in the file is read — the correction still runs, just with defaults everywhere. |
expect=1 | Before a statement | The exact row count the statement must affect. Also accepts >=1, <=5 and any. When the count does not match, onfail decides what happens. |
onfail=halt | Before a statement | The default. An unmet expect aborts the correction and rolls the whole thing back. |
onfail=warn | Before a statement | Records a warning and carries on. The statement stays applied. |
onfail=skip | Before a statement | Records a skipped note and carries on. Because the correction runs in one transaction, the statement has already executed — skip does not undo it. |
no-undo | Before a statement | Marks the statement as not reversible. No inverse DML is generated for it, so data undo will not restore what it changed. |
The header is not optional decoration
A file without -- dblift:formatted is never directive-parsed. Every expect in it is ignored silently and the statements run unguarded. The header can appear anywhere in the file. If a correction matters enough to assert row counts, check the header is present.
Configuring a set
Corrections are grouped into named sets. A set says where its files live, which columns identify a row, and what it will not allow.
data_sets:
billing:
directories:
- "./corrections/billing"
keys:
subscriptions: [id]
billing_attempts: [id]
policy:
require_expect: true
allow_full_table: false
max_affected_rows: 5000
environments:
prod:
data_sets:
billing:
policy:
undo: required
max_affected_rows: 500
denied_tables: [ledger_entries]The environment block overlays the root set, so production can be stricter than staging without a second set of files. Here production halves the row ceiling, insists every correction be reversible, and puts the ledger out of reach entirely.
Set keys
| Key | Default | What it does |
|---|---|---|
directories | [] | Where this set's correction files live. Scanned recursively unless the entry says otherwise. |
keys | {} | Per-table restore columns. Used to build the inverse statement that undo replays. With undo: required, each key must be a primary key, a UNIQUE constraint, or a unique index that is not partial and not an expression. Without undo: required, an unverified restore key is only a warning. The same check applies to the restore_key_columns fallback. Whether the constraint is enabled and validated is read on Oracle, SQL Server, and Db2. Redshift keys are never verified. |
apply_mode | rerun | Stored on the set; not consumed at runtime today. |
authoring | imperative | Stored on the set; not consumed at runtime today. |
history_table | dblift_data_history_{name} | Audit ledger for this set. Empty still resolves to dblift_data_history_<set>. Each data set needs its own table. Two sets that resolve to the same name, ignoring case, are rejected when the file loads. |
Policy keys
data plan checks the rules that need no execution and records a violation on the correction. policy.triggers and policy.cascades are applied by data apply (and --dry-run). data plan reports the impact and the undo verdict but does not reject on those two keys. data apply checks the other rules again, and it is the only step that can enforce max_affected_rows, because the row count is not known until the statement runs.
| Key | Default | What it does |
|---|---|---|
enabled | true | Turns the data set off without deleting its files. Checked at plan time. |
require_expect | false | Every statement must carry an expect directive, or the plan is refused. |
allow_unchecked | true | Set to false to reject statements whose row count nobody asserted. Checked at plan time. |
undo | optional | Set to required and a correction without a reversible path is refused at plan time. The calling licence must be able to capture images. |
triggers | follows undo | allow, warn, or reject. Absent means reject when undo is required, and warn when undo is optional. An enabled trigger on an affected table is the finding. |
cascades | follows undo | allow, warn, or reject, with the same default as triggers. Enterprise images a direct child (cascade-delete, set-null, set-default), so reject lets that depth-1 cascade through on Enterprise and refuses it on Pro. A deeper cascade, or a parent-key update, is not restored on either tier. |
allow_full_table | true | Set to false to reject any statement with no WHERE clause. Checked at plan time. |
max_affected_rows | — | Ceiling on rows a single correction may touch. Enforced at apply, not at plan. |
allowed_tables | — | Allowlist. When set, only these tables may be corrected. Checked at plan time. |
denied_tables | — | Denylist, applied after the allowlist. Checked at plan time. |
max_statement_bytes | 2000000 | Size ceiling on a single statement. Checked at plan time. |
restore_key_columns | id, pk, uuid, key | Fallback columns when no primary key is introspected. With undo: required, the key that is used must be a primary key, a UNIQUE constraint, or a unique index. |
Not available on document stores
Corrections are SQL from end to end — the file is split into statements, each gated by a row count, inverted to build the undo, and applied in one transaction. A document store offers none of those, so DBLift refuses rather than half-emulating them. On Azure Cosmos DB or MongoDB, express the change as a Python migration against the provider SDK instead.