Informatica PowerCenter 10.5 left standard support on March 31, 2026. Extended support runs into 2027 and sustaining support to 2029, each step costing more and delivering less. With Salesforce's acquisition of Informatica closed on November 18, 2025, the product's long-term direction is being set by a company whose interest is a cloud CRM, not your on-premises ETL grid.
If you run PowerCenter and Snowflake, this is the playbook we use to get you off one and fully onto the other. It is written for the team that has to do the work, not for the steering committee.
Step 1 — Inventory the repository
Do not start from the folder list in the Designer client. Export the repository to XML (pmrep objectexport, or the Repository Manager export) and parse it, so the inventory is complete and machine-readable. For each mapping capture:
- Source and target connections and tables
- Transformation types used (Expression, Lookup, Aggregator, Router, Update Strategy, Normalizer, Java, SQL override, and so on)
- The session's load type (insert, update, upsert, truncate-and-load) and commit settings
- The workflow that runs it, its schedule, and its upstream/downstream dependencies
- Last successful run date from the repository's run history
That last field is the most valuable. In every mature PowerCenter repository we have assessed, a large share of the mappings had not run in over a year. Those do not get migrated; they get a retirement ticket.
Step 2 — Classify by pattern
Sort the live mappings into buckets. The bucket determines the target design and the effort.
| Pattern | Typical PowerCenter shape | Snowflake target |
|---|---|---|
| Pass-through / stage load | Source → Expression → Target, truncate-and-load | Openflow or Snowpipe ingestion straight to a raw table; no transform |
| Type 1 dimension | Lookup + Update Strategy, upsert | dbt incremental model with unique_key, or a Dynamic Table with QUALIFY dedupe |
| Type 2 SCD | Lookup + Expression comparing columns + Update Strategy with effective dates | dbt snapshot (strategy: check or timestamp) |
| Aggregate / fact build | Joiner + Aggregator + Sorter | dbt model or Dynamic Table over the staged sources |
| Conditional routing | Router to multiple targets | One model per target sharing a CTE, or a single model with a discriminator column |
| Procedural | Java transformation, stored-procedure calls, SQL overrides with side effects | Snowpark Python stored procedure, scheduled by a task; redesign candidate |
The first four buckets usually cover the large majority of mappings and convert mechanically. The last one is where the senior engineers spend their time.
Step 3 — Rebuild ingestion on Openflow
PowerCenter did two jobs: moving data in and transforming it. Split them. Ingestion goes to Openflow, Snowflake's managed integration service (Apache NiFi under the hood, GA since June 2025) with connectors for the usual sources — SQL Server, Oracle, PostgreSQL, MySQL CDC, SaaS APIs, files in cloud storage, Kafka. For each source system:
- Create an Openflow connector for the source, landing every table PowerCenter read into a
RAWschema, one table per source table, with a load timestamp and (for CDC sources) the change operation. - Land everything as-is. No transformations in the ingestion layer — that is the mistake that made PowerCenter mappings unreadable.
- For file drops that PowerCenter read from a landing directory, replace with an external stage plus Snowpipe
AUTO_INGEST.
The raw schema is now a faithful copy of the sources, refreshed continuously, and it is the only input the transformation layer needs.
Step 4 — Rebuild transforms as dbt Projects on Snowflake
dbt Projects on Snowflake (GA November 2025) runs dbt Core inside the account: the project lives in a Snowflake workspace connected to your Git repo, runs on a warehouse, and schedules through tasks, with no separate dbt Cloud contract or orchestrator. Each classified mapping becomes a model:
-- models/marts/dim_customer.sql (Type 1 dimension, formerly m_LOAD_DIM_CUSTOMER)
{{ config(materialized='incremental', unique_key='customer_id', incremental_strategy='merge') }}
SELECT
customer_id,
UPPER(TRIM(first_name)) AS first_name,
UPPER(TRIM(last_name)) AS last_name,
COALESCE(email, 'UNKNOWN') AS email,
country_code,
_loaded_at
FROM {{ source('crm', 'customers') }}
{% if is_incremental() %}
WHERE _loaded_at > (SELECT MAX(_loaded_at) FROM {{ this }})
{% endif %}
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _loaded_at DESC) = 1
A Type 2 dimension is a snapshot rather than a model:
# snapshots/dim_product_history.yml
snapshots:
- name: dim_product_history
relation: source('erp', 'products')
config:
schema: snapshots
unique_key: product_id
strategy: check
check_cols: [name, category, list_price]
Every Expression transformation's logic becomes SQL in the model. Every Lookup becomes a join. Every Router becomes a WHERE in a separate model. Add tests (unique, not_null, accepted_values, relationships) to each model as you go — this is the test coverage PowerCenter never had.
For mappings with many near-identical copies (the classic "one mapping per source region"), write one Jinja macro and generate the models.
Step 5 — Parallel-run validation
Run the old and new pipelines side by side against the same source data for at least one full business cycle (month-end if finance depends on it). Reconcile automatically, every day:
-- Row-level: anything in one side but not the other
WITH legacy AS (SELECT * FROM legacy_edw.dim_customer),
modern AS (SELECT * FROM analytics.dim_customer)
SELECT 'only_in_legacy' AS side, * FROM (SELECT * FROM legacy EXCEPT SELECT * FROM modern)
UNION ALL
SELECT 'only_in_modern', * FROM (SELECT * FROM modern EXCEPT SELECT * FROM legacy);
-- Aggregate-level: totals by business key, with tolerance for rounding
SELECT COALESCE(l.fiscal_month, m.fiscal_month) AS fiscal_month,
l.total_amount AS legacy_total, m.total_amount AS modern_total,
ABS(COALESCE(l.total_amount,0) - COALESCE(m.total_amount,0)) AS diff
FROM (SELECT fiscal_month, SUM(amount) total_amount FROM legacy_edw.fact_sales GROUP BY 1) l
FULL OUTER JOIN (SELECT fiscal_month, SUM(amount) total_amount FROM analytics.fact_sales GROUP BY 1) m
ON l.fiscal_month = m.fiscal_month
WHERE ABS(COALESCE(l.total_amount,0) - COALESCE(m.total_amount,0)) > 0.01
ORDER BY 1;
Put these in a Dynamic Table or a scheduled dbt test so the reconciliation report is waiting each morning. Every discrepancy gets a root cause: a genuine bug in the new model, an undocumented PowerCenter behavior (Update Strategy quirks and implicit data-type coercion are the usual suspects), or a legacy defect you are choosing not to reproduce. Write the decision down — the auditors will ask.
Step 6 — Cut over and decommission
Cut over one subject area at a time, consumers first: repoint BI and downstream feeds at the new schema, watch for a cycle, then disable the PowerCenter workflow. When the last workflow is off, run the checklist:
- Workflow schedules disabled and documented
- Integration Service and Repository Service stopped
- Source-system service accounts PowerCenter used revoked
- Firewall rules and ODBC/JDBC entries removed
- Repository backup archived with a retention date
- License renewal cancelled in writing
- Servers decommissioned or reclaimed
Timing
For a repository of a few hundred live mappings with a typical pattern mix, a focused team can complete assessment in two to three weeks and the full migration inside two quarters, with the parallel-run window being the long pole. Starting now lands you before extended support expires; starting in 2027 means paying for sustaining support while you migrate.
We run these migrations end-to-end, or we run the assessment and hand your team the plan. See our Informatica to Snowflake migration service or contact us.