Models and Outputs

All models are published to the spotlight schema.

Current-run models

These are rebuilt on every run.

reconciliation__monthly_summary

Monthly enrollment and claims metrics joined at the member-month level.

Grain: data_source, payer, plan, year_month_int

Column	Description
data_source	Source system identifier
payer	Payer name
plan	Plan name
year_month_int	Month as integer (e.g., 202603)
member_months	Count of enrolled member-months
members	Distinct members enrolled in the month
claims	Distinct claim IDs with activity in the month
claim_lines	Total claim lines in the month
paid_amount	Total paid for claims matching enrolled members in that month
paid_amount_without_enrollment	Total paid for all claims in the month (ignoring enrollment)
members_with_claims	Members who had at least one claim
pct_members_with_claims	members_with_claims / members × 100
claims_per_1000	claims / member_months × 1000
pmpm_paid	paid_amount / member_months
avg_paid_per_claim	paid_amount / claims

The model creates a calendar spine to ensure every month in the data range is represented, even months with zero claims.

dq_results__input_layer_field_summary

Field-level completeness analysis across input-layer tables.

Grain: run_id, data_source, payer, plan, model_name, field_name

Column	Description
run_id	dbt invocation or custom run identifier
run_ts	Run timestamp
model_name	Source table (e.g., input_layer__eligibility)
field_name	Column being evaluated
total_rows	Total rows in the source table for this data source/payer/plan
applicable_rows	Rows where this field should be populated
not_applicable_rows	Rows excluded by business logic (e.g., DRG for professional claims)
null_rows	Rows where the field is null or blank
non_null_rows	Rows where the field has a value
total_rows_pct	Always 100
applicable_rows_pct	applicable_rows / total_rows × 100
null_rows_pct	null_rows / total_rows × 100
non_null_rows_pct	non_null_rows / total_rows × 100

dq_results__terminology_field_summary

Field-level terminology validation. Same columns as the completeness summary, plus:

Column	Description
valid_rows	Non-null values that pass the terminology check
invalid_rows	Non-null values that fail the terminology check
valid_rows_pct	valid_rows / total_rows × 100
invalid_rows_pct	invalid_rows / total_rows × 100
valid_rows_applicable_pct	valid_rows / applicable_rows × 100

History models

Built only when dq_persist_results: true. These are incremental tables that accumulate rows across official runs.

reconciliation_history__monthly_summary

Same columns as reconciliation__monthly_summary plus run_id and run_ts. Uses merge (or delete+insert) to upsert by (run_id, data_source, payer, plan, year_month_int).

dq_history__input_layer_field_summary

Same columns as dq_results__input_layer_field_summary. Upserts by (run_id, data_source, payer, plan, model_name, field_name).

dq_history__terminology_field_summary

Same columns as dq_results__terminology_field_summary. Upserts by (run_id, data_source, payer, plan, model_name, field_name).

Convenience views

Each history model has two companion views:

View	Returns
*__latest_official_run	Rows from the most recent run_ts / run_id
*__previous_official_run	Rows from the second most recent run_ts / run_id

These make it easy to compare consecutive runs without writing window functions.

Applicability rules

Not all fields apply to all claim types. The package uses these rules when calculating applicable vs. not-applicable rows for medical claims:

Field	Applicable to
drg_code, drg_code_type	Institutional claims only
revenue_center_code	Institutional claims only
procedure_code_1/2/3	Institutional claims only
discharge_disposition_code	Institutional claims only
admit_source_code	Institutional claims only
admit_type_code	Institutional claims only
bill_type_code	Institutional claims only
place_of_service_code	Professional claims only
All other fields	All claim types

This prevents professional claims from being counted as "missing" for fields that only exist on institutional claims, and vice versa.

Tests

Run all package tests:

dbt test -s tag:spotlight

Test	Validates
Grain uniqueness	No duplicate rows per unique key combination on all summary and history tables
Percentage bounds	All *_pct columns are between 0 and 100
Not null	Required fields (run_id, run_ts, data_source) are populated