Using Lookup in ADF Data Flow

Introduction

SSIS developer, we all know the importance of Lookup Transform. Here in ADF, we have two types of Lookup. One is for control flow and other is for data flow. The behavior of both the lookup is not same. Control flow lookup provide only record sets but in Data flow we are using it as for looking up some records from other data sets.

In ADF data flow Lookup feature is more than the SSIS Lookup Transform. So, in this article we are thinking to demonstrate all the functionality of ADF Data Flow lookup with a simple example.

 

Hope it will be interesting.

 

Case scenario

We have a Flat file which contains Employee Code, Salary and Bonus Information. There are multiple Flat files in our Azure BLOB Container.

Now we have a JSON file which contains information about Employee Code, Employee Name and Employee Email.

Now we have to use the Lookup Transform to find the Employee Name and Employee Email from our JSON file.

 

About Data Flow Lookup

Before moving to our actual data flow solution, we must need to understand every property of Data Flow Lookup.

 


 

Primary stream 

The incoming stream of data. This stream is equivalent to the left side of a join.

Lookup stream

The data that is appended to the primary stream. Which data is added is determined by the lookup conditions. This stream is equivalent to the right side of a join.

 

Match multiple rows 

If enabled, a row with multiple matches in the primary stream will return multiple rows. Otherwise, only a single row will be returned based upon the 'Match on' condition.

 

Match on 

Only visible if 'Match multiple rows' is not selected. Choose whether to match on any row, the first match, or the last match. Any row is recommended as it executes the fastest. If first row or last row is selected, you'll be required to specify sort conditions.

 

Lookup conditions: 

Choose which columns to match on. If the equality condition is met, then the rows will be considered a match. Hover and select 'Computed column' to extract a value using the data flow expression language.

 

Non-equi joins

To use a conditional operator such as not equals (!=) or greater than (>) in your lookup conditions, change the operator dropdown between the two columns. Non-equi joins require at least one of the two streams to be broadcasted using Fixed broadcasting in the Optimize tab.

 

 

 

ADF Solution Data Flow

Here we are not using any Sync as we are going to display output in Data Preview mode.

 


 

Source – 1

 


 

Source – 2

 


 

 

Flatten Transform

 


 

Lookup

 


 

Select with Data Preview

 


 


 

 

Hope you like it.

 


Comments

  1. SQL Knowledge Bank - Azure13 June 2020 at 09:41

    If you have any confusion between == and === operators, here I give you a simple explanation. == check only values and === checks values snd type. Take a simple example:
    If(12 == '12') :
    pint( 'Yes')
    else:
    print('No')
    ---> Yes

    If(12 === '12') :
    pint( 'Yes')
    else:
    print('No')
    ---> No

    Hope you understand that.

    ReplyDelete

Post a Comment

Popular Posts

Activity Dependencies with Logical AND in ADF

Image
Introduction In this article we are going to discuss about Activity Dependencies at ADF Control flow. As a SSIS developer we all have Experience about Precedence Constrains and how useful it is when building SSIS control flow. In ADF we have the similar concept with some limitation. Depends on this, we have to design our control flow in ADF. Hope it will be interesting.   Precedence Constraints in SSIS Before moving further, we have to review our SSIS Precedence Constraints.   Here we have: 1.       Constraint – Success, Failure, Completion 2.       Expression 3.       Logical AND / OR   It is really flexible.   Activity Dependency in ADF     In ADF Activity Dependencies provides only Logical AND not any Logical OR. It has Add Activity on: 1.       Success 2.       Failure 3.       Completion 4.       Skipped   ADF Control Flow Wrong Design Pattern     In the above control flow design patter, the Stored Procedure Activity is not execute
Read more

Vertipaq Engine – Column & Segment Elimination

Image
  Introduction As we are discussing about the power of Vertipaq Engine, in this post we are going to discuss about another beautiful technique that Vertipaq Engine maintain to make our query faster and that is Column Elimination and Segment Elimination.   Hope it will be interesting. Case Study We have a table which is imported into Vertipaq engine. Table Name: Order_Details   Column Elimination As Vertipaq Engine stores data as a columner fashion so it’s looks like.   Now we need to fetch data for Customer ‘ABC Company’ and want to know the Total Quntity the specified company purchased. For that we need only columns named Customer and Qty. All others columns are simply eliminated. It actually limit the data by eleminating the columns and called columns elemenation. Segment Elimination This is done by horizontal Partitioning.   For Power Pivot it takes 1 million per partition and for SSAS Tabular it takes 8 million per Partition. Now how it works.
Read more