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

sub-job

SGP · RevFind-Transform-QuickSight-Account-Transform

called by RevFind-Daily-Processing

sub-job SGP/RevFind-Transform-QuickSight-Account-Transform · db SGP_ProdMirror · updated 2026-04-02

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

RevFind-Transform-QuickSight-Account-Transform

14 tasks · SGP
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. Create AccountKeyMapsqlrevfind_transform_quicksight_account_transform__create_accountkeymap.sql
    ✨ Builds a mapping table to link source account identifiers with internal unique keys for consistent cross-system tracking.
    writes AccountKeyMap · reads AccountKeyMapSourceEncounter
  2. Create AccountDeltasqlrevfind_transform_quicksight_account_transform__create_accountdelta.sql
    ✨ Identifies and loads account IDs that have changed or been deleted since the last pipeline execution into a delta table.
    writes AccountDelta · reads AccountKeyMapProcessLogSourceChargeSourceEncounterSourceTransaction
    ↳ feeds from Create AccountKeyMap
  3. Load AccountDeltaLogsqlrevfind_transform_quicksight_account_transform__load_accountdeltalog.sql
    ✨ Records the total number of changed accounts processed in the current batch for auditing and monitoring purposes.
    writes AccountDeltaLog · reads AccountDelta
    ↳ feeds from Create AccountDelta
  4. Load TransformPatientsqlrevfind_transform_quicksight_account_transform__load_transformpatient.sql
    ✨ Builds and updates the patient transformation table by merging demographic data and generating unique identifiers from source systems.
    writes TransformPatient · reads SourcePatient
  5. Load TransformLocationsqlrevfind_transform_quicksight_account_transform__load_transformlocation.sql
    ✨ Builds and updates the location transformation table by mapping facility addresses and zip codes to standardized state codes.
    writes TransformLocation · reads CMSZipMapSourceLocation
  6. Load TransformProvidersqlrevfind_transform_quicksight_account_transform__load_transformprovider.sql
    ✨ Builds and updates the provider transformation table by parsing names and mapping National Provider Identifiers from source systems.
    writes TransformProvider · reads SourceProvider
  7. Load TransformPayersqlrevfind_transform_quicksight_account_transform__load_transformpayer.sql
    ✨ Builds and updates the payer transformation table by consolidating insurance carrier names and generating unique payer identifiers.
    writes TransformPayer · reads SourceInsurance
  8. Load TransformChargesqlrevfind_transform_quicksight_account_transform__load_transformcharge.sql
    ✨ Loads charge-level details into the transformation table, calculating service units, amounts, and bill types for updated accounts.
    writes TransformCharge · reads AccountDeltaAccountKeyMapSourceChargeSourceEncounterSourceLocation
    ↳ feeds from Create AccountDelta, Create AccountKeyMap
  9. Load TransformAccountInsurancesqlrevfind_transform_quicksight_account_transform__load_transformaccountinsurance.sql
    ✨ Updates account insurance records by determining the coordination of benefits order and mapping member policy information for active coverage.
    writes TransformAccountInsurance · reads AccountDeltaAccountKeyMapSourceEncounterSourcePatientEncounterInsuranceSourcePatientEncounterInsuranceExceptions
    ↳ feeds from Create AccountDelta, Create AccountKeyMap
  10. Load TransformTransactionsqlrevfind_transform_quicksight_account_transform__load_transformtransaction.sql
    ✨ Loads and categorizes financial transactions into payments, adjustments, or denials based on source codes and coordination of benefits.
    writes TransformTransaction · reads AccountDeltaAccountKeyMapAdjustmentCodeRollupSourceEncounterSourceTransactionTransformAccountInsurance
    ↳ feeds from Create AccountDelta, Create AccountKeyMap, Load TransformAccountInsurance
  11. Load TransformAccountsqlrevfind_transform_quicksight_account_transform__load_transformaccount.sql
    ✨ Builds the account transformation table by aggregating charge balances and linking encounters to providers, facilities, and primary insurance.
    writes TransformAccount · reads AccountDeltaAccountKeyMapSourceEncounterSourceLocationTransformCharge
    ↳ feeds from Create AccountDelta, Create AccountKeyMap, Load TransformCharge
  12. Merge TransformTransactionMergeSqldisabled
    writes TransformTransaction · reads AdjustmentCodeRollupImportTransactionSourceEncounterTransformAccountInsurance
    merge data source type=ThisDatabasetable name=TransformTransactionmerge mode=InsertUpdateHardDeletebulk copy timeout seconds=300bulk copy use table lock
  13. Update Deltasqlrevfind_transform_quicksight_account_transform__update_delta.sqldisabled
    ✨ Identifies and records account IDs with recent transaction updates to track incremental changes for downstream processing.
    writes AccountDelta · reads AccountDeltaProcessLogTransformTransaction
  14. Load TransformChargeTransactionMeasuresqlrevfind_transform_quicksight_account_transform__load_transformchargetransactionmeasure.sql
    ✨ Calculates and updates aggregated financial metrics, including payments, adjustments, and denials, for accounts identified in the delta tracking table.
    writes TransformChargeTransactionMeasure · reads AccountDeltaAccountKeyMapAdjustmentCodeRollupSourceChargeTransformChargeTransformTransaction
    ↳ feeds from Create AccountDelta, Create AccountKeyMap, Load TransformCharge, Load TransformTransaction