joining two tables that share no key and never did, what's your actual approach

[u/No-Plant-5234]

Postgres 15, if that matters for the answer.

got handed a partner export to reconcile against our customer table. different ID schemes entirely, no overlap, no mapping doc, and the person who built theirs left last year.

what I did was fuzzy match on lower(trim(email)) plus last name, then eyeball a sample of a few hundred rows. it shipped. it is also clearly bad, because I picked those two fields because they were populated, not because I had any reason to think they were good discriminators. I have no idea what my false match rate is. I just know nobody has complained.

the part that bothers me is that a bad join doesn't announce itself. a wrong customer count looks exactly like a right customer count. at least a syntax error tells you something happened.

so what do people actually do here.

do you block on something cheap first to cut the comparison space down, or just run the full cross product and filter? does anyone compute a match score and hold back anything below a threshold rather than committing it? and if you do that, what threshold and did you pick it for a reason or the way I picked my fields.

[u/soxinthebox]

Sounds like you need to do comparisons of counts and distinct counts on the combination keys you are thinking to use and understand whether the repeated rows will be acceptable as is or if not, how you intend to handle them.

[u/gumnos]

The last time I had to do this, I created a new key field, then used various fuzzy-join attempts to create the actual key and populate the new key field. It was iterative (sounds like you're doing it iteratively too), getting the obvious ones first, then those that required a little massaging, then those that were ugly but automateable, and finally hand-editing the last ones I couldn't do in any sort of automated fashion.

Everything beyond the obvious+unique ones involved subject-matter experts (account managers in my case) to give the guidance/corrections/edits on what could be improved so I wasn't just guessing. I'd provide them with close-match reports (kinda cross-product'y reports like you're doing), and sometimes it involved real-world phone-calls to clients to get things cleared up (the worst-cases were linkages on common names, so "Juan Hernandez" might have a dozen matches at the client business, so the account-manager would have to use their HR feed, call each one individually and link them to the right contact)

Once I had all the legit FK values in that new column (SELECT * FROM tbl WHERE newkeycol IS NULL returns 0 rows), views/applications/reports could be updated to join on that instead, and FK constraints added to the schema to enforce the relationship into the future.

[u/No-Plant-5234 → u/gumnos]

the new key column populated iteratively is what I should have done from the start. mine leaves no artifact, its a join condition sitting in a view and you can't see what it decided or why. the account managers ringing the twelve juan hernandezes is the piece I don't have either, theres no SME on the partner side, the person who built it left. how long did the whole thing take you end to end? trying to work out whether Im looking at a week or a quarter

[u/gumnos → u/No-Plant-5234]

I worked to get most clients accurate on the initial load, but sometimes they gave me Data Of A Certain Kwality™, so it largely depend(s|ed) on a couple notable factors:

• the size of the problematic client data (the more bogus rows, the longer the effort)

• the competency of the SME (some account-managers had a knack for doing the pairing and could do things in half the time of their coworkers)

• the quality of the initial "this is my best guess" reports I could provide them with

• the clean-ness/dirtiness of the data (did they have to make dozens or hundreds of calls? Or was it fairly self-evident?)

• the client's employee-churn ("oh, that HR feed was sooo last-week, you'll need to work with this week's data which has 20% churn" 😖)

At the ideal end of the spectrum of those for normal clients (~1000–2000 records/emps), it was an hour or two of my effort up front, about a day of SME/AM effort to do cleanup in Excel, returning the updated data to me (or they could update it directly in the system), and less than an hour or two for me to push it back into the DB. On the far end, my less-than-a-day typically remained the same, but the SME/AM effort could blossom to a week or more to deal with dirtier data, their own inefficiencies, calling folks, etc.

[u/eww1991]

Hash two consistent columns (as you did emails and something else) and something else consistent, surname etc. Hand then back the list of unmatched with the question of how they want to handle the unmatched and a breakdown of %age unmatched etc. Gove them the options and get their choice in writing

[u/No-Plant-5234 → u/eww1991]

[u/BarfingOnMyFace → u/eww1991]

This is the way

[u/Hour-Measurement-835]

Hit this reconciling partner files against a customer master. Count rows matching more than one target, no labels needed, that's where the fuzzy join falls over. Shared accounts@ emails collide hard.

[u/No-Plant-5234 → u/Hour-Measurement-835]

no labels needed is the part I'd mised, every measurement I was imagining needed ground truth I don't have, so I'd basically concluded it wasn't measurable and stopped. counting how many source rows hit more than one target needs nothing but the join I already wrote and accounts@ / billing@ / info@ is a different collision from the household one someone else raised further down and those aren't one family sharing an address, they're a whole company behind one mailbox, so the multi-match count could be genuinely large on the B2B rows. Running that tonight. it's the only number I can produce this week that isn't a guess.

[u/Hour-Measurement-835 → u/No-Plant-5234]

That count won't catch the accounts@ one. Five partner rows landing on the same master row each match exactly once. It's hits per target row you want there.

[u/Imaginary__Bar → u/Hour-Measurement-835]

not because I had any reason to think they were good discriminators. I have no idea what my false match rate is. I just know nobody has complained.

If I was your boss I would be furious.

I don't mind the mismatched data, I could defend the decision of "we did the best we could", but I would really need to know the true match/mismatch rate - even an estimate - before I go into that meeting where I have to defend your shoddy work.

[u/Hour-Measurement-835 → u/Imaginary__Bar]

Sampling what matched only gets you the false positive rate. For an estimate you can defend you need a second sample off the rows that didn't join, and that's the one people skip.

[u/bigeyez]

Did you make people aware of what was happening? What you did isnt necessarily a bad way to approach it but I definitely would have given the heads up to whoever the data was going to that there was a potential for issues.

What if emails are shared? What if one person used multiple emails and ended up on more then one row? What if they used the same email but changed their last name?

Lots of little edge cases where matching like that can fall apart so making folks aware is definitely what I'd do.

[u/No-Plant-5234 → u/bigeyez]

no, not properly. it's in the PR description which is not the same as telling anyone. the people consuming the output think they have a customer count, not an estimate of one. shared emails and one person on two rows are both live in there right now and I can't tell you how many. that's the part Im fixing first before the matching itself.

[u/bigeyez → u/No-Plant-5234]

So yeah if I were you I'd reach out ASAP and disclose that. If you dont normally speak with the end users directly communicate it to your lead or supervisor.

The last thing you want is for people to figure it out later that there might be issues and then blame you for the data being bad despite you just doing what you could to fulfill the request.

[u/No-Plant-5234 → u/bigeyez]

yeah. writing it up for my lead tonight before I have anything measured "here's the number" can come later "this is an estimate" should have gone out with the thing itself and the reason i didn't isn't that it slipped. it shipped it looked fine and the thing I delivered last month might be wrong in a way i can't quantify is a much harder message to send once nobody's complaining.

[u/LARRY_Xilo]

Fully depends on the specific situation. Like what happens if you get it wrong? Will it make some stats wrong or will you loose a lot of money or will you get in trouble with the law? How many entries are there in the two tables? Is doing it by hand possible? How soon do you need it? Could be filled over time by a person when they work on the entry anyway? How are these to tables related in reality? Is it a 1 to 1 match?

does anyone compute a match score and hold back anything below a threshold rather than committing it?

Thats what we do for matching bank transactions to accounts but we do it outside SQL in program logic and give the users options to choose from with likely hood of matches if we arent extremely sure.

[u/No-Plant-5234 → u/LARRY_Xilo]

fair i skipped all of that. [fill in: X rows on ours, Y on theirs] and it feeds [reporting / billing / whatever it actually feeds] so being wrong means [the real consequence]. by hand isn't possible at that size, which is how I ended up here. the bank transaction one is interesting because you kept a human in the loop for the uncertain band instead of picking a cutoff and committing everything. mine has no uncertain band at all, everything either joined or vanished. did you find users actually work through that queue or does it just grow.

[u/LARRY_Xilo → u/No-Plant-5234]

They do but also when you are on top of it its a few cases a day (out of 10000+ bank transactions) also its usually teams handling this not just one person. Most people just know to write their bill number in the transaction or at least their contract number and do it from an account that is registered to them. Just gets complicated when you have cases that dont write anything relevant and its their wifes account who got a completely different name and so on. But also the good thing is its a 1 to 1 thing a transaction will only ever go to one account. You dont have that so even if you found a perfect match you could always find another match.

Not knowing what your environment looks like. For me I would probably try something like you put your results in a table with an extra field like "verified" and if someone is supposed to get billed using this table you raise a warning and ask the user to verify once this is done its gets put into the table as verified and doesnt need to happen again. Then for reporting you can split it into verified and unverified and overtime it should get better and better. If you can define an uncertainty band you could even prefill the ones you are certain about. But I get this might likely not be a solution for you.

[u/neumastic]

Emails are not great, but generally they should avoid most of the issues of false positives unless you have a people entering data that use random email addresses to avoid having to ask customers or when customers refuse.

What do you mean by fuzzy match? Are you calling the lower/trim a fuzzy match or (what I’m guessing) is that you’re using some string comparison algorithm and fine tuning the score that’s acceptable? For the latter, try doing a strict match and step down strictness for ones that aren’t matched? Though I’m not sure on the architecture, size, or if it’s a “live” dataset and what performance hits you can take there. That may or may not cause some lag if the database is a shared resource.

Last names are tricky reliable because of two-part last names (I imagine that isn’t a revelation, though). I had a client that insisted we do a strict case-insensitive comparison of first and last names (separately) for some approval validations. We ran into issues with records where one person put part of the name with the first name and another that put it with the last. In the end, I stripped all non-alpha characters including white space from the concatenated first/last name. It isn’t great but the validation is for a form their employees use, so we can get away with it.

I used fuzzy matches before, but that was to flag items to help BAs review faster when normal checks failed.

[u/No-Plant-5234 → u/neumastic]

honestly the lower/trim is what I was calling fuzzy which now that you've asked it out loud is just normalisation. no scoring, no distance function, straight equality on a cleaned string. so theres nothing to tune and nothing to step down from it either matched or it didn't. the strict-first-then-loosen order is what I'd do differently it also gives you a natural place to stop rather than one setting that has to be right for everything and the two-part surname thing is not a revelation but I still didn't handle it some of theirs have the particle in the first name field. stripping non-alpha off the concatenated name is uglier than what I did and would have worked better.

[u/ChaosEngine-6502]

My approach for these scenarios is to look at sufficiently large sample data set using the query to try and spot potential issues. If spotted, try and implement some workaround that mitigates (or at least flags) the inaccuracy before it goes into production. These workarounds on tables with no natural FKs where you're having to fudge a relationship often incur a performance overhead, so that also needs to be a factor.

If you have some kind of accuracy metric you can build in that potentially flags inaccurate rows, it would be a good idea to include it. We've had a number of issues like this in the past, and we make it clear to people who want the data of the potential issues and risks with trying to do this kind of "fuzzy" matching.

The reality is, there will likely be some edge case that pops up at a later point you'll have to try and account for. Even if there's nothing in the data now that would show up, it doesn't mean that it won't in the future.

[u/No-Plant-5234 → u/ChaosEngine-6502]

he performance cost is the bit nobody else has mentioned and its probably the real reason I didn't build any of this. the naive version is one join and its fast. anything with a score and a flag column is a different shape of query. I didn't weigh that up though, I just wrote the cheap one and found the justification later.the per-row flag is better than one overall accuracy number because it travels with the data. mine has a number that lives in my head, and nothing downstream carries any marker at all, so by the time it reaches a dashboard it looks exactly as solid as a real key. on the future edge case the version that worries me isn't the data getting weirder, its their side changing. if the partner starts populating email differently next quarter, nothing errors. the match rate just drops, the customer count goes down, and someone reads it as churn. do you track match rate over time as its own metric, or is that overkill for most of these?

[u/ChaosEngine-6502 → u/No-Plant-5234]

We're involved in real-time healthcare integration, so we're focused on delivery of data to other systems, rather than building dashboards and reports.

Tracking match rate over time isn't something I would worry about now, unless someone has specifically asked for it. The use of a customer email address seems like a sensible enough approach in absence of any natural key, although it does have to assume the email address is available in all the tables you need to join on.

[u/Responsible_Pie8156]

You have to understand the data generation process and analyze for erroneous matches and keys without matches and also the use case. You could try it with or without fuzzy matching or regex or whatever seems to help, you actually have to look at the nature of the mismatches to decide what that process should be. But if the data doesn't exist to match things reliably then it just is what it is and its a business decision whether the errors are acceptable for the use case.

[u/No-Plant-5234 → u/Responsible_Pie8156]

I never asked how their IDs get generated which in hindsight is the first question. I treated the export as data that arrived rather than as output from a system somebody designed. no idea if their ID is sequential, has a region prefix, encodes a signup date, anything and the "the data doesn't support this, thats a business decision" outcome I didn't put on the table. probably because I assumed shipping something was what I was being asked for.

[u/Responsible_Pie8156 → u/No-Plant-5234]

If you can derive any info from their IDs that would be great but if it's not immediately obvious how whatever IDs are formed idk if that's likely to help. I just meant join everything using your rules and analyze what got joined. Look at what rows from both sides don't have a match, look at if there are any one to many matches or many to many matches it if you're expecting one to one. If you notice likely errors are there rules you can think of to fix them? Is it more important not to make false matches or not to miss a potential match?

You do actually need to understand what this data is and the use case, so nobody here can really tell you what do without more info. Is this from 2 different billing systems that you expect to be consistent? Or is it from users manually filling out forms where you're not even expecting most people to match? If you know there are going to be inaccuracies due to the data it's on you to make sure stakeholders are still on board with it before you implement a solution.

[u/reditandfirgetit]

Did you do a straight match or use something like levenshtein() function to generate a score?

[u/No-Plant-5234 → u/reditandfirgetit]

straight match. equality on lower(trim(email)) plus surname no levenshtein no score. so there's no number i could threshold on even if I wanted to which is the actual problem underneath the one I posted about.

[u/reditandfirgetit → u/No-Plant-5234]

Thats as strong a match as you're going to get, anything else is a guess that would need to be manually reviewed. If you need a confidence score, levenshtein is probably your best bet. Your current logic would all score a 1.0 (100%)

[u/Better-Credit6701]

Sounds like you did the best with the data you had. Problem is the data you had. I've worked with data where we had thousands of duplicate email addresses such as <no email.com >. I would go through the data regularly but I was never going to get it perfect.

If it would be data that I had to live with, I would work with an identity seed after dedupping. In our case for things like a account number, it was a 'smart' number that each position represented something like area, region, lot,... But the whole customer table had an identity seed that we could tie into other tables such as address, email, accounts so they could have multiple accounts. At the end, we would have a bit column to mark if it was active or not so we could see when they moved, name change, active account...

Dedupping is an art, with many different examples and techniques which also requires data cleaning

[u/No-Plant-5234 → u/Better-Credit6701]

household emails plus last name is the exact hole and I didn't think of it. same family, shared address, same surname, my composite does nothing there. first name catches most of it but then Im into sofia vs sofía and whatever their system did with accents. going to count how many of my matches are multi-member households before I say anything else about a match rate

[u/tomthedj]

I deal with this quite a bit but ive found a "somewhat" reliable way to do this and have it at least flag potential issues so I dont have to eyeball it. essentially for both tables I make a "key" row by having all row data consolidated and delimtted after going through as much normalization as possible. then I get characteristics about each key like character count, length, if it contains certain keywords, if it doesn't, anything that can help make any type of distinction. then some logic is written to find close matches and how close of a match they are, which is then ranked to find the best possible matches. sometimes this works pretty well, others it can be the biggest pain because one table will require many linking tables while the other gives you nothing. but i have found it to be the most consistent when achieving what I need.

[u/No-Plant-5234 → u/tomthedj]

using the whole row as the key instead of picking fields is the bit that gets me because I did the opposite. I chose email and surname because they were populated which meant I decided what mattered before looking at anything. consolidating everything and then deriving characteristics gets you the discriminators out of the data instead of out of your head. two things I want to ask. does the rank survive downstream, or does it collapse to one chosen match before it leaves your process? mine collapses and that's the exact step where the guess stops looking like a guess and the asymmetry you mentioned, one side needing linking tables while the other gives you nothing that's my situation and nobody else has named it. when it goes that way, do you build the key off the poorer table and accept you're throwing away signal on the rich side or do you flatten the rich one down first?

[u/Aggressive_Bobcat493]

Use all possible variables you can (email plus anything else) to fuzzy match on and if you can, develop a measure of how close/confident the match is. Pull a hundred or so matches for manual review at different levels of the match confidence measure to get a sense of at what point you lose confidence in the match. Use that to give your team a sense of what proportion are confidently matched vs some confidence vs no confidence. Run your analyses on the different groups and compare to see if poor matching is likely biasing results. See if the original data you received can have any more matchable columns included at source. If all this looks bad, tell your team you simply can't match the datasets.

[u/Aggressive_Bobcat493 → u/Aggressive_Bobcat493]

Also, you mentioned both have an ID - is there anything in the ids that could help? Does one include a geography reference e.g. state of residence?

[u/No-Plant-5234 → u/Aggressive_Bobcat493]

pulling a hundred at each confidence level instead of a hundred overall is the bit I hadn't thought of. that gives you where it falls off rather than one average thats hiding the falloff. and "tell your team you can't match the datasets" I didn't treat as an available outcome, I treated shipping something as the only one

[u/Aggressive_Bobcat493 → u/No-Plant-5234]

Exactly. Similar issue I had last year, I used the literal value of the fuzzy string match algorithm (think it was jaro winkler) as my "how confident am I?" measure, then a quick group_by call to start looking at confidence-wise stats. Hope it works for you!

[u/Confident-Mud-390]

Are both keys perhaps sequential? Do both tables have some CreatedOn datetime field?

If so then you should be able to use that?!

[u/No-Plant-5234 → u/Confident-Mud-390]

checking the sequential thing now the fact I have to check is its own answer i never looked at how their IDs get generated. less hopeful about the timestamps though. theirs is when the row landed in their system ours is when the customer signed up with us so the same person can be months apart. and anything they backfilled all shares one date.

[u/thesqlguy]

You are looking at this backwards.

You say I have some tables of data how can I join them?

But the answer is based on what do you want to return.

You need to have some idea what output you want to get from combining these two datasets. You're not just joining them for fun I assume right? You have an explicit output you are trying to return/calculate?

Even if the data has dups, is fuzzy, is confusing, is ill-defined, you still should know when you write any query what you want to return.

For example, you mentioned email address. Is that the key you want to have in your output,.I.e., return one row per email address? Or is there some other key like customerId you want to return data for?

From there you decide what you want and analyze the tables to figure out how to join/filter/dedup/aggregate to get the result you are looking to calculate.

It could be that you don't even join these two tables directly at all. Maybe you union them. Or maybe you join them through another table first. Or aggregate them each first.

So if you can give a simplified but representative example of the data you have and the output you want maybe someone can give you tangible advice.

But if all you have for a spec is 'combine these two heaps of data and generate another heap of data' then you have bigger problems than just not knowing how to join these tables.

[u/dyogenys]

It's not as satisfying as having nice trustworthy key, but email is pretty good I would say. People generally have one unique email. What is the smartest thing to do is different all the time, and I like that.

[u/Better-Credit6701 → u/dyogenys]

Seen too many times an entire household will share the same email address. It isn't as popular as it once was

[u/dyogenys → u/Better-Credit6701]

But that shouldn't be a problem for OP who composites it with name

[u/Better-Credit6701 → u/dyogenys]

It would since he used email address plus last name. It would be people within the same family and often same last name who share email addresses

[u/dyogenys → u/Better-Credit6701]

Oh yeah, I didn't re-read it. Good catch. OP, first name is important

[u/Better-Credit6701 → u/dyogenys]

I would throw in some other columns as well, converting that to a unique identification

[u/dyogenys → u/Better-Credit6701]

As long as they can't differ for the same person. (crazy that someone downvoted your helpful comment)

[u/Better-Credit6701 → u/dyogenys]

(crazy that someone downvoted your helpful comment)

Well, come to expect that on Reddit...

[u/dyogenys → u/Better-Credit6701]

Well I corrected it with my votes because yourse are the most helpful comments in here. Literally pointet out a real flaw and got downvoted for it, I hope it wasn't OP

[u/No-Plant-5234 → u/Better-Credit6701]

yeah, thats the hole. same household, shared address, same surname... email plus last name does nothing there it's exactly the case my composite was supposed to catch and doesn't. first name helps but the partner file has particles landing in the first name field on some rows and the surname field on others so I'd be adding a field that's inconsistent on their side. going to count how many of my matches are multi-member households first before I claim any match rate at all.

also worth saying you two worked out the actual failure in about four replies. I had months.

[u/Better-Credit6701 → u/No-Plant-5234]

"also worth saying you two worked out the actual failure in about four replies. I had months."

Perhaps because I spent some time trying to fix the same issue with over 2 million email accounts. Also had another issue when we would collect multiple email addresses per customer. Another column you might want is IsPrimaryEmail since people often have multiple email accounts plus a IsActive flag to disable bad rows without actually deleting them

[u/Sexy_Koala_Juice]

I mean email (in theory) should be distinct. Like that should be enough

[u/No-Plant-5234 → u/Sexy_Koala_Juice]

in theory yeah, and I assumed the same. the bit I didn't do was check, one count vs count(distinct) on their file would have told me whether it was true for this data, and I never ran it. Further down someone pointed out shared household addresses and accounts@ mailboxes, which is where 'should be distinct' stops being true in practice.

[u/Sexy_Koala_Juice → u/No-Plant-5234]

True, but if emails aren’t unique then you’re screwed, cause email+last name isn’t guaranteed to be unique either, not even email+full name, as some cultures name their children after their parents.