This article is contributed. See the original author and article here.
ADF has added the ability to now cache your data streams to a sink that writes to a cache instead of a data store, allowing you to implement what ETL tools typically refer to as Cached Lookups or Unconnected Lookups.
The ADF Data Flow Lookup Transformation performs a left outer join with a series of options to handle multiple matches and tags rows as lookup found / no lookup found. What the cached lookup enables is a mechanism to store those lookup streams in caches and access them from your expressions.
Many powerful use cases are enabled with this new ADF feature where you can now lookup reference data that is stored in cache and referenced via key lookups with different values, multiple times, without the need to specify separate Lookup transformation calls. Now you can simply use a lookup() function to grab additional specific columns as in: lookup().myColumn1.
Additionally, you can use the new function outputs() to grab an entire matrix of rows and columns from cache and iterate through an array of rows, picking your specific columns to reference.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.