how does Fuzzy logic works in Azure Data Factory?

Question

how does Fuzzy logic works in Azure Data Factory?

Vemasani Chethana 1

I have been going through Fuzzy Logic with sample data. I have done fuzzy matching with name and address in ADF using Fuzzy Logic. I was trying to understand the process behind the fuzzy logic in Azure Data Factory. How the matching similarity is calculated?

There are some situations occurred during process:

Some records were missing while performing fuzzy logic using ADF.
Even it has same matching data for name and address it shows "N/A" for the similarity in ADF.

Can anyone explain or suggest any brief explained document?

Thank you in advance.

MartinJaffer-MSFT 26,236 Reputation points

2022-12-20T03:44:16.577+00:00

Hello @Anonymous and welcome to Microsoft Q&A.

If I understand correctly, you are in Data Flow, Join transformation, using the Fuzzy Matching option.
You want to know the details of similarity calculation.

I'll need to do some tests or inquire internally for the full explanation, but I do have some to start.
In the video about fuzzy matching, the example used, Roger vs Rogers got a score of 83%. If we take a ratio of the length of text, 5 / 6 = 83.333%.
So it appears the similarity score is number of characters matched divided by total characters.
In the above case, all characters were in matching order, only 1 was missing.

What type of join are you using? If inner join, then records with low similarity score would be dropped, as they are not a match.

Can you give a couple examples that yield "N/A" ? And a couple that were dropped? I'm planning to put together a set of test data.
Vemasani Chethana 1 Reputation point

2022-12-21T12:45:42.743+00:00

Hi @MartinJaffer-MSFT ,

Thanks for the clarification. We used Left join in ADF using Fuzzy Logic.

Here are the few examples for your reference.

Thank you in Advance.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T22:38:26.58+00:00

@Anonymous I assume the Source columns are the left table and Target columns are the right table in the join.

I'm not sure what is generating the Target Name_Fuzzy Match and Target Address_Fuzzy Match columns. It looks to me like it is a best-effort amalgamation of the Source and Target values, seen in "Rainbow HOSP". "Rainbow" is from the Target, and "HOSP" is from the Source, just based upon capitalization.

Based upon this assumption I'm thinking the #N/A is when the Source and Target are so unlike each other, there is no reconciliation, as in the case of "RAINBOW HOSPITAL" vs "8763 linger Rd". The Match score for those two is undoubtedly far below the minimum 60% similarity threshold.

That said, the results are incomplete. When "Rainbow Hospital" matched, except for capitalization, why did the result leave off "ITAL" in "HOSPITAL"? The same for "St" in "Sloth St".

Could you please explain how you have set up to generate those 2 columns? It looks like from something other than Data Factory, but I assume that is just for ease of format/display.

I'll take these and plug them into my Data Flow.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T23:12:31.177+00:00

So I typed up you data, and plugged it in. The data preview took a long time. Here's what I got @Anonymous .

I expected more to match, but looking closer, I think it is that the 2 match conditions are AND'ed together. Rainbow Hospital may be a good match in Name, but not in Addr.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T23:35:58.403+00:00

@Anonymous I jsut noticed Power Query has Table.FuzzyJoin . Have I totally misunderstood what we are working on? Are you using Mapping Data Flow, or Power Query?
MartinJaffer-MSFT 26,236 Reputation points

2022-12-22T01:53:52.793+00:00

Well I did find out for the scoring, @Anonymous

The FuzzyJoin algorithm was developed by the Microsoft Research Team, and is the same algorithm used by Power Query's fuzzy join except the ADF version runs as scale on Spark.

Here is a related published paper.
https://dl.acm.org/doi/10.1145/3448016.3452824

I was expecting just a simple distance function, but the paper seems to be much more.

I am also told the distance function is jaccard-similarity.

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-12-20T03:44:16.577+00:00

Hello @Anonymous and welcome to Microsoft Q&A.

If I understand correctly, you are in Data Flow, Join transformation, using the Fuzzy Matching option.
You want to know the details of similarity calculation.

I'll need to do some tests or inquire internally for the full explanation, but I do have some to start.
In the video about fuzzy matching, the example used, Roger vs Rogers got a score of 83%. If we take a ratio of the length of text, 5 / 6 = 83.333%.
So it appears the similarity score is number of characters matched divided by total characters.
In the above case, all characters were in matching order, only 1 was missing.

What type of join are you using? If inner join, then records with low similarity score would be dropped, as they are not a match.

Can you give a couple examples that yield "N/A" ? And a couple that were dropped? I'm planning to put together a set of test data.
Vemasani Chethana 1 Reputation point

2022-12-21T12:45:42.743+00:00

Hi @MartinJaffer-MSFT ,

Thanks for the clarification. We used Left join in ADF using Fuzzy Logic.

Here are the few examples for your reference.

Thank you in Advance.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T22:38:26.58+00:00

@Anonymous I assume the Source columns are the left table and Target columns are the right table in the join.

I'm not sure what is generating the Target Name_Fuzzy Match and Target Address_Fuzzy Match columns. It looks to me like it is a best-effort amalgamation of the Source and Target values, seen in "Rainbow HOSP". "Rainbow" is from the Target, and "HOSP" is from the Source, just based upon capitalization.

Based upon this assumption I'm thinking the #N/A is when the Source and Target are so unlike each other, there is no reconciliation, as in the case of "RAINBOW HOSPITAL" vs "8763 linger Rd". The Match score for those two is undoubtedly far below the minimum 60% similarity threshold.

That said, the results are incomplete. When "Rainbow Hospital" matched, except for capitalization, why did the result leave off "ITAL" in "HOSPITAL"? The same for "St" in "Sloth St".

Could you please explain how you have set up to generate those 2 columns? It looks like from something other than Data Factory, but I assume that is just for ease of format/display.

I'll take these and plug them into my Data Flow.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T23:12:31.177+00:00

So I typed up you data, and plugged it in. The data preview took a long time. Here's what I got @Anonymous .

I expected more to match, but looking closer, I think it is that the 2 match conditions are AND'ed together. Rainbow Hospital may be a good match in Name, but not in Addr.
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T23:35:58.403+00:00

@Anonymous I jsut noticed Power Query has Table.FuzzyJoin . Have I totally misunderstood what we are working on? Are you using Mapping Data Flow, or Power Query?
MartinJaffer-MSFT 26,236 Reputation points

2022-12-22T01:53:52.793+00:00

Well I did find out for the scoring, @Anonymous

The FuzzyJoin algorithm was developed by the Microsoft Research Team, and is the same algorithm used by Power Query's fuzzy join except the ADF version runs as scale on Spark.

Here is a related published paper.
https://dl.acm.org/doi/10.1145/3448016.3452824

I was expecting just a simple distance function, but the paper seems to be much more.

I am also told the distance function is jaccard-similarity.

Share via

how does Fuzzy logic works in Azure Data Factory?

Your answer