ADF Assert transformation - check if a value exists in lookup table

Question

Hi,

I have a requirement for Data flow transformation as shown in the diagram.

I need to check each values of Col2 in Main table with Col1 of lookup table. If match is found update Col2 of Main table with corresponding Col2 value in Lookup table. If match is not found then skip the row and send to blob using Assert transformation.

Can anybody please guide me how I can design it please?

Regards,

Partha

Answer 1

Hi Partha Das,

Thanks for using Microsoft Q&A forum and posting your query.

Your requirement can be achieved by following below series of steps in your data flow:

1. Have two sources MainTable (M) and LookupTable (L), and then have a Lookup transformation. In lookup transformation , have lookup condition as M.Col2==L.Col1
2. Then have an Assert Transformation, to identify which rows aren't matching. For this you will have to have Assert Type == Expect true and in the expression you can do MainTable@Col2 == LookupTable@Col1 The output will look like below where the error rows are the unmatched rows.
   
3. Then have a Derived column transformation to update the value of MainTable@Col2 with value of LookupTable@Col1 where the match is met using a case function in expression builder and will MainTable@Col2 value if not matched . And also add additional column as unMatchedRows = isError() so that this can be used in next transformation to split the Matched rows vs unmatched Rows and write them to respective sinks.
4. Then have a split transformation to split the streams based on matched and unmatched condition as below:
5. Then you can have a select transformation to select on the column that you would like to copy/update your sink data store.
6. Stream 1 will have Matched rows (M.Col2 == L.Col1) with updated MainTable@Col2 and you to update your sink based on the key value.
7. Stream 2 will have the unmatched rows and you can sink them your desired storage location.

Hope this helps.

---

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.