Scheduling, Data Intervals & the Logical Date Trap

Your shop runs one job every day. The job builds daily_revenue for one day. Airflow is the tool that starts this job on a schedule.

You already know the logical date: the day a run is about. In this chapter you learn what Airflow means by it, and where the trap is.

You will answer three questions:

1. When does each daily run start?
2. Which orders does each run read?
3. What happens when you turn on a DAG with an old start date?

A paid order counts as revenue. Refunded and pending orders do not. Every example uses the shop table below:

You met the DAG in the last chapter: tasks and arrows. Here it is written as code.

A DAG run is one time the DAG runs.

A daily DAG makes one DAG run per day. Each run builds daily_revenue for one day.

Look at the settings that decide when runs exist:

import pendulum
from airflow.sdk import DAG
from airflow.timetables.interval import CronDataIntervalTimetable

with DAG(
    dag_id="daily_revenue",
    schedule=CronDataIntervalTimetable("0 0 * * *", timezone="UTC"),
    start_date=pendulum.datetime(2026, 1, 1, tz="UTC"),
    catchup=False,
) as dag:
    ...  # the tasks go here

The schedule is the rule that creates runs. Here it is a cron string, "0 0 * * *", which means every day at 00:00. A timetable is the object that turns that rule into runs.

The start_date is where the first run begins: 2026-01-01. The catchup setting decides what happens to days that are already over. A later section explains it.

Every run has a data interval. It is the span of time the run is about.

For a daily DAG, one interval is one full day. It starts at 00:00 and ends at the next 00:00.

The start is included. The end is not.

Airflow creates a run only after its interval has ended. So the run about 2026-01-10 starts around 2026-01-11 00:00. Look at the timeline:

time ->    Jan 10 00:00        Jan 11 00:00        Jan 12 00:00
           |<-- interval A --->|<-- interval B --->|
           orders of Jan 10    orders of Jan 11

                               ^ run A starts here
                                                   ^ run B starts here

Airflow gives each run these values. The table shows them for the run about 2026-01-10:

The logical date is a label for the day. It is not the time the run starts.

In Airflow 2, the logical date was also called execution_date. Airflow 3 removed that old name, but many tutorials still use it.

Now see which orders each run reads. Every run reads the orders whose order_date is inside its interval:

An operator is a ready-made kind of task. SQLExecuteQueryOperator runs SQL on a database. The task_id names the task, and conn_id is the name of the saved database login.

A template is text with {{ }} holes in it. Airflow fills each hole when the run starts. Use the data interval values to pick the rows.

The first statement deletes the day, so a rerun is safe. The second builds the day:

from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

build_revenue = SQLExecuteQueryOperator(
    task_id="build_daily_revenue",
    conn_id="shop_db",
    sql=[
        "DELETE FROM daily_revenue WHERE revenue_date = '{{ data_interval_start | ds }}'",
        """
        INSERT INTO daily_revenue (revenue_date, revenue)
        SELECT DATE '{{ data_interval_start | ds }}', COALESCE(SUM(amount), 0)
        FROM orders
        WHERE status = 'paid'
          AND order_date >= '{{ data_interval_start | ds }}'
          AND order_date <  '{{ data_interval_end | ds }}'
        """,
    ],
)

In a template, | ds turns a timestamp into text like 2026-01-10. The short name {{ ds }} is the logical date as text. With this timetable it equals the start.

Airflow fills in the template for the run about 2026-01-10. This is the query the database receives:

SELECT DATE '2026-01-10', COALESCE(SUM(amount), 0)
FROM orders
WHERE status = 'paid'
  AND order_date >= '2026-01-10'
  AND order_date <  '2026-01-11';

Order 103 has the next day, 2026-01-11, so it belongs to the next run. That run gets 0.00, because 103 is refunded.

A common mistake is to ask the computer clock for the day, instead of reading the interval. The code looks fine:

from datetime import date, timedelta

day = date.today() - timedelta(days=1)   # "yesterday" by the clock

On the first try, this works. The run about 2026-01-10 starts on 2026-01-11, so "yesterday" is 2026-01-10. It breaks when the run happens at another time:

No task fails. The number is simply wrong, and it looks normal. The interval fixes this because it never changes for one run.

The first DAG chose its timetable on purpose. Airflow has two main kinds, and they treat the same cron string differently.

A tick is a moment when the schedule fires. Look at the same tick, 2026-01-11 00:00, under both:

In Airflow 2, a plain cron string such as "0 0 * * *" or "@daily" used the data interval timetable. In Airflow 3, it uses the trigger timetable by default. Airflow 3 has a setting for this:

# airflow.cfg
[scheduler]
create_cron_data_intervals = False   # Airflow 3 default: plain cron -> trigger timetable
create_cron_data_intervals = True    # plain cron -> data interval timetable

The trap: the start equals the end. Run the same SQL for the tick 2026-01-10 00:00 and the window is empty:

SELECT DATE '2026-01-10', COALESCE(SUM(amount), 0)
FROM orders
WHERE status = 'paid'
  AND order_date >= '2026-01-10'
  AND order_date <  '2026-01-10';

The fix is the one you already used. Pass the timetable yourself, as in the first DAG, so the DAG means the same on every setup.

The start_date is the first day the DAG may have runs. Catchup decides what Airflow does with intervals that ended before you turned the DAG on.

This uses the data interval timetable from the first DAG.

Say start_date is 2026-01-01, and you turn the DAG on at 2026-01-21 08:00. Twenty intervals have already ended: 2026-01-01 up to 2026-01-20. Look at both settings:

With catchup on, the runs build the history. Here are the runs that find orders:

Set catchup on every DAG. If you do not, the default comes from the setting catchup_by_default. It is False in Airflow 3 and it was True in Airflow 2.

Always write start_date as a fixed date. A moving value like datetime.now() changes every time Airflow reads your file. The DAG may then never run.

A backfill creates runs for a range of dates that you choose. Use it when runs are missing. For example, catchup was off, or a breakdown skipped days.

Say the runs for 2026-01-10 to 2026-01-12 do not exist. This command makes one run for each logical date in that range:

# Airflow 3
airflow backfill create --dag-id daily_revenue \
    --from-date 2026-01-10 --to-date 2026-01-12

# Airflow 2
airflow dags backfill --start-date 2026-01-10 --end-date 2026-01-12 daily_revenue

This is why the interval matters. Each backfilled run asks for its own day, so it never reads today. A clock-based query would read the same wrong day three times.

A backfill runs your tasks again and again. The DELETE before the INSERT keeps that safe. The Airflow chapter Backfills, Catchup, Retries & Deadlines covers the limits and the safe way to run big backfills.

UTC is one time for everyone, with no clock changes. You met it in the Python chapters. Airflow can also schedule in a local time zone.

Daylight saving time means clocks move one hour forward in spring and one hour back in autumn.

In a local zone with daylight saving time, some days are not 24 hours long. Look at the shop in Berlin (Europe/Berlin):

Code that says "the window is the run time minus 24 hours" is wrong on those two days. The interval from a time-zone-aware timetable already has the right length.

So schedule in UTC, as in the first DAG. Set timezone="UTC" on the timetable and use a UTC start_date. Convert to local time only when you show results.

Seven things to remember:

• A run has a data interval: the span of time it is about.
• With a data interval timetable, a run starts after its interval ends. The run about 2026-01-10 starts on 2026-01-11.
• The logical date is the day the run is about. In Airflow 2 it was also called execution_date.
• Read data_interval_start and data_interval_end in your SQL. Never use now() to pick the data day.
• Airflow 3 uses the trigger timetable for a plain cron string, so the start equals the end. Choose your timetable on purpose.
• Catchup decides whether past intervals get runs. A backfill makes runs for a range you choose.
• Schedule in UTC, with a fixed start_date.

Check yourself:

1. A DAG with catchup on and start_date 2026-01-01 is turned on at 2026-01-04 00:30. Which logical dates get runs?
2. Which order does the run about 2026-01-15 read, and when does it start?
3. A task returns 0.00 for a day with a paid order. Name two causes.

Answers:

1. 2026-01-01, 2026-01-02 and 2026-01-03. The 01-03 interval ended at 2026-01-04 00:00.
2. It reads order 104, and it starts on 2026-01-16 00:00.
3. The query used the clock instead of the interval, or a trigger timetable made the start equal the end.