Add a Dead-Letter File to an ETL Pipeline That Drops Records

A bare except: pass makes bad rows vanish without a trace, and nobody notices until a report has gaps. Log each failure and write the failed rows to a dead-letter file, so every input row ends up either loaded or recorded with its reason.

Get started - freeMid and Senior open as you complete Junior projects
Data Engineerpythonetlerror-handling

The silent drop

for row in reader:
    try:
        out.append(clean(row))
    except:
        pass
print(f"ETL complete: {len(out)} records processed")

The summary says 7 records processed. It does not say 10 went in. Three rows with a bad email, a bad date and a bad amount are gone, and nothing records why.

Every row goes somewhere

failed = []
for row in reader:
    try:
        out.append(clean(row))
    except Exception as e:
        print(f"row id={row['id']} failed: {e}")
        failed.append({**row, "error": str(e)})

write_csv(DEAD_LETTER_FILE, failed)
print(f"total={total} processed={len(out)} failed={len(failed)}")

Dead-letter queues in real pipelines

The same idea appears everywhere: SQS and Kafka have dead-letter queues, Airflow tasks write reject tables, dbt tests quarantine bad rows. The rule is the same - never lose data silently, and make failures visible and replayable. Add an alert when the failure rate crosses a threshold.

What you do in the project

You replace the bare except in a Python ETL, write the failed rows to a dead-letter CSV with their errors, and confirm 7 rows load and 3 are recorded.

Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.

Get started - free

What you'll practice

FAQ

What is a dead-letter queue?

A dead-letter queue (or file or table) stores records a pipeline could not process, together with the reason. It keeps bad data out of the target without losing it, so it can be inspected, corrected and replayed.

Why is except pass bad in Python?

It hides every error, including bugs and interrupts, so failures happen silently. Catch the specific exceptions you expect and record or re-raise them.

How do I know if my pipeline is losing data?

Reconcile counts on every run - rows read must equal rows loaded plus rows rejected - and alert when they do not match or when the reject rate rises.

Keep learning

How to Handle Bad CSV Data in PythonData projectValidate Data Before Loading ItData projectData Quality Monitoring With PandasData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.

Get started - free
Get started - free