For relational endpoints, SQL ELT optimization can push transformation logic to either source or target endpoints within the same ecosystem. For example, you need to migrate customer transaction data from an Oracle database on AWS to another Oracle instance within the same AWS cloud ecosystem.

For cloud data warehouse endpoints, both the source and target can reside within the same ecosystem, or the source can be a data lake in a compatible hyperscaler. For example, you need to move data from Azure Data Lake Storage data lake to your Snowflake cloud data warehouse, and you want all the data processing to occur within the Snowflake ecosystem.

You can select the SQL ELT optimization type in the runtime options of the task to either Full or Source. In Full SQL ELT optimization, you can decide whether to let the Data Integration Server process any transformation logic it can't push to the endpoint, run the task without SQL optimization, or fail the task. In Source SQL ELT optimization, the Data Integration Server processes any transformation logic that the source can't handle. You can also configure the task to create temporary views in the database, along with other available options.

When you run a task enabled with SQL ELT optimization, the task converts the transformation logic to an SQL query, which is sent to the endpoint for execution. The performance of the task improves because data processing leverages the endpoint resources within the underlying cloud infrastructure. The ability to push transformation logic to the endpoint depends on the database, transformation logic, and task configuration.

SQL ELT Optimization

advanced session properties aren't displayed for mapping tasks that are based on mappings in SQL ELT mode because mappings in SQL ELT mode are automatically configured to push transformation logic to the cloud data warehouse.