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. The set of supported components is incomplete, 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¶

The Snowflake Scripting output format is being rolled out incrementally. Today four data-flow transformations are converted in this format:

Transformation	Status
Source Definition	Available
Source Qualifier	Available
Expression	Available
Target Definition	Available
Filter	Not yet available
Lookup	Not yet available

Other transformations (such as Joiner and Aggregator) are also not yet converted in this format. When an unsupported transformation is encountered, 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.