Reads forgive mistakes; writes don't. Every destructive statement earns a ritual.
-- Ritual: SELECT first, with the same WHERE
SELECT * FROM stg_orders WHERE status = 'test';
-- Then, and only then, write with the identical WHERE
UPDATE stg_orders SET status = 'new' WHERE status = 'test';
DELETE FROM stg_orders WHERE status = 'test' AND ordered_at < now() - interval '30 days';
Rules
- SELECT-first, always. Write the WHERE as a SELECT, eyeball row count,
then convert to UPDATE/DELETE. No bare
UPDATE t SET ...without WHERE — ever, in any environment. - Prefer soft deletes in marts (
is_deleted,deleted_at) over DELETE: history survives, SCD logic stays simple, and recovery is an UPDATE. - Batch large changes (
... WHERE id BETWEEN a AND b LIMIT 10000in a loop) to avoid long locks and WAL explosions on big tables. - In pipelines, UPDATE/DELETE are code smells — they signal mutable
sources. Prefer append-only staging + rebuilt marts; reserve in-place writes
for true corrections with an audit column (
updated_at,updated_by).