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. Pre-SQL and post-SQL on that Source Qualifier become EXECUTE IMMEDIATE statements around the table. See Pre-SQL and post-SQL.
4. Each intermediate transformation becomes a common table expression (CTE), or a temporary table when its result feeds more than one downstream transformation.
5. A Mapplet instance first materializes its input as tmp_mplt_in_cte_<instance>, then either reads the Mapplet procedure inline with TABLE(...) or breaks the pipeline with CALL plus RESULT_SCAN. See Mapplets.
6. 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. Pre-SQL and post-SQL on the Target run around that write statement.

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 are inserted 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.

Mapplets¶

A Mapplet becomes its own result-set stored procedure, separate from the procedures of the Mappings that use it, so a Mapplet shared by several Mappings is converted once. Mapping procedures stay public.m_<Mapping>. Mapplet procedures use a hashed, folder-qualified name such as ScriptingCases_AnActiveMapplet_eeb91c86, because Informatica allows the same Mapplet name in different folders while Snowflake puts every procedure in one schema.

Input Transformation and Output Transformation objects inside a Mapplet are the Mapplet’s ports. They aren’t rows in the 34-name transformation catalog.

The Mapplet procedure takes an in_table VARCHAR parameter, reads the caller’s table through IDENTIFIER(:in_table), and returns the Output Transformation’s exposed ports:

CREATE OR REPLACE PROCEDURE public.ScriptingCases_AnActiveMapplet_eeb91c86 (in_table VARCHAR)
RETURNS TABLE (
   i11 VARCHAR(10),
   i21 NUMERIC(10, 0)
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      res RESULTSET;
   BEGIN
      res := (
      WITH cte_input AS
      (
         SELECT
            CAST(LEFT(i1, 10) AS VARCHAR(10)) AS i1,
            CAST(LEFT(i2, 10) AS VARCHAR(10)) AS i2
         FROM
            IDENTIFIER(:in_table)
      ),
      cte_internalfilter AS
      (
         WITH source_data AS
         (
            SELECT
               i1,
               i2
            FROM
               cte_input
         )
         SELECT
            i1,
            i2
         FROM
            source_data
         WHERE
            i2 > 10
      )
      SELECT
         CAST(LEFT(i1, 10) AS VARCHAR(10)) AS i11,
         CAST(i2 AS NUMERIC(10, 0)) AS i21
      FROM
         cte_internalfilter
         );
      RETURN TABLE(res);
   END;
$$;

cte_input is name-bound: it maps the Input Transformation’s port names onto columns of the caller’s table, and it casts each port to the declared type. The body is wrapped in the DECLARE res RESULTSET; ... RETURN TABLE(res) scaffold that Snowflake requires to return a result set.

The calling Mapping materializes those input rows as tmp_mplt_in_cte_<instance> and reads the procedure with TABLE(...):

CREATE OR REPLACE TEMPORARY TABLE tmp_mplt_in_cte_anactivemapplet AS
   SELECT
      i1 AS i1,
      i2 AS i2
   FROM
      tmp_sq_activemappletsource;
INSERT INTO YOUR_DB.YOUR_SCHEMA.ActiveMappletTarget (i11, i21)
WITH cte_anactivemapplet AS
(
   SELECT
      *
   FROM
      TABLE(public.ScriptingCases_AnActiveMapplet_eeb91c86('tmp_mplt_in_cte_anactivemapplet'))
)
SELECT
   sd.i11 AS i11,
   sd.i21 AS i21
FROM
   cte_anactivemapplet AS sd;

Call sites that pass scope¶

The previous example is the inline form, where the Mapplet composes as a CTE. When the Mapplet procedure takes a scope parameter, Snowflake rejects :scope inside FROM TABLE(), so the converter emits a pipeline break instead: a CALL as a pre-hook, then TABLE(RESULT_SCAN(LAST_QUERY_ID())):

CREATE OR REPLACE TEMPORARY TABLE tmp_mplt_in_cte_mappletwithvariables AS
   SELECT
      ROW_NUMBER()
      OVER (
      ORDER BY
         null) AS __row_id,
      EmpName AS i_1,
      DeptCode AS i2
   FROM
      tmp_sq_tbg_employees;
CALL public.ScriptingCases_MappletWithVariables_853af6f1('tmp_mplt_in_cte_mappletwithvariables', :scope);
INSERT INTO YOUR_DB.YOUR_SCHEMA.D_TABLE_2 (TXT)
WITH cte_mappletwithvariables AS
(
   SELECT
      *
   FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT
   sd.o_out AS TXT
FROM
   cte_mappletwithvariables AS sd;

A scope VARCHAR parameter is added to the Mapplet procedure only when the Mapplet references a control variable. The pipeline-break form is also used when the Mapplet body runs DDL or DML before its final query, because Snowflake rejects those statements inside FROM TABLE().

No-input mapplets¶

A Mapplet that contains its own Source Qualifier has no input boundary, so its procedure takes no parameters and the call site passes no arguments, including no :scope:

CREATE OR REPLACE PROCEDURE public.ScriptingCases_MappletNoInput_44cf2690 ()
RETURNS TABLE (
   EmpId NUMERIC(10, 0),
   EmpName VARCHAR(100),
   DeptCode VARCHAR(10),
   UpdatedSalary NUMERIC(10, 2)
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      res RESULTSET;
   BEGIN
      res := (
      WITH cte_sq_tbg_employees AS
      (
         SELECT
            EmpId,
            EmpName,
            DeptCode,
            Salary
         FROM
            YOUR_DB.dbo.Tbg_Employees
      ),
      cte_incrementsalaries AS
      (
         SELECT
            EmpId,
            EmpName,
            DeptCode,
            Salary + 2 AS UpdatedSalary
         FROM
            cte_sq_tbg_employees
      )
      SELECT
         CAST(EmpId AS NUMERIC(10, 0)) AS EmpId,
         CAST(LEFT(EmpName, 100) AS VARCHAR(100)) AS EmpName,
         CAST(LEFT(DeptCode, 10) AS VARCHAR(10)) AS DeptCode,
         CAST(UpdatedSalary AS NUMERIC(10, 2)) AS UpdatedSalary
      FROM
         cte_incrementsalaries
         );
      RETURN TABLE(res);
   END;
$$;

INSERT INTO YOUR_DB.YOUR_SCHEMA.EmpTarget (EmpId, EmpName, DeptCode, UpdatedSalary)
WITH cte_mappletnoinput AS
(
   SELECT
      *
   FROM
      TABLE(public.ScriptingCases_MappletNoInput_44cf2690())
)
SELECT
   sd.EmpId AS EmpId,
   sd.EmpName AS EmpName,
   sd.DeptCode AS DeptCode,
   sd.UpdatedSalary AS UpdatedSalary
FROM
   cte_mappletnoinput AS sd;

Passive mapplets¶

A passive Mapplet returns one row for each row you pass in, and the enclosing Mapping usually needs columns that bypass the Mapplet. Those carry columns are reunited with a converter-synthesized __row_id. Passive cte_input reads that surrogate as the first positional column ($1 AS __row_id) and reads the Input Transformation ports by name:

WITH cte_input AS
(
   SELECT
      $1 AS __row_id,
      CAST(LEFT(i_1, 10) AS VARCHAR(10)) AS i_1,
      CAST(LEFT(i_21, 10) AS VARCHAR(10)) AS i_21
   FROM
      IDENTIFIER(:in_table)
)

The call site materializes __row_id on tmp_mplt_in_cte_<instance> and left-joins the Mapplet result back to the carry columns:

CREATE OR REPLACE TEMPORARY TABLE tmp_mplt_in_cte_passivemapplet AS
   SELECT
      ROW_NUMBER()
      OVER (
      ORDER BY
         null) AS __row_id,
      Salary AS i_1,
      o_cal AS i_21,
      SQ_Tbg_Employees__Id,
      SQ_Tbg_Employees__Name,
      SQ_Tbg_Employees__DName
   FROM
      tmp_cte_exptrans;
INSERT INTO YOUR_DB.YOUR_SCHEMA.EmpTarget (Id, Name, DName, Salary)
WITH cte_passivemapplet AS
(
   SELECT
      m.o_calc AS o_calc,
      m.i_11 AS i_11,
      i.SQ_Tbg_Employees__Id AS SQ_Tbg_Employees__Id,
      i.SQ_Tbg_Employees__Name AS SQ_Tbg_Employees__Name,
      i.SQ_Tbg_Employees__DName AS SQ_Tbg_Employees__DName
   FROM
      tmp_mplt_in_cte_passivemapplet AS i
      LEFT JOIN
         TABLE(public.ScriptingCases_PassiveMapplet_4065eabc('tmp_mplt_in_cte_passivemapplet')) AS m
         ON m.__row_id = i.__row_id
)
SELECT
   sd.SQ_Tbg_Employees__Id AS Id,
   sd.SQ_Tbg_Employees__Name AS Name,
   sd.SQ_Tbg_Employees__DName AS DName,
   sd.o_calc AS Salary
FROM
   cte_passivemapplet AS sd;

The carry columns are aliased <source_qualifier>__<port> so they can’t collide with the Mapplet’s own output ports. __row_id doesn’t appear in your PowerCenter metadata.

Multiple inputs or outputs¶

A Mapplet with more than one Input Transformation or Output Transformation isn’t converted to a procedure. The enclosing Mapping emits the same SSC-EWI-INF0001 placeholder shown in Unsupported transformations, including Unsupported transformation 'mplt_multi_io' and null AS out_a for the connected output port. No FROM TABLE(public.ScriptingCases_mplt_multi_io(...)) call site is generated, and no Mapplet procedure file is emitted.

Pre-SQL and post-SQL¶

Pre-SQL and post-SQL attached to a Source Qualifier or a Target become EXECUTE IMMEDIATE statements in the procedure body, positioned around the statement they belong to. Mapping-level hooks on a Source Qualifier run before and after the temporary table:

EXECUTE IMMEDIATE 'TRUNCATE TABLE staging.pre';
CREATE OR REPLACE TEMPORARY TABLE tmp_sq_src_table AS
   SELECT
      ID,
      NAME
   FROM
      YOUR_DB.dbo.SRC_TABLE;
EXECUTE IMMEDIATE 'UPDATE audit.log SET n = n + 1';

EXECUTE IMMEDIATE is used rather than inlining the statement because the hook is arbitrary SQL that the converter doesn’t parse as part of the data flow.

When a Target has both mapping-level and session-level Pre-SQL or Post-SQL, the session value overrides the mapping value. Only the session hook is emitted around the INSERT:

LET effective_tgt_tgt_table_pre_sql VARCHAR := COALESCE(public.GetControlVariableUDF('m_SimpleMapping_tgt_tgt_table_pre_sql', :scope) :: VARCHAR, 'TRUNCATE TABLE staging.tgt_mapping_pre');
IF (effective_tgt_tgt_table_pre_sql IS NOT NULL
AND effective_tgt_tgt_table_pre_sql <> '') THEN
   EXECUTE IMMEDIATE 'BEGIN
' || :effective_tgt_tgt_table_pre_sql || '
;
END;';
END IF;
INSERT INTO YOUR_DB.YOUR_SCHEMA.TGT_TGT_TABLE (ID, NAME)
WITH cte_exp_transform AS
(
   SELECT
      ID,
      NAME
   FROM
      tmp_sq_src_table
)
SELECT
   sd.ID AS ID,
   sd.NAME AS NAME
FROM
   cte_exp_transform AS sd;
LET effective_tgt_tgt_table_post_sql VARCHAR := COALESCE(public.GetControlVariableUDF('m_SimpleMapping_tgt_tgt_table_post_sql', :scope) :: VARCHAR, 'UPDATE audit.log SET tgt_mapping_post = CURRENT_TIMESTAMP()');
IF (effective_tgt_tgt_table_post_sql IS NOT NULL
AND effective_tgt_tgt_table_post_sql <> '') THEN
   EXECUTE IMMEDIATE 'BEGIN
' || :effective_tgt_tgt_table_post_sql || '
;
END;';
END IF

A hook that contains several statements is wrapped in its own BEGIN ... END block, and a hook that references Informatica variables is built by concatenating the variable values into the string before it runs. See Workflows and orchestration.

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.