Built an Automated SQL ETL Pipeline — 100% PostgreSQL, No Python
Writing a SQL query is one thing. Building a process that runs reliably without you is another — here's the full breakdown of a scheduled, self-auditing pipeline for 116,000+ real bank records.
Shaharier Shourov
Data Analyst & Aspiring Data Engineer
Why most ETL projects stop short
Most portfolio ETL projects follow the same pattern: write a script, run it once, screenshot the output, done. That proves you can transform data — it doesn't prove you can operate a data system. The gap between those two things is where most of the real learning happens.
I wanted to close that gap with a project that stayed running after I stopped touching it.
"Writing a SQL query is one thing. Building a process that runs reliably without you is another."
The dataset
I used a real-world bank transaction dataset with over 116,000 records. It was messy in exactly the ways production data usually is — account numbers stored in scientific notation, currency values in inconsistent formats, and transaction dates mixed across several date formats in the same column.
Staying 100% inside PostgreSQL
Most tutorials reach for Python and Airflow by default. I deliberately avoided both. Every transformation — cleaning, deduplication, business logic, and scheduling — was written as native PostgreSQL functions and stored procedures, orchestrated entirely by pgAgent, PostgreSQL's built-in job scheduler.
- Cleaning: normalised account numbers out of scientific notation, standardised currency formatting, and parsed inconsistent date strings into proper timestamp types
- Deduplication: identified and removed duplicate transaction records using window functions
- Audit logging: every pipeline run writes a row to an audit table — timestamp, rows processed, rows rejected, and run status
- Scheduling: pgAgent triggers the entire pipeline automatically, with zero manual intervention
What this taught me
The shift in mindset was the real outcome. I stopped thinking about "a query that answers a question" and started thinking about "a system that keeps answering the question, automatically, forever." That means idempotent runs (running it twice doesn't create duplicate data), graceful error handling, and a paper trail for every execution.
What's next
This project is the anchor for the SQL side of my data engineering portfolio. Next, I'm extending my toolkit into Python for the pieces SQL can't reach cleanly — API ingestion and more flexible orchestration with Airflow.
Full technical write-up and case study available in my portfolio.
