Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. data_interval_start and data_interval_end

Airflow & DAGs · Scheduling Deep Dive

data_interval_start and data_interval_end

Mediumairflow-44
schedulingdata-intervalidempotencybackfill

Question

What are data_interval_start and data_interval_end, and why should SQL use them instead of now()?

Solution

In Airflow, data_interval_start and data_interval_end define the exact time boundaries of the data slice that a DAG run is responsible for processing. They are the foundation of deterministic, reproducible pipelines.

Understanding the execution window

Airflow operates on data intervals rather than execution moments. A daily DAG scheduled for 2025-01-01 covers the half-open interval from midnight to midnight:

Interval:   [2025-01-01 00:00:00, 2025-01-02 00:00:00)
Start time: 2025-01-01 00:00:00  (data_interval_start)
End time:   2025-01-02 00:00:00  (data_interval_end)
Triggered:  2025-01-02 00:00:00 or later (after all Jan 1 data has arrived)

Because a pipeline cannot process all of January 1 until January 1 has completed, the scheduler launches the run only after data_interval_end passes.

Why using now() in SQL breaks pipelines

A common mistake among junior engineers is querying data using real-time functions:

-- Dangerous: non-deterministic SQL query
SELECT * FROM raw_orders
WHERE order_timestamp >= NOW() - INTERVAL '1 DAY';

If this task runs at 02:00, it reads from yesterday at 02:00 to today at 02:00. If the task fails due to a network glitch and retries at 04:00, the query extracts a different 24-hour window, silently skipping two hours of orders. If you run a backfill three months later, NOW() queries current data instead of historical records, corrupting your analytics tables.

Safe parameterization

Use templated interval macros inside your extraction scripts:

-- Safe: idempotent extraction query
SELECT * FROM raw_orders
WHERE order_timestamp >= '{{ data_interval_start }}'
  AND order_timestamp < '{{ data_interval_end }}';

Every execution and retry receives identical timestamp strings. Whether the pipeline runs on schedule tonight, retries tomorrow morning, or is backfilled next year, the query extracts the exact same data slice.

PreviousNext