How does Pushdown Optimization mode work in Centerprise?

Moving data, containing millions of records, between source, ETL server, and target database can be a time-consuming process. When source and target database reside on the same server, unnecessary data movements and delays can be prevented by applying transformations to data in pushdown optimization mode.

The major difference between ETL and ELT lies in the stage at which we want to process the transformation logic, the latter of which pushes down the transformation logic to the source or target database. Centerprise integration server translates the applied transformation logic into automatically generated SQL queries. This eliminates the need for extracting data from the source, migrating it to staging tables on an ETL server for applying transformations, and then loading the transformed data on the target database. As a result, performance is significantly improved and data is readily made available to the end-users.

On the other hand, the ETL process involves mapping from the source to the destination only after the data has been transformed.

Say, for instance, you wish to migrate data from a PDF file to a database using ELT. In this case, you will extract the source data and push it to the database without applying any transformations. Here, it is important to note that the successful implementation of the process depends on the database or systems being used, the transformation logic, and a number of other factors.

Types of Pushdown Mode

There are two types of pushdown optimization modes:

1. Full pushdown optimization mode
2. Partial pushdown optimization mode

In full pushdown optimization mode, the Centerprise integration server executes the job completely in the pushdown mode. And in partial pushdown mode, the transformation logic is either pushed down to the target database or the source database, depending on the transformation logic and database provider.

Database Providers supported by Centerprise for ELT

Centerprise supports the following database providers:

1. MySQL
2. SQL
3. Oracle
4. Postgres

Verify Pushdown Mode

Certain transformation logic cannot be executed in a pushdown mode. ‘Verify Pushdown Mode’ feature in Centerprise identifies the transformation logic that can be pushed down to the source or destination database.

To learn more about Pushdown Optimization mode in Centerprise and its use cases, download the white paper Centerprise Automated Pushdown Optimization.