LoadAccountData

Overview

LoadAccountDataTask imports historical account data from external billing systems into MDClarity's four core account tables using custom SQL queries. It intelligently merges new and updated data while preserving application-controlled fields and user modifications.

Use this task when: You need to import or sync historical billing data (accounts, charges, remits, insurance) from external systems like Epic, Cerner, or data warehouses.

Don't use this task when: Loading visit data for prospective estimates (use LoadVisitsTask), importing from files (use LoadDataFileTask), or needing simple table replacement without merge logic (use ImportSqlTask).

Parameters

Execution Flow

flowchart TD
    A[Start LoadAccountData] --> B[Validate All Four Queries]
    B --> C{All Queries Valid?}
    C -->|No| Z[Throw ArgumentException]
    C -->|Yes| D[Get Medicare Insurance Keys from FeeLogic]
    D --> E[Setup AccountChargeLoader]

    E --> F[Load AccountCharge Data]
    F --> F1[Create temp view from query]
    F1 --> F2[Process in batches of BatchSize]
    F2 --> F3[Compare with existing data]
    F3 --> F4[Merge: Insert new + Update changed]
    F4 --> F5[Queue affected accounts for reprocessing]

    F5 --> G[Load Account Data]
    G --> G1[Create temp view from query]
    G1 --> G2[Process in batches of BatchSize]
    G2 --> G3[Preserve OriginalInsurance logic]
    G3 --> G4[Merge: Insert new + Update changed]
    G4 --> G5[Queue affected accounts for reprocessing]

    G5 --> H[Load AccountRemit Data]
    H --> H1[Create temp view from query]
    H1 --> H2[Process in batches of BatchSize]
    H2 --> H3[Merge: Insert new + Update changed]

    H3 --> I[Load AccountInsurance Data]
    I --> I1[Create temp view from query]
    I1 --> I2[Process in batches of BatchSize]
    I2 --> I3[Merge: Insert new + Update changed]

    I3 --> J[End]

    style F fill:#e1f5ff
    style G fill:#e1f5ff
    style H fill:#e1f5ff
    style I fill:#e1f5ff
    style F5 fill:#fff4e1
    style G5 fill:#fff4e1

Processing Algorithm

Load Order and Dependencies

The task loads data in a specific order due to dependencies between tables:

1. AccountCharge (first) - Loaded first because Account table calculations depend on charge-level totals (AmountCharged, AmountAdjusted, etc.)
2. Account (second) - Loaded after charges to ensure accurate financial aggregations
3. AccountRemit (third) - Payment/adjustment details linked to accounts and charges
4. AccountInsurance (fourth) - Insurance coverage information for accounts

Intelligent Merge Logic

For each table, the loader:

1. Creates a temporary database view from the source query
2. Retrieves existing records from the target table in batches
3. Compares source data with existing data row-by-row
4. Identifies changes using specialized comparers (e.g., AccountChargeComparer)
5. Performs bulk merge operations: inserts for new records, updates for changed records
6. Queues affected accounts for reprocessing (triggers recalculation of estimates)

Special Account.Insurance Handling

The Account table has special logic to preserve user-modified insurance assignments:

IF OriginalInsurance IS NOT NULL AND OriginalInsurance = source.Insurance
THEN keep existing Account.Insurance (user changed it)
ELSE use source.Insurance (take incoming value)

This prevents overwriting manual insurance corrections made by users in the application.

Column Mismatch Handling

If the source query is missing columns that exist in the target table:

• Nullable columns: Automatically filled with typed NULL values
• Non-nullable columns: Throws InvalidOperationException with clear error message

If the source query has extra columns not in the target table:

• Extra columns are ignored (allows queries with bonus data for debugging)

Example Execution

For a customer loading 1000 accounts with 5000 charges, using BatchSize=10000:

1. AccountCharge: 5000 charges loaded in 1 batch, compared row-by-row, 500 new + 200 updated
2. Account: 1000 accounts loaded in 1 batch, 100 new + 50 updated
3. AccountRemit: 300 remits loaded in 1 batch, 50 new + 10 updated
4. AccountInsurance: 1500 insurance records loaded in 1 batch, 200 new + 100 updated
5. Accounts queued for reprocessing: 650 (those with data changes)

Usage Examples

Example 1: Basic account data import from billing system

var task = new LoadAccountDataTask
{
    AccountQuery = @"
        SELECT AccountID, Facility, Insurance, PatientKey, ServiceDate, LengthOfStay,
               AmountCharged, AmountAdjusted, AmountDenied, AmountPatient,
               AmountPaidInsurance, AmountPaidPatient, HasSecondary,
               AmountAdjustedSecondary, AmountDeniedSecondary, AmountPatientSecondary,
               AmountPaidInsuranceSecondary, AmountAllowed, Balance
        FROM BillingSystem.dbo.Accounts
        WHERE ServiceDate >= '2024-01-01'",

    AccountChargeQuery = @"
        SELECT AccountID, ChargeNumber, ChargeTypeID, BillType, ProviderID, ServiceDate,
               Description, Codes, Units, AmountCharged, AmountAdjusted, AmountDenied,
               AmountPatient, AmountPaidInsurance, AmountPaidPatient, AmountAllowed, Balance
        FROM BillingSystem.dbo.AccountCharges
        WHERE ServiceDate >= '2024-01-01'",

    AccountRemitQuery = @"
        SELECT AccountID, ChargeNumber, RemitType, Description, RemitDate, Amount,
               PayerName, COB, RemarkCode, AdjustmentCode, DenialCategory
        FROM BillingSystem.dbo.Remits
        WHERE RemitDate >= '2024-01-01'",

    AccountInsuranceQuery = @"
        SELECT AccountID, COB, SourceInsuranceID, SourcePatientInsuranceID,
               MemberID, GroupNumber, GroupName, InsuranceKey, ICN
        FROM BillingSystem.dbo.AccountInsurances
        WHERE CreatedDate >= '2024-01-01'"
};

Example 2: Incremental daily sync with date filtering

var task = new LoadAccountDataTask
{
    // Only load accounts modified in the last 24 hours
    AccountQuery = @"
        SELECT AccountID, Facility, Insurance, PatientKey, ServiceDate, LengthOfStay,
               AmountCharged, AmountAdjusted, AmountDenied, AmountPatient,
               AmountPaidInsurance, AmountPaidPatient, HasSecondary,
               AmountAdjustedSecondary, AmountDeniedSecondary, AmountPatientSecondary,
               AmountPaidInsuranceSecondary, AmountAllowed, Balance
        FROM BillingSystem.dbo.Accounts
        WHERE ModifiedDate >= DATEADD(day, -1, GETDATE())",

    // Sync charges for those modified accounts plus any new charges
    AccountChargeQuery = @"
        SELECT AccountID, ChargeNumber, ChargeTypeID, BillType, ProviderID, ServiceDate,
               Description, Codes, Units, AmountCharged, AmountAdjusted, AmountDenied,
               AmountPatient, AmountPaidInsurance, AmountPaidPatient, AmountAllowed, Balance
        FROM BillingSystem.dbo.AccountCharges
        WHERE AccountID IN (
            SELECT AccountID FROM BillingSystem.dbo.Accounts
            WHERE ModifiedDate >= DATEADD(day, -1, GETDATE())
        ) OR ModifiedDate >= DATEADD(day, -1, GETDATE())",

    AccountRemitQuery = "SELECT * FROM BillingSystem.dbo.Remits WHERE RemitDate >= DATEADD(day, -1, GETDATE())",
    AccountInsuranceQuery = "SELECT * FROM BillingSystem.dbo.AccountInsurances WHERE ModifiedDate >= DATEADD(day, -1, GETDATE())",
    BatchSize = 5000  // Smaller batches for frequent incremental loads
};

Example 3: Handling missing optional columns

var task = new LoadAccountDataTask
{
    // Query missing optional nullable columns like InitialDenialCategory, DenialCategory
    // The loader will automatically inject typed NULLs for these columns
    AccountQuery = @"
        SELECT AccountID, Facility, Insurance, PatientKey, ServiceDate, LengthOfStay,
               AmountCharged, AmountAdjusted, AmountDenied, AmountPatient,
               AmountPaidInsurance, AmountPaidPatient, HasSecondary,
               AmountAdjustedSecondary, AmountDeniedSecondary, AmountPatientSecondary,
               AmountPaidInsuranceSecondary, AmountAllowed, Balance
               -- Note: InitialDenialCategory, DenialCategory, InitialAmountDenied omitted
               -- These will be filled with CAST(NULL AS appropriate_type)
        FROM LegacySystem.dbo.Accounts",

    // Similar for other queries...
    AccountChargeQuery = "...",
    AccountRemitQuery = "...",
    AccountInsuranceQuery = "..."
};

Example 4: Large volume import with performance tuning

var task = new LoadAccountDataTask
{
    AccountQuery = "SELECT * FROM HistoricalData.dbo.Accounts",
    AccountChargeQuery = "SELECT * FROM HistoricalData.dbo.Charges",
    AccountRemitQuery = "SELECT * FROM HistoricalData.dbo.Remits",
    AccountInsuranceQuery = "SELECT * FROM HistoricalData.dbo.Insurances",
    BatchSize = 100_000  // Larger batch size for initial historical load (more memory, faster processing)
};

Related Tasks Comparison

Performance Considerations

Database Impact

• Tables Written: Account, AccountCharge, AccountRemit, AccountInsurance (4 tables)
• Tables Read: FeeLogic (for Medicare insurance keys), plus source queries
• Temporary Objects: Creates 4 temp views (__AccountLoaderTempView__, etc.) and 4 temp tables for paging
• Bulk Operations: Uses BulkComparer pattern for efficient row-by-row comparison
• Indexes: Relies on primary keys (AccountID, ChargeNumber) and foreign keys for merge performance
• Locking: Performs batch updates which may lock rows; safe during off-hours for large imports

Optimization Tips