Skip to content
All case studies
Payments technology provider (now part of IBM Canada) · Banking & Payments

Automating Bank and Credit Union Onboarding with AWS Lambda and Python

Onboarding new banks and credit unions meant analysts processing Excel and JSON files by hand. I replaced it with a serverless AWS pipeline that cleans, validates and logs every file automatically.

Client
Payments technology provider (now part of IBM Canada)
Industry
Banking & Payments
My role
Systems Integration Developer
Duration
6 months
Onboarding data intake that analysts used to process by hand
Automated
Logged to S3 for tracing and audit
Every run
Pay only when onboarding is happening
Serverless
Spreadsheet pages flowing into a cloud and coming out as neat, organized data blocks

Onboarding a new bank or credit union onto a payments platform involves a lot of data: institution details, configuration settings, account structures and more. At this company, much of that data arrived in Excel and JSON files, and analysts processed it by hand.

Manual processing works when volumes are small. It starts to hurt as you grow: every onboarding takes analyst time, every manual step is a chance for a typo, and when something goes wrong it's hard to trace what happened.

My job, as the systems integration developer, was to automate it.

An analyst's desk with two monitors showing large spreadsheets, a stack of printed forms and a cup of coffee, late afternoon light

The old way and the new way

StepBeforeAfter
Receiving the dataFiles sent in and saved by handFiles land in a cloud storage bucket (Amazon S3)
Reading the filesAn analyst opens each Excel or JSON fileAutomatically read as soon as they arrive
Cleaning and checkingBy eye, row by rowAutomated cleaning and validation rules
Loading into the systemCopied in manuallyLoaded automatically
Tracing problemsMemory and email threadsA stored log of every run

How it works

I built the automation on AWS as a set of serverless functions (AWS Lambda) with a data-cleaning pipeline written in Python using Pandas.

  1. Files arrive in S3. The onboarding files are dropped into a storage bucket instead of being handled by hand.
  2. A Lambda function picks them up. Each new file triggers processing automatically. There are no servers to keep running when nothing is happening.
  3. Pandas cleans and validates the data. Inconsistent formats, missing values and obvious errors are caught by rules, the same way every time.
  4. The clean data is loaded into the onboarding process.
  5. Every run is logged to S3, so if something goes wrong, there's a record of exactly what was processed and what happened.

A clean flat illustration of documents flowing from a folder into a cloud, through a filter, and into a database, with a small logbook beside it

Here's a simplified example of the kind of validation step the pipeline runs. It's not the production code, but it shows the idea: rules that used to live in an analyst's head now live in code that runs the same way every time.

import pandas as pd
 
REQUIRED = ["institution_id", "institution_name", "routing_number"]
 
def clean(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    df = df.rename(columns=lambda c: c.strip().lower().replace(" ", "_"))
    df["institution_name"] = df["institution_name"].str.strip()
    df["routing_number"] = df["routing_number"].astype(str).str.zfill(9)
 
    problems = df[df[REQUIRED].isna().any(axis=1)]
    return df.drop(problems.index), problems  # clean rows, rows to review

Rows that fail the rules aren't silently dropped. They're set aside for an analyst to review, which keeps people involved exactly where judgment is needed.

Why serverless was the right fit

Onboarding happens in bursts, not around the clock. Serverless functions only run (and only cost money) when there's work to do. There's no server to patch, scale or monitor during quiet periods, which suited a process that might be busy one week and idle the next.

What changed

  • Analysts stopped doing data entry. Their time went to reviewing exceptions instead of copying rows.
  • Consistency. The same rules apply to every file, every time.
  • Traceability. Every run leaves a log in S3, so problems can be investigated instead of guessed at.
  • Low running cost. Serverless meant paying for processing only when onboarding was actually happening.

An analyst calmly reviewing a short list of highlighted items on a laptop in a bright office

The broader lesson

Many businesses have a process like this: important, repetitive, file-based, and done by hand because "it's always been done that way." These are often the best first candidates for automation. They're well understood, the rules already exist in people's heads, and the payoff shows up right away.

If your team spends hours moving data between spreadsheets and systems, I'm happy to look at whether it can be automated, and give you an honest answer if it can't.

Tech stack

  • AWS Lambda
  • Amazon S3
  • Python
  • Pandas

Client details are shared with permission or anonymised, and some specifics are changed to protect confidential information. Results are from this project and aren't a guarantee for others. Views are my own. Full disclaimer

Have a similar challenge?

Tell me about your project and I'll come back with how I'd approach it.

More case studies