Data dictionary

Every table and column in the Channel Keys listing. Refer to objects as CORE.<TABLE> under the database name you choose when you get the listing. Column names are plain uppercase identifiers, so no quoting is needed.

Keys and conventions

• NDC11: 11-digit, zero-padded 5-4-2 NDC. XREF_NDC_FORMATS maps every FDA 10-digit format to it.
• APPL_TYPE and APPL_NO: the Orange Book application key (N for NDA, A for ANDA), APPL_NO zero-padded to six digits.
• LABELER_CODE: the first five digits of the NDC. DIM_LABELER carries the best labeler name and the matched Open Payments manufacturer with its match score.
• ID_340B: the HRSA covered entity ID. Contract pharmacies key on ID_340B plus CONTRACT_ID.
• SNAPSHOT_DATE: the refresh a row came from. CHANGE_LOG compares consecutive refreshes; META_SOURCE_RUN records the agency files behind each one.
• Derived fields (EST_LOE_DATE, IS_DISCONTINUED, IS_ACTIVE, manufacturer matches) are ours. Validate before regulatory or contractual use.

Tables

• NDC_PRODUCT
• NDC_PACKAGE
• DIM_NDC11
• DIM_LABELER
• XREF_NDC_FORMATS
• OB_PRODUCT
• OB_PATENT
• OB_EXCLUSIVITY
• DIM_APPLICATION
• SDUD_UTILIZATION
• OPAIS_COVERED_ENTITY
• OPAIS_CONTRACT_PHARMACY
• OPAIS_CE_NPI
• OPAIS_CE_MEDICAID
• OPAIS_CE_SHIPPING
• OP_PAYMENT_SUMMARY
• OP_PRODUCT_SUMMARY
• OP_RECIPIENT_MANUFACTURER_YEAR
• OP_ENTITY_PROFILE
• CHANGE_LOG
• META_SOURCE_RUN

NDC_PRODUCT 345,642 rows

FDA NDC Directory product rows (finished, excluded and unfinished files) with typed dates and normalized application numbers.

NDC_PACKAGE 640,244 rows

FDA NDC Directory package rows with the 11-digit NDC.

DIM_NDC11 638,180 rows

One row per 11-digit NDC (5-4-2) with product, labeler, application, marketing dates, Orange Book LOE fields and discontinuation flag. Precedence: finished > unfinished > excluded.

DIM_LABELER 18,241 rows

One row per labeler code with best name, product counts, marketing-date range and the matched CMS Open Payments manufacturer (exact or fuzzy, score shown).

XREF_NDC_FORMATS 640,244 rows

Crosswalk between FDA 10-digit hyphenated NDCs (4-4-2, 5-3-2, 5-4-1), 10-digit unhyphenated and the 11-digit claims format.

OB_PRODUCT 48,865 rows

Orange Book products (applicant, TE code, RLD/RS flags, approval date).

OB_PATENT 22,438 rows

Orange Book patents with expiry dates, use codes and delist flags.

OB_EXCLUSIVITY 2,347 rows

Orange Book exclusivity codes and dates.

DIM_APPLICATION 27,428 rows

One row per NDA/ANDA with first approval, last patent expiry, last exclusivity, estimated loss-of-exclusivity date and linked NDC product counts.

SDUD_UTILIZATION 17,221,265 rows

Medicaid State Drug Utilization Data by state, NDC, year and quarter (FFS and MCO), typed measures and suppression flag.

OPAIS_COVERED_ENTITY 94,951 rows

340B OPAIS covered entities (participating and terminated) with addresses, officials and termination details.

OPAIS_CONTRACT_PHARMACY 409,900 rows

340B contract pharmacy arrangements per covered entity with begin and termination dates and an is_active flag.

OPAIS_CE_NPI 759,991 rows

NPIs registered to each 340B covered entity.

OPAIS_CE_MEDICAID 297,056 rows

Medicaid billing numbers registered to each 340B covered entity.

OPAIS_CE_SHIPPING 165,048 rows

Ship-to addresses registered to each 340B covered entity.

OP_PAYMENT_SUMMARY 20,631 rows

CMS Open Payments general payments summarized by manufacturer, program year, recipient type, nature and form of payment.

OP_PRODUCT_SUMMARY 66,151 rows

Open Payments general payments summarized by manufacturer and product (with NDC-11 where the reported NDC resolves), split and full-amount views.

OP_RECIPIENT_MANUFACTURER_YEAR 9,461,472 rows

Open Payments general payments summarized by covered recipient (NPI or profile id), manufacturer and program year.

OP_ENTITY_PROFILE 4,482 rows

Open Payments company and teaching-hospital profiles (physician profiles excluded).

CHANGE_LOG 0 rows

Row-level adds, removals and field changes between consecutive refreshes for NDC, Orange Book and 340B tables (old and new values as JSON).

META_SOURCE_RUN 13 rows

Source files behind this refresh: URL, size, SHA-256 and server last-modified.