Project Name
Banking Data Warehouse With Full History and Zero Duplicates Using PowerCenter
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. If a customer’s address changed, a report for last quarter showed this quarter’s address, and if a load failed halfway and was run again, balances were counted twice. The bank needed 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.
Most of these problems failed quietly: the load finished “successfully,” and the numbers were still wrong.
- Lost Customer History: Customer details were overwritten on every load, so a report rerun for a past date showed today’s address, segment, and KYC status.
- Inconsistent Source Data: Names, phone numbers, and identity fields arrived in different formats from each system, so the same customer looked like two different people.
- Duplicates on Reruns: When a load failed partway and was run again, rows already loaded were loaded a second time.
- No Proof of Completeness: There was no check that row counts and amounts in the warehouse matched what the source sent.
- Slow Change Detection: Comparing 25 customer attributes column by column in every run was slow and easy to get wrong when a column was added.
- Environment-Bound Code: Connection names, file paths, and dates were hard-coded, so moving to production meant editing mappings.
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.
- Checksum-Based Change Detection: An MD5 checksum of tracked attributes is compared with the stored value through a cached Lookup, and a Router sends each row to the new, changed, or unchanged group.
- SCD Type 2 Customer History: A changed customer’s 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.
- Reusable Cleansing Mapplet: One mapplet trims and standardises names, validates phone and identity number formats, and applies default values. Rows that fail validation go to an error table instead of being dropped.
- Idempotent Reruns by Load Date: Staging tables are truncated before load, the fact session deletes rows for the current load date before inserting, and dimension updates compare against current rows.
- Nightly Reconciliation: A stored procedure compares loaded counts and amounts against the source control file and writes the result to a batch control table.
- 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.
- Banking-Specific Handling: A Normalizer turns up to 4 joint holders into one row each, SCD Type 3 keeps current and previous branch region, and exchange rates are looked up only for non-INR transactions. Marts provide a daily branch summary and a top customer ranking for relationship managers.
Technology Stack
| Category | Technology |
|---|---|
| Platform | Informatica PowerCenter |
| Orchestration | PowerCenter Workflow (Event Wait, Link Conditions) |
| Database | Oracle / SQL Server (Data Warehouse) |
| Integration | Core Banking and Card Extracts, Loan System Oracle Schema |
| Configuration | Parameter Files and Mapping Variables |
| Architecture | SCD Type 1, 2, 3 Dimensions, Fact, and Marts |
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: 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.
- 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.
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.
Are Your Bank’s Reports Still Built on Data That Changes With Every Reload?
