Moving a bank’s ETL to Azure Data Factory

Context

A large Caribbean bank. Its data warehouse fed reporting and analytics across the bank, and I led the data warehouse team inside the Digital Transformation Unit.

It was the largest data volume of my career: daily account snapshots of about 1.5 million rows a day, kept for 7 years, and 10 years of bank-wide transactions at 500,000 to 999,000+ a day.

Problem

Warehouse loads ran on Pentaho Data Integration and took too long to finish inside the nightly window.

Constraints

• Banking data and its controls.
• Sources in Oracle, SQL Server and DB2.
• Nightly batch windows that reports depended on each morning.
• Delivery in Agile sprints alongside other work.

Architecture

The diagram, step by step

1. Sources: Oracle, SQL Server, DB2 and files.
2. Azure Data Factory pipelines move the data.
3. Staging in the warehouse.
4. SQL and PL/SQL transformations.
5. Star-schema models.
6. Tableau and BI Publisher reports.

The warehouse itself moved from on-premises Oracle to Oracle Cloud.

Key decisions

• ELT instead of ETL. Data Factory moved the data and the warehouse did the transformations, where the SQL and PL/SQL already lived and ran fastest.
• Migrate in sprint-sized batches. With the scrum master and product owners, the 400 processes became epics and sprint tasks, so each batch went live and was checked before the next.
• Automate the checks. Validation and profiling scripts compared each migrated load with the old one, which also cut manual testing by 40%.

Outcome

400 processes migrated and load times down 50%.

My role

Technical lead and manager of the data warehouse team, with six people reporting to me. I designed the ELT pattern, the migration plan and the validation.

What I can’t share

The bank’s name, table names and internal systems.