Changing Production Data Safely

Goal

You will be able to go around the whole loop once: narrow a change request the customer sent as a sentence into a condition, build a way back, demonstrate the rollback on a copy, and then finish with the real apply and the control group verification.

Why it matters

In the earlier courses, the access you held was effectively read-only. A wrong query can be run again, and a wrong hypothesis can be deleted and rebuilt. Writing is different. A wrong UPDATE rolls back only as far as the way back you built in advance allows.

The four things this lab enforces are all rules from the field. Count before you translate the request sentence into a condition — the scope the requester pictured and the scope that actually changes drift apart, and until you count, neither side knows how wide the gap is. Build the way back first — a file copy is for rolling everything back at once and a row snapshot is for explaining what changed and how, so you need both. Do not leave the rollback as a statement — rollbacks usually fail for trivial reasons, but if you discover that trivial failure after the real apply, it is no longer trivial. Count what should not change as well — a verification without a control group passes even in an incident that touched the neighbors.

This time the request is as follows. The customer's operations owner sent "Please change all pending orders to cancelled", citing the settlement close. The reference date is 2026-08-29. But the 45 pending orders are not one lump. Some have been stuck for a long time, some came in yesterday and may still be in progress, and for some it cannot be confirmed who the customer is.

This lab narrows the target with two criteria — the creation date must be before 2026-08-15 (that day is not included), and the order's customer_id must exist in the customers table.

Steps

Create /root/change and load /opt/data/support.sql into /root/change/ops.db. Start on a working copy, not on the customer's original.
Write four lines to /root/change/scope.txt. request_total= is all pending rows that change if the request is translated literally, target= is the count narrowed by the two criteria above, excluded_recent= is the pending orders created on or after 2026-08-15, and excluded_orphan= is the old pending orders of customers that are not in customers.
Make a file copy at /root/change/ops.db.bak, and dump the before-change values of the target rows to /root/change/backup.csv with the header id,customer_id,amount,status,created_at.
Write the plan in /root/change/plan.md. It needs six sections, ## 요청, ## 대상과 범위, ## 영향, ## 되돌리는 방법, ## 검증 방법, and ## 중단 기준 (the Korean headings mean request, target and scope, impact, rollback method, verification method, and abort criteria), and ## 대상과 범위 must come before ## 되돌리는 방법.
Inside a transaction, run the UPDATE, read SELECT changes(), and then ROLLBACK. In /root/change/dryrun.txt, write changes= and after_rollback_pending=, the pending count taken again after the rollback.
Create /root/change/rehearsal.db from ops.db.bak, apply the same change, and restore it using backup.csv. In /root/change/rehearsal.txt, write applied=, restored_pending=, and diff=, the row-by-row comparison of the restored copy against ops.db.bak.
Apply the change for real to ops.db with the same condition, and leave changed= in /root/change/applied.txt.
Write four lines to /root/change/verify.txt — backup_ids_cancelled=, control_pending=, paid_unchanged=, and refunded_unchanged= — and append a ## 적용 결과 section (the Korean heading means apply result) to the end of plan.md to record the verified numbers.

Notes

If you read with sqlite3 -readonly <파일> "<쿼리>", you can prevent accidental writes (the placeholders are the database file and the query).
To bundle several statements into one transaction, pass them on standard input: sqlite3 ops.db <<'SQL' ... SQL.
For the row-by-row comparison, attach the two databases with ATTACH and count with EXCEPT.
Common mistake 1: in step 2, adding excluded_recent and excluded_orphan together. If the reasons for exclusion differ, you must count them separately so that you can write the reason in the plan.
Common mistake 2: in step 5, forgetting ROLLBACK and going all the way to COMMIT. Then the dry run becomes the real apply.
Common mistake 3: in step 6, doing the restore by writing a new UPDATE statement. In a real incident, the only thing left in your hands is the backup file, so you need to see whether you can get back using that file alone.

Create a working copy

Create /root/change and load /opt/data/support.sql into /root/change/ops.db. Start on a working copy, not on the customer's original.

The snapshot the customer gave you is /opt/data/support.sql. sqlite3 takes SQL on standard input — if you pass it as an argument, only an empty DB is left.

Count the gap between the request and the actual scope

Write four lines to /root/change/scope.txt. request_total= is all pending rows that change if the request is translated literally, target= is the count narrowed by the two criteria above, excluded_recent= is the pending orders created on or after 2026-08-15, and excluded_orphan= is the old pending orders of customers that are not in customers.

Count the condition translated literally from the request and the narrowed condition, each with COUNT. There are two criteria for narrowing — the creation date, and whether that customer exists in customers.

Make the change reversible

Make a file copy at /root/change/ops.db.bak, and dump the before-change values of the target rows to /root/change/backup.csv with the header id,customer_id,amount,status,created_at.

Make the file copy with cp and the row snapshot with .mode csv in sqlite3. The condition for the snapshot must be the same condition you narrowed to in step 2.

Write the change plan

Write the plan in /root/change/plan.md. It needs six sections, ## 요청, ## 대상과 범위, ## 영향, ## 되돌리는 방법, ## 검증 방법, and ## 중단 기준 (the Korean headings mean request, target and scope, impact, rollback method, verification method, and abort criteria), and ## 대상과 범위 must come before ## 되돌리는 방법.

Six sections are required. The rollback method section must say what you roll back with, down to the file path, and the abort criteria section is where you write what result will make you stop.

Dry-run without committing

Inside a transaction, run the UPDATE, read SELECT changes(), and then ROLLBACK. In /root/change/dryrun.txt, write changes= and after_rollback_pending=, the pending count taken again after the rollback.

Open with BEGIN, run the UPDATE, read the number of affected rows with SELECT changes(), and close with ROLLBACK. Writing that you rolled back and having actually rolled back are different facts, so count again.

Rehearse the rollback

Create /root/change/rehearsal.db from ops.db.bak, apply the same change, and restore it using backup.csv. In /root/change/rehearsal.txt, write applied=, restored_pending=, and diff=, the row-by-row comparison of the restored copy against ops.db.bak.

Do it on a separate copy, not on the production copy. When you roll back, do not write the condition again; read backup.csv and roll back from it — in a real incident, that file is the only thing you have in hand.

Apply for real with the narrowed condition

Apply the change for real to ops.db with the same condition, and leave changed= in /root/change/applied.txt.

Use a condition that is literally the same as the one you tested in step 5. If you feel the urge to touch the condition here, it means you have to do the dry run again.

Verify by counting the control group too

Write four lines to /root/change/verify.txt — backup_ids_cancelled=, control_pending=, paid_unchanged=, and refunded_unchanged= — and append a ## 적용 결과 section (the Korean heading means apply result) to the end of plan.md to record the verified numbers.

Count what should have changed and what should not have changed together. And the plan is closed only when it records the result — append the apply result section.