08-02-2017 08:00 AM
The manual is generally the first place for information:
08-02-2017 08:03 AM
In a lookup transformation, the lookup tables are loaded into memory at the start, and the main table is processed sequentially.
In a join transformation, all tables need to be sorted (often done implicitly by a SQL step) and then merged along the by variables.
Joins should be done when the contributing tables are quite large, or there is a m:n relationship.
Lookups are good for smaller lookup tables (small just meaning they should fit into memory) and 1:n relationships (lookup to main); one can do several lookups in one sequential step.
08-02-2017 03:30 PM