1h 4m read

How to Migrate from ETL to ELT Without Breaking Reporting or Agentic AI Freshness

A step-by-step ETL to ELT migration guide: land raw extracts in an object-storage landing zone, move transformation logic into the lakehouse, and run parallel synchronization until parity and near real-time freshness are proven for agentic AI consumers.

How to Migrate from ETL to ELT Without Breaking Reporting or Agentic AI Freshness

The end state is a running ELT pipeline: raw source extracts land as immutable files in object storage, transformation executes inside the lakehouse, and the legacy ETL estate keeps serving every existing consumer in parallel until parity is proven. One principle governs the whole sequence — nothing is decommissioned until its replacement has been reconciled against it, on the same data, over the same closed time window.

That principle matters more now than it did for a warehouse-only migration. Your legacy ETL feeds reports and dashboards that finance closes against. It may also feed reverse-ETL syncs into operational systems. Alongside those consumers sits a newer one: an agentic AI workload that needs fresher data than an overnight batch can supply, and that will make decisions — or decline to — based on how stale its inputs are. Moving to ELT changes where transformation runs. It does not, by itself, make data fresher. Freshness comes from the extraction method and the synchronization design, which is why this guide treats it as a separate workstream with its own testable contract.

The sequence below runs three workstreams in a fixed order: file-first ingestion into object storage, relocation of transformation logic into the lakehouse, and parallel synchronization that keeps both pipelines live until each consumer is cut over. Each step ends with a checkpoint you can confirm before proceeding.

Prerequisites — confirm each of these before you move a single table:

  • An object-storage landing zone exists with versioning explicitly enabled and a lifecycle rule configured for noncurrent versions.

  • A lakehouse table format is chosen (Iceberg or Delta Lake) and your team can state its commit, snapshot and time-travel semantics without consulting documentation.

  • Every downstream consumer of every legacy ETL output is inventoried, with a named contact per consumer.

  • A single owner per data domain is named and authorized to approve or reject cutover for that domain.

  • The agentic AI consumer's current data latency is measured, not assumed.

Prerequisites: what must exist before you move a single table

Treat each prerequisite as a pass/fail check rather than a recommendation. The landing zone is the first: object versioning is disabled by default on Amazon S3 and must be explicitly enabled, and the same is true of the equivalent feature on other object stores. Enabling it changes your cost and deletion behavior, so the lifecycle rule for noncurrent versions belongs in the same change, not a later ticket. The S3 Versioning documentation states the operational consequence plainly: if you have an object-expiration lifecycle configuration on an unversioned bucket and want to keep the same permanent-delete behavior after enabling versioning, you must add a noncurrent expiration configuration.

The table format decision is the second. You do not need to standardize on one format across the estate, but you do need to know which one each target table uses before you write transformation logic against it, because schema evolution, time travel and rollback behavior are format-specific. Iceberg, for example, documents that schema updates are metadata changes requiring no data-file rewrite, and that version rollback lets users reset tables to a good state.

The consumer inventory is the third, and it is the one teams most often shortcut. A report is easy to find; a reverse-ETL feed that writes back into a CRM, or an agent that queries a table directly through a semantic layer, is not. Enumerate consumers from query logs and lineage metadata where you have them, and from interviews where you do not.

The fourth prerequisite is a named domain owner with authority to approve cutover. Without that name, parity disputes have no resolver and the migration stalls at the first ambiguous mismatch.

The fifth is a measured baseline for the agentic AI consumer. If you do not know today's end-to-end latency from source event to queryable row, you cannot tell whether the new pipeline improved it, and you cannot write a freshness contract that means anything.

Step 1: Inventory legacy ETL outputs and rank them by blast radius

Build the migration backlog from the legacy ETL estate itself. Enumerate every table, view and materialized output the legacy tool produces, including intermediate tables that other jobs read even though no human consumer queries them directly. Intermediate outputs are the most common source of surprise breakage, because they are invisible in the BI layer but load-bearing in the pipeline graph.

For each output, record its consumers and classify what breaks if the output is wrong, late, or missing. A finance close report that is wrong is a different category of risk from a marketing dashboard that is late by an hour. Capture the failure mode, not just the consumer name: wrong values, late arrival, schema change, or disappearance.

Rank the backlog by blast radius — how many consumers are affected and how severe the consequence is — rather than by table size or by how easy the table looks. The ranking sets migration order, and the order has a purpose beyond risk management: the low-blast-radius tables are where you prove the pattern. Your first ported model should be one where a mistake costs an internal analyst an hour, not one where it costs a regulatory filing. Reserve the highest-blast-radius tables for the point where your reconciliation tooling and your rollback procedure have both been exercised at least once.

Two refinements are worth the time. First, group tables that share a source system and a transformation lineage, so that porting them together exercises the same extraction path and the same joins. Second, flag any table whose consumer is the agentic AI workload separately — those tables carry the freshness requirement and may need a different extraction mechanism than the rest, which is easier to plan once than to retrofit later.

Checkpoint: you have a ranked list in which every table names a consumer contact, a failure mode, and a target migration wave.

Step 2: Stand up the object-storage landing zone with idempotent, duplicate-free writes

The landing zone holds raw source extracts as immutable files. Partition by source system and extraction window, and record the extraction timestamp and the source watermark in the object path or in file metadata — either works, but pick one convention and enforce it, because the transformation layer will read it to determine what has already been processed.

The design decision that matters most is object naming. Name objects so that a retry of the same extraction window writes to the same key and overwrites rather than creating a second file. A deterministic path built from source system, table, and extraction window boundaries gives you that property. A path containing a random run identifier or a wall-clock timestamp does not, and you will discover the duplicates weeks later when a row count doubles.

Object versioning is your recovery mechanism, not your deduplication mechanism. These are different jobs and conflating them is the single most common landing-zone error. Versioning protects you when an extract is written incorrectly and you need the previous object back; it does not prevent two writes of the same logical record from both being read by the transformation layer. Deduplication is a property of the write pattern, and idempotent naming is how you get it.

Three versioning facts change your operating costs and behavior, and all three are documented by the providers. Versioning is disabled by default and must be explicitly enabled. Every version is billed as a full object, not as a diff — AWS states that if you have three versions of an object stored, you are charged for three objects. And a lifecycle rule for noncurrent versions is required if you want the permanent-delete behavior you had before enabling versioning. On Azure, confirm your account type before designing around blob versioning: Microsoft documents that storage accounts with a hierarchical namespace enabled for Azure Data Lake Storage are not currently supported for blob versioning. If your landing zone is an ADLS Gen2 account, plan recovery around soft delete and snapshots instead.

Checkpoint: deliberately fail a load mid-extraction, retry it, and confirm the transformation layer reads exactly one logical record per source row.

Step 3: Relocate transformation logic into the lakehouse, table by table

Port each legacy transformation into a lakehouse model in two passes. The first pass preserves the legacy logic's semantics exactly — same joins, same filters, same null handling, same aggregation grain — even where the original logic looks wrong or inefficient. The second pass optimizes, and it happens after parity is proven, not before. Combining the two passes is how migrations lose the ability to attribute a parity failure to a cause.

The table format buys you room to change your mind mid-migration. Iceberg schema updates are metadata changes, so adding, dropping, renaming or widening a column does not rewrite data files, and the format guarantees those changes are independent and free of side effects. Partition evolution works the same way: when you change a partition spec, old data written under the earlier spec remains unchanged, new data is written under the new spec, and both layouts coexist in one table with each partition version's metadata kept separately. For a migration, this means a partitioning decision you get wrong in month one does not force a second migration in month three — you evolve the spec and move on.

Materialization is a per-model decision, not a platform-wide setting. A view always reflects the latest source data and stores nothing extra, which suits low-volume models where freshness matters more than query cost. An incremental table persists results and processes only new or changed rows, which suits high-volume models where a full rebuild is expensive. Materialized views sit between the two, letting the platform manage refresh logic. The trade-off is real in both directions, and the right answer differs by model within the same pipeline.

Incremental strategies are not portable across engines. dbt's own documentation shows that supported strategies vary by adapter — merge is broadly available, while delete+insert and insert_overwrite are not supported everywhere, and microbatch support differs too. Confirm which strategies your transformation engine actually implements before you commit a model to one, and note that insert_overwrite operates on partitions rather than rows, so it does not use a unique key the way merge does.

Checkpoint: each ported model runs end-to-end against a full historical reload and against a single-day incremental run, and the two produce consistent results for the overlapping period.

Step 4: Run legacy and lakehouse in parallel and prove parity before cutover

Parity is a measurement, not an impression. Define the controls before you run the comparison, so that a passing result means something specific. Four controls cover most cases: row counts by partition, column-level aggregates or checksums on the measures each consumer reads, null and distinct counts on join and primary keys, and a business-level reconciliation of the headline metrics the consumer actually displays.

The fourth control is the one that catches the failures the first three miss. A pipeline can match on row counts and column sums while still producing a different number on the dashboard, because the dashboard applies its own filters, time zone conversions or currency handling. Reconcile the number the consumer sees.

Compare like with like. Parity must be measured on the same time window and the same source snapshot. If the legacy pipeline ran at 02:00 and the lakehouse pipeline ran at 04:00, the source data changed in between, and a mismatch may reflect the source rather than the transformation. The practical fix is to compare closed periods — completed days or completed hours — rather than the open current period, and to record the watermark at which each comparison was taken so that a later investigation can reconstruct what each pipeline had actually seen.

When a mismatch appears, investigate it. Do not average it away, do not exclude the partition, and do not adjust the tolerance until the number passes. A mismatch is information about a semantic difference between the two implementations, and it will resurface after cutover if you suppress it now.

Define the exit criterion in advance: a stated number of consecutive closed periods with zero unexplained variance, signed off by the consumer contact named in Step 1. The number is yours to choose based on the reporting cadence — a daily report needs enough days to cover weekly seasonality, and a monthly close needs at least one full cycle.

Checkpoint: the defined number of consecutive periods has passed with zero unexplained variance, and the consumer owner has signed off in writing.

Step 5: Express "near real-time" as a testable freshness contract for the agentic AI consumer

"Near real-time" is not a specification until someone writes a number and a unit next to it. The W3C's Data on the Web Best Practices describes real-time as a range from milliseconds to a few seconds after data creation, which is a useful anchor for calibrating expectations but not a requirement your agent must meet. Your agent's requirement comes from what it does: an agent answering a customer's question about their own order needs seconds; an agent summarizing yesterday's pipeline health does not.

Write the contract as a file, not as a sentence in a design document. The Open Data Contract Standard defines SLA properties that map directly onto this problem: a latency property with a value and unit, a frequency property describing how often data is refreshed, and an element identifying which timestamp column the SLA is measured against. The standard also supports a slaDefaultElement so that properties without an explicit element inherit a default. Expressing freshness this way makes it machine-readable, versionable, and testable.

Testing the contract requires measuring the gap between source event time and lakehouse commit time at each stage of the pipeline, not just at the end. Stage-level measurement tells you where latency accumulates: extraction, landing, transformation, or commit. Alert when the gap exceeds the contracted value, and publish the measured freshness alongside the data so the agent can decide whether to answer or defer. An agent that knows its data is four hours old can qualify its answer; an agent that assumes freshness cannot.

If the contract requires sub-minute latency, batch extraction will not deliver it. Log-based change data capture reads the database transaction log — the write-ahead log in PostgreSQL, the binary log in MySQL, redo logs in Oracle — and propagates changes as they occur, with minimal impact on the source database. That is the mechanism that makes sub-minute freshness achievable, and it is a different extraction design from the file-based batch path. Decide which tables need it based on the contract, not on principle.

Checkpoint: the contract exists as a file under version control, a monitor enforces it, and a deliberate pipeline pause triggers the alert within the expected window.

Step 6: Cut over consumers, then decommission legacy transformation logic

Cut over one consumer at a time, behind a switch you can flip back. The switch may be a view definition, a connection string, a semantic-layer mapping or a feature flag — the mechanism matters less than the property that reversing it takes minutes and requires no data movement.

After each redirect, keep the legacy pipeline running and writing to its own output for a defined soak period. The soak period is not a formality: it is the window in which you find out whether the new pipeline behaves correctly under real query load, real late-arriving data and real operational incidents. During the soak, the legacy output remains a valid rollback target, which means a bad cutover is reversed by flipping the switch back rather than by restoring from backup.

Separately, the lakehouse table format gives you a second rollback path at the data layer. Iceberg documents version rollback as a way to correct problems by resetting tables to a good state, and time travel lets you query a table at a specific snapshot. So a bad transformation is corrected by resetting the table to a known-good snapshot, while a bad cutover is corrected by pointing the consumer back at the legacy output. Keep these two paths distinct in your runbook; confusing them during an incident wastes the minutes you do not have.

Define decommission criteria explicitly before you stop anything: parity held through the full soak period, no consumer reading the legacy output (verified from logs, not from memory), and a rollback rehearsed in a non-production environment. Only when all three hold does the legacy job get switched off — and even then, keep its output readable for a defined retention window in case an audit or a forgotten consumer surfaces.

Checkpoint: a rollback has been rehearsed outside production, and the measured time to restore service is recorded in the runbook.

Common mistakes that break reporting or freshness during the shift

Each of these failure modes is prevented by a step above; they are listed here with their symptoms so you can recognize them when they appear.

Versioning enabled without a noncurrent-version lifecycle rule. Symptom: storage cost climbs steadily with no growth in source data. Correction: add the noncurrent expiration configuration in the same change as versioning, and verify it deletes as expected rather than assuming it does.

Versioning treated as deduplication. Symptom: row counts in the lakehouse exceed source counts, often by a multiple that matches your retry rate. Correction: fix object naming so retries overwrite, and treat versioning purely as recovery.

Porting and improving in the same change. Symptom: a parity mismatch you cannot attribute, because two variables changed at once. Correction: revert the optimization, prove parity on the faithful port, then optimize as a separate change with its own verification.

Comparing open periods across pipelines on different schedules. Symptom: intermittent mismatches that resolve themselves on the next run. Correction: compare closed periods only, and record the watermark for each comparison.

Declaring near real-time without a measured baseline. Symptom: the agent's latency requirement turns out to be unachievable with the extraction method chosen, discovered after cutover. Correction: measure current latency first, write the contract from the requirement, and select the extraction mechanism — batch or log-based CDC — to match it.

Decommissioning on cutover day. Symptom: an incident in week two with no rollback target. Correction: hold the legacy job through the soak period and stop it only against the three decommission criteria.

Verify your migration: acceptance checklist

Run this against your own migration. Each item is a yes or no; a no is a blocker for the workstream it belongs to.

Landing zone

  • Object versioning is explicitly enabled on the landing bucket or container.

  • A lifecycle rule for noncurrent versions is configured and verified to delete.

  • Object naming is deterministic per extraction window, so a retry overwrites.

  • A deliberate failure-and-retry test produced exactly one logical record per source row.

  • Account type confirmed where the provider restricts versioning (for example, ADLS Gen2 with hierarchical namespace).

Transformation

  • Every legacy ETL output has an identified lakehouse counterpart, including intermediate tables.

  • Each ported model preserves legacy semantics, with optimizations applied as separate changes.

  • Schema evolution has been exercised on at least one table without a data rewrite.

  • Partition evolution has been tested, and old and new layouts coexist in one table.

  • The incremental strategy for each model is confirmed as supported by your engine.

Parity

  • Reconciliation controls are defined: partition row counts, measure aggregates, key null and distinct counts, business-level metric.

  • Comparisons use closed periods and record the watermark.

  • The required number of consecutive clean periods has completed.

  • Consumer owner sign-off is recorded in writing.

Freshness

  • The freshness contract exists as a versioned file with latency, frequency and element defined.

  • A monitor measures source-event-to-commit latency at each pipeline stage.

  • The alert fires on a deliberate pipeline pause.

  • Measured freshness is published to the agentic AI consumer.

Decommission

  • Rollback has been rehearsed in a non-production environment, with time-to-restore recorded.

  • No consumer reads the legacy output, verified from logs.

  • The legacy job is stopped only after the soak period and the three criteria are met.

FAQ

Can we migrate incrementally, or do we need a big-bang cutover? Incrementally, and you should. The parallel-run design in Steps 4 through 6 exists precisely so that consumers move one at a time while both pipelines stay live. A big-bang cutover removes your rollback target at the moment you are most likely to need it.

How long should a parallel run last? Long enough to cover the seasonality of the reports it feeds — at minimum one full reporting cycle, and longer for anything tied to a monthly or quarterly close. Set the number of consecutive clean periods in advance rather than deciding after you see the results. Step 4 covers the exit criterion.

Can the lakehouse serve the agentic AI consumer directly, or do we need a serving layer? It depends on the query pattern and the latency in your freshness contract. A lakehouse table read directly satisfies many analytical agent workloads; a low-latency point-lookup pattern may need a serving layer in front. Decide from the measured contract in Step 5 rather than from a general preference.

What if parity fails on a table with no clear owner? Escalate to the domain owner named in the prerequisites, and treat the absence of an owner as the finding. A table with no consumer contact is either unused — in which case document that and exclude it from the migration — or used by someone who has not been identified, which is a risk to resolve before cutover, not after.

Is object versioning alone sufficient for audit and recovery? No. Versioning protects individual objects against overwrite and deletion, which covers recovery from bad writes. It does not provide deduplication, it does not give you a queryable history of table state, and it does not by itself satisfy retention requirements that specify immutability over a defined period. Pair it with the table format's snapshot history for data-level audit, and with an object-lock or retention policy where a compliance requirement demands immutable storage.