SQL ELT optimization

You can use SQL ELT optimization in mapping tasks to push transformation logic to a source or target endpoint for execution. SQL ELT optimization applies to tasks that read from and write to relational databases or cloud data warehouses.

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.

Note: SQL ELT optimization functionality varies depending on the support available for the connector. For more information, see the help for the appropriate connector.

SQL ELT optimization in advanced mode

You can run a mapping in advanced mode using full SQL ELT optimization to push all transformation logic to the source and target databases.

If full SQL ELT optimization isn't possible, Data Integration runs the mapping logic on the Data Integration Server and on an advanced cluster.

The following table describes the log files that you can use to troubleshoot the job:

Log file	Description
Session log	Indicates whether SQL ELT optimization is possible.
Mapping compilation log	Displays the SQL query that runs on the database.
Spark driver log	Displays the SQL query that runs on the database and the individual entries that track job progress on the database.