Airflow

A daily Airflow DAG extracts orders, transforms them, and loads to Snowflake. Last night the load task ran a multi-row INSERT, inserted 18,420 rows successfully, then died on the final COMMIT because the warehouse momentarily went unavailable. Airflow marked the task failed and retried it once. The retry ran the same INSERT cleanly. The warehouse now has 18,420 duplicate orders. Walk through what's wrong with the task design, how you'd fix it, and the broader rule this scenario is asking you to internalize.

Team A owns a daily ingestion DAG: pulls from a partner SFTP, lands raw files into S3, writes a daily fact table. Typical finish: 08:15, but the SFTP latency varies — finishes range from 07:40 to 11:30. Team B owns a downstream DAG that reads Team A's fact table. Team B currently schedules at 09:00 ("after A is usually done"). About once a month, A runs late and B runs on yesterday's data without realizing. The analytics team finally noticed. Walk through how you'd redesign this cross-DAG dependency.

Your team runs Airflow with the CeleryExecutor on 4 worker pods, 8 task slots each = 32 concurrent tasks. Mostly fine. But once a day, the analytics team's monthly_rollup DAG runs a single PythonOperator that loads a 12GB pandas DataFrame into memory. While it runs, 8 other tasks on the same worker fail with timeouts or get killed by the OOM-killer. The analytics team says "it's just a big rollup, deal with it." How do you isolate this without asking them to rewrite the task?

Product fixed a long-standing bug in the upstream events API. You now need to backfill 18 months of a daily events_to_warehouse DAG. Constraints: (a) the partner API allows 120 requests/minute, hard ceiling; (b) the warehouse COPY job for one day's data uses 3 warehouse credits and 12 minutes; (c) some of those 18 months overlap with other DAGs that depend on this table; (d) the production DAG is still running daily — you can't pause it. You have ~10 days to complete the backfill before the next quarterly review. Design the backfill.

Production Airflow has grown organically to 1,000+ DAGs. Lately: the scheduler heartbeat is irregular (target 5s, sometimes 30+s), DAGs are starting 5–8 minutes late, and the scheduler pod's CPU is pinned. The cluster is on Airflow 2.7 with CeleryExecutor, 1 scheduler pod, 8 vCPU / 16GB RAM. Workers have free slots — they're hungry; the scheduler isn't feeding them. Walk through your diagnostic process, the most likely root causes, and the path back to a healthy scheduler.