Automated SQL ETL Pipeline

The question

I built this project around a bank transaction dataset with more than 116,000 records. The focus was preparing the data in PostgreSQL and organising the steps into a scheduled workflow.

The existing project covers cleaning, deduplication, audit logging and scheduling with pgAgent. It is a portfolio implementation, not a claim that I operate a live banking data system.

The workflow

1. 01 / INPUTRaw transactions
2. 02 / PREPAREClean and standardise
3. 03 / LOADDeduplicate and log
4. 04 / SCHEDULERun with pgAgent

The project cover records 116,201 source rows. The workflow diagram above summarises the described process; it is not an execution trace.

Cleaning without losing meaning

The source problems described in the project include scientific notation in account identifiers, inconsistent currency formatting and mixed date formats. Each requires a clear rule for acceptable input and a way to review records that do not fit.

Account identifiers need particular care. Formatting an identifier differently cannot restore digits already lost through numeric conversion. I would preserve the raw value and reconcile it with the source before treating a normalised value as correct.

Deduplication and traceability

The project uses SQL for duplicate handling and audit logging. A deduplication rule needs to distinguish a repeated record from a legitimate transaction with similar values. A useful audit record should make the result of each run understandable.

To demonstrate repeatability, the same input should be loaded more than once and the resulting counts compared. Failure handling also needs its own test. Those checks should be supported by SQL and execution logs, rather than inferred from a successful scheduled run.

Scheduling is one part of reliability

pgAgent is a separate agent for scheduling PostgreSQL jobs. Its availability, permissions and job configuration all matter. Scheduling the workflow does not by itself guarantee continuous service or remove the need to monitor failures.

What I take from this project

This work extends my focus from an individual query to a sequence of transformations. The next step is to make the implementation easier to inspect through source SQL, run logs and documented recovery behaviour.