Proving idempotency with a test, not a manual rerun

Description

Count how many times, in this guide, you confirmed idempotency by running a command twice and comparing the output with your own eyes: module 1's mini-project (uv run python -m kiosko_pipeline, twice, comparing 106.15 against 106.15), module 3's (saving two runs to files and comparing with diff), modules 4, 5, and 6's (the same discipline, every time). Five modules in a row, the same manual check, repeated by hand every time. This lesson finally turns it into something pytest confirms on its own: two new tests, added to tests/test_pipeline_integration.py, running the command two and three times in a row and comparing the warehouse's state with assert — this guide's central idempotency proof, automated once and for all.

Connection to the module. This lesson completes this module's second pillar, which lesson 5 started: an integration test confirming correctness, and now, this lesson, a test confirming this entire guide's most-repeated property — that running the pipeline more than once over the same partition never duplicates anything.

An analogy: the certified scale, not "it looks the same to me"

A merchant selling by weight could, in theory, trust their own eye to decide whether two portions of goods weigh the same — and the first few times, they'd probably get it right. But "it looks the same to me" isn't a guarantee a business can rest on: it doesn't scale (someone has to look, every time), it isn't objective (two different people could decide different things looking at the same scale), and it says nothing about the next time nobody's looking. A certified scale solves that same problem in a completely different way: a number, verified by an instrument, that doesn't depend on someone checking it every time nor on the eyesight of whoever checks it.

Every double run you did in modules 1, 3, 4, 5, and 6 was "it looks the same to me": you ran the command, read 106.15, ran it again, read 106.15 again, and decided, yourself, that they matched. It works, as long as someone is watching closely every time the code changes. This lesson is the certified scale: an assert comparing two exact numbers, with nobody needing to look at anything — and that, from today on, runs automatically every time someone executes uv run pytest, forever, with nobody needing to remember to repeat the manual check.

Worked example: two runs, one single assert

Add this to tests/test_pipeline_integration.py, alongside lesson 5's three tests:

def test_running_the_cli_twice_for_the_same_date_does_not_duplicate_rows(tmp_path, data_dir):
    """The idempotency proof this module exists to automate: run the packaged
    CLI twice, over the SAME partition date and the SAME warehouse file, and
    assert -- not eyeball a diff -- that the row count and revenue are
    identical both times. Modules 1, 3, 4, 5 and 6 all verified this same
    property by hand, running a command twice and comparing output visually.
    This is that check, turned into one pytest assertion that runs forever."""
    db_path = tmp_path / "warehouse.db"

    main(["--date", "2026-08-05", "--data-dir", str(data_dir), "--db-path", str(db_path)])
    first_run = _warehouse_counts(db_path)

    main(["--date", "2026-08-05", "--data-dir", str(data_dir), "--db-path", str(db_path)])
    second_run = _warehouse_counts(db_path)

    assert first_run == second_run == (2, 9.55)

Read this test's shape carefully, because it's simpler than the weight of the promise it confirms might make you expect. main([...]) gets called twice, with exactly the same arguments — same --date, same --data-dir, same --db-path — and each time it captures the warehouse's state with the same _warehouse_counts() helper you already built in lesson 5. The final assert — first_run == second_run == (2, 9.55) — chains two comparisons in a single line, leveraging Python's support for chained comparisons: it confirms, at once, that the first run gave the correct result, that the second gave the correct result, and that both are exactly equal to each other. If load_to_warehouse() ever lost its overwrite-partition pattern — the DELETE FROM fact_orders WHERE dt = ? before inserting, unchanged since module 1 — this test would catch it immediately: second_run would be (4, 19.10) instead of (2, 9.55), the exact double, and the assert would fail with a message showing both values side by side.

Worked example: a harder case, two interleaved partitions

A second test, slightly more demanding, confirms one partition's idempotency isn't affected by a different partition getting loaded in between — the kind of sequence a real scheduler would produce if it ever needed to reprocess an old day while still loading new ones:

def test_running_the_cli_three_times_across_two_different_dates_stays_correct(tmp_path, data_dir):
    """A slightly harder idempotency case: re-run one date twice AND load a
    second date in between, and confirm the overwrite-partition pattern
    (load.py, unchanged since module 1) still only ever touches its own
    partition -- 2026-08-06 does not disturb 2026-08-05, no matter how many
    times either one re-runs."""
    db_path = tmp_path / "warehouse.db"

    main(["--date", "2026-08-05", "--data-dir", str(data_dir), "--db-path", str(db_path)])
    main(["--date", "2026-08-06", "--data-dir", str(data_dir), "--db-path", str(db_path)])
    main(["--date", "2026-08-05", "--data-dir", str(data_dir), "--db-path", str(db_path)])  # re-run day 1

    row_count, revenue = _warehouse_counts(db_path)
    assert row_count == 2 + 5  # 2026-08-05 (2 rows) + 2026-08-06 (5 rows), not duplicated
    assert revenue == 9.55 + 11.05

Three calls: 2026-08-05, then 2026-08-06, then 2026-08-05 again. If the overwrite-partition pattern had a bug deleting the wrong partitions — say, if DELETE FROM fact_orders forgot the WHERE dt = ? and wiped the complete table every time — the third call would have destroyed 2026-08-06's data while reloading 2026-08-05, and the final count would show 2 rows instead of 7. If the pattern had the opposite bug — never deleting anything, only accumulating — the final count would show 9 rows (2 + 5 + 2, 2026-08-05's reload adding on instead of replacing) instead of 7. assert row_count == 2 + 5 confirms, with a single number, that neither bug is present: every partition overwrites only itself, touching no other.

Run the complete file:

uv run pytest tests/test_pipeline_integration.py -v

What to expect:

============================= test session starts ==============================
platform darwin -- Python 3.12.3, pytest-9.1.1, pluggy-1.6.0
rootdir: /path/to/kiosko_pipeline
configfile: pyproject.toml
collecting ... collected 5 items

tests/test_pipeline_integration.py::test_cli_run_for_a_known_date_loads_the_right_row_count_and_revenue PASSED [ 20%]
tests/test_pipeline_integration.py::test_cli_run_for_every_known_day_matches_foundations_numbers PASSED [ 40%]
tests/test_pipeline_integration.py::test_cli_run_for_the_orphan_store_date_quarantines_without_crashing PASSED [ 60%]
tests/test_pipeline_integration.py::test_running_the_cli_twice_for_the_same_date_does_not_duplicate_rows PASSED [ 80%]
tests/test_pipeline_integration.py::test_running_the_cli_three_times_across_two_different_dates_stays_correct PASSED [100%]

============================== 5 passed in 0.05s ===============================

Five tests, including this lesson's two — each one running the complete command two or three times, and the whole suite still finishes in hundredths of a second, because main(argv) never launches a new process (the exact same reason lesson 5's "going deeper" section explained). And now, the package's complete suite:

uv run pytest -v 2>&1 | tail -3

What to expect:

tests/test_transform_polars.py::test_find_unregistered_stores_polars_is_empty_for_the_known_week PASSED [100%]

============================== 40 passed in 0.21s ==============================

Forty tests: the thirty-five inherited from modules 1 through 6, with no change at all, plus the five from test_pipeline_integration.py you built in this lesson and the last — including, finally, this entire guide's idempotency proof, automated with assert.

Diagram: from manual verification to assert

flowchart LR
    subgraph Antes["Modules 1, 3, 4, 5, 6 -- manual verification"]
        A["Run the command"] --> B["Read 106.15\nin the terminal"]
        B --> C["Run the command\nagain"]
        C --> D["Read 106.15\nagain"]
        D --> E["Decide, by eye,\nthey match"]
    end
    subgraph Ahora["This module -- automated"]
        F["main(argv) -- run 1"] --> G["_warehouse_counts()\n-- captures first_run"]
        G --> H["main(argv) -- run 2"]
        H --> I["_warehouse_counts()\n-- captures second_run"]
        I --> J["assert first_run == second_run\n-- a machine decides"]
    end
    E -.replaced by.-> J

Going deeper: what this proof guarantees, and what it doesn't

It's worth being precise about the exact scope of what these two tests confirm, because "idempotent" is a word sometimes used, more broadly than what this code actually proves. These tests confirm that the warehouse's final state is the same no matter how many times a partition's load gets repeated — the formal definition of idempotency you already used since foundations: f(f(x)) == f(x). They don't confirm, nor try to confirm, that the two runs are indistinguishable in every respect: each main() call generates its own real timestamp (unlike python -m kiosko_pipeline's runs, with fixed_timestamp), and each run's run_id, while deterministic within that specific run, is the same between both calls only because both process the same date — not because the mechanism somehow prevents two different runs from sharing the same identifier.

This distinction matters in practice: a real system consuming kiosko_pipeline's log line by line would see two different pipeline_run_completed events, one per main() call, with two different timestamps — evidence that two separate runs genuinely happened. What it wouldn't see, and it's what these tests guarantee, is any difference in what ended up in the warehouse: it makes no difference whether someone ran the command once or a hundred times over the same date, fact_orders ends up with the exact same rows, every time.

Common mistakes

Writing an idempotency test comparing only the row count, with no revenue. What happens: someone writes assert first_run[0] == second_run[0] — only the count — with no revenue compared too. A bug that, say, loaded the correct number of rows but with a corrupted unit_price on the second pass would pass this test undetected. Why it happens: the row count is the most visible number and the easiest to reason about, and it feels like enough evidence that "nothing got duplicated." How to spot it: ask yourself what kind of bug would leave the row count intact but change the content — any broken calculation formula between the first and second run is exactly that case, and a test only looking at the count would never see it. How to fix it: this lesson's tests compare the complete (row_count, revenue) tuple, the exact same discipline you already saw in module 1 with the four-metric comparison table (rows_extracted, rows_valid, rows_rejected, rows_loaded) instead of just the total revenue — more than one signal is harder to break by accident with no test noticing.

Confusing "I ran the test once and it passed" with "I confirmed idempotency holds over time." What happens: someone runs uv run pytest tests/test_pipeline_integration.py once, sees 5 passed, and assumes kiosko_pipeline's idempotency is "solved forever," with no understanding that what actually sustains that guarantee — load_to_warehouse()'s overwrite-partition pattern — is still code someone could break tomorrow, without noticing, while modifying load.py for some completely different reason. Why it happens: seeing a test green feels like a permanent guarantee, not a point-in-time check. How to spot it: if you believe these two tests guarantee idempotency "forever" with nobody ever running them again, you confused "I wrote the test" with "the test runs." How to fix it: these tests' real value isn't today's run — it's that they run every time someone executes uv run pytest, including any future continuous-integration run (lesson 7). If someone, six months from now, modifies load.py in a way breaking the overwrite-partition pattern, these two tests would fail immediately, in the same run where the bug got introduced — that's the real guarantee, not a one-time stamp of approval.

Using a shared tmp_path between the two idempotency tests, instead of a fresh one per test. What happens: someone, trying to save lines, tries sharing the same db_path between test_running_the_cli_twice_for_the_same_date_does_not_duplicate_rows and test_running_the_cli_three_times_across_two_different_dates_stays_correct — say, with a fixture with a scope broader than function. The result: the second test inherits the rows the first one left, and its exact-count asserts start failing in a confusing way, with no real bug in the production code. Why it happens: it seems like a reasonable optimization to avoid recreating a warehouse from scratch for every test. How to spot it: if a test in this file fails with a row count higher than expected, and the extra number suspiciously matches another test's rows in the same file, check whether both share the same tmp_path or db_path. How to fix it: tmp_path, as used by the five tests in this lesson and the last, has function scope by default — exactly like module 2's fixtures — every test gets its own temporary folder, fresh and empty, with no test able to inherit another's state. There's no reason, in this file, to change that default behavior.

Exercises

Exercise 1 — Break the overwrite-partition pattern, and confirm the test catches it. Temporarily edit load.py so load_to_warehouse() doesn't run the DELETE FROM fact_orders WHERE dt = ? before inserting (comment out that line). Run test_running_the_cli_twice_for_the_same_date_does_not_duplicate_rows and describe what pytest reports. Undo the change when done.

Exercise 2 — Write a third idempotency test, over the quarantined partition. Should running kiosko-pipeline --date 2026-08-11 (the partition with S04) twice in a row produce any change in the warehouse between the first and second run? Write a test confirming your prediction.

def test_running_the_cli_twice_for_the_quarantined_date_stays_empty(tmp_path, data_dir):
    db_path = tmp_path / "warehouse.db"

    exit_code_1 = main(["--date", "2026-08-11", "--data-dir", str(data_dir), "--db-path", str(db_path)])
    exit_code_2 = main(["--date", "2026-08-11", "--data-dir", str(data_dir), "--db-path", str(db_path)])

    assert exit_code_1 == exit_code_2 == 0

Exercise 3 — Explain, without looking at "going deeper," what these two tests DO and DO NOT guarantee. In 3-4 sentences, explain the difference between "the warehouse's final state is identical between runs" (what these tests guarantee) and "the two runs are indistinguishable in every sense" (what they don't guarantee), with a concrete example of something that does change between two kiosko-pipeline runs.

Summary and next step

In this lesson you completed tests/test_pipeline_integration.py, adding two tests that run the complete command two and three times in a row and compare the warehouse's final state with assert — the idempotency proof you always verified, up to this module, by hand, command by command, module after module. You confirmed, with the complete suite green, that none of the thirty-five inherited tests got affected, and that this file's five new tests — three about correctness (lesson 5), two about idempotency (this lesson) — run in fractions of a second, with no cost from launching new processes.

Before moving on you should be able to: write a test confirming idempotency by comparing an external resource's final state, not just a return value; explain why comparing count and revenue is more robust than comparing just one of the two; and explain the difference between "the stored result is identical" and "the two runs are indistinguishable in every sense."

With kiosko_pipeline tested, configured with no hardcoded value, with a production engine chosen with a rationale, and with its idempotency automatically tested, you might think the work is done. Lesson 7 stops, on purpose, before assuming that — and honestly names what a real code reviewer would ask before approving this package to genuinely run in production, not on your laptop.

Resources

• pytest — official documentation on assert and assertion rewriting, the foundation of every comparison in this lesson. docs.pytest.org/en/stable/how-to/assert.html.
• Python — official documentation on chained comparisons (a == b == c), used in this lesson's first test. docs.python.org/3/reference/expressions.html#comparisons.
• DESIGN of data-engineering-foundations-guide — the original idempotency definition (f(f(x)) == f(x)) this lesson automates. src/guides/data-engineering-foundations-guide/DISENO.md.
• DESIGN of python-for-data-engineering-guide — this lesson's explicit requirement: an integration test running the pipeline twice and asserting the row count. src/guides/python-for-data-engineering-guide/DISENO.md.