Data ontology
The estate, by business domain 14 domains, the apps & tables, metrics and reports inside each, and the flows that funnel between them. Zoom into any lane to its lineage, then into any table to its fields. 5 of 14 contested; 14 of 14 have no owner.
↰ Zoom out ▲ Explode OTD flow inferred (0 keys) credit_amount fork
Finance CONTESTED Key numbers in this domain are computed inconsistently and unowned. 107 tables · 19 metrics · 39 reports — UNOWNED › Finance — Key numbers in this domain are computed inconsistently and unowned. (source: —; UNOWNED — no owns edge) Membership CONTESTED Key numbers in this domain are computed inconsistently and unowned. 84 tables · 11 metrics · 25 reports — UNOWNED › Membership — Key numbers in this domain are computed inconsistently and unowned. (source: —; UNOWNED — no owns edge) Claims CONTESTED Key numbers in this domain are computed inconsistently and unowned. 60 tables · 9 metrics · 17 reports — UNOWNED › Claims — Key numbers in this domain are computed inconsistently and unowned. (source: —; UNOWNED — no owns edge) Premium & Billing CONTESTED Key numbers in this domain are computed inconsistently and unowned. 56 tables · 5 metrics · 18 reports — UNOWNED › Premium & Billing — Key numbers in this domain are computed inconsistently and unowned. (source: —; UNOWNED — no owns edge) Risk & Actuarial CONTESTED Key numbers in this domain are computed inconsistently and unowned. 34 tables · 7 metrics · 14 reports — UNOWNED › Risk & Actuarial — Key numbers in this domain are computed inconsistently and unowned. (source: —; UNOWNED — no owns edge) Provider CLEAN In regular use; lightly governed. 85 tables · 9 metrics · 21 reports — UNOWNED › Provider — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Clinical & Care CLEAN In regular use; lightly governed. 63 tables · 9 metrics · 24 reports — UNOWNED › Clinical & Care — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Workforce CLEAN In regular use; lightly governed. 59 tables · 8 metrics · 18 reports — UNOWNED › Workforce — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Appeals CLEAN In regular use; lightly governed. 38 tables · 7 metrics · 18 reports — UNOWNED › Appeals — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Sales & Broker CLEAN In regular use; lightly governed. 41 tables · 3 metrics · 13 reports — UNOWNED › Sales & Broker — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Pharmacy CLEAN In regular use; lightly governed. 35 tables · 7 metrics · 13 reports — UNOWNED › Pharmacy — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Reporting & BI CLEAN In regular use; lightly governed. 35 tables · 0 metrics · 18 reports — UNOWNED › Reporting & BI — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Quality & STARS CLEAN In regular use; lightly governed. 33 tables · 6 metrics · 13 reports — UNOWNED › Quality & STARS — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Fraud CLEAN In regular use; lightly governed. 29 tables · 6 metrics · 13 reports — UNOWNED › Fraud — In regular use; lightly governed. (source: —; UNOWNED — no owns edge) Source Tables Metrics Reports & dashboards Finance — Source system: — gl_posting 240,000,000 rows gl_posting — 240,000,000 rows. Click to open its fields. claim_line_payment_allocation 195,000,000 rows claim_line_payment_allocation — 195,000,000 rows. Click to open its fields. ar_transaction 120,000,000 rows ar_transaction — 120,000,000 rows. Click to open its fields. gl_posting 48,000,000 rows gl_posting — 48,000,000 rows. Click to open its fields. rebate_accrual 21,000,000 rows rebate_accrual — 21,000,000 rows. Click to open its fields. ar_aging_snapshot 18,000,000 rows ar_aging_snapshot — 18,000,000 rows. Click to open its fields. provider_payment 17,500,000 rows provider_payment — 17,500,000 rows. Click to open its fields. check_register 17,500,000 rows check_register — 17,500,000 rows. Click to open its fields. ar_balance 15,000,000 rows ar_balance — 15,000,000 rows. Click to open its fields. premium_revenue_recognition 9,600,000 rows premium_revenue_recognition — 9,600,000 rows. Click to open its fields. Net Claims Cost contested Net Claims Cost — contested Days in Claims Pa… contested Days in Claims Payable (DCP) — contested COB Recovery Rate metric COB Recovery Rate — defined Provider Payment … metric Provider Payment Accuracy — defined Capitation as % o… metric Capitation as % of Medical Spend — defined Rebate Capture Ra… metric Rebate Capture Rate — defined Premium AR Aging … metric Premium AR Aging % > 90 days — defined Days Sales Outsta… metric Days Sales Outstanding (Premium DSO) — defined Provider Payment Ac… computes Provider Payment Accuracy & Recovery Capitation Settleme… computes Capitation Settlement Report Claims GL Reconcili… $2.41B% OTD Claims GL Reconciliation — $2.41B% (undefined) Monthly Rebate Accr… computes Monthly Rebate Accrual & Capture Rebate Reconciliati… computes Rebate Reconciliation Workbook AR Aging Summary (G… computes AR Aging Summary (Group & Individual) Premium Revenue Rec… $3.94B annual% OTD Premium Revenue Recognition Roll-forward — $3.94B annual% (undefined) Write-off & Bad Deb… computes Write-off & Bad Debt Reserve Analysis CMS APTC/CSR Reconc… computes CMS APTC/CSR Reconciliation Exception Report Collections Pipelin… computes Collections Pipeline & Recovery ← billing_account → gl_… Incoming flow from Premium & Billing: billing_account → gl_cost_center_master ← refund_transaction → … Incoming flow from Premium & Billing: refund_transaction → gl_chart_of_accounts ← gl_premium_revenue_re… Incoming flow from Premium & Billing: gl_premium_revenue_recognition → gl_chart_of_accounts ← gl_ibnr_reserve_posti… Incoming flow from Risk & Actuarial: gl_ibnr_reserve_posting → gl_chart_of_accounts ← gl_account_mapping_xr… Incoming flow from Reporting & BI: gl_account_mapping_xref → gl_chart_of_accounts ← trial_balance_snapsho… Incoming flow from Reporting & BI: trial_balance_snapshot → gl_chart_of_accounts ← campaign → gl_cost_ce… Incoming flow from Sales & Broker: campaign → gl_cost_center_master ← sales_employee → gl_c… Incoming flow from Workforce: sales_employee → gl_cost_center_master ← employee_master → gl_… Incoming flow from Workforce: employee_master → gl_cost_center_master ← position → gl_cost_ce… Incoming flow from Workforce: position → gl_cost_center_master ← department → gl_chart… Incoming flow from Workforce: department → gl_chart_of_accounts ← department → gl_cost_… Incoming flow from Workforce: department → gl_cost_center_master ← org_unit → gl_cost_ce… Incoming flow from Workforce: org_unit → gl_cost_center_master ← payroll_detail → gl_c… Incoming flow from Workforce: payroll_detail → gl_chart_of_accounts ← payroll_detail → gl_c… Incoming flow from Workforce: payroll_detail → gl_cost_center_master ← time_entry → gl_cost_… Incoming flow from Workforce: time_entry → gl_cost_center_master ← requisition → gl_cost… Incoming flow from Workforce: requisition → gl_cost_center_master ← headcount_snapshot → … Incoming flow from Reporting & BI: headcount_snapshot → gl_cost_center_master ← contingent_worker → g… Incoming flow from Workforce: contingent_worker → gl_cost_center_master ← fact_workforce_headco… Incoming flow from Workforce: fact_workforce_headcount → dim_cost_center ← fact_workforce_headco… Incoming flow from Workforce: fact_workforce_headcount → gl_cost_center_master ← dim_employee → gl_cos… Incoming flow from Workforce: dim_employee → gl_cost_center_master ← provider_incentive_pa… Incoming flow from Provider: provider_incentive_payment → gl_chart_of_accounts ← provider_incentive_pa… Incoming flow from Provider: provider_incentive_payment → gl_cost_center_master provider_payment → re… → Outgoing flow to Claims: provider_payment → remittance_advice provider_payment → pr… → Outgoing flow to Provider: provider_payment → provider_master provider_payment → pr… → Outgoing flow to Provider: provider_payment → provider_master capitation_payment → … → Outgoing flow to Provider: capitation_payment → provider_master capitation_payment → … → Outgoing flow to Provider: capitation_payment → provider_master interest_payment → cl… → Outgoing flow to Claims: interest_payment → claim_header cob_recovery → claim_… → Outgoing flow to Claims: cob_recovery → claim_line subrogation_case → me… → Outgoing flow to Membership: subrogation_case → member_eligibility subrogation_case → cl… → Outgoing flow to Claims: subrogation_case → claim_header subrogation_case → me… → Outgoing flow to Membership: subrogation_case → member_master overpayment_recovery … → Outgoing flow to Claims: overpayment_recovery → claim_line overpayment_recovery … → Outgoing flow to Provider: overpayment_recovery → provider_master overpayment_recovery … → Outgoing flow to Provider: overpayment_recovery → provider_master claim_line_payment_al… → Outgoing flow to Claims: claim_line_payment_allocation → claim_line gl_posting → claim_he… → Outgoing flow to Claims: gl_posting → claim_header rebate_contract_term … → Outgoing flow to Pharmacy: rebate_contract_term → formulary_master rebate_contract_term … → Outgoing flow to Pharmacy: rebate_contract_term → ndc_drug_master rebate_accrual → phar… → Outgoing flow to Pharmacy: rebate_accrual → pharmacy_claim_line rebate_accrual → clai… → Outgoing flow to Claims: rebate_accrual → claim_line rebate_accrual → phar… → Outgoing flow to Pharmacy: rebate_accrual → pharmacy_claim_header rebate_accrual → ndc_… → Outgoing flow to Pharmacy: rebate_accrual → ndc_drug_master rebate_invoice → invo… → Outgoing flow to Premium & Billing: rebate_invoice → invoice_header rebate_payment → invo… → Outgoing flow to Premium & Billing: rebate_payment → invoice_header rebate_dispute → invo… → Outgoing flow to Premium & Billing: rebate_dispute → invoice_header dir_fee → pharmacy_cl… → Outgoing flow to Pharmacy: dir_fee → pharmacy_claim_header pharmacy_remittance →… → Outgoing flow to Provider: pharmacy_remittance → pharmacy_master pharmacy_remittance →… → Outgoing flow to Provider: pharmacy_remittance → provider_master ar_balance → billing_… → Outgoing flow to Premium & Billing: ar_balance → billing_account ar_transaction → bill… → Outgoing flow to Premium & Billing: ar_transaction → billing_account ar_transaction → invo… → Outgoing flow to Premium & Billing: ar_transaction → invoice_header ar_aging_snapshot → b… → Outgoing flow to Premium & Billing: ar_aging_snapshot → billing_account write_off → billing_a… → Outgoing flow to Premium & Billing: write_off → billing_account write_off → invoice_h… → Outgoing flow to Premium & Billing: write_off → invoice_header cms_payment_reconcili… → Outgoing flow to Premium & Billing: cms_payment_reconciliation → subsidy_aptc_detail collections_case → bi… → Outgoing flow to Premium & Billing: collections_case → billing_account premium_revenue_recog… → Outgoing flow to Premium & Billing: premium_revenue_recognition → billing_plan_ref premium_revenue_recog… → Outgoing flow to Membership: premium_revenue_recognition → plan_master gl_journal_import_int… → Outgoing flow to Membership: gl_journal_import_interface → employer_group ap_supplier_master → … → Outgoing flow to Provider: ap_supplier_master → provider_master ap_invoice_header → i… → Outgoing flow to Premium & Billing: ap_invoice_header → invoice_header ap_invoice_line → inv… → Outgoing flow to Premium & Billing: ap_invoice_line → invoice_header ap_payment_applicatio… → Outgoing flow to Premium & Billing: ap_payment_application → invoice_header ap_claims_disbursemen… → Outgoing flow to Claims: ap_claims_disbursement_feed → claim_header ap_claims_disbursemen… → Outgoing flow to Provider: ap_claims_disbursement_feed → provider_master ap_hold → invoice_hea… → Outgoing flow to Premium & Billing: ap_hold → invoice_header ar_cash_receipt → emp… → Outgoing flow to Membership: ar_cash_receipt → employer_group gl_capitation_expense… → Outgoing flow to Provider: gl_capitation_expense → provider_master expense_report → empl… → Outgoing flow to Workforce: expense_report → employee_master gl_security_assignmen… → Outgoing flow to Workforce: gl_security_assignment → employee_master loss_ratio_summary → … → Outgoing flow to Risk & Actuarial: loss_ratio_summary → reserve_run_header loss_ratio_summary → … → Outgoing flow to Membership: loss_ratio_summary → group_benefit_offering actuarial_gl_bridge →… → Outgoing flow to Risk & Actuarial: actuarial_gl_bridge → reserve_run_header overpayment → siu_case → Outgoing flow to Fraud: overpayment → siu_case overpayment → claim_h… → Outgoing flow to Claims: overpayment → claim_header overpayment → case → Outgoing flow to Clinical & Care: overpayment → case recovery → case → Outgoing flow to Clinical & Care: recovery → case recovery_transaction … → Outgoing flow to Claims: recovery_transaction → claim_header savings_estimate → si… → Outgoing flow to Fraud: savings_estimate → siu_case savings_estimate → ca… → Outgoing flow to Clinical & Care: savings_estimate → case broker_payment → brok… → Outgoing flow to Sales & Broker: broker_payment → broker_master labor_cost_allocation… → Outgoing flow to Workforce: labor_cost_allocation → employee_master labor_cost_allocation… → Outgoing flow to Workforce: labor_cost_allocation → department mart_mlr_summary → di… → Outgoing flow to Reporting & BI: mart_mlr_summary → dim_line_of_business mart_finance_pnl → di… → Outgoing flow to Reporting & BI: mart_finance_pnl → dim_line_of_business Source Tables Metrics Reports & dashboards Membership — Source system: — snapshot_membership_daily 2,100,000,000 rows snapshot_membership_daily — 2,100,000,000 rows. Click to open its fields. stg_eligibility_raw 52,000,000 rows stg_eligibility_raw — 52,000,000 rows. Click to open its fields. audit_trail 42,000,000 rows audit_trail — 42,000,000 rows. Click to open its fields. member_month_summary 21,600,000 rows member_month_summary — 21,600,000 rows. Click to open its fields. member_month_actuarial 21,600,000 rows member_month_actuarial — 21,600,000 rows. Click to open its fields. accumulator_transaction 18,400,000 rows accumulator_transaction — 18,400,000 rows. Click to open its fields. eligibility_verification_log 8,900,000 rows eligibility_verification_log — 8,900,000 rows. Click to open its fields. churn_prediction 7,600,000 rows churn_prediction — 7,600,000 rows. Click to open its fields. enrollment_834_inbound 7,200,000 rows enrollment_834_inbound — 7,200,000 rows. Click to open its fields. sold_membership_snapshot 7,200,000 rows sold_membership_snapshot — 7,200,000 rows. Click to open its fields. Member Months contested Member Months — contested Active Membership contested Active Membership — contested Retro Enrollment … metric Retro Enrollment Rate — defined 834 Auto-Adjudica… metric 834 Auto-Adjudication Rate — defined Member Months Gro… metric Member Months Growth (YoY) — defined Dependent Coverag… metric Dependent Coverage Ratio — defined Termination Rate … metric Termination Rate (Annualized) — defined Eligibility Verif… undefined Eligibility Verification Match Rate — undefined Daily Active Member… 601,840% OTD Daily Active Membership Snapshot — 601,840% (undefined) Member Months Trend… 7,184,200% OTD Member Months Trend (Monthly) — 7,184,200% (undefined) Enrollment Operatio… computes Enrollment Operations Dashboard 834 Error Aging Rep… computes 834 Error Aging Report Retro Enrollment Ex… computes Retro Enrollment Exception Log Group Census & Enro… 601,840% OTD Group Census & Enrollment by Employer — 601,840% (undefined) CMS MMR Reconciliat… computes CMS MMR Reconciliation Report Medicaid Redetermin… computes Medicaid Redetermination Worklist Dependent Verificat… computes Dependent Verification Audit Termination & Reins… computes Termination & Reinstatement Summary ← claim_header → member… Incoming flow from Claims: claim_header → member_eligibility ← claim_header → member… Incoming flow from Claims: claim_header → member_master ← claim_header → plan_m… Incoming flow from Claims: claim_header → plan_master ← claim_header → group_… Incoming flow from Claims: claim_header → group_benefit_offering ← adjudication_result →… Incoming flow from Claims: adjudication_result → group_benefit_offering ← cob_detail → member_m… Incoming flow from Claims: cob_detail → member_master ← other_insurance_cover… Incoming flow from Claims: other_insurance_coverage → member_eligibility ← other_insurance_cover… Incoming flow from Claims: other_insurance_coverage → member_master ← eob → member_master Incoming flow from Claims: eob → member_master ← capitation_roster → m… Incoming flow from Provider: capitation_roster → member_eligibility ← capitation_roster → m… Incoming flow from Provider: capitation_roster → member_master ← capitation_roster → p… Incoming flow from Provider: capitation_roster → plan_master ← subrogation_case → me… Incoming flow from Finance: subrogation_case → member_eligibility ← subrogation_case → me… Incoming flow from Finance: subrogation_case → member_master ← member_cost_share_det… Incoming flow from Premium & Billing: member_cost_share_detail → member_master ← pharmacy_crossover_cl… Incoming flow from Pharmacy: pharmacy_crossover_claim → member_eligibility ← pharmacy_crossover_cl… Incoming flow from Pharmacy: pharmacy_crossover_claim → member_master ← coordination_of_care_… Incoming flow from Clinical & Care: coordination_of_care_flag → member_eligibility ← coordination_of_care_… Incoming flow from Clinical & Care: coordination_of_care_flag → member_master ← risk_adjustment_claim… Incoming flow from Risk & Actuarial: risk_adjustment_claim_extract → member_master ← broker_attribution → … Incoming flow from Sales & Broker: broker_attribution → member_master ← broker_attribution → … Incoming flow from Sales & Broker: broker_attribution → employer_group ← stars_gap_claim_flag … Incoming flow from Quality & STARS: stars_gap_claim_flag → member_eligibility ← stars_gap_claim_flag … Incoming flow from Quality & STARS: stars_gap_claim_flag → member_master ← claim_payment_summary… Incoming flow from Reporting & BI: claim_payment_summary → member_master ← member_claim_history_… Incoming flow from Reporting & BI: member_claim_history_index → member_eligibility ← member_claim_history_… Incoming flow from Reporting & BI: member_claim_history_index → member_master ← premium_rate_table → … Incoming flow from Premium & Billing: premium_rate_table → benefit_plan ← premium_rate_table → … Incoming flow from Premium & Billing: premium_rate_table → group_benefit_offering ← member_premium → memb… Incoming flow from Premium & Billing: member_premium → member_master ← member_premium → bene… Incoming flow from Premium & Billing: member_premium → benefit_plan ← member_premium → grou… Incoming flow from Premium & Billing: member_premium → group_benefit_offering ← aptc_subsidy → member… Incoming flow from Premium & Billing: aptc_subsidy → member_master ← aptc_subsidy → benefi… Incoming flow from Premium & Billing: aptc_subsidy → benefit_plan ← aptc_subsidy → group_… Incoming flow from Premium & Billing: aptc_subsidy → group_benefit_offering ← broker_of_record → em… Incoming flow from Sales & Broker: broker_of_record → employer_group ← broker_of_record → me… Incoming flow from Sales & Broker: broker_of_record → member_master ← group_renewal → emplo… Incoming flow from Sales & Broker: group_renewal → employer_group ← network_affiliation →… Incoming flow from Provider: network_affiliation → plan_master ← network_plan_map → pl… Incoming flow from Provider: network_plan_map → plan_master ← network_plan_map → gr… Incoming flow from Provider: network_plan_map → group_benefit_offering ← provider_group → empl… Incoming flow from Provider: provider_group → employer_group ← provider_group_member… Incoming flow from Provider: provider_group_membership → employer_group ← provider_roster_load … Incoming flow from Provider: provider_roster_load → employer_group ← auth_request → member… Incoming flow from Clinical & Care: auth_request → member_master ← auth_diagnosis → memb… Incoming flow from Clinical & Care: auth_diagnosis → member_master ← auth_note → member_ma… Incoming flow from Clinical & Care: auth_note → member_master ← health_risk_assessmen… Incoming flow from Clinical & Care: health_risk_assessment → plan_master ← pharmacy_claim_header… Incoming flow from Pharmacy: pharmacy_claim_header → member_pharmacy_eligibility ← pharmacy_claim_header… Incoming flow from Pharmacy: pharmacy_claim_header → plan_master ← prior_auth_request → … Incoming flow from Pharmacy: prior_auth_request → member_pharmacy_eligibility ← pharmacy_benefit_accu… Incoming flow from Premium & Billing: pharmacy_benefit_accumulator → member_pharmacy_eligibility ← pharmacy_benefit_accu… Incoming flow from Premium & Billing: pharmacy_benefit_accumulator → group_benefit_offering ← specialty_drug_case →… Incoming flow from Clinical & Care: specialty_drug_case → member_pharmacy_eligibility ← medication_adherence_… Incoming flow from Quality & STARS: medication_adherence_metric → member_pharmacy_eligibility ← opioid_risk_score → m… Incoming flow from Risk & Actuarial: opioid_risk_score → member_pharmacy_eligibility ← plan_pharmacy_benefit… Incoming flow from Premium & Billing: plan_pharmacy_benefit_config → plan_master ← plan_pharmacy_benefit… Incoming flow from Premium & Billing: plan_pharmacy_benefit_config → group_benefit_offering ← billing_account → emp… Incoming flow from Premium & Billing: billing_account → employer_group ← billing_group_master … Incoming flow from Premium & Billing: billing_group_master → employer_group ← invoice_header → empl… Incoming flow from Premium & Billing: invoice_header → employer_group ← invoice_line → plan_m… Incoming flow from Premium & Billing: invoice_line → plan_master ← invoice_line → group_… Incoming flow from Premium & Billing: invoice_line → group_benefit_offering ← premium_rate_schedule… Incoming flow from Premium & Billing: premium_rate_schedule → plan_master ← premium_rate_schedule… Incoming flow from Premium & Billing: premium_rate_schedule → group_benefit_offering ← premium_calculation →… Incoming flow from Premium & Billing: premium_calculation → plan_master ← binder_payment → plan… Incoming flow from Premium & Billing: binder_payment → plan_master ← billing_member_covera… Incoming flow from Premium & Billing: billing_member_coverage → plan_master ← billing_member_covera… Incoming flow from Premium & Billing: billing_member_coverage → group_benefit_offering ← billing_plan_ref → pl… Incoming flow from Premium & Billing: billing_plan_ref → plan_master ← billing_plan_ref → gr… Incoming flow from Premium & Billing: billing_plan_ref → group_benefit_offering ← group_billing_roster … Incoming flow from Premium & Billing: group_billing_roster → employer_group ← self_bill_reconciliat… Incoming flow from Premium & Billing: self_bill_reconciliation → employer_group ← premium_holiday → emp… Incoming flow from Premium & Billing: premium_holiday → employer_group ← broker_commission_bil… Incoming flow from Sales & Broker: broker_commission_billing → employer_group ← premium_revenue_recog… Incoming flow from Finance: premium_revenue_recognition → plan_master ← membership_count_snap… Incoming flow from Premium & Billing: membership_count_snapshot → plan_master ← gl_journal_import_int… Incoming flow from Finance: gl_journal_import_interface → employer_group ← ar_cash_receipt → emp… Incoming flow from Finance: ar_cash_receipt → employer_group ← gl_premium_revenue_re… Incoming flow from Premium & Billing: gl_premium_revenue_recognition → employer_group ← gl_premium_revenue_re… Incoming flow from Premium & Billing: gl_premium_revenue_recognition → plan_master ← gl_premium_revenue_re… Incoming flow from Premium & Billing: gl_premium_revenue_recognition → group_benefit_offering ← pmpm_actual → group_b… Incoming flow from Risk & Actuarial: pmpm_actual → group_benefit_offering ← manual_rate → group_b… Incoming flow from Premium & Billing: manual_rate → group_benefit_offering ← experience_rate → emp… Incoming flow from Premium & Billing: experience_rate → employer_group ← underwriting_case → e… Incoming flow from Sales & Broker: underwriting_case → employer_group ← loss_ratio_summary → … Incoming flow from Finance: loss_ratio_summary → group_benefit_offering ← capitation_actuarial … Incoming flow from Provider: capitation_actuarial → group_benefit_offering ← benefit_actuarial_val… Incoming flow from Premium & Billing: benefit_actuarial_value → plan_master ← benefit_actuarial_val… Incoming flow from Premium & Billing: benefit_actuarial_value → group_benefit_offering ← large_claim_pooling →… Incoming flow from Claims: large_claim_pooling → employer_group ← siu_case → member_dem… Incoming flow from Fraud: siu_case → member_demographic_flagged ← book_of_business → em… Incoming flow from Sales & Broker: book_of_business → employer_group ← book_of_business → pl… Incoming flow from Sales & Broker: book_of_business → plan_master ← opportunity → employe… Incoming flow from Sales & Broker: opportunity → employer_group ← quote → plan_master Incoming flow from Sales & Broker: quote → plan_master ← quote → group_benefit… Incoming flow from Sales & Broker: quote → group_benefit_offering ← rate_card → plan_mast… Incoming flow from Sales & Broker: rate_card → plan_master ← rate_card → group_ben… Incoming flow from Sales & Broker: rate_card → group_benefit_offering ← application → employe… Incoming flow from Sales & Broker: application → employer_group ← application → plan_ma… Incoming flow from Sales & Broker: application → plan_master ← application → group_b… Incoming flow from Sales & Broker: application → group_benefit_offering ← commission_line → pla… Incoming flow from Sales & Broker: commission_line → plan_master ← renewal → employer_gr… Incoming flow from Premium & Billing: renewal → employer_group ← renewal → plan_master Incoming flow from Premium & Billing: renewal → plan_master ← appeal_case → member_… Incoming flow from Appeals: appeal_case → member_demographic ← appeal_case → plan_ma… Incoming flow from Appeals: appeal_case → plan_master ← appeal_case → group_b… Incoming flow from Appeals: appeal_case → group_benefit_offering ← grievance_case → memb… Incoming flow from Appeals: grievance_case → member_demographic ← grievance_case → plan… Incoming flow from Appeals: grievance_case → plan_master ← complaint_intake → me… Incoming flow from Appeals: complaint_intake → member_demographic ← member_representative… Incoming flow from Appeals: member_representative → member_demographic ← state_complaint_refer… Incoming flow from Appeals: state_complaint_referral → member_demographic ← dim_group → employer_… Incoming flow from Sales & Broker: dim_group → employer_group ← fact_claim → dim_memb… Incoming flow from Claims: fact_claim → dim_member ← fact_claim → plan_mas… Incoming flow from Claims: fact_claim → plan_master ← fact_premium → dim_me… Incoming flow from Premium & Billing: fact_premium → dim_member ← fact_premium → dim_pl… Incoming flow from Premium & Billing: fact_premium → dim_plan ← fact_premium → employ… Incoming flow from Premium & Billing: fact_premium → employer_group ← fact_premium → plan_m… Incoming flow from Premium & Billing: fact_premium → plan_master ← fact_capitation → pla… Incoming flow from Claims: fact_capitation → plan_master ← fact_pharmacy_claim →… Incoming flow from Pharmacy: fact_pharmacy_claim → dim_member ← fact_authorization → … Incoming flow from Clinical & Care: fact_authorization → dim_member ← fact_premium_billing … Incoming flow from Premium & Billing: fact_premium_billing → employer_group ← fact_risk_score → dim… Incoming flow from Risk & Actuarial: fact_risk_score → dim_member ← fact_quality_measure … Incoming flow from Quality & STARS: fact_quality_measure → dim_member ← fact_appeal → dim_mem… Incoming flow from Appeals: fact_appeal → dim_member ← mart_claims_pmpm → di… Incoming flow from Claims: mart_claims_pmpm → dim_plan ← mart_claims_pmpm → pl… Incoming flow from Claims: mart_claims_pmpm → plan_master ← mart_stars_summary → … Incoming flow from Quality & STARS: mart_stars_summary → dim_plan ← mart_risk_adjustment … Incoming flow from Risk & Actuarial: mart_risk_adjustment → dim_plan ← mart_risk_adjustment … Incoming flow from Risk & Actuarial: mart_risk_adjustment → plan_master ← bridge_provider_netwo… Incoming flow from Provider: bridge_provider_network → plan_master ← stg_premium_raw → emp… Incoming flow from Premium & Billing: stg_premium_raw → employer_group ← fact_care_gap → dim_m… Incoming flow from Clinical & Care: fact_care_gap → dim_member ← fact_care_management … Incoming flow from Clinical & Care: fact_care_management → dim_member ← fact_admission → dim_… Incoming flow from Clinical & Care: fact_admission → dim_member ← fact_er_visit → dim_m… Incoming flow from Clinical & Care: fact_er_visit → dim_member ← fact_grievance → dim_… Incoming flow from Appeals: fact_grievance → dim_member ← fact_sales_pipeline →… Incoming flow from Sales & Broker: fact_sales_pipeline → employer_group ← mart_utilization_summ… Incoming flow from Claims: mart_utilization_summary → dim_plan ← mart_utilization_summ… Incoming flow from Claims: mart_utilization_summary → plan_master ← measure_eligible_popu… Incoming flow from Quality & STARS: measure_eligible_population → plan_master ← measure_eligible_popu… Incoming flow from Quality & STARS: measure_eligible_population → group_benefit_offering ← measure_rate_result →… Incoming flow from Quality & STARS: measure_rate_result → plan_master ← measure_star_assignme… Incoming flow from Quality & STARS: measure_star_assignment → plan_master eligibility_verificat… → Outgoing flow to Provider: eligibility_verification_log → provider_master accumulator_transacti… → Outgoing flow to Claims: accumulator_transaction → claim_header enrollment_applicatio… → Outgoing flow to Sales & Broker: enrollment_application → broker_master pcp_assignment → prov… → Outgoing flow to Provider: pcp_assignment → provider_master capitation_eligibilit… → Outgoing flow to Provider: capitation_eligibility_roster → provider_master member_pharmacy_eligi… → Outgoing flow to Pharmacy: member_pharmacy_eligibility → formulary_master sold_membership_snaps… → Outgoing flow to Sales & Broker: sold_membership_snapshot → broker_master member_crm_profile → … → Outgoing flow to Sales & Broker: member_crm_profile → broker_master retention_case → brok… → Outgoing flow to Sales & Broker: retention_case → broker_master group_account → broke… → Outgoing flow to Sales & Broker: group_account → broker_master Source Tables Metrics Reports & dashboards Claims — Source system: — bridge_claim_diagnosis 1,100,000,000 rows bridge_claim_diagnosis — 1,100,000,000 rows. Click to open its fields. fact_claim_line 920,000,000 rows fact_claim_line — 920,000,000 rows. Click to open its fields. fact_eob_remittance 680,000,000 rows fact_eob_remittance — 680,000,000 rows. Click to open its fields. claim_status_history 380,000,000 rows claim_status_history — 380,000,000 rows. Click to open its fields. stg_claims_raw 380,000,000 rows stg_claims_raw — 380,000,000 rows. Click to open its fields. fact_claim 340,000,000 rows fact_claim — 340,000,000 rows. Click to open its fields. adjudication_message 310,000,000 rows adjudication_message — 310,000,000 rows. Click to open its fields. adjudication_result 225,000,000 rows adjudication_result — 225,000,000 rows. Click to open its fields. pricing_audit 225,000,000 rows pricing_audit — 225,000,000 rows. Click to open its fields. claim_line 210,000,000 rows claim_line — 210,000,000 rows. Click to open its fields. Medical Loss Rati… contested Medical Loss Ratio (MLR) (headline metric) — contested Claims PMPM contested Claims PMPM — contested Auto-Adjudication… metric Auto-Adjudication Rate — defined First-Pass Resolu… metric First-Pass Resolution Rate — defined Claims Denial Rate contested Claims Denial Rate — contested Pended Claim Aging metric Pended Claim Aging — defined Clean Claim Turna… metric Clean Claim Turnaround Time — defined Average Paid per … metric Average Paid per Claim — defined Daily Adjudication … computes Daily Adjudication Operations Dashboard Pended & Aged Claim… computes Pended & Aged Claims Inventory Monthly MLR Pack (C… 86.4%% OTD Monthly MLR Pack (Claims Basis) — 86.4%% (undefined) Net Claims Cost Rec… $2.41B% OTD Net Claims Cost Reconciliation — $2.41B% (undefined) COB Savings & Recov… computes COB Savings & Recovery Tracker Claims PMPM Trend b… $412.70% OTD Claims PMPM Trend by Product Line — $412.70% (undefined) Denial Rate & Reaso… computes Denial Rate & Reason Analysis Edit & Override Eff… computes Edit & Override Effectiveness Prompt-Pay Complian… computes Prompt-Pay Compliance Report High-Dollar Claim R… computes High-Dollar Claim Review Log ← provider_payment → re… Incoming flow from Finance: provider_payment → remittance_advice ← authorization_link → … Incoming flow from Clinical & Care: authorization_link → claim_header ← interest_payment → cl… Incoming flow from Finance: interest_payment → claim_header ← cob_recovery → claim_… Incoming flow from Finance: cob_recovery → claim_line ← subrogation_case → cl… Incoming flow from Finance: subrogation_case → claim_header ← overpayment_recovery … Incoming flow from Finance: overpayment_recovery → claim_line ← claim_geo_attribution… Incoming flow from Reporting & BI: claim_geo_attribution → claim_header ← claim_line_payment_al… Incoming flow from Finance: claim_line_payment_allocation → claim_line ← fraud_referral → clai… Incoming flow from Fraud: fraud_referral → claim_header ← appeal_link → denied_… Incoming flow from Appeals: appeal_link → denied_claim ← appeal_link → claim_l… Incoming flow from Appeals: appeal_link → claim_line ← member_cost_share_det… Incoming flow from Premium & Billing: member_cost_share_detail → claim_line ← coordination_of_care_… Incoming flow from Clinical & Care: coordination_of_care_flag → claim_header ← risk_adjustment_claim… Incoming flow from Risk & Actuarial: risk_adjustment_claim_extract → claim_diagnosis ← risk_adjustment_claim… Incoming flow from Risk & Actuarial: risk_adjustment_claim_extract → claim_header ← encounter_submission … Incoming flow from Reporting & BI: encounter_submission → claim_header ← broker_attribution → … Incoming flow from Sales & Broker: broker_attribution → claim_header ← stars_gap_claim_flag … Incoming flow from Quality & STARS: stars_gap_claim_flag → claim_header ← gl_posting → claim_he… Incoming flow from Finance: gl_posting → claim_header ← claim_payment_summary… Incoming flow from Reporting & BI: claim_payment_summary → claim_header ← accumulator_transacti… Incoming flow from Membership: accumulator_transaction → claim_header ← pharmacy_claim_line →… Incoming flow from Pharmacy: pharmacy_claim_line → claim_line ← pricing_adjudication_… Incoming flow from Pharmacy: pricing_adjudication_result → claim_line ← rebate_accrual → clai… Incoming flow from Finance: rebate_accrual → claim_line ← ap_claims_disbursemen… Incoming flow from Finance: ap_claims_disbursement_feed → claim_header ← rule_hit → flagged_cl… Incoming flow from Fraud: rule_hit → flagged_claim ← rule_hit → claim_head… Incoming flow from Fraud: rule_hit → claim_header ← rule_hit → claim_line Incoming flow from Fraud: rule_hit → claim_line ← medical_record_reques… Incoming flow from Fraud: medical_record_request → claim_header ← overpayment → claim_h… Incoming flow from Finance: overpayment → claim_header ← recovery_transaction … Incoming flow from Finance: recovery_transaction → claim_header ← appeal_claim_link → c… Incoming flow from Appeals: appeal_claim_link → claim_header ← appeal_claim_link → c… Incoming flow from Appeals: appeal_claim_link → claim_line ← fact_appeal → claim_h… Incoming flow from Appeals: fact_appeal → claim_header ← measure_numerator → c… Incoming flow from Quality & STARS: measure_numerator → claim_header claim_header → member… → Outgoing flow to Membership: claim_header → member_eligibility claim_header → provid… → Outgoing flow to Provider: claim_header → provider_master claim_header → member… → Outgoing flow to Membership: claim_header → member_master claim_header → plan_m… → Outgoing flow to Membership: claim_header → plan_master claim_header → group_… → Outgoing flow to Membership: claim_header → group_benefit_offering claim_header → provid… → Outgoing flow to Provider: claim_header → provider_master claim_header → facili… → Outgoing flow to Provider: claim_header → facility_master adjudication_result →… → Outgoing flow to Membership: adjudication_result → group_benefit_offering cob_detail → member_m… → Outgoing flow to Membership: cob_detail → member_master other_insurance_cover… → Outgoing flow to Membership: other_insurance_coverage → member_eligibility other_insurance_cover… → Outgoing flow to Membership: other_insurance_coverage → member_master eob → member_master → Outgoing flow to Membership: eob → member_master remittance_advice → p… → Outgoing flow to Provider: remittance_advice → provider_master remittance_advice → p… → Outgoing flow to Provider: remittance_advice → provider_master queue_assignment → ex… → Outgoing flow to Workforce: queue_assignment → examiner auth_claim_link → aut… → Outgoing flow to Clinical & Care: auth_claim_link → auth_request cob_pharmacy_detail →… → Outgoing flow to Pharmacy: cob_pharmacy_detail → pharmacy_claim_header claim_lag_snapshot → … → Outgoing flow to Risk & Actuarial: claim_lag_snapshot → reserve_run_header high_cost_claimant → … → Outgoing flow to Clinical & Care: high_cost_claimant → case large_claim_pooling →… → Outgoing flow to Membership: large_claim_pooling → employer_group flagged_claim → provi… → Outgoing flow to Provider: flagged_claim → provider_master flagged_claim → provi… → Outgoing flow to Provider: flagged_claim → provider_master flagged_claim → facil… → Outgoing flow to Provider: flagged_claim → facility_master prepay_edit → provide… → Outgoing flow to Provider: prepay_edit → provider_flag fact_claim → dim_memb… → Outgoing flow to Membership: fact_claim → dim_member fact_claim → dim_prov… → Outgoing flow to Provider: fact_claim → dim_provider fact_claim → dim_date → Outgoing flow to Reporting & BI: fact_claim → dim_date fact_claim → plan_mas… → Outgoing flow to Membership: fact_claim → plan_master fact_capitation → dim… → Outgoing flow to Provider: fact_capitation → dim_provider fact_capitation → pla… → Outgoing flow to Membership: fact_capitation → plan_master mart_claims_pmpm → di… → Outgoing flow to Membership: mart_claims_pmpm → dim_plan mart_claims_pmpm → pl… → Outgoing flow to Membership: mart_claims_pmpm → plan_master mart_utilization_summ… → Outgoing flow to Membership: mart_utilization_summary → dim_plan mart_utilization_summ… → Outgoing flow to Membership: mart_utilization_summary → plan_master claim_measure_event →… → Outgoing flow to Provider: claim_measure_event → provider_master claim_measure_event →… → Outgoing flow to Quality & STARS: claim_measure_event → measure_master Source Tables Metrics Reports & dashboards Premium & Billing — Source system: — member_cost_share_detail 195,000,000 rows member_cost_share_detail — 195,000,000 rows. Click to open its fields. stg_premium_raw 90,000,000 rows stg_premium_raw — 90,000,000 rows. Click to open its fields. fact_premium 86,000,000 rows fact_premium — 86,000,000 rows. Click to open its fields. invoice_line 62,000,000 rows invoice_line — 62,000,000 rows. Click to open its fields. premium_calculation 58,000,000 rows premium_calculation — 58,000,000 rows. Click to open its fields. fact_premium_billing 42,000,000 rows fact_premium_billing — 42,000,000 rows. Click to open its fields. cash_application 34,000,000 rows cash_application — 34,000,000 rows. Click to open its fields. billing_member_coverage 24,000,000 rows billing_member_coverage — 24,000,000 rows. Click to open its fields. group_billing_roster 14,000,000 rows group_billing_roster — 14,000,000 rows. Click to open its fields. lockbox_transaction 11,000,000 rows lockbox_transaction — 11,000,000 rows. Click to open its fields. Total Premium Rev… contested Total Premium Revenue — contested Premium Collectio… metric Premium Collection Rate — defined Binder Payment Ef… metric Binder Payment Effectuation Rate — defined Delinquency Rate metric Delinquency Rate — defined Unapplied Cash metric Unapplied Cash — defined Premium Billing Rec… $2.94B% OTD Premium Billing Reconciliation — $2.94B% (undefined) Provider Remittance… computes Provider Remittance Setup Audit Monthly Premium Bil… $3.94B annual% OTD Monthly Premium Billed vs Collected — $3.94B annual% (undefined) Active Membership b… 589,400 members% OTD Active Membership by Plan & Segment — 589,400 members% (undefined) Delinquency & Grace… computes Delinquency & Grace Period Watchlist Binder Payment & Ef… computes Binder Payment & Effectuation Tracker Unapplied & Suspens… computes Unapplied & Suspense Cash Report Group Self-Bill Rec… computes Group Self-Bill Reconciliation Variance Medicare Premium & … computes Medicare Premium & SSA Withhold Reconciliation Daily Cash Applicat… computes Daily Cash Application Productivity ← rebate_invoice → invo… Incoming flow from Finance: rebate_invoice → invoice_header ← rebate_payment → invo… Incoming flow from Finance: rebate_payment → invoice_header ← rebate_dispute → invo… Incoming flow from Finance: rebate_dispute → invoice_header ← ar_balance → billing_… Incoming flow from Finance: ar_balance → billing_account ← ar_transaction → bill… Incoming flow from Finance: ar_transaction → billing_account ← ar_transaction → invo… Incoming flow from Finance: ar_transaction → invoice_header ← ar_aging_snapshot → b… Incoming flow from Finance: ar_aging_snapshot → billing_account ← write_off → billing_a… Incoming flow from Finance: write_off → billing_account ← write_off → invoice_h… Incoming flow from Finance: write_off → invoice_header ← cms_payment_reconcili… Incoming flow from Finance: cms_payment_reconciliation → subsidy_aptc_detail ← collections_case → bi… Incoming flow from Finance: collections_case → billing_account ← broker_commission_bil… Incoming flow from Sales & Broker: broker_commission_billing → billing_group_master ← premium_revenue_recog… Incoming flow from Finance: premium_revenue_recognition → billing_plan_ref ← ap_invoice_header → i… Incoming flow from Finance: ap_invoice_header → invoice_header ← ap_invoice_line → inv… Incoming flow from Finance: ap_invoice_line → invoice_header ← ap_payment_applicatio… Incoming flow from Finance: ap_payment_application → invoice_header ← ap_hold → invoice_hea… Incoming flow from Finance: ap_hold → invoice_header ← commission_line → pre… Incoming flow from Sales & Broker: commission_line → premium_receipt member_cost_share_det… → Outgoing flow to Claims: member_cost_share_detail → claim_line member_cost_share_det… → Outgoing flow to Membership: member_cost_share_detail → member_master premium_rate_table → … → Outgoing flow to Membership: premium_rate_table → benefit_plan premium_rate_table → … → Outgoing flow to Membership: premium_rate_table → group_benefit_offering member_premium → memb… → Outgoing flow to Membership: member_premium → member_master member_premium → bene… → Outgoing flow to Membership: member_premium → benefit_plan member_premium → grou… → Outgoing flow to Membership: member_premium → group_benefit_offering aptc_subsidy → member… → Outgoing flow to Membership: aptc_subsidy → member_master aptc_subsidy → benefi… → Outgoing flow to Membership: aptc_subsidy → benefit_plan aptc_subsidy → group_… → Outgoing flow to Membership: aptc_subsidy → group_benefit_offering provider_remittance_c… → Outgoing flow to Provider: provider_remittance_config → provider_master pharmacy_benefit_accu… → Outgoing flow to Membership: pharmacy_benefit_accumulator → member_pharmacy_eligibility pharmacy_benefit_accu… → Outgoing flow to Membership: pharmacy_benefit_accumulator → group_benefit_offering plan_pharmacy_benefit… → Outgoing flow to Pharmacy: plan_pharmacy_benefit_config → formulary_master plan_pharmacy_benefit… → Outgoing flow to Provider: plan_pharmacy_benefit_config → pharmacy_network plan_pharmacy_benefit… → Outgoing flow to Membership: plan_pharmacy_benefit_config → plan_master plan_pharmacy_benefit… → Outgoing flow to Membership: plan_pharmacy_benefit_config → group_benefit_offering billing_account → emp… → Outgoing flow to Membership: billing_account → employer_group billing_account → gl_… → Outgoing flow to Finance: billing_account → gl_cost_center_master billing_group_master … → Outgoing flow to Membership: billing_group_master → employer_group billing_group_master … → Outgoing flow to Sales & Broker: billing_group_master → broker_master invoice_header → empl… → Outgoing flow to Membership: invoice_header → employer_group invoice_line → plan_m… → Outgoing flow to Membership: invoice_line → plan_master invoice_line → group_… → Outgoing flow to Membership: invoice_line → group_benefit_offering premium_rate_schedule… → Outgoing flow to Membership: premium_rate_schedule → plan_master premium_rate_schedule… → Outgoing flow to Membership: premium_rate_schedule → group_benefit_offering premium_calculation →… → Outgoing flow to Membership: premium_calculation → plan_master binder_payment → plan… → Outgoing flow to Membership: binder_payment → plan_master billing_member_covera… → Outgoing flow to Membership: billing_member_coverage → plan_master billing_member_covera… → Outgoing flow to Membership: billing_member_coverage → group_benefit_offering billing_plan_ref → pl… → Outgoing flow to Membership: billing_plan_ref → plan_master billing_plan_ref → gr… → Outgoing flow to Membership: billing_plan_ref → group_benefit_offering group_billing_roster … → Outgoing flow to Membership: group_billing_roster → employer_group self_bill_reconciliat… → Outgoing flow to Membership: self_bill_reconciliation → employer_group refund_transaction → … → Outgoing flow to Finance: refund_transaction → gl_chart_of_accounts premium_holiday → emp… → Outgoing flow to Membership: premium_holiday → employer_group membership_count_snap… → Outgoing flow to Membership: membership_count_snapshot → plan_master gl_premium_revenue_re… → Outgoing flow to Membership: gl_premium_revenue_recognition → employer_group gl_premium_revenue_re… → Outgoing flow to Membership: gl_premium_revenue_recognition → plan_master gl_premium_revenue_re… → Outgoing flow to Membership: gl_premium_revenue_recognition → group_benefit_offering gl_premium_revenue_re… → Outgoing flow to Finance: gl_premium_revenue_recognition → gl_chart_of_accounts manual_rate → group_b… → Outgoing flow to Membership: manual_rate → group_benefit_offering experience_rate → emp… → Outgoing flow to Membership: experience_rate → employer_group benefit_actuarial_val… → Outgoing flow to Membership: benefit_actuarial_value → plan_master benefit_actuarial_val… → Outgoing flow to Membership: benefit_actuarial_value → group_benefit_offering renewal → employer_gr… → Outgoing flow to Membership: renewal → employer_group renewal → plan_master → Outgoing flow to Membership: renewal → plan_master fact_premium → dim_me… → Outgoing flow to Membership: fact_premium → dim_member fact_premium → dim_pl… → Outgoing flow to Membership: fact_premium → dim_plan fact_premium → dim_da… → Outgoing flow to Reporting & BI: fact_premium → dim_date fact_premium → employ… → Outgoing flow to Membership: fact_premium → employer_group fact_premium → plan_m… → Outgoing flow to Membership: fact_premium → plan_master fact_premium_billing … → Outgoing flow to Sales & Broker: fact_premium_billing → dim_group fact_premium_billing … → Outgoing flow to Membership: fact_premium_billing → employer_group mart_revenue_monthly … → Outgoing flow to Reporting & BI: mart_revenue_monthly → dim_line_of_business stg_premium_raw → emp… → Outgoing flow to Membership: stg_premium_raw → employer_group Source Tables Metrics Reports & dashboards Risk & Actuarial — Source system: — risk_adjustment_claim_extract 110,000,000 rows risk_adjustment_claim_extract — 110,000,000 rows. Click to open its fields. fact_risk_score 7,200,000 rows fact_risk_score — 7,200,000 rows. Click to open its fields. hcc_capture 3,100,000 rows hcc_capture — 3,100,000 rows. Click to open its fields. risk_score 1,240,000 rows risk_score — 1,240,000 rows. Click to open its fields. raf_reconciliation 960,000 rows raf_reconciliation — 960,000 rows. Click to open its fields. chart_review_finding 680,000 rows chart_review_finding — 680,000 rows. Click to open its fields. readmission_risk_score 640,000 rows readmission_risk_score — 640,000 rows. Click to open its fields. claim_lag_triangle 540,000 rows claim_lag_triangle — 540,000 rows. Click to open its fields. reserve_run_detail 520,000 rows reserve_run_detail — 520,000 rows. Click to open its fields. raf_gap_opportunity 420,000 rows raf_gap_opportunity — 420,000 rows. Click to open its fields. Risk Adjustment F… contested Risk Adjustment Factor (RAF) — contested IBNR Reserve Bala… metric IBNR Reserve Balance — defined Risk-Based Capita… metric Risk-Based Capital (RBC) Ratio — defined IBNR Reserve Bala… metric IBNR Reserve Balance — canonical Completion Factor metric Completion Factor — defined Reserve Redundanc… metric Reserve Redundancy/Deficiency — defined Loss Ratio Trend metric Loss Ratio Trend — defined IBNR & Claim Lag Tr… 38.6% OTD IBNR & Claim Lag Triangle — 38.6% (undefined) IBNR Reserve Roll-F… computes IBNR Reserve Roll-Forward RBC & Solvency Trend computes RBC & Solvency Trend Monthly IBNR Reserv… computes Monthly IBNR Reserve Certification Package Completion Factor T… computes Completion Factor Triangle Workbook RAF Yield & Gap Clo… 1.043% OTD RAF Yield & Gap Closure Report — 1.043% (undefined) CMS RAF Reconciliat… 1.043% OTD CMS RAF Reconciliation Report — 1.043% (undefined) Reserve Development… computes Reserve Development / Run-off Triangle Pricing Trend & Ass… computes Pricing Trend & Assumption Summary Reserve Variance / … computes Reserve Variance / Actual-to-Expected Analysis ← claim_lag_snapshot → … Incoming flow from Claims: claim_lag_snapshot → reserve_run_header ← loss_ratio_summary → … Incoming flow from Finance: loss_ratio_summary → reserve_run_header ← actuarial_gl_bridge →… Incoming flow from Finance: actuarial_gl_bridge → reserve_run_header ← aberrancy_score → ris… Incoming flow from Fraud: aberrancy_score → risk_score ← score_feature → risk_… Incoming flow from Fraud: score_feature → risk_score ← member_risk_segment →… Incoming flow from Clinical & Care: member_risk_segment → risk_score risk_adjustment_claim… → Outgoing flow to Claims: risk_adjustment_claim_extract → claim_diagnosis risk_adjustment_claim… → Outgoing flow to Membership: risk_adjustment_claim_extract → member_master risk_adjustment_claim… → Outgoing flow to Claims: risk_adjustment_claim_extract → claim_header provider_efficiency_m… → Outgoing flow to Provider: provider_efficiency_metric → provider_master opioid_risk_score → m… → Outgoing flow to Membership: opioid_risk_score → member_pharmacy_eligibility gl_ibnr_reserve_posti… → Outgoing flow to Finance: gl_ibnr_reserve_posting → gl_chart_of_accounts pmpm_actual → group_b… → Outgoing flow to Membership: pmpm_actual → group_benefit_offering raf_gap_opportunity →… → Outgoing flow to Provider: raf_gap_opportunity → provider_master chart_review_finding … → Outgoing flow to Provider: chart_review_finding → provider_master fact_ibnr_reserve → d… → Outgoing flow to Reporting & BI: fact_ibnr_reserve → dim_line_of_business fact_risk_score → dim… → Outgoing flow to Membership: fact_risk_score → dim_member mart_risk_adjustment … → Outgoing flow to Membership: mart_risk_adjustment → dim_plan mart_risk_adjustment … → Outgoing flow to Membership: mart_risk_adjustment → plan_master Source Tables Metrics Reports & dashboards Provider — Source system: — capitation_roster 28,000,000 rows capitation_roster — 28,000,000 rows. Click to open its fields. fee_schedule_rate 9,800,000 rows fee_schedule_rate — 9,800,000 rows. Click to open its fields. ref_fee_schedule 4,800,000 rows ref_fee_schedule — 4,800,000 rows. Click to open its fields. bridge_member_pcp 2,800,000 rows bridge_member_pcp — 2,800,000 rows. Click to open its fields. fee_schedule_line 2,400,000 rows fee_schedule_line — 2,400,000 rows. Click to open its fields. bridge_provider_network 1,200,000 rows bridge_provider_network — 1,200,000 rows. Click to open its fields. mart_provider_performance 840,000 rows mart_provider_performance — 840,000 rows. Click to open its fields. pharmacy_network_affiliation 540,000 rows pharmacy_network_affiliation — 540,000 rows. Click to open its fields. network_affiliation 520,000 rows network_affiliation — 520,000 rows. Click to open its fields. fact_credentialing 520,000 rows fact_credentialing — 520,000 rows. Click to open its fields. Active Network Pr… metric Active Network Providers — defined Credentialing Cyc… metric Credentialing Cycle Time — defined Recredentialing C… metric Recredentialing Compliance Rate — defined Provider Director… contested Provider Directory Accuracy — contested Network Adequacy … metric Network Adequacy Pass Rate — defined Average Contracte… contested Average Contracted Rate vs Medicare — contested Fee Schedule Load… metric Fee Schedule Load Completeness — defined PAR Provider Ratio metric PAR Provider Ratio — defined Network Composition… computes Network Composition Dashboard Credentialing Pipel… computes Credentialing Pipeline Tracker Recredentialing Due… computes Recredentialing Due Report Provider Directory … computes Provider Directory Accuracy Scorecard Network Adequacy Fi… computes Network Adequacy Filing (CMS) Fee Schedule Rate A… computes Fee Schedule Rate Analysis Allowed Amount Pric… $412.80% OTD Allowed Amount Pricing Audit — $412.80% (undefined) Contract Renewal Pi… computes Contract Renewal Pipeline Provider Roster Rec… computes Provider Roster Reconciliation Sanction & Exclusio… computes Sanction & Exclusion Monitoring ← claim_header → provid… Incoming flow from Claims: claim_header → provider_master ← claim_header → provid… Incoming flow from Claims: claim_header → provider_master ← claim_header → facili… Incoming flow from Claims: claim_header → facility_master ← remittance_advice → p… Incoming flow from Claims: remittance_advice → provider_master ← remittance_advice → p… Incoming flow from Claims: remittance_advice → provider_master ← provider_payment → pr… Incoming flow from Finance: provider_payment → provider_master ← provider_payment → pr… Incoming flow from Finance: provider_payment → provider_master ← capitation_payment → … Incoming flow from Finance: capitation_payment → provider_master ← capitation_payment → … Incoming flow from Finance: capitation_payment → provider_master ← overpayment_recovery … Incoming flow from Finance: overpayment_recovery → provider_master ← overpayment_recovery … Incoming flow from Finance: overpayment_recovery → provider_master ← fraud_referral → prov… Incoming flow from Fraud: fraud_referral → provider_master ← fraud_referral → prov… Incoming flow from Fraud: fraud_referral → provider_master ← eligibility_verificat… Incoming flow from Membership: eligibility_verification_log → provider_master ← pcp_assignment → prov… Incoming flow from Membership: pcp_assignment → provider_master ← capitation_eligibilit… Incoming flow from Membership: capitation_eligibility_roster → provider_master ← provider_appeal → pro… Incoming flow from Appeals: provider_appeal → provider_master ← provider_quality_scor… Incoming flow from Quality & STARS: provider_quality_score → provider_master ← provider_efficiency_m… Incoming flow from Risk & Actuarial: provider_efficiency_metric → provider_master ← provider_remittance_c… Incoming flow from Premium & Billing: provider_remittance_config → provider_master ← provider_audit_trail … Incoming flow from Reporting & BI: provider_audit_trail → provider_master ← auth_request → provid… Incoming flow from Clinical & Care: auth_request → provider_master ← auth_request → facili… Incoming flow from Clinical & Care: auth_request → facility_master ← concurrent_review → f… Incoming flow from Clinical & Care: concurrent_review → facility_master ← inpatient_stay → faci… Incoming flow from Clinical & Care: inpatient_stay → facility_master ← pharmacy_claim_header… Incoming flow from Pharmacy: pharmacy_claim_header → pharmacy_master ← mac_price_list → phar… Incoming flow from Pharmacy: mac_price_list → pharmacy_network ← pharmacy_remittance →… Incoming flow from Finance: pharmacy_remittance → pharmacy_master ← pharmacy_remittance →… Incoming flow from Finance: pharmacy_remittance → provider_master ← fraud_waste_abuse_fla… Incoming flow from Fraud: fraud_waste_abuse_flag → pharmacy_master ← plan_pharmacy_benefit… Incoming flow from Premium & Billing: plan_pharmacy_benefit_config → pharmacy_network ← ap_supplier_master → … Incoming flow from Finance: ap_supplier_master → provider_master ← ap_claims_disbursemen… Incoming flow from Finance: ap_claims_disbursement_feed → provider_master ← gl_capitation_expense… Incoming flow from Finance: gl_capitation_expense → provider_master ← raf_gap_opportunity →… Incoming flow from Risk & Actuarial: raf_gap_opportunity → provider_master ← chart_review_finding … Incoming flow from Risk & Actuarial: chart_review_finding → provider_master ← siu_case → provider_f… Incoming flow from Fraud: siu_case → provider_flag ← siu_subject → provide… Incoming flow from Fraud: siu_subject → provider_master ← siu_subject → provide… Incoming flow from Fraud: siu_subject → provider_master ← aberrancy_score → pro… Incoming flow from Fraud: aberrancy_score → provider_master ← aberrancy_score → pro… Incoming flow from Fraud: aberrancy_score → provider_master ← flagged_claim → provi… Incoming flow from Claims: flagged_claim → provider_master ← flagged_claim → provi… Incoming flow from Claims: flagged_claim → provider_master ← flagged_claim → facil… Incoming flow from Claims: flagged_claim → facility_master ← prepay_edit → provide… Incoming flow from Claims: prepay_edit → provider_flag ← watchlist → provider_… Incoming flow from Fraud: watchlist → provider_master ← appeal_provider_link … Incoming flow from Appeals: appeal_provider_link → provider_master ← appeal_provider_link … Incoming flow from Appeals: appeal_provider_link → facility_master ← fact_claim → dim_prov… Incoming flow from Claims: fact_claim → dim_provider ← fact_capitation → dim… Incoming flow from Claims: fact_capitation → dim_provider ← fact_authorization → … Incoming flow from Clinical & Care: fact_authorization → dim_provider ← fact_fraud_case → dim… Incoming flow from Fraud: fact_fraud_case → dim_provider ← fact_admission → dim_… Incoming flow from Clinical & Care: fact_admission → dim_facility ← fact_admission → faci… Incoming flow from Clinical & Care: fact_admission → facility_master ← fact_er_visit → facil… Incoming flow from Clinical & Care: fact_er_visit → facility_master ← chart_chase → provide… Incoming flow from Clinical & Care: chart_chase → provider_master ← supplemental_data_rec… Incoming flow from Quality & STARS: supplemental_data_record → provider_master ← claim_measure_event →… Incoming flow from Claims: claim_measure_event → provider_master capitation_roster → m… → Outgoing flow to Membership: capitation_roster → member_eligibility capitation_roster → m… → Outgoing flow to Membership: capitation_roster → member_master capitation_roster → p… → Outgoing flow to Membership: capitation_roster → plan_master network_affiliation →… → Outgoing flow to Membership: network_affiliation → plan_master network_plan_map → pl… → Outgoing flow to Membership: network_plan_map → plan_master network_plan_map → gr… → Outgoing flow to Membership: network_plan_map → group_benefit_offering provider_group → empl… → Outgoing flow to Membership: provider_group → employer_group provider_group_member… → Outgoing flow to Membership: provider_group_membership → employer_group provider_roster_load … → Outgoing flow to Membership: provider_roster_load → employer_group provider_outreach → e… → Outgoing flow to Workforce: provider_outreach → employee_master capitation_actuarial … → Outgoing flow to Membership: capitation_actuarial → group_benefit_offering bridge_provider_netwo… → Outgoing flow to Membership: bridge_provider_network → plan_master provider_measure_attr… → Outgoing flow to Quality & STARS: provider_measure_attribution → measure_master provider_quality_scor… → Outgoing flow to Quality & STARS: provider_quality_scorecard → measure_master provider_incentive_pa… → Outgoing flow to Finance: provider_incentive_payment → gl_chart_of_accounts provider_incentive_pa… → Outgoing flow to Finance: provider_incentive_payment → gl_cost_center_master provider_incentive_pa… → Outgoing flow to Quality & STARS: provider_incentive_payment → measure_master Source Tables Metrics Reports & dashboards Clinical & Care — Source system: — um_audit_log 58,000,000 rows um_audit_log — 58,000,000 rows. Click to open its fields. assessment_question 24,000,000 rows assessment_question — 24,000,000 rows. Click to open its fields. authorization_link 22,000,000 rows authorization_link — 22,000,000 rows. Click to open its fields. clinical_note 12,500,000 rows clinical_note — 12,500,000 rows. Click to open its fields. fact_authorization 9,800,000 rows fact_authorization — 9,800,000 rows. Click to open its fields. auth_status_history 9,700,000 rows auth_status_history — 9,700,000 rows. Click to open its fields. member_contact_log 8,900,000 rows member_contact_log — 8,900,000 rows. Click to open its fields. auth_note 7,300,000 rows auth_note — 7,300,000 rows. Click to open its fields. auth_line 6,100,000 rows auth_line — 6,100,000 rows. Click to open its fields. auth_procedure 5,200,000 rows auth_procedure — 5,200,000 rows. Click to open its fields. Authorization Tur… metric Authorization Turnaround Time (TAT) — defined Prior Auth Approv… metric Prior Auth Approval Rate — defined Pre-Service Denia… contested Pre-Service Denial Rate — contested SLA Compliance Ra… metric SLA Compliance Rate — defined Case Management E… metric Case Management Engagement Rate — defined 30-Day Readmissio… contested 30-Day Readmission Rate — contested Average Length of… metric Average Length of Stay (ALOS) — defined Care Plan Currenc… metric Care Plan Currency Rate — defined Daily UM Turnaround… computes Daily UM Turnaround & SLA Dashboard Prior Authorization… computes Prior Authorization Volume & Approval Trend Denials & Overturn … computes Denials & Overturn Analysis Concurrent Review /… computes Concurrent Review / Inpatient Census 30-Day Readmission … computes 30-Day Readmission Tracker Case Management Pro… computes Case Management Productivity Report Disease Management … computes Disease Management Program Outcomes Care Plan Complianc… computes Care Plan Compliance & Overdue Review List Regulatory UM Turna… computes Regulatory UM Turnaround Compliance (CMS/State) HRA Completion & Ou… computes HRA Completion & Outreach Status ← appeal_link → case Incoming flow from Appeals: appeal_link → case ← auth_claim_link → aut… Incoming flow from Claims: auth_claim_link → auth_request ← member_medication → c… Incoming flow from Pharmacy: member_medication → case ← medication_reconcilia… Incoming flow from Pharmacy: medication_reconciliation → case ← prior_auth_request → … Incoming flow from Pharmacy: prior_auth_request → auth_request ← prior_auth_appeal → a… Incoming flow from Appeals: prior_auth_appeal → auth_request ← prior_auth_appeal → c… Incoming flow from Appeals: prior_auth_appeal → case ← high_cost_claimant → … Incoming flow from Claims: high_cost_claimant → case ← siu_case → case Incoming flow from Fraud: siu_case → case ← siu_subject → case Incoming flow from Fraud: siu_subject → case ← case_allegation → case Incoming flow from Fraud: case_allegation → case ← case_note → case Incoming flow from Fraud: case_note → case ← case_status_history →… Incoming flow from Fraud: case_status_history → case ← case_assignment → case Incoming flow from Fraud: case_assignment → case ← investigation → case Incoming flow from Fraud: investigation → case ← medical_record_reques… Incoming flow from Fraud: medical_record_request → case ← overpayment → case Incoming flow from Finance: overpayment → case ← recovery → case Incoming flow from Finance: recovery → case ← savings_estimate → ca… Incoming flow from Finance: savings_estimate → case ← referral → case Incoming flow from Fraud: referral → case ← external_referral → c… Incoming flow from Fraud: external_referral → case ← case_evidence → case Incoming flow from Fraud: case_evidence → case ← sla_tracking → case Incoming flow from Reporting & BI: sla_tracking → case ← audit_log → case Incoming flow from Fraud: audit_log → case ← appeal_case → case Incoming flow from Appeals: appeal_case → case ← grievance_case → case Incoming flow from Appeals: grievance_case → case ← complaint_intake → ca… Incoming flow from Appeals: complaint_intake → case ← timeliness_clock → ca… Incoming flow from Appeals: timeliness_clock → case ← acknowledgement_lette… Incoming flow from Appeals: acknowledgement_letter → case ← appeal_auth_link → au… Incoming flow from Appeals: appeal_auth_link → auth_request ← case_assignment → case Incoming flow from Appeals: case_assignment → case ← case_note → case Incoming flow from Appeals: case_note → case ← case_document → case Incoming flow from Appeals: case_document → case ← cms_universe_export →… Incoming flow from Reporting & BI: cms_universe_export → case ← state_complaint_refer… Incoming flow from Appeals: state_complaint_referral → case ← appeal_audit_log → ca… Incoming flow from Appeals: appeal_audit_log → case ← category_ii_complaint… Incoming flow from Appeals: category_ii_complaint_link → case ← quality_of_care_refer… Incoming flow from Quality & STARS: quality_of_care_referral → case ← fact_fraud_case → case Incoming flow from Fraud: fact_fraud_case → case ← fact_grievance → case Incoming flow from Appeals: fact_grievance → case authorization_link → … → Outgoing flow to Claims: authorization_link → claim_header coordination_of_care_… → Outgoing flow to Claims: coordination_of_care_flag → claim_header coordination_of_care_… → Outgoing flow to Membership: coordination_of_care_flag → member_eligibility coordination_of_care_… → Outgoing flow to Membership: coordination_of_care_flag → member_master auth_request → member… → Outgoing flow to Membership: auth_request → member_master auth_request → provid… → Outgoing flow to Provider: auth_request → provider_master auth_request → facili… → Outgoing flow to Provider: auth_request → facility_master auth_diagnosis → memb… → Outgoing flow to Membership: auth_diagnosis → member_master auth_note → member_ma… → Outgoing flow to Membership: auth_note → member_master concurrent_review → f… → Outgoing flow to Provider: concurrent_review → facility_master inpatient_stay → faci… → Outgoing flow to Provider: inpatient_stay → facility_master case_assignment → emp… → Outgoing flow to Workforce: case_assignment → employee_master health_risk_assessmen… → Outgoing flow to Membership: health_risk_assessment → plan_master member_contact_log → … → Outgoing flow to Workforce: member_contact_log → employee_master um_audit_log → employ… → Outgoing flow to Workforce: um_audit_log → employee_master dur_alert → pharmacy_… → Outgoing flow to Pharmacy: dur_alert → pharmacy_claim_header dur_alert → ndc_drug_… → Outgoing flow to Pharmacy: dur_alert → ndc_drug_master specialty_drug_case →… → Outgoing flow to Membership: specialty_drug_case → member_pharmacy_eligibility specialty_drug_case →… → Outgoing flow to Pharmacy: specialty_drug_case → ndc_drug_master drug_recall_event → n… → Outgoing flow to Pharmacy: drug_recall_event → ndc_drug_master chart_review → medica… → Outgoing flow to Fraud: chart_review → medical_record_request fact_authorization → … → Outgoing flow to Membership: fact_authorization → dim_member fact_authorization → … → Outgoing flow to Provider: fact_authorization → dim_provider fact_care_gap → dim_m… → Outgoing flow to Membership: fact_care_gap → dim_member fact_care_gap → measu… → Outgoing flow to Quality & STARS: fact_care_gap → measure_master fact_care_management … → Outgoing flow to Membership: fact_care_management → dim_member fact_admission → dim_… → Outgoing flow to Membership: fact_admission → dim_member fact_admission → dim_… → Outgoing flow to Provider: fact_admission → dim_facility fact_admission → faci… → Outgoing flow to Provider: fact_admission → facility_master fact_er_visit → dim_m… → Outgoing flow to Membership: fact_er_visit → dim_member fact_er_visit → facil… → Outgoing flow to Provider: fact_er_visit → facility_master medical_record_review… → Outgoing flow to Quality & STARS: medical_record_review → measure_master chart_chase → provide… → Outgoing flow to Provider: chart_chase → provider_master chart_chase → measure… → Outgoing flow to Quality & STARS: chart_chase → measure_master gap_in_care → measure… → Outgoing flow to Quality & STARS: gap_in_care → measure_master member_risk_segment →… → Outgoing flow to Risk & Actuarial: member_risk_segment → risk_score Source Tables Metrics Reports & dashboards Workforce — Source system: — reviewer_caseload 740,000 rows reviewer_caseload — 740,000 rows. Click to open its fields. examiner_productivity 540,000 rows examiner_productivity — 540,000 rows. Click to open its fields. fact_workforce_headcount 42,000 rows fact_workforce_headcount — 42,000 rows. Click to open its fields. dim_employee 18,000 rows dim_employee — 18,000 rows. Click to open its fields. employee_master 4,200 rows employee_master — 4,200 rows. Click to open its fields. sales_employee 640 rows sales_employee — 640 rows. Click to open its fields. examiner 420 rows examiner — 420 rows. Click to open its fields. ag_analyst 210 rows ag_analyst — 210 rows. Click to open its fields. employee_demographic 0 rows employee_demographic — 0 rows. Click to open its fields. employee_contact 0 rows employee_contact — 0 rows. Click to open its fields. Total Headcount metric Total Headcount — defined Full-Time Equival… metric Full-Time Equivalent (FTE) — defined Voluntary Turnove… contested Voluntary Turnover Rate — contested Time to Fill metric Time to Fill — defined Benefits Particip… metric Benefits Participation Rate — defined Overtime Hours Ra… contested Overtime Hours Ratio — contested Average Base Sala… contested Average Base Salary — contested Offer Acceptance … metric Offer Acceptance Rate — defined Examiner Productivi… computes Examiner Productivity Scorecard Reviewer Caseload &… computes Reviewer Caseload & Capacity Planning Payroll Register computes Payroll Register Compensation Benchm… computes Compensation Benchmarking Workbook Annual Merit & Bonu… computes Annual Merit & Bonus Planning Model Recruiting Funnel &… computes Recruiting Funnel & Time-to-Fill Open Requisitions A… computes Open Requisitions Aging Benefits Enrollment… computes Benefits Enrollment Summary Open Enrollment Ele… computes Open Enrollment Election Audit Time & Attendance E… computes Time & Attendance Exceptions ← queue_assignment → ex… Incoming flow from Claims: queue_assignment → examiner ← provider_outreach → e… Incoming flow from Provider: provider_outreach → employee_master ← case_assignment → emp… Incoming flow from Clinical & Care: case_assignment → employee_master ← member_contact_log → … Incoming flow from Clinical & Care: member_contact_log → employee_master ← um_audit_log → employ… Incoming flow from Clinical & Care: um_audit_log → employee_master ← expense_report → empl… Incoming flow from Finance: expense_report → employee_master ← gl_security_assignmen… Incoming flow from Finance: gl_security_assignment → employee_master ← case_assignment → emp… Incoming flow from Fraud: case_assignment → employee_master ← investigator → employ… Incoming flow from Fraud: investigator → employee_master ← audit_log → employee_… Incoming flow from Fraud: audit_log → employee_master ← case_assignment → ag_… Incoming flow from Appeals: case_assignment → ag_analyst ← case_assignment → emp… Incoming flow from Appeals: case_assignment → employee_master ← headcount_snapshot → … Incoming flow from Reporting & BI: headcount_snapshot → employee_master ← headcount_snapshot → … Incoming flow from Reporting & BI: headcount_snapshot → department ← labor_cost_allocation… Incoming flow from Finance: labor_cost_allocation → employee_master ← labor_cost_allocation… Incoming flow from Finance: labor_cost_allocation → department sales_employee → gl_c… → Outgoing flow to Finance: sales_employee → gl_cost_center_master employee_master → gl_… → Outgoing flow to Finance: employee_master → gl_cost_center_master position → gl_cost_ce… → Outgoing flow to Finance: position → gl_cost_center_master department → gl_chart… → Outgoing flow to Finance: department → gl_chart_of_accounts department → gl_cost_… → Outgoing flow to Finance: department → gl_cost_center_master org_unit → gl_cost_ce… → Outgoing flow to Finance: org_unit → gl_cost_center_master payroll_detail → gl_c… → Outgoing flow to Finance: payroll_detail → gl_chart_of_accounts payroll_detail → gl_c… → Outgoing flow to Finance: payroll_detail → gl_cost_center_master time_entry → gl_cost_… → Outgoing flow to Finance: time_entry → gl_cost_center_master requisition → gl_cost… → Outgoing flow to Finance: requisition → gl_cost_center_master contingent_worker → g… → Outgoing flow to Finance: contingent_worker → gl_cost_center_master fact_workforce_headco… → Outgoing flow to Finance: fact_workforce_headcount → dim_cost_center fact_workforce_headco… → Outgoing flow to Finance: fact_workforce_headcount → gl_cost_center_master dim_employee → gl_cos… → Outgoing flow to Finance: dim_employee → gl_cost_center_master Source Tables Metrics Reports & dashboards Appeals — Source system: — appeal_audit_log 4,200,000 rows appeal_audit_log — 4,200,000 rows. Click to open its fields. appeal_link 3,100,000 rows appeal_link — 3,100,000 rows. Click to open its fields. case_note 1,840,000 rows case_note — 1,840,000 rows. Click to open its fields. case_document 920,000 rows case_document — 920,000 rows. Click to open its fields. fact_appeal 680,000 rows fact_appeal — 680,000 rows. Click to open its fields. complaint_intake 310,000 rows complaint_intake — 310,000 rows. Click to open its fields. case_assignment 276,000 rows case_assignment — 276,000 rows. Click to open its fields. timeliness_clock 244,000 rows timeliness_clock — 244,000 rows. Click to open its fields. fact_grievance 240,000 rows fact_grievance — 240,000 rows. Click to open its fields. acknowledgement_letter 231,000 rows acknowledgement_letter — 231,000 rows. Click to open its fields. Denial Overturn R… contested Denial Overturn Rate — contested Appeals Timelines… metric Appeals Timeliness Rate — defined Grievance Timelin… metric Grievance Timeliness Rate — defined Overturn Rate metric Overturn Rate — defined Expedited Appeal … metric Expedited Appeal Compliance Rate — defined Acknowledgement L… metric Acknowledgement Letter Timeliness — defined Appeal Overturn R… metric Appeal Overturn Rate — defined CMS Part C Reconsid… computes CMS Part C Reconsideration Universe (Table 1) CMS Part D Coverage… computes CMS Part D Coverage Determination & Appeals Universe Daily Timeliness At… computes Daily Timeliness At-Risk Worklist Overturn Root-Cause… computes Overturn Root-Cause Analysis Grievance Category … computes Grievance Category Trend Dashboard STARS Appeals Measu… computes STARS Appeals Measure Pack (D-AM, C-AM) Monthly Compliance … computes Monthly Compliance Timeliness Scorecard SLA Breach & Self-D… computes SLA Breach & Self-Disclosure Log IRO / Level 2 Outco… computes IRO / Level 2 Outcome Summary ALJ & Higher-Level … computes ALJ & Higher-Level Appeals Tracker ← cms_universe_export →… Incoming flow from Reporting & BI: cms_universe_export → appeal_case ← cms_universe_export →… Incoming flow from Reporting & BI: cms_universe_export → grievance_case ← quality_of_care_refer… Incoming flow from Quality & STARS: quality_of_care_referral → grievance_case ← quality_of_care_refer… Incoming flow from Quality & STARS: quality_of_care_referral → appeal_provider_link appeal_link → denied_… → Outgoing flow to Claims: appeal_link → denied_claim appeal_link → claim_l… → Outgoing flow to Claims: appeal_link → claim_line appeal_link → case → Outgoing flow to Clinical & Care: appeal_link → case provider_appeal → pro… → Outgoing flow to Provider: provider_appeal → provider_master prior_auth_appeal → p… → Outgoing flow to Pharmacy: prior_auth_appeal → prior_auth_request prior_auth_appeal → a… → Outgoing flow to Clinical & Care: prior_auth_appeal → auth_request prior_auth_appeal → c… → Outgoing flow to Clinical & Care: prior_auth_appeal → case appeal_case → member_… → Outgoing flow to Membership: appeal_case → member_demographic appeal_case → plan_ma… → Outgoing flow to Membership: appeal_case → plan_master appeal_case → group_b… → Outgoing flow to Membership: appeal_case → group_benefit_offering appeal_case → case → Outgoing flow to Clinical & Care: appeal_case → case grievance_case → memb… → Outgoing flow to Membership: grievance_case → member_demographic grievance_case → plan… → Outgoing flow to Membership: grievance_case → plan_master grievance_case → case → Outgoing flow to Clinical & Care: grievance_case → case complaint_intake → me… → Outgoing flow to Membership: complaint_intake → member_demographic complaint_intake → ca… → Outgoing flow to Clinical & Care: complaint_intake → case timeliness_clock → ca… → Outgoing flow to Clinical & Care: timeliness_clock → case acknowledgement_lette… → Outgoing flow to Clinical & Care: acknowledgement_letter → case appeal_claim_link → c… → Outgoing flow to Claims: appeal_claim_link → claim_header appeal_claim_link → c… → Outgoing flow to Claims: appeal_claim_link → claim_line appeal_auth_link → au… → Outgoing flow to Clinical & Care: appeal_auth_link → auth_request appeal_provider_link … → Outgoing flow to Provider: appeal_provider_link → provider_master appeal_provider_link … → Outgoing flow to Provider: appeal_provider_link → facility_master appeal_pharmacy_link … → Outgoing flow to Pharmacy: appeal_pharmacy_link → pharmacy_claim_header appeal_pharmacy_link … → Outgoing flow to Pharmacy: appeal_pharmacy_link → ndc_drug_master appeal_pharmacy_link … → Outgoing flow to Pharmacy: appeal_pharmacy_link → formulary_master case_assignment → ag_… → Outgoing flow to Workforce: case_assignment → ag_analyst case_assignment → case → Outgoing flow to Clinical & Care: case_assignment → case case_assignment → emp… → Outgoing flow to Workforce: case_assignment → employee_master case_note → case → Outgoing flow to Clinical & Care: case_note → case case_document → case → Outgoing flow to Clinical & Care: case_document → case member_representative… → Outgoing flow to Membership: member_representative → member_demographic state_complaint_refer… → Outgoing flow to Membership: state_complaint_referral → member_demographic state_complaint_refer… → Outgoing flow to Clinical & Care: state_complaint_referral → case appeal_audit_log → ca… → Outgoing flow to Clinical & Care: appeal_audit_log → case category_ii_complaint… → Outgoing flow to Clinical & Care: category_ii_complaint_link → case fact_appeal → dim_mem… → Outgoing flow to Membership: fact_appeal → dim_member fact_appeal → claim_h… → Outgoing flow to Claims: fact_appeal → claim_header fact_grievance → dim_… → Outgoing flow to Membership: fact_grievance → dim_member fact_grievance → case → Outgoing flow to Clinical & Care: fact_grievance → case Source Tables Metrics Reports & dashboards Sales & Broker — Source system: — broker_attribution 48,000,000 rows broker_attribution — 48,000,000 rows. Click to open its fields. email_engagement 42,000,000 rows email_engagement — 42,000,000 rows. Click to open its fields. campaign_member 18,000,000 rows campaign_member — 18,000,000 rows. Click to open its fields. commission_line 8,900,000 rows commission_line — 8,900,000 rows. Click to open its fields. campaign_response 4,100,000 rows campaign_response — 4,100,000 rows. Click to open its fields. document_attachment 3,200,000 rows document_attachment — 3,200,000 rows. Click to open its fields. lead_activity 2,400,000 rows lead_activity — 2,400,000 rows. Click to open its fields. broker_commission_billing 2,100,000 rows broker_commission_billing — 2,100,000 rows. Click to open its fields. application_status_history 2,100,000 rows application_status_history — 2,100,000 rows. Click to open its fields. quote_line 1,600,000 rows quote_line — 1,600,000 rows. Click to open its fields. Lead Conversion R… metric Lead Conversion Rate — defined Marketing Cost Pe… contested Marketing Cost Per Acquisition (CPA) — contested Book-of-Business … metric Book-of-Business Retention Rate — defined Broker Commission A… computes Broker Commission Accrual by Premium Underwriting Pipeli… computes Underwriting Pipeline & Decision Log Daily Sales Pipelin… computes Daily Sales Pipeline Dashboard Commission Cycle Re… computes Commission Cycle Reconciliation Campaign ROI & Attr… computes Campaign ROI & Attribution Report Lead Source Perform… computes Lead Source Performance Report Broker Commission S… computes Broker Commission Statement (broker-facing) Agency Hierarchy & … computes Agency Hierarchy & Override Report Quote-to-Close Funn… computes Quote-to-Close Funnel Broker Licensing & … computes Broker Licensing & Appointment Compliance ← enrollment_applicatio… Incoming flow from Membership: enrollment_application → broker_master ← billing_group_master … Incoming flow from Premium & Billing: billing_group_master → broker_master ← sold_membership_snaps… Incoming flow from Membership: sold_membership_snapshot → broker_master ← broker_payment → brok… Incoming flow from Finance: broker_payment → broker_master ← member_crm_profile → … Incoming flow from Membership: member_crm_profile → broker_master ← retention_case → brok… Incoming flow from Membership: retention_case → broker_master ← group_account → broke… Incoming flow from Membership: group_account → broker_master ← broker_performance_sn… Incoming flow from Reporting & BI: broker_performance_snapshot → broker_master ← fact_premium_billing … Incoming flow from Premium & Billing: fact_premium_billing → dim_group broker_attribution → … → Outgoing flow to Claims: broker_attribution → claim_header broker_attribution → … → Outgoing flow to Membership: broker_attribution → member_master broker_attribution → … → Outgoing flow to Membership: broker_attribution → employer_group broker_of_record → em… → Outgoing flow to Membership: broker_of_record → employer_group broker_of_record → me… → Outgoing flow to Membership: broker_of_record → member_master group_renewal → emplo… → Outgoing flow to Membership: group_renewal → employer_group broker_commission_bil… → Outgoing flow to Premium & Billing: broker_commission_billing → billing_group_master broker_commission_bil… → Outgoing flow to Membership: broker_commission_billing → employer_group underwriting_case → e… → Outgoing flow to Membership: underwriting_case → employer_group book_of_business → em… → Outgoing flow to Membership: book_of_business → employer_group book_of_business → pl… → Outgoing flow to Membership: book_of_business → plan_master opportunity → employe… → Outgoing flow to Membership: opportunity → employer_group quote → plan_master → Outgoing flow to Membership: quote → plan_master quote → group_benefit… → Outgoing flow to Membership: quote → group_benefit_offering rate_card → plan_mast… → Outgoing flow to Membership: rate_card → plan_master rate_card → group_ben… → Outgoing flow to Membership: rate_card → group_benefit_offering application → employe… → Outgoing flow to Membership: application → employer_group application → plan_ma… → Outgoing flow to Membership: application → plan_master application → group_b… → Outgoing flow to Membership: application → group_benefit_offering commission_line → pla… → Outgoing flow to Membership: commission_line → plan_master commission_line → pre… → Outgoing flow to Premium & Billing: commission_line → premium_receipt campaign → gl_cost_ce… → Outgoing flow to Finance: campaign → gl_cost_center_master dim_group → employer_… → Outgoing flow to Membership: dim_group → employer_group fact_sales_pipeline →… → Outgoing flow to Membership: fact_sales_pipeline → employer_group Source Tables Metrics Reports & dashboards Pharmacy — Source system: — stg_pharmacy_raw 220,000,000 rows stg_pharmacy_raw — 220,000,000 rows. Click to open its fields. fact_pharmacy_claim 210,000,000 rows fact_pharmacy_claim — 210,000,000 rows. Click to open its fields. pharmacy_claim_line 44,000,000 rows pharmacy_claim_line — 44,000,000 rows. Click to open its fields. pricing_adjudication_result 44,000,000 rows pricing_adjudication_result — 44,000,000 rows. Click to open its fields. pharmacy_claim_header 42,000,000 rows pharmacy_claim_header — 42,000,000 rows. Click to open its fields. ncpdp_reject_code 7,800,000 rows ncpdp_reject_code — 7,800,000 rows. Click to open its fields. awp_price_history 6,200,000 rows awp_price_history — 6,200,000 rows. Click to open its fields. member_medication 5,400,000 rows member_medication — 5,400,000 rows. Click to open its fields. pharmacy_crossover_claim 3,800,000 rows pharmacy_crossover_claim — 3,800,000 rows. Click to open its fields. mac_price_detail 2,800,000 rows mac_price_detail — 2,800,000 rows. Click to open its fields. Rx PMPM metric Rx PMPM — defined Generic Dispensin… metric Generic Dispensing Rate (GDR) — canonical Prior Auth Approv… metric Prior Auth Approval Rate — defined Specialty Spend S… metric Specialty Spend Share — defined MAC Effectiveness… metric MAC Effectiveness (MAC %) — defined Pharmacy Net Cost… metric Pharmacy Net Cost PMPM — defined Medication Adhere… metric Medication Adherence (PDC ≥80%) — defined Medication Reconcil… computes Medication Reconciliation Post-Discharge Rx PMPM Trend by Li… computes Rx PMPM Trend by Line of Business Pharmacy MLR Feed t… 14.8% of premium% OTD Pharmacy MLR Feed to Actuarial — 14.8% of premium% (undefined) Generic Dispensing … computes Generic Dispensing & MAC Effectiveness Prior Authorization… computes Prior Authorization Operations Dashboard Formulary Change Im… computes Formulary Change Impact Analysis Copay Accumulator &… computes Copay Accumulator & Maximizer Tracking Top Drug Spend by G… computes Top Drug Spend by GPI (Executive) Pharmacy Claims Rej… computes Pharmacy Claims Reject Analytics Pharmacy Trend & Re… computes Pharmacy Trend & Rebate Accrual Report ← prior_auth_appeal → p… Incoming flow from Appeals: prior_auth_appeal → prior_auth_request ← dur_alert → pharmacy_… Incoming flow from Clinical & Care: dur_alert → pharmacy_claim_header ← dur_alert → ndc_drug_… Incoming flow from Clinical & Care: dur_alert → ndc_drug_master ← member_pharmacy_eligi… Incoming flow from Membership: member_pharmacy_eligibility → formulary_master ← rebate_contract_term … Incoming flow from Finance: rebate_contract_term → formulary_master ← rebate_contract_term … Incoming flow from Finance: rebate_contract_term → ndc_drug_master ← rebate_accrual → phar… Incoming flow from Finance: rebate_accrual → pharmacy_claim_line ← rebate_accrual → phar… Incoming flow from Finance: rebate_accrual → pharmacy_claim_header ← rebate_accrual → ndc_… Incoming flow from Finance: rebate_accrual → ndc_drug_master ← specialty_drug_case →… Incoming flow from Clinical & Care: specialty_drug_case → ndc_drug_master ← cms_pde_record → phar… Incoming flow from Reporting & BI: cms_pde_record → pharmacy_claim_header ← cms_pde_record → ndc_… Incoming flow from Reporting & BI: cms_pde_record → ndc_drug_master ← dir_fee → pharmacy_cl… Incoming flow from Finance: dir_fee → pharmacy_claim_header ← controlled_substance_… Incoming flow from Fraud: controlled_substance_log → pharmacy_claim_header ← controlled_substance_… Incoming flow from Fraud: controlled_substance_log → ndc_drug_master ← cob_pharmacy_detail →… Incoming flow from Claims: cob_pharmacy_detail → pharmacy_claim_header ← drug_recall_event → n… Incoming flow from Clinical & Care: drug_recall_event → ndc_drug_master ← plan_pharmacy_benefit… Incoming flow from Premium & Billing: plan_pharmacy_benefit_config → formulary_master ← pharmacy_claim_audit … Incoming flow from Fraud: pharmacy_claim_audit → pharmacy_claim_header ← appeal_pharmacy_link … Incoming flow from Appeals: appeal_pharmacy_link → pharmacy_claim_header ← appeal_pharmacy_link … Incoming flow from Appeals: appeal_pharmacy_link → ndc_drug_master ← appeal_pharmacy_link … Incoming flow from Appeals: appeal_pharmacy_link → formulary_master pharmacy_crossover_cl… → Outgoing flow to Membership: pharmacy_crossover_claim → member_eligibility pharmacy_crossover_cl… → Outgoing flow to Membership: pharmacy_crossover_claim → member_master member_medication → c… → Outgoing flow to Clinical & Care: member_medication → case medication_reconcilia… → Outgoing flow to Clinical & Care: medication_reconciliation → case pharmacy_claim_header… → Outgoing flow to Provider: pharmacy_claim_header → pharmacy_master pharmacy_claim_header… → Outgoing flow to Membership: pharmacy_claim_header → member_pharmacy_eligibility pharmacy_claim_header… → Outgoing flow to Membership: pharmacy_claim_header → plan_master pharmacy_claim_line →… → Outgoing flow to Claims: pharmacy_claim_line → claim_line prior_auth_request → … → Outgoing flow to Membership: prior_auth_request → member_pharmacy_eligibility prior_auth_request → … → Outgoing flow to Clinical & Care: prior_auth_request → auth_request mac_price_list → phar… → Outgoing flow to Provider: mac_price_list → pharmacy_network pricing_adjudication_… → Outgoing flow to Claims: pricing_adjudication_result → claim_line fact_pharmacy_claim →… → Outgoing flow to Membership: fact_pharmacy_claim → dim_member pharmacy_measure_even… → Outgoing flow to Quality & STARS: pharmacy_measure_event → measure_master Source Tables Metrics Reports & dashboards Reporting & BI — Source system: — billing_audit_log 210,000,000 rows billing_audit_log — 210,000,000 rows. Click to open its fields. encounter_submission 58,000,000 rows encounter_submission — 58,000,000 rows. Click to open its fields. crm_audit_log 58,000,000 rows crm_audit_log — 58,000,000 rows. Click to open its fields. claim_geo_attribution 48,000,000 rows claim_geo_attribution — 48,000,000 rows. Click to open its fields. claim_payment_summary 48,000,000 rows claim_payment_summary — 48,000,000 rows. Click to open its fields. cms_pde_record 9,800,000 rows cms_pde_record — 9,800,000 rows. Click to open its fields. provider_audit_trail 2,900,000 rows provider_audit_trail — 2,900,000 rows. Click to open its fields. etl_load_audit 2,100,000 rows etl_load_audit — 2,100,000 rows. Click to open its fields. campaign_attribution 1,900,000 rows campaign_attribution — 1,900,000 rows. Click to open its fields. data_quality_log 890,000 rows data_quality_log — 890,000 rows. Click to open its fields. Encounter Submissio… computes Encounter Submission Compliance Executive Claims Co… 86.4%% OTD Executive Claims Cost Summary — 86.4%% (undefined) Fee Schedule Load E… computes Fee Schedule Load Exception Report CMS PDE Submission … computes CMS PDE Submission & Reject Tracking Monthly Close Finan… computes Monthly Close Financial Statements (P&L, Balance Sheet) Trial Balance & Clo… computes Trial Balance & Close Status Dashboard Suspense & Default-… computes Suspense & Default-Mapping Exposure Report Broker Production S… 619,400% OTD Broker Production Scorecard — 619,400% (undefined) Executive Sales & D… 619,400% OTD Executive Sales & Distribution Review — 619,400% (undefined) Monthly Headcount &… computes Monthly Headcount & Turnover Dashboard ← fact_claim → dim_date Incoming flow from Claims: fact_claim → dim_date ← fact_premium → dim_da… Incoming flow from Premium & Billing: fact_premium → dim_date ← fact_ibnr_reserve → d… Incoming flow from Risk & Actuarial: fact_ibnr_reserve → dim_line_of_business ← mart_mlr_summary → di… Incoming flow from Finance: mart_mlr_summary → dim_line_of_business ← mart_revenue_monthly … Incoming flow from Premium & Billing: mart_revenue_monthly → dim_line_of_business ← mart_finance_pnl → di… Incoming flow from Finance: mart_finance_pnl → dim_line_of_business claim_geo_attribution… → Outgoing flow to Claims: claim_geo_attribution → claim_header encounter_submission … → Outgoing flow to Claims: encounter_submission → claim_header claim_payment_summary… → Outgoing flow to Claims: claim_payment_summary → claim_header claim_payment_summary… → Outgoing flow to Membership: claim_payment_summary → member_master member_claim_history_… → Outgoing flow to Membership: member_claim_history_index → member_eligibility member_claim_history_… → Outgoing flow to Membership: member_claim_history_index → member_master provider_audit_trail … → Outgoing flow to Provider: provider_audit_trail → provider_master cms_pde_record → phar… → Outgoing flow to Pharmacy: cms_pde_record → pharmacy_claim_header cms_pde_record → ndc_… → Outgoing flow to Pharmacy: cms_pde_record → ndc_drug_master gl_account_mapping_xr… → Outgoing flow to Finance: gl_account_mapping_xref → gl_chart_of_accounts trial_balance_snapsho… → Outgoing flow to Finance: trial_balance_snapshot → gl_chart_of_accounts sla_tracking → siu_ca… → Outgoing flow to Fraud: sla_tracking → siu_case sla_tracking → case → Outgoing flow to Clinical & Care: sla_tracking → case broker_performance_sn… → Outgoing flow to Sales & Broker: broker_performance_snapshot → broker_master cms_universe_export →… → Outgoing flow to Appeals: cms_universe_export → appeal_case cms_universe_export →… → Outgoing flow to Appeals: cms_universe_export → grievance_case cms_universe_export →… → Outgoing flow to Clinical & Care: cms_universe_export → case headcount_snapshot → … → Outgoing flow to Workforce: headcount_snapshot → employee_master headcount_snapshot → … → Outgoing flow to Workforce: headcount_snapshot → department headcount_snapshot → … → Outgoing flow to Finance: headcount_snapshot → gl_cost_center_master idss_audit_result → m… → Outgoing flow to Quality & STARS: idss_audit_result → measure_master submission_validation… → Outgoing flow to Quality & STARS: submission_validation_error → measure_master data_quality_finding … → Outgoing flow to Quality & STARS: data_quality_finding → measure_master Source Tables Metrics Reports & dashboards Quality & STARS — Source system: — fact_quality_measure 48,000,000 rows fact_quality_measure — 48,000,000 rows. Click to open its fields. stars_gap_claim_flag 7,200,000 rows stars_gap_claim_flag — 7,200,000 rows. Click to open its fields. medication_adherence_metric 4,200,000 rows medication_adherence_metric — 4,200,000 rows. Click to open its fields. nps_survey 820,000 rows nps_survey — 820,000 rows. Click to open its fields. nps_response 190,000 rows nps_response — 190,000 rows. Click to open its fields. provider_quality_score 188,000 rows provider_quality_score — 188,000 rows. Click to open its fields. mart_stars_summary 32,000 rows mart_stars_summary — 32,000 rows. Click to open its fields. quality_of_care_referral 7,800 rows quality_of_care_referral — 7,800 rows. Click to open its fields. measure_master 0 rows measure_master — 0 rows. Click to open its fields. measure_version_history 0 rows measure_version_history — 0 rows. Click to open its fields. Star Rating contested Star Rating — contested Medication Adhere… metric Medication Adherence (PDC) — defined Net Promoter Scor… metric Net Promoter Score (NPS) — defined Mandatory Trainin… contested Mandatory Training Compliance — contested HEDIS Measure Rate metric HEDIS Measure Rate — defined CAHPS Composite S… metric CAHPS Composite Score — defined Provider Quality Sc… computes Provider Quality Scorecard STAR Adherence (PDC… computes STAR Adherence (PDC) Gap Report NPS Trend & Verbati… computes NPS Trend & Verbatim Analysis Quality-of-Care Ref… computes Quality-of-Care Referral Pipeline STARS Performance D… 4.0% OTD STARS Performance Dashboard — 4.0% (undefined) Stars Executive Das… 3.5% OTD Stars Executive Dashboard — 3.5% (undefined) HEDIS Rate Trend Wo… computes HEDIS Rate Trend Workbook Star What-If Simula… 3.5% OTD Star What-If Simulator — 3.5% (undefined) CAHPS / HOS Survey … computes CAHPS / HOS Survey Results Supplemental Data Y… computes Supplemental Data Yield Report ← fact_care_gap → measu… Incoming flow from Clinical & Care: fact_care_gap → measure_master ← medical_record_review… Incoming flow from Clinical & Care: medical_record_review → measure_master ← chart_chase → measure… Incoming flow from Clinical & Care: chart_chase → measure_master ← gap_in_care → measure… Incoming flow from Clinical & Care: gap_in_care → measure_master ← provider_measure_attr… Incoming flow from Provider: provider_measure_attribution → measure_master ← provider_quality_scor… Incoming flow from Provider: provider_quality_scorecard → measure_master ← idss_audit_result → m… Incoming flow from Reporting & BI: idss_audit_result → measure_master ← submission_validation… Incoming flow from Reporting & BI: submission_validation_error → measure_master ← provider_incentive_pa… Incoming flow from Provider: provider_incentive_payment → measure_master ← claim_measure_event →… Incoming flow from Claims: claim_measure_event → measure_master ← pharmacy_measure_even… Incoming flow from Pharmacy: pharmacy_measure_event → measure_master ← data_quality_finding … Incoming flow from Reporting & BI: data_quality_finding → measure_master stars_gap_claim_flag … → Outgoing flow to Claims: stars_gap_claim_flag → claim_header stars_gap_claim_flag … → Outgoing flow to Membership: stars_gap_claim_flag → member_eligibility stars_gap_claim_flag … → Outgoing flow to Membership: stars_gap_claim_flag → member_master provider_quality_scor… → Outgoing flow to Provider: provider_quality_score → provider_master medication_adherence_… → Outgoing flow to Membership: medication_adherence_metric → member_pharmacy_eligibility quality_of_care_refer… → Outgoing flow to Appeals: quality_of_care_referral → grievance_case quality_of_care_refer… → Outgoing flow to Appeals: quality_of_care_referral → appeal_provider_link quality_of_care_refer… → Outgoing flow to Clinical & Care: quality_of_care_referral → case fact_quality_measure … → Outgoing flow to Membership: fact_quality_measure → dim_member mart_stars_summary → … → Outgoing flow to Membership: mart_stars_summary → dim_plan measure_eligible_popu… → Outgoing flow to Membership: measure_eligible_population → plan_master measure_eligible_popu… → Outgoing flow to Membership: measure_eligible_population → group_benefit_offering measure_numerator → c… → Outgoing flow to Claims: measure_numerator → claim_header measure_rate_result →… → Outgoing flow to Membership: measure_rate_result → plan_master supplemental_data_rec… → Outgoing flow to Provider: supplemental_data_record → provider_master measure_star_assignme… → Outgoing flow to Membership: measure_star_assignment → plan_master Source Tables Metrics Reports & dashboards Fraud — Source system: — rule_hit 4,120,000 rows rule_hit — 4,120,000 rows. Click to open its fields. controlled_substance_log 4,100,000 rows controlled_substance_log — 4,100,000 rows. Click to open its fields. score_feature 2,980,000 rows score_feature — 2,980,000 rows. Click to open its fields. audit_log 2,840,000 rows audit_log — 2,840,000 rows. Click to open its fields. aberrancy_score 486,000 rows aberrancy_score — 486,000 rows. Click to open its fields. network_link_analysis 412,000 rows network_link_analysis — 412,000 rows. Click to open its fields. case_note 148,000 rows case_note — 148,000 rows. Click to open its fields. pharmacy_claim_audit 120,000 rows pharmacy_claim_audit — 120,000 rows. Click to open its fields. case_evidence 98,300 rows case_evidence — 98,300 rows. Click to open its fields. case_status_history 92,500 rows case_status_history — 92,500 rows. Click to open its fields. Provider Sanction… metric Provider Sanction Exposure — defined Total Recoveries … metric Total Recoveries (FWA) — defined Identified Overpa… metric Identified Overpayment Dollars — defined Aberrancy Hit Rate metric Aberrancy Hit Rate — defined Case Cycle Time metric Case Cycle Time — defined Fraud Recovery Ra… metric Fraud Recovery Rate — defined Opioid / Controlled… computes Opioid / Controlled Substance Surveillance FWA Investigation Q… computes FWA Investigation Queue Quarterly Pharmacy … computes Quarterly Pharmacy Audit Recovery Provider Aberrancy … computes Provider Aberrancy Top 200 Case Aging & SLA Br… computes Case Aging & SLA Breach Report Investigator Produc… computes Investigator Productivity Dashboard Hotline & Referral … computes Hotline & Referral Triage Log Law Enforcement Ref… computes Law Enforcement Referral Tracker Rule Hit & False-Po… computes Rule Hit & False-Positive Analysis Network Link Analys… computes Network Link Analysis Visualizer ← chart_review → medica… Incoming flow from Clinical & Care: chart_review → medical_record_request ← overpayment → siu_case Incoming flow from Finance: overpayment → siu_case ← savings_estimate → si… Incoming flow from Finance: savings_estimate → siu_case ← sla_tracking → siu_ca… Incoming flow from Reporting & BI: sla_tracking → siu_case fraud_referral → clai… → Outgoing flow to Claims: fraud_referral → claim_header fraud_referral → prov… → Outgoing flow to Provider: fraud_referral → provider_master fraud_referral → prov… → Outgoing flow to Provider: fraud_referral → provider_master controlled_substance_… → Outgoing flow to Pharmacy: controlled_substance_log → pharmacy_claim_header controlled_substance_… → Outgoing flow to Pharmacy: controlled_substance_log → ndc_drug_master fraud_waste_abuse_fla… → Outgoing flow to Provider: fraud_waste_abuse_flag → pharmacy_master pharmacy_claim_audit … → Outgoing flow to Pharmacy: pharmacy_claim_audit → pharmacy_claim_header siu_case → member_dem… → Outgoing flow to Membership: siu_case → member_demographic_flagged siu_case → provider_f… → Outgoing flow to Provider: siu_case → provider_flag siu_case → case → Outgoing flow to Clinical & Care: siu_case → case siu_subject → provide… → Outgoing flow to Provider: siu_subject → provider_master siu_subject → provide… → Outgoing flow to Provider: siu_subject → provider_master siu_subject → case → Outgoing flow to Clinical & Care: siu_subject → case case_allegation → case → Outgoing flow to Clinical & Care: case_allegation → case case_note → case → Outgoing flow to Clinical & Care: case_note → case case_status_history →… → Outgoing flow to Clinical & Care: case_status_history → case case_assignment → case → Outgoing flow to Clinical & Care: case_assignment → case case_assignment → emp… → Outgoing flow to Workforce: case_assignment → employee_master investigator → employ… → Outgoing flow to Workforce: investigator → employee_master aberrancy_score → pro… → Outgoing flow to Provider: aberrancy_score → provider_master aberrancy_score → pro… → Outgoing flow to Provider: aberrancy_score → provider_master aberrancy_score → ris… → Outgoing flow to Risk & Actuarial: aberrancy_score → risk_score score_feature → risk_… → Outgoing flow to Risk & Actuarial: score_feature → risk_score rule_hit → flagged_cl… → Outgoing flow to Claims: rule_hit → flagged_claim rule_hit → claim_head… → Outgoing flow to Claims: rule_hit → claim_header rule_hit → claim_line → Outgoing flow to Claims: rule_hit → claim_line investigation → case → Outgoing flow to Clinical & Care: investigation → case medical_record_reques… → Outgoing flow to Claims: medical_record_request → claim_header medical_record_reques… → Outgoing flow to Clinical & Care: medical_record_request → case referral → case → Outgoing flow to Clinical & Care: referral → case external_referral → c… → Outgoing flow to Clinical & Care: external_referral → case case_evidence → case → Outgoing flow to Clinical & Care: case_evidence → case watchlist → provider_… → Outgoing flow to Provider: watchlist → provider_master audit_log → case → Outgoing flow to Clinical & Care: audit_log → case audit_log → employee_… → Outgoing flow to Workforce: audit_log → employee_master fact_fraud_case → dim… → Outgoing flow to Provider: fact_fraud_case → dim_provider fact_fraud_case → case → Outgoing flow to Clinical & Care: fact_fraud_case → case claim_line 210,000,000 rows · · PHI / PII ⚠ cob_savings_amount null on ~38% of lines (only populated when a COB cycle ran) claim_line_id id feeds Claims Denial Rate claim_line_id [id] — feeds Claims Denial Rate claim_id id claim_id [id] line_number integer line_number [integer] revenue_code code revenue_code [code] procedure_code code procedure_code [code] units decimal units [decimal] charge_amount money charge_amount [money] allowed_amount money allowed_amount [money] paid_amount money feeds Medical Loss Ratio (MLR), Claims PMPM, Net Claims Cost, Days in Claims Payable (DCP), Average Paid per Claim, Capitation as % of Medical Spend paid_amount [money] — feeds Medical Loss Ratio (MLR), Claims PMPM, Net Claims Cost, Days in Claims Payable (DCP), Average Paid per Claim, Capitation as % of Medical Spend adjudication_result 225,000,000 rows · claim_line_id id claim_line_id [id] claim_id id claim_id [id] adjudication_id id adjudication_id [id] benefit_plan_id id benefit_plan_id [id] disposition code disposition [code] allowed_amount money allowed_amount [money] paid_amount money paid_amount [money] pricing_method code pricing_method [code] adjudicated_datetime datetime adjudicated_datetime [datetime] adjudication_message 310,000,000 rows · adjudication_id id adjudication_id [id] message_seq integer message_seq [integer] message_code code message_code [code] message_text text message_text [text] severity code severity [code] provider_payment 17,500,000 rows · · Financial payment_id id payment_id [id] remittance_id id remittance_id [id] provider_id id provider_id [id] gl_account code gl_account [code] cost_center code cost_center [code] payment_amount money feeds Provider Payment Accuracy payment_amount [money] — feeds Provider Payment Accuracy payment_date date payment_date [date] payment_status code payment_status [code] void_flag boolean void_flag [boolean] capitation_roster 28,000,000 rows · · PHI / PII ⚠ duplicate member spans across two PCPs in ~2% of months roster_id id roster_id [id] provider_id id provider_id [id] member_id id member_id [id] capitation_period code capitation_period [code] plan_id id plan_id [id] effective_date date effective_date [date] termination_date date termination_date [date] assignment_status code assignment_status [code] claim_status_history 380,000,000 rows · claim_id id claim_id [id] status_seq integer status_seq [integer] from_status code from_status [code] to_status code feeds First-Pass Resolution Rate to_status [code] — feeds First-Pass Resolution Rate status_datetime datetime status_datetime [datetime] changed_by id changed_by [id] reason_code code reason_code [code] authorization_link 22,000,000 rows · ⚠ ~14% of inpatient claims have no auth_id link though one exists in UM system claim_id id claim_id [id] auth_id id auth_id [id] auth_match_method code auth_match_method [code] auth_status code auth_status [code] units_authorized decimal units_authorized [decimal] units_used decimal can exceed authorized units_used [decimal] fee_schedule_rate 9,800,000 rows · ⚠ ~3% of rates predate current code set (terminated CPT codes) fee_schedule_id id fee_schedule_id [id] procedure_code code terminated codes procedure_code [code] modifier_code code modifier_code [code] allowed_rate money allowed_rate [money] rate_basis code rate_basis [code] effective_date date effective_date [date] pricing_audit 225,000,000 rows · claim_line_id id claim_line_id [id] pricing_audit_id id pricing_audit_id [id] pricing_source code pricing_source [code] contract_id id contract_id [id] fee_schedule_id id fee_schedule_id [id] computed_allowed money computed_allowed [money] priced_datetime datetime priced_datetime [datetime] examiner 420 rows · · PHI / PII examiner_id id examiner_id [id] employee_id id employee_id [id] examiner_name text examiner_name [text] team_code code team_code [code] skill_level code skill_level [code] active_flag boolean active_flag [boolean] examiner_productivity 540,000 rows · examiner_id id examiner_id [id] work_date date work_date [date] claims_worked integer claims_worked [integer] claims_finalized integer claims_finalized [integer] avg_handle_seconds integer avg_handle_seconds [integer] quality_score decimal quality_score [decimal] network_affiliation 520,000 rows · ⚠ duplicate active spans for same provider/network ~4% provider_id id provider_id [id] network_id code network_id [code] affiliation_status code affiliation_status [code] effective_date date effective_date [date] termination_date date duplicate active spans termination_date [date] claim_geo_attribution 48,000,000 rows · ⚠ ~8% rural/urban flag null where provider ZIP missing claim_id id claim_id [id] service_state code service_state [code] service_county_fips code service_county_fips [code] rural_urban_flag code null ~8% rural_urban_flag [code] rating_area code rating_area [code] claim_line_payment_allocation 195,000,000 rows · · Financial claim_line_id id claim_line_id [id] allocation_id id allocation_id [id] gl_account code gl_account [code] cost_center code cost_center [code] fund_id id fund_id [id] allocated_amount money allocated_amount [money] accounting_period code accounting_period [code] claim_lag_triangle 540,000 rows · · Financial service_month code service_month [code] paid_month code paid_month [code] product_line code product_line [code] paid_amount money paid_amount [money] claim_count integer claim_count [integer] appeal_link 3,100,000 rows · ⚠ ~10% of appeal_id values not present in the appeals system (cross-app orphans) claim_line_id id claim_line_id [id] appeal_id id cross-app orphans ~10% appeal_id [id] case_id id case_id [id] appeal_level code appeal_level [code] appeal_outcome code appeal_outcome [code] linked_date date linked_date [date] member_cost_share_detail 195,000,000 rows · · PHI / PII · Financial claim_line_id id claim_line_id [id] member_id id member_id [id] copay_applied money copay_applied [money] coinsurance_applied money coinsurance_applied [money] deductible_applied money deductible_applied [money] member_total_liability money member_total_liability [money] pharmacy_crossover_claim 3,800,000 rows · · PHI / PII ⚠ ~5% missing ndc pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] ndc code ~5% null ndc [code] medical_claim_id id medical_claim_id [id] crossover_reason code crossover_reason [code] crossover_date date crossover_date [date] risk_adjustment_claim_extract 110,000,000 rows · · PHI / PII · Financial ⚠ HCC mapping null on ~7% of diagnoses pending recode claim_id id claim_id [id] member_id id member_id [id] icd10_code code icd10_code [code] hcc_code code null ~7% hcc_code [code] risk_score_id id risk_score_id [id] service_year integer service_year [integer] extract_date date extract_date [date] encounter_submission 58,000,000 rows · ⚠ ~4% rejected by state and not resubmitted claim_id id claim_id [id] encounter_id id encounter_id [id] submission_type code submission_type [code] regulator code regulator [code] submission_status code rejected, not resubmitted ~4% submission_status [code] submitted_date date submitted_date [date] broker_attribution 48,000,000 rows · · PHI / PII ⚠ broker_id null on ~30% (group-direct and individual non-brokered) claim_id id claim_id [id] member_id id member_id [id] broker_id id null ~30% broker_id [id] group_id id group_id [id] attribution_date date attribution_date [date] stars_gap_claim_flag 7,200,000 rows · · PHI / PII claim_id id claim_id [id] member_id id member_id [id] measure_id code measure_id [code] gap_status code gap_status [code] numerator_hit_flag boolean numerator_hit_flag [boolean] evaluated_date date evaluated_date [date] check_register 17,500,000 rows · · Financial check_number text check_number [text] payment_id id payment_id [id] bank_account code bank_account [code] check_amount money check_amount [money] issue_date date issue_date [date] cleared_flag boolean cleared_flag [boolean] cleared_date date cleared_date [date] gl_posting 240,000,000 rows · · Financial ⚠ ~1% post to a suspense gl_account pending reclassification posting_id id posting_id [id] gl_account code suspense postings ~1% gl_account [code] cost_center code cost_center [code] fund_id id fund_id [id] claim_id id claim_id [id] debit_amount money debit_amount [money] credit_amount money credit_amount [money] accounting_period code accounting_period [code] posted_date date posted_date [date] claim_payment_summary 48,000,000 rows · · PHI / PII ⚠ recomputed nightly; cob_savings rollup excludes lines where cob ran after the snapshot claim_id id claim_id [id] member_id id member_id [id] total_billed money total_billed [money] total_allowed money total_allowed [money] total_paid money total_paid [money] total_cob_savings money excludes late-COB lines total_cob_savings [money] total_member_liability money total_member_liability [money] snapshot_date date snapshot_date [date] eligibility_verification_log 8,900,000 rows · · PHI / PII ⚠ 12% of inquiries unmatched to member_id inquiry_id id inquiry_id [id] member_id id feeds Eligibility Verification Match Rate member_id [id] — feeds Eligibility Verification Match Rate provider_npi text provider_npi [text] inquiry_datetime datetime inquiry_datetime [datetime] response_code code feeds Eligibility Verification Match Rate response_code [code] — feeds Eligibility Verification Match Rate service_type_code code service_type_code [code] trace_number text trace_number [text] channel code channel [code] accumulator_transaction 18,400,000 rows · · PHI / PII ⚠ orphaned claim_id rows from purged claim history accumulator_txn_id id accumulator_txn_id [id] member_id id member_id [id] benefit_plan_id id benefit_plan_id [id] claim_id id claim_id [id] accumulator_type code accumulator_type [code] applied_amount money applied_amount [money] transaction_date date transaction_date [date] reversal_flag boolean reversal_flag [boolean] enrollment_834_inbound 7,200,000 rows · · PHI / PII ⚠ 4% fail member_id match, routed to error queue file_id text file_id [text] record_id id record_id [id] group_id id group_id [id] member_id id member_id [id] maintenance_type_code code maintenance_type_code [code] benefit_begin_date date benefit_begin_date [date] benefit_end_date date benefit_end_date [date] received_datetime datetime received_datetime [datetime] process_status code feeds 834 Auto-Adjudication Rate process_status [code] — feeds 834 Auto-Adjudication Rate member_month_summary 21,600,000 rows · · PHI / PII ⚠ recomputed nightly; lags retro adjustments by up to 30 days member_id id member_id [id] coverage_month date coverage_month [date] benefit_plan_id id benefit_plan_id [id] group_id id group_id [id] line_of_business code line_of_business [code] is_eligible_full_month boolean is_eligible_full_month [boolean] member_month_fraction decimal feeds Member Months, Member Months Growth (YoY) member_month_fraction [decimal] — feeds Member Months, Member Months Growth (YoY) eligibility_span_id id eligibility_span_id [id] audit_trail 42,000,000 rows · · PHI / PII audit_id id audit_id [id] entity_type text entity_type [text] entity_key text entity_key [text] member_id id member_id [id] action code action [code] changed_by text changed_by [text] changed_at datetime changed_at [datetime] old_value text old_value [text] new_value text new_value [text] fee_schedule_line 2,400,000 rows · ⚠ 7% rows with rate=0 (load gaps) fee_schedule_line_id id fee_schedule_line_id [id] fee_schedule_id id fee_schedule_id [id] procedure_code code procedure_code [code] modifier code modifier [code] allowed_amount money 7% zero rate feeds Claims PMPM, Fee Schedule Load Completeness allowed_amount [money] — feeds Claims PMPM, Fee Schedule Load Completeness unit_basis code unit_basis [code] rvu_value decimal rvu_value [decimal] effective_date date effective_date [date] termination_date date termination_date [date] provider_quality_score 188,000 rows · ⚠ 8% null measure attribution quality_score_id id quality_score_id [id] provider_id id provider_id [id] measure_id id measure_id [id] measurement_period code measurement_period [code] numerator integer numerator [integer] denominator integer 8% null attribution denominator [integer] rate decimal rate [decimal] benchmark_percentile integer benchmark_percentile [integer] provider_audit_trail 2,900,000 rows · audit_id id audit_id [id] provider_id id provider_id [id] table_name text table_name [text] field_name text field_name [text] old_value text old_value [text] new_value text new_value [text] change_timestamp datetime change_timestamp [datetime] changed_by text changed_by [text] auth_line 6,100,000 rows · auth_line_id id auth_line_id [id] auth_id id auth_id [id] line_number integer line_number [integer] service_code code service_code [code] service_code_type code service_code_type [code] requested_units integer requested_units [integer] approved_units integer approved_units [integer] unit_type code unit_type [code] service_from_date date service_from_date [date] auth_procedure 5,200,000 rows · auth_procedure_id id auth_procedure_id [id] auth_id id auth_id [id] procedure_code code procedure_code [code] procedure_code_system code procedure_code_system [code] modifier_1 code modifier_1 [code] modifier_2 code modifier_2 [code] requested_quantity integer requested_quantity [integer] tooth_or_site code tooth_or_site [code] auth_status_history 9,700,000 rows · status_history_id id status_history_id [id] auth_id id auth_id [id] from_status code from_status [code] to_status code to_status [code] changed_datetime datetime changed_datetime [datetime] changed_by_employee_id id changed_by_employee_id [id] reason_code code reason_code [code] auth_note 7,300,000 rows · · PHI / PII auth_note_id id auth_note_id [id] auth_id id auth_id [id] member_id id member_id [id] note_type code note_type [code] note_text text note_text [text] authored_by_employee_id id authored_by_employee_id [id] authored_datetime datetime authored_datetime [datetime] is_phi_redacted boolean is_phi_redacted [boolean] assessment_question 24,000,000 rows · · PHI / PII assessment_question_id id assessment_question_id [id] assessment_id id assessment_id [id] member_id id member_id [id] question_code code question_code [code] question_text text question_text [text] response_value text response_value [text] response_score integer response_score [integer] answered_datetime datetime answered_datetime [datetime] clinical_note 12,500,000 rows · · PHI / PII clinical_note_id id clinical_note_id [id] case_id id case_id [id] member_id id member_id [id] note_type code note_type [code] note_text text note_text [text] encounter_datetime datetime encounter_datetime [datetime] authored_by_employee_id id authored_by_employee_id [id] signed_datetime datetime signed_datetime [datetime] addendum_flag boolean addendum_flag [boolean] member_contact_log 8,900,000 rows · · PHI / PII ⚠ phone numbers in mixed formats; 6% invalid contact_log_id id contact_log_id [id] case_id id case_id [id] member_id id member_id [id] contact_method code contact_method [code] contact_direction code contact_direction [code] contact_datetime datetime contact_datetime [datetime] outcome_code code outcome_code [code] phone_number text 6% invalid format phone_number [text] duration_minutes integer duration_minutes [integer] member_medication 5,400,000 rows · · PHI / PII ⚠ ndc null on 14% (free-text drug name only) member_medication_id id member_medication_id [id] member_id id member_id [id] case_id id case_id [id] ndc code 14% null ndc [code] drug_name text drug_name [text] dosage text dosage [text] reconciliation_source code reconciliation_source [code] start_date date start_date [date] end_date date end_date [date] reviewer_caseload 740,000 rows · caseload_id id caseload_id [id] employee_id id employee_id [id] snapshot_date date snapshot_date [date] queue_code code queue_code [code] assigned_count integer assigned_count [integer] completed_count integer completed_count [integer] avg_turnaround_hours decimal feeds Authorization Turnaround Time (TAT) avg_turnaround_hours [decimal] — feeds Authorization Turnaround Time (TAT) role code role [code] um_audit_log 58,000,000 rows · · PHI / PII audit_id id audit_id [id] employee_id id employee_id [id] member_id id member_id [id] object_type code object_type [code] object_id id object_id [id] action code action [code] event_datetime datetime event_datetime [datetime] source_ip text source_ip [text] readmission_risk_score 640,000 rows · · PHI / PII · Financial ⚠ model_version null on legacy scores (pre-2023) risk_score_id id risk_score_id [id] member_id id member_id [id] stay_id id stay_id [id] score_value decimal score_value [decimal] risk_band code risk_band [code] model_version text null on legacy model_version [text] scored_date date scored_date [date] pharmacy_claim_header 42,000,000 rows · · PHI / PII ⚠ 0.7% of reversed claims retain status 'P' (paid) instead of 'R' pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] ndc code ndc [code] pharmacy_npi code pharmacy_npi [code] prescriber_npi code prescriber_npi [code] date_of_service date date_of_service [date] date_filled date date_filled [date] claim_status code values 'P','R','D','C' plus 4 leg… claim_status [code] rx_number text rx_number [text] pharmacy_claim_line 44,000,000 rows · ⚠ orphaned pharmacy_claim_id rows from a 2024 reversal-purge job claim_line_id id feeds Generic Dispensing Rate (GDR) claim_line_id [id] — feeds Generic Dispensing Rate (GDR) pharmacy_claim_id id pharmacy_claim_id [id] ndc code ndc [code] quantity_dispensed decimal quantity_dispensed [decimal] days_supply integer 4% report days_supply=0 days_supply [integer] ingredient_cost money ingredient_cost [money] dispensing_fee money dispensing_fee [money] sales_tax money sales_tax [money] total_amount_paid money feeds Medical Loss Ratio (MLR), Net Claims Cost, Rx PMPM, Specialty Spend Share total_amount_paid [money] — feeds Medical Loss Ratio (MLR), Net Claims Cost, Rx PMPM, Specialty Spend Share pharmacy_network_affiliation 540,000 rows · ⚠ orphaned pharmacy_npi rows for terminated pharmacies network_id id network_id [id] pharmacy_npi code pharmacy_npi [code] provider_id id provider_id [id] effective_date date effective_date [date] termination_date date termination_date [date] preferred_flag boolean preferred_flag [boolean] reimbursement_tier code reimbursement_tier [code] mac_price_detail 2,800,000 rows · ⚠ 9% of mac_unit_price entries stale (>180 days since update) mac_list_id id mac_list_id [id] gpi code gpi [code] ndc code ndc [code] mac_unit_price money mac_unit_price [money] effective_date date effective_date [date] last_updated date last_updated [date] awp_price_history 6,200,000 rows · ⚠ AWP rollover gaps for 1.4% of NDCs ndc code ndc [code] awp_unit_price money awp_unit_price [money] wac_unit_price money wac_unit_price [money] effective_date date effective_date [date] price_source text price_source [text] pricing_adjudication_result 44,000,000 rows · claim_line_id id claim_line_id [id] pharmacy_claim_id id pharmacy_claim_id [id] pricing_basis_used code feeds MAC Effectiveness (MAC %) pricing_basis_used [code] — feeds MAC Effectiveness (MAC %) mac_list_id id mac_list_id [id] calculated_allowed money calculated_allowed [money] u_and_c_submitted money u_and_c_submitted [money] lower_of_logic_applied code lower_of_logic_applied [code] adjudication_datetime datetime adjudication_datetime [datetime] ncpdp_reject_code 7,800,000 rows · reject_event_id id reject_event_id [id] pharmacy_claim_id id pharmacy_claim_id [id] ncpdp_reject_code code ncpdp_reject_code [code] reject_description text reject_description [text] reject_datetime datetime reject_datetime [datetime] rebate_accrual 21,000,000 rows · · Financial ⚠ accrual_amount null for compound claims (~2%) accrual_id id accrual_id [id] pharmacy_claim_id id pharmacy_claim_id [id] claim_line_id id claim_line_id [id] ndc code ndc [code] rebate_contract_id id rebate_contract_id [id] accrual_period code accrual_period [code] accrual_amount money feeds Medical Loss Ratio (MLR), Net Claims Cost, Rx PMPM, Rebate Capture Rate accrual_amount [money] — feeds Medical Loss Ratio (MLR), Net Claims Cost, Rx PMPM, Rebate Capture Rate gl_account code gl_account [code] cost_center code cost_center [code] medication_adherence_metric 4,200,000 rows · · PHI / PII ⚠ PDC denominator zero for 0.8% of members (div-by-zero) member_id id member_id [id] measure_id code measure_id [code] gpi_class code gpi_class [code] measurement_period code measurement_period [code] pdc_numerator integer pdc_numerator [integer] pdc_denominator integer pdc_denominator [integer] pdc_score decimal feeds Medication Adherence (PDC) pdc_score [decimal] — feeds Medication Adherence (PDC) adherent_flag boolean feeds Medication Adherence (PDC) adherent_flag [boolean] — feeds Medication Adherence (PDC) cms_pde_record 9,800,000 rows · · PHI / PII ⚠ reject_code populated on 1.9% of accepted PDEs pde_id id pde_id [id] pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] ndc code ndc [code] gross_drug_cost money gross_drug_cost [money] patient_pay_amount money patient_pay_amount [money] lics_amount money lics_amount [money] cms_submission_date date cms_submission_date [date] pde_status code pde_status [code] controlled_substance_log 4,100,000 rows · · PHI / PII cs_log_id id cs_log_id [id] pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] prescriber_npi code prescriber_npi [code] ndc code ndc [code] dea_schedule code dea_schedule [code] morphine_mg_equivalent decimal morphine_mg_equivalent [decimal] days_supply integer days_supply [integer] date_of_service date date_of_service [date] pharmacy_claim_audit 120,000 rows · audit_id id audit_id [id] pharmacy_claim_id id pharmacy_claim_id [id] pharmacy_npi code pharmacy_npi [code] audit_type code audit_type [code] discrepancy_amount money discrepancy_amount [money] audit_finding code audit_finding [code] recovered_amount money recovered_amount [money] audit_date date audit_date [date] invoice_line 62,000,000 rows · · Financial invoice_line_id id invoice_line_id [id] invoice_id id invoice_id [id] member_id id ~1% orphaned member_id (termed me… member_id [id] plan_id id plan_id [id] benefit_plan_id id benefit_plan_id [id] line_type code line_type [code] coverage_month date coverage_month [date] billed_amount money feeds Total Premium Revenue, Premium Collection Rate billed_amount [money] — feeds Total Premium Revenue, Premium Collection Rate rate_amount money rate_amount [money] premium_calculation 58,000,000 rows · · Financial ⚠ recalc duplicates when rate retro-applied (~4%) premium_calc_id id premium_calc_id [id] member_id id member_id [id] plan_id id plan_id [id] coverage_month date coverage_month [date] rate_schedule_id id rate_schedule_id [id] gross_premium money gross_premium [money] aptc_amount money aptc_amount [money] net_premium money net_premium [money] calc_timestamp datetime calc_timestamp [datetime] cash_application 34,000,000 rows · · Financial cash_app_id id cash_app_id [id] premium_id id premium_id [id] invoice_id id invoice_id [id] invoice_line_id id ~7% null (applied at header only) invoice_line_id [id] applied_amount money feeds Premium Collection Rate applied_amount [money] — feeds Premium Collection Rate application_date date application_date [date] application_type code application_type [code] auto_matched_flag boolean auto_matched_flag [boolean] lockbox_transaction 11,000,000 rows · · PHI / PII · Financial ⚠ OCR mis-reads on remitter name (~8%) lockbox_txn_id id lockbox_txn_id [id] bank_batch_id text bank_batch_id [text] remitter_name text OCR variants remitter_name [text] check_number text check_number [text] deposit_date date deposit_date [date] amount money amount [money] matched_premium_id id ~14% null (unmatched) matched_premium_id [id] matched_flag boolean matched_flag [boolean] ar_balance 15,000,000 rows · · Financial ar_balance_id id ar_balance_id [id] billing_account_id id billing_account_id [id] accounting_period code accounting_period [code] opening_balance money opening_balance [money] charges money charges [money] payments money payments [money] adjustments money adjustments [money] closing_balance money feeds Days Sales Outstanding (Premium DSO) closing_balance [money] — feeds Days Sales Outstanding (Premium DSO) gl_account code gl_account [code] ar_transaction 120,000,000 rows · · Financial ar_txn_id id ar_txn_id [id] billing_account_id id billing_account_id [id] invoice_id id ~9% null (non-invoice adjustments) invoice_id [id] txn_type code txn_type [code] txn_date date txn_date [date] amount money amount [money] gl_account code ~5% unmapped gl_account [code] cost_center code cost_center [code] source_system code source_system [code] ar_aging_snapshot 18,000,000 rows · · Financial aging_snapshot_id id aging_snapshot_id [id] billing_account_id id billing_account_id [id] as_of_date date as_of_date [date] bucket_current money bucket_current [money] bucket_1_30 money bucket_1_30 [money] bucket_31_60 money bucket_31_60 [money] bucket_61_90 money bucket_61_90 [money] bucket_91_120 money feeds Premium AR Aging % > 90 days bucket_91_120 [money] — feeds Premium AR Aging % > 90 days bucket_over_120 money feeds Premium AR Aging % > 90 days bucket_over_120 [money] — feeds Premium AR Aging % > 90 days billing_member_coverage 24,000,000 rows · · Financial ⚠ diverges from enrollment system by ~1.8% of member-months (timing of retro terms) billing_coverage_id id billing_coverage_id [id] member_id id feeds Active Membership member_id [id] — feeds Active Membership billing_account_id id billing_account_id [id] plan_id id plan_id [id] benefit_plan_id id benefit_plan_id [id] coverage_tier code coverage_tier [code] billed_start_date date billed_start_date [date] billed_end_date date open-ended nulls ~20% billed_end_date [date] billing_status code billing_status [code] group_billing_roster 14,000,000 rows · · Financial ⚠ roster member_id mismatches enrollment (~4%) roster_line_id id roster_line_id [id] group_id id group_id [id] member_id id ~4% mismatch vs enrollment member_id [id] coverage_month date coverage_month [date] coverage_tier code coverage_tier [code] group_reported_premium money group_reported_premium [money] plan_calculated_premium money plan_calculated_premium [money] variance_amount money variance_amount [money] reconciled_flag boolean reconciled_flag [boolean] gl_posting 48,000,000 rows · · Financial gl_posting_id id gl_posting_id [id] ar_txn_id id ar_txn_id [id] gl_account code gl_account [code] cost_center code cost_center [code] fund_id id ~8% null fund_id [id] debit_amount money debit_amount [money] credit_amount money credit_amount [money] posting_date date posting_date [date] journal_batch_id text journal_batch_id [text] broker_commission_billing 2,100,000 rows · commission_billing_id id commission_billing_id [id] broker_id id broker_id [id] group_id id null for individual market group_id [id] member_id id member_id [id] coverage_month date coverage_month [date] premium_base money premium_base [money] commission_rate decimal commission_rate [decimal] commission_amount money commission_amount [money] billing_audit_log 210,000,000 rows · · PHI / PII audit_id id audit_id [id] entity_type code entity_type [code] entity_id id entity_id [id] action code action [code] changed_by id changed_by [id] old_value text old_value [text] new_value text new_value [text] change_timestamp datetime change_timestamp [datetime] premium_revenue_recognition 9,600,000 rows · · Financial ⚠ unearned/earned split timing differences vs GL (~3%) rev_rec_id id rev_rec_id [id] plan_id id plan_id [id] coverage_month date coverage_month [date] billed_premium money feeds Total Premium Revenue billed_premium [money] — feeds Total Premium Revenue earned_premium money earned_premium [money] unearned_premium money unearned_premium [money] gl_account code gl_account [code] recognition_date date recognition_date [date] reserve_run_detail 520,000 rows · · Financial ⚠ completion_factor null for most recent 2 incurred months by design; downstream joins drop them reserve_run_id id reserve_run_id [id] incurred_month date incurred_month [date] service_category code service_category [code] paid_to_date money paid_to_date [money] completion_factor decimal null for 2 most recent incurred m… feeds Completion Factor completion_factor [decimal] — feeds Completion Factor ultimate_incurred money ultimate_incurred [money] ibnr_amount money feeds IBNR Reserve Balance ibnr_amount [money] — feeds IBNR Reserve Balance member_months integer member_months [integer] pmpm_ultimate decimal feeds Claims PMPM pmpm_ultimate [decimal] — feeds Claims PMPM risk_score 1,240,000 rows · · PHI / PII · Financial risk_score_id id risk_score_id [id] member_id id member_id [id] model_version code model_version [code] payment_year integer payment_year [integer] raf_score decimal raf_score [decimal] demographic_score decimal demographic_score [decimal] disease_score decimal disease_score [decimal] normalized_raf decimal feeds Risk Adjustment Factor (RAF) normalized_raf [decimal] — feeds Risk Adjustment Factor (RAF) score_status code score_status [code] hcc_capture 3,100,000 rows · · PHI / PII · Financial ⚠ ~6% rows reference suspect HCCs not supported by chart (overstated capture) hcc_capture_id id hcc_capture_id [id] member_id id member_id [id] payment_year integer payment_year [integer] hcc_code code hcc_code [code] diagnosis_code code diagnosis_code [code] coefficient decimal coefficient [decimal] capture_source code capture_source [code] is_chart_confirmed boolean ~6% suspect/unconfirmed is_chart_confirmed [boolean] risk_score_id id risk_score_id [id] raf_reconciliation 960,000 rows · · PHI / PII · Financial ⚠ ~11% null on cms_returned_raf pending mid-year MMR return files reconciliation_id id reconciliation_id [id] member_id id member_id [id] payment_year integer payment_year [integer] internal_raf decimal feeds Risk Adjustment Factor (RAF) internal_raf [decimal] — feeds Risk Adjustment Factor (RAF) cms_returned_raf decimal ~11% null pending return files cms_returned_raf [decimal] raf_variance decimal raf_variance [decimal] reconciliation_status code reconciliation_status [code] raf_gap_opportunity 420,000 rows · · PHI / PII · Financial gap_id id gap_id [id] member_id id member_id [id] payment_year integer payment_year [integer] suspected_hcc code suspected_hcc [code] suspect_source code suspect_source [code] estimated_raf_lift decimal estimated_raf_lift [decimal] gap_status code gap_status [code] provider_id id provider_id [id] chart_review_finding 680,000 rows · · PHI / PII · Financial finding_id id finding_id [id] member_id id member_id [id] payment_year integer payment_year [integer] hcc_code code hcc_code [code] finding_type code finding_type [code] reviewer_id id reviewer_id [id] chart_date date chart_date [date] provider_npi id provider_npi [id] member_month_actuarial 21,600,000 rows · · PHI / PII ⚠ duplicate member spans cause ~2% double-counted member-months pre-dedup member_month_id id member_month_id [id] member_id id member_id [id] group_id id group_id [id] benefit_plan_id id benefit_plan_id [id] coverage_month date coverage_month [date] line_of_business code line_of_business [code] is_dual_eligible boolean is_dual_eligible [boolean] exposure_fraction decimal ~2% duplicate spans feeds Member Months exposure_fraction [decimal] — feeds Member Months case_note 148,000 rows · · PHI / PII ⚠ free-text PII leakage in note_text (member names typed inline) note_id id note_id [id] case_id id case_id [id] author_employee_id id author_employee_id [id] note_type code note_type [code] note_text text free-text PII leakage note_text [text] created_at datetime created_at [datetime] is_privileged boolean is_privileged [boolean] case_status_history 92,500 rows · history_id id history_id [id] case_id id case_id [id] from_status code from_status [code] to_status code to_status [code] changed_by_employee_id id changed_by_employee_id [id] changed_at datetime changed_at [datetime] reason_code code reason_code [code] aberrancy_score 486,000 rows · risk_score_id id risk_score_id [id] provider_id id provider_id [id] provider_npi text provider_npi [text] model_id id model_id [id] scoring_period text scoring_period [text] aberrancy_score decimal aberrancy_score [decimal] percentile_rank decimal percentile_rank [decimal] peer_group code peer_group [code] computed_at datetime computed_at [datetime] score_feature 2,980,000 rows · feature_id id feature_id [id] risk_score_id id risk_score_id [id] feature_name text feature_name [text] feature_value decimal feature_value [decimal] contribution_weight decimal contribution_weight [decimal] z_score decimal z_score [decimal] rule_hit 4,120,000 rows · · PHI / PII ⚠ orphaned claim_id rows after claims purge hit_id id hit_id [id] rule_id id rule_id [id] claim_id id claim_id [id] claim_line_id id claim_line_id [id] provider_id id provider_id [id] member_id id member_id [id] hit_amount money hit_amount [money] hit_date date hit_date [date] disposition code disposition [code] case_evidence 98,300 rows · · PHI / PII evidence_id id evidence_id [id] case_id id case_id [id] evidence_type code evidence_type [code] file_reference text file_reference [text] chain_of_custody text chain_of_custody [text] uploaded_by_employee_id id uploaded_by_employee_id [id] uploaded_at datetime uploaded_at [datetime] is_phi boolean is_phi [boolean] network_link_analysis 412,000 rows · · PHI / PII ⚠ directionless edges duplicated (A-B and B-A) link_id id link_id [id] entity_a_type code entity_a_type [code] entity_a_id id entity_a_id [id] entity_b_type code entity_b_type [code] entity_b_id id entity_b_id [id] link_type code link_type [code] link_strength decimal link_strength [decimal] shared_address text shared_address [text] detected_date date detected_date [date] audit_log 2,840,000 rows · · PHI / PII log_id id log_id [id] employee_id id employee_id [id] case_id id case_id [id] action code action [code] entity_accessed text entity_accessed [text] member_id id member_id [id] accessed_at datetime accessed_at [datetime] ip_address text ip_address [text] sold_membership_snapshot 7,200,000 rows · CRM ⚠ SOLD basis counts applications not yet effectuated; 4% diverge from enrollment system snapshot_id id snapshot_id [id] snapshot_month date snapshot_month [date] member_id id member_id [id] broker_id id broker_id [id] plan_id id plan_id [id] group_id id null for individual group_id [id] sold_status code includes 'pending_effectuation' w… sold_status [code] effective_date date effective_date [date] is_active_sold boolean feeds Active Membership, Member Persistency Rate is_active_sold [boolean] — feeds Active Membership, Member Persistency Rate lead_activity 2,400,000 rows · CRM · PHI / PII ⚠ activity_type free-text in legacy rows activity_id id activity_id [id] lead_id id lead_id [id] broker_id id broker_id [id] activity_type code free-text in legacy rows activity_type [code] activity_at datetime activity_at [datetime] outcome code outcome [code] notes text notes [text] quote_line 1,600,000 rows · CRM · PHI / PII quote_line_id id quote_line_id [id] quote_id id quote_id [id] relationship code relationship [code] date_of_birth date date_of_birth [date] tobacco_use boolean tobacco_use [boolean] rated_premium money rated_premium [money] rating_area code rating_area [code] application_status_history 2,100,000 rows · CRM history_id id history_id [id] application_id id application_id [id] from_status code from_status [code] to_status code to_status [code] changed_at datetime changed_at [datetime] changed_by text changed_by [text] commission_line 8,900,000 rows · CRM ⚠ orphaned member_id from terminated policies; 2% premium-basis mismatch with billing commission_line_id id commission_line_id [id] statement_id id statement_id [id] broker_id id broker_id [id] member_id id orphaned rows ~2% member_id [id] plan_id id plan_id [id] premium_id id null where reconstructed premium_id [id] earned_premium_basis money 2% mismatch vs billing feeds Total Premium Revenue earned_premium_basis [money] — feeds Total Premium Revenue commission_amount money feeds Broker Commission Expense Ratio commission_amount [money] — feeds Broker Commission Expense Ratio comp_type code comp_type [code] campaign_member 18,000,000 rows · CRM ⚠ member_id and lead_id both null in 3% of rows campaign_member_id id campaign_member_id [id] campaign_id id campaign_id [id] member_id id null where targeting a lead member_id [id] lead_id id null where targeting a member lead_id [id] send_status code send_status [code] sent_at datetime sent_at [datetime] responded boolean responded [boolean] campaign_response 4,100,000 rows · CRM ⚠ attribution duplicated across overlapping campaigns response_id id response_id [id] campaign_member_id id campaign_member_id [id] campaign_id id campaign_id [id] response_type code response_type [code] response_at datetime response_at [datetime] converted_opportunity_id id null if no conversion converted_opportunity_id [id] attributed_revenue money double-counted across campaigns feeds Marketing Cost Per Acquisition (CPA) attributed_revenue [money] — feeds Marketing Cost Per Acquisition (CPA) email_engagement 42,000,000 rows · CRM engagement_id id engagement_id [id] campaign_member_id id campaign_member_id [id] member_id id null for lead targets member_id [id] event_type code event_type [code] event_at datetime event_at [datetime] device_type code device_type [code] link_url text link_url [text] nps_survey 820,000 rows · CRM survey_id id survey_id [id] member_id id member_id [id] campaign_id id null for transactional surveys campaign_id [id] survey_type code survey_type [code] sent_at datetime sent_at [datetime] channel code channel [code] status code status [code] nps_response 190,000 rows · CRM · PHI / PII ⚠ verbatim free-text uncoded; 8% score out of 0-10 range nps_response_id id nps_response_id [id] survey_id id survey_id [id] member_id id member_id [id] nps_score integer 8% out of 0-10 range feeds Net Promoter Score (NPS) nps_score [integer] — feeds Net Promoter Score (NPS) nps_category code feeds Net Promoter Score (NPS) nps_category [code] — feeds Net Promoter Score (NPS) verbatim text uncoded free-text verbatim [text] responded_at datetime responded_at [datetime] churn_prediction 7,600,000 rows · CRM ⚠ model_version null for backfilled scores churn_score_id id churn_score_id [id] member_id id member_id [id] score_date date score_date [date] churn_probability decimal churn_probability [decimal] churn_band code feeds Book-of-Business Retention Rate churn_band [code] — feeds Book-of-Business Retention Rate top_driver code top_driver [code] model_version text null for backfilled model_version [text] sales_employee 640 rows · CRM · PHI / PII ⚠ manager_employee_id self-reference loops in 3 rows employee_id id employee_id [id] employee_name text employee_name [text] role code role [code] manager_employee_id id self-reference loops manager_employee_id [id] cost_center code cost_center [code] hire_date date hire_date [date] status code status [code] document_attachment 3,200,000 rows · CRM ⚠ orphaned attachments to deleted entities (~6%) attachment_id id attachment_id [id] entity_type code entity_type [code] entity_id id orphaned ~6% entity_id [id] file_name text file_name [text] document_type code document_type [code] uploaded_by text uploaded_by [text] uploaded_at datetime uploaded_at [datetime] crm_audit_log 58,000,000 rows · CRM audit_id id audit_id [id] entity_type code entity_type [code] entity_id id entity_id [id] action code action [code] changed_by text changed_by [text] changed_at datetime changed_at [datetime] field_changed text null for bulk operations field_changed [text] campaign_attribution 1,900,000 rows · CRM ⚠ multi-touch attribution model changed mid-year; pre/post not reconciled attribution_id id attribution_id [id] application_id id application_id [id] campaign_id id campaign_id [id] attribution_model code pre/post model not reconciled attribution_model [code] attribution_weight decimal feeds Marketing Cost Per Acquisition (CPA) attribution_weight [decimal] — feeds Marketing Cost Per Acquisition (CPA) attributed_at datetime attributed_at [datetime] complaint_intake 310,000 rows · · PHI / PII ⚠ intake_channel has 4 casing variants (Phone/phone/PHONE/Tel) intake_id id intake_id [id] member_id id member_id [id] case_id id case_id [id] intake_channel code 4 casing variants intake_channel [code] intake_datetime datetime intake_datetime [datetime] contact_name text contact_name [text] contact_phone text contact_phone [text] summary_text text summary_text [text] routed_to_queue code routed_to_queue [code] timeliness_clock 244,000 rows · ⚠ clock_pause_total_hours negative in 0.3% of rows (calc bug) clock_id id clock_id [id] appeal_id id appeal_id [id] case_id id case_id [id] clock_type code clock_type [code] regulatory_threshold_hours integer regulatory_threshold_hours [integer] clock_start_datetime datetime clock_start_datetime [datetime] clock_due_datetime datetime clock_due_datetime [datetime] clock_stop_datetime datetime clock_stop_datetime [datetime] clock_pause_total_hours decimal 0.3% negative clock_pause_total_hours [decimal] acknowledgement_letter 231,000 rows · ⚠ sent_date after due_date in 5% of rows (late ack) letter_id id letter_id [id] appeal_id id appeal_id [id] case_id id case_id [id] letter_type code letter_type [code] generated_date date generated_date [date] due_date date due_date [date] sent_date date 5% late vs due feeds Acknowledgement Letter Timeliness sent_date [date] — feeds Acknowledgement Letter Timeliness delivery_method code delivery_method [code] language_code code language_code [code] case_assignment 276,000 rows · assignment_id id assignment_id [id] appeal_id id appeal_id [id] case_id id case_id [id] employee_id id employee_id [id] queue_code code queue_code [code] assigned_datetime datetime assigned_datetime [datetime] reassigned_flag boolean reassigned_flag [boolean] sla_priority code sla_priority [code] ag_analyst 210 rows · · PHI / PII employee_id id employee_id [id] analyst_name text analyst_name [text] team_code code team_code [code] line_of_business_focus code line_of_business_focus [code] hire_date date hire_date [date] active_flag boolean active_flag [boolean] certification_level code certification_level [code] case_note 1,840,000 rows · · PHI / PII ⚠ note_text contains pasted PHI in member-correspondence notes note_id id note_id [id] appeal_id id appeal_id [id] case_id id case_id [id] note_text text note_text [text] note_type code note_type [code] authored_by id authored_by [id] authored_datetime datetime authored_datetime [datetime] internal_only_flag boolean internal_only_flag [boolean] case_document 920,000 rows · document_id id document_id [id] appeal_id id appeal_id [id] case_id id case_id [id] document_type code document_type [code] file_name text file_name [text] contains_phi_flag boolean contains_phi_flag [boolean] uploaded_datetime datetime uploaded_datetime [datetime] storage_uri text storage_uri [text] appeal_audit_log 4,200,000 rows · audit_id id audit_id [id] appeal_id id appeal_id [id] case_id id case_id [id] changed_field text changed_field [text] old_value text old_value [text] new_value text new_value [text] changed_by id changed_by [id] change_datetime datetime change_datetime [datetime] quality_of_care_referral 7,800 rows · · PHI / PII qoc_referral_id id qoc_referral_id [id] case_id id case_id [id] member_id id member_id [id] provider_id id provider_id [id] severity_level code severity_level [code] referred_to_dept code referred_to_dept [code] referral_date date referral_date [date] peer_review_required_flag boolean peer_review_required_flag [boolean] employee_master 4,200 rows · · PHI / PII ⚠ 3 casing variants on preferred_name; 8% null middle_name employee_id id feeds Benefits Participation Rate employee_id [id] — feeds Benefits Participation Rate legal_first_name text legal_first_name [text] legal_last_name text legal_last_name [text] preferred_name text 3 casing variants preferred_name [text] date_of_birth date date_of_birth [date] ssn text ssn [text] hire_date date hire_date [date] employment_status code feeds Total Headcount employment_status [code] — feeds Total Headcount position_id id position_id [id] employee_demographic 0 rows · · PHI / PII ⚠ 22% null on self_identified_ethnicity (voluntary) employee_id id employee_id [id] gender code gender [code] self_identified_ethnicity code 22% null self_identified_ethnicity [code] veteran_status code veteran_status [code] disability_status code disability_status [code] marital_status code marital_status [code] primary_language code primary_language [code] employee_contact 0 rows · · PHI / PII ⚠ orphaned employee_id rows after terminations contact_id id contact_id [id] employee_id id employee_id [id] contact_type code contact_type [code] email_address text email_address [text] phone_number text phone_number [text] is_primary boolean is_primary [boolean] fact_claim 340,000,000 rows · · PHI / PII ⚠ ~1.5% of paid_amount restated after retro-adjustments not reflected until monthly reload claim_sk id claim_sk [id] claim_id id claim_id [id] member_id id member_id [id] provider_id id provider_id [id] plan_id id plan_id [id] service_date_sk id service_date_sk [id] paid_date_sk id paid_date_sk [id] billed_amount money billed_amount [money] allowed_amount money allowed_amount [money] fact_claim_line 920,000,000 rows · ⚠ line paid_amount sums diverge from header by ~0.3% on COB claims claim_line_sk id claim_line_sk [id] claim_line_id id claim_line_id [id] claim_id id claim_id [id] member_id id member_id [id] provider_id id provider_id [id] procedure_sk id procedure_sk [id] service_date_sk id service_date_sk [id] units integer units [integer] allowed_amount money allowed_amount [money] fact_premium 86,000,000 rows · · Financial ⚠ retro membership adjustments cause ~2% of months to be double-booked then reversed premium_sk id premium_sk [id] premium_id id premium_id [id] member_id id member_id [id] group_id id group_id [id] plan_id id plan_id [id] coverage_month_sk id coverage_month_sk [id] billed_premium money billed_premium [money] collected_premium money collected_premium [money] subsidy_amount money subsidy_amount [money] fact_pharmacy_claim 210,000,000 rows · ⚠ ~4% null ndc on compounded scripts rx_sk id rx_sk [id] pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] ndc code ~4% null ndc [code] prescriber_npi text prescriber_npi [text] fill_date_sk id fill_date_sk [id] days_supply integer days_supply [integer] quantity decimal quantity [decimal] ingredient_cost money ingredient_cost [money] fact_authorization 9,800,000 rows · auth_sk id auth_sk [id] auth_id id auth_id [id] member_id id member_id [id] provider_id id provider_id [id] requested_date_sk id requested_date_sk [id] decision_date_sk id decision_date_sk [id] service_type_code code service_type_code [code] auth_status code auth_status [code] units_approved integer units_approved [integer] fact_premium_billing 42,000,000 rows · · Financial billing_sk id billing_sk [id] invoice_id id invoice_id [id] group_id id group_id [id] premium_id id premium_id [id] invoice_date_sk id invoice_date_sk [id] invoice_amount money invoice_amount [money] paid_amount money paid_amount [money] aging_bucket code aging_bucket [code] fact_risk_score 7,200,000 rows · · Financial ⚠ two model versions (V24/V28) coexist; ~15% scored on older version risk_score_sk id risk_score_sk [id] risk_score_id id risk_score_id [id] member_id id member_id [id] scoring_period_sk id scoring_period_sk [id] raf_score decimal mixed model version raf_score [decimal] hcc_count integer hcc_count [integer] model_version code model_version [code] fact_quality_measure 48,000,000 rows · quality_sk id quality_sk [id] measure_id id measure_id [id] member_id id member_id [id] measurement_year integer measurement_year [integer] numerator_flag boolean numerator_flag [boolean] denominator_flag boolean denominator_flag [boolean] exclusion_flag boolean exclusion_flag [boolean] fact_appeal 680,000 rows · appeal_sk id appeal_sk [id] appeal_id id appeal_id [id] member_id id member_id [id] claim_id id claim_id [id] received_date_sk id received_date_sk [id] resolved_date_sk id resolved_date_sk [id] appeal_type code appeal_type [code] outcome_code code outcome_code [code] mart_provider_performance 840,000 rows · perf_sk id perf_sk [id] provider_id id provider_id [id] quarter_sk id quarter_sk [id] total_paid money total_paid [money] claim_count integer claim_count [integer] attributed_members integer feeds Provider Network Adequacy % attributed_members [integer] — feeds Provider Network Adequacy % quality_score decimal quality_score [decimal] mart_stars_summary 32,000 rows · stars_sk id stars_sk [id] contract_id text contract_id [text] measure_id id measure_id [id] measurement_year integer measurement_year [integer] measure_rate decimal measure_rate [decimal] star_value decimal feeds Star Rating star_value [decimal] — feeds Star Rating weight decimal weight [decimal] bridge_provider_network 1,200,000 rows · ⚠ orphaned provider_id rows referencing termed providers provider_id id provider_id [id] network_id id network_id [id] plan_id id plan_id [id] effective_date date effective_date [date] par_status code par_status [code] bridge_claim_diagnosis 1,100,000,000 rows · · PHI / PII claim_id id claim_id [id] diagnosis_sk id diagnosis_sk [id] icd10_code code icd10_code [code] diagnosis_sequence integer diagnosis_sequence [integer] poa_flag code poa_flag [code] bridge_member_pcp 2,800,000 rows · ⚠ ~7% members attributed to two PCPs in overlapping spans member_id id member_id [id] provider_id id provider_id [id] attribution_start date attribution_start [date] attribution_end date attribution_end [date] attribution_method code attribution_method [code] stg_claims_raw 380,000,000 rows · ⚠ ~3% reject/resubmit duplicates retained stg_claim_id id stg_claim_id [id] claim_id id claim_id [id] member_id id member_id [id] provider_id id provider_id [id] load_datetime datetime load_datetime [datetime] source_system_code code source_system_code [code] raw_status text raw_status [text] stg_eligibility_raw 52,000,000 rows · ⚠ casing variants on coverage_status (5 distinct spellings) stg_elig_id id stg_elig_id [id] member_id id member_id [id] plan_id id plan_id [id] coverage_status text 5 casing variants coverage_status [text] eff_date date eff_date [date] load_datetime datetime load_datetime [datetime] stg_premium_raw 90,000,000 rows · · Financial stg_premium_id id stg_premium_id [id] premium_id id premium_id [id] member_id id member_id [id] group_id id group_id [id] booked_amount money booked_amount [money] load_datetime datetime load_datetime [datetime] stg_pharmacy_raw 220,000,000 rows · stg_rx_id id stg_rx_id [id] pharmacy_claim_id id pharmacy_claim_id [id] member_id id member_id [id] ndc code ndc [code] load_datetime datetime load_datetime [datetime] etl_load_audit 2,100,000 rows · audit_id id audit_id [id] target_table text target_table [text] batch_id id batch_id [id] load_datetime datetime load_datetime [datetime] insert_count integer insert_count [integer] update_count integer update_count [integer] reject_count integer reject_count [integer] data_quality_log 890,000 rows · ⚠ DQ rules exist but most failures are warn-only, not blocking dq_log_id id dq_log_id [id] rule_name text rule_name [text] target_table text target_table [text] eval_datetime datetime eval_datetime [datetime] fail_count integer fail_count [integer] severity code mostly warn-only severity [code] fact_eob_remittance 680,000,000 rows · eob_sk id eob_sk [id] claim_id id claim_id [id] claim_line_id id claim_line_id [id] remit_date_sk id remit_date_sk [id] patient_responsibility money patient_responsibility [money] plan_paid money plan_paid [money] adjustment_reason_code code adjustment_reason_code [code] fact_credentialing 520,000 rows · ⚠ ~10% expired credentials still marked active cred_sk id cred_sk [id] provider_id id provider_id [id] provider_npi text provider_npi [text] cred_status code ~10% stale active cred_status [code] approved_date_sk id approved_date_sk [id] recred_due_date_sk id recred_due_date_sk [id] ref_fee_schedule 4,800,000 rows · fee_schedule_id id fee_schedule_id [id] cpt_hcpcs_code code cpt_hcpcs_code [code] allowed_amount money allowed_amount [money] effective_date date effective_date [date] region text region [text] fact_workforce_headcount 42,000 rows · hc_sk id hc_sk [id] cost_center code cost_center [code] month_sk id month_sk [id] employee_id id employee_id [id] fte_count decimal fte_count [decimal] labor_cost money labor_cost [money] dim_employee 18,000 rows · · PHI / PII employee_sk id employee_sk [id] employee_id id employee_id [id] employee_name text employee_name [text] department text department [text] cost_center code cost_center [code] hire_date date hire_date [date] current_flag boolean current_flag [boolean] fact_grievance 240,000 rows · grievance_sk id grievance_sk [id] case_id id case_id [id] member_id id member_id [id] category_code code category_code [code] received_date_sk id received_date_sk [id] resolution_days integer resolution_days [integer] snapshot_membership_daily 2,100,000,000 rows · ⚠ midnight cutover causes ~0.2% over/undercount on month boundaries snap_sk id snap_sk [id] member_id id member_id [id] plan_id id plan_id [id] snapshot_date_sk id snapshot_date_sk [id] active_flag boolean boundary over/undercount active_flag [boolean] lob_code code lob_code [code] measure_master 0 rows · measure_id id measure_id [id] measure_code code measure_code [code] measure_name text measure_name [text] measure_steward text measure_steward [text] domain_category code domain_category [code] measurement_year integer measurement_year [integer] weight_part_c_d decimal weight_part_c_d [decimal] is_star_measure boolean is_star_measure [boolean] reporting_methodology code reporting_methodology [code] measure_version_history 0 rows · measure_version_id id measure_version_id [id] measure_id id measure_id [id] spec_version text spec_version [text] effective_date date effective_date [date] retired_date date retired_date [date] change_summary text change_summary [text] ncqa_release code ncqa_release [code] On-Time Delivery, traced on the same data — two answers
%
Ops, shipment grain
4,895 / 5,203
%
Finance, post-credit
4,116 / 4,695