Green DAG for three days while partitions were missing
Atlas Media runs a Cloud Composer 2 environment with a single hourly DAG. The DAG has four tasks: extract from a Cloud SQL Postgres replica to Cloud Storage using a Python operator, load into a partitioned BigQuery table, run a SQL transformation that overwrites a daily aggregate, and send a Slack message. On Friday a library upgrade changed an exception class; the extract task caught the exception, logged a warning, and returned success. For three days the DAG reported green while 72 hourly partitions were missing. The transformation kept overwriting the daily aggregate with partial data. The BigQuery table is 2 TB, partitioned by hour, and downstream finance reports read it. The team needs to backfill and prevent recurrence. They have 200 Composer worker slots and a 500-slot BigQuery reservation. The DAG has catchup=False and depends_on_past=False.
1. Why did monitoring miss the failure, and what changes would make the pipeline fail loudly next time?
Read the worked answer
The Python operator caught a broad exception and returned success, so Airflow marked the task green. Airflow monitors task state, not data correctness. The fix is to remove the broad catch and let the exception fail the task. Add on_failure_callback and an SLA alert, but do not stop there. Add data-quality gates: row count greater than zero, freshness within two hours, and a check that the expected hourly partition exists. Run these as separate tasks so a missing partition fails the DAG.
Also alert on absence of data, not only on task failure. A Cloud Monitoring alert on the DAG's successful run count or a daily completeness query against INFORMATION_SCHEMA.PARTITIONS would have caught the three-day gap. The judgement is that a green DAG is not evidence of correct data; the pipeline needs both execution alerts and data assertions.
2. Design the backfill. What concurrency and idempotency controls matter?
Read the worked answer
Backfill only the 72 missing hours first, then recompute the affected daily aggregates after all hours exist. Use the Airflow CLI backfill or a manually triggered DAG run with an explicit date range, because catchup=False means the scheduler will not do it automatically. Keep the same code path as production so the backfill is tested by the same logic.
Idempotency is critical. The load should write to the correct hour partition and use MERGE or partition overwrite, not append. The daily aggregate should be recomputed from complete hourly data, not incrementally patched from partial runs. Limit concurrency with max_active_runs and a pool so 72 runs do not overwhelm the Cloud SQL replica or exhaust Composer workers; start with 4 to 8 parallel runs. Validate row counts per hour before publishing the aggregate. The tradeoff is speed versus load; a controlled backfill is safer than maximum parallelism.
3. How would you prevent the daily aggregate from being overwritten with partial data in the future?
Read the worked answer
Add a completeness dependency before the aggregate task. A sensor or SQL check should confirm that all 24 hourly partitions for the target date exist and passed quality checks. If not, the aggregate task should skip or fail, not overwrite. Build the new aggregate in a staging table and swap it atomically with CREATE OR REPLACE TABLE only after validation. Alternatively, use a MERGE that is driven by a complete date and refuses to run on incomplete input.
The judgement is to separate data arrival from data publishing. The DAG can still run hourly, but the daily aggregate is a publish step gated by completeness. Add a visual indicator or control table so finance knows which dates are certified. This avoids the failure mode where the pipeline is technically running but the published number is wrong.