Testing Against a Live Database

The day a clean room exposed twelve of my tests

For months, my trading bot's test suite ran green on my machine. Then I built CI, and the very first real pipeline run failed twelve tests I had not touched in weeks.

The tests were not broken. They were not hermetic, and the clean room called their bluff.

Hermetic (as in hermetically sealed) means a test creates everything it needs and depends on nothing outside itself. My twelve failures all shared one disease: they were written against a database that always contained my production world: backfilled market candles, an environment ceiling set by config, an instrument created once by a script long ago. The tests never declared those dependencies. They just quietly assumed a world that happened to exist everywhere the suite had ever run, until CI handed them a brand-new PostgreSQL containing only what migrations create, and every hidden assumption surfaced at once.

Two worlds, and what each one catches

The tempting fix is to make everything run in the clean room and call it done. I ended up somewhere more interesting: the same test suite deliberately runs in BOTH worlds, because each world catches a different class of bug.

• The clean room (CI) catches tests that lean on existing state. If a test passes only when candles are pre-loaded, CI fails it, and that is a bug in the TEST.
• The prod-truth gate (deploy) catches code whose assumptions have drifted from reality: a migration that works on empty schemas but chokes on real rows, logic that assumes a table is small. That is a bug in the CODE, and no clean room can find it, because the clean room is exactly what hides it.

I was once tempted to consolidate them into one gate. Keeping both was the right ruling: they answer different questions, and neither can impersonate the other. (A related separation, which stage of the DEPLOY should test against which database, went wrong in its own instructive way; that story is "The Deploy Gate That Cried Wolf".)

The fix, test by test: own your world

Making a test hermetic is unglamorous manual work with one rule: create what you need, wipe what could interfere, assume nothing about existing rows.

def test_verdict_on_pullback(db):
    # own the world: create the instrument and its history explicitly
    inst = make_instrument(db, symbol="TEST.X", shariah_status="compliant")
    seed_candles(db, inst, closes=[100, 98, 97, 99])  # exactly the shape needed
 
    verdict = run_rules(db, inst)
 
    assert verdict.action == "BUY"

No global fixtures that "usually" exist. No reliance on row counts. Every test is a small sealed terrarium.

The twist: sometimes the "failure" means the system is real

My favorite bug from this saga inverted the lesson. Later, a test asserted that a +6% price move triggers an abnormal-move alert. It failed, and I went hunting for the regression.

There was none. The test database (which carries real market history, because the app is genuinely live) had real volatility for that instrument, and by its OWN statistics a +6% day was not abnormal. The alert gate was working CORRECTLY. My test had assumed a world where 6% is always dramatic; the data disagreed, on the merits.

That failure taught me more than the twelve original ones:

1. When a test fails, the test's model of the world is as suspect as the code.
2. A system smart enough to adapt to real data will embarrass fixtures written for a naive world. The fixture must then specify its world precisely (calm instrument, low sigma, THEN 6% is abnormal), not just its inputs.

Takeaways

• Tests that pass only on an empty database are loans. You pay them back, with interest, the first time the suite meets a database with a past.
• Hermetic is a property you must enforce, because it degrades silently: every green run on a convenient database hides one more accidental dependency.
• Run the suite in a clean room to audit your tests; run it against real state to audit your assumptions. Two worlds, two different bug classes, no shortcut covers both.
• When a good gate fails a good build, read the failure carefully before "fixing" anything. Sometimes the red is the system being right.

Pictures to add later

1. CI pipeline run with the twelve failures (if the old pipeline page still shows it)
2. The same suite green in both worlds afterwards (CI run + deploy gate log)