Informatica PowerCenter to Databricks migration: a practical guide

Informatica PowerCenter has run enterprise ETL for two decades, and many estates still hold thousands of mappings built by people who have since moved on. Teams now look at it for three reasons: the licence is one of the largest lines in the data budget, Informatica is steering PowerCenter customers toward its cloud platform (so a re-platform is coming either way), and the data increasingly lands in Databricks anyway.

Moving PowerCenter logic into Databricks is a well-trodden path. This is the approach we recommend.

1. Export the repository and inventory it

Everything in PowerCenter (mappings, mapplets, sessions, worklets and workflows) can be exported from the repository as XML, using Repository Manager or the pmrep command line:

pmrep connect -r <repository> -d <domain> -n <user> -x <password>
pmrep objectexport -f <folder> -o workflow -n <workflow_name> -m -s -b -r -u wf_export.xml

With the XML in hand, count the transformation types across the estate:

grep -ohE '<TRANSFORMATION [^>]*' *.xml | grep -oE 'TYPE ="[^"]*"' | sort | uniq -c | sort -rn

That count is the basis of a realistic estimate. Pay particular attention to:

• Java, SQL and Stored Procedure transformations: logic that needs reading and rewriting by hand.
• Update Strategy transformations: the insert, update and delete rules that have to become MERGE statements.
• Mapplets: reusable logic that should become one shared view, function or module, not copies in every pipeline.
• Parameter files and mapping variables: dates, paths and environment settings the new pipelines must receive.
• Pre- and post-session SQL, and pushdown optimization: logic that runs in the source or target database rather than in Informatica, which is easy to miss.

Also capture, per workflow, its schedule, runtime and the consumers of its output, and use the workflow run history to find what still runs and what still matters. Retire unused mappings before you convert them. Databricks’ Lakebridge analyzer can profile an exported estate and produce much of this inventory for you.

2. Map each transformation to Spark SQL and Delta

PowerCenter processes rows through a chain of transformations; Databricks works on whole datasets. Most transformations have a direct equivalent:

PowerCenter transformation	On Databricks
Source Qualifier	Ingest into a bronze Delta table (Lakeflow Connect, Auto Loader for files, or a JDBC read); its SQL override becomes the first query in silver
Expression	Column expressions in a SQL SELECT
Filter	WHERE
Router	One filtered query per output group, or a CASE column feeding separate writes
Joiner	A join
Lookup (connected or unconnected)	A join; broadcast small lookup tables
Aggregator	GROUP BY
Sorter, Rank	ORDER BY and window functions such as ROW_NUMBER()
Union	UNION ALL
Normalizer	explode or stack
Sequence Generator	An identity column on the Delta table
Update Strategy (DD_INSERT, DD_UPDATE, DD_DELETE)	MERGE INTO with matching WHEN MATCHED and WHEN NOT MATCHED clauses
Slowly changing dimension mappings	AUTO CDC (previously APPLY CHANGES) with SCD type 2 in Lakeflow Declarative Pipelines, or a MERGE
Mapplet	A shared SQL view, SQL function or Python module
SQL, Java, Stored Procedure transformations	Rewrite in SQL where possible, otherwise Python; test each one
Target definitions	Writes to Delta tables

Conversion tooling handles the routine rows of this table well. Build the converted logic as Lakeflow Declarative Pipelines (formerly Delta Live Tables) or SQL and Python notebooks, in bronze, silver and gold layers, and add expectations so bad rows are caught at load time rather than in a report.

3. Rebuild workflows as Lakeflow Jobs

Workflow Manager concepts map closely onto Lakeflow Jobs (formerly Databricks Workflows):

• Sessions become tasks that run the converted pipeline or notebook.
• Worklets become nested jobs or grouped tasks.
• Links with conditions become task dependencies with run-if conditions.
• Decision tasks become if/else condition tasks.
• Event-Wait tasks become file-arrival triggers.
• Timer tasks and the Informatica scheduler become job schedules.
• Command tasks become Python or SQL tasks.
• Email tasks become job notifications.
• Parameter files become job parameters, kept in version control with the pipeline code.

If you use PowerExchange for change data capture, plan its replacement explicitly: Lakeflow Connect or a log-based CDC tool landing changes in bronze, applied with AUTO CDC or MERGE.

4. Reconcile every target in a parallel run

Run the converted pipelines alongside PowerCenter against the same sources, and compare every target table: row counts, totals of key measures, and full row-level comparisons for anything that feeds finance or regulatory reporting. Lakebridge’s reconciler, or a set of comparison queries, does this table by table.

Differences almost always come from a short list: implicit data type conversions in Expression transformations, null handling in lookups and joins, trailing spaces and character sets, decimal precision, and date or time zone defaults set at session level. Explain each one, fix it, and cut over a domain only after its comparisons have been clean for an agreed number of runs and its owner has signed off.

5. Plan the licence end date from day one

The business case usually rests on switching off the Informatica licence and its servers. Agree the renewal date that the program has to beat, schedule waves backwards from it, and decommission each workflow the day its domain cuts over. A migration that leaves PowerCenter running “for now” keeps both costs.

The short version

Export the repository, count transformation types, convert the routine logic with tooling, rewrite the scripted transformations by hand, rebuild workflows as Lakeflow Jobs, reconcile every target in a parallel run, and work back from the licence renewal date.

For other legacy sources, see our guides to Teradata and Pentaho migrations. For an estimate on your own estate, the Data Platform Assessment includes the inventory, the effort by transformation type and a fixed-price plan for each wave. See also our cloud data migration service.