SnowConvert AI - Informatica PowerCenter¶

This topic documents the Informatica PowerCenter components supported by SnowConvert AI and describes the output generated for each component category.

For general information about the ETL migration process, prerequisites, and step-by-step migration instructions, see ETL Migration.

Control flow elements¶

Informatica Workflows define the orchestration logic: which Sessions run, in what order, and with what variable context. SnowConvert AI converts Workflow elements into Snowflake TASK DAGs and stored procedures.

These Informatica Workflow elements are supported:

Unlisted Workflow elements generate an EWI code indicating manual conversion is required.

Session task¶

A Session in Informatica executes a Mapping at runtime. Each Session is converted into a Snowflake TASK that runs the corresponding dbt project.

Conversion output:

• A CREATE OR REPLACE TASK statement with AFTER dependency on the preceding task
• Variable initialization via BuildDbtVarsJsonUDF
• EXECUTE DBT PROJECT call with the variable JSON payload

Example:

:force:

CREATE OR REPLACE TASK public.wf_daily_load_s_load_customers
WAREHOUSE = DUMMY_WAREHOUSE
AFTER public.wf_daily_load
AS
BEGIN
    LET dbt_vars VARCHAR := public.BuildDbtVarsJsonUDF('wf_daily_load');
    EXECUTE DBT PROJECT public.m_load_customers ARGS = :dbt_vars;
END;

Notes:

• Replace DUMMY_WAREHOUSE with your actual Snowflake warehouse name
• The AFTER clause preserves the original Workflow execution order
• Reusable Sessions are resolved to reference the correct Mapping

Session overrides¶

Informatica Sessions can override properties defined at the Mapping level. SnowConvert AI captures these overrides and applies them during conversion.

Pre-SQL and Post-SQL¶

• Session-level Pre-SQL statements are generated before the EXECUTE DBT PROJECT call within the TASK body
• Session-level Post-SQL statements are generated after the EXECUTE DBT PROJECT call
• Pre-SQL and Post-SQL on Target transformations are also supported

SQL Override¶

• When a Session defines a SQL Override for a Source Qualifier, the override takes priority over the Mapping-level SQL
• The override is applied during dbt model generation, replacing the default source query

Worklet task¶

Informatica Worklets are reusable, nested sub-workflows that can be embedded inside a parent Workflow. SnowConvert AI converts Worklets into nested Snowflake Task Graphs.

Conversion output:

• A separate SQL file per Worklet in the worklets/ directory
• The Worklet’s internal Start task and Session tasks are converted following the same patterns as the parent Workflow
• The parent Workflow references the Worklet root task via an AFTER dependency

Example structure:

{FolderName}/{MappingName}/
├── wf_daily_load.sql               # Parent Workflow
└── worklets/
    └── wklt_validate.sql             # Worklet sub-graph

The Worklet Task Graph preserves the original execution hierarchy. Worklet tasks follow the same naming and variable management patterns as the parent Workflow.

Start task¶

The Informatica Start task (the entry point of a Workflow) is converted into an initialization TASK that sets up the variable context:

:force:

CREATE OR REPLACE TASK public.wf_daily_load AS
BEGIN
    CALL public.InitVariablesFromConfig('wf_daily_load');
END;

This TASK loads default variable values and applies parameter file overrides before any Session tasks execute.

dbt project execution¶

Within the orchestration code, Sessions are executed using Snowflake’s EXECUTE DBT PROJECT command:

:force:

EXECUTE DBT PROJECT schema.project_name ARGS = :dbt_vars;

Important requirements:

• The project_name must match the name used when deploying the dbt project (via CREATE DBT PROJECT or Snowflake Workspace deployment)
• The ARGS parameter passes workflow variables as a JSON payload built by BuildDbtVarsJsonUDF
• Each execution runs the entire dbt project with all models in dependency order

Deployment options:

• Snowflake CLI: snow dbt deploy --schema schema_name --database database_name --force package_name
• Snowflake Workspace: Upload and deploy via UI

Variable management¶

Informatica variables ($$var assignments) are converted into a table-driven management system. The infrastructure components are generated in the etl_configuration/ folder.

Control variables table¶

The control_variables table stores all workflow variables, parameters, and their values:

UDFs and procedures¶

Parameter file support¶

Informatica .txt and .xml parameter files are parsed during migration. Parameter values are loaded into the control_variables table at runtime via InitVariablesFromConfig, overriding default variable values for each workflow execution.

Built-in variables¶

Data flow components¶

Informatica Mappings define the data transformation logic: how data flows from sources through transformations to targets. SnowConvert AI converts each Mapping into a standalone dbt project with a three-tier model architecture.

These Informatica Mapping transformations are supported:

Unlisted Mapping transformations generate an EWI code indicating manual conversion is required.

Layer organization¶

dbt project structure¶

Each Mapping produces a standalone dbt project:

{MappingName}/
├── dbt_project.yml                   # Materialization config
├── profiles.yml                      # Snowflake connection profile
├── models/
│   ├── sources.yml                   # Source table definitions
│   ├── staging/
│   │   ├── stg_customers.sql
│   │   └── stg_regions.sql
│   ├── intermediate/
│   │   ├── int_expression.sql
│   │   └── int_joiner.sql
│   └── marts/
│       └── customer_dim.sql
└── macros/
    └── *.sql                         # Mapplet-derived macros

Before deploying, replace the `YOUR_SCHEMA` and `YOUR_DB` placeholders in `sources.yml` and `profiles.yml` with your actual Snowflake schema and database names.

Expression functions¶

SnowConvert AI supports over 60 Informatica expression functions across the following categories:

Naming and sanitization rules¶

SnowConvert AI applies consistent sanitization rules to all Informatica object names to ensure dbt and Snowflake compatibility:

These rules apply uniformly across all generated artifacts: dbt model names, Snowflake TASK names, procedure names, and variable identifiers.