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.
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
- Required fields are present.
- Numbers are numbers, inside sensible ranges.
- Dates parse in one agreed format.
- Keys are unique and references exist.
- Values come from an allowed set (status, country code).
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 - freeWhat you'll practice
- Writing row-level validation rules for required fields, ranges and dates
- Rejecting bad rows before they reach the database
- Recording rejected rows with a reason in an errors file
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
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