Automated SQL ETL Pipeline
100% PostgreSQL, no Python, no Airflow — a production-style ETL pipeline processing 116,000+ real bank transaction records with cleaning, deduplication, audit logging, and scheduled automation.
Dataset
Bank transactions
Records
116,000+
Tools
PostgreSQL, pgAgent
Automation
100% SQL
116K+
Transaction records processed
100%
PostgreSQL — no Python
0
Manual runs required
24/7
pgAgent-scheduled automation
The problem
One thing I learned transitioning into data engineering: writing a SQL query is one thing, building a process that runs reliably without you is another. Most portfolio projects stop at "here's a clean query that answers a question." This one goes further — it builds a pipeline that keeps running.
The source data was a real-world bank transaction dataset with over 116,000 records, and it was messy in exactly the ways production data usually is: scientific-notation account numbers, inconsistent currency formatting, and dates in mixed formats.
"Building a process that runs reliably without you is a different skill entirely from writing a query."
The pgAgent job scheduler running the pipeline automatically — no manual intervention, no Python, no Airflow.
What the pipeline does
Data cleaning
Fixes scientific-notation account numbers, normalises currency formatting, and standardises inconsistent date formats — all in native SQL.
Deduplication & audit log
Removes duplicate transactions and writes every pipeline run to an audit log table — so every load is traceable after the fact.
Scheduled automation
pgAgent runs the entire pipeline on a schedule — no Python scripts, no Airflow DAGs, no manual triggers.
Why 100% SQL matters
Most ETL tutorials reach for Python and Airflow by default. This project deliberately stayed inside PostgreSQL to prove that a large share of production-style ETL work — cleaning, transformation, deduplication, logging, and scheduling — can be handled entirely within the database engine using stored procedures, functions, and the built-in pgAgent job scheduler.
That's a meaningful distinction for smaller teams or single-database environments where standing up a separate orchestration layer isn't worth the operational overhead. It also forced a much deeper understanding of what SQL alone is capable of before reaching for external tools.
What this project pushed me to learn
Beyond individual queries, this project was about building a reliable, production-style data workflow: idempotent runs, proper error handling, an audit trail for every execution, and a scheduler that doesn't need a human to babysit it. That shift in thinking — from "write a query" to "build a system" — is the core skill this project demonstrates.
