Running It Twice: Idempotency, Git, and Reviewing an Automation

By the end of this lesson you can write a database load that has the same stored result when repeated with the same input, review and revert committed code changes with Git, and check an automation before it causes an irreversible effect.

Scheduled jobs fail halfway. A network drops, a machine restarts, someone stops a run because it looked wrong. The question that decides whether that is an inconvenience or an incident is: what happens when you simply run it again?

If a repeat duplicates rows or re-sends something, a failure needs investigation before retry. For the scoped database load below, repeating the same input after a rolled-back or completed transaction is safe; other steps and external effects need their own retry design.

Git records committed code changes for review and can often reverse them with another commit. It does not undo database writes, sent messages, or uncommitted work. Read the diff before committing, including changes an assistant produced.

Idempotent means running an operation more than once has the same effect as running it once. It is a property to design in, not a behaviour to hope for.

For a load step, the tool is an upsert: insert the row, or update it if the key already exists.

INSERT INTO notices (notice_id, amount) VALUES (?, ?)
ON CONFLICT(notice_id) DO UPDATE SET amount = excluded.amount

excluded refers to the row that would have been inserted, so the update takes the new value. This needs a primary key or unique constraint to conflict on, which is why Module 9 put one there from the start.

Compare the alternatives. With this keyed table, a plain INSERT raises IntegrityError when the second batch reaches an existing key. Without a key, an identical full rerun stores duplicate rows, so a simple sum over those rows doubles; partial or revised batches produce other wrong totals. "Delete everything then reload" may produce the expected count for a complete batch but discards rows the new batch omits.

Three more habits that make a pipeline re-runnable:

• Write output to a temporary name, then rename. Finish the temporary file before a same-filesystem rename so readers of the destination do not see a partially written replacement. Check that replacement is supported on the target platform and handle failures; a process interrupted before the rename leaves the old destination, while crash durability needs additional care.
• Record what has been processed, by identifier and by run, so a resumed run can skip completed work rather than redoing it.
• Support --dry-run. Any automation that changes data should be able to describe what it would do without doing it.

Git, in the six commands this course needs:

git status                 what has changed
git diff                   exactly what changed, line by line
git add -p                 stage selected changes, reviewing each one
git commit -m "message"    record a small, described step
git log --oneline          the history of steps
git revert <commit>        record an inverse commit (resolve conflicts if needed)

git revert records an inverse commit for committed file changes when they can be applied; conflicts may need resolution. It preserves history but does not undo external effects. git reset --hard resets the checkout and index and discards uncommitted tracked changes, so it is not a routine undo button.

Commit small and often, and always read git diff before committing. That habit is the entire defence against accepting a change nobody examined, whoever or whatever produced it.

Reviewing an automation that changes data. Five questions, before it runs anywhere real:

1. What does it delete, overwrite, or send, and can that be undone?
2. What happens if it runs twice, or is interrupted halfway?
3. What does it do when its input is empty? An empty batch that causes a full delete is a real and common failure.
4. Does every failure produce a message, or can it fail silently?
5. Does it log or transmit anything that should stay private?

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE notices (notice_id TEXT PRIMARY KEY, amount INTEGER NOT NULL)")

UPSERT = (
    "INSERT INTO notices (notice_id, amount) VALUES (?, ?)"
    " ON CONFLICT(notice_id) DO UPDATE SET amount = excluded.amount"
)


def load(rows, dry_run=False):
    """Upsert input rows; return input count. Dry run writes nothing."""
    if dry_run:
        return len(rows)
    with connection:
        connection.executemany(UPSERT, rows)
    return len(rows)


print(load([("A-1", 300000), ("A-2", 100000)]))
print(connection.execute("SELECT COUNT(*) FROM notices").fetchone()[0])

print(load([("A-1", 350000), ("A-3", 90000)]))
print(connection.execute("SELECT COUNT(*) FROM notices").fetchone()[0])
print(connection.execute("SELECT amount FROM notices WHERE notice_id = 'A-1'").fetchone()[0])

print(load([("A-4", 10)], dry_run=True))
print(connection.execute("SELECT COUNT(*) FROM notices").fetchone()[0])

load upserts a batch and returns the number of input rows, which is not a count of inserted or changed database rows. The whole batch is inside a single with connection: block, so an error partway through rolls the batch's database changes back rather than leaving half of it applied.

The second call sends a revised amount for a notice already stored and a notice that is new. The upsert handles both in one statement, with no need to ask first which case each row is.

dry_run returns only the input count without executing SQL. It is a no-write check in this example, not a description of which rows would insert or update; a fuller dry run should report those proposed actions without applying them.

Predict all seven printed lines.

2
2
2
3
350000
1
3

Remove the ON CONFLICT clause, leaving a plain INSERT, and predict what the second load call does.

sqlite3.IntegrityError: UNIQUE constraint failed: notices.notice_id

An assistant was asked for a nightly refresh script. It produced this.

def refresh(connection, rows):
    connection.execute("DELETE FROM notices")
    for notice_id, amount in rows:
        try:
            connection.execute(
                "INSERT INTO notices (notice_id, amount) VALUES (?, ?)", (notice_id, amount)
            )
        except Exception:
            pass
    connection.commit()
    print("refresh complete")

def refresh(connection, rows):
    if not rows:
        raise ValueError("refusing to refresh from an empty batch")
    with connection:
        connection.executemany(UPSERT, rows)
    return len(rows)

Write the load step, then run it twice over the same batch. The row count after the second run must match the count after the first.

Loading this exercise…

Optional: add a runs table recording a timestamp and the number of rows processed, and write one row per invocation inside the same transaction as the load. Then run the pipeline three times and query the table. A record of what ran and when is the difference between "the numbers changed and I do not know why" and a one-query answer, and the capstone's runbook asks for exactly this.

Sign in to track your progress on this exercise.

1. Define idempotent in one sentence, for a load step.
2. Why is delete-then-insert a poor way to achieve it?
3. What does git revert do that git reset --hard does not?
4. Which review question catches an automation that empties a table during an upstream outage?

Sign in to track your progress on this exercise.

Design for the second run: upsert rather than delete-and-reload, wrap a batch in one transaction, refuse an empty input, support a dry run, commit small and read every diff, and ask the five review questions before an automation touches real data. Module 11 steps back to a design question: when a class earns its place, and when a function was already enough.