Guide

Automating Reporting WorkflowsDeep dive

Orchestrate Excel Reports with Apache Airflow

Turn a scheduled Excel script into a DAG: importable report functions, storage keys through XCom, a deferrable sensor for upstream data, and logical dates that make reruns safe.

A report that is one script on a timer belongs in cron. A report that has to wait for an upstream load, build several outputs, validate them and then distribute them is a pipeline, and pipelines benefit from something that tracks dependencies, retries individual steps and keeps a history you can look at. This guide, part of Scheduling Python Excel Scripts with Cron, puts an Excel reporting job into an Airflow DAG without letting the orchestration swallow the logic.

Prerequisites

Bash

pip install "apache-airflow==2.10.*" pandas openpyxl xlsxwriter

An Airflow instance — Astronomer, MWAA, a Helm deployment or a local airflow standalone — and somewhere shared to put files, which the examples below treat as S3.

Keep the report logic out of the DAG

The most important design decision is made before any Airflow code: the report should be ordinary functions in an ordinary module, importable and testable without an Airflow installation. The DAG then becomes a thin description of order and scheduling.

Python

# reports/regional.py — no Airflow imports anywhere in this file
from datetime import date
from pathlib import Path
import pandas as pd

def build(run_date: date, source: Path, out_dir: Path) -> Path:
    frame = pd.read_excel(source, engine="calamine")
    summary = frame.groupby("Region", as_index=False)["Revenue"].sum()
    target = out_dir / f"regional-{run_date:%Y-%m-%d}.xlsx"
    with pd.ExcelWriter(target, engine="xlsxwriter") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False)
        frame.to_excel(writer, sheet_name="Detail", index=False)
    return target

def validate(path: Path, minimum_rows: int = 1) -> dict:
    summary = pd.read_excel(path, sheet_name="Summary")
    if len(summary) < minimum_rows:
        raise ValueError(f"{path.name}: summary has {len(summary)} rows")
    return {"rows": len(summary), "total": float(summary["Revenue"].sum())}

Everything Airflow-specific stays in the DAG file. That separation is what lets the report be run from a laptop during development and covered by the tests in Test Excel Output with pytest.

The DAG

Python

from datetime import datetime, timedelta
from pathlib import Path
import pendulum
from airflow.decorators import dag, task
from airflow.providers.amazon.aws.hooks.s3 import S3Hook

from reports import regional

BUCKET = "reporting-artifacts"

@dag(
    dag_id="regional_revenue_report",
    schedule="0 6 * * 1-5",
    start_date=pendulum.datetime(2026, 8, 1, tz="Europe/London"),
    catchup=False,
    max_active_runs=1,
    default_args={"retries": 2, "retry_delay": timedelta(minutes=5)},
    tags=["excel", "reporting"],
)
def regional_revenue_report():

    @task
    def build(logical_date=None) -> str:
        hook = S3Hook()
        local_dir = Path("/tmp/reports")
        local_dir.mkdir(exist_ok=True)
        source = Path(hook.download_file(
            key=f"exports/orders-{logical_date:%Y-%m-%d}.xlsx",
            bucket_name=BUCKET, local_path=str(local_dir), preserve_file_name=True,
        ))
        built = regional.build(logical_date.date(), source, local_dir)
        key = f"reports/{built.name}"
        hook.load_file(filename=str(built), key=key, bucket_name=BUCKET, replace=True)
        return key

    @task
    def check(key: str) -> dict:
        hook = S3Hook()
        local = Path(hook.download_file(key=key, bucket_name=BUCKET,
                                        local_path="/tmp", preserve_file_name=True))
        return regional.validate(local)

    @task
    def distribute(key: str, stats: dict) -> None:
        print(f"sending {key}: {stats['rows']} regions, total {stats['total']:,.0f}")

    built_key = build()
    distribute(built_key, check(built_key))

regional_revenue_report()

max_active_runs=1 prevents two runs of a report writing the same output at once, and catchup=False stops a newly deployed DAG from immediately running every day since the start date — both defaults worth setting deliberately rather than discovering.

Passing files between tasks

Tasks may run on different workers, so anything written to local disk in one task may not exist in the next. The rule is that XCom carries a key and shared storage carries the file.

Python

@task
def build() -> str:
    ...
    return key                    # a short string — fine for XCom

@task
def check(key: str) -> dict:
    ...                           # download from the key

Returning a DataFrame or file bytes from a task pushes them into Airflow's metadata database, which is the reliable way to make a scheduler slow and an operations team unhappy. The same argument applies to reading the source: fetch it in the task that needs it rather than at module level, since module-level code runs on every DAG parse.

Waiting for the upstream file

The dependency that motivates Airflow in the first place is usually "do not build until the export has landed". A sensor expresses that, and deferrable mode means it costs nothing while it waits.

Python

from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor

wait = S3KeySensor(
    task_id="wait_for_export",
    bucket_name=BUCKET,
    bucket_key="exports/orders-{{ ds }}.xlsx",
    deferrable=True,
    timeout=60 * 60 * 3,
    poke_interval=300,
)
wait >> built_key

deferrable=True hands the wait to the triggerer rather than occupying a worker slot for three hours, which matters as soon as several reports wait in parallel. {{ ds }} is the logical date, which is what makes a backfill look for the right day's file rather than today's.

Making the DAG idempotent

Naming every artefact by logical_date rather than datetime.now() is what makes a rerun safe. A task that fails at 06:10 and retries at 06:15 must produce the same file name, and a backfill for last Tuesday must produce Tuesday's.

Python

target = out_dir / f"regional-{logical_date:%Y-%m-%d}.xlsx"     # correct
target = out_dir / f"regional-{datetime.now():%Y-%m-%d}.xlsx"   # wrong on every retry

Uploading with replace=True completes the property: rerunning overwrites rather than failing or accumulating duplicates. Between the two, a rerun becomes something an operator can do without thinking about it, which is most of Airflow's practical value.

Alerting on the runs that matter

Airflow's default failure email is a poor alert: it fires per task, says little, and is easy to filter into a folder nobody opens. A callback that posts a short message naming the DAG, the task and the logical date is far more useful, and it lives in one place.

Python

def on_failure(context) -> None:
    task = context["task_instance"]
    message = (
        f"{task.dag_id}.{task.task_id} failed for {context['logical_date']:%Y-%m-%d} "
        f"(try {task.try_number} of {task.max_tries + 1})"
    )
    notify_channel(message)          # Teams, Slack, PagerDuty — whatever the team reads

default_args = {
    "retries": 2,
    "retry_delay": timedelta(minutes=5),
    "on_failure_callback": on_failure,
}

Including the try number is what keeps the alert honest: a first failure that will be retried in five minutes is information, and a final one after every retry is an incident. Sending only on the last attempt — by checking task.try_number > task.max_tries — is the variant worth adopting once the alert volume becomes noticeable.

The complementary signal is an SLA: a report that must be on somebody's desk by 07:00 should say so, so that a run which is merely slow is caught as well as one which failed.

Python

@task(sla=timedelta(minutes=45))
def build(logical_date=None) -> str:
    ...

Testing a DAG without running Airflow

Because the report logic lives outside the DAG, most of the testing is ordinary pytest against those functions. What remains worth testing about the DAG itself is that it imports cleanly and has no cycles — a check that catches the majority of deployment failures and runs in under a second.

Python

from airflow.models import DagBag

def test_dags_import_cleanly():
    bag = DagBag(dag_folder="dags", include_examples=False)
    assert not bag.import_errors, bag.import_errors
    dag = bag.get_dag("regional_revenue_report")
    assert dag is not None
    assert dag.default_args["retries"] >= 1

Running that in continuous integration means a typo in a DAG file fails the pull request rather than the 06:00 schedule. It is the cheapest test in the repository and catches the failure mode that costs the most, which is a DAG that stops appearing in the interface without anybody noticing.

Common pitfalls

Symptom	Cause	Fix
Task fails with a missing file	The previous task wrote to a different worker's disk	Pass a storage key, not a path
Scheduler slows down over weeks	Large XCom payloads in the metadata database	Return keys and small dicts only
A backfill produces today's date in every file	`datetime.now()` used instead of `logical_date`	Use the logical date everywhere
DAG runs hundreds of times on deploy	`catchup` left at its default	`catchup=False` unless backfilling is intended
Import errors at parse time	Heavy imports or I/O at module level	Import inside the task function
PDF conversion fails on some workers	LibreOffice not in every worker image	Isolate that task on its own queue or image

Performance and scale

Airflow's overhead is per task, not per row: each one is a scheduling decision, a worker slot and a database write. That makes a DAG of five meaningful tasks much healthier than one of fifty trivial ones, and it argues against the instinct to split a report into a task per sheet.

Where a report genuinely fans out — one workbook per region, thirty regions — dynamic task mapping expresses it without writing thirty tasks:

Python

@task
def regions() -> list[str]:
    return ["North", "South", "East", "West"]

@task
def build_one(region: str) -> str:
    ...

build_one.expand(region=regions())

Mapped tasks run in parallel up to the pool's limit, which is the right way to shorten a fan-out. The per-region generation itself is covered in Generate One Excel Report per Region in a Loop.

Conclusion

Keep the report as importable functions and let the DAG describe only order, scheduling and retries. Pass storage keys between tasks rather than files, name every artefact by the logical date so retries and backfills are safe, wait for upstream data with a deferrable sensor, and keep the task count proportional to the number of genuinely separate steps. Cron remains the right answer for a single script; this is what to reach for when the report has become a pipeline.

Frequently asked questions

Why use Airflow rather than cron for an Excel report? For a single script on a schedule, cron is simpler and you should keep it. Airflow earns its keep when the report has dependencies — wait for a warehouse load, then build, then check, then distribute — and when you need retries, backfills and a visible history of which runs succeeded.

Should the workbook be passed between tasks? Never through XCom, which is for small values. Write the file to shared storage — S3, a mounted volume, a blob container — and pass its key between tasks. XCom then carries a string, which is what it is designed for.

How do I stop two runs writing the same file? Name outputs by the logical date rather than the wall clock: the run for 1 September writes report-2026-09-01.xlsx whether it runs on time or three days late during a backfill. That single convention makes the whole DAG idempotent.

Do I need a separate worker image with LibreOffice? If any task converts to PDF, yes — and it is worth isolating it. Use a KubernetesPodOperator or a dedicated queue so only the conversion task needs the heavier image, rather than growing every worker.

Up one level: Scheduling Python Excel Scripts with Cron — the simpler scheduling options and when they suffice.
Schedule Recurring Excel Reports with APScheduler — in-process scheduling without an orchestrator.
Run a Python Excel Report in Docker — the image the workers run.
Upload an Excel Report to Amazon S3 with boto3 — the storage layer tasks hand keys through.
Retry a Failed Excel Report Job in Python — in-script retries, and where the orchestrator's take over.