How to Validate Data Before Loading It Into a Database (Python ETL)

A loader that inserts every CSV row as-is lets empty names, negative amounts and malformed dates into the table, and one bad date can crash the whole load. Check each row first and send the bad ones to an errors file with a reason.

Start this project - freeLive cloud workspace in your browser · lands on your portfolio
Data Engineerpythonpostgresqletl

Why validate before the INSERT

Once bad data is in a table, every report that reads it is wrong, and finding and deleting it later is much harder than stopping it at the door. Validation at load time is the cheapest data quality check there is.

Check each row against simple rules

from datetime import datetime

REQUIRED = ["order_id", "customer_name", "product", "amount", "sale_date"]

def problems(row):
    for col in REQUIRED:
        if not (row.get(col) or "").strip():
            return f"missing {col}"
    if float(row["amount"]) < 0:
        return "negative amount"
    try:
        datetime.strptime(row["sale_date"], "%Y-%m-%d")
    except ValueError:
        return "bad date"
    return None

Rows with no problems are inserted; the rest go to errors.csv with the reason, using csv.DictWriter and an extra error column.

Rules worth adding in real pipelines

Libraries such as pandera and Great Expectations, and dbt tests, express the same rules declaratively once a pipeline grows.

What you do in the project

You add validation to a Python loader for a 12-row sales CSV, load the 7 valid rows into Postgres, and write the rejected rows with their reasons.

Do this project yourself. It's ready in a live cloud workspace in your browser - when you finish, it lands on your portfolio.

Start this project - free

What you'll practice

FAQ

What is data validation in ETL?

Data validation checks each record against rules (required fields, types, ranges, formats, uniqueness) before it is loaded, so bad data is rejected or quarantined instead of reaching the target tables.

How do I validate a date string in Python?

Use datetime.strptime(value, "%Y-%m-%d"). It raises ValueError when the string does not match the format or is not a real date.

Should I reject bad rows or stop the whole load?

For most batch loads, reject and record the bad rows and load the rest, then alert if the reject count is high. Stop the load when bad data means the whole file is untrustworthy.

Keep learning

How to Handle Bad CSV Data in PythonData projectHow to Validate DataFrames With panderaData projectETL Error Handling With a Dead-Letter FileData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

Do this project yourself. It's ready in a live cloud workspace in your browser - when you finish, it lands on your portfolio.

Start this project - free
Start this project - free