Image generated with AI
Inspired by the “Is this a pigeon?” meme. The dates and scenario are illustrative.
Your backfill finished at 07:03 this morning. It ran cleanly. Every dependency was green. But it was processing last Thursday’s data, and your gate just let it through as today’s load.
A successful backfill can finish this morning and still belong to last week’s reporting period. If the downstream gate checks only whether its dependencies ran today, the wrong load can pass.
I would make business date an explicit input and retain the exact upstream run IDs that authorized the output. The small SQLite lab accompanying this article demonstrates that contract, including a retry after publication and two concurrent publishers.
The clock answers a narrower question
Imagine two independently scheduled dimension jobs and a daily sales load. The sales load needs both dimensions for October 23.
At 07:00 UTC on October 23, both jobs complete successfully. A check based on today’s start time sees two green runs and opens the gate. But those runs were backfills for October 16. The current reporting period is still missing.
The opposite case is just as useful: a valid October 23 run can start before midnight and complete later. A midnight cutoff can exclude work the consumer actually needs.
In the Databricks Jobs API, start_time_from filters runs by when they started, which says nothing about the business period the code processed. The API also separates completed runs from successful ones. Both are good inputs for a collector, but neither tells you a run covered the right day.
The fix is not a smarter clock. It is an explicit record of which business date each run actually processed, and a gate that checks that record instead of the wall clock.
Write down which attempt counts
My example records job ID, business date, attempt ordinal, run ID, state and a timezone-aware start timestamp. It checks the exact required job IDs against the requested date.
The attempt ordinal is assigned by the producer for each job/date pair. It’s kept separate from run ID and from when the observation arrived. A delayed message about attempt one must not overwrite the outcome of attempt two.
I chose a conservative rule: the greatest recorded attempt wins. If attempt 1 succeeds but attempt 2 fails or is still running, the gate blocks: the earlier success is invisible until the newer attempt resolves. Only when the most recent attempt succeeds does the date become ready. Another system might accept the first success instead. That’s a different contract, with its own tests.
A running observation may transition to one terminal result. Replaying that result is harmless. Changing an already terminal result is rejected; a repair must carry a new attempt ordinal.
This policy still depends on the collector. If the gate has not learned that a newer attempt exists, it cannot block on that invisible attempt. In a deployment, I would define who publishes authoritative observations and how missing or delayed updates are detected.
An executable failure, then a valid release
The lab uses two synthetic dependencies and a synthetic total of 17,500 integer cents. Its demo first records successful October 16 backfills with October 23 start timestamps. Checking October 23 returns false. Recording successful October 23 attempts makes that date ready.
Run these commands from the repository root:
python -m unittest discover -s python/business_date_gate -p 'test_*.py' -v
python python/business_date_gate/gate.py
The demo prints:
Backfill completed today: False
Required business date ready: True
First publication: True
Same publication retried: False
That last False is the retry being ignored: the existing publication stays and nothing gets written twice.
Put the output and its evidence in one transaction
A readiness check alone does not prevent two workers from publishing the same date. In this local example, the publication key is the consumer name plus business date.
The code opens a SQLite BEGIN IMMEDIATE transaction, reads readiness, writes the target total and stores the successful upstream run IDs. Both inserts commit together. A repeated call with the same key, dependency set and amount returns without inserting another row. A different amount or dependency set is rejected so a correction cannot silently masquerade as a retry.
After publication, an upstream failure does not erase the original evidence. Replaying the already committed result remains a no-op. Rebuilding or revoking that result would require an explicit repair workflow.
The failure test matters here. A SQLite trigger rejects the evidence insert on purpose, right after the target insert. The transaction rolls back both writes. Removing the trigger and retrying succeeds. A separate test launches two publishers on independent connections to the same temporary database; only one creates the publication.
All 25 new tests passed locally with Python 3.12 and SQLite. They also cover date validation, midnight crossings, missing jobs, delayed observations, state transitions and conflicting retries.
What the lab doesn’t cover
Output and evidence live in one SQLite database, so the atomic part holds there and nowhere else. Delta, S3 or a message broker need their own commit checks. The total is illustrative too; the gate doesn’t read upstream tables.
On Databricks, I’d pass business date as a job parameter and let a table-update trigger on a control table start the check. The trigger only decides when to look. The date and attempt rules above still decide if the output goes out.
Before deploying, I’d test the collector’s pagination, retries and repair handling, and map the real run states explicitly.
References:
- Databricks run-list filters: https://docs.databricks.com/api/jobs/v2/list-runs
- Job parameters: https://docs.databricks.com/aws/en/jobs/job-parameters
- Table-update triggers: https://docs.databricks.com/aws/en/jobs/trigger-table-update
- SQLite transactions: https://www.sqlite.org/lang_transaction.html
Top comments (1)
The "greatest recorded attempt wins" rule is a nice conservative choice, and calling out that a gate can't block on an attempt it hasn't heard of yet is honest. On Delta, I've had good results with a similar idea: the publishing job writes the business date and upstream run IDs into the table's commit metadata (spark.databricks.delta.commitInfo.userMetadata, or the userMetadata write option), so DESCRIBE HISTORY shows which inputs authorised each version. Combined with a MERGE into a small control table keyed on consumer plus business date, it gives you the same "retry is a no-op, a different amount is rejected" behaviour without a separate database.