‍

The ETL versus ELT debate gets a simple answer in most enterprise data contexts: ELT wins because modern cloud platforms make in-place transformation cheaper and more flexible. For Medicare Advantage (MA) plans, regional carriers, and managed Medicaid organizations, that answer is incomplete. In this article, we’ll go over the prerequisites that determine whether ETL or ELT is right for payers.

‍

The Short Answer for Payer CIOs

‍

Most payer engineering leaders who have been reading general enterprise data guidance on the etl vs elt debate have probably already landed on ELT as the obvious winner. Modern cloud platforms, cheaper compute, and the flexibility of transforming data in-place make it look like a no-brainer. For most industries, it is. But payers are not in most industries.

‍

ELT Is the Structurally Superior Pattern for Modern Payer Platforms, but It Requires Three Prerequisites That Most Migration Plans Skip

‍

ELT offers real advantages for health plans and MA organizations: more flexibility for the data engineering team, preserved raw source fidelity, and compute economics that favor it on Databricks or Microsoft Fabric. The problem is that most migration roadmaps treat the platform as the prerequisite. It is not. The actual prerequisites are raw data governance across a PHI-rich landing zone, transformation auditability sufficient for CMS submission defense.

‍

Schema management capable of handling heterogeneous source formats including:

‍

• 834 enrollment files
• 835 and 837 claim transactions
• CMS MOR files
• HL7 v2 ADT feeds

‍

Skip any of those three, and ELT stops being an efficiency gain and starts being a liability.

‍

Without Those Foundations, ELT Introduces Compliance and RAF Revenue Risk That Outweighs the Efficiency Gain

‍

In traditional ETL, transformation logic runs before data lands in the warehouse. PHI gets masked and validated before it touches storage. In ELT, raw data lands first, and that is exactly where payer risk sits. Unmasked 834 and 835 files in a bronze zone create a HIPAA exposure window. A subtle logic error in the diagnosis transformation pipeline does not throw a failure alert either. It produces a mistaken CMS submission, a depressed RAF score, and a payment shortfall that compounds across the membership base.

‍

Why the Standard ETL vs ELT Framework Does Not Apply to Payer Data

‍

Payer Source Data Is Structurally Different from Typical Enterprise Data 

‍

An 834 eligibility transaction is not a database row. An 837 claim file is not a CSV export. These are X12 EDI formats with deeply nested loops, hierarchical structures, and format specifications going back decades. MOR and MMR files from CMS carry their own schema quirks. HL7 v2 ADT feeds from HIEs arrive as pipe-delimited messages that behave nothing like the JSON payloads most data engineers worked with before joining a health plan. Each format demands parsing logic that has to be explicitly designed, not assumed.

‍

Eligibility Is Not Append-Only

‍

Eligibility is the source of truth for everything in a health plan: claims payment, PCP attribution, quality measure assignment, and risk adjustment revenue. Unlike most enterprise data, it is not append-only. Member terminations happen retroactively. PCP attribution changes mid-year when a provider switches organizations. Plan adjustments ripple backward through coverage periods. If the ELT pipeline defaults to incremental loading with no policy for handling overwrites, those changes will silently corrupt downstream member truth.

‍

IBNR Estimation Depends on a Complete and Current Member View

‍

Claims in MA experience a well-documented lag of 30 to 60 days between service delivery and processing. That lag is why actuaries calculate IBNR (Incurred But Not Reported) estimates. If the enterprise data warehouse delivers an incrementally patched member view instead of a complete, current one, IBNR models work off incomplete data and board-level financial forecasting cannot be trusted.

‍

Prerequisites ELT Requires Before a Payer Can Use It Safely

‍

Raw Data Governance and PHI Exposure in the Landing Zone 

‍

When 834, 835, and MOR files land in the bronze layer unmasked, PHI exists in cloud storage before a single transformation has run. HHS's Security Rule guidance is clear that "addressable" implementation specifications are not optional. Entities must implement them if reasonable and appropriate, or document an equivalent alternative. Landing-zone encryption and key management are not controls to postpone to phase two.

‍

How Leading Payers Are Solving This with Medallion Architecture and More

‍

The pattern gaining traction at regional health plans is bronze-silver-gold medallion architecture. Bronze holds raw ingested data with strict access controls. Silver applies cleansing, deduplication, and tokenization. Gold surfaces analytics-ready member views. On Databricks, this maps to Delta Lake with tiered encryption and Unity Catalog for column-level access control. On Snowflake, dynamic data masking and column-level security perform a similar function. 

‍

Transformation Auditability for CMS Submissions

‍

In legacy ETL tools like Informatica or SSIS, lineage was partially baked in because transformation logic was centralized. In ELT, lineage must be designed explicitly from day one. CMS's March 2024 HPMS memo on encounter data describes that CMS sends an MAO-002 report after submission and an MAO-004 report informing submitters about risk adjustment eligibility of diagnoses, including those added or deleted. If the pipeline cannot deterministically reproduce what was submitted and why diagnoses changed, outcomes cannot be defended during RADV-related readiness work.

‍

Schema Management Across Heterogeneous Formats

‍

Under CMS-0057-F payer-to-payer exchange requirements, FHIR R4 payloads are arriving from external partners. Those payloads do not map cleanly into schemas built for EDI files. Schema evolution controls are needed that handle both formats without manual intervention every time CMS updates a specification.

‍

Full Refresh vs Incremental Loading 

‍

Why Incremental Loading Is the Right Default for Payer Data Warehouses

‍

Payer data warehouses cannot sustain a full refresh strategy at scale. At 10,000 members, a full table rebuild may take a few hours. At 500,000 members, the next refresh cycle starts before the current one finishes. The downstream consequences are missed CMS submission windows, delayed eligibility updates, and financial close cycles that block reporting teams.

‍

Incremental loading solves this by updating only what changed since the last refresh. For health plans managing eligibility cascades, claims lag, and HCC recalculation dependencies, this is not a performance optimization. It is a prerequisite for reliable payer data operations at scale. Health plans processing 500,000 members have reduced eligibility refresh times from six hours to under 20 minutes by adopting incremental patterns.

‍

Managing Retroactive Adjustments with Incremental Patterns

‍

The most common objection to incremental loading is retroactive eligibility. When a payer retroactively adjusts PCP attribution for a member cohort, that change touches historical records. This is a real risk, but it is solved at the orchestration layer, not by defaulting to full refreshes.

‍

The answer is a written policy distinguishing append-safe sources from overwrite-prone ones, paired with dependency-aware orchestration. For eligibility, incremental pipelines must use Change Data Capture logic that compares incoming files against the current state and propagates only changed records downstream. When PCP attribution retroactively changes from January to March, the incremental job identifies that member, updates the attribution record with a modification timestamp, triggers a delta recalculation of affected claims, and updates HCC codes and RAF scores for that member only. Microsoft Fabric provides native CDC capabilities through Delta Lake, making this pattern straightforward to implement without custom change-tracking logic.

‍

Where Incremental Loading Applies and Where Full Refresh Still Makes Sense

‍

Incremental loading is the right default for most payer source types. ADT feeds, pharmacy claims, EDI claim batches, eligibility updates, and risk score recalculations all fit naturally into incremental patterns. Each update only affected members and their downstream records.

‍

Full refresh still makes sense in specific, bounded scenarios. Monthly financial close often warrants it for audited reporting completeness. Annual HEDIS calculations typically require full population analysis. These are deliberate choices for defined reporting cycles, not the architectural default. A common hybrid runs incremental loads weekly for operational data, full refresh monthly for financial reporting, and quarterly for quality analytics. The discipline is a written policy specifying which sources follow which pattern and why, so teams are not silently applying incremental logic to sources that carry retroactive overwrites.

‍

CMS Submission Auditability: The Risk That Defines the Architecture Decision

‍

What MAO-004 Files Are and Why They Function as a Receipt of Diagnosis Acceptance from CMS

‍

The MAO-004 file is how CMS confirms submitted ICD-10 diagnosis codes were received and accepted for risk adjustment. Think of it as the government's receipt. When submitted diagnoses match MAO-004 confirmation, the RAF scores and payment are correct. When they do not, because of a transformation error between source data and submission, both are wrong. CMS's RADV final rule fact sheet frames this directly: the RADV program addresses instances where CMS paid MA organizations more than warranted because submitted diagnoses were not supported in the medical record.

‍

How a Transformation Error Can Cause RAF Score Inaccuracies 

‍

A logic error miscoding even a small percentage of chronic condition diagnoses, say HCC 19 (diabetes without complication) versus HCC 18 (diabetes with acute complication), produces a RAF differential of 0.1 to 0.3 per member. With CMS projecting average capitated payments of $16,242 per beneficiary per year in 2026, fractional RAF inaccuracies carry significant revenue consequences at any meaningful membership scale. The error is invisible in the pipeline and only surfaces at CMS reconciliation.

‍

How to Build Auditability into an ELT Pipeline 

‍

On Databricks, Delta Lake transaction logs provide a time-travel-capable record of every write operation. Combined with dbt model lineage, teams can trace a specific diagnosis code from its raw source file through every transformation step to its final submitted value. On Snowflake, dynamic tables with query history and dbt Cloud lineage perform a similar function. The design principle is the same across platforms: auditability cannot be retroactively added. It has to be part of the pipeline architecture from the first transformation layer.

‍

What CMS Audit Documentation Requirements Actually Look Like at the Pipeline Level 

‍

CMS expects payers to demonstrate, at the record level, that submitted diagnoses are supported by documented clinical encounters. At the pipeline level, that means producing a lineage trace from MAO-004 submission back to the source encounter record through every intermediate transformation. In practice this maps to versioned transformation code, immutable raw inputs, job-run metadata, and the ability to replay a submission month using the same code and the same raw snapshot. It is achievable in modern ELT, but only if the architecture is designed for it explicitly from the start.

‍

Platform-Specific Implications for the Payer Stack

‍

Databricks Delta Live Tables and How They Handle Streaming ADT Feeds Differently Than Snowflake Dynamic Tables for Payer Workloads

‍

Databricks Delta Live Tables are built for continuous ingestion with declarative pipeline management, making them well-suited for streaming HL7 v2 ADT feeds from HIEs where near-real-time member census matters. Snowflake moved toward managed incremental pipelines with Dynamic Tables, which reached general availability in April 2024, supporting batch and near-real-time patterns. DLT handles the streaming ADT pattern more natively; Dynamic Tables are stronger for structured batch workloads with complex dependency management. Neither is inherently cheaper for all payer workloads. The deciding factor is the data's latency requirements and the team's existing platform expertise.

‍

Microsoft Fabric and Where Transformation Logic Lives When a Payer Is Running Power BI on Top of Their EDW

‍

Fabric collapses the boundary between the data platform and the BI layer, which creates ambiguity about where transformation logic lives. If business logic bleeds into Power BI calculated measures instead of sitting in the gold layer, the result is version control problems and CMS audit gaps. Fabric's pipeline metering includes 0.0056 CU hours per orchestration activity run, which compounds in high-frequency payer ingestion patterns and needs to be explicitly accounted for in the cost model.

‍

Amazon Redshift Considerations for Payers Who Are AWS-Native but Evaluating a Platform Consolidation

Redshift Serverless begins at $1.50 per hour and performs well for structured payer workloads. Its key limitation is handling semi-structured FHIR JSON, which is more cumbersome than Databricks or Snowflake. As CMS-0057-F drives more FHIR inbound data, that limitation becomes increasingly relevant for any payer planning to stay AWS-native long-term or consolidating platforms around a single engine.

‍

How Each Platform Handles PHI Access Controls at the Landing Layer and What That Means for a HIPAA-Compliant Bronze Zone

‍

A HIPAA-compliant bronze zone requires role-based access with least-privilege enforcement, encryption at rest with customer-managed keys, and audit logging of every read and write operation. Snowflake's dynamic data masking and Databricks Unity Catalog both support column-level policies out of the box. Redshift requires more deliberate configuration for equivalent column-level security. The platform matters less than whether these controls are implemented explicitly rather than assumed by default.

‍

The Hidden Operational Cost of Legacy ETL That Payer Teams Undercount

‍

What Informatica and SSIS Pipelines Built During the ICD-10 Transition Era Typically Look Like Today

‍

Many payer teams are still running Informatica or SSIS pipelines last meaningfully updated during the ICD-10 transition in 2015. Those pipelines carry undocumented transformation logic in proprietary mapping files, brittle dependencies on source schemas that break whenever an upstream vendor updates a file format, and zero FHIR ingestion support. Every incident response event and every schema-change fire drill pulls the data engineering team away from the work that actually drives revenue.

‍

Why CMS-0057-F Creates a Forcing Function Toward ELT

‍

Legacy ETL pipelines output EDI-shaped schemas. FHIR R4 payloads from payer-to-payer exchange are not EDI-shaped. That mismatch is not a configuration problem. It is architectural. And because CMS-0057-F makes FHIR exchange mandatory, payers cannot defer the fix. Every month a legacy ETL pipeline stays in production is a month it cannot ingest or process the data types that CMS now requires payers to handle.

‍

How to Characterize the Operational Tax of Maintaining Legacy ETL Versus the Migration Investment of Moving to ELT 

‍

The most underestimated legacy ETL cost is people-time. BLS median wage data from May 2024 puts database architects at $135,980 annually and database administrators at $104,620. A team of four to eight mixed roles dedicated to pipeline maintenance is not unusual at a regional health plan. At published medians, that footprint plausibly represents high six to low seven figures in fully-loaded annual labor cost before counting the opportunity cost of what those engineers are not building. Platform compute compounds this: AWS Glue charges $0.44 per DPU-hour and Azure Data Factory transformation compute bills at $0.274 per vCore-hour with a minimum of 8 vCores. And critically: it is impossible to AI-enable a brittle, undocumented ETL pipeline. Machine learning models for HCC suspect identification, IBNR estimation, and STARS gap closure all require a clean, well-governed data foundation. Legacy ETL makes that foundation impossible to build on.

‍

Building the Internal Case for an ELT Migration

‍

How to Frame the Architecture Decision as a Revenue Engineering Problem

‍

The frame that works is revenue engineering, not infrastructure modernization. The question is not "should we upgrade our data platform?" It is "what is it costing us in RAF revenue and STARS performance to maintain an architecture that cannot support accurate HCC capture, real-time member attribution, and CMS submission auditability?" That framing puts the decision in language finance leadership understands and can act on.

‍

Addressing the PHI-in-the-Raw-Layer Objection That Typically Comes from Compliance or Legal Stakeholders

‍

Compliance and legal stakeholders will raise the PHI exposure concern the moment they understand how ELT works. The answer is to demonstrate that medallion architecture with tokenization at ingestion, tiered encryption, and Unity Catalog or Snowflake RBAC provides more granular PHI governance than most legacy ETL environments actually have. Many Informatica pipelines, if audited carefully, have PHI moving through transformation servers with weaker access controls than a properly configured Databricks bronze zone. The ELT architecture is not the risk. The unaudited legacy one is.

‍

What a Phased Migration from Legacy ETL to Production ELT Looks Like at a Regional Payer and Where the Highest-Risk Transition Points Are

‍

Start with new data sources, not legacy ones. Onboard FHIR payer-to-payer exchange data first, because those pipelines do not yet exist and there is nothing to break. Next, migrate eligibility with a full-refresh policy established from the start. Claims come after eligibility is stable, because claims attribution depends on a correct member view. CMS submission pipelines migrate last, and only after lineage design has been validated through at least one submission cycle. The highest-risk transition point is eligibility. Get that wrong and every downstream system, from claims routing to quality reporting to RAF submissions, inherits the error.

‍

What Invene Sees in the Field

‍

The pattern Invene encounters most often at regional health plans and Medicare Advantage organizations is not a failure to adopt ELT. It is a failure to design the consumption layer that makes ELT actually useful. Teams focus on ingestion, build bronze and silver layers with reasonable care, then rush the gold layer. Analytics teams end up querying silver-layer data directly, bypassing the business logic that should live in gold.

‍

What that produces is a data platform that can run ELT processes but cannot support analytics. ML models for suspect HCC identification cannot run against an inconsistently modeled member table. STARS gap closure programs cannot query a gold layer that does not exist. Everyone thinks about the technical process. 

‍

Almost no one thinks early enough about the change management process of designing a smart consumption layer about what the downstream consumer actually needs from the gold layer to do their job.

‍

Final Thoughts

‍

The etl vs elt decision for payer organizations is not a technical debate. It is a revenue and compliance decision that requires technical expertise to execute correctly. ELT is the right directional choice for health plans and Medicare Advantage organizations operating on modern cloud platforms. But the prerequisites are real, the financial exposure from skipping them is documented, and the regulatory pressure from CMS-0057-F is not going away. If the team has PHI governance, transformation auditability, and schema management in place, ELT provides the scalable foundation that supports accurate HCC capture, CMS submission integrity, and the AI-ready analytics clinical and finance leaders need. If those foundations are not yet solid, build them before committing to a migration timeline.

‍

Frequently Asked Questions

‍

How can Invene help our organization with its payer data architecture decisions?

‍

Invene is a healthcare-specialized technology firm that helps payers, providers, HealthTech companies, life sciences organizations, and medical device makers build compliant, high-impact software and data systems. For payer data architecture specifically, Invene brings deep expertise in enterprise data warehouse design, cloud migration, data fabric architecture, and AI-powered analytics. Rather than treating ETL vs ELT as a generic technical question, Invene helps payer engineering teams evaluate the decision in the context of their specific PHI governance posture, CMS submission requirements, and RAF revenue risk. Our team works fluently across the regulatory and technical domains that define payer data work, including HIPAA, HITRUST, FHIR, HCC capture, and CMS audit compliance, eliminating the need to spend time educating on healthcare fundamentals before the real work begins.

‍

What is the core difference between ETL and ELT in a payer data context?

‍

In ETL (Extract, Transform, Load), data is cleaned and transformed before it lands in the data warehouse. In ELT (Extract, Load, Transform), raw data lands first and transformation happens inside the platform. For payers, the critical implication is that ELT puts PHI-rich source files like 834 and 835 transactions into the storage layer before any masking or governance has run, which requires explicit controls that many migration plans underestimate.

‍

Why does claims lag matter for choosing between full refresh and incremental loading?

‍

The 30-to-60-day claims lag means claims are still arriving well after the service date. When combined with retroactive eligibility changes, an incremental loading strategy can produce a member view that is silently incomplete. Full refreshes of eligibility ensure the enterprise data warehouse reflects actual current member state rather than an accumulated approximation.

‍

How does a transformation error in an ELT pipeline affect RAF scores and CMS revenue?

‍

If the transformation logic miscodes chronic condition diagnoses before CMS submission, the RAF score CMS calculates will be lower than actual population risk warrants. With CMS projecting average MA capitated payments of $16,242 per beneficiary per year in 2026, even fractional RAF inaccuracies carry significant revenue consequences at scale. The error is invisible in the pipeline and only surfaces at CMS reconciliation.

‍

What does CMS-0057-F require that legacy ETL pipelines cannot support?

‍

CMS-0057-F requires payers to support FHIR-format data exchange under payer-to-payer exchange rules, including five years of patient data history. Legacy ETL pipelines built for EDI X12 formats have no FHIR ingestion support and output schemas incompatible with FHIR-structured data. This structural mismatch cannot be resolved through configuration and acts as a direct forcing function toward ELT on a modern platform.

‍

What should a CIO look for when evaluating whether their ELT migration plan is ready for payer workloads?

‍

Three specific prerequisites indicate readiness: a medallion architecture with tokenization at ingestion and tiered encryption controls PHI in the bronze zone; transformation lineage is explicitly designed and testable, not assumed to be handled by the platform; and schema management handles both EDI and FHIR formats without manual intervention. If all three are present, the migration can proceed. If any are absent, additional design work is needed before go-live.

‍

‍