Make Exists transformation in Azure Data Factory make sense please?

Question

I am working in ADF and have had a very difficult time adjusting from proper logical coding to...this
I am trying to filter out course numbers that don't exist in our system for an update on attendance roll call stored in our SQL managed instance
In the images attached, you will see the academic year (acad_yr), term, and course number (crse_nbr) from out internal DB and the input from our API call.
we can clearly see matching acad_yr, term and course number but the exists transformation completely overlooks it and returns if i change exists to not exists

Answer 1

@Richard Carballo

Thanks for using MS Q&A platform and posting your query.

The issue you're facing with the Exists transformation in Azure Data Factory (ADF) seems to be related to how the matching condition is defined. Here are some things to check based on the image you sent:

Match Columns: Make sure the columns you've selected for comparison in the Exists transformation exactly match between the left and right inputs. In the image, it looks like you are comparing acad_yr, term, and crse_nbr from both sides. Double-check that there are no typos or case-sensitivity issues in the column names.

Null Values: If any of the columns involved in the matching condition can contain null values, the Exists transformation might not be working as expected. By default, null values don't match other values, even nulls. You might need to explicitly handle nulls in your matching condition using an expression like ISNULL(left_column, '') == ISNULL(right_column, '').

Custom Expression: Consider using a custom expression in the Exists transformation to define a more complex matching logic, especially if a simple column comparison isn't sufficient.

Here are some additional resources that you might find helpful:

• Microsoft Documentation on Exists Transformation: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-exists
• Blog post on using Exists Transformation: https://m.youtube.com/watch?v=eIcx2_Gnmkw

Hope this helps. Do let us know if you any further queries.