SnowConvert AI - Informatica PowerCenter - Snowflake Scripting mappings and transformations¶

This page describes how SnowConvert AI converts an Informatica PowerCenter Mapping 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
$$;

SnowConvert AI builds the body 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. Each Source Qualifier becomes a CREATE OR REPLACE TEMPORARY TABLE statement.
3. Each Expression becomes a common table expression (CTE), or a temporary table when its result feeds more than one downstream transformation.
4. The Target becomes the final INSERT statement, which carries the upstream CTEs inside its source query.

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 SnowConvert AI cannot resolve the source connection, it writes 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, SnowConvert AI materializes it 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.

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 does not yet convert, SnowConvert AI keeps the data flow 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_FilterMapping):

<TRANSFORMATION NAME="FIL_Transform" TYPE="Filter">
   <TRANSFORMFIELD NAME="ID" PORTTYPE="INPUT/OUTPUT" EXPRESSION="ID"/>
   <TRANSFORMFIELD NAME="NAME" PORTTYPE="INPUT/OUTPUT" EXPRESSION="NAME"/>
   <TABLEATTRIBUTE NAME="Filter Condition" VALUE="TRUE"/>
</TRANSFORMATION>

Snowflake:

!!!RESOLVE EWI!!! /*** SSC-EWI-INF0001 - INFORMATICA POWERCENTER TRANSFORMATION IS NOT SUPPORTED BY SNOWCONVERT ***/!!!
cte_fil_transform AS
(
   -- Unsupported transformation 'FIL_Transform' (UnsupportedTransformation) — no inline scripting translator registered for this type
   --
   --<TRANSFORMATION NAME="FIL_Transform" TYPE="Filter" ... >
   --  ...
   --</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.