Migrating data without stopping the system

Created: February 2026 Updated: Sept. 29, 2026

from work

Python, Oracle, ETL, incremental loading

Moving a large amount of data to a new database schema while the system has to keep running.

The data has to move to a new schema in an Oracle database, and the schemas differ a lot: fields from one old table are spread across several new ones and the other way round. The system runs all the time, so stopping everything for a weekend is out of the question, and the data keeps changing, so what I moved in the morning may be out of date by the evening.

Why not one script

One script in a maintenance window is out, because there is no window, and if it fails halfway you start again. Change replication with ready-made tools would be fine for similar schemas, but here the data needs heavy transformation on the way. So we're writing our own ETL with incremental loading.

Small batches

Scripts in Python, small steps: fetch a batch of records, map it to the new schema, write, check. Every run only picks up what has changed since the previous one, batches are picked by last-change date, so an interrupted run simply starts again from the same place.

The hard part: matching

Data we've already moved keeps changing in the old system and has to be linked up again with what's in the new one. The catch is that the new system doesn't keep the old identifiers, so records are matched on several columns at once. Writing those matching rules is the hardest part of the whole migration, and testing them is no easier.

The matching lives in the MERGE condition, on several columns instead of an identifier, so a repeated run updates what's already there instead of creating duplicates:

sql

MERGE INTO records_new t
USING (SELECT :owner_code AS owner_code, :doc_number AS doc_number, :doc_date AS doc_date,
              :status AS status, :updated_at AS updated_at FROM dual) s
ON (t.owner_code = s.owner_code AND t.doc_number = s.doc_number AND t.doc_date = s.doc_date)
WHEN MATCHED THEN
    UPDATE SET t.status = s.status, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (owner_code, doc_number, doc_date, status, updated_at)
    VALUES (s.owner_code, s.doc_number, s.doc_date, s.status, s.updated_at)

After each batch we compare record counts on both sides, and records that can't be matched unambiguously end up in a separate table with a description of the problem instead of stopping the whole migration.

Machine-translated from Polish (original).