Create a mapping in SQL ELT mode when your source and target are in the same cloud ecosystem and you want all the mapping logic to be processed by your cloud ecosystem. For example, you need to move data in an Azure Data Lake Storage data lake to your Snowflake cloud data warehouse, and you want all the data processing to occur within Snowflake.
When you run a mapping in SQL ELT mode, the transformation logic is translated into ecosystem-specific commands and SQL statements that run in the underlying cloud infrastructure. Because data isn't moved out of the cloud infrastructure to transform it, the overall processing speed increases.
Mappings in SQL ELT mode can read data from a cloud data warehouse and write it to the same cloud data warehouse. They can also read data from a data lake in your cloud ecosystem and write it to a cloud data warehouse in the same ecosystem.
Mappings in SQL ELT mode can load data to the following targets and extract data from the following sources based on the target type:
Target
Supported sources
Amazon Redshift
- Amazon Redshift
- Amazon S3
Databricks
- Databricks
- Amazon S3
- Microsoft Azure Data Lake Storage Gen2
Google BigQuery
- Google BigQuery
- Amazon S3
- Google Cloud Storage
Microsoft Fabric Data Warehouse
- Microsoft Fabric Data Warehouse
- Microsoft Fabric Lakehouse
Snowflake Data Cloud
- Snowflake Data Cloud
- Amazon S3
- Microsoft Azure Data Lake Storage Gen2
To create a mapping in SQL ELT mode, you create a mapping and select Mapping - SQL ELT as the mapping type. You're then prompted to choose a target connection. If your organization doesn't have any connections, you're prompted to create one.
After you choose the target connection, the Mapping Designer opens.
The following image shows the Mapping Designer for a mapping in SQL ELT mode:
The mapping is automatically configured with the target connection you chose. You can add additional sources, targets, and transformations to the mapping. The transformations available in the transformation palette are transformations that the target cloud data warehouse can process. For example, Snowflake can't process the logic in an SQL transformation, so the transformation palette for mappings in SQL ELT mode doesn't include the SQL transformation.
To run a mapping in SQL ELT mode, you create a mapping task.
Sources in mappings in SQL ELT mode
Sources in mappings in SQL ELT mode can behave differently than sources in other types of mappings.
The sources can behave differently in the following ways:
•When you create a mapping in SQL ELT mode, you can read data from cloud data lakes and cloud data warehouses within the cloud ecosystem. For example, when you're loading data to Snowflake Data Cloud and you configure the source connection in the Source transformation, you can choose only an Amazon S3, Azure Data Lake Storage Gen2, or Snowflake Data Cloud connection.
•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 add multiple sources to the mapping, all sources use the same source connection or connection parameter. If you change the source connection or connection parameter in one of the sources, the Mapping Designer updates the other sources so that they all use the same connection.
•Mappings in SQL ELT mode that read data from a cloud data warehouse always write data to the same cloud data warehouse. Therefore, if the source is a Snowflake cloud data warehouse, it must use the same connection or connection parameter as the target.
•If you want to create a connection parameter 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're loading data to Snowflake Data Cloud and you create a connection parameter in the Source transformation, 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.
•If the source and target use the same connection and you change the target connection in the mapping properties, the connection in the source is automatically updated to use the same connection. If the Source transformation uses a different connection, then the source connection doesn't change when you change the target connection.
For example, if you change the Databricks connection in the mapping properties, the Databricks connection in the source is automatically updated to use the same connection. However, if the Source transformation uses an Amazon S3 or Azure Data Lake Storage Gen2 connection, then the source connection doesn't change when you change the target connection.
There might be other behavior differences for sources based on the connector type. For more information, see the appropriate connector guide.
Targets in mappings in SQL ELT mode
Targets in mappings in SQL ELT mode can behave differently than targets in other types of mappings.
The targets can behave differently in the following ways:
•When you create a mapping in SQL ELT mode, you choose the target connection first.
•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.
•You change the target connection in the mapping properties. In the Target transformation, the target connection is read-only.
•If you add multiple targets to the mapping, all targets must use the same target connection or connection parameter. If you change the target connection, the Mapping Designer updates all targets in the mapping to use the new connection.
There might be other behavior differences for targets based on the connector type. For more information, see the appropriate connector guide.
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.
Mapping behavior differences
Mapping behavior can vary based on whether the mapping is a mapping in SQL ELT mode or another type of mapping. The Mapping Designer also differs slightly.
The Mapping Designer for mappings in SQL ELT mode is separate from the Mapping Designer for other mappings. If you want to switch to or from SQL ELT mode, create a new mapping.
Mappings in SQL ELT mode differ from other mappings in the following ways:
•When you create a mapping in SQL ELT mode, you choose the target connection first. To change the target connection, click the mapping canvas and update the mapping properties. You can't change the target connection in the Target transformation.
•If you create expressions within a mapping in SQL ELT mode, you use your cloud data warehouse's native functions and expression syntax instead of Informatica functions and expression syntax. For more information about the native functions and expression syntax, see the documentation for your cloud data warehouse.
•You can't test run a mapping in SQL ELT mode from within the Mapping Designer. To run a mapping in SQL ELT mode, create a mapping task.
•Mappings in SQL ELT mode can't transform hierarchical data. Any field that contains hierarchical data is treated as a pass-through field in all transformations. If you need to transform hierarchical data, create a mapping or mapping in advanced mode.
•Mappings in SQL ELT mode can't include mapplets.
•Mappings in SQL ELT mode can't have multiple flows or pipelines. If you need to process data in multiple pipelines, create a mapping or mapping in advanced mode.
•CLAIRE recommendations and the Inventory panelaren't available in mappings in SQL ELT mode.
There might be other mapping behavior differences based on the connector type. For more information, see the appropriate connector guide.
Mapping configuration in SQL ELT mode
Use the Mapping Designer to create a mapping in SQL ELT mode.
The Mapping Designer for mappings in SQL ELT mode is different than the Mapping Designer for other types of mappings. Therefore, you can't copy mappings in SQL ELT mode to other modes, and you can't copy other types of mappings to SQL ELT mode.
Creating a mapping in SQL ELT mode
To create a mapping in SQL ELT mode, choose Mapping - SQL ELT when you create a mapping.
If you've configured a primary cloud data warehouse, the target connection is automatically set to your primary cloud data warehouse, but you can change it if you wish. If you haven't configured a primary cloud data warehouse, you'll need to choose the cloud platform when you create the mapping.
1In Data Integration, click New > Mappings > Mapping.
2Click Create.
3In the New Mapping dialog box, select Mapping - SQL ELT and then click Continue.
If you've configured a primary cloud data warehouse, the Mapping Designer opens in SQL ELT mode. The target connection is set to your primary cloud data warehouse.
4If you haven't configured a primary cloud data warehouse, perform the following steps:
aChoose your cloud platform and then click Select.
Data Integration prompts you to choose the target connection or create a new one.
bSelect a target connection.
The Target Connection list displays the target connections for the cloud platform you selected. If your organization has no target connections for the selected platform, or if you want to use a connection that doesn't appear in the drop-down list, click Add Connection to create a connection.
cClick Next.
The Mapping Designer opens in SQL ELT mode. The target connection is configured with the connection you selected.
5Configure the target object, configure the source, and add any required transformations to the mapping.
