Mappings

Back Next

Transformations in mappings in SQL ELT mode

Transformations in mappings in SQL ELT mode can behave differently than transformations in other types of mappings. The transformations available also differ based on the mapping mode.

A mapping in SQL ELT mode includes only transformations that the cloud ecosystem can process. If you paste an unsupported transformation into the Mapping Designer, the mapping validation fails. For more information about the transformations you can use in a mapping in SQL ELT mode, see Transformations
.

The following types of transformations behave differently in mappings in SQL ELT mode.

There might be other behavior differences for transformations based on the connector type. For more information, see the appropriate connector guide.

Lookup transformations

Lookup transformations can behave differently in the following ways:

When you create a mapping in SQL ELT mode, you can perform a lookup using data from cloud data lakes and cloud data warehouses within the cloud ecosystem.

For example, when you configure a Google BigQuery connection in the Lookup transformation, you can choose only an Amazon S3, Google Cloud Storage, or Google BigQuery connection.

You can perform a lookup against the source or target only, so Lookup transformations always use the same connection as the source or target.

If the Lookup transformation uses the same connection as the Source transformation and you change the source connection, the mapping designer updates all Lookup transformations in the mapping so that they use the same connection as the Source transformation. Similarly, if the Lookup transformation uses the same connection as the Target transformation and you change the target connection, the mapping designer updates all Lookup transformations in the mapping so that they use the same connection as the Target transformation.

You can add multiple Lookup transformations to the mapping, but all lookups must use the same connection or connection parameter as the Source or Target transformation.

By default,

Data Integration

displays the native data type, native precision, and native scale on the

Fields

tab instead of the Informatica transformation data type, precision, and scale. You can display the transformation data type, precision, and scale by right clicking the header row and choosing the properties to display.

If you want to create a connection parameter in the Lookup transformation that is the same as the target type, you need to create the parameter in the mapping properties where you choose the target connection. For example, if you create a connection parameter in a Lookup transformation

in a mapping that loads data to Snowflake Data Cloud

, you can set the connection type to Amazon S3 or Azure Data Lake Storage Gen2. If you want to create a Snowflake connection parameter, you need to create the parameter in the mapping properties where you choose the target connection.

Sequence transformations

All Sequence transformations need to be connected directly to one downstream Expression transformation or to one target.

Transformations that include expressions

When you configure an expression in a transformation like an Aggregator, Expression, or Filter transformation, the functions you use to create the expression are the cloud ecosystem functions. For example, if you configure an expression in an Expression transformation in a mapping in SQL ELT mode, the functions listed in the expression editor are Snowflake functions, not Informatica transformation language functions.

The expression syntax must be valid in the cloud ecosystem. The expression editor doesn't validate the expression syntax. If you enter an invalid expression, the mapping fails at run time.

Mappings in SQL ELT mode

Download Guide

Watch

Comments

Cloud Data Integration Homepage