Informatica PowerCenter - Snowflake Scripting output¶

This page describes the Snowflake Scripting output format for Informatica PowerCenter. In this format, each Mapping is converted into a Snowflake stored procedure and each Workflow into a Task graph that calls those procedures. This is an alternative to the generally available dbt output format, where each Mapping becomes a dbt project instead.

Warning

The Snowflake Scripting output format is in active development, and the generated code may change between releases. For production migrations, use the generally available dbt output format. Review every generated procedure before you run it.

The procedure model¶

Each Mapping becomes one Snowflake stored procedure. The procedure is named public.m_<Mapping>, with the m_ prefix added unless the Mapping name already starts with it. The procedure takes a single scope parameter, which carries the variable scope used to resolve variables and parameters at runtime:

CREATE OR REPLACE PROCEDURE public.m_load_customers (scope VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      -- The Mapping data flow becomes the procedure body.
   END
$$;

Inside the body, The data flow is built from the Mapping’s transformations: each Source Qualifier becomes a temporary table, each Expression becomes a common table expression (CTE) or a temporary table, and the Target becomes an INSERT statement. For the details and a complete worked example, see Mappings and transformations.

Supported transformations¶

Most data-flow transformations are now converted in this format:

Transformation	Status	Notes
Source Definition	Available	Supplies table metadata. Emits no SQL of its own.
Source Qualifier	Available	Becomes a temporary table.
Expression	Available	Becomes a CTE, or a temporary table when reused downstream.
Filter	Available	None
Router	Available	None
Joiner	Available	None
Union	Available	None
Aggregator	Available	Both grouped and ungrouped aggregation.
Sorter	Available	Includes the distinct (remove duplicates) option.
Sequence Generator	Available	Reusable and non-reusable.
Normalizer	Available	None
Lookup (connected)	Available	None
Lookup (unconnected)	Available	Becomes a Snowflake scalar UDF.
Lookup (flat file)	Available	Requires a stage path mapping.
Mapplet	Available	Becomes its own procedure. Reusable, passive, and self-sourcing forms are converted.
Update Strategy	Partially available	Converted for the dispatch shapes described in Mappings and transformations. Other shapes fall back to the dbt output.
Target Definition	Available	Becomes the write statement.
Stored Procedure	Not yet available	Converted in the dbt output format.
Java	Not yet available	Converted in the dbt output format.
Flat File Source, Flat File Target	Not yet available	Converted in the dbt output format. Flat file Lookup is available.

When a transformation isn’t converted in this format, a placeholder marked with an EWI code is emitted so you can convert it manually. See Mappings and transformations for how each supported transformation is generated and how unsupported ones are handled.

For these transformations in the dbt output format, see dbt mappings and transformations.

Orchestration¶

Workflow orchestration is the same for both output formats. A Workflow becomes a graph of Snowflake Tasks, and the only difference is how a Session runs its Mapping: in the Snowflake Scripting format, the Session’s Task calls the Mapping procedure and forwards the variable scope:

CALL public.m_load_customers(:scope);

For the full orchestration reference (Sessions, Worklets, and the Decision, Assignment, and Email tasks), see Workflows and orchestration.

Shared references¶

Several references apply to both output formats, because the generated Snowflake expressions and the orchestration are the same regardless of format:

Workflows and orchestration: how Workflows become Snowflake Task graphs.
Variables and parameters: the control_variables table, parameter files, and variable scope.
Expression functions: Informatica functions and their Snowflake equivalents.
Data types: how PowerCenter data types map to Snowflake types.

For the concept map and the full supported-component matrix, see the Informatica PowerCenter overview.