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 sql()
function.
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.