Field Validations

Spotlight DQ evaluates fields across three input-layer tables. Each field is checked for completeness (null vs non-null). Fields in the terminology summary are additionally validated against reference data or business rules.

Eligibility fields

Field	Completeness	Terminology validation
gender	Yes	Valid values: male, female, unknown
race	Yes	Must exist in terminology__race
dual_status_code	Yes	Must exist in terminology__medicare_dual_eligibility
original_reason_entitlement_code	Yes	Valid values: 0, 1, 2, 3
medicare_status_code	Yes	Valid values: 00, 10, 11, 20, 21, 31, 40
birth_date	Yes	Must be after 1900-01-01 and before current date
death_date	Yes	Must be after 1900-01-01 and before current date
social_security_number	Yes	9 digits or ###-##-#### format
address	Yes	Non-null, non-blank, not literal "NULL"
city	Yes	Non-null, non-blank, not literal "NULL"
state	Yes	Must exist in reference_data__ansi_fips_state (abbreviation, name, or code)
zip_code	Yes	5-digit or 9-digit (with or without hyphen)
phone	Yes	10 digits, or 11 digits starting with 1
email	Yes	Non-null and contains @

Medical claim fields

Field	Completeness	Terminology validation	Applicability
claim_type	Yes	Valid values: professional, institutional	All
claim_start_date	Yes	After 1970-01-01 and before current date	All
claim_end_date	Yes	After 1970-01-01 and before current date	All
claim_line_start_date	Yes	After 1970-01-01 and before current date	All
claim_line_end_date	Yes	After 1970-01-01 and before current date	All
admission_date	Yes	After 1970-01-01 and before current date	All
discharge_date	Yes	After 1970-01-01 and before current date	All
paid_date	Yes	After 1970-01-01 and before current date	All
service_unit_quantity	Yes	Must be greater than 0	All
hcpcs_modifier_1	Yes	Non-null (presence only)	All
drg_code	Yes	Must exist in terminology__ms_drg or terminology__apr_drg (based on drg_code_type)	Institutional
revenue_center_code	Yes	Must exist in terminology__revenue_center	Institutional
hcpcs_code	Yes	Must exist in terminology__hcpcs_level_2	All
diagnosis_code_1	Yes	Must exist in terminology__icd_10_cm	All
diagnosis_code_2	Yes	Must exist in terminology__icd_10_cm	All
diagnosis_code_3	Yes	Must exist in terminology__icd_10_cm	All
procedure_code_1	Yes	Must exist in terminology__icd_10_pcs	Institutional
procedure_code_2	Yes	Must exist in terminology__icd_10_pcs	Institutional
procedure_code_3	Yes	Must exist in terminology__icd_10_pcs	Institutional
discharge_disposition_code	Yes	Must exist in terminology__discharge_disposition	Institutional
admit_source_code	Yes	Must exist in terminology__admit_source	Institutional
admit_type_code	Yes	Must exist in terminology__admit_type	Institutional
bill_type_code	Yes	Must exist in terminology__bill_type	Institutional
place_of_service_code	Yes	Must exist in terminology__place_of_service	Professional
rendering_npi	Yes	Must exist in terminology__provider	All
billing_npi	Yes	Must exist in terminology__provider	All
facility_npi	Yes	Must exist in terminology__provider	All

Pharmacy claim fields

Field	Completeness	Terminology validation
prescribing_provider_npi	Yes	Must exist in terminology__provider
dispensing_provider_npi	Yes	Must exist in terminology__provider
dispensing_date	Yes	After 1970-01-01 and before current date
ndc_code	Yes	Must exist in terminology__ndc
quantity	Yes	Must be greater than 0
days_supply	Yes	Must be greater than 0
refills	Yes	Must be greater than 0
paid_date	Yes	After 1970-01-01 and before current date

How validation works

For each field in the terminology summary:

1. Total rows: all rows in the source table for the data_source / payer / plan combination.
2. Applicable rows: rows where the field is expected to have a value (respects claim-type applicability rules).
3. Null rows: applicable rows where the value is null or blank.
4. Non-null rows: applicable rows with a value present.
5. Valid rows: non-null rows where the value passes the terminology check (reference table join or rule).
6. Invalid rows: non-null rows where the value fails the terminology check.

The key metric for most monitoring is valid_rows_applicable_pct, the percentage of applicable, non-null values that are valid. A drop in this metric across runs signals terminology drift or data quality degradation.

The DQ Results Viewer shows pass/fail status against configurable thresholds, with per-metric validity distributions for quick triage.