Pushdown Optimization Preview in Informatica Cloud (IICS)

Introduction

In our previous article, we have discussed about Pushdown Optimization feature in Informatica Cloud, its different types and advanced options associated with it. In the earlier versions of Informatica cloud, it is not possible to identify if a mapping supports pushdown optimization until it is run. A bit of trial and error is required to completely identify the issues in the mapping which is preventing it from optimized for push down.

This issue is resolved with the introduction of the Pushdown Optimization Preview feature. Let us discuss in detail how this feature works with a real time example.

Pushdown Optimization Preview

Pushdown Optimization Preview helps in identifying the SQL to be executed and issues if any in the mapping that is preventing it from being configured for Pushdown Optimization. To preview pushdown optimization results, run the Pushdown Preview Job which creates and runs a temporary preview mapping task which displays the SQL to be executed and any warnings in the Pushdown Optimization panel.

Consider the below simple mapping for the demonstration of the Pushdown preview feature.

• The mapping reads data from the Snowflake table Employees as source.
• A Lookup transformation is introduced to get the Department Name of the employees based on the Department ID field.
• In the Expression transformation a new field FullName is created which is a concatenation of first name and last name fields.
• Finally the data is loaded into another Snowflake table Employee_Details with an Upsert operation on Employee_Id field.

Running a Pushdown Preview Job

Running a Pushdown Preview job provides the preview of SQL queries that Data Integration pushes to the database and any warnings if any associated with it.

Follow below steps to run a Pushdown Preview job.

1. Open the Pushdown Optimization panel on the top right corner of the mapping designer page.

2. Click Preview Pushdown button.

3. Select the runtime environment in the pushdown preview wizard and click Next.

4. In the Pushdown Options page of the pushdown preview wizard, select the Pushdown Optimization options.

5. Click the Pushdown Preview button.

Understanding Pushdown Preview Job Results

After the Pushdown Preview job is completed, the results are displayed in the Pushdown Optimization Panel. Let us understand the each of these result components.

Pushdown SQL

The SQL¹ query that is built on the transformation logic of the mapping that that Data Integration pushes to the database is displayed in the Pushdown Optimization Panel as shown in the below image.

Pushdown Transformations List

The transformations that are part of the SQL generated for Pushdown Optimization can be viewed from View List of transformations² for the group. To highlight the transformations part of the SQL on the mapping canvas, click Highlight transformations³ for the group you want to see.

Pushdown Warnings

The Warnings if any that are preventing the mapping to be configured for Pushdown Optimization are displayed under General Warnings section.

The below image is an example which shows the it is not possible to enable pushdown optimization on the mapping used for demonstration because the Lookup transformation is not configured to use all values or report error when it encounters multiple matches.

Pushdown Data Preview Results

Once the SQL is generated after running the Pushdown preview job, the first 15 rows of the can be previewed in the Pushdown Optimization panel by running data preview job. When you run a pushdown optimization data preview job, Data Integration creates a temporary mapping task that contains a virtual target immediately before the target or the last transformation possible for pushdown optimization.

To preview the data of the pushdown optimized SQL, follow below steps.

1. Click Run Data Preview⁴ button.

2. Once the job is completed, click on View Data Preview to view the sample records generated by the query.

3. The preview records can also be downloaded as a CSV file from the data preview wizard by clicking download button.

The preview results are also stored in the following directory in the secure agent installed machine. Data Integration purges the directory once every 24 hours and the files that are more than 24 hours old are deleted.

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

Subscribe to our Newsletter !!

Related Articles: