Simply put, the SQL transform allows you to specify SQL statements directly inside the dataflow to extract source data instead of using imported source table objects. Technically, it has nothing to do with optimizing the performance of ETL as it is not a generally recommended practice to substitute the source table objects with the SQL transform containing hard-coded SELECT
SQL statements.
Take the dataflow used in the previous recipe and select Validation | Display Optimized SQL… to see the query pushed down to the database level. We are going to use this query to configure our SQL transform object, which will substitute all source table objects on the left-hand side of the dataflow.
On the Optimized SQL window, click on Save As… to save this push-down query to the file.
Drag-and-drop the SQL transform from Local Object Library | Transforms | Platform into your dataflow.
Now you can remove all objects on the left-hand side of the...