Home › Learn › Shift left › Testing data logic offline

Shift left · page 6 of 13

Testing SCD2, idempotency, duplicates and dropped rows without a cluster

Unit testing SCD2, idempotency, duplicates, dropped records and cartesian joins in PySpark without a cluster, with a test kit, drafted tests, and CI.

↑ All of shift leftShift left, explainedHow software solved itSyntax treesPure functionsTests, coverage, complexityTesting data logic offlineClean codeSecurity and costWhat is missingThe bundleHow it is builtAll the checksOpen source and ours

In plain words

Yes, without a cluster, if the logic is a pure function

"Without running it" means without a cluster, real tables or real data. The code still runs, on a local Spark session with a few hand-made rows. That takes seconds. The trick is the one from pure functions: write SCD2 as history and batch in, new history out. Then the MERGE or overwrite is a thin shell, and every rule is testable.

The working example is in the repo: examples/scd2. It ran on 2 Nov 2026 on Spark 3.5.1: 27 tests passed in about 26 seconds on a laptop.

The cases

SCD2 as a table of cases

Each row is a batch and the history we expect afterwards. These eight cases are where SCD2 code usually breaks:

CaseWhat must happen
New customerA current version is added
Row with no real changeNothing changes
City changedThe old version is closed on the new date; the new one is current
Two changes for one customer in one batchTwo versions, in time order; only the last is current
Same row twice in a batchCounted once
Row older than the current version (late)Not applied, and reported as late, never lost silently
Batch replayedNothing changes
Value changes back to an earlier oneThat is a new version

Beyond single cases

Five checks that run on every case

Hand-written cases check specific answers. These checks hold for every input, so the test kit runs them on each case automatically:

QuestionCheckHow
Is it idempotent?assert_idempotentApply the batch, then apply it again to the result. The second run must change nothing.
Any duplicates?assert_uniqueOne current row per customer; no two versions with the same start date.
Any records dropped?assert_nothing_droppedEvery input key is in the output or in an explicit rejects list. An inner join that loses orders with no matching store fails this.
Did a join multiply rows?assert_no_fanoutRows out are not more than rows in for a lookup join. A duplicate key in the lookup fails this.
Cartesian join?assert_no_cartesianRead the plan with empty inputs. A join with no key, or an always-true condition, fails this.
@pytest.mark.parametrize("name, batch, expected", CASES, ids=IDS)
def test_scd2_properties(spark, name, batch, expected):
    hist, b = spark.createDataFrame(BASE, H), spark.createDataFrame(batch, B)
    out = scd2(hist, b)
    kit.assert_idempotent(scd2, hist, b)                         # run twice, nothing changes
    kit.assert_unique(out, ["customer_id"], where="is_current")  # one current version per key
    kit.assert_unique(out, ["customer_id", "valid_from"])        # no duplicate versions
    kit.assert_nothing_dropped(b, out, "customer_id", rejects=late_rows(hist, b))
    kit.assert_no_cartesian(scd2(kit.schema_only(hist), kit.schema_only(b)))

From examples/scd2/test_scd2.py.

Does the kit catch real bugs? The example plants them: an append-only "SCD2" that never closes versions fails the idempotency and one-current-row checks. An inner join that loses orders fails the dropped-records check. A join with no key fails the cartesian check. A test kit that can't fail is worth nothing, so these planted bugs are part of the tests.

A trap we hit. Building the empty inputs with df.limit(0) let Spark notice they were empty and fold the whole join away, so the cartesian product never appeared in the plan. Empty inputs must come from an empty RDD, which Spark can't see through. The kit's schema_only() does that.

Existing code with no tests

Can tests be written backwards from the code?

Yes, in three ways, from cheapest to best:

  1. Snapshot tests (syrupy): run the pure function on a small sample and save the output as the expected answer. It pins today's behaviour so a refactor can't change it by accident. It does not say today's behaviour is right.
  2. Property skeletons (Hypothesis ghostwriter): generates test outlines from the function's signature; we add a helper that turns a schema into generated rows with nulls, duplicates and edge dates.
  3. Drafted case tables: Claude reads each untested pure function from the purity report and drafts a table of cases. A drafted test is kept only if mutation testing shows it catches deliberate bugs, so the model never decides on its own that code is tested.

Pynguin, a research tool that generates unit tests automatically, is on trial; it is unlikely to do well with DataFrame code.

Where it runs

Laptop, container, CI: the same tests

WhereWhat runsNeeds
Laptoppre-commit on commit; pytest when you chooseJava 17, Python, pinned pyspark
Dev containerThe same, in a container with Java 17, Python 3.11 and the pinned librariesDocker or a cloud dev environment
CI on GitHub or Azure DevOpspre-commit on the changed files, all tests, coverage on changed lines (80%)The template pipeline

Two settings matter everywhere: pin pyspark to the cluster's Spark version, and set PYSPARK_PYTHON to the test's own Python, always. On our machine a shell setting started workers on a different Python and every test failed. The templates are in the repo; they are written but not yet run in a pipeline.

FAQ

Questions people ask

Can SCD type 2 logic be unit tested without a cluster?

Yes. Write it as a pure function (history and batch in, new history out) and test it on a local Spark session with a few rows: new key, no change, change, two changes in one batch, duplicate row, late row, replay, change back. The MERGE that writes the result stays a thin shell.

How do I test that a PySpark job is idempotent?

Apply the batch to the state, then apply the same batch to the result. The second result must equal the first. Run it on every test case, not just one.

How do I catch rows silently dropped by a join?

Check that every input key appears in the output or in an explicit rejects set. An inner join on a lookup with a missing key fails this check; a left join with a rejects list passes.

Can I detect a cartesian join in a test without data?

Yes. Build empty inputs from an empty RDD with the real schema, run the transform and read the physical plan for CartesianProduct or BroadcastNestedLoopJoin. Don't use limit(0): Spark folds empty inputs away and hides the problem.

Next

Where to go next

Next page: Clean-code checks: early return and the rest

↑ All of shift left · The repo on GitHub · Data project review