Make Exists transformation in Azure Data Factory make sense please?

Richard Carballo 0 Reputation points
2024-05-24T13:52:57.8833333+00:00

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 existsScreenshot 2024-05-24 094251

Screenshot 2024-05-24 094311

Screenshot 2024-05-24 094354

Screenshot 2024-05-24 094431

Screenshot 2024-05-24 094613

Screenshot 2024-05-24 094631

Screenshot 2024-05-24 094643

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,870 questions
{count} votes

1 answer

Sort by: Most helpful
  1. phemanth 7,190 Reputation points Microsoft Vendor
    2024-05-27T05:57:49.47+00:00

    @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:

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

    0 comments No comments