Optimizing dataflow execution – push-down techniques
The Extract, Transform, and Load sequence can be modified to Extract, Load, and Transform by delegating the power of processing and transforming data to the database itself where the data is being loaded to.
We know that to apply transformation logic to a specific dataset we have to first extract it from the database, then pass it through transform objects, and finally load it back to the database. Data Services can (and most of the time, should, if possible) delegate some transformation logic to the database itself from which it performs the extract. The simplest example is when you are using multiple source tables in your dataflow joined with a single Query transform. Instead of extracting each table's contents separately onto an ETL box by sending multiple SELECT * FROM <table> requests, Data Services can send the generated single SELECT statement with proper SQL join conditions defined in the Query transform's FROM and WHERE tabs...