Semantic Data Deduplication: HNSW, MNN, and LLM

Alright. Hi, everyone. My name is Abhijit, and I work in upgrade as a software developer on the site. I've been working on an app, that does web scraping to find the best events for your friends that recommends it to you. It will speak closer because we've been on here. Sorry. I'm done a little bit. You said you called Mike? Do you wanna use this? That's how I because I saw it. Right. Yeah. So I've been working on an app that does a lot of web scraping, to find the best events. And 1 of the problems I encountered was to look at data. So I asked the web scraper to, like, find the best restaurants in Montreal. So, like, if I run it today, it's gonna find, let's say, r Sergey, and then I run it next week, and then more likely than not, it's gonna find the same event. So I ended up having a lot of duplicate data in my database. And at first, I was trying, like, traditional methods, which kinda works for, like, simpler cases, like bar charts, for example. So we would get the edit distance, so that's how many characters you need to change in order to, get to the end result. And then if it's a low amount, you can merge the 2, records. But then you also have, records like these, like Sano De Reppan, and Ek women's exfol. So in this case, since there's no overlap, the edit distance is too big and they will not merge together. So then I figured how can we capture like the semantic meaning behind the 2 records and it led me to LOIS. So today what I'm gonna present is how to do semantic deduplication of data using L1s. I have a sample data set of some events. We can look at them here. Just trying to run it again. And you can see there's a lot of duplicates here, and, like, the titles are not exactly the same. So we have Bar George and then we have Bar George near Montreal Museum of Fine Arts. So the first thing that we're gonna do is create embeddings for our records. You can use any, embedded model that you want. It works better if it's normalized. And then after, what we can do with the embeddings is create an index. The 1 we use is the hierarchical, navigable small growth index. So this is a, similarity search index that allows you to quickly find clusters, that are similar to the, regular that you're searching for. So in this case, we have multiple, layers. So the top layer is to make big jumps in your data, and then as you go down layers, you make smaller jumps until you find the most similar record to the query that you provide. And then this is how you do it in pgvector. So I have my table and then I say create the HNSW index using vector cosine distance as the similarity measure. And then I provide some parameters which are just the default parameters, that I can go over later. So then we can use, cosine distance again using this operator to look at the clusters that are within our dataset. So here I'll just set, the cosine distance to 0 1 2, see what the cluster would look like. And this is what you would have to do to figure out, like, what the correct distance is that works for your use case. So so when I look at my data, I can see that it's matching up a cluster to, this jazz bar with Modavid, which is also a restaurant that plays jazz music, but the distance is 0.1559. And I don't really want this in my cluster because later on, we're gonna send this data to the LOB and we don't want to send extra tokens when it's not needed. So then I can adjust the full send distance to maybe a bit lower so that I can still find the other clusters but maybe exclude some records. So if I run this, then I see that it's excluded and, the clusters will go here. And then we get to the main algorithm for this. So, it works like this. We first find all the events and then pull the clusters using cosine distance like it showed. Then we use mutual nearest neighbors, which is a way to improve the quality of your data to make sure that you're not finding events that are, like, really far on the edge so that both, all records that you find have a bidirectional relationship. And then after we can just pull up all the unique notes in your table using, depth first search. And this is how I define my LLM agent. First, I have the expected output, which is the, table, the schema of the table that I'm using. And then I'm using, for the purpose of this demo, a free model, which is the NVIDIA model, through OpenRouter. And the prompt is basically just, like, I'm giving you the original record and some suspected duplicates that I found. Can you tell me which ones are duplicates so that I can work somewhere here? And then finally, this is, like, the job. So find all, events and then run the agents on it here. And finally, I'm just deleting the record search. Look at the same margin that means a single, record. So if I run this, It takes a while because I'm using the free auto. Okay. I have a backup. Yeah. So it takes a while so I have I have a backup. It kinda shows you, like, what the deviance is doing. So certifying the data for first cluster, which is, Bar Sergey. And then we are passing the original record as well as it is somewhere here. It says, disconnected duplicates. There you go. And the suspected duplicates, and then the agent is basically telling us, okay, confirm 2 duplicates, and then we're merging them. So after oh, I have, like, 30 seconds. After, we merge all the records, you can, you know, query our data again, our table, and we see that there is only, like, ID. And yeah. So, future improve improvements. So every time we run the job, it's gonna look at all your clusters. So what you could do is maybe implement, like, an update check to make sure that you're not, like, rereading clusters that you've already analyzed very fast. And also, if I was to do it in production, I'd probably use, like, Gemini, Flashlight model. So you need a model that can support, structured outputs, but it's also really fast in terms of looking per second. So, yeah, that was the original.