Overview
This project started with an observation: too much time went into moving data around, and not enough into understanding it. Every week followed the same pattern. Export data, clean it by hand, update spreadsheets, fix inconsistencies, and finally build reports. It worked, but it did not scale, and it was not reliable.
The first goal was to build a pipeline that runs in the background and keeps data clean, updated, and ready to use. The second goal came once the pipeline was live. Pipelines do not fail loudly. A missing column, a handful of null values, a delayed load that no one notices, and reports go out on bad data. Automating the flow was only half the work. The other half was making the data flowing through it verifiable.
Architecture
At a high level the flow is straightforward:
- Data is collected from multiple sources (CSV files, APIs)
- A Python-based ETL pipeline processes and cleans the data
- Cleaned data is stored in PostgreSQL
- A dashboard connects directly to the database for live reporting
- Scheduled jobs run everything without manual triggers
The monitoring layer sits on top of that same pipeline rather than replacing any of it:
- Validation checks run after each ETL stage
- Results are logged into monitoring tables
- Alerts are triggered when thresholds are breached
- A separate dashboard shows data health status
The focus throughout was a predictable, repeatable, trustworthy data flow, and a way to see when that flow was off.
Building the pipeline
The ETL pipeline was modular and debuggable. Each step had one responsibility: extract loads raw data, transform cleans and standardizes it, and load writes it into structured tables.
import pandas as pd
import psycopg2
def extract_data(path):
return pd.read_csv(path)
def transform_data(df):
df.columns = df.columns.str.lower().str.strip()
df = df.dropna()
return df
def load_data(df, connection):
cursor = connection.cursor()
for _, row in df.iterrows():
cursor.execute(
"INSERT INTO sales (date, product, revenue) VALUES (%s, %s, %s)",
(row['date'], row['product'], row['revenue'])
)
connection.commit()
# Pipeline execution
df = extract_data("data/sales.csv")
df_clean = transform_data(df)
conn = psycopg2.connect("dbname=mydb user=user password=pass")
load_data(df_clean, conn)
I kept transformations transparent and SQL-friendly. That made debugging faster and kept the data model clean enough to reason about.
Adding quality monitoring
Once the pipeline ran on a schedule, “mostly correct” was not good enough, since the data now drove decisions directly. The monitoring layer runs three kinds of checks:
- Completeness: are there missing values?
- Freshness: is the data up to date?
- Validity: does the data follow expected rules?
Each check is modular and can be extended without touching the pipeline itself.
import pandas as pd
from datetime import datetime
def check_nulls(df):
return df.isnull().sum().sum()
def check_freshness(df, date_column):
latest_date = pd.to_datetime(df[date_column]).max()
return (datetime.now() - latest_date).days
def validate_data(df):
issues = {}
null_count = check_nulls(df)
if null_count > 0:
issues['nulls'] = null_count
freshness_gap = check_freshness(df, 'date')
if freshness_gap > 1:
issues['stale_data'] = freshness_gap
return issues
# Example usage
df = pd.read_csv("data/sales.csv")
issues = validate_data(df)
if issues:
print("Data issues detected:", issues)
The checks are lightweight and fast so they do not slow the pipeline. When a check fails, the result is logged to a monitoring table, and alerts can route to email, Slack, or logging systems depending on requirements.
Results
- Reporting time reduced from roughly 4 to 6 hours to under 10 minutes
- Manual errors eliminated through validation checks in the transform step
- Data freshness improved from weekly to near real-time
- Stakeholders gained self-serve access through the dashboard
- Data issues detected before they reached reports, not after
- Debugging time reduced because failures are logged where the pipeline can point to them
Where it stands
The pipeline moved the team from collecting data to using it, and the monitoring layer kept that data verifiable as the volume grew. The two pieces are one system: automate the flow, then check the flow. With both in place, a broken load shows up on the health dashboard the same day it happens, and reports stop being a weekly manual event.