Banking Data Warehouse With Full History and Zero Duplicates Using PowerCenter

Overview

A bank whose reports changed every time the warehouse reloaded

Our client is a bank that runs regulatory returns, branch MIS, and relationship manager reports from a central data warehouse, not from its operational systems. The warehouse brings together data from the bank’s core banking, loan, and card systems, and its reports are only as trustworthy as the load behind them.

Reporting ran directly on extracts that were overwritten every night. When a customer’s address changed, last quarter’s report showed this quarter’s address. When a load failed halfway and was run again, balances were counted twice. The bank partnered with Ksolves to build a nightly load that keeps the full history of customer changes and proves every night that what was loaded matches what the source sent, without writing anything back to the source systems.

Challenge

Six gaps that made the warehouse numbers unreliable

Solution

One nightly workflow that keeps history, reruns safely, and checks itself

Ksolves, an AI-first Big Data services company, built a nightly batch load on Informatica PowerCenter that moves data from 3 source systems through staging, dimensions and facts, and summary marts in 1 workflow. Each layer loads only after the previous one succeeds, the source systems stay read-only, and every failure path was tested before sign-off. Each fix below answers the challenge with the same number.

1. 01

   SCD Type 2 customer history

   When a customer changes, the current version is closed with an end date of the day before the load date and a new version is inserted with a new surrogate key. Each transaction links to the customer version that was current on its transaction date, so past reports stay as they were.

2. 02

   Reusable cleansing mapplet

   One mapplet trims and standardises names, validates phone and identity number formats, and applies default values the same way for every source. Rows that fail validation go to an error table for review instead of being silently dropped.

3. 03

   Idempotent reruns by load date

   Staging tables are truncated before each load, the fact session deletes rows for the current load date before inserting, and dimension updates compare against current rows. A rerun replaces the night’s data instead of adding to it.

4. 04

   Nightly reconciliation

   A stored procedure compares loaded row counts and amounts against the source control file and writes the result to a batch control table, so every night has a recorded pass or fail.

5. 05

   Checksum-based change detection

   Instead of comparing 25 columns one by one, an MD5 checksum of the tracked attributes is compared with the stored value through a cached Lookup. A Router sends each row to the new, changed, or unchanged group, and adding a column only means adding it to the checksum.

6. 06

   Parameter-driven environments

   Database connections, file paths, and the load date come from one parameter file per environment. The incremental loan extract moves its timestamp forward only after a successful run, so a failed night is picked up again in full.

7. 07

   Banking-specific handling

   A Normalizer turns up to 4 joint holders on an account into one row each. SCD Type 3 keeps the current and previous branch region side by side, and exchange rates are looked up only for non-INR transactions.

Technology stack

Delivery

How a night’s data moves from source to report

Ksolves handed over a production-ready nightly load on Informatica PowerCenter, covering all three source systems in one workflow, from staging through dimensions, facts, and summary marts. The bank received the full set of mappings, the reusable cleansing mapplet, the reconciliation procedure, and one parameter file each for development, test, and production. Every failure path was tested and signed off before go-live, so the reporting team took over a load they can rerun, audit, and extend to a new core banking system without changing the code.

Results

From overwritten extracts to reports that hold for any past date

The reporting team moved from numbers that drifted with every reload to a warehouse that can show what any report looked like on any past date.

• 1 Current Row per Customer, Full History Kept

  Every customer change is kept as a new version with effective dates, leaving exactly 1 current row, so any report can be rerun as of any past date and give the same answer.

• 0 Duplicate Rows on Reruns

  Running the same night twice gives the same row counts and totals as running it once.

• 1 Reconciliation Record Every Night

  Every run is checked against source control totals and recorded in the batch control table as reconciled or not.

• 0 Code Changes Between Environments

  The same code moves from development to test to production using 1 parameter file per environment.

• 9 Failure Paths Tested at Sign-Off

  Deliberate tests confirm that changes, reruns, partial failures, invalid data, and mismatched totals are all handled.

Client testimonial

What the bank’s data team says

Ksolves replaced overwritten nightly extracts with a history-preserving data warehouse load on Informatica PowerCenter. Every customer change is kept as a new version, and every transaction links to the version that applied at the time, so last month’s reports stay unchanged. Failed nights can be rerun without duplicates, every run ends with a recorded reconciliation, and the same code runs in every environment. Connecting a different core banking system only requires mapping its extract to the same staging layout.

Team

Meet the Engineers on Your Project

Every engagement is staffed by senior engineers from our Big Data team, who will design, build, and support your platform.

Anil Kushwaha

Technology Head

Karthik Hegde

Technical Lead

Rahul Prakash Surti

Senior Software Engineer