Zero-Copy Warehouse Modernization: Moving to Apache Iceberg Without Downtime

Cross-posted. This article’s canonical home is Alex Merced’s Lakehouse Blog.

Warehouse migrations have a reputation, and the reputation is earned. The classic project copies everything: export the tables, rebuild the schemas, port the pipelines, recreate the reports, run both systems in parallel until trust transfers, then cut over. Industry analyses of these projects find the same pattern year after year: significant delays in a large share of them, parallel-run periods that stretch from months into years, and organizations paying two full infrastructure bills long past the date the business case promised one. The migration becomes a residency.

The failure is not in the destination. Open lakehouse architectures on Apache Iceberg deliver what they promise. The failure is in the verb. Copying an estate means recreating every dependency the estate accumulated over a decade, all at once, under a project deadline, while the business keeps changing the source. There is a different verb available now, and this article is about it: connect first, copy last, and only copy what earns it.

Zero-copy modernization inverts the classic project. Instead of moving data to the new platform and then connecting consumers, it connects the new platform to data where it lives, delivers value through federation, lands new data in open formats from day one, and materializes history into Iceberg table by table, each move validated while the legacy system keeps serving as the safety net. Downtime is not minimized. It is designed out, because at no point does a consumer lose a working path to the data.

A disclosure before the details: I work at Dremio, whose platform includes zero-ETL federation across databases and warehouses, so I have watched many of these migrations up close and I have an employer with a product in this story. The pattern itself is architectural, executable with multiple vendors’ tools or with open source components, and I keep the article at the pattern level throughout.

Why Copy-First Migrations Fail

Understanding the failure mechanics of the classic approach is worth ten minutes, because every zero-copy design decision exists to break one of these mechanisms.

The first mechanism is the dependency iceberg, no pun available to avoid. A warehouse that served a company for a decade is not a set of tables. It is tables plus the views over them, the stored procedures nobody fully understands, the scheduled jobs, the BI reports with hand-tuned SQL, the spreadsheet exports finance runs quarterly, the service accounts with undocumented consumers, and the tribal knowledge of which numbers to trust. Copying the tables is the easy tenth of the project. The other nine tenths is rediscovering and re-implementing dependencies, which happens the hard way: something breaks downstream, and the team learns another dependency existed.

The second mechanism is the moving source. Migrations take quarters, businesses do not pause, and the source estate keeps growing new tables, new columns, and new logic while the copy proceeds. The project chases a target that moves, reconciliation becomes permanent, and the gap between systems never quite closes, which is why parallel runs stretch. Every month of migration adds migration.

The third mechanism is the trust cliff. In a copy-first project, consumers switch from a system they trust to a system they do not in one motion per workload, and rational consumers resist. Report owners delay validation, finance wants one more quarter of parallel numbers, and the cutover date slips for reasons that are organizational and completely legitimate: trust transfers through accumulated experience, and the project structure provides no way to accumulate it gradually.

The fourth mechanism is the double bill, the compounding result of the first three. The business case assumed a parallel period measured in weeks, the mechanisms above stretch it to quarters or years, and the organization pays full price for both estates the whole time, which erodes the savings that justified the project and, worse, executive patience for finishing it. Stalled migrations do not fail loudly. They persist expensively.

Read the four mechanisms together and the design requirements for a better approach write themselves: deliver value before any data moves, so the project earns patience instead of spending it. Stop the source from growing, so the target chases nothing. Move trust gradually, workload by workload, with the legacy system as fallback rather than as parallel twin. And make every step independently valuable, so a pause at any point leaves the organization better off than it started, not stranded mid-copy.

What Zero-Copy Means, Precisely

The phrase needs precision because it names a principle, not an absolute. Data that must live permanently in Iceberg does eventually get written there once. Zero-copy means everything else: no copies made for access, no copies made for evaluation, no copies made to keep two systems synchronized as a way of life, and no bulk copy of the estate as the project’s opening move. Three capabilities, all mature in the current ecosystem, make the principle practical.

Federation is the first: query engines that execute SQL against operational databases, legacy warehouses, and lakehouse tables in one statement, joining across them with pushdown into each source, so consumers get unified access while the data stays where it is. Federation is how the new platform delivers value in week one, before a single byte migrates, and it is how the long tail of rarely-touched data gets served forever without earning a migration at all. If the virtualization idea is new to you, this walkthrough of data virtualization and the semantic layer covers the mechanics in more depth.

External table interoperability is the second, and the last two years transformed it. The major warehouses now read and write Iceberg tables through open catalog interfaces, and lakehouse engines query warehouse-managed tables through the same seams. A dataset converted to Iceberg once serves both worlds during the entire transition: legacy reports read it through the warehouse’s external table support while new workloads read it natively. That bidirectional window is what turns cutover from an event into a gradient.

Change data capture is the third, for the tables that operational systems keep changing. CDC streams replicate source changes into Iceberg continuously, holding the Iceberg copy seconds behind the source, which converts data synchronization from a batch chase into a solved streaming problem and makes the eventual writer cutover a minutes-long traffic switch rather than a weekend. The apply patterns that hold up under continuous upserts are covered in detail in these Iceberg CDC pipeline notes.

A scope note, because the title says legacy databases and estates hold more than warehouses. The same pattern covers the analytical burden that operational databases carry against their will: the reporting replicas, the read-only copies of PostgreSQL and SQL Server that exist because analysts needed access and nobody wanted them touching production. Those replicas get the identical treatment, federate first, CDC-mirror the hot tables into Iceberg, retire the infrastructure, and the operational databases lose their analytical traffic entirely, which often pays for the early quarters by itself. What stays out of scope is the operational workload proper: transactional systems remain transactional systems, and this pattern moves their analytical shadow, not their day job.

One clarification prevents a common misreading: zero-copy does not mean the lakehouse queries the legacy warehouse forever. Serving analytics through federation into an expensive legacy engine indefinitely just relocates the bill. Federation is the bridge and the long-tail servant, while the center of gravity moves to Iceberg, because open tables on object storage are where the economics and the multi-engine freedom live. The discipline is in what earns the move and when.

The Target, Briefly

The destination architecture deserves one section of grounding, compressed because I cover it at length elsewhere. The estate lands on Apache Iceberg tables over Parquet on object storage, coordinated through a catalog speaking the Iceberg REST protocol, such as Apache Polaris, with credential vending carrying security so every engine, present and future, enforces the same policies from the same place. Compute becomes a portfolio: a warehouse-scale SQL engine with a semantic layer for BI, batch and streaming engines for pipelines, embedded and Python-native tools for the light work. Two properties matter here: any engine reads the tables, which makes gradual consumer movement possible, and the catalog is the single governance point, which lets legacy and modern access coexist without policy drift.

The semantic layer earns a specific mention because it is the migration’s secret weapon for the trust problem. Metric definitions, governed views, and business models over the new estate give BI consumers a surface that answers identically to what they know, and moving a dashboard becomes repointing it at a semantic model rather than rewriting its SQL. Where legacy views encode years of business logic, the semantic layer is where that logic gets re-homed once, reviewed, and version controlled, instead of per consumer. It is also where the swap point lives, which the next phase makes concrete.

The Strategy: Strangle, Never Transplant

The overall shape follows the strangler fig pattern from application modernization: the new system grows around the old one, takes over responsibilities incrementally, and the old system shrinks until switching it off is an anticlimax. Four phases, each independently valuable, each reversible, none requiring downtime.

Phase one connects: the modern platform federates the legacy warehouse and the operational sources, and consumers get value through unified access with zero data movement. Phase two redirects the inflow: new data lands in Iceberg first, so the legacy estate stops growing and the target stops moving. Phase three materializes: historical tables move in priority order, each backfilled, synchronized where still changing, validated, and served to both worlds through the interoperability window. Phase four retires: consumers shift workload by workload, writers cut over where CDC has held the systems in lockstep, and the legacy system winds down from landlord to tenant to gone.

Phases overlap, different tables sit in different phases at once, and the sequence per table is invariant. What never happens is the big bang: no moment where everything switches, no weekend of held breath, no single point where rollback means restore.

Phase One: Federate First, Move Nothing

The opening move is connection. The modern engine gets read access to the legacy warehouse and the key operational databases, catalogs their schemas, and serves queries that join across all of them. For consumers the pitch needs no migration vocabulary: one place to query everything, including joins that were impossible when the customer table lived in the warehouse, events lived in the lake, and reference data lived in PostgreSQL.

The value this phase delivers is real on its own terms, and its migration function is reconnaissance. Federated query logs become the dependency map the copy-first project never had: which tables get queried, by whom, how often, joined against what, with what latency expectations. That map, accumulated passively over weeks, replaces the interview-and-archaeology dependency discovery that classic migrations do badly under deadline, and it drives every prioritization decision in phase three. The tables nobody queried in ninety days, routinely a third or more of a legacy estate, just identified themselves as candidates for archival rather than migration, which is the cheapest migration outcome there is.

Phase one also quietly solves the migration’s naming problem, which sounds trivial and is not. Consumers meet the new platform as the place where queries got easier, not as the thing replacing what they trust, so the program’s internal brand forms around addition rather than substitution. Teams that skip federation and open with “we are migrating your tables” spend the rest of the program negotiating with the resistance that framing created. The technical sequencing and the change-management sequencing turn out to be the same sequencing.

Two disciplines keep phase one honest. Pushdown must be real, with filters, projections, and aggregations reaching the sources so federated queries impose bounded load on systems still doing their day jobs, and workload management capping what federation demands of the legacy engine. And federation must route through the new governance from the start, so the security model consumers eventually live under is the one they start under and the audit trail begins on day one.

The construction work inside phase one is the semantic layer, and it is where consumers get names that survive the move. Build it in three tiers so the swap point stays separate from the logic. The staging tier is one-to-one with the physical tables, staging.sales.fact_orders selecting from the legacy table with column renames and type casts applied, and its only job is to give every physical table exactly one logical alias. The conformed tier carries the legacy views’ joins, filters, and business rules on top of staging, working in standard names and standard types so it does not care whether the source underneath is a legacy engine or Iceberg. The gold tier is what dashboards query, deliberately thin, because a thin gold layer lets you change conformed logic without changing the name consumers use.

Port the legacy views into the conformed tier in dependency order, those built only on base tables first, and diff each ported view against the original it replaces while the original data is still in place, which is cheap here and expensive after a data move. Then translate access control: export the legacy grants and map them onto the new namespaces, granting at the gold tier where consumers touch the data, and re-implement row-level security as row filters and column masking as column masks on the gold views, so the policies travel with the view through every later phase.

One consumer-facing detail decides whether this phase lands quietly: name compatibility. Keep the legacy qualified names available as aliases over the new logical names, so a consumer’s SQL does not change and only its connection does, and retire the aliases after the migration rather than during it.

The cohort plan belongs to phase four, but its groundwork lands here: the semantic layer already answers to the names consumers know, and the federation logs record which consumer queries which table.

Phase Two: Redirect the Inflow

The highest-consequence early decision is where new data lands, because it decides whether the target chases a moving source forever. Phase two establishes the rule: new pipelines write Iceberg, full stop. New event streams commit to Iceberg tables through streaming writers. New batch feeds land through right-sized ingestion, serverless patterns for the small ones. New vendor deliveries arrive in the landing zone and materialize into Iceberg. The legacy warehouse receives new data only where a legacy consumer strictly needs it there, and each case gets an owner and a review date.

The mechanism that makes this painless is the interoperability window running in reverse: the legacy warehouse reads the new Iceberg tables as external tables through its open catalog integration, so legacy reports needing new data get it without the data living in the warehouse. New data serves both worlds from the open side, which is exactly the center-of-gravity shift, applied at the estate’s leading edge where it costs nothing.

The compounding effect is easy to underestimate. Every month it runs, the legacy estate becomes more purely historical: a fixed, shrinking, increasingly cold body of tables, which is the easiest possible thing to migrate. The moving-source mechanism from the failure analysis is not mitigated. It is switched off.

Phase Three: Materialize What Earns It

With the inflow redirected and the dependency map accumulating, history moves, and the discipline is that tables migrate in earned order rather than schema order. The queue forms from the federation logs: hot tables serving expensive or frequent workloads first, because they buy the most performance and retire the most legacy load. Warm tables follow on schedule. Cold tables wait indefinitely, served through federation at near-zero cost, and the coldest graduate to archive. A large fraction of the estate never migrates at all, which is not a compromise. It is the pattern working: migration effort concentrates where migration returns.

The disposition framework deserves writing down, because it keeps the program governed. Every table in the legacy estate gets exactly one disposition, recorded with an owner and a review date:

Disposition	Criteria	Destination
Migrate hot	Frequent queries, expensive workloads, active writers	Native Iceberg, prioritized
Migrate warm	Regular but modest use	Native Iceberg, scheduled
Federate indefinitely	Rare queries, no performance pressure	Served in place through federation
Archive	No queries in the lookback window	Exported to cold storage, then dropped
Retire	Duplicates, abandoned experiments, superseded copies	Dropped after owner confirmation

The proportions surprise teams the first time. Estates that audit honestly find the migrate tiers cover a minority of tables carrying a large majority of query traffic, while archive and retire claim a third or more of the count. The list converts that surprise into savings, because the project’s biggest cost avoidance is the copying it never does, and it gives the program a progress metric that resists gaming: dispositions resolved, not tables copied.

Per table, the materialization sequence has three shapes depending on the table’s life.

Static history, tables no longer written, moves with a one-time backfill: read from the source, write to Iceberg with deliberate choices about partitioning and sorting, because migration is the one free opportunity to fix the layout decisions the legacy system imposed. Validate, publish, done.

Append-only tables in active use get a backfill plus a redirected writer: history backfills while the pipeline’s output moves to Iceberg, with a watermark handoff so no rows fall between the systems.

Actively mutating tables, the operational mirrors and slowly changing dimensions, get the full treatment: backfill a consistent snapshot, then CDC holds the Iceberg table synchronized with the source, applying inserts, updates, and deletes continuously through MERGE-based apply jobs. On current Iceberg versions this is far cheaper than it was two years ago, with v3 deletion vectors keeping the merge-on-read bookkeeping compact under continuous upserts. The table then serves both worlds, warehouse consumers through external reads, modern consumers natively, while remaining seconds-fresh for as long as the transition needs. There is no deadline pressure on any consumer, because the synchronized state is stable, cheap, and boring.

Backfill mechanics deserve their own paragraph of craft, because the bulk load is where careless projects hurt their live systems. The backfill reads from the source in bounded, keyed ranges, partition by partition or key range by key range, throttled to a load budget the source’s operators agreed to, scheduled into the source’s quiet hours where they exist. Each range lands in Iceberg as its own commit, which makes the backfill resumable at range granularity: a failure at range four hundred restarts at range four hundred, not at zero. For CDC-destined tables, the rule is capture-before-copy: start the change stream first, buffering, then snapshot, then apply the buffered changes with the idempotent MERGE, and the timestamp guard resolves the overlap between snapshot contents and buffered changes without ceremony. Run the first backfill against the least important table on the list, time it, and let the measured rate size the schedule for the rest, because measured estimates survive contact with the source’s real throughput.

This is also the phase where layout and modeling debts get paid deliberately. The legacy schema’s quirks, the partitioning that made sense on that engine, the types chosen around old limitations, get corrected in the Iceberg version, with the mapping documented and the semantic layer absorbing the renames so consumers see continuity while the physical tables improve underneath.

The CDC apply job is the workhorse of this phase, so here is its core shape, the same MERGE pattern whether run on Spark, a warehouse-scale engine, or a streaming framework’s SQL layer:

MERGE INTO lake.core.customers t
USING (
    SELECT * EXCLUDE (rn) FROM (
        SELECT *, row_number() OVER (
            PARTITION BY customer_id
            ORDER BY source_commit_ts DESC, source_lsn DESC
        ) AS rn
        FROM staged_cdc_batch
    ) WHERE rn = 1
) s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.source_commit_ts > t.source_commit_ts THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT *

Three details in that statement carry the correctness. The inner deduplication collapses each batch to the latest change per key, ordered by the source’s commit timestamp with the log sequence number breaking ties, which is what defends against out-of-order delivery inside a batch. The timestamp guard on the update clause makes the apply idempotent and safe against replays, since stale changes lose to what already landed. And carrying the source ordering columns on the target table, a small storage tax, is what makes both defenses possible and makes reconciliation queries later trivially precise. Every CDC pipeline that skips these details works in the demo and pays during the first replay.

Stored Procedures, Scheduled Transformations, and the Write Side

Everything so far is about reads, the large majority of consumers and the reason the phases are shaped around them. The write side is smaller and harder, and it needs its own plan rather than an assumption that ingestion will follow the readers. A legacy warehouse has three kinds of writers, each with a different exit.

Ingestion pipelines load source data into base tables and repoint at each table’s writer cutover in phase four. The check that belongs in phase one is tooling: the pipeline’s framework has to write Iceberg, which today means Spark, Flink, PyIceberg, DuckDB, or a commercial tool with an Iceberg sink. Inventory pipelines by tooling early, because one built on a tool with no Iceberg sink is a rewrite, and rewrites belong on the schedule, not in the week of the swap.

Transformation jobs are where stored procedures live, and stored procedures do not port. A four-hundred-line procedure with cursors, temp tables, and conditional branching has no equivalent in an Iceberg lakehouse, and pretending otherwise is how migrations stall in their final quarter. Three options are realistic. Rewrite it as a set of SQL statements orchestrated by a scheduler such as dbt, Airflow, or Dagster, which is the common answer and is a rewrite. Rewrite it as a Spark or Python job when the logic is procedural rather than relational, which is also a rewrite. Or, for procedures whose only job is to produce a derived table, replace the procedure with a governed view, or a materialized acceleration over that view, and delete the job entirely.

The third option is more common than teams expect. A large fraction of nightly procedures exist to materialize a join a modern engine evaluates on demand with acceleration, and the audit question for each is whether it still earns a materialized artifact or exists because the old engine could not do otherwise. Answer that before rewriting anything, because the rewrite budget shrinks every time the answer is “this only built a table.”

Sequence the transformation work by dependency, the same rule as the table work. A derived table written by a procedure and read by dashboards swaps like any other table, except its ingestion path is the rewritten job: inputs already on Iceberg or reachable through the staging views, first run writes to Iceberg, and the old procedure keeps running in parallel for a fixed window, three cycles for a nightly job, with outputs diffed before the swap. A parallel run with no end date is a double-ETL by another name.

Direct application writes are the rarest and the messiest. An application inserting into a staging table through a database driver needs either an Iceberg-capable write path or an intermediary, and the cleanest intermediary is object storage: the application writes files to a landing prefix, and a serverless function commits them to Iceberg, which turns a direct write into an ingestion pipeline the migration already handles. Name the write path for every table in phase one, because the runbook’s repoint step cannot be written for a table whose writer nobody has identified.

Phase Four: Cutover as a Gradient

The final phase is where the classic project concentrates its risk and this pattern dissolves it, because cutover happens per consumer and per writer, never per estate.

Read cutover moves consumers workload by workload, and the semantic layer does the heavy lifting: dashboards repoint to governed models whose numbers have reconciled against legacy outputs for weeks, analysts move when their working tables are native and faster, and federation keeps every unmigrated dependency reachable, so no consumer faces a gap. Trust transfers the only way it ever does, through accumulated correct answers, at each consumer’s pace inside a project timeline instead of against it.

The mechanics repeat per cohort. Order them by risk: internal exploratory dashboards first, then scheduled batch reports, then executive dashboards, then regulatory and external outputs. Test each BI tool’s generated SQL against the new engine before its cohort moves, because date functions, string concatenation, and TOP versus LIMIT are the usual breakages, and they surface as errors rather than quietly wrong numbers. When a cohort finishes its reconciliation period, revoke its direct credentials to the legacy warehouse, so the engine’s service account becomes the last direct reader and any remaining connection is visible as a blocker rather than a surprise at decommissioning.

The BI estate gets its own sub-plan inside read cutover, because dashboards are where the trust cliff historically lived. The sequence that works: inventory the reports from the legacy side’s query logs, rank by audience and refresh cost, and move them in cohorts, each running a reconciliation period where the semantic layer’s numbers publish alongside the legacy numbers before the switch. The logic buried in legacy views gets ported once, reviewed by the metric’s owner, with the legacy definition retired rather than left as a shadow source of truth. Report consumers experience the move as an address change with receipts attached, and the loudest historical objection to warehouse migrations, “the numbers changed,” gets answered before it is raised.

Writer cutover is the sharper moment per table, and CDC synchronization makes it small: the source writer pauses or drains, the apply job confirms lag at zero, writes redirect to the Iceberg table, and any remaining consumers of the legacy copy flip to reading Iceberg externally. Minutes per table, rehearsed on unimportant tables first, with rollback being the mirror procedure while the window stays open. Tables cut over in dependency order, leaves first, and the estate crosses the halfway point without any single day feeling different.

Decommissioning closes the loop, and it deserves a checklist rather than an assumption, because stalling here is how organizations pay the double bill after winning the migration. The exit test per legacy component: zero queries in the federation and audit logs for an agreed period, dependencies confirmed re-homed, data confirmed in Iceberg or archived, then access revoked before deletion, because revocation is reversible and finds the consumer the logs missed. The final state is a read-only archive on minimal capacity, then an export, then a closed account, on a date someone owns from phase one.

A Swap Runbook for One Table

The swap is the moment of truth per table, and the goal is for it to be boring. The runbook below is for an append-only fact table with a nightly load, the common case, and it adapts to other shapes by changing the delta step. Work through it in order, and automate each step as a SQL statement or an API call, because the runbook is only repeatable if a script can run it.

Confirm the background copy caught up to the last completed load: compare row counts and max load timestamps between Iceberg and the legacy table.
Confirm the parity checks pass against a temporary view that points at the Iceberg table, so a type or logic problem surfaces before the real swap rather than after it.
Wait for tonight’s load to finish on the legacy table, then copy the final delta, the rows with a load timestamp above the Iceberg table’s maximum, which is small and takes minutes.
Repoint the ingestion pipeline to write Iceberg. Deploy the configuration change without triggering a run.
Redefine the staging view to select from the Iceberg table. One statement, no consumer visible.
Refresh every acceleration structure that depends on the gold views over this table, then run the parity checks against the swapped view: row counts, aggregates, a checksum over a sampled query.
Revoke write access on the legacy table and rename it with a frozen suffix where the warehouse allows renames without breaking the engine’s connector metadata.
Record the swap date, schedule the full-snapshot diff for one week out, and schedule the drop for the end of the retention period.

The swap itself is a view redefinition, and this is the whole of it:

-- Before: the staging view reads the legacy warehouse.
CREATE OR REPLACE VIEW staging.sales.fact_orders AS
SELECT
  ORDER_ID AS order_id, CUSTOMER_KEY AS customer_key,
  ORDER_DT AS order_date, CAST(ORDER_AMT AS DECIMAL(18,4)) AS order_amount,
  STATUS_CD AS status_code, LOAD_TS AS load_timestamp
FROM legacy_edw.SALES.FACT_ORDERS;

-- After: the same view, the same columns and types, reading Iceberg.
CREATE OR REPLACE VIEW staging.sales.fact_orders AS
SELECT order_id, customer_key, order_date, order_amount,
       status_code, load_timestamp
FROM lakehouse.sales.fact_orders;

The conformed and gold views above it do not change, and acceleration structures refresh from the new source on their next cycle. Consumers see the same names, columns, and types, and after the refresh the same or better performance. The renaming and casting the staging view did against the legacy source now lives in the Iceberg table’s schema, because the copy wrote standard names and types, which is why the second statement is shorter.

Elapsed time is typically under an hour, most of it waiting on the refresh. No consumer is notified because none is affected, and the next night’s load lands in Iceberg while the legacy table never receives another row. Batch the swaps once the runbook is automated: ten tables a night is a reasonable pace for a team of two, so an estate with twelve hundred tables worth migrating is four months of low-risk background work, not a critical-path cutover. For mutable tables, keep the same order, add CDC catch-up before the view swap, and use a short freeze as the fallback when the source offers no change capture.

Validation: The Discipline That Buys the Trust

Every phase above leans on one currency, demonstrated correctness, so validation is not a supporting detail. It is the migration’s engine, and it has to beat the row counts classic projects lean on, because two tables agree on row counts while disagreeing on the money.

The working standard is layered reconciliation, automated and continuous rather than run once at handoff. Structural checks confirm schema parity through the mapping document, types, nullability, and the deliberate differences. Volume checks compare row counts per partition, which localizes discrepancies instead of merely detecting them. Content checks compare aggregates that reflect business meaning, sums of measures, distinct counts of keys, min and max of dates, chosen with the table’s owners so the checks encode what correct means for that table. Sampled row-level checks hash full rows across systems for a moving sample, catching the subtleties aggregates smooth over.

The reconciliation queries themselves run cheaply through the same federation that serves users, comparing live systems in one statement:

SELECT
    COALESCE(l.order_month, w.order_month) AS order_month,
    l.row_ct AS lake_rows,
    w.row_ct AS wh_rows,
    l.total_amount AS lake_amount,
    w.total_amount AS wh_amount
FROM (
    SELECT DATE_TRUNC('month', order_ts) AS order_month,
           COUNT(*) AS row_ct, SUM(amount) AS total_amount
    FROM lake.sales.orders
    GROUP BY 1
) l
FULL OUTER JOIN (
    SELECT DATE_TRUNC('month', order_ts) AS order_month,
           COUNT(*) AS row_ct, SUM(amount) AS total_amount
    FROM legacy_wh.sales.orders
    GROUP BY 1
) w ON l.order_month = w.order_month
WHERE l.row_ct IS DISTINCT FROM w.row_ct
   OR l.total_amount IS DISTINCT FROM w.total_amount

An empty result is the daily heartbeat of a synchronized table, a non-empty result names the partition to investigate, and the query’s history, published where consumers see it, is the trust ledger that makes read cutovers a formality. For CDC-synchronized tables the checks run with a lag allowance, and lag is a monitored metric with an alert threshold, because a quietly stalled apply job is this pattern’s most dangerous silent failure. Access parity belongs in the same routine: run a sample of each role’s queries through the new surface and confirm the same rows return and the same queries are rejected, because a row filter inverted by accident is a data breach with a delay fuse.

Iceberg’s snapshot model sharpens every check: reconciliations run against a named snapshot rather than a moving table, so an investigation examines the exact state that failed, and the trust ledger cites immutable versions instead of timestamps that no longer resolve.

The organizational half matters equally: sign-off is per table and belongs to the data’s owner, not the migration team, with the checks and their history as evidence. Distributed sign-off is slower per table and faster per estate, converting the trust cliff into a hundred small steps each owner takes on their own data.

A Worked Example: A Two-Warehouse Estate Unwinds

Here is a composite drawn from migrations I have watched succeed, with no invented benchmark numbers, because the sequencing and decision points are the transferable parts.

The starting estate is messier than the diagrams, which is the realistic part: a legacy on-premise warehouse carrying a decade of finance and operations reporting, a cloud warehouse adopted five years ago that was supposed to replace it and instead became a second estate, operational PostgreSQL and SQL Server databases feeding both through aging ETL, and an AI initiative neither warehouse serves well. Two platform bills, three copies of the customer table, and a steering committee that already survived one stalled migration, which is why “big bang” is banned in the kickoff.

Phase one connects all of it in six weeks. The modern platform federates both warehouses and the operational databases, the semantic layer goes up with the first governed metrics, and the first delivered value is a cross-estate join analysts had wanted for years: cloud warehouse events against on-premise finance dimensions, one query, no pipeline. Phase two moves the dozen inbound feeds onto Iceberg-first landing that same quarter, with one steering metric: new tables created in the legacy estates, trending to zero. The federation logs accumulate, and by week ten the dependency map exists as data rather than interviews. Its first finding: over a third of the legacy warehouse’s tables saw no query in the observation window, so the disposition list opens with the archive tier already populated.

Phase three runs as a standing workstream for the following year, never as a crunch. The hot tier, under two hundred tables carrying most of the query traffic, materializes first: static history backfilled with improved partitioning, the operational mirrors under CDC with the MERGE apply pattern, the reconciliation heartbeats publishing daily. The finance tables get the full ceremony, aggregate checks designed with the controllers, a parallel quarter close, and sign-off from the finance data owner rather than the platform team, which makes the eventual dashboard moves administrative. The warm tier follows on schedule, the cold tier stays federated, and each quarterly review archives a few more tables as their last consumers confirm. The stored procedures follow their dependencies: the sixty that only materialized a join disappear into views, the dozen encoding real business logic get rewritten and parallel-run for three cycles each.

Phase four overlaps the back half of phase three, cohort by cohort. BI moves in waves with published reconciliation ledgers, the analysts having largely moved themselves once the hot tables were native and faster. Writer cutovers run as rehearsed twenty-minute changes, leaves first, the orders mirror last. The on-premise warehouse reaches its exit checklist first, then a read-only archive for a retention period, then an export to cold storage, then powered off, a date that had an owner since month one. The cloud warehouse survives, smaller, as one engine in the portfolio, attached to the same catalog as everything else, and its renewal negotiation happens with the estate’s gravity elsewhere, which changes the numbers on both sides of the table.

The retrospective names the decisions that mattered most, and none are tools: the disposition list with dates, the refusal to copy the unqueried third, validation as standing infrastructure with distributed sign-off, and the exit check that revoked access before deleting, which caught two forgotten consumers gently. The AI initiative never waited either: it read governed tables through the catalog from phase two onward, which is the quiet proof that value delivery and modernization run concurrently when connection precedes copying.

Failure Modes and Their Defenses

The forever bridge. Federation works so well that migration stops, and analytics runs through the legacy engine’s compute indefinitely, paying its rates with extra steps. The defense is the phase-two rule without exceptions plus a disposition list with dates: everything is migrating, federated by decision, or archiving, and “still deciding” expires.

Consumers that keep their direct credentials. A team with a direct connection to the legacy warehouse keeps using it, and their job breaks the day the table is frozen, with the tell being a query in the legacy log from a principal that should be on the modern path. The defense is revoking direct access per cohort as it completes cutover, with the engine’s service account the only remaining reader, and treating every surviving connection as a blocker.

Ported views that depend on legacy-only functions. A view uses a warehouse-specific construct, a QUALIFY, a CONNECT BY, a dynamic PIVOT, that the federation layer pushes down and cannot evaluate itself. It works for months because the source is the legacy system, then breaks at the swap, and the tell is a view that only plans successfully with pushdown. Audit every ported view in phase one and rewrite source-specific SQL before validation starts.

CDC drift. Schema changes break the apply job, or lag grows unnoticed, and the Iceberg copy silently ages while consumers assume freshness. Defenses: schema change detection that fails loudly to the owning team, lag as a paged metric per synchronized table, and the reconciliation heartbeat as the backstop.

The unmapped consumer. A service account, a quarterly finance export, a script on a laptop, reading the legacy system outside the federation path, discovered when decommissioning breaks it. The defense is in the sequence: revoke access before deleting, hold a listening period, and review legacy-side query logs at the exit checklist, so the consumers the map missed announce themselves against a reversible action.

Layout nostalgia. Teams replicate the legacy schema exactly into Iceberg, importing the over-partitioning and the denormalizations that existed for the old optimizer, and the new estate inherits debts it was supposed to retire. The defense is treating each migration as a design review with a default of improvement, with the semantic layer preserving consumer-facing continuity over the improved layout.

The stalled decommission. Everything migrated, nothing turned off, both bills continue. The defense threads the whole article: decommission dates owned from day one, exit checklists per component, and executive reporting that counts legacy spend remaining, not just tables moved.

Running It: Team Shape and Program Governance

The phases describe the data’s journey, and programs stall or succeed on how the humans are arranged around them, so the organizational design deserves its own section.

The shape that works is a small core team plus distributed ownership, deliberately not a large migration team. The core, typically a handful of platform engineers, owns the shared machinery: the federation layer, the catalog and its policies as code, the CDC infrastructure, the validation framework, and the disposition list as a living artifact. Table migrations run as thin, repeatable playbooks the core publishes and domain teams execute for their own tables, with the core reviewing rather than performing. The people who know what correct means for a table are its owners, and a central team performing every migration re-learns each table’s semantics secondhand, slowly, under deadline.

Program governance runs on the artifacts already named, which is the point of naming them. The disposition list is the backlog and the progress report, the reconciliation dashboards are the quality gate, and the legacy-spend-remaining line is the executive metric, reported beside dispositions resolved, because together they resist the two classic distortions: celebrating table counts while spend persists, or chasing spend cuts by rushing validation. Quarterly reviews walk the exceptions, the phase-two inflow exceptions, the federate-indefinitely tier, the stalled dispositions, each with its owner present, and the output is dates changed or dates confirmed, never status prose.

Two cadence rules keep the program healthy across the year-plus it runs. First, the standing workstream beats the crunch: a steady few tables per week through the playbook outperforms quarterly pushes that burn the core team and batch the risk. Second, ship value announcements, not milestone announcements: every cohort of moved dashboards, every retired replica, every workload made faster gets communicated in consumer terms. The stalled migrations in everyone’s memory did the opposite, announcing phases while consumers noticed nothing, and that lesson is as transferable as the technical ones.

The Economics, Told Honestly

The zero-copy pattern does not make modernization free. It changes the spending curve’s shape, and the shape is the point.

The classic project’s curve is a long trench: heavy spend on copying before meaningful value, then the double-bill plateau during the extended parallel run, with the payoff arriving at a decommission date that keeps receding. The zero-copy curve front-loads value instead: federation delivers unified access in weeks, redirected inflow accrues open-estate benefits immediately, and each materialized table pays back on its own schedule while legacy spend declines as workloads leave. The parallel period still exists per table, and it is short, cheap, and bounded rather than long and total.

One line item surprises finance teams, so name it early: the modern engine’s cost while it sits in front of the legacy warehouse and the lakehouse is nearly empty. That cost is real, and it buys the decoupling that makes everything else safe. It is also small next to a year of double-ETL, and it is the same engine that serves the lakehouse afterward, so it is not a temporary expense.

Three lines belong on the dashboard from the start: legacy platform spend trending down, modern platform spend trending with workload value, and the migration’s own labor. The honest expectations: total spend rises modestly in the early quarters, crosses below the legacy baseline as hot workloads move, and the end state is not just a cheaper platform bill but the strategic position the decoupling delivers, engines as a portfolio, AI consumers on governed open tables, and no future migration of this kind again, because the estate’s seams are now standards. That is the real return.

Conclusion

Warehouse modernization fails when it is a copying project, because copying an estate means recreating a decade of dependencies under deadline while the source moves and trust waits for a cliff. It succeeds when it is a connection project: federate first and deliver value with the data in place, give consumers one stable name per table through a semantic layer that outlives the layout, redirect new data into Iceberg so the target stops moving, materialize history in the order the query logs earn, hold changing tables in lockstep with CDC until each writer’s cutover is a rehearsed few minutes, and let consumers cross on accumulated correct answers.

The write side gets a schedule of its own for the same reason: pipelines repoint at the swap, stored procedures are rewritten or dissolved into governed views, and application writes route through a landing zone. The swap runbook then makes each table’s cutover half an hour of scripted steps nobody outside the team notices.

The tools for every phase are shipping and mature, and what the pattern asks of you is discipline rather than heroics: dispositions for every table, validation as infrastructure, a write-side plan that names each writer, and a decommission date somebody owns. The estates that follow it end up somewhere better than migrated: they end up on their last proprietary format, with every future engine decision reduced to configuration. If you want the longer treatment of the migration patterns and the platform they land on, this companion piece on moving a legacy warehouse to the lakehouse picks up where this one stops.

Keep Going

If this piece was useful, the deeper treatments are in my books. Architecting an Apache Iceberg Lakehouse from Manning covers the platform design this migration lands on, and Apache Iceberg: The Definitive Guide from O’Reilly covers the table format underneath it. You can find every book I have written, across lakehouse architecture, Apache Iceberg, Apache Polaris, and AI, at books.alexmerced.com.