LEARN. PRACTICE. GROW.Reserve time using proportionate evidence
A window ending at minute 120 and requiring 25 for recovery, ten for validation, and five for margin has its latest safe recovery start at minute eighty. At minute 76, an indivisible ten-minute step does not fit before that boundary. This calculation is useful only if durations have a basis. A 25-minute restore with one tenth of the data does not establish a production maximum, nor justify multiplying by ten and calling the result guaranteed. Identify volume, dependencies, resources, and validations included in rehearsal. Use representative observations and state uncertainty. When an assumption becomes invalid, update the decision before spending the reserve.
Bind a check to the effect it controls
A revision read followed by an unconditional write leaves a gap during which another process can change state. In the exercise, WHERE revision=? is part of the statement that modifies the row and increments revision. Zero rows means the premise did not match. This control protects that database change under demonstrated conditions; it does not automatically protect a file, remote call, or change outside the statement. It also does not turn technical capability into organizational authorization. In an execution plan, identify where each gate is enforced, which effect it prevents, and which operations remain outside scope. An OK stored in a log is historical evidence, not a permanent lock.
Expand without generalizing compatibility
The lab creates orders with integer amount_units and a row valued at one hundred. It then adds optional amount_cents. The explicit SELECT amount_units query still returns one hundred while the new field is NULL. The exercise demonstrates one reader/schema combination, not every old client. A client depending on column position or a rigid INSERT shape may have other conditions. The new column is also not populated merely because it exists. Before promoting the new read, identify the conversion rule and source of truth during coexistence. The example uses whole units multiplied by one hundred; it is not a complete monetary-value design.
Observe what happens after backfill
Backfill sets amount_cents=amount_units×100. At that instant, one hundred units and ten thousand cents agree. A second connection, representing an old writer, updates only amount_units to 125. The new column remains ten thousand when the rule requires 12500. There is no physical corruption: integrity_check returns ok. Divergence comes from the write path during coexistence. A monthly job can produce this effect after a short, apparently healthy canary. Inventory every relevant producer, including maintenance and closing, and choose an explicit consistency strategy. Repeating backfill can reconcile one instant but does not by itself resolve future writes from the old producer.
Test failure between two writes
After reconciliation to 125 and 12500, the exercise starts a transaction, changes the old field to 150, and injects an exception before the second write. Code executes ROLLBACK and confirms the pair remains 125/12500. A successful transaction then changes both to 150/15000. The result demonstrates atomicity within exercised scope and explicit failure handling. It does not mean every Python exception automatically reverses all resources. It does not adapt old writers or test distributed concurrency. For a real change, include failures at relevant points, verify resulting state, and document paths that still do not use the compatible writing mechanism.
Recognize the point that breaks the old contract
Finally, the script removes amount_units. The new query returns 15000 cents and the old query fails with no such column. The earlier binary file may remain available, but the contract it depends on has disappeared. Contraction needs its own decision, consumer inventory, appropriate observation, and recovery compatible with intended state. Do not conclude that initial expansion made the whole path reversible. Test the required compatibility directions: new version over earlier data, old version over data produced by the new one, and state after removal. If a combination stops working, update recovery options before promising them to RUN. Retain these results with the execution record.
-- Disposable lab only; all data is fictional.
ALTER TABLE orders ADD COLUMN amount_cents INTEGER;
UPDATE orders SET amount_cents=amount_units*100;
-- An old writer can now make the new column stale:
UPDATE orders SET amount_units=125 WHERE id=1;
SELECT amount_units,amount_cents FROM orders; -- 125, 10000A fictional service migrates value representation while a closing job retains old code. Rehearsal exposes divergence before the new read is promoted.
Common pitfalls
Treating backfill as synchronization, one successful query as universal compatibility, an exception as automatic rollback, or a retained binary as demonstrated recovery.
Related topics: SQL · Databases · Disaster Recovery
Compatibility depends on read and write paths and the state they produce. Rehearsing coexistence exposes risks that an isolated deployment does not show.
Reference: SQLite ALTER TABLE · BigSavant Change Management 2026.1; independent technical curriculum