bileyroy opened a new issue, #69872:
URL: https://github.com/apache/airflow/issues/69872
### Under which category would you file this issue?
Airflow Core
### Apache Airflow version
3.3.0
### What happened and how to reproduce it?
`airflow db clean` silently skips `dag_run` records where `start_date IS
NULL`. These records accumulate indefinitely in the metadata database and can
never be cleaned by any number of `airflow db clean` runs.
This affects **any dag_run that is marked failed before execution begins**
.
In our production environment, this resulted in **11,841+ orphaned dag_run
records** that were permanently invisible to the cleanup command.
### Root cause
In `airflow-core/src/airflow/utils/db_cleanup.py`, the `_build_query()`
function builds the cleanup query with:
```python
conditions = [base_table_recency_col < clean_before_timestamp]
```
Ref:
https://github.com/apache/airflow/blob/main/airflow-core/src/airflow/utils/db_cleanup.py#L424
For `dag_run`, `recency_column` is `start_date`. When `start_date IS NULL`:
- `NULL < '2026-07-15'` evaluates to `NULL` in SQL (not `TRUE`)
- The row is silently excluded from the result set
- The row can **never** be deleted by `airflow db clean`
### How to reproduce
**1. Create a simple test DAG with `max_active_runs=1`:**
```python
from airflow.sdk import DAG, task
from datetime import datetime, timedelta
import time
with DAG(
dag_id="test.null_start_date_repro",
schedule=None,
start_date=datetime(1970, 1, 1),
catchup=False,
max_active_runs=1,
) as dag:
@task
def wait_2_minutes():
time.sleep(120)
wait_2_minutes()
```
<img width="1307" height="683" alt="Image"
src="https://github.com/user-attachments/assets/9eac2007-82dd-4aa7-9f15-093e6881ef9d";
/>
**2. Trigger two runs:**
- Trigger run 1 — it starts executing (sleeps 2 min), `start_date` is set
- Trigger run 2 while run 1 is still running — it enters `queued` state
with `start_date = NULL` (blocked by `max_active_runs=1`)
- Mark run 2 as `failed` via the UI while it's still queued
**3. Verify the state in the database:**
```sql
SELECT id, run_id, start_date, state, created_at
FROM dag_run
WHERE dag_id = 'test.null_start_date_repro'
ORDER BY created_at;
```
<img width="1225" height="487" alt="Image"
src="https://github.com/user-attachments/assets/9dff31fd-71f3-45a2-8dab-946b0c54fa5c";
/>
Result:
| id | run_id | start_date | state | created_at |
|----|--------|-----------|-------|------------|
| 4334 | manual__...40.338513 | 2026-07-14 12:03:41 | success | 2026-07-14
12:03:40 |
| 4335 | manual__...45.960859 | **NULL** | failed | 2026-07-14 12:03:45 |
**4. Run `airflow db clean` dry-run (with a future timestamp to ensure all
records are eligible):**
```bash
airflow db clean \
--clean-before-timestamp '2026-07-15 00:00:00+00:00' \
--tables 'dag_run' \
--dag-ids 'test.null_start_date_repro' \
--dry-run \
--verbose \
--yes
```
Output:
```
Performing dry run for table dag_run
Checking table dag_run
Found 1 rows meeting deletion criteria.
```
**Expected:** 2 rows found (both dag_runs)
**Actual:** Only 1 row found — the `NULL start_date` record (id=4335) is
invisible
### Impact
- NULL `start_date` records **accumulate indefinitely** — no number of
cleanup runs will remove them
- In our production DB: **11,841 orphaned records** from failed backfills
(logical_date ranging from 1994 to 2026)
### What you think should happen instead?
`airflow db clean` should be able to clean `dag_run` records regardless of
whether `start_date` is NULL or not.
One approach to solve this:
- When `start_date` is NULL, the cleanup should fall back to `created_at`
- This should be opt-in via a CLI flag (e.g. `--fallback-on-null`)
### Operating System
GKE / Linux (Debian-based Airflow container)
### Deployment
Official Apache Airflow Helm Chart
### Apache Airflow Provider(s)
_No response_
### Versions of Apache Airflow Providers
_No response_
### Official Helm Chart version
Not Applicable
### Kubernetes Version
_No response_
### Helm Chart configuration
_No response_
### Docker Image customizations
_No response_
### Anything else?
_No response_
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
