Migrating from Informatica to dbt

Informatica has run enterprise data pipelines for twenty-plus years. PowerCenter mappings, workflows, the repository — it's battle-tested and it's everywhere. It's also expensive, server-bound, and staffed by a specialist skill set that's getting harder and pricier to hire.

When teams re-evaluate, the destination is increasingly a modern ELT stack with dbt at the transformation layer. If you've read our Talend → dbt playbook, the shape here is similar — but Informatica has its own components and its own traps. This is the Informatica-specific version.

Why teams leave Informatica

1. Cost. PowerCenter licensing plus the servers it runs on is a large, fixed line item. dbt Core is open source; the compute is your warehouse, billed per use.
2. Transformations run on Informatica's engine, not your warehouse. You pay for and maintain that engine. dbt pushes the same logic down into Snowflake/BigQuery/Databricks compute that scales with your data.
3. Version control is bolted on. Informatica stores mappings in a repository database. dbt is Git-native — every change is a readable PR.
4. The hiring pool. Informatica specialists are shrinking and costly. Anyone who writes SQL can maintain dbt.

Map the Informatica components to their modern equivalents

Whether you're on PowerCenter or the cloud IICS (Informatica Intelligent Cloud Services), the concepts translate:

The most important row: Informatica's Pushdown Optimization feature exists to make Informatica behave like dbt — run the transformation in the database instead of on the ETL server. With dbt, that's not a feature you toggle; it's the entire architecture. Teams that already lean on pushdown are, in a sense, already halfway to the dbt model.

The audit: inventory every mapping and workflow

Before writing dbt, catalogue the estate. For each mapping and workflow:

• Sources and targets — where data comes from, where it lands.
• Transformations used — Expression, Aggregator, Joiner, Lookup, Filter, Router, Update Strategy.
• Reusable mapplets — these become dbt macros or intermediate models; find them once.
• Schedule and dependencies — the workflow order and what breaks downstream.
• Consumer — who reads the output.

As with every ETL migration, expect a real slice of workflows to be orphaned — scheduled, consuming compute, feeding nothing anyone looks at. Retire those instead of migrating dead weight.

Extraction is not dbt's job

The single most important thing to internalize: dbt transforms data that's already in the warehouse. It does not extract.

Informatica's source connections and reader components get replaced by purpose-built ingestion:

• Fivetran for managed SaaS/database connectors — Salesforce, SAP, Oracle, hundreds more.
• Airbyte for self-hosted or custom sources.
• Cloud Functions / Workflows for bespoke API pulls.

Raw data lands untouched, registers as dbt sources, and lineage starts clean.

Converting mappings to dbt models

Each Informatica mapping becomes a SQL file, layered:

Staging models (stg_*.sql) — one per source. This is where Expression transformations (renames, casts, cleanups) and Filters live:

-- models/staging/stg_customers.sql
SELECT
    customer_id,
    UPPER(TRIM(customer_name))     AS customer_name,   -- Expression transform
    CAST(signup_date AS DATE)      AS signup_date,
    country_code
FROM {{ source('crm', 'raw_customers') }}
WHERE customer_id IS NOT NULL                            -- Filter transform

Mart models (mart_*.sql) — Joiner, Aggregator, and Lookup transformations become SQL joins and GROUP BYs:

-- models/marts/mart_customer_revenue.sql
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id)   AS orders,        -- Aggregator
    SUM(o.order_total)  AS revenue
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('stg_orders') }} o       -- Joiner
    ON c.customer_id = o.customer_id
GROUP BY 1, 2

Two Informatica-specific translations worth naming:

• Lookup transformation → a JOIN or dbt ref(). Most lookups are just joins the visual tool obscured.
• Update Strategy (insert/update/delete flags) → dbt incremental models with a unique_key and merge strategy. This is where Informatica's row-level DML logic goes.

Don't port mappings 1:1. Visual transformations often hide join logic that only works by accident — rewriting in SQL exposes the assumptions.

Reusable mapplets → macros

Informatica mapplets exist for reuse. Their dbt equivalent is a macro (for logic reused across models) or an intermediate model (for a reusable transformed dataset). Identify the mapplets once and you've found your macros for the whole migration.

Orchestration and testing

Workflows and sessions become an orchestrator DAG — Airflow, Dagster, or Prefect — that decides what runs and when. dbt decides how to transform.

Testing is where dbt quietly wins. Informatica's data-quality checks exist but are inconsistently wired. dbt makes them the default:

models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

dbt test runs in CI; failures block merges.

Parallel validation: the non-negotiable step

Two to four weeks running Informatica and the dbt stack side by side. Compare row counts, aggregate values, and dashboards daily. When they match for a full week, retire the Informatica workflow — not before.

What the stack looks like after

Timeline

For a medium PowerCenter estate (30–80 mappings, 2–3 sources):

Total: 7–8 weeks for a team of two. Faster with a clean estate; slower where Update Strategy logic and undocumented mapplets pile up.

We've run this migration across Snowflake, BigQuery, and Databricks targets. If you're weighing the move off Informatica — or PowerCenter vs IICS specifics — book a discovery call and we'll map your workflows to a dbt stack. Related: Talend → dbt and the legacy stack migration playbook.