Legacy Pipeline Atlas worker-1snapshot 2026-07-15

sub-job

PAA · Load-Dimension-Members

Loads Dimension Members

called by Daily-Processing, Daily-Processing-PAAMobile

sub-job PAA/Load-Dimension-Members · db PAA_ProdMirror · updated 2025-11-25

task tree

What the sub-job runs

Tasks in order, each with what it does — typed stepsfrom the MDClarity task docs (click to open the reference), sub-jobsyou can expand inline (▶) to reveal their own tasks, and SQL steps summarized by Gemini (✨). Every step shows the tables it writes/reads and which earlier step it feeds from(sqlglot lineage).

sub-job

Load-Dimension-Members

23 tasks · PAA
sub-job / Flow action SQL / native step▶ expand a sub-job inline · ↗ open it as its own page · a typed step opens its doc
  1. Load Insurance Categorysqlload_dimension_members__load_insurance_category.sql
    ✨ Loads unique insurance categories into the dimension member table to organize payers by their broader classification.
    writes DimensionMember · reads SourceInsuranceMap
  2. Load Insurancesqlload_dimension_members__load_insurance.sql
    ✨ Loads insurance payers into the dimension member table and maps them to their respective insurance categories.
    writes DimensionMember · reads InsuranceSourceInsuranceMap
  3. Load EligibilityInsurancesqlload_dimension_members__load_eligibilityinsurance.sql
    ✨ Updates the eligibility insurance dimension by filtering insurance records for those containing valid electronic benefit API references.
    writes DimensionMember · reads Insurance
  4. Load QuoteInsurancesqlload_dimension_members__load_quoteinsurance.sql
    ✨ Loads the quote insurance dimension by extracting unique insurance entities from contract mapping data, excluding standard charge entries.
    writes DimensionMember · reads ContractMap
  5. Load Facility - SourcePhygenFacilitysqlload_dimension_members__load_facility_sourcephygenfacility.sql
    ✨ Updates the facility dimension with names and parent profit centers sourced from the Phygen facility reference data.
    writes DimensionMember · reads FacilitySourcePhygenFacility
  6. Load Facility (SourceEpicFacilityReference-Old)sqlload_dimension_members__load_facility_sourceepicfacilityreference_old.sqldisabled
    ✨ Updates facility display names in the dimension table using legacy Epic facility reference mapping.
    writes DimensionMember · reads FacilitySourceEpicFacilityReference
  7. Load VisitStatussqlload_dimension_members__load_visitstatus.sql
    ✨ Loads unique appointment status values from visit data into the dimension member table for reporting.
    writes DimensionMember · reads TransformVisit
  8. Load Provider Dimensionsqlload_dimension_members__load_provider_dimension.sql
    ✨ Populates the provider dimension with surgeon details and their associated provider types from source systems.
    writes DimensionMember · reads ProviderSourcePhygenSurgeons
  9. Load ProviderType Dimensionsqlload_dimension_members__load_providertype_dimension.sql
    ✨ Builds the provider type dimension by extracting unique parent classifications from the existing provider dimension members.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  10. Load ChargeTypesqlload_dimension_members__load_chargetype.sql
    ✨ Loads charge types into the dimension table and categorizes them using extracted CPT subcategory suffixes.
    writes DimensionMember · reads ChargeType
  11. Load Procedure SubCategorysqlload_dimension_members__load_procedure_subcategory.sql
    ✨ Builds the procedure subcategory dimension by extracting parent categories from existing charge type records.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  12. Load ProcedureCodesqlload_dimension_members__load_procedurecode.sql
    ✨ Loads unique procedure codes into the dimension table by extracting them from charge type data.
    writes DimensionMember · reads ChargeType
  13. Load Modifiersqlload_dimension_members__load_modifier.sql
    ✨ Updates the modifier dimension with codes and descriptions sourced from common procedure modifier reference data.
    writes DimensionMember · reads ProcedureModifier
  14. Load Timesqlload_dimension_members__load_time.sql
    ✨ Builds a time dimension hierarchy including days, months, and years based on the range of appointment and visit dates.
    writes DimensionMember · reads SourceAppointmentvisit
  15. Load EligibilityServiceTypesqlload_dimension_members__load_eligibilityservicetype.sql
    ✨ Loads standard eligibility service type categories, such as medical and surgical care, into the dimension table.
    writes DimensionMember
  16. Load ChargesPresent Custom Property Dimensionsqlload_dimension_members__load_chargespresent_custom_property_dimension.sql
    ✨ Builds a dimension to flag whether charges are present on a record using simple affirmative or negative indicators.
    writes DimensionMember
  17. Load CoverageStatusFlag Custom Property Dimensionsqlload_dimension_members__load_coveragestatusflag_custom_property_dimension.sql
    ✨ Populates primary and secondary insurance coverage status dimensions with standard values like active, inactive, or failed.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  18. Load PatientEmailInfo Custom Property Dimensionsqlload_dimension_members__load_patientemailinfo_custom_property_dimension.sql
    ✨ Updates the patient email information dimension to track whether an email address is present or absent in the system.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  19. Load Estimate Status Custom Property Dimensionsqlload_dimension_members__load_estimate_status_custom_property_dimension.sql
    ✨ Loads predefined estimate status categories into the dimension table to track how patient cost estimates are communicated.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  20. Load Eligibility Status Custom Property Dimensionsqlload_dimension_members__load_eligibility_status_custom_property_dimension.sql
    ✨ Loads insurance eligibility status categories into the dimension table to categorize patient coverage verification results.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Financial Counselor Custom Property Dimension, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  21. Load Financial Counselor Custom Property Dimensionsqlload_dimension_members__load_financial_counselor_custom_property_dimension.sql
    ✨ Loads financial counselor identifiers into the dimension table to support staff assignment and reporting for patient financial services.
    writes DimensionMember · reads DimensionMember
    ↳ feeds from Load ChargeType, Load ChargesPresent Custom Property Dimension, Load CoverageStatusFlag Custom Property Dimension, Load Eligibility Status Custom Property Dimension, Load EligibilityInsurance, Load EligibilityServiceType, Load Estimate Status Custom Property Dimension, Load Facility - SourcePhygenFacility, Load Facility Group, Load Insurance, Load Insurance Category, Load Modifier, Load PatientEmailInfo Custom Property Dimension, Load Procedure SubCategory, Load ProcedureCode, Load Provider Dimension, Load ProviderType Dimension, Load QuoteInsurance, Load State, Load Time, Load VisitStatus
  22. Load Facility Groupsqlload_dimension_members__load_facility_group.sql
    ✨ Builds the facility group dimension by extracting and updating unique profit center names from the source facility data.
    writes DimensionMember · reads SourcePhygenFacility
  23. Load Statesqlload_dimension_members__load_state.sql
    ✨ Updates state dimensions using facility location data to allow for geographic segmentation of healthcare services and revenue.
    writes DimensionMember · reads Facility