Wait for Upstream Data Before Running a Report

A report scheduled for 07:00 assumes that the data it needs is there by 07:00. Most days it is. On the day the ERP export runs late, or the overnight database load fails halfway, the report still runs on time — and quietly produces figures from yesterday's file or from a half-loaded table. Nothing errors, the email goes out, and the wrong numbers are discussed in the morning meeting. The cure is to make the report check that its inputs are ready before it builds anything, wait a while if they are not, and give up at a deadline with a clear message instead of a wrong report. This page builds those readiness checks for the three common kinds of input — a file, a marker, a database table — a polling loop with a deadline, and a test that runs the whole thing against a simulated clock. It belongs to Scheduling Python Excel Scripts with Cron.

Prerequisites

pip install pandas openpyxl

The examples use a local folder for the export file and a SQLite database with a load-status table, both created on the fly. The same checks work against a network share, an SFTP folder or a production database with the connection changed.

Step 1 — Write each readiness check as a small function

import sqlite3
from contextlib import closing
from datetime import date, datetime, timedelta
from pathlib import Path

def export_file_ready(folder, business_date, min_age_s=120, now=None):
    """The export for the business date exists, is non-empty and has not changed for min_age_s."""
    now = now or datetime.now()
    path = Path(folder) / f"sales_{business_date:%Y%m%d}.xlsx"
    if not path.exists():
        return False, f"{path.name} not there yet"
    st = path.stat()
    if st.st_size == 0:
        return False, f"{path.name} is empty"
    age = now.timestamp() - st.st_mtime
    if age < min_age_s:
        return False, f"{path.name} changed {age:.0f}s ago, may still be copying"
    return True, f"{path.name} ready ({st.st_size:,} bytes)"

def marker_ready(folder, business_date):
    marker = Path(folder) / f"sales_{business_date:%Y%m%d}.done"
    return marker.exists(), f"{marker.name} {'found' if marker.exists() else 'missing'}"

def table_loaded(db, table, business_date):
    with closing(sqlite3.connect(db)) as con:
        row = con.execute("SELECT status, rows FROM load_status WHERE table_name = ? AND business_date = ?",
                          (table, business_date.isoformat())).fetchone()
    if row is None:
        return False, f"{table}: no load recorded for {business_date}"
    status, rows = row
    return (status == "complete" and rows > 0), f"{table}: {status}, {rows:,} rows"

Each check returns two things: whether the input is ready and a sentence saying why or why not. The sentence is what turns a late-data notice from "inputs not ready" into something a person can act on, such as "sales_20261005.xlsx changed 40s ago, may still be copying". The checks are specific to the business date the report is for, not to "the newest file" — picking up the newest file is exactly how a report ends up running on yesterday's data without anyone noticing.

The file check has a subtlety. A file that exists may still be arriving: a large export copied over a network share appears at its full name long before the last byte is written. Requiring the file to be unchanged for a couple of minutes is a cheap guard. A marker file is better when the upstream system can write one — the producer writes the data, then creates an empty .done file, and the marker's existence means the data is complete. For a database, the equivalent is a load-status table that the load process updates at the end, so a half-finished load is never mistaken for a finished one.

Step 2 — Poll until ready or until the deadline

import time

def wait_until_ready(checks, deadline, now=datetime.now, sleep=time.sleep, first_wait=60, max_wait=600):
    wait = first_wait
    while True:
        results = [(name, *check()) for name, check in checks]
        pending = [(n, why) for n, ok, why in results if not ok]
        if not pending:
            return True, results
        if now() >= deadline:
            return False, results
        pause = min(wait, max_wait, max((deadline - now()).total_seconds(), 0))
        print(f"{now():%H:%M:%S} waiting {pause:.0f}s for: " + "; ".join(why for _, why in pending))
        sleep(pause)
        wait *= 2

The loop runs every check, returns as soon as all of them pass, and otherwise sleeps and tries again. The waits grow — one minute, two, four, then capped at ten — so a late input is noticed quickly when it is only a little late, without checking every few seconds for an hour when it is very late. The last wait is shortened so the loop wakes exactly at the deadline. The clock and the sleep function are parameters, which costs nothing in production and makes it possible to test the whole loop in a fraction of a second, as step 4 does.

Step 3 — Run, or say clearly that the data is late

def run_report(business_date, folder, db, deadline, now=datetime.now, sleep=time.sleep):
    checks = [("export", lambda: export_file_ready(folder, business_date, now=now())),
              ("marker", lambda: marker_ready(folder, business_date)),
              ("orders table", lambda: table_loaded(db, "orders", business_date))]
    ready, results = wait_until_ready(checks, deadline, now=now, sleep=sleep)
    if not ready:
        lines = [f"- {name}: {'ready' if ok else why}" for name, ok, why in results]
        return "LATE", (f"The {business_date:%d %b} sales report has not been produced: "
                        f"inputs were not ready by {deadline:%H:%M}.\n" + "\n".join(lines))
    # build_report(business_date) would go here
    return "OK", f"report for {business_date} built from: " + "; ".join(why for _, _, why in results)

When the deadline passes, the job does not fall back to the data it has. It sends a short notice instead — to the report's recipients if they would otherwise wonder where it is, or to the people who run the upstream system — listing each input with its state. A notice that says "the orders table load is still running" saves an hour of emails; a report built on partial data costs much more, because nobody knows it is wrong. Once the data arrives, the report can be produced by rerunning the job for that business date, which is safe if the job is written to be rerun, as described in Make an Excel Report Job Safe to Rerun.

Step 4 — Test it against a simulated clock

import os, tempfile

class FakeClock:
    def __init__(self, start):
        self.t = start
    def now(self):
        return self.t
    def sleep(self, seconds):
        self.t += timedelta(seconds=seconds)
        for at, action in list(events):
            if at <= self.t:
                action(); events.remove((at, action))

folder = Path(tempfile.mkdtemp()); db = str(folder / "warehouse.sqlite")
with closing(sqlite3.connect(db)) as con, con:
    con.execute("CREATE TABLE load_status (table_name TEXT, business_date TEXT, status TEXT, rows INTEGER)")
bd = date(2026, 10, 5)

def drop_export(at):
    p = folder / f"sales_{bd:%Y%m%d}.xlsx"
    p.write_bytes(b"x" * 48_213)
    os.utime(p, (at.timestamp(), at.timestamp()))
def write_marker():
    (folder / f"sales_{bd:%Y%m%d}.done").touch()
def finish_load():
    with closing(sqlite3.connect(db)) as con, con:
        con.execute("INSERT INTO load_status VALUES ('orders', ?, 'complete', 18342)", (bd.isoformat(),))

start = datetime(2026, 10, 6, 7, 0)
clock = FakeClock(start)
events = [(start + timedelta(minutes=5), lambda: drop_export(start + timedelta(minutes=5))),
          (start + timedelta(minutes=6), write_marker), (start + timedelta(minutes=13), finish_load)]
print(run_report(bd, folder, db, deadline=start + timedelta(minutes=45), now=clock.now, sleep=clock.sleep))

The fake clock replaces datetime.now and time.sleep: sleeping simply moves the clock forward and fires any events that are due, such as the export file landing at 07:05 or the database load finishing at 07:13. The test runs in milliseconds but walks through the real sequence of waits. It is worth also running it with an event that never happens — the database load never finishing — to see the late-data notice, which is what the next block does.

for f in folder.iterdir():
    if f.suffix in (".xlsx", ".done"):
        f.unlink()
with closing(sqlite3.connect(db)) as con, con:
    con.execute("DELETE FROM load_status")
clock = FakeClock(start)
events = [(start + timedelta(minutes=5), lambda: drop_export(start + timedelta(minutes=5))),
          (start + timedelta(minutes=6), write_marker)]
status, message = run_report(bd, folder, db, deadline=start + timedelta(minutes=45), now=clock.now, sleep=clock.sleep)
print(status); print(message)

In the first run the checks at 07:00, 07:01 and 07:03 find nothing; at 07:07 the export and marker are ready and only the database load is outstanding; at 07:15 everything passes and the report is built. In the second run the load never finishes, the waits grow to the ten-minute cap, and at 07:45 the job returns the late notice with the export and marker marked ready and the orders table named as the problem.

Choosing the deadline

The deadline is a business decision more than a technical one. Work back from when the report is needed: if the morning meeting is at 09:00 and the report takes ten minutes to build and send, a deadline of 08:30 leaves a margin. Start the job early enough that a normal day finds the data ready on the first check, and use the history of when inputs actually arrived — the waiting messages are a good record if they are logged — to set the start time and spot an upstream process that is drifting later week by week.

Common pitfalls

• Using the newest file. Look for the file for the business date, so yesterday's never passes.
• Treating "exists" as "complete". Check stability, a marker file or a load-status row.
• Falling back to old data at the deadline. Send a late-data notice instead.
• Fixed one-minute polling for hours. Grow the interval and cap it.
• Testing with real waits. Inject the clock and the sleep and simulate the morning.

Conclusion

Before a scheduled report builds anything, check that each input is ready for the business date — a stable export file, a producer's marker file, a complete load-status row — and poll with growing waits until a deadline. If the deadline passes, send a notice that lists each input's state instead of a report built on stale or partial data, and rerun the report once the data arrives. Inject the clock so the waiting logic can be tested in milliseconds.

Frequently asked questions

How do I make a Python script wait for a file before running? Check for the expected file name for the business date in a loop, sleep between checks with a growing interval, and stop at a deadline.

How can I tell that a file has finished copying? Wait until its size and modification time have not changed for a short period, or better, have the producer write a separate marker file when it is done.

What should a report do if its data is late? Not run on old data; send a short notice explaining which inputs are missing, and rerun once they arrive.

How do I test code that sleeps and reads the clock? Pass the clock and sleep functions in as parameters and replace them in tests with a fake clock that advances when asked to sleep.

Related

• Up one level: Scheduling Python Excel Scripts with Cron — running reports on a schedule.
• Watch a Folder and Process New Excel Files — reacting to files as they arrive.
• Skip a Report Run When the Input File Has Not Changed — avoiding duplicate runs.
• Detect a Scheduled Report That Did Not Run — the other side of the problem.