Confidential client, name withheld under NDA

Legacy ETL Modernization and Migration to Snowflake for a US Retail and Commercial Bank

Quick answer: A Snowflake warehouse with streaming ingestion and dbt ELT gives a bank's fraud and risk teams transaction data within seconds of posting. Bacancy Technology built this for a US retail and commercial bank.

Outcomes deliveredDelivered July 2025 to April 2026 by a team of 7
Seconds

Transaction data to fraud teams

Down from 24 hours

75%

Lower ETL running costs

Incremental dbt models, no nightly reloads

60%

Faster fraud model scoring

Extract step removed with Snowpark

100%

Regulatory figures traced to source

Replaces manual evidence gathering

Client name withheld under NDA. To honour our non-disclosure agreement and compliance obligations, we describe this client by industry and region only. The project scope, technology and outcomes are shared without details that could identify them.

About the Client

The client is a US bank offering retail and commercial banking. Its analytics ran on SQL Server, where a heavy overnight ETL load left fraud and risk teams with day-old data. Undocumented pipelines slowed every change, and regulatory figures could not be traced to source transactions.

Bacancy Technology migrated the warehouse to Snowflake, rebuilt batch ETL as dbt ELT with streaming ingestion and moved fraud and risk models into Snowpark. Data now arrives within seconds, and ETL running costs fell 75%.

Client type
Retail and commercial bank
Industry
Banking
Location
USA
Team
7-member team
Timeline
July 2025 to April 2026
Project size
Non-disclosable
Engagement
Snowflake migration and data engineering

Challenges & Solutions

Our Snowflake and dbt engineers fixed each problem on one governed data platform.

Challenge 1: Replacing the overnight batch with continuous data

Every system that read from the SQL Server warehouse waited on the overnight batch, so fraud data was up to 24 hours old.

Solution: Our Snowflake migration team moved the warehouse to Snowflake and added streaming ingestion for card and payment feeds. Ingestion runs on dedicated compute, separate from query warehouses, so it works through the business day.

Challenge 2: Rebuilding undocumented ETL as tested dbt models

Hand-coded, undocumented pipelines meant every change started with reading scripts, and a new source took 2 to 3 weeks.

Solution: Our dbt developers rebuilt the pipeline logic as SQL models, each with tests and documentation, under Git version control. Fivetran connectors, configured per source, replaced the custom ingestion scripts.

Challenge 3: Tracing every regulatory figure to its source

Quarterly regulatory figures could not be traced to their source transactions, so analysts rebuilt each calculation by hand.

Solution: Our data engineers rebuilt the regulatory datasets as dbt models, one per report, calculated against transaction-level records. Power BI reports now point at those models, with Snowflake RBAC and dynamic data masking on customer identifiers.

Challenge 4: Scoring fraud and risk models inside Snowflake

Models scored in a separate environment, so each run extracted and copied customer transaction data.

Solution: Our Snowflake consulting team planned the move of fraud and risk models into Snowpark and rewrote the scoring code in Python to run inside Snowflake. The bank retired the extract jobs and removed the external copies of customer data.

How the Platform Works

The client fraud and risk reporting dashboard on Snowflake

Core Features

  • Streaming transaction ingestionCard and payment feeds land continuously on dedicated compute.
  • Incremental dbt transformationsTests on every model and every change tracked in version control.
  • Traceable regulatory reportingEach filed figure maps to the transaction records behind it.
  • Snowpark fraud and risk scoringModels score data in place, with no extract step.
  • Governed customer dataSnowflake RBAC and dynamic masking on customer identifiers.

The Stack Behind the Data Platform

Legacy warehouse
SQL Server
Cloud data platform
Snowflake | Snowpark | Snowflake Cortex
Data ingestion
Fivetran
Transformation and orchestration
dbt | dbt tests | dbt Cloud
BI and reporting
Power BI
Scripting
Python
Security and governance
Snowflake RBAC | Dynamic data masking
Version control and CI/CD
Git | GitHub Actions

Ready to start?

2500+ projects delivered by Bacancy Technology

Get access to an experienced team of developers and engineers from Bacancy Technology, handpicked to ace your goals. Kickstart within 48 hours, no-risk trial.

Book A 30 Min Call
14+Years of business experience
1458+Happy customers
30+Countries with happy customers
1050+Agile-enabled employees

More Case Studies

View All Case Studies