Home / Data Platform / dbt & Quality

Data PlatformHow-toIntermediate

Transformation: legacy GUI ETL (Informatica, SSIS, DataStage) to ELT with dbt

Replace mapping-heavy ETL tools with SQL-first ELT in dbt. Inventory mappings, separate extraction from transformation, rebuild logic as layered dbt models with tests, run old and new in parallel and retire the ETL servers.

Deepak Bhardwaj2 min read

Applies toInformatica PowerCenter, SSIS, IBM DataStage sources; dbt Core / dbt Cloud on Snowflake, Databricks, BigQuery, Synapse/Fabric

The shift

ETL tool ELT with dbt
Transform in the ETL server, then load Load raw first, transform inside the warehouse
Logic in GUI mappings Logic in version-controlled SQL
Testing by eyeballing Tests run with every build
Lineage in a proprietary repository Lineage generated from ref()

dbt does transformation only. Extraction and loading move to a separate tool (Fivetran, Airbyte, ADF, Lakeflow Connect, native connectors).

1. Inventory

Export the ETL repository metadata (mappings, sessions, workflows, sources, targets). For each mapping record: sources, targets, transformations (lookups, aggregations, SCD logic), schedule, consumers, and whether it's still used. Typically 20–40% can be retired.

2. Design the layers

models/
  staging/      one model per source table: rename, cast, light cleanup (views)
  intermediate/ joins and business logic reused by marts
  marts/        facts and dimensions consumers query (tables / incremental)
snapshots/      SCD Type 2 history

3. Rebuild a mapping

A typical Informatica mapping (source qualifier → lookup → expression → aggregator → target) becomes:

-- models/staging/stg_erp__orders.sql
select
    order_id::number            as order_id,
    customer_no                 as customer_id,
    try_to_date(order_dt, 'YYYYMMDD') as order_date,
    amount::number(18,2)        as amount
from {{ source('erp', 'orders') }}

-- models/marts/fct_daily_sales.sql
{{ config(materialized='incremental', unique_key=['order_date','customer_id'], incremental_strategy='merge') }}
select o.order_date, o.customer_id, c.region, sum(o.amount) as revenue
from {{ ref('stg_erp__orders') }} o
left join {{ ref('dim_customer') }} c using (customer_id)
{% if is_incremental() %}
where o.order_date >= (select dateadd(day, -3, max(order_date)) from {{ this }})
{% endif %}
group by 1, 2, 3

SCD Type 2 dimensions become dbt snapshots:

{% snapshot customer_snapshot %}
{{ config(target_schema='snapshots', unique_key='customer_id', strategy='check', check_cols=['name','region','segment']) }}
select * from {{ ref('stg_crm__customers') }}
{% endsnapshot %}

4. Tests

models:
  - name: fct_daily_sales
    columns:
      - name: customer_id
        tests: [not_null, relationships: {to: ref('dim_customer'), field: customer_id}]
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_date, customer_id]

5. Parallel run and reconcile

Run both for several cycles; compare with a reconciliation model (row counts and sums by key dimensions, differences listed). Switch consumers per subject area; turn off the ETL workflow; archive the mapping export.

Tip

Don't translate mappings one to one. Many legacy mappings exist to stage data between tools — in ELT those steps collapse into a few models.

Checklist

  • Mapping inventory with keep/retire decisions
  • Extraction moved to a loader; raw data landing in the warehouse
  • dbt project layered (staging/intermediate/marts), snapshots for SCD2
  • Tests on keys, relationships and business rules; CI on pull requests
  • Parallel run reconciled; ETL servers retired

Related fixes