From Qlik, Informatica and DataStage to dbt Projects on Snowflake: A Source-by-Source Guide

For years, running dbt in production meant running something next to your warehouse: dbt Cloud, or a container, a scheduler and a secrets store you looked after yourself. dbt Projects on Snowflake removes that layer. Snowflake hosts managed dbt Core and dbt Fusion runtimes. You deploy a project as a Snowflake object, run it with EXECUTE DBT PROJECT, schedule it with Snowflake tasks, and read run history, logs and lineage in Snowsight. Runs use a normal virtual warehouse, with no separate dbt licence or per-user fee.

That makes dbt the obvious transformation layer for any team already on Snowflake. It also leaves a large gap. When your legacy estate is Teradata or Oracle SQL, Snowflake's own migration tooling already translates it. When it is ten years of Qlik load scripts, Informatica mappings, DataStage parallel jobs, Talend tMaps, Alteryx workflows, SSIS packages or SAS programs, there is no SQL to translate. The logic lives in expression languages, stage variables, lookup caches, tool configurations and macro code.

This guide covers that gap, one source at a time: what each tool's logic becomes in a dbt project, where the conversion is subtle, and how the finished project reaches production on Snowflake.

Why non-SQL sources need a parser, not a transpiler

A SQL-to-SQL translator works on text it can read: it takes one dialect's grammar and writes out another's. A Qlik app, an Informatica mapping or a DataStage job is a different kind of artifact. It is a graph of steps stored as XML, a binary container or a proprietary script. Each step carries expressions in its own language, with its own rules for NULLs, string handling, type coercion and row order. Two things have to happen before any dbt can be written:

1. Rebuild the graph. Work out which step feeds which, which lookups are cached references, which outputs are rejects, and which variables hold state across rows. That graph becomes the dbt DAG of ref() calls.
2. Keep the semantics. Every expression has to produce the same answer in Snowflake SQL, including the edge cases where the legacy engine behaves differently from ANSI SQL. Most migration defects come from these edge cases, and the examples below show several.

A general-purpose LLM can produce plausible dbt from a screenshot of a job. It cannot promise the same result on the row where LAST_NAME is NULL. This is where deterministic parsing earns its place.

Qlik: load scripts to dbt models

Qlik Sense and QlikView apps hold their data preparation in a load script: MAPPING LOAD tables, ApplyMap() lookups, preceding loads stacked on top of a SQL SELECT, RESIDENT re-reads of tables already in memory, and STORE ... INTO QVD files that other apps then read. In most of that script, the only line Snowflake could translate on its own is the SQL SELECT.

MapRegion:
MAPPING LOAD country_code, region
FROM [lib://ref/regions.qvd] (qvd);

Customers:
LOAD customer_id,
     name,
     ApplyMap('MapRegion', country_code, 'Unknown') AS region;
SQL SELECT customer_id, name, country_code FROM crm.customers;

LEFT JOIN (Customers)
LOAD customer_id, Sum(amount) AS lifetime_value
RESIDENT Orders
GROUP BY customer_id;

STORE Customers INTO [lib://marts/dim_customers.qvd] (qvd);

{{ config(materialized='table') }}

with customers as (
    select * from {{ ref('stg_crm__customers') }}
),
regions as (
    select * from {{ ref('stg_ref__regions') }}
),
order_totals as (
    select customer_id, sum(amount) as lifetime_value
    from {{ ref('stg_crm__orders') }}
    group by customer_id
)

select
    c.customer_id,
    c.name,
    coalesce(r.region, 'Unknown') as region,
    o.lifetime_value
from customers c
left join regions r      on c.country_code = r.country_code
left join order_totals o on c.customer_id  = o.customer_id

models:
  - name: stg_ref__regions
    columns:
      - name: country_code
        tests: [unique, not_null]

Informatica: mappings to incremental models

A PowerCenter mapping, or its IDMC equivalent, is a chain of transformations: Source Qualifier, Expression, Lookup, Filter, Router, Update Strategy, Target. The logic sits in Informatica's expression language inside the exported XML. The Update Strategy decides whether each row is inserted or updated, which in dbt is an incremental model with a merge.

<TRANSFORMATION NAME="EXP_DERIVE" TYPE="Expression">
  <TRANSFORMFIELD NAME="FULL_NAME" PORTTYPE="OUTPUT"
     EXPRESSION="LTRIM(RTRIM(FIRST_NAME)) || ' ' || LTRIM(RTRIM(LAST_NAME))"/>
  <TRANSFORMFIELD NAME="STATUS_CD" PORTTYPE="OUTPUT"
     EXPRESSION="IIF(ISNULL(STATUS), 'UNK',
                 DECODE(STATUS, 'A', 'ACTIVE', 'I', 'INACTIVE', 'OTHER'))"/>
</TRANSFORMATION>

<TRANSFORMATION NAME="UPD_CUSTOMER" TYPE="Update Strategy">
  <TABLEATTRIBUTE NAME="Update Strategy Expression"
     VALUE="IIF(ISNULL(LKP_CUSTOMER_KEY), DD_INSERT, DD_UPDATE)"/>
</TRANSFORMATION>

{{ config(
    materialized='incremental',
    unique_key='customer_id',
    incremental_strategy='merge'
) }}

select
    customer_id,
    coalesce(trim(first_name), '') || ' ' || coalesce(trim(last_name), '') as full_name,
    case
        when status is null then 'UNK'
        when status = 'A'   then 'ACTIVE'
        when status = 'I'   then 'INACTIVE'
        else 'OTHER'
    end as status_cd,
    updated_at
from {{ source('crm', 'customers') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

DataStage: parallel jobs to models and snapshots

An IBM DataStage parallel job is a canvas of stages joined by links. Transformer stages hold derivations, constraints and stage variables. Lookup stages join reference links. The Slowly Changing Dimension stage manages Type 2 history. Sequences decide which job runs after which. Each piece has a direct dbt counterpart, and the job's graph becomes the dbt DAG almost stage for stage.

Stage variable  svSegment = If lnk_in.ANNUAL_REV >= 1000000 Then "ENTERPRISE"
                            Else If lnk_in.ANNUAL_REV >= 100000 Then "MID"
                            Else "SMB"
Constraint      lnk_in.STATUS <> "X"
Derivation      CUST_NAME = Trim(lnk_in.CUST_NAME)
Derivation      SEGMENT   = svSegment

SCD stage       SCD_CUSTOMER   business key CUST_ID, Type 2 on SEGMENT, ADDRESS

select
    cust_id,
    regexp_replace(trim(cust_name), ' +', ' ') as cust_name,
    case
        when annual_rev >= 1000000 then 'ENTERPRISE'
        when annual_rev >= 100000  then 'MID'
        else 'SMB'
    end as segment,
    address,
    country_code
from {{ source('crm', 'customers') }}
where status <> 'X'

{% snapshot customers_snap %}
{{ config(
    target_schema='snapshots',
    unique_key='cust_id',
    strategy='check',
    check_cols=['segment', 'address']
) }}
select * from {{ ref('int_customers') }}
{% endsnapshot %}

Talend: tMaps, lookups and contexts

Talend jobs keep their business logic in tMap components as Java expressions, with lookups, inner-join rejects and context variables that change per environment. In dbt, the Java expressions become SQL, the contexts become var() or environment variables, and the reject flow becomes either its own model or a test.

out.email   = row1.email == null ? "unknown" : row1.email.toLowerCase().trim()
out.region  = context.region
filter      = row1.country.equals("US") && row1.active
lookup      = row2 (inner join on customer_id, "Catch lookup inner join reject" = true)

select
    c.customer_id,
    coalesce(lower(trim(c.email)), 'unknown') as email,
    '{{ var("region") }}'                     as region
from {{ ref('stg_customers') }} c
inner join {{ ref('stg_accounts') }} a on c.customer_id = a.customer_id
where c.country = 'US' and c.active

The catch inner-join reject output becomes a companion model, int_us_customers_rejects, which is an anti-join on the same key. The team keeps the audit trail it had in Talend, but as a table it can query.

Alteryx: workflows to model chains

An Alteryx workflow (.yxmd) is XML describing tools and their connections: Input Data, Filter, Formula, Join, Summarize, Output. Each tool has a direct counterpart in a SQL CTE. The part that needs care is the Join tool, which has three outputs: L (left only), J (joined) and R (right only). Downstream tools often use more than one of them.

<Node ToolID="4">
  <GuiSettings Plugin="AlteryxBasePluginsGui.Formula.Formula"/>
  <Properties><Configuration><FormulaFields>
    <FormulaField field="MarginPct" type="Double"
       expression="IIF([Revenue] > 0, [Margin] / [Revenue], Null())"/>
  </FormulaFields></Configuration></Properties>
</Node>

with joined as (   -- Join tool, J output
    select p.*, s.revenue, s.margin
    from {{ ref('stg_products') }} p
    inner join {{ ref('stg_sales') }} s on p.product_id = s.product_id
)
select
    *,
    iff(revenue > 0, margin / revenue, null) as margin_pct   -- Formula tool
from joined

If the workflow also uses the L output, for example to report products with no sales, that becomes its own model. The mapping from each tool's output to a dbt model is recorded, so an analyst who knew the workflow can find their logic in the new project.

SSIS packages and SAS programs

SSIS

An SSIS data flow runs OLE DB Source, Derived Column, Lookup, Conditional Split, then Destination. Derived Column expressions such as ISNULL(Phone) ? "N/A" : REPLACE(Phone, "-", "") become coalesce(replace(phone, '-', ''), 'N/A'). Each Conditional Split output becomes a filtered model. Lookup "redirect rows to no match output" becomes an anti-join model, the same pattern as the Talend rejects. The control flow's precedence constraints become the DAG order, and SQL Agent schedules become Snowflake tasks.

SAS

SAS DATA steps and PROC SQL become models. %MACRO definitions become Jinja macros. PROC FORMAT value formats become seeds or lookup models. The row-order logic that makes SAS distinctive, such as RETAIN and FIRST./LAST. processing, becomes window functions.

%macro region_summary(region);
  proc sql;
    create table summary_&region. as
    select product, sum(sales) as total_sales
    from work.sales where region = "&region."
    group by product;
  quit;
%mend;

{% macro region_summary(region) %}
    select product, sum(sales) as total_sales
    from {{ ref('stg_sales') }}
    where region = '{{ region }}'
    group by product
{% endmacro %}

The full mapping, on one page

Deploying and running the project on Snowflake

The output of a conversion is a standard dbt project. There is nothing proprietary to install, and your team can open it in a Snowflake Workspace or any editor. The path to production follows Snowflake's documented workflow.

-- 1. Deploy the generated project from a Git repository stage
CREATE DBT PROJECT analytics.dbt.customer_marts
  FROM '@analytics.integrations.migryx_git/branches/main'
  DEFAULT_TARGET = 'prod'
  COMMENT = 'Converted from DataStage and Informatica by MigryX';

-- 2. Build models, snapshots and tests once, on demand
EXECUTE DBT PROJECT analytics.dbt.customer_marts
  ARGS = 'build --target prod';

-- 3. Replace the legacy scheduler with a Snowflake task
CREATE OR ALTER TASK analytics.dbt.nightly_customer_marts
  WAREHOUSE = transform_wh
  SCHEDULE  = 'USING CRON 0 2 * * * America/New_York'
AS
  EXECUTE DBT PROJECT analytics.dbt.customer_marts ARGS = 'build --target prod';

Two Snowflake features matter a lot during a migration. First, env.yml keeps development, test and production settings in one Git-versioned file, much as Talend contexts and Informatica parameter files did. Second, Slim CI lets a pull request rebuild only the models that changed and defer everything else to production. When you are converting a large estate in waves, that keeps each wave's validation quick and cheap.

Cut over on evidence, not faith. Before any legacy job is switched off, run the converted models against the same inputs and compare the results with the legacy output, both row by row and in aggregate. Once the dbt tests generated from the legacy rules and that parity report both pass, the job can be decommissioned.

What should not become dbt

dbt is a transformation framework. It should not absorb everything a legacy platform did. A good conversion puts each piece of work where Snowflake handles it best:

• Set-based transformations, which are the large majority of ETL logic, go to dbt models.
• Procedural or row-by-row logic that SQL can't express cleanly, such as SAS IML, SSIS script tasks in C# or Alteryx Python tools, goes to Snowpark, called from the pipeline.
• Statistical and scoring models, such as SAS PROC LOGISTIC, go to Snowpark or Cortex ML.
• File landing and continuous ingestion go to Snowpipe, ahead of dbt's staging layer.
• Orchestration across non-dbt steps goes to Snowflake tasks, which can call EXECUTE DBT PROJECT as one step in the graph.

Being deliberate about this split is how you avoid a dbt project full of workarounds. It also makes the case to the business straightforward: every legacy job gets a clear Snowflake-native home.