There are different ways in which to perform the lookup of a record from another table in Data Services. The three most popular ones are: a table join with a Query transform, using the
lookup_ext() function, and using the
In this recipe, we will take a look at all these methods and discuss how they affect the performance of ETL code execution and their impact on a database used to source data from.
We will be using the same dataflow as in the first recipe, the one which populates the
PERSON_DETAILS stage table from multiple OLTP tables.
We will perform a lookup for the
PHONENUMBER column of a person from the OLTP table
PERSONPHONE in three different ways.