Informatica PowerCenter - Snowflake Scripting mappings and transformations¶

This page describes how an Informatica PowerCenter Mapping is converted to a Snowflake stored procedure in the Snowflake Scripting output format. For the dbt output format, see dbt mappings and transformations. For the concept map and the supported-component matrix, see the Informatica PowerCenter overview.

The mapping procedure¶

Each Mapping becomes one stored procedure named public.m_<Mapping>. The procedure takes a single scope parameter, returns VARCHAR, and runs as the caller. The data flow becomes the body, wrapped in a BEGIN ... END block:

CREATE OR REPLACE PROCEDURE public.m_<Mapping> (scope VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      -- Source Qualifier temporary tables, then the Target INSERT.
   END
$$;

The body is built in data-flow order:

1. If the Mapping references runtime variables, a LET declaration for each one reads its value with GetControlVariableUDF and the scope parameter. See Variables and parameters.
2. If the Mapping uses a Sequence Generator, a CREATE OR REPLACE SEQUENCE statement is emitted first.
3. Each Source Qualifier becomes a CREATE OR REPLACE TEMPORARY TABLE statement.
4. Each intermediate transformation becomes a common table expression (CTE), or a temporary table when its result feeds more than one downstream transformation.
5. The Target becomes the final write statement, which carries the upstream CTEs inside its source query. That statement is normally an INSERT, but an Update Strategy turns it into a MERGE or a DELETE.

The scope parameter is forwarded from the calling Task so the procedure resolves the same variable values the Workflow set at runtime. See Workflows and orchestration.

Source and Source Qualifier¶

A Source Definition emits no SQL of its own: it supplies the table metadata (database, schema, and table name) that the Source Qualifier reads. The Source Qualifier becomes a temporary table that selects the connected columns from the source table:

Informatica (m_SimpleMapping):

<SOURCE NAME="SRC_TABLE" DATABASETYPE="Microsoft SQL Server" OWNERNAME="dbo">
   <!-- ID (primary key), NAME source fields omitted -->
</SOURCE>
<TRANSFORMATION NAME="SQ_SRC_TABLE" TYPE="Source Qualifier">
   <!-- ID, NAME ports omitted -->
   <TABLEATTRIBUTE NAME="Sql Query" VALUE=""/>
   <TABLEATTRIBUTE NAME="Source Filter" VALUE=""/>
</TRANSFORMATION>

Snowflake:

CREATE OR REPLACE TEMPORARY TABLE tmp_sq_src_table AS
   SELECT
      ID,
      NAME
   FROM
      YOUR_DB.dbo.SRC_TABLE;

The temporary table is named tmp_sq_<name> after the Source Qualifier. When the source connection cannot be resolved, the YOUR_DB and YOUR_SCHEMA placeholders into the qualified table name.

Expression¶

An Expression transformation becomes a CTE whose SELECT list carries each output port. A passthrough port appears as col AS col, and a computed port carries its translated formula. Informatica functions and operators convert to their Snowflake equivalents; for the full list, see Expression functions.

Informatica (m_SimpleMapping):

<TRANSFORMATION NAME="EXP_Transform" TYPE="Expression">
   <TRANSFORMFIELD NAME="ID" PORTTYPE="INPUT/OUTPUT" EXPRESSION="ID"/>
   <TRANSFORMFIELD NAME="NAME" PORTTYPE="INPUT/OUTPUT" EXPRESSION="NAME"/>
</TRANSFORMATION>

Snowflake:

WITH source_data AS
(
   SELECT
      ID,
      NAME
   FROM
      tmp_sq_src_table
)
SELECT
   ID AS ID,
   NAME AS NAME
FROM
   source_data

When an Expression feeds more than one downstream transformation, it is materialized as a CREATE OR REPLACE TEMPORARY TABLE tmp_cte_<name> instead of a CTE, so the result is computed once and reused.

Target¶

The Target becomes the procedure’s final statement: an INSERT into the target table whose source query carries the upstream CTEs. The upstream data flow is aliased as sd:

Informatica (m_SimpleMapping):

<TARGET NAME="TGT_TABLE" DATABASETYPE="Microsoft SQL Server">
   <TARGETFIELD NAME="ID" KEYTYPE="PRIMARY KEY" DATATYPE="nvarchar" PRECISION="50"/>
   <TARGETFIELD NAME="NAME" KEYTYPE="NOT A KEY" DATATYPE="nvarchar" PRECISION="100"/>
</TARGET>

Snowflake:

INSERT INTO YOUR_DB.YOUR_SCHEMA.TGT_TGT_TABLE
WITH cte_exp_transform AS
(
   WITH source_data AS
   (
      SELECT
         ID,
         NAME
      FROM
         tmp_sq_src_table
   )
   SELECT
      ID AS ID,
      NAME AS NAME
   FROM
      source_data
)
SELECT
   *
FROM
   cte_exp_transform AS sd;

The CTEs are nested inside the INSERT rather than placed before it, because Snowflake Scripting does not accept a leading WITH clause as a statement inside a BEGIN ... END block.

Naming conventions¶

Every generated object name is derived from the name of the transformation it came from, lowercased:

Filter¶

A Filter becomes a WHERE clause on its source_data input:

CREATE OR REPLACE TEMPORARY TABLE tmp_cte_filtrans AS
   WITH source_data AS
   (
      SELECT
         ID,
         NAME,
         GRADE
      FROM
         tmp_sq_flt_fork2_src
   )
   SELECT
      ID,
      NAME,
      GRADE
   FROM
      source_data
   WHERE
      GRADE > 50;

This example is materialized as a temporary table because two downstream transformations read the filtered rows. A Filter with a single consumer becomes a cte_<name> CTE instead.

Router¶

A Router materializes its input once, then emits one write statement per output group. Each group’s condition becomes that statement’s WHERE clause. The default group is written last, and its condition is the negation of every other group’s condition:

CREATE OR REPLACE TEMPORARY TABLE tmp_cte_rtrtrans AS
   SELECT
      ID,
      GRADE
   FROM
      tmp_sq_rtr_c1_src;
INSERT INTO YOUR_DB.YOUR_SCHEMA.RTR_C1_MATCH (ID, GRADE)
SELECT
   ID AS ID,
   GRADE AS GRADE
FROM
   tmp_cte_rtrtrans
WHERE
   GRADE > 80;
INSERT INTO YOUR_DB.YOUR_SCHEMA.RTR_C1_DEF (ID, GRADE)
SELECT
   ID AS ID,
   GRADE AS GRADE
FROM
   tmp_cte_rtrtrans
WHERE
   NOT COALESCE(GRADE > 80, FALSE);

The default group wraps the negated condition in COALESCE(..., FALSE) so that rows where the condition evaluates to NULL still reach the default group, which is how PowerCenter routes them.

Joiner¶

A Joiner becomes a CTE that joins its master and detail inputs on the join condition. The Informatica join type determines the SQL join: a normal join becomes an inner join, and master or detail outer joins become the corresponding outer join.

Union¶

A Union becomes a CTE whose source_data combines every input with UNION ALL, which preserves duplicate rows the way PowerCenter does:

INSERT INTO YOUR_DB.YOUR_SCHEMA.U37_C1_TGT
WITH cte_uniont AS
(
   WITH source_data AS
   (
      SELECT
         TXT
      FROM
         tmp_sq_u37_c1_emp
      UNION ALL
      SELECT
         TXT
      FROM
         tmp_sq_u37_c1_stu
   )
   SELECT
      TXT AS TXT
   FROM
      source_data
)
SELECT
   *
FROM
   cte_uniont AS sd;

Aggregator¶

An Aggregator becomes a CTE with a GROUP BY over its group-by ports. Each aggregate port carries its translated aggregate function, cast to the port’s declared type:

SELECT
   DEPTCODE AS DEPTCODE,
   CAST(SUM(SALARY) AS NUMBER(18,2)) AS SUMSALARY,

An Aggregator with no group-by ports aggregates over the whole input, producing a single row.

Sorter¶

A Sorter becomes an ORDER BY on its input. When the transformation has the distinct option enabled, duplicate rows are removed as well.

Sequence Generator¶

A Sequence Generator becomes a native Snowflake sequence, created at the top of the procedure body before any data flows. Downstream ports read it with NEXTVAL:

CREATE OR REPLACE SEQUENCE YOUR_DB.YOUR_SCHEMA.SEQTRANS_seq
   START WITH 1
   INCREMENT BY 1;

The sequence is named <transformation>_seq and persists after the procedure finishes, because a Snowflake sequence isn’t a temporary object. Reusable and non-reusable Sequence Generators are both converted.

Normalizer¶

A Normalizer becomes a chain of CTEs that expand each repeating group into separate output rows, one CTE per generated occurrence.

Lookup¶

A connected Lookup becomes a CTE containing two inner CTEs: lookup_reference reads the lookup table, and input_data carries the incoming rows. They’re joined with a LEFT JOIN on the lookup condition, so rows with no match still pass through with NULL lookup values.

Because PowerCenter returns a single row per lookup, lookup_reference is deduplicated with QUALIFY ROW_NUMBER() partitioned by the lookup condition ports:

INSERT INTO YOUR_DB.YOUR_SCHEMA.LKP_Tgt_Baseline (SC_ROW_ID, EmpId, DeptName)
WITH
--** SSC-FDM-INF0070 - Use Any Value RETURNS AN ARBITRARY ROW WHEN MULTIPLE ROWS MATCH; IN SNOWFLAKE THE SELECTED ROW MAY VARY PER RUN. ADD AN ORDER BY TO THE LOOKUP SQL OVERRIDE ONLY IF A SPECIFIC ROW IS REQUIRED. **
cte_lkptrans AS
(
   WITH lookup_reference AS
   (
      SELECT
         DeptCode ,
         DeptName
      FROM
         YOUR_DB.YOUR_SCHEMA.LKP_Dept_Baseline
      QUALIFY
         ROW_NUMBER() OVER (
         PARTITION BY
            DeptCode
         ORDER BY
            (
               SELECT
                  null
            )) = 1
   ),
   input_data AS
   (
      SELECT
         DeptCode DeptCode1,
         SC_ROW_ID SQ_LKP_Emp_Baseline__SC_ROW_ID,
         EmpId SQ_LKP_Emp_Baseline__EmpId
      FROM
         tmp_sq_lkp_emp_baseline
   )
   SELECT
      input_data.DeptCode1 ,
      input_data.SQ_LKP_Emp_Baseline__SC_ROW_ID ,
      input_data.SQ_LKP_Emp_Baseline__EmpId ,
      lookup_reference.DeptCode,
      lookup_reference.DeptName
   FROM
      input_data
      LEFT JOIN
         lookup_reference
         ON lookup_reference.DeptCode = input_data.DeptCode1
)
SELECT
   sd.SQ_LKP_Emp_Baseline__SC_ROW_ID AS SC_ROW_ID,
   sd.SQ_LKP_Emp_Baseline__EmpId AS EmpId,
   sd.DeptName AS DeptName
FROM
   cte_lkptrans AS sd;

Input ports are aliased <source_qualifier>__<port> inside input_data so they can’t collide with the lookup table’s own column names.

The SSC-FDM-INF0070 note is emitted because the ORDER BY inside ROW_NUMBER() has no deterministic key. If a lookup can match several rows and you need a specific one, add an ORDER BY to the Lookup SQL override.

An unconnected Lookup is converted differently: it becomes a Snowflake scalar UDF that the calling expression invokes. A flat file Lookup follows the connected shape, but it reads the file from a stage, so it needs a stage path mapping.

Update Strategy¶

An Update Strategy changes the Target’s write statement instead of adding a CTE of its own. A DD_UPDATE strategy becomes a MERGE that matches on the target’s primary key:

--** SSC-FDM-INF0019 - THE UPDATE STRATEGY LOGIC WAS MOVED TO THE TARGET MODEL. **
MERGE INTO YOUR_DB.YOUR_SCHEMA.us_upd_tgt AS tgt
USING tmp_sq_us_upd_src AS src ON tgt.order_id = src.order_id
WHEN MATCHED THEN
   UPDATE SET
      tgt.customer_name = src.customer_name,
      tgt.amount = src.amount;

A DD_DELETE strategy becomes a DELETE of the target rows whose key matches an incoming row. The SSC-FDM-INF0019 note records that the strategy’s logic now lives in the target write statement rather than in a transformation of its own.

A complete example¶

The following Mapping reads a source table, passes the columns through an Expression, and writes them to a target.

Informatica (m_SimpleMapping):

<SOURCE NAME="SRC_TABLE" DATABASETYPE="Microsoft SQL Server" OWNERNAME="dbo">
   <!-- ID (primary key), NAME source fields omitted -->
</SOURCE>
<TARGET NAME="TGT_TABLE" DATABASETYPE="Microsoft SQL Server">
   <!-- ID (primary key), NAME target fields omitted -->
</TARGET>
<MAPPING NAME="m_SimpleMapping">
   <TRANSFORMATION NAME="SQ_SRC_TABLE" TYPE="Source Qualifier">
      <!-- ID, NAME ports omitted -->
   </TRANSFORMATION>
   <TRANSFORMATION NAME="EXP_Transform" TYPE="Expression">
      <TRANSFORMFIELD NAME="ID" PORTTYPE="INPUT/OUTPUT" EXPRESSION="ID"/>
      <TRANSFORMFIELD NAME="NAME" PORTTYPE="INPUT/OUTPUT" EXPRESSION="NAME"/>
   </TRANSFORMATION>
   <TRANSFORMATION NAME="TGT_TGT_TABLE" TYPE="Target Definition">
      <!-- ID, NAME input ports omitted -->
   </TRANSFORMATION>
   <!-- INSTANCE + CONNECTOR chain SRC_TABLE -> SQ_SRC_TABLE -> EXP_Transform -> TGT_TGT_TABLE omitted -->
</MAPPING>
<WORKFLOW NAME="WF_SimpleMapping">
   <SESSION NAME="s_m_SimpleMapping" MAPPINGNAME="m_SimpleMapping"/>
</WORKFLOW>

Snowflake:

CREATE OR REPLACE PROCEDURE public.m_SimpleMapping (scope VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      CREATE OR REPLACE TEMPORARY TABLE tmp_sq_src_table AS
         SELECT
            ID,
            NAME
         FROM
            YOUR_DB.dbo.SRC_TABLE;
      INSERT INTO YOUR_DB.YOUR_SCHEMA.TGT_TGT_TABLE
      WITH cte_exp_transform AS
      (
         WITH source_data AS
         (
            SELECT
               ID,
               NAME
            FROM
               tmp_sq_src_table
         )
         SELECT
            ID AS ID,
            NAME AS NAME
         FROM
            source_data
      )
      SELECT
         *
      FROM
         cte_exp_transform AS sd;
   END
$$;

The Workflow’s Task calls this procedure and forwards the scope:

CALL public.m_SimpleMapping(:scope);

Unsupported transformations¶

When a Mapping uses a transformation that the Snowflake Scripting format doesn’t convert, such as a Stored Procedure or a Java transformation, the data flow is kept connected by emitting a placeholder CTE. The placeholder selects NULL for each output port, includes the original transformation definition as comments, and is marked with SSC-EWI-INF0001:

Informatica (m_StoredProcMapping):

<TRANSFORMATION NAME="SP_Transform" TYPE="Stored Procedure">
   <TRANSFORMFIELD NAME="ID" PORTTYPE="INPUT/OUTPUT" EXPRESSION="ID"/>
   <TRANSFORMFIELD NAME="NAME" PORTTYPE="INPUT/OUTPUT" EXPRESSION="NAME"/>
</TRANSFORMATION>

Snowflake:

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0001 - INFORMATICA POWERCENTER TRANSFORMATION IS NOT SUPPORTED BY SNOWCONVERT ***/!!!
cte_sp_transform AS
(
   -- Unsupported transformation 'SP_Transform' (UnsupportedTransformation) — no inline scripting translator registered for this type
   --
   --<TRANSFORMATION NAME="SP_Transform" TYPE="Stored Procedure" ... >
   --  ...
   --</TRANSFORMATION>
   --
   SELECT
      null AS ID,
      null AS NAME
)

Downstream transformations still reference this CTE by name, so the rest of the procedure is generated normally. Convert the flagged transformation manually, or generate the Mapping in the dbt output format, which supports it today.