Skip to content

Commercial Pharma Tests Enterprise

Commercial Pharma tests verify that the identifiers used in pharmaceutical claims, prescribing, and clinical trial data are well formed — the prescriber, product, diagnosis, procedure, and setting codes that commercial pharmaceutical data is joined and reported on.

All 12 are Validity tests on a single column, and all are grouped in the Commercial Pharma generation set.

Note

These tests check format, and a check digit where the identifier specification defines one. Except for Valid CMS POS, they do not verify that a code exists in the current published code set — a well-formed code for a retired or never-issued value passes.

Null and empty values are not counted as invalid by any of them. Use Required Entry or Percent Missing to test that an identifier is populated.

Trailing spaces are ignored by the format checks, so an identifier held in a fixed-width character column is not counted as invalid for its padding.

Auto-generation weighs the column name as well as the profiled values, so a column whose name does not identify the code it holds does not get a test. Add one manually to cover it.

Valid NPI

Tests that National Provider Identifier (NPI) values are exactly 10 digits and that the final digit is a valid Luhn check digit.

Auto-generated when profiling finds a text column named as an NPI, holding consistently ten-digit values that profiling did not identify as phone numbers.
Scope Column
Measures Count of invalid NPIs
Threshold Maximum acceptable count of invalid NPIs (default: 0)
On failure Invalid NPI values found.
Default severity Fail

How it works: Validates the 10-digit numeric format, then computes the Luhn check digit over the first nine digits with the constant 24 added to account for the 80840 prefix the NPI specification requires. A valid NPI leaves the running sum evenly divisible by 10. A value that fails either the format or the check digit counts as invalid.

When to use: On any provider, prescriber, or referring-provider identifier column. The check digit catches transposed and mistyped digits that a length-and-digits check would pass, which is what makes this stronger than a pattern test. A Luhn-valid number is not necessarily an issued NPI, so a failure is conclusive but a pass is not.

Valid DEA

Tests that DEA registration numbers are correctly formatted and check-digit valid.

Auto-generated when profiling finds a text column named as a DEA registration number whose values consistently match the DEA format.
Scope Column
Measures Count of invalid DEAs
Threshold Maximum acceptable count of invalid DEAs (default: 0)
On failure Invalid DEA registration numbers found.
Default severity Fail

How it works: Validates a letter, then a letter or the digit 9, then seven digits. The seventh digit is the check digit: it must equal the sum of the first, third, and fifth digits, plus twice the sum of the second, fourth, and sixth, modulo 10. A value that fails either the format or the check digit counts as invalid. Lowercase and mixed-case prefixes are accepted, and any letter is accepted in the first position.

When to use: On prescriber DEA registration columns in prescription and controlled-substance data. The check digit catches transposed and mistyped digits that a format check alone would accept. Registrant type is not validated, so a value carrying a registrant-type letter the DEA has not assigned still passes.

Valid NDC

Tests that values match a National Drug Code (NDC) format.

Auto-generated when profiling finds a text column named as an NDC whose values consistently match one of the package-level or 11-digit forms below.
Scope Column
Measures Count of invalid NDCs
Threshold Maximum acceptable count of invalid NDCs (default: 0)
On failure Invalid NDC formats found.
Default severity Warning

How it works: Accepts an 11-digit numeric string, or a hyphenated segmented form in any FDA configuration — 5-4-2, 4-4-2, 5-3-2, or 5-4-1. So 12345678901, 12345-6789-01, 1234-5678-90, 12345-678-90, and 12345-6789-0 all pass. An FDA-assigned NDC is 10 digits in one of three configurations, and the 11-digit 5-4-2 form is the normalization billing requires, produced by padding the short segment with a leading zero. A bare 10-digit string counts as invalid: with the separators removed, the segment configuration is ambiguous, so the value cannot be reliably normalized.

When to use: On the package-level and 11-digit drug code columns that claims and dispensing data carry. If your column holds two-segment product codes, use Valid NDC Product. If it holds codes stripped of their separators as bare 10-digit strings, use Valid NDC Any Form.

Valid NDC Product

Tests that values match a product-level National Drug Code (NDC) format.

Auto-generated when profiling finds a text column named as an NDC whose values consistently match one of the two-segment product forms below.
Scope Column
Measures Count of invalid product NDCs
Threshold Maximum acceptable count of invalid product NDCs (default: 0)
On failure Invalid product NDC formats found.
Default severity Warning

How it works: Accepts a hyphenated two-segment form in any FDA configuration — 4-4, 5-3, or 5-4. So 1234-5678, 12345-678, and 12345-6789 all pass. A bare 8- or 9-digit string counts as invalid: with the separator removed, the segment configuration is ambiguous.

When to use: On the product code columns that product masters, formularies, and market-access data carry, where the package segment is absent by design. If your column holds package-level or 11-digit codes, as claims and dispensing data do, use Valid NDC.

Valid NDC Any Form

Tests that values are a National Drug Code (NDC) in any recognized form, for columns that store the code without its separators.

Auto-generated when profiling finds a text column named as an NDC, holding consistently ten-digit values that profiling did not identify as phone numbers.
Scope Column
Measures Count of invalid NDCs
Threshold Maximum acceptable count of invalid NDCs (default: 0)
On failure Values found that are not an NDC in any recognized form.
Default severity Warning

How it works: Accepts every recognized NDC form — a bare 10- or 11-digit string, any of the hyphenated three-segment package forms, and any of the hyphenated two-segment product forms. A value counts as invalid only when it is not an NDC in any of them.

When to use: On drug code columns stored as bare 10-digit strings, where dropping the separators is the warehouse's convention rather than a defect. It catches what is still wrong under that convention: leading-zero truncation from a spreadsheet or an integer cast, placeholder text such as UNKNOWN, over-long values, and malformed hyphenation. It will not tell you that a code carries the wrong segment configuration, and no format check can tell you that a phone number is sitting in an NDC column. If your column holds its separators consistently, use Valid NDC or Valid NDC Product instead.

Valid ICD-10 Diagnosis

Tests that values match the ICD-10-CM diagnosis code format.

Auto-generated when profiling finds a text column named as a diagnosis code rather than a procedure code, holding values that start with a letter.
Scope Column
Measures Count of invalid ICD-10 codes
Threshold Maximum acceptable count of invalid ICD-10 codes (default: 0)
On failure Invalid ICD-10 code formats found.
Default severity Fail

How it works: Accepts a category letter, a digit, an alphanumeric character, and an optional extension of up to four alphanumeric characters. The decimal point is optional, so both E119 and E11.9 pass — X12 837 electronic claims are non-compliant if diagnosis codes carry one, so the undotted form is what most warehouse data holds. A trailing decimal point with no extension is invalid. Lowercase and mixed-case values are accepted.

When to use: On diagnosis code columns known to hold ICD-10-CM. Any category letter is accepted, including U: Chapter 22 (U00–U85) is in active use for U07.0 vaping, U07.1 COVID-19, and U09.9 post-COVID condition. If a single column carries ICD-9-CM codes as well, use Valid ICD Diagnosis.

Valid ICD-9 Diagnosis

Tests that values match the ICD-9-CM diagnosis code format.

Auto-generated when profiling finds a text column named as a diagnosis code rather than a procedure code, holding numeric codes with no letter prefix other than E or V.
Scope Column
Measures Count of invalid ICD-9 codes
Threshold Maximum acceptable count of invalid ICD-9 codes (default: 0)
On failure Invalid ICD-9 code formats found.
Default severity Fail

How it works: Accepts a 3-digit numeric code or a V-code (V followed by two digits), each with an optional extension of one or two digits, or an E-code (E followed by three digits) with an optional extension of one digit. The decimal point before an extension is optional, so 4019, 25000, and 78650 pass alongside 401.9, 250.00, and 786.50. A numeric or V-code carrying three extension digits is invalid, so 250000 does not pass. Lowercase and mixed-case values are accepted.

When to use: On diagnosis code columns that hold only ICD-9-CM — historical claims data, or a warehouse that keeps ICD-9-CM in its own column alongside newer records. If a single column carries ICD-10-CM codes as well, use Valid ICD Diagnosis.

Valid ICD Diagnosis

Tests that values match either the ICD-9-CM or the ICD-10-CM diagnosis code format.

Auto-generated when profiling finds a text column named as a diagnosis code rather than a procedure code, holding numeric codes mixed with codes that start with a letter other than E or V.
Scope Column
Measures Count of invalid ICD codes
Threshold Maximum acceptable count of invalid ICD codes (default: 0)
On failure Invalid ICD-9 or ICD-10 code formats found.
Default severity Fail

How it works: Accepts either code system — an ICD-9-CM 3-digit numeric code or V-code with an optional one- or two-digit extension, an ICD-9-CM E-code with an optional one-digit extension, or an ICD-10-CM category letter, digit, alphanumeric character, and optional extension of up to four alphanumeric characters. So 250.00, E849.1, V70.0, and E11.9 all pass. Decimal points are optional throughout, and a trailing decimal point with no extension is invalid. A value is invalid only when it fits neither system. Lowercase and mixed-case values are accepted.

When to use: On a column that spans the ICD-9 to ICD-10 transition and legitimately holds both. Because it accepts both systems, it is the most permissive of the three diagnosis tests — prefer Valid ICD-9 Diagnosis or Valid ICD-10 Diagnosis on a column that should hold only one.

Valid ICD-10 Procedure (PCS)

Tests that values match the ICD-10-PCS procedure code format.

Auto-generated when profiling finds a text column named as a procedure code rather than a diagnosis code, holding values that are consistently seven characters from the ICD-10-PCS character set.
Scope Column
Measures Count of invalid ICD-10-PCS codes
Threshold Maximum acceptable count of invalid ICD-10-PCS codes (default: 0)
On failure Invalid ICD-10-PCS code formats found.
Default severity Fail

How it works: Accepts exactly seven characters, each a digit or a letter other than I or O — the code set omits both to avoid confusion with 1 and 0. So 0JH60DZ and B2111ZZ pass, while 0JH60DI and 0JH60DO do not. ICD-10-PCS codes carry no decimal point. Lowercase and mixed-case values are accepted.

When to use: On the inpatient procedure code columns that facility claims carry. Auto-generation requires the column to be named as a procedure code as well as to hold the seven-character shape, because the shape alone does not separate a PCS code from an ICD-10-CM diagnosis code — B2111ZZ fits both. Add the test by hand for a procedure column whose name is not recognized. It checks the shape of the code, not the meaning of its section, body-system, and root-operation characters.

Valid HCPCS J-Code

Tests that values match the HCPCS Level II J-code format.

Auto-generated when profiling finds a text column named as a J-code whose values are consistently a letter followed by four digits.
Scope Column
Measures Count of invalid HCPCS J-codes
Threshold Maximum acceptable count of invalid HCPCS J-codes (default: 0)
On failure Invalid HCPCS J-code formats found.
Default severity Warning

How it works: Accepts the letter J followed by exactly four digits, for example J1234. Lowercase and mixed-case values are accepted.

When to use: On columns holding the J-code subset of HCPCS Level II, which covers drugs administered other than by mouth. If your column holds the full HCPCS Level II range, its A, B, E, G, L, and Q series codes and its 5-digit CPT codes will all count as invalid here — use Pattern Match instead.

Valid CMS POS

Tests that values are valid CMS Place of Service codes.

Auto-generated when profiling finds a text column named as a place of service, with a maximum length of two characters and fewer than 70 distinct values.
Scope Column
Measures Count of invalid POS codes
Threshold Maximum acceptable count of invalid POS codes (default: 0)
On failure Values found that are not valid CMS Place of Service codes.
Default severity Fail

How it works: Compares each value against the fixed two-digit Place of Service code set CMS publishes for professional claims. Because that set is fixed and small, this test checks membership in it rather than only the shape of the value. The set it holds is current as of August 2026, including codes 27, 58, and 66; codes CMS adds after that date count as invalid until the set is updated.

When to use: On the place-of-service column of professional claims. Because the code set is fixed, a failure here is a genuine invalid value rather than a formatting difference.

Valid NCT

Tests that values match the ClinicalTrials.gov NCT identifier format.

Auto-generated when profiling finds a text column named as an NCT identifier whose values are consistently eight digits, with or without a three-letter prefix.
Scope Column
Measures Count of invalid NCT identifiers
Threshold Maximum acceptable count of invalid NCT identifiers (default: 0)
On failure Invalid NCT identifier formats found.
Default severity Warning

How it works: Accepts exactly eight digits, optionally preceded by the literal prefix NCT. So both NCT01234567 and 01234567 pass — and so does any other eight-digit number, including date-shaped values such as 20260815. Lowercase and mixed-case prefixes are accepted, since registry identifiers are routinely copied from URLs and citations in lowercase.

When to use: On clinical trial registry identifier columns in study, enrollment, and outcomes data. On a column that stores only the numeric part of the identifier, expect the test to confirm the shape rather than the identifier — any eight-digit value passes.