CASE STUDY
Oracle 19c to Snowflake for investment banking payments
How an investment banking payments team migrated from Oracle 19c to a modern cloud data platform, standardized definitions, and delivered trusted reporting for operations and controls.
Objective
Replace an Oracle 19c reporting and analytics estate with Snowflake, modernise transformations in dbt, and deliver a governed Power BI layer for payments operations and control teams.
Primary solutions used
AI accelerated migrations
BI modernization
Data enablement
Starting point
Oracle 19c hosted core payment reporting tables and historical extracts. Transformations were split across stored procedures, scheduled jobs, and spreadsheet logic. Reporting logic varied across teams and definitions were not consistent across payment types.
Key challenges
High volume transaction and event data with reconciliation dependencies
Tight controls around access, auditability, and change management
Multiple upstream feeds with inconsistent reference data
Long release cycles due to tightly coupled transformations and reports
Target architecture
Landing and staging in Amazon S3
Ingestion to Snowflake using batch and CDC patterns depending on source
Transformation and modelling in dbt with testing and documentation
Curated marts designed for operations, finance, and risk reporting
Power BI semantic layer aligned to agreed KPIs and definitions
Delivery approach
Phase 1 Assess and plan
Inventory tables, jobs, reports, and dependencies
Define migration waves by business domain and cutover risk
Agree KPI definitions and reconciliation rules with control owners
Set non functional requirements for latency, retention, and access controls
Phase 2 Build and migrate
Create S3 landing zones with clear folder conventions and retention policies
Implement ingestion pipelines with audit columns and load logging
Build dbt models with naming standards and layered architecture
Add data tests for key constraints, reconciliations, and late arriving data
Create a consistent Power BI dataset per domain, then unify into a shared semantic layer
Phase 3 Validate and cut ove
Create S3 landing zones with clear folder conventions and retention policies
Implement ingestion pipelines with audit columns and load logging
Build dbt models with naming standards and layered architecture
Add data tests for key constraints, reconciliations, and late arriving data
Create a consistent Power BI dataset per domain, then unify into a shared semantic layer
Data model highlights
Payment transaction fact with event level detail and lifecycle status
Reference data conformed across payment rails and channels
Daily control totals and exception tables for reconciliation workflows
Marts aligned to operational KPIs, risk indicators, and service performance
Tech stack
Amazon S3
Snowflake
dbt
Power BI
Outcomes to capture on your site
Replace this section with your real metrics once you have them.
Examples you can measure cleanly in Snowflake and Power BI include:
Reduction in report build and change time
Improvement in query performance versus Oracle
Reduction in manual reconciliation effort
Fewer KPI definition disputes due to a shared semantic layer