Converting 431 Informatica Mappings
to Snowflake Without Anyone Reading Them

The Problem

An asset management business was running its reporting warehouse on Oracle, on-premise, with Informatica doing the loading and Control-M running the schedule. 412 tables, 431 mappings across 396 workflows, and 94 Control-M jobs holding it together.

The platform wasn't broken. It was expensive, and it needed people. Engineers started the day working out what had failed overnight, and files still arrived by being put somewhere by hand. That second cost is the one that doesn't appear in a budget line, and it's usually the one that decides the business case.

Why This Was Hard

Moving the tables was the straightforward part. Moving 431 mappings was not — and the reason is specific to Informatica.

A stored procedure is text. You can read it, and a model can be given it directly. An Informatica mapping is not text in any useful sense. It's a graph, held as metadata: sources, targets, and a chain of transformations wired together port by port.

Two properties of that graph don't survive a naive translation into SQL. First, it has an execution order — transformations run in sequence, and the output of one is the input to the next. Second, some transformations hold state between rows. An Expression transformation can carry a variable from one row to the next. Translate a stateful port as an ordinary column expression and you get valid SQL that quietly returns different numbers.

The Solution

Four hundred-odd mappings is too many to convert by hand inside a delivery window. But it's also a highly repetitive problem — Informatica has a finite set of transformation types, and each one has a known SQL equivalent. That combination, repetitive and well-defined, is what makes automation worth building.

The starting point wasn't a prompt. It was a parser. Each mapping's metadata was read into a structured representation: sources, targets, transformations, ports, and the links between them. Only then did the agent see it — and what it saw was a resolved graph rather than an XML dump. That single decision removed most of the guesswork.

Nothing was trusted until the data agreed. A validation framework ran source and target for the same inputs and compared the results directly: row counts, null counts per column, column-level checksums and numeric totals. It reported which columns disagreed rather than a pass or a fail.

What Replaced What

SnapLogic replaced Informatica. Pipelines handle ingestion and then call the Snowflake procedures. Crucially it also took over the file arrivals that people had been managing by hand — which is where most of the drop in daily intervention came from.

Snowflake procedures replaced the mappings. Transformation compute runs in the warehouse rather than inside the integration tool, which is both faster and cheaper for set-based work.

Control-M stayed. The 94 jobs encoded dependencies that had grown over years. Replacing the enterprise scheduler would have been a second programme with its own risk, so the jobs were rewired rather than retired.

Results

All 412 tables and their loading logic moved to Snowflake, with the Informatica dependency removed and the on-prem footprint retired.

Annual run cost fell by 50% — from eliminating Oracle Enterprise Edition licences, Informatica PowerCenter licences, on-prem servers, storage, and the DR environment.

Query performance improved by 50%, and retrieval times for downstream reporting fell correspondingly. Manual intervention dropped by 80%, which came from two changes: automated ingestion replacing hand-managed file drops, and far fewer overnight failures to triage in the morning.

The operational change was the one the team noticed. Mornings stopped starting with a triage list.

Key Takeaway

When the thing you're converting isn't text, don't start with a prompt. Start with a parser. An Informatica mapping is a graph with execution order and, in places, row-level state. Handed raw to a model it produces confident SQL that runs and returns the wrong answer. Resolved into a proper structure first, the same model becomes reliable — because it's reasoning about dataflow instead of guessing at it.

The second lesson is about where to spend the checking. Structural validation is cheap and catches the obvious. Only a direct data comparison catches the expensive failure: a procedure that compiles, runs, completes successfully and produces a different number.