Python ETL bugs junior data engineers fix first
A data pipeline can run without errors and still be wrong. The numbers on tomorrow's dashboard double, or a few hundred rows move to the wrong day, and nobody notices until a report doesn't add up. This guide covers the bugs that come up most in a Python ETL job, how to spot each one, and how to fix it so a re-run can't make it worse. The examples come from Streamline, Kadra's nightly ETL practice codebase.
Make the load safe to run twice
Every pipeline gets re-run. A job fails halfway and retries, or someone backfills last Tuesday. If the load step appends rows, each re-run adds another copy of the day and every total goes up. A load that gives the same result no matter how many times it runs is called idempotent.
Streamline's DATA-501 ticket is this bug. The fix is to upsert on a natural key: a field that identifies the same record on every run, such as an event id from the source system. A matching row is replaced and a new row is added.
# Wrong: every run appends another copy
table.extend(new_rows)
# Right: replace on the natural key, add only what's new
index = {row["event_id"]: i for i, row in enumerate(table)}
for row in new_rows:
if row["event_id"] in index:
table[index[row["event_id"]]] = row
else:
index[row["event_id"]] = len(table)
table.append(row)In a database, the same idea is INSERT ... ON CONFLICT (event_id) DO UPDATE in Postgres, or MERGE in most warehouses. Either one needs a unique constraint on the key, because that constraint is what stops duplicates when two loads overlap.
To test it, load a day, load it again, and assert the totals didn't change. Then load half the rows, re-run with all of them, and check that the result matches a single clean run, and that other dates didn't move.
Parse timestamps as UTC, and bucket by the UTC date
Python's datetime has two traps. A timestamp without an offset parses as a naive datetime, and later code tends to treat it as local time. And calling astimezone() with no argument converts to the machine's local zone, not UTC.
Either one moves rows near midnight onto the wrong day. A row stamped 03:30 UTC on November 8 is still November 7 in Toronto, so a daily count run on a Toronto laptop puts it on the 7th. That's DATA-509 in Streamline.
from datetime import datetime, timezone
def parse_utc(ts: str) -> datetime:
parsed = datetime.fromisoformat(ts)
if parsed.tzinfo is None:
# The source sends UTC without an offset, so say so explicitly
return parsed.replace(tzinfo=timezone.utc)
return parsed.astimezone(timezone.utc)
def utc_day(ts: str) -> str:
return parse_utc(ts).date().isoformat()This bug hides on servers set to UTC, because local time and UTC are the same there. Your tests should set a different zone, such as America/Toronto, and include rows just before and after midnight and on a daylight saving change.
Don't let one bad row stop the whole batch
Upstream data is messy. A field goes missing or a number arrives as text. If the transform raises on the first bad row, the night's load fails and every good row waits for someone to wake up. That's DATA-514, the senior ticket in Streamline.
The usual fix is a dead-letter sink: the bad row is set aside with the reason it failed, the rest of the batch loads, and the run reports how many rows it skipped.
loaded, dead_letter = [], []
for row in rows:
if not is_valid(row):
dead_letter.append(row)
continue
loaded.append(transform(row))
summary = {"loaded": len(loaded), "skipped": len(dead_letter)}Only catch the failures you expect. A validation failure belongs in the dead-letter sink, while a database outage should still fail the job. And treat the skipped count as a signal. One bad row is noise, but half the batch means the source changed, and someone should be alerted.
Turn each acceptance criterion into a test
Each of these tickets lists what must be true when it's fixed, including what must not change, like rows for other dates staying put. Write a pytest test for each line. The ones about what stays the same are the regressions a reviewer looks for first.
Practice on a working pipeline
Streamline is a Python ETL job with the DATA tickets above planted in it. You get your own GitHub repo, fix each ticket on a branch and open a pull request. An AI teammate reviews the diff, and a hidden pytest suite decides whether the fix passes.
Every Streamline repo opens in GitHub Codespaces with Python 3.13 and pip installed, so there's nothing to set up on your own machine.
Questions people ask
What does idempotent mean in ETL?
Running the job twice for the same input leaves the same result as running it once. It matters because jobs get retried and backfilled, and a load that appends will double the data every time.
What is a dead-letter queue in a data pipeline?
A place where records that fail validation are stored with the reason they failed, so the rest of the batch can load. Someone can inspect them later, fix the source and replay them.
What is a good data engineering portfolio project?
One that shows you can keep a pipeline correct, not just build one. Fixing a re-run that double-counts or a timezone bug, with tests that prove it, gives you more to talk about in an interview than a notebook that loads a CSV.
Is Kadra free?
Yes, Kadra is free during beta with no credit card. After beta, Pro is planned at $12 a month or $79 a year, and the demo and your first full sprint stay free.
Try it on a real ticket
Pick a stack, fix a bug in a working codebase and get your pull request reviewed. Free during beta.
More guides
- How to get software engineering experience before your first job
- What your first code review at a software job looks like
- How to fix a bug in a codebase you didn't write
- What is a software engineering job simulator?
- Money bugs junior Java developers fix in banking code
- Common bugs in a Node.js CMS backend, and how to fix them
- Payment bugs Python developers fix in a FastAPI service
- Go REST API bugs junior developers fix first
- Booking bugs C# developers fix in ASP.NET Core
- Checkout bugs developers fix in a Next.js store
- Realtime chat bugs developers fix in TypeScript