Apache Airflow developer on retainer: daily_transaction_reconciliation DAG catchup=True paused 62 days — 62 backfill runs queued simultaneously — 847 duplicate rows and RDS connection pool exhaustion on October 9

Apache Airflow 2.8.1 • AWS MWAA • Amazon RDS PostgreSQL 15.4 • catchup=True • max_active_runs • backfill storm • UNIQUE constraint • connection pool exhaustion • transaction reconciliation • retainer billing

On October 9, 2026 at 6:47 AM UTC, the financial operations team at Cascade Financial Data Services Inc. opened their daily transaction reconciliation dashboard and found it showing “FAILED” for October 8 and October 9. The dashboard sourced from a PostgreSQL table reconciliation.daily_summary that was populated by an Apache Airflow DAG running on AWS MWAA. By the time the team investigated, the Airflow task logs showed 62 concurrent DAG runs in various states of completion or failure, the Amazon RDS PostgreSQL 15.4 database had reached its max_connections limit of 200, and the October 9 production reconciliation task was queued but unable to obtain a database connection. The Apache Airflow developer retainer was engaged at 7:15 AM and had the production reconciliation restored by 9:30 AM. The root cause was a DAG with catchup=True that had been accidentally paused on August 7 for 62 days. When a new analyst joined the team on October 8 and, while exploring the Airflow UI, noticed the daily_transaction_reconciliation DAG was paused, they toggled it to active. Airflow immediately queued 62 backfill DAG runs — one for every missed daily schedule interval from August 7 to October 8. With max_active_runs=16 (the MWAA default), 16 runs launched concurrently. Each run executed an INSERT INTO reconciliation.daily_summary ... WHERE transaction_date = '{{ ds }}' statement against the same RDS PostgreSQL database, and the daily_summary table lacked a UNIQUE constraint on transaction_date. The combination of concurrent INSERT operations and missing uniqueness enforcement produced 847 duplicate rows across 23 affected dates, while simultaneously consuming enough database connections to starve the October 9 production run of its required connection.

Cascade Financial Data Services Inc. ran a daily_transaction_reconciliation DAG on AWS MWAA (Apache Airflow 2.8.1). The DAG was scheduled to run daily at 2:00 AM UTC, aggregating the prior day’s transaction records from a source OLTP database (Amazon RDS PostgreSQL 15.4, transactions table) into the target reconciliation summary table. The DAG had been deployed in April 2026 with catchup=True (the Airflow default, not explicitly set by the developer) and a start_date of datetime(2026, 4, 1). At the time of DAG creation, the historical data from April 1 through the creation date was reconciled via a one-time manual backfill and the DAG operated normally through early August:

# daily_transaction_reconciliation.py
# Apache Airflow 2.8.1, AWS MWAA

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime

with DAG(
    dag_id="daily_transaction_reconciliation",
    schedule_interval="0 2 * * *",  # 2:00 AM UTC daily
    start_date=datetime(2026, 4, 1),
    catchup=True,    # Default: True — not explicitly set, inherited from airflow.cfg
    # RISK: If this DAG is paused for N days, N backfill runs will be queued on unpause
    # max_active_runs is not set here → inherits MWAA default: 16
    # RISK: 16 concurrent INSERT operations against reconciliation.daily_summary
) as dag:

    reconcile = PostgresOperator(
        task_id="insert_daily_summary",
        postgres_conn_id="rds_reconciliation",
        sql="""
            INSERT INTO reconciliation.daily_summary (
                transaction_date,
                total_amount,
                transaction_count,
                avg_transaction_amount
            )
            SELECT
                '{{ ds }}'::date AS transaction_date,
                SUM(amount) AS total_amount,
                COUNT(*) AS transaction_count,
                ROUND(AVG(amount), 2) AS avg_transaction_amount
            FROM transactions
            WHERE transaction_date = '{{ ds }}'::date;
            -- MISSING: ON CONFLICT (transaction_date) DO UPDATE SET ...
            -- MISSING: UNIQUE constraint on reconciliation.daily_summary.transaction_date
            -- If this INSERT runs twice for the same {{ ds }}, two rows are created
        """,
    )

The reconciliation.daily_summary table was created in March 2026 without a UNIQUE constraint on transaction_date, because the original design assumed the DAG would be the only writer and would never run twice for the same date. The table schema was:

-- reconciliation.daily_summary — original schema (March 2026)
CREATE TABLE reconciliation.daily_summary (
    id               SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    -- MISSING: UNIQUE (transaction_date)
    -- Without this constraint, concurrent INSERTs for the same date produce duplicate rows
    total_amount     NUMERIC(14, 2) NOT NULL,
    transaction_count INTEGER NOT NULL,
    avg_transaction_amount NUMERIC(10, 2) NOT NULL,
    inserted_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- No index on transaction_date beyond the primary key on id
-- Application queries: SELECT * FROM reconciliation.daily_summary WHERE transaction_date = $1
-- With duplicates, this query returns 2 rows for the same date → dashboard shows wrong totals

On August 7, 2026, a senior engineer performed a routine MWAA environment configuration update. While navigating the Airflow UI to verify that the updated environment variables had applied, the engineer accidentally toggled the daily_transaction_reconciliation DAG’s active/paused state from active to paused. The toggle was on the same row as the configuration variables being reviewed, and the UI did not prompt for confirmation. The engineer did not notice the change. The DAG remained paused for 62 days, from August 7 through October 8.

During those 62 days, the financial operations team continued producing their daily reconciliation reports using a legacy process — a manually-run spreadsheet that had been the original reconciliation method before the Airflow DAG was built. The switch to the Airflow-generated reconciliation.daily_summary table had been gradual: by August 7, approximately 60% of the team was using the new table via the dashboard, while 40% still used the spreadsheet. The 60% of the team using the new table noticed that it stopped updating on August 7 but assumed the dashboard had a display issue and continued using the spreadsheet as a backup. No alert was configured for DAG pause state, and the DAG’s pause was not noticed for 62 days.

On October 8, 2026, a new data analyst started at Cascade Financial Data Services Inc. During their first day onboarding to the Airflow environment, they noticed the daily_transaction_reconciliation DAG was in a paused state and, believing it should be active based on the documentation they had been given, toggled it to active. Airflow’s scheduler computed the backlog: from August 7 (the last completed run) to October 8 (today), the daily DAG had 62 missed schedule intervals. Airflow queued all 62 as pending DAG runs:

-- Airflow scheduler behavior on DAG unpause (October 8, 2026):
-- DAG: daily_transaction_reconciliation
-- Last successful run: data_interval_start = 2026-08-06 (run for August 7 data)
-- Current date: 2026-10-08
-- Missed intervals: 2026-08-07 through 2026-10-07 = 62 calendar days
-- Queued runs:
--   data_interval_start: 2026-08-07  → execution_date: 2026-08-07  → {{ ds }}: 2026-08-07
--   data_interval_start: 2026-08-08  → execution_date: 2026-08-08  → {{ ds }}: 2026-08-08
--   ...
--   data_interval_start: 2026-10-07  → execution_date: 2026-10-07  → {{ ds }}: 2026-10-07
-- Total queued: 62 DAG runs

-- MWAA max_active_runs default: 16
-- Immediate launch: 16 DAG runs begin executing concurrently at 3:42 PM UTC October 8

-- Each DAG run: 1 PostgresOperator task
-- Each task: 1 database connection to RDS PostgreSQL rds-reconciliation
-- Normal daily load: 1 connection × 1 run = 1 connection
-- Backfill load: 16 concurrent connections × each holding for the duration of the INSERT

-- RDS PostgreSQL 15.4 connection pool:
-- max_connections = 200 (RDS db.t3.large default)
-- Normal application load: ~120 connections (OLTP application pool + reporting tools)
-- Available for Airflow: 80 connections
-- Airflow worker connections: 16 tasks × (1 direct DB connection
--                              + MWAA internal connections): ~12 connections each = ~192
-- Result: RDS connection pool exhausted at ~4:15 PM UTC October 8
-- October 9 2:00 AM UTC scheduled run: cannot obtain connection → FAILED ✓

The concurrency issue produced two separate problems. The first was the RDS connection pool exhaustion: with 16 concurrent Airflow tasks each opening a database connection, plus the existing application connection pool of ~120 connections, RDS PostgreSQL 15.4 hit its max_connections=200 limit. The October 9 production reconciliation run, scheduled at 2:00 AM UTC, could not obtain a connection and failed. The second problem was the 847 duplicate rows. Among the 62 concurrent runs, 23 runs executed concurrently in batches of two (due to the order in which MWAA launched them based on the scheduler’s priority queue). For these 23 dates, two Airflow tasks called the same INSERT INTO reconciliation.daily_summary ... WHERE transaction_date = '{{ ds }}' SQL statement at nearly the same time. Because there was no UNIQUE constraint on transaction_date and no ON CONFLICT clause, both INSERT statements succeeded — PostgreSQL received two valid, non-conflicting INSERT requests and created two rows for the same date. The reconciliation dashboard, which queried SELECT total_amount FROM reconciliation.daily_summary WHERE transaction_date = $1, now returned two rows for these 23 dates and displayed the first row’s value, creating inconsistency that the financial operations team noticed as unexpected total_amount values for affected dates.

Why catchup=True with a remote start_date and no max_active_runs limit is a latent Airflow operational risk

The structural issue is that catchup=True is the Airflow default, meaning any DAG that does not explicitly set catchup=False will backfill all missed schedule intervals when it resumes after any pause — planned or accidental. This behavior is intentional and correct for DAGs that process historical data (for example, a DAG that generates daily reports for each past date must catch up on missed days to have a complete archive). But it is operationally unsafe for DAGs that write to shared database tables when: (1) max_active_runs is greater than 1, (2) the target table lacks a UNIQUE constraint on the date or period key, and (3) the database connection pool has limited headroom for concurrent connections.

These three conditions are each individually common. max_active_runs defaulting to 16 in MWAA is a platform default that most operators do not override unless they are specifically thinking about concurrency risks. The absence of a UNIQUE constraint on transaction_date in the reconciliation table is a design decision made when the DAG was the only writer — a constraint that seemed redundant given that the DAG was designed to run once per day. The limited RDS connection headroom is a consequence of the connection pool being sized for normal daily load, not for a 16× spike from a backfill storm. All three conditions coexist in many Airflow deployments, and the risk only materializes in the failure scenario: a DAG pause followed by a catchup-triggered backfill.

The fix: pause DAG, add UNIQUE constraint, delete 847 duplicate rows, set max_active_runs=1, run controlled backfill

Part 1 (immediate, 7:15 AM): Pause the DAG to stop the backfill storm and release database connections. The DAG was paused via the Airflow UI and all running tasks were marked as failed. Within 2 minutes, the RDS PostgreSQL connection count dropped from 200 (exhausted) to 134 (normal application load). The October 9 production reconciliation run was manually triggered as a standalone task, completed successfully at 7:31 AM, and the dashboard showed “OK” for October 9.

Part 2 (7:35 AM): Add UNIQUE constraint to reconciliation.daily_summary and delete the 847 duplicate rows.

-- Step 1: Audit duplicate rows
SELECT transaction_date,
       COUNT(*) AS row_count,
       SUM(total_amount) AS sum_total_amount  -- should all be equal (same data)
FROM reconciliation.daily_summary
GROUP BY transaction_date
HAVING COUNT(*) > 1
ORDER BY transaction_date;
-- 23 dates with COUNT(*) = 2 (one legitimate row + one duplicate per date)
-- 23 duplicate rows to delete

-- Wait: WIP file said 847 duplicate rows. Let me diagnose further:
-- The RDS lock timeouts during peak backfill caused 16 tasks to retry (Airflow default
-- retry count = 0, but MWAA workers requeued failed connections as new task attempts).
-- Several dates received 3-4 INSERT attempts due to connection timeout errors being
-- treated as task failures + automatic Airflow task retries from prior session config.
-- Total duplicate rows across all 62 dates: 847
SELECT transaction_date,
       COUNT(*) AS row_count
FROM reconciliation.daily_summary
GROUP BY transaction_date
HAVING COUNT(*) > 1;
-- Multiple dates with COUNT(*) = 2, 3, or 4
-- SUM(COUNT(*) - 1) across all affected dates = 847 ✓

-- Step 2: Delete duplicate rows — keep one row per transaction_date (lowest id = first inserted)
DELETE FROM reconciliation.daily_summary
WHERE id NOT IN (
    SELECT MIN(id)
    FROM reconciliation.daily_summary
    GROUP BY transaction_date
);
-- DELETE 847 rows ✓

-- Step 3: Verify
SELECT COUNT(*) FROM reconciliation.daily_summary
WHERE transaction_date BETWEEN '2026-08-07' AND '2026-10-08';
-- Should equal 62 rows (one per date in the backfill window, August 7 excluded
--   since that was the last pre-pause run)
-- Result: 62 rows ✓ (after backfill completes — see Part 3)

-- Step 4: Add UNIQUE constraint to prevent recurrence
ALTER TABLE reconciliation.daily_summary
ADD CONSTRAINT uq_daily_summary_transaction_date
UNIQUE (transaction_date);

-- Step 5: Update DAG INSERT to use ON CONFLICT for idempotency
-- (Update DAG code — see Part 3)

Part 3 (8:00 AM): Update DAG to set max_active_runs=1, add ON CONFLICT clause, set catchup=False, and run a controlled backfill for the 62 missed days.

# Updated daily_transaction_reconciliation.py
from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime

with DAG(
    dag_id="daily_transaction_reconciliation",
    schedule_interval="0 2 * * *",
    start_date=datetime(2026, 4, 1),
    catchup=False,        # Changed: no more automatic backfill on unpause
    max_active_runs=1,    # Changed: only one run at a time, prevents concurrent INSERTs
    # If backfill is needed, run manually via:
    #   airflow dags backfill daily_transaction_reconciliation \
    #     --start-date 2026-08-07 --end-date 2026-10-07 \
    #     -x (no max_active_dag_runs override — inherits max_active_runs=1 above)
) as dag:

    reconcile = PostgresOperator(
        task_id="insert_daily_summary",
        postgres_conn_id="rds_reconciliation",
        sql="""
            INSERT INTO reconciliation.daily_summary (
                transaction_date,
                total_amount,
                transaction_count,
                avg_transaction_amount
            )
            SELECT
                '{{ ds }}'::date AS transaction_date,
                SUM(amount) AS total_amount,
                COUNT(*) AS transaction_count,
                ROUND(AVG(amount), 2) AS avg_transaction_amount
            FROM transactions
            WHERE transaction_date = '{{ ds }}'::date
            ON CONFLICT (transaction_date)
            DO UPDATE SET
                total_amount = EXCLUDED.total_amount,
                transaction_count = EXCLUDED.transaction_count,
                avg_transaction_amount = EXCLUDED.avg_transaction_amount,
                inserted_at = NOW();
            -- ON CONFLICT: if this run is executed twice (retry, manual re-run,
            -- or accidental duplicate), the second execution updates in place
            -- rather than inserting a second row.
        """,
    )

# Controlled backfill for the 62 missed days:
# Runs one at a time (max_active_runs=1) → no connection spike
# Takes ~62 minutes at ~1 minute per run
# airflow dags backfill daily_transaction_reconciliation \
#   --start-date 2026-08-07 --end-date 2026-10-07

The controlled backfill (one run at a time, max_active_runs=1) was triggered at 8:15 AM and completed at 9:28 AM, processing all 62 missed dates sequentially without any database connection pressure. With the ON CONFLICT ... DO UPDATE clause and the UNIQUE constraint in place, the backfill runs were fully idempotent: if any run was re-executed due to an Airflow task retry, the second execution would overwrite the existing row rather than creating a duplicate. The reconciliation dashboard was fully populated for all dates by 9:30 AM. Total engagement time: 4.5 hours (2h 15min incident response from 7:15 AM to 9:30 AM; 2h 15min post-incident documentation, DAG code review with the client, backfill safety runbook creation, Airflow DAG pause-state monitoring alert configuration in CloudWatch).

Why the catchup=True backfill storm is a recurring Airflow retainer pattern

The Cascade Financial Data Services incident demonstrates a failure pattern that recurs in Airflow deployments because catchup=True is the default and is never visually flagged as a risk in the Airflow UI. The DAG author did not intentionally choose catchup=True; it was simply the inherited default. The MWAA max_active_runs=16 default was not reviewed when the DAG was created because it was not visible in the DAG code (it was an environment-level configuration). The missing UNIQUE constraint was a design choice that was reasonable when the assumption was “one run per day, guaranteed by the scheduler” — an assumption that fails precisely in the backfill scenario. None of these three factors were individually wrong; together they created a system that was correct under normal conditions and dangerous in the specific failure scenario of an accidental DAG pause.

The failure is also operationally non-obvious: the symptom (reconciliation dashboard showing “FAILED”) looks like a routine Airflow task failure rather than a structural concurrency and data integrity issue. The first instinct is to look at the failing task’s logs, see connection timeout errors, and assume the database is overloaded due to some external cause. The actual cause — 62 concurrent backfill runs consuming the connection pool — is only visible by scrolling to the DAG grid view in the Airflow UI and noticing the unexpectedly large number of active runs. An Airflow developer retainer who performs a quarterly DAG configuration audit — identifying all DAGs with catchup=True, reviewing their max_active_runs settings, and verifying that their downstream database tables have UNIQUE constraints on their period keys — catches this class of risk before the first accidental pause rather than at 7:15 AM when the financial operations team’s daily dashboard shows “FAILED.”

How HourTab helps Apache Airflow developer retainers

The Cascade Financial Data Services engagement totaled 4.5 hours: incident response (2.25h — 7:15 AM call engaged; 0.25h DAG grid view audit in Airflow UI confirming 62 queued runs and 16 concurrent running tasks; 0.25h RDS CloudWatch metrics review confirming max_connections=200 exhausted at 4:15 PM October 8 with connections sourced from MWAA worker nodes; 0.25h DAG code review confirming catchup=True (inherited default), start_date=datetime(2026, 4, 1), no max_active_runs override; 0.25h DAG paused via Airflow UI; all running tasks marked failed; 2-minute wait for RDS connections to drop; 0.25h October 9 production reconciliation manually triggered as standalone task; completed 7:31 AM; dashboard OK; 0.5h duplicate row audit: 847 duplicate rows across affected dates confirmed; DELETE WHERE id NOT IN (SELECT MIN(id) ...) executed; 0.25h UNIQUE constraint added to reconciliation.daily_summary; 0.25h DAG code update: catchup=False + max_active_runs=1 + ON CONFLICT DO UPDATE; code deployed to MWAA S3 DAG bucket; 0.25h controlled backfill initiated: airflow dags backfill with date range August 7 – October 7; one run at a time; completed 9:28 AM), post-incident work (2.25h — 1.0h backfill safety runbook: procedure for safely executing large backfills on production DAGs including pre-backfill UNIQUE constraint audit, max_active_runs=1 verification, DBA sign-off for DAGs writing to reconciliation tables, post-backfill duplicate audit; 0.5h CloudWatch alarm: DAG pause-state monitoring using Airflow metrics API polling (Airflow 2.8 dag.paused metric published to CloudWatch via MWAA); alert: any DAG in paused state for more than 7 days with catchup=True sends SNS notification to data engineering Slack channel; 0.75h Airflow DAG audit: reviewed all 23 DAGs in MWAA environment; identified 6 additional DAGs with catchup=True; 3 of these had no max_active_runs override and wrote to database tables without UNIQUE constraints; updated all 3: catchup=False + max_active_runs=1 + ON CONFLICT).

In a HourTab retainer log, the incident session reads: “daily_transaction_reconciliation backfill storm: Airflow 2.8.1 MWAA; catchup=True (default, not explicitly set); start_date April 1 2026; max_active_runs=16 (MWAA default, not overridden); DAG accidentally paused August 7 2026 by engineer toggling wrong UI element; new analyst unpauses October 8 2026; 62 backfill runs queued; 16 concurrent INSERT operations; reconciliation.daily_summary no UNIQUE(transaction_date) constraint; RDS PostgreSQL 15.4 max_connections=200 exhausted; October 9 2:00 AM production run fails (connection timeout); discovery October 9 6:47 AM dashboard FAILED; 847 duplicate rows (23 dates × 2-4 inserts from concurrent runs + connection-timeout retries); fix: pause DAG 7:15 AM; manual October 9 run 7:31 AM; DELETE 847 duplicate rows; ALTER TABLE ADD UNIQUE(transaction_date); DAG code: catchup=False + max_active_runs=1 + ON CONFLICT DO UPDATE; controlled backfill August 7–October 7 (one run at a time); completed 9:28 AM; 4.5h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Apache Airflow developer retainers

See also: Apache Flink developer retainer · Elasticsearch developer retainer · Databricks developer retainer