DAG succeeded but the data is wrong

A green Airflow status indicates only that every task process exited with return code 0. It does not verify that the data processed was complete, accurate, or semantically valid.

Why pipelines fail silently

Several common issues cause green runs to deliver bad data:

• Partial upstream data: The pipeline extracted from an upstream database while an ingestion job was still writing. The extraction task ran without errors, but extracted only 20% of yesterday's transactions.
• Non-deterministic date filtering: The SQL query used WHERE order_date = CURRENT_DATE - 1 instead of parameterized data interval macros. If the DAG ran 30 minutes past midnight due to scheduler queuing, CURRENT_DATE - 1 evaluated to the wrong day, processing the wrong records.
• Silent inner join drops: Upstream engineering added a new category code that was missing in the reference dimension table. An inner join silently discarded 40% of the fact records without throwing a database error.

-- Dangerous: Silent row loss on unmatched foreign keys
SELECT f.*, d.category_name
FROM raw_orders f
INNER JOIN dim_categories d ON f.category_id = d.category_id;

Investigating the discrepancy

When the business reports incorrect numbers:

First, check the task logs to inspect how many rows were extracted and inserted. If the operator does not log row counts, run a quick query in the data warehouse comparing row counts for that partition against the rolling 30-day average.

Second, inspect the SQL queries executed during that specific run. Verify whether timestamp filters were bounded by data_interval_start and data_interval_end.

Third, check whether upstream source tables had late-arriving records that landed after the extraction finished.

Prevention and guardrails

To make sure green runs reflect genuine data health:

• Implement data quality gates as blocking tasks in the DAG using tools like Great Expectations, Soda, or custom SQL check operators.
• Validate critical metrics before writing to production tables: assert that row count is within expected variance, primary keys are unique, and null rates on foreign keys are zero.
• If a data quality assertion fails, let the task fail with a non-zero exit code so Airflow turns red and alerts the engineering team before business users consume the corrupted partition.