Forum Discussion

Teskay's avatar
Teskay
Regular Visitor
1 year ago
Solved

Dataflow Gen 2 failure with fuzzy matching

I am trying to do a left join fuzzy merge and DF Gen2 just too slow and takes hours before failing.    Started with data of over 12 million rows as the left table and right table about 1000 rows, t...
data engineering
lakehouse
notebook
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Teskay,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    It looks like you're facing performance issues with fuzzy merging in Dataflow Gen2, especially when dealing with large datasets. Fuzzy matching is inherently resource-intensive, and DF Gen2 may not be the best tool for handling such operations at scale. You can use Fabric Notebooks with PySpark which is best suited for large datasets and is optimized for distributed processing, instead of performing fuzzy matching in DF Gen2. You can also use a SQL-based approach with Soundex or Levenshtein distance as it runs faster in Lakehouse compared to DF Gen2.

    If you still want to go with fuzzy matching then, reduce the number of comparisons by preprocessing. You can apply Standardization like convert all to lowercase or remove spaces and special characters before matching or use blocking techniques for example: compare only rows starting with the same letter.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

spencer_sa's avatar
spencer_sa
Impactful Individual
1 year ago

As Anonymous says, fuzzy matching is computationally expensive - you effectively have to do a cross-join between the two tables first.
There are a few optimisations you can do - as Hammad mentions you can reduce the number of comparisons.  You can also remove exact matches (as equality is much quicker than Levenshtein) and you can also consider broadcasting the smaller table to all nodes (8GB limit).
Pyspark has both Soundex and Levenshtein built into it.

I did a basic proof of concept that took a table of 1 million random 8 character strings and another of 1000 strings and ran a filter on distances of 1 - on an F64 it took around 8 minutes (which was far faster than I had anticipated) and returned a single row (which was not what I was expecting so I'll need to convince myself it did what I wanted.)