When you run a mapping configured for pushdown optimization, the mapping converts the transformation logic to an SQL query. The mapping sends the query to the database and then database executes the query. You can use full and source pushdown optimization for the ODBC connection type that uses Amazon ODBC Redshift drivers for mapping.

For a given mapping, the pushdown optimizer analyzes the transformation logic. If you select the full pushdown optimization to pushdown the data from the transformation to Amazon Redshift, the optimizer generates the appropriate SQL statement automatically.

When you perform an insert operation, the pushdown optimization generates the following sample transaction:

BEGIN;
CREATE TEMP VIEW PM_(…) AS SELECT (…) …
CREATE TEMP VIEW PM (…) AS SELECT (…) A JOIN (SELECT …) LEFT JOIN
INSERT INTO STG_CUSTOMER(…) SELECT (…)
INSERT INTO STG_CUSTOMER(…) SELECT(…)
DROP VIEW IF EXISTS …
DROP VIEW IF EXISTS …
COMMIT

The following lists the transformation types that you can use for the mapping configured for pushdown optimization:

Two sources in multiple pipelines
Expressions
Lookups with SQL override
Filters
Routers
Target

For more information about Amazon Redshift pushdown optimization, supported functions, and operators, see the following guides:

Amazon Redshift pushdown optimization on cloud, see
Amazon Redshift Connector User Guide
.
Amazon Redshift pushdown optimization on Big Data Management, see
PowerExchange for Amazon Redshift User Guide
.
Amazon Redshift pushdown optimization on PowerCenter, see
PowerExchange for Amazon Redshift for PowerCenter User Guide
.