Data Dictionary
rosterproof · published surface (PUBLISHED_V1)
Every column a customer can see, in plain English.
⏰ All timestamps in this feed are UTC, stored as
TIMESTAMP_NTZ (no timezone suffix — read them as UTC). All dates are calendar
dates as published by the source.
EXCLUSION_CURRENT — one row per currently-listed exclusion record per source
| Column | Type | Meaning |
|---|---|---|
| EXCLUSION_KEY | VARCHAR(64) | Stable record identity: SHA-256 over source + identity fields (name, DOB, state) + exclusion date. Stable across runs. Known caveat: if a source corrects a typo in an identity field, the key changes, so the correction appears in the change feed as REMOVED (old key) + ADDED (new key) rather than CHANGED. Join key for history and matches. |
| SOURCE_ID | VARCHAR(20) | Which list: LEIE (federal), TX, CA, NY, OH, GA, NC, AZ, WA, NJ, MD, MN, MO, TN, PA. Only publishable sources appear on this surface; sources whose terms of use block redistribution (currently Michigan) are held back until permission or public-records sourcing clears them. |
| ENTITY_TYPE | VARCHAR(20) | INDIVIDUAL or ENTITY (organization). |
| LAST_NAME / FIRST_NAME / MIDDLE_NAME | VARCHAR | Person name as published by the source (casing preserved; LEIE is all-caps, Texas mixed-case). Null for organizations. |
| BUSINESS_NAME | VARCHAR(500) | Organization name as published. Null for individuals. |
| DOB | DATE | Date of birth when the source publishes it (LEIE and Ohio; other sources do not). |
| NPI_SOURCE | VARCHAR(10) | NPI as published by the source — often missing. See EXCLUSIONS_SCREENABLE for validated NPIs. |
| LICENSE_NUMBER | VARCHAR(100) | Professional license number where published (TX, CA, NY, PA, MD, MO; LEIE does not publish one). |
| SPECIALTY | VARCHAR(200) | Specialty/occupation as published. |
| ADDRESS / CITY / STATE / ZIP | VARCHAR | Location as published (LEIE full address; Texas state only). STATE is a 2-letter code; unknown states are NULL. |
| EXCLUSION_TYPE | VARCHAR(100) | The source's own code or status (e.g. LEIE 1128b4, PA Precluded). Null when the source publishes none. |
| EXCLUSION_TYPE_DESC | VARCHAR(500) | Plain-English description of the code; 'Not specified by source' when none is published. Never null. |
| EXCLUSION_DATE | DATE | When the exclusion took effect. Occasionally a few days in the future (OIG forward-dates scheduled exclusions). |
| REINSTATEMENT_DATE | DATE | Reinstatement date when published. LEIE removes reinstated records from its file, so this is populated only for sources that print end dates (Texas, Pennsylvania, and NJ/AZ scheduled ends). |
| EXCLUSION_AUTHORITY | VARCHAR(200) | Statute / program cited (e.g. 42 USC 1320a-7). |
| FIRST_SEEN_AT | TIMESTAMP_NTZ (UTC) | When this pipeline first observed the record. Stable across refreshes. |
| LAST_SEEN_AT | TIMESTAMP_NTZ (UTC) | Most recent refresh that confirmed the record. |
| RAW_RECORD_HASH | VARCHAR(64) | Content hash used for change detection. |
EXCLUSION_HISTORY — every version of every record, forever (SCD2)
| Column | Type | Meaning |
|---|---|---|
| EXCLUSION_KEY | VARCHAR(64) | Joins to EXCLUSION_CURRENT. |
| VERSION_NUM | NUMBER | 1, 2, 3… per key. |
| CHANGE_TYPE | VARCHAR(20) | ADDED / CHANGED / REMOVED / REINSTATED. |
| VALID_FROM / VALID_TO | TIMESTAMP_NTZ (UTC) | Version validity window; VALID_TO null = still current. |
| IS_CURRENT | BOOLEAN | Convenience flag for the open version. |
| RECORD | VARIANT | Full JSON snapshot of the record at this version. |
The SCD2 guarantee, in plain English: version rows are immutable once written,
with one exception — when a newer version arrives, the prior version's VALID_TO is
filled to close its window. Content fields are never modified. Any past date therefore maps to
exactly one version, gap-free: WHERE :your_date >= VALID_FROM AND (:your_date <
VALID_TO OR VALID_TO IS NULL) always returns exactly one row per key.
How the tables relate — a worked example
Dr. Patel is excluded by Texas effective Jan 1 2026; Texas later publishes a reinstatement dated Mar 1 2026. The pipeline observes the record on Jan 2 and the reinstatement on Mar 2.
Moment 1 — during February (excluded, no end in sight):
| Object | What you see | How it got there |
|---|---|---|
| EXCLUSION_CURRENT | 1 row, REINSTATEMENT_DATE null | inserted Jan 2 |
| EXCLUSION_HISTORY | v1 ADDED, window open (VALID_TO null, IS_CURRENT TRUE) | inserted Jan 2 |
| CHANGE_FEED | 1 row: ADDED, observed Jan 2 | appended Jan 2 |
| EXCLUSIONS_SCREENABLE | IS_ACTIVE TRUE, EXCLUSION_END null | derived from EXCLUSION_CURRENT |
Moment 2 — after Mar 2 (reinstatement observed):
| Object | What you see | How it got there |
|---|---|---|
| EXCLUSION_CURRENT | still 1 row, REINSTATEMENT_DATE = 2026-03-01 | updated in the Mar 2 rebuild |
| EXCLUSION_HISTORY | v1: VALID_TO = Mar 2, IS_CURRENT FALSE — content untouched; v2 REINSTATED, window open | v1 window-closed; v2 inserted |
| CHANGE_FEED | 2 rows: the Jan ADDED (untouched) + REINSTATED observed Mar 2 | appended Mar 2 |
| EXCLUSIONS_SCREENABLE | IS_ACTIVE FALSE, EXCLUSION_END = 2026-03-01, basis PUBLISHED_BY_SOURCE | derived — end date now published |
Ask "what did we believe on Feb 10?" and EXCLUSION_HISTORY answers with exactly
one row: v1 (Feb 10 falls inside its Jan 2 → Mar 2 window) — Dr. Patel excluded, no end date.
That is the historical-claims validation story: point-in-time truth for any date, forever.
CHANGE_FEED — append-only delta stream (poll WHERE OBSERVED_AT > :last_run)
| Column | Type | Meaning |
|---|---|---|
| CHANGE_ID | NUMBER | Monotonic id. |
| OBSERVED_AT | TIMESTAMP_NTZ (UTC) | When the pipeline observed the change. |
| SOURCE_ID / EXCLUSION_KEY / CHANGE_TYPE | As above. | |
| SUMMARY | VARCHAR(1000) | Human-readable one-liner. |
| RECORD | VARIANT | Record snapshot (or removal note). |
SOURCE_REGISTRY — one row per covered source list
| Column | Type | Meaning |
|---|---|---|
| SOURCE_ID / SOURCE_NAME / JURISDICTION | Identity of the list (FEDERAL or state code). | |
| PUBLISHER / SOURCE_URL / UPDATE_CADENCE | Where the data comes from and how often it updates. EMAIL_REQUEST = the source delivers the file by email on request rather than publishing a fetchable URL (e.g. NJ OSC); refreshed each time a new file arrives. | |
| SOURCE_FORMAT | VARCHAR | What the source actually publishes — CSV, TSV, XLSX/XLS (Excel), PDF (extracted through a reconciliation-gated pipeline: independent double-extraction, per-page count checks, verbatim spot-checks against the PDF text layer), JSON (portal API endpoints), or HTML_TABLE (scraped portal pages). Whatever the format, every record lands in the same canonical schema — your screening query never depends on how a state chose to publish. |
| LAST_CHECKED_AT / LAST_CHANGED_AT | TIMESTAMP_NTZ (UTC) | Last fetch vs last time content actually changed. |
| ACTIVE_RECORD_COUNT | NUMBER | Rows currently in EXCLUSION_CURRENT for this source. |
EXCLUSIONS_SCREENABLE — the "join your roster against this" view (ALL records, active and ended)
Contains every exclusion record we have ever observed — currently-active ones and
ended ones (reinstated or no-longer-published) — so historical claim dates can be validated
against past exclusion windows. Filter IS_ACTIVE = TRUE for present-day screening.
| Column | Type | Meaning |
|---|---|---|
| EXCLUSION_KEY / SOURCE_ID / JURISDICTION | As above. | |
| ENTITY_TYPE | VARCHAR | INDIVIDUAL or ORGANIZATION. |
| LAST_NAME / FIRST_NAME | VARCHAR | Person name components as published (null for organizations). Compose display names as you prefer. |
| BUSINESS_NAME | VARCHAR | Organization name as published (null for individuals). |
| STATE | VARCHAR | 2-letter code as published; NULL when unknown. |
| EXCLUSION_REASON | VARCHAR(500) | Plain-English reason for the exclusion; 'Not specified by source' when none published. |
| EXCLUSION_AUTHORITY | VARCHAR(200) | Which government body excluded them — a state agency/statute citation as published, or 'SAM.gov / <agency>' for federal SAM records. Filter or route screening hits on this. |
| EXCLUSION_START | DATE | Start of the exclusion window (the source's exclusion date). Earliest genuine dates reach back to 1957: state master lists retain decades-old records. The 11 pre-1970 records (PA 1957–1968, TX 1959) were individually reviewed 2026-07-14 — distinct scattered dates, era-consistent statuses, some with administrative closures decades later — and confirmed as published by the sources, not data artifacts. |
| EXCLUSION_END | DATE | End of the window; NULL while open-ended. May be in the future — some sources (AZ) publish fixed-term exclusions with scheduled end dates. |
| EXCLUSION_END_BASIS | VARCHAR | How we know it ended: PUBLISHED_BY_SOURCE = the source printed a reinstatement date; REMOVED_BY_SOURCE = the source silently dropped the record — the end date is when our pipeline observed the removal (UTC), not an official reinstatement date. NULL while active. |
| IS_ACTIVE | BOOLEAN | TRUE while the exclusion is in force: still published AND not past any published end date (a future scheduled end is still active). |
| NPI | VARCHAR(10) | Best available NPI: NPPES-validated match first, else the source-published NPI. |
| NPI_ORIGIN | VARCHAR | VERIFIED_WITH_NPPES (validated against the national registry) or UNVERIFIED_SOURCE_CLAIM (the source printed an NPI that NPPES could not confirm). Null when no NPI. |
| NPI_STATUS | VARCHAR | Status of the NPI in the national registry, not of the exclusion: ACTIVE_IN_NPPES (live credential), DEACTIVATED_IN_NPPES (real NPI, since deactivated — common for excluded providers), NOT_FOUND_IN_NPPES. |
| MATCH_CONFIDENCE | VARCHAR | HIGH = deterministic (source NPI or exact identity), MEDIUM = strong corroborated match (e.g. license+state), LOW = needs review. |
| MATCH_BASIS | VARCHAR | Plain-English statement of HOW the NPI was matched: 'NPI published by source, verified', 'Unique name match in national registry', 'State license number match', or 'AI-adjudicated match among ambiguous candidates - human review required'. Null when no match. |
| MATCH_SCORE | FLOAT | 0.0–1.0 numeric score behind the label. |
| NEEDS_HUMAN_REVIEW | BOOLEAN | TRUE = do not auto-act on this record; have a person verify. Causes: deactivated NPI, unvalidated source-published NPI, or AI-adjudicated match. TRUE does not mean the data is unverified — it means the correct response requires human judgment (a verified-but-deactivated NPI calls for a historical claims sweep, not an automated block). |
| EXCLUDED_IN_MULTIPLE_JURISDICTIONS | BOOLEAN | TRUE when this record's NPI-confirmed identity is excluded by 2+ sources (see ENTITY_OVERLAP). Limitation: FALSE can also mean "no NPI resolved to check against". |
Filter guidance: screen against IS_ACTIVE = TRUE AND
MATCH_CONFIDENCE = 'HIGH' for automated blocking; route NEEDS_HUMAN_REVIEW =
TRUE rows to a human queue. Join-casing note: name fields are published
exactly as each source rendered them (LEIE all-caps, Texas mixed-case, …); normalize case on
both sides before name-based joins.
ENTITY_OVERLAP — providers excluded in multiple jurisdictions
One row per NPI that appears in 2 or more sources. Limitation (by design): NPI-confirmed matches only — records that could not be resolved to an NPI cannot be overlapped, so these counts are a floor, not a census.
| Column | Type | Meaning |
|---|---|---|
| NPI | VARCHAR(10) | The provider's NPI. |
| LAST_NAME / FIRST_NAME | VARCHAR | Person name components (representative across sources; null for organizations). |
| BUSINESS_NAME | VARCHAR | Organization name (null for individuals). |
| SOURCE_IDS | ARRAY | Which sources list this NPI (e.g. ["CA","LEIE","TX"]). |
| JURISDICTION_COUNT | NUMBER | How many sources list this NPI (2 up to 5 today). |
| EARLIEST_EXCLUSION_START | DATE | Earliest exclusion window start across sources. |
| ACTIVE_ANYWHERE | BOOLEAN | TRUE if at least one source lists the exclusion as currently in force. |
| NPI_STATUS | VARCHAR | ACTIVE_IN_NPPES or DEACTIVATED_IN_NPPES — the registry status of the credential itself. |
FEED_INFO — freshness/trust (check this first)
| Column | Type | Meaning |
|---|---|---|
| FEED_VERSION | VARCHAR | Feed schema version (1.0). |
| FEED_REFRESHED_AT_UTC | TIMESTAMP_NTZ (UTC) | Last successful pipeline run, whole feed. |
| SOURCE_ID / SOURCE_NAME / JURISDICTION | One row per source. | |
| SOURCE_LAST_CHECKED_AT_UTC / SOURCE_LAST_CHANGED_AT_UTC | TIMESTAMP_NTZ (UTC) | Per-source fetch vs content-change times. |
| ACTIVE_RECORD_COUNT | NUMBER | Per-source current row count. |
| NPPES_SNAPSHOT_DATE | DATE | NPPES monthly file used for NPI matching. |
Which object answers which question
EXCLUSIONS_SCREENABLE= final answers for screening. One row per exclusion record with the best available NPI, confidence labels, and review flags. This is what you join rosters/claims against.CHANGE_FEED= what changed since my last poll.EXCLUSION_HISTORY= full audit trail of every version of every record.FEED_INFO= is the feed fresh and which NPPES snapshot backs the matches.