SQL ELT query preview

When you create a mapping that is configured for SQL ELT optimization or a mapping in SQL ELT mode, you can preview the SQL queries that Data Integration pushes to the database or cloud data warehouse. Preview these queries in the SQL ELT Query panel in the Mapping Designer.

You can preview SQL ELT queries for some connector types. For more information, see the help for the appropriate connector.

When you preview SQL ELT queries, Data Integration creates and runs a temporary SQL ELT preview mapping task. When the job completes, Data Integration displays the SQL to be executed and any warnings in the SQL ELT Query panel.

The following image shows the SQL ELT Query panel:

For mappings that are configured for SQL ELT optimization, Data Integration groups SQL and warnings based on the data flow run order. After you run the SQL ELT preview job, you can preview the data after it's transformed by the transformations included in the SQL queries.

If the SQL ELT optimization type that you select isn't available, Data Integration lists the SQL queries, if any, that can be executed. For example, if you select Full SQL ELT optimization, but the target doesn't support it, Data Integration displays the SQL queries that will be pushed to the source.

You can't preview SQL ELT queries in advanced mode.

Running an SQL ELT query preview for mappings configured for SQL optimization

Preview the SQL queries that Data Integration pushes to the database on the SQL ELT Query panel.

Before you run an SQL ELT query preview, verify that the following conditions are true:

To run an SQL ELT query preview for a mapping that is configured for SQL optimization:

Data Integration displays the SQL queries and any warnings in the SQL ELT Query panel. If a warning relates to a transformation, click the warning to select the transformation on the mapping canvas. If SQL ELT optimization fails, Data Integration lists any queries generated up to the point of failure.

To view the transformations included in SQL ELT optimization, click View Transformations for the group you want to view. To highlight the group on the mapping canvas, click Highlight for the group you want to see.

You can monitor preview jobs on the My Jobs, Running Jobs, and All Jobs pages. Data Integration names the job <mapping name>_sql_elt_preview-<instance number>, for example, Mapping1_sql_elt_preview-2. You can download the session log for the preview job.

If you update the mapping after you run the preview job, the preview is no longer valid. To restart a preview job, run it again from the SQL ELT Query panel. You can't restart the job from the My Jobs, Running Jobs, or All Jobs page.

Running an SQL ELT query preview for mappings in SQL ELT mode

Preview the SQL queries that Data Integration pushes to the cloud data warehouse on the SQL ELT Query panel.

Before you run an SQL ELT query preview, verify that the mapping is valid.

To run an SQL ELT query preview for a mapping in SQL ELT mode:

Data Integration displays the SQL queries and any warnings in the SQL ELT Query panel. If a warning relates to a transformation, click the warning to select the transformation on the mapping canvas. If query generation fails at any point, Data Integration lists any queries generated up to the point of failure.

SQL ELT query preview results files

Data Integration stores preview results in a JSON file on the Secure Agent machine.

If you run a preview more than once, Data Integration overwrites the JSON file.

By default, files are stored in the following directory:

<Secure Agent installation directory>/apps/Data_Integration_Server/data/cache/pdo_preview

Files are stored in this directory unless the organization administrator changes the $PMCacheDir property for the Data Integration Server service on the Secure Agent. For more information about Secure Agent services, see the Administrator help.

Data Integration purges the directory once every 24 hours. During the purge, Data Integration deletes files that are more than 24 hours old.

SQL ELT query data preview

After you run an SQL ELT query preview for a mapping that is configured for SQL ELT optimization, you can preview the data after it is transformed by the transformation logic included in the queries. You can preview the first 15 rows of data for any group in the SQL ELT Query panel.

To preview the data, you must have the Data Integration Data Previewer role or your user role must have the "Data - preview" feature privilege for Data Integration.

When you run an SQL ELT query preview, Data Integration creates a temporary mapping task that contains a virtual target immediately before the target or the last transformation possible for SQL ELT optimization.

When you run SQL ELT query data preview, Data Integration runs a data preview job using the parameter values and session attributes that you entered when you configured the SQL ELT query preview job. The transformation that Data Integration runs data preview on depends on the type of SQL ELT optimization that is possible.

The following table describes the transformation that data preview runs on for each type of optimization:

Optimization type	Transformation
Full	Transformation immediately before the Target transformation.
Partial	Last transformation where SQL ELT optimization is possible.

You can't preview SQL ELT query data for the following transformations:

You can't preview SQL ELT query data for a mapping if the last transformation where SQL ELT optimization is possible is a Mapplet transformation. You can't preview the data for mappings in SQL ELT mode.

If you run an SQL ELT query data preview job at the same time you run a mapping data preview job in the same mapping, the job fails.

Running an SQL ELT query data preview job

To preview the data after the SQL queries are executed, run data preview. You run SQL ELT query data preview after you run SQL ELT query preview.

You can't run SQL ELT query data preview jobs for mappings in SQL ELT mode.