Debugging Airflow 101, Episode 01

Overlapping DAG run hell

Debugging Airflow 101 is an endless series where I share the Airflow mistakes I’ve made at my day job and how I debugged them, so you can learn from my experience and avoid breaking production at yours.

When a DAG run starts before the previous run has finished, that is called an overlapping run.

It can also be a chain of runs, each starting before the one before it has completed, so you can have many runs of the same DAG going at the same time. That is why I call it hell.

Let’s look at a real-life example from a logistics client I worked with at my day job.

You have a DAG scheduled to run every 15 minutes. It orchestrates a data pipeline that brings data from a source into a target table incrementally, using a checkpoint. The incremental data is then processed by some business logic and loaded into a table that is used directly in reporting.

The checkpoint can be a date column or a sequential integer ID column from the table the pipeline is loading. Let’s assume it is a sequential integer ID.

To store the checkpoint, we’ll use a logging table that looks like this:

Business requirement

To give you more context, here is the business problem.

This is a logistics client (they move parcels from point A to point B), so it’s very important for them to regularly monitor the efficiency of their delivery partners, and of the warehouses and locations a parcel passes through before it reaches the customer.

To do this, they collect data throughout the parcel’s journey by scanning the label pasted on it. This scan data is append-only, so the data for a parcel keeps accumulating in the database.

Every time a parcel passes a milestone, a scan is recorded. A milestone can be the first pickup from the customer, the first time the parcel enters a warehouse, the last warehouse it goes to, or the parcel finally reaching its destination.

So the problem is to get this data into a form that can be analysed to judge how efficient the business’s logistics system is.

Technical challenge

Let’s go into the technical details. There are two important tables in this case.

First, the table where scan data is loaded from the source database, where scans are recorded right after they happen. Let’s call it Scans.

Second, the table that holds KPI data such as the FirstScanDate or LastScanDate of a label. Let’s call it Fact_Scan_KPI.

If someone wants the time taken for a label to be delivered, it’s a simple calculation:

LastScanDate − FirstScanDate

The pipeline follows these steps to load data into the target table:

1. Get the last loaded ID from the logging table.
2. Filter the source table to get only the rows with an ID greater than the last loaded ID from step 1, and load them into the Scans table.
3. MERGE/UPSERT the staging table data into the target table.
4. Update the last loaded ID (the checkpoint) in the logging table, using the max value of the ID column in the Scans table.

The diagram below shows an Airflow DAG where each task follows one of these steps.

Simulating the problem

Let’s simulate the data load for Run 0 and Run 1.

Without overlapping runs

Run 0

Say the ID column has the value 0 in the logging table. When the first task runs in Airflow, it returns 0 as the value in XCom, which is how tasks share data in Airflow.

The second task, Load_Scan_Table, uses this value to get all rows from the source table with an ID greater than 0. Basically, it brings in everything for Run 0.

Let’s assume the max ID in the source table was 1000 at that moment, so the max ID in the Scans table is also 1000. Once the Scans table is loaded, the third task is triggered.

The third task checks the logging table, finds that the last ID is 0 for Run 0, and sets that as start_id. For end_id it takes the max of the ID column in the Scans table, which is 1000.

So it takes 1000 records, applies the business logic and loads the Fact_Scan_KPI table. The last task then updates the logging table to 1000.

Run 1

This time the first task returns 1000 to XCom, and the whole process repeats.

With overlapping runs

We know the DAG is scheduled to run every 15 minutes. What if Run 0 takes more than 15 minutes to complete, say 25 minutes? Meanwhile Run 1 starts, and you can feel the trouble coming.

Assume Run 1 loads 500 rows into the Scans table.

Run 1’s first and second tasks finish. Right at this point, the last task of Run 0 executes. It updates the last ID in the logging table with the max ID in the Scans table, which has just been loaded with new data by Run 1’s second task. Now the last ID in the logging table is 1500, and the max ID in the Scans table is also 1500.

If you read that carefully, you can see the problem coming. I’ve tried to simplify it in the diagram below.

For Run 1’s third task, start_id comes from the logging table and end_id is the max ID in the Scans table. Both are 1500, so when the task filters the Scans table, it finds no new data:

-- This query will not return anything because start_id and end_id both have value 1500
SELECT *
FROM Scans
WHERE ID > start_id AND ID <= end_id

So no data goes into the Fact_Scan_KPI table. To make it worse, say it takes 20 minutes for Run 1 to reach this point, and Run 2 is triggered and starts loading data into the Scans table.

The worst case is that when Run 2 finishes loading the Scans table, the last task of Run 1 executes and updates the logging table with the max ID in Scans again. The two values become equal once more, so no data is loaded into Fact_Scan_KPI.

If this keeps happening, we will never get data in the Fact_Scan_KPI table. Even if some runs work, we end up with a lot of missing data, which means incorrect reporting.

The solution

From the situation above, we know our requirement is to make the DAG runs sequential. Airflow has a parameter that stops overlapping runs:

max_active_runs = 1

This is the config you give to your DAG. It limits the DAG to one running instance at a time. Here is a code snippet that applies it:

from datetime import datetime
from airflow import DAG

with DAG(
    dag_id="my_dag",
    schedule="*/15 * * * *",
    start_date=datetime(2026, 1, 1),
    catchup=False,
    max_active_runs=1,  # only one run of this DAG at a time
) as dag:
    ...

After this fix, the DAG runs execute sequentially and the Fact_Scan_KPI table starts getting correct data. That leads to correct KPI calculations for the downstream business users.

At my job it took me a long time to figure out what was happening, because I didn’t know whether the issue was in Airflow. We were reconciling data with another server for this table and found discrepancies.

We were using Google Cloud’s BigQuery data warehouse to store this table. I used BigQuery’s time travel feature and Airflow’s metadata about the DAG and its runs extensively to get to the root cause.

Once I found it, the fix was simple. That is why going to the root cause is so important: very often the fix is easy to apply once you know the real cause.

I’ll write more about BigQuery’s time travel feature some other time, in a series on Google Cloud. Till then, stay tuned, and thanks for reading this far.