The Dirty Vendor Master File: 7 Data Quality Checks Every AP Audit Should Start With

Ask an AP auditor where duplicate payments come from and you’ll rarely hear “the payment process.” The answer is almost always “the vendor master.”

A few years ago I watched an audit team chase what looked like a textbook duplicate payment: same amount, same week, same invoice reference. It took them most of a day to figure out why the duplicate check hadn’t caught it. The answer was almost funny. The invoice had been paid to “ACME Corp” and to “Acme Corporation.” With two vendor IDs. Two clean, fully approved, completely legitimate-looking payments.

The homegrown duplicate check wasn’t broken. It was working just fine, but as far as it knew, these were two different companies. The process was correct, but the data was wrong.

That’s the thing about the vendor master file: every AP control you have sits on top of it and quietly trusts it. Three-way matching, approval workflows, duplicate detection. All of them assume the records underneath are right. When the file is dirty, your controls aren’t failing. They’re succeeding at the wrong thing.

How It Gets This Way

Nobody sets out to build a messy vendor file. It rots slowly, and mostly for organizational reasons rather than technical ones.

Start with who creates vendor records. In most companies that’s several different people across purchasing, AP, and shared services, each with their own habits. One types names in all caps. One abbreviates “Incorporated.” One copies whatever string happens to be on the invoice, trailing spaces included. Without validation at entry, every habit becomes a permanent record.

Then add time. Every ERP migration imports the old system’s duplicates into the new one. Every acquisition merges in someone else’s vendor file, complete with someone else’s problems. The migration project always has a deadline, and vendor deduplication is always the task that gets cut in week three.

And then there’s the workaround that guarantees the problem compounds. A buyer searches for a vendor and can’t find it, usually because it was entered under a slightly different name. So the buyer creates it again. The data quality problem causes the failed search. The failed search causes the next data quality problem. Around it goes.

Underneath all of this is the real cause: no owner. Purchasing assumes AP owns the vendor master. AP assumes purchasing does. IT owns the database table and nothing else. Files without an owner do not get cleaned, ever.

The Seven Checks

These are the checks we run first, in roughly the order they pay off. All of them are full-population tests. Run them against every record, not a sample. A sample tells you that you have a problem; the full population tells you where.

1. Duplicate vendors hiding under name variants

The classic. “J&J Supplies” and “J and J Supplies.” “ACME Corp” and “Acme Corporation.” Exact-match duplicate checks sail right past these, which is exactly why they survive year after year. For non-English vendor names, the number of spelling variants increases even more. “Müller GmbH” can show up as “Mueller GmbH” or “Muller GmbH”, all typed in good faith by different hands.

Catching them takes two steps. First normalize: strip the whitespace and punctuation, then uppercase everything with UPPER() and TRIM(). Then compare phonetically with SOUNDEX(), which maps names that sound alike to the same code regardless of spelling. Sort the file by that code and the candidate pairs land next to each other, ready for a human decision.

2. Different vendor IDs, same bank account

Two vendor records with different names paying into the same bank account are either a duplicate or something you want escalated today. Shared addresses are the softer version of the same check. There are innocent explanations, like shared office buildings or factoring companies. Every pair still deserves thirty seconds of attention.

Technically this is just the vendor file joined against itself, keeping the pairs where the account matches and the vendor ID doesn’t. Of the seven checks, this is the one most likely to find deliberate fraud rather than sloppiness.

3. Employees in the vendor file

Match the vendor master against the HR employee file on bank account, address, and phone number. Most hits will have boring explanations. The ones that don’t are the most expensive finding in this entire list.

In practice this check dies for a mundane reason. The HR extract is a CSV on someone’s desktop, the vendor master lives in Oracle, and getting them into the same place means a ticket and a two-week wait. This is, frankly, the problem Tabular IQ exists to remove. Open the extract, connect to the vendor table, link them on the spot, and be done before lunch.

4. Missing and malformed tax IDs

Blank tax IDs block your compliance reporting. Malformed ones tell you the record was created carelessly. And carelessness clusters. Find the blanks with ISNULL() and EMPTY(), then run the rest through REGEXP(). A VAT number, an EIN, or a Steuernummer each has a pattern you can validate against.

5. Dormant vendors that suddenly wake up

A vendor silent for three years receives a payment. Maybe the relationship genuinely resumed. Or maybe someone changed the bank account on a record nobody watches, submitted an invoice, and changed it back. Dormant records are popular targets for a reason. Flag payments to long-quiet vendors, and pay special attention when the vendor record was modified shortly before the money moved.

6. Incomplete critical fields

Every blank address, payment-term, or contact field is a decision someone will improvise later, differently every time. A completeness profile shows what percentage of each critical column is actually filled. It takes minutes to build and tells you exactly where the rot is concentrated.

7. The invisible formatting problems

Trailing spaces. Tabs pasted in from spreadsheets. “Straße” here, “Strasse” there. None of it looks wrong on screen, and all of it silently breaks the exact joins that checks 1 through 6 depend on. TRIM(), PROPER(), and TRANSLATE() do this unglamorous work. Do it first. It makes everything else trustworthy.

For quick reference:

Check	What it catches	Useful functions
Name variants	Duplicate vendors	SOUNDEX(), UPPER(), TRIM()
Shared bank account	Duplicates, fraud	self-join on account fields
Employee match	Conflict of interest, fraud	cross-file join vs. HR data
Tax ID validation	Compliance gaps	ISNULL(), EMPTY(), REGEXP()
Dormant activity	Account takeover	date comparison
Completeness	Process gaps	EMPTY() per column
Normalization	Silent match failures	TRIM(), PROPER(), TRANSLATE()

Keeping It Clean

Finding the mess once is a project. Keeping it clean is a routine, and the routine has three parts.

Give the file an owner: one team with the authority to merge records, deactivate vendors, and say no. This costs nothing, and it’s the single change that outlasts every tool decision.

Validate at the door. Mandatory fields, tax ID format checks, and a fuzzy duplicate search before a new vendor can be saved. Every record stopped at entry is one you never have to clean up later.

And make the checks recurring. Quarterly works for most teams. This is where tooling starts to matter, because all seven checks are perfectly doable in Excel until the file crosses a few thousand rows and the fuzzy matching starts eating weekends. They’re doable in SQL if you have a DBA with spare capacity every quarter. What you actually want is for an AP analyst to run the whole battery without asking anyone for anything.

That’s how we approached it in Tabular IQ. Connect straight to the vendor table in MySQL or Oracle, or just open the extract next to the HR file and the payment ledger, and run everything above with ordinary functions. No ETL pipeline, no ticket queue. And with the new Tabular IQ AP Edition, the whole checklist ships as a built-in vendor master cleanup tool: normalization, fuzzy duplicate scoring, the employee cross-match, and an exception report you can drop straight into the audit file. If you’d like to see it against your own vendor file, get in touch. That demo tends to be memorable.

Run It Before Your Auditors Do

A dirty vendor master looks like a cosmetic problem right up until it turns out to be the root cause. The duplicate payments, the reconciliation that never ties out, the fraud that ran for two years inside a record nobody looked at: they all start in this one file. The seven checks above take an afternoon the first time. They will find something. They always do.