The Illusion of Clean Data: The Pharma Identifiers Generic Tools Never Check

Picture the ticket. A rep writes in on Monday because their territory is short about 40 TRx and they want to know where it went. You trace it to one prescriber whose NPI in the specialty pharmacy feed does not match the NPI in Veeva. The value is not missing and it is not null: ten digits, all numeric, sitting there looking exactly like an NPI. Two of them are transposed, and the join has been dropping that prescriber since March. Your pattern test on that column has been green the entire time.

It was green because commercial pharma runs on identifiers almost nobody outside the industry has heard of. NPI for the prescriber. DEA for the one who can write a controlled substance. NDC for the product, at two different levels of specificity. ICD-9 and ICD-10 for the diagnosis, often in the same column. HCPCS J-codes for anything infused. CMS Place of Service for where it was given. NCT for the trial. These are the join keys the whole commercial picture is assembled on, and every one of them has a published format with rules that a general-purpose data quality tool knows nothing about.

So it counts characters instead. Ten digits in the NPI column, so the column passes. That is the hole, and it is wide enough to drive a quarter through.

The pack, in one paragraph

As of 27 August 2026, DataOps TestGen Enterprise ships a generation set called Commercial Pharma: twelve test types covering the identifiers above, validated the way their specifications define them rather than by counting characters. They generate off your profiling run alongside the standard set, and there is nothing to configure. The release notes list the whole set.

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

Four kinds of check, and the difference matters

The twelve sort into four groups. Knowing which group a test belongs to tells you how much a passing result is worth, which is the part that gets glossed over most often.

Two run arithmetic. Valid NPI and Valid DEA compute the check digit the specification defines and compare it against the digit sitting in the value. A failure here is not a matter of opinion.

Three cover the level an NDC column stores. Package, product, or unsegmented.

Six check shape only. The four ICD tests, Valid HCPCS J-Code and Valid NCT confirm a value is well-formed. They do not consult any code set, so a well-formed value that is not a real code will pass.

One checks membership in a published list. Valid CMS POS, and it is the only one. Hold on to that distinction.

Arithmetic: the two check digits

An NPI is ten digits, and the tenth one is not data. It is 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 ten, which is the entire reason the digit is there. Hand-keying and OCR produce transpositions, so the identifier was designed to catch them. A regex that counts to ten throws that design away.

DEA registration numbers carry the same idea in a different arithmetic. 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 ten. Valid NPI and Valid DEA run those calculations in SQL, on the landing table, on every run.

Level: which NDC your column actually stores

NDC is where most commercial pharma warehouses lose a day a quarter, and the reason matters, because people usually get it wrong.

The FDA assigns a ten-digit code in one of three segment layouts: 4-4-2, 5-3-2 or 5-4-1. Billing wants eleven digits in 5-4-2, which you get by padding the short segment with a leading zero. That works as long as the segments are still visible. Strip the hyphens and they are not. Take 1234567890:

Table 2: three different products, the same ten characters, and nothing in the value to say which one you are holding.

Be clear about what the three tests do and do not resolve. All three accept every FDA layout; none of them can tell you which layout a stripped value came from, and Valid NDC Any Form says so in its own usage notes. What separates them is the code level your column stores. Valid NDC takes the package-level three-segment form and the eleven-digit form your 867 files and claims data carry. It rejects a bare ten-digit string deliberately: that string is ambiguous, and treating it as valid is how the wrong product ends up in a report. Valid NDC Product takes the two-segment product-level form your product master and formulary files carry. Valid NDC Any Form takes any recognized form including bare digits, for the warehouse that strips separators as a convention and has decided to live with the ambiguity. Even that loosest one still catches the leading zero Excel ate, the UNKNOWN somebody typed into a required field, and the value that is somehow twelve characters long.

Shape: ICD, J-codes and trial numbers

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, including that the code set leaves out I and O so nobody confuses them with 1 and 0. The diagnosis tests accept dotted and undotted forms, because 837 claims drop the decimal and your vendor extract may or may not have put it back.

Valid HCPCS J-Code checks the J plus four digits that Part B buy-and-bill runs on, one slice of HCPCS Level II scoped to drugs. Valid NCT covers ClinicalTrials.gov identifiers on the medical affairs side, as NCT plus eight digits or eight bare digits.

These six check that a value is well-formed. They do not check that it exists. Z99.999 passes Valid ICD-10 Diagnosis, 000.00 passes Valid ICD-9 Diagnosis, ZZZZZZZ passes Valid ICD-10 Procedure, and J0001 passes Valid HCPCS J-Code, and not one of those is a real published code. That is a deliberate scope decision rather than a gap: the ICD-10-CM code set alone runs to more than 70,000 codes and revises annually, so carrying it inside a test would mean shipping a copy that silently goes stale.

Membership: the one test that checks a real list

Valid CMS POS is the exception, and the pack’s genuine differentiator. CMS publishes Place of Service as a small fixed list, small enough that the test carries the actual list and checks membership against it. The copy in the pack is dated in its own usage notes: current as of August 2026, including codes 27, 58 and 66.

That means a Place of Service code the test rejects is worse than malformed. It does not exist. If your site-of-care mix suddenly shows growth in a POS code CMS never published, you would rather hear it from a test than from the slide.

You don’t configure any of it

Profile the schema and the tests write themselves, using 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 is not caution for its own sake. B2111ZZ is a legal ICD-10-PCS procedure code and a legal ICD-10-CM diagnosis code, so the shape cannot tell you which one you are holding, but the column name usually 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 is 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 tests check format, plus a check digit where the specification defines one, and with the single exception of Valid CMS POS they never consult a code set or call NPPES. A Luhn-valid NPI is therefore a number that could have been issued. It is not proof that one was, and it says nothing about whether the provider is still active or whether the NPI belongs to the prescriber your feed claims it does. A failure tells you something definite; a pass only narrows the possibilities.

The same limit runs 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 storing the numeric part alone, 20260815 passes.

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.

Why some fail and some only warn

Seven of the twelve default to Fail and five to Warning, and the line between them follows how much a failure actually proves.

A test defaults to Fail where a bad value can only be a bad value. Valid NPI and Valid DEA run a check digit, so a failure is arithmetic rather than judgement. Valid CMS POS checks membership in a list CMS publishes, so a failure is a code that does not exist. The four ICD tests check shapes the code systems define exactly, and a value outside those shapes is malformed whatever else it is.

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.

Where it runs

We hand-write every test per database flavor, because there is no portable way to express Luhn arithmetic in SQL and faking it produces a test that passes everything. That covers the databases TestGen already supports: PostgreSQL, Amazon Aurora PostgreSQL, Amazon Redshift, Snowflake, Databricks SQL, Google BigQuery, Microsoft SQL Server, Microsoft OneLake, Azure SQL Database, Azure Synapse Analytics, Oracle Database, SAP HANA and Salesforce Data 360.

When a test fails you get the failing values themselves, ranked by count, rather than a percentage. That means you can take the actual codes back to the vendor along 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 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, which is the right default for an identifier and the wrong one for a vendor feed you have never profiled. Point it at last week’s drop first and see what comes back before you tighten anything.

Profiling tells you what’s broken, and on most commercial teams nobody is free to fix it. We do that part too: DataKitchen builds and runs commercial pharma data warehouses with these same tests in them, and a rare-disease biotech that told us they had a Cadillac and needed a Corolla cut the annual cost of running its commercial data by about 58% that way.

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