The Illusion of Clean Data: Why Generic Profiling Fails Healthcare Identifiers

A rep opens a ticket on Monday. Their territory is short about 40 TRx for the quarter, and they want to know where it went.

You trace it. One prescriber in that alignment has an NPI in the specialty pharmacy feed that doesn’t match the NPI in Veeva. It isn’t missing and it isn’t null. Ten digits, all numeric, sitting in the column looking exactly like an NPI. It just isn’t one. Two digits are transposed, and the join has been quietly dropping that prescriber since March.

Your pattern test on that column is green. It’s been green the whole time.

That’s the gap this release closes. On Friday, August 28, 2026, DataOps TestGen Enterprise ships a new generation set called Commercial Pharma: 12 test types that validate healthcare identifiers the way the specs define them, check digits included. They generate off your profiling run alongside the standard set, and you configure none of it. The rest of this post is what each one does and where it stops.

The check digit exists for exactly this

An NPI is 10 digits, and the tenth one isn’t data. It’s a Luhn check digit computed over the first nine, with the 80840 prefix the spec requires folded into the sum. Transpose two adjacent digits and the sum stops landing on a multiple of 10.

That’s the whole point of the design. Hand-keying and OCR produce transpositions, so the identifier carries a digit whose job is to catch them. A regex that counts to 10 throws that away.

DEA registration numbers work the same way. The seventh digit has to equal the first, third, and fifth digits added together, plus twice the sum of the second, fourth, and sixth, mod 10.

The 10-versus-11 problem, stated precisely

NDC is where most commercial pharma warehouses lose a day a quarter.

The FDA assigns a 10-digit code in one of three segment configurations: 4-4-2, 5-3-2, or 5-4-1. Billing wants 11 digits in 5-4-2, which you get by padding the short segment with a leading zero. That works fine as long as you can still see the segments.

Now somebody hands you a 10-digit string with the hyphens stripped. Take 1234567890.

Table 1: three products, the same 10 characters, and nothing in the value to tell you which one you’re holding.

So the pack gives you three tests instead of one. Valid NDC takes the package-level and 11-digit forms your 867 files and claims data carry, and it treats a bare 10-digit string as invalid on purpose. Valid NDC Product takes the two-segment codes your product master and formulary files carry. Valid NDC Any Form is for the warehouse that strips separators as a convention and can live with the ambiguity, and it still catches the leading zero Excel ate, the UNKNOWN somebody typed into a required field, and the value that is somehow 12 characters long.

Claims-based diagnosis data

Four tests cover ICD. Valid ICD-10 Diagnosis, Valid ICD-9 Diagnosis, and Valid ICD Diagnosis for the column that spans the transition and honestly holds both, which describes most longitudinal claims extracts nobody has cleaned since 2015.

Valid ICD-10 Procedure checks the seven-character PCS shape, and it knows the code set leaves out I and O so nobody confuses them with 1 and 0. The diagnosis tests accept the dotted and undotted forms, because 837 claims drop the decimal and your vendor extract may or may not have put it back.

Valid NCT covers ClinicalTrials.gov identifiers on the medical affairs side.

Site of care, if you sell anything infused

Valid HCPCS J-Code checks the J plus four digits that Part B buy-and-bill runs on. It’s one slice of HCPCS Level II, scoped to drugs on purpose.

Valid CMS POS checks Place of Service, and it’s the one test in the pack that checks meaning rather than shape. CMS publishes Place of Service as a small fixed list, so the test carries the actual list and checks membership against it. The copy in the pack is current as of August 2026. If your site-of-care mix suddenly shows growth in a POS code that doesn’t exist, you’d rather hear it from a test than from the slide.

Four different problems, one shape. Every failure above is well-formed enough to clear a generic pattern test, which is why the column stays green while the join quietly drops rows.

You don’t configure any of it

Profile the schema. The tests write themselves. It’s the same mechanic that wrote data quality tests for 3 million NYC taxi rides in five minutes, pointed at identifiers instead of fare amounts.

Generation reads the column name and the profiled values together, and it needs both to agree. A column matching %npi% whose most frequent pattern is 10 digits gets Valid NPI. Unless the name says phone, fax, or contact. Unless profiling already tagged the values as a US phone number, which happens more than you’d like on vendor feeds where the HCP contact block sits three columns over.

The two-signal rule isn’t caution for its own sake. B2111ZZ is a legal ICD-10-PCS procedure code and a legal ICD-10-CM diagnosis code. The shape can’t tell you which one you’re holding. The column name can.

The cost is that a column named code_3 gets nothing. Add that one by hand, the same way you shop the rest of the test catalog. That’s the residue no profiler reaches, and on a commercial team your best data quality rules live in someone else’s head: the analyst who knows which specialty pharmacy reports units instead of scripts.

The specific test replaces the generic one

You already had Pattern Match, and on a column Valid NPI now covers, it’s noise.

So when a Commercial Pharma test generates on a column, it removes the Pattern Match test that generated on that same column in the same run. Only that run’s tests. Only unlocked ones. Anything you wrote by hand or locked stays exactly where it is. Before you confirm generation, the dialog tells you the most it will delete.

What these tests won’t tell you

These check format, and a check digit where the spec defines one. They never call NPPES.

Hold onto that distinction. A Luhn-valid NPI is a number that could have been issued. It isn’t proof one was, and it says nothing about whether the provider is still active or whether the NPI belongs to the prescriber your feed says it does. A failure is conclusive. A pass isn’t.

Same discipline down the list. A well-formed ICD-10 code from a category retired in 2019 passes. A DEA number carrying a registrant-type letter the DEA never assigned passes. Valid NCT accepts any eight digits, so on a column that stores the numeric part alone, 20260815 passes. Valid CMS POS is the exception, and only because that code set is small enough to carry.

None of this replaces Veeva Network or whatever you use for HCP mastering. MDM tells you what it could match. These tell you what arrived broken, per column, per feed, on the landing table, before a match rate has the chance to hide it. That distinction is the same one that makes testing commercial pharma data harder than anyone tells you: the failures live between the feeds, and the feed itself never errors.

Empty and null values don’t count as invalid anywhere in the pack. That’s a Required Entry question and a different test. The format checks ignore trailing spaces, so an identifier sitting in a fixed-width CHAR column doesn’t fail for its padding. The tests fold case first, since NCT and ICD codes come back lowercase from URLs and hand-built extracts constantly.

Introducing the Commercial Pharma test pack

All 12 tests ship as one generation set, and they sort into three kinds of check.

Arithmetic is Valid NPI and Valid DEA: compute the check digit the spec defines, compare it against the one sitting in the value, fail on a mismatch. Configuration is the three NDC tests plus Valid NCT, where the only question is which segment layout you are holding. Ontology is the other six, which check a value against a code system somebody actually publishes: the four ICD tests, Valid HCPCS J-Code, and Valid CMS POS.

The grouping is worth keeping in mind when a test fires, because it tells you who to go and talk to. An arithmetic failure goes back to whoever keyed the value. A configuration failure is usually a conversation about file format with the vendor sending it. An ontology failure means somebody is using a code the code system does not contain.

Why some fail and some only warn

Seven of the 12 default to Fail. Five default to Warning. The line isn’t arbitrary.

A test defaults to Fail when a bad value can only be a bad value. Valid NPI and Valid DEA both run a check digit, so a failure there is arithmetic rather than opinion. Valid CMS POS checks membership in a code set CMS actually publishes, so a failure is a code that doesn’t exist. The four ICD tests check shapes the code systems define exactly, and a value outside them is wrong.

The rest default to Warning because a format mismatch is usually a convention difference. Your vendor sends NDCs with hyphens and your product master stores them without. Neither side is broken. Somebody just never agreed on a form. A J-code column that also carries the rest of HCPCS Level II lights up the same way, and that’s a scoping decision rather than a data defect.

Change the default on any of them. Severity is set per test definition, so if unhyphenated NDCs really do break your gross-to-net, promote that one to Fail and let it stop the run.

The twelve new test types

Table 2: all 12 are Validity tests on a single column, and all belong to the Commercial Pharma generation set.

Where it runs

Every test is hand-written per database flavor, from PostgreSQL and Snowflake to Oracle and SAP HANA, because there’s no portable way to express Luhn arithmetic in SQL and faking it produces a test that passes everything.

When a test fails you get the failing values and their counts, ranked. Not a percentage. The actual codes, so you can take them back to the vendor with the file name and the row count.

That is the same reporting that turns a feed-by-feed inventory of what tends to break into something a launch team can act on rather than argue about.

Turning it on

The new pack ships with TestGen Enterprise. Open Generate Tests and select Commercial Pharma alongside Standard.

Threshold defaults to zero invalid values on every test in the pack. That’s the right default for an identifier and the wrong one for a vendor feed you’ve never profiled. Point it at last week’s drop first and see what comes back.

Start with the free version

Everything underneath the Commercial Pharma pack is open source: the profiler that reads your schema, the standard test set it generates, the scoring, the hygiene detection. You can run it against a copy of last week’s specialty pharmacy file this afternoon, behind your firewall, without talking to anyone.

And if the answer is “we don’t have the people for this”

Profiling tells you what’s broken. Fixing it is a different job, and on most commercial teams there is nobody free to do it.

We do that part too. DataKitchen builds and runs commercial pharma data warehouses: the specialty pharmacy and claims feeds landed, the identifiers reconciled, the alignment and gross-to-net logic tested, the reports the launch team actually asks for. Same tests, run by the people who wrote them, and you keep the platform at the end of it. Our most recent customer story is exactly this job: a rare-disease biotech with a phase III indication ahead of it and a finite runway told us they had a Cadillac and needed a Corolla. Consolidating two hourly vendors into one managed service cut the annual cost of running their commercial data by about 58%, and the savings went into the launch.

If the argument for testing this data is the part you’re still making internally, the rest of the case is here: why testing commercial pharma data is harder than anyone tells you, the commercial data most likely to be wrong, how a rare-disease biotech cut its data bill to fund the next launch, and what to cut when you’re hoarding cash before the FDA gate opens.

The whole argument on one page

Something to send to the person who owns the feed:

What a generic pattern test misses, on the left, and what the Commercial Pharma pack checks instead, on the right.

FAQ