SQL, data modeling, data quality, EDA, visualization · insight-1060
Parameterize each run by its data interval and never read "now" inside a transformation
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A daily aggregation filters on CURRENT_DATE - 1, and a rerun two days later after a failure silently computes a different day.
Action
Pass the interval start and end as run parameters (the scheduler's logical date), have the query read exactly that partition, and write to the same partition. Add a test that runs the job twice for the same interval and compares outputs.
Reason
Idempotence requires the output to be a function of the input interval, not of the wall clock.
Boundary
Daily and hourly batch jobs, backfills and any orchestrated SQL.
Symptom or risk
The same job id produces different outputs depending on when it runs, so retries, backfills and tests are not reproducible and gaps appear in the history.
Exceptions
Operational jobs that really mean "current state" and are not meant to be rerun for history.
Trade-offs
Templated queries are harder to paste into a console and require parameter conventions.
Pitfalls
Computing the interval correctly but reading "latest" from the upstream table, which has moved on.
Lessons and concepts
Claims and their sources
Apache Airflow's best practices say tasks should produce the same outcome on every re-run, advise against now() in critical computation, and recommend reading a fixed partition keyed by data_interval_start.