The report was a ten-year overview of where co-op students had worked around the world, and one of its tables listed the top employers. A few colleagues reviewing it stopped on two names near the top, a pair of financial firms, and said roughly the same thing: those can't be that big. One of them had an independent count from a different system, and it didn't come close to ours. When we traced it, one of those firms had four real placements. Our table said well over a hundred.
The cause turned out to be a piece of logic that I think a lot of data teams have somewhere, and that almost everyone writes the same way the first time. It's worth walking through, because it sits right on the line between old-fashioned data cleaning and the kind of matching problem people now want to hand to AI.
The problem it was solving is called entity resolution, which is a formal name for figuring out that several messy records all refer to the same real thing. Employers show up in co-op data under whatever name somebody typed. The same company can appear as a short name, a full legal name, the legal name with "LLP" on the end, and a misspelling of all three. If you count placements by raw name, one large employer looks like four medium ones. So somewhere upstream, someone had built a standardization step. It took the domain from each employer's email or website, and grouped every record sharing a domain under one canonical name. Everyone at the same corporate domain is the same company. It's a genuinely clever heuristic, and for most employers it works.
-- the original idea: one domain, one employer, one name
with domain_master as (
select
domain_key,
employername as canonical_name,
row_number() over (partition by domain_key order by employername) as rn
from stg_employers
where domain_key is not null
)
select
e.employer_id,
coalesce(dm.canonical_name, e.employername) as standardized_employer_name
from stg_employers e
left join domain_master dm
on dm.domain_key = e.domain_key and dm.rn = 1
Nothing in that query asks whether a domain actually belongs to one organization.
The flaw is the word "most." A domain only identifies an organization if the organization owns it. Nobody owns verizon.net. Or comcast.net, or msn.com, or the big Chinese webmail providers. Small employers, and a lot of international employers, register with a personal or ISP email address, and the grouping logic had no idea those domains were shared by thousands of unrelated people. So every employer on the same free email domain got collapsed together, and the whole group took the name of whichever employer happened to be picked as the representative. One webmail domain swept a couple of hundred distinct employers under the name of a single securities firm. Another one turned more than a hundred unrelated employers into a small theater company. A placeholder domain, the kind people type when a form demands an email and they don't have one, merged well over a hundred employers into one nonprofit.
The three grey bars are illustrative. The red ones are the real counts from the investigation: how many unrelated employers each shared domain swept under one name. A real organization's domain barely clears the axis.
The top-employers table was just where it became visible. The same standardized name fed every co-op mart that reported by employer, so the error was everywhere, just rarely large enough in any one place for somebody to stop and say that can't be right. The two firms that got caught were caught because the error concentrated on them. One legitimately large employer had quietly been pushed out of the top fifteen entirely, because the inflated ones took its spot. Nobody flagged that one, because a missing name doesn't look wrong.
It also turned out to be two bugs, not one. Some of the corrupted names were arriving already wrong from the upstream system, before they ever reached our pipeline, and those weren't ours to fix. The rest came from our own fallback logic applying the same domain grouping when the upstream name was missing. Separating those took most of the investigation, and it's the kind of detail that decides whether a fix actually fixes anything.
The fix for our half was deliberately unglamorous. A seed file, which in dbt is a small version-controlled lookup table, listing domains that don't identify an organization: consumer webmail, ISPs, alumni forwarding addresses, placeholders like example.com, even linkedin.com. Records on those domains are excluded from grouping and keep their own name. Adding a newly discovered junk domain is now a one-line change instead of a SQL edit. A test fails the build if any employer on a listed domain ever receives a domain-derived name again. And legitimate grouping is untouched, a university domain or a military branch domain still collapses to one organization, which is exactly what you want.
domain,category
verizon.net,isp
comcast.net,isp
msn.com,consumer_webmail
qq.com,consumer_webmail
163.com,consumer_webmail
example.com,placeholder
company.com,placeholder
alum.mit.edu,alumni_forwarding
linkedin.com,platform
-- the fix: shared domains never get to name anybody
with domain_master as (
select domain_key, employername as canonical_name,
row_number() over (partition by domain_key order by employername) as rn
from stg_employers e
where domain_key is not null
and domain_key not in (
select domain from {{ ref('seed_non_org_domains') }}
)
)
...
The other suggestion that came out of the write-up is the one I like more, because it doesn't depend on anyone knowing the list in advance. A real company domain maps to one employer, maybe a handful of divisions. A shared domain maps to dozens or hundreds. So you can flag any domain whose count of distinct employer names is implausibly high and review it before it's allowed to group anything. That's a small statistical check instead of a hardcoded list, and it catches the next free email provider nobody's thought of yet.
-- flag any domain that looks shared before it is allowed to group anything
select
domain_key,
count(distinct lower(trim(employername))) as distinct_names,
count(*) as records
from stg_employers
where domain_key is not null
group by domain_key
having count(distinct lower(trim(employername))) > 15
order by distinct_names desc
This is where the AI question comes in, because it always does now. Isn't this exactly what language models and embeddings are for? An embedding turns a piece of text into a list of numbers such that similar meanings end up close together, so "PricewaterhouseCoopers LLP" and "PwC" would land near each other even though they share almost no characters. Matching employer names by meaning instead of by email domain would have avoided this specific bug entirely, since a theater company and a veterinary clinic don't mean the same thing no matter what email provider they use.
I think that's true, and I'd happily use similarity matching as one signal. But I'd want it to be one signal, not the decider, for the same reason the domain rule failed. The domain rule was right most of the time, and its failures were silent and clustered. A similarity model will also be right most of the time, and its failures will also be silent and clustered, just in different places: two genuinely different companies with similar generic names, a parent and subsidiary that should or shouldn't merge depending on the question being asked. Some groupings that look wrong are actually right, a company whose divisions were renamed after an acquisition, for instance. No model knows that. Somebody in the co-op office does.
So the version I'd build next combines all three. Rules for the things we know for certain, like the list of domains that never identify anyone. A statistical check for the things we can detect, like a domain that's suddenly shared by fifty employers. A similarity model for proposing merges we haven't thought of. And a person approving the ones that matter before they change a number in a report.
The real lesson is the same one this blog keeps circling back to. The bug was found by people who knew the domain looking at a number and saying that doesn't feel right, and then by someone else having an independent count to check it against. Not by a test, and not by a model. Whatever does the matching, keep a second number around that you trust for a different reason.