Idempotent DLT Migration for Legacy Databases

Legacy migrations fail in the boring places: the network drops, a batch is restarted, or a mapping changes after half the destination has already been written. The useful question is not whether the first run works, but whether the second run leaves the target cleaner or more corrupted.

This use case came from a legacy database migration where the source was MS-SQL, the destination was PostgreSQL, and the pipeline needed to tolerate reruns. The goal was not to make failure impossible. The goal was to make retries predictable.

The shape was deliberately small. A table-specific migration declared what to read, how to rename fields, what to transform, what to drop, and which key represented row identity. A shared loader sent the cleaned data through DLT using merge semantics. That gave every table the same rerun contract while leaving table-specific mapping decisions explicit.

An idempotent migration is not a migration that never fails. It is a migration whose retry has a predictable effect. If the same source rows are read again, the destination should converge on the same state rather than collect duplicates, partial transforms, or timestamp noise that makes every run look new.

DLT's merge loading model fits that requirement because it is designed to deduplicate and merge incoming data into existing destination rows when a merge key or primary key is available. PostgreSQL exposes the database-side primitive for this family of behavior through INSERT ... ON CONFLICT, where a conflict target lets the database update or ignore rows that would otherwise violate uniqueness.

The important design choice is the key. The migration has to identify a stable row identity before the load reaches PostgreSQL. A convenient counter from a staging export is not enough if it can change between runs. A business identifier is not enough if it is only unique most of the time. The merge key is the contract that tells the destination whether the incoming row is new or already known.

There are limits. DLT merge loading does not provide zero downtime by itself. It does not remove the need for destination constraints, batch sizing, schema review, or a rollback plan. Its value here is narrower and more useful: when the pipeline sees the same logical row twice, it has a key to decide that the row should merge rather than append.

The migration pattern worked because table migrations followed one sequence: fetch, rename, transform, and drop. That order matters.

Fetch comes first because every later step needs a concrete dataset. Rename happens before custom transformation so table-specific logic can target destination-oriented names instead of carrying legacy source names through the pipeline. Transformation happens before dropping columns, which means derived fields can still read old fields before those fields disappear. Drop stays last so the pipeline can tolerate a source field already being absent.

This is not schema-agnostic magic. Every table still needs a human to encode the migration rules. Legacy schemas are full of local meanings, columns that changed purpose, names that encode business history, and fields that should not survive the move. A shared migration skeleton should standardize execution order, not pretend those decisions can be inferred safely.

The smaller win is reviewability. A table author declares the source shape, destination shape, primary key, rename map, fields to remove, and deterministic transformations. The loader owns how the data is written. A reviewer can ask two direct questions: does this table produce the right destination shape, and does the loader have a stable key for merging it?

The transformation layer used small, named operations for deterministic data-frame changes. Examples included splitting one field into separate destination fields and normalizing text into lower snake case. The useful property was not the specific operation. It was the boundary: each transformation had to return a valid frame before the load step could continue.

That kind of early failure is valuable in migration work. A transform that accidentally returns nothing should fail at the transformation boundary, not halfway through a destination load. It is easier to fix one bad mapping than to clean up an uncertain write.

I would keep this API narrow. Use it for row-local shape changes: split a field, normalize casing, trim values, map known categories, or convert data types where the mapping is reviewed. Do not hide source reads, destination writes, network calls, or cross-table orchestration inside a transformation. The rerun contract gets weaker when transformation output depends on mutable outside state.

One caveat matters for the idempotency story. The design had a helper for adding a migration timestamp, but that hook was not active in the execution path. The shipped run did not add that timestamp. Claiming otherwise would be wrong, and it would change the rerun model because a fresh value can make identical source data look different on every pass.

The source connector had the usual responsibilities: build a SQLAlchemy engine, test a connection, compose a read query, and load results into pandas. That part is easy to describe and easy to get wrong. Credential handling is the risk boundary.

SQLAlchemy supports building engines from connection URLs, including credential, host, and database components. That standard surface is useful, but it also makes accidental secret exposure easy when connection details live beside application code or migration notes.

For this style of migration, the safer pattern is to load secrets from the runtime environment or a secret manager, keep connection construction in one place, and make tests use explicit fake values. The migration contract can stay the same. Only source initialization changes.

The source query also needs review. Selecting a configured list of columns is safer than selecting everything once the table is understood. If table names or column names ever come from outside trusted code, use SQLAlchemy primitives or strict allow-lists. Idempotency does not compensate for a weak source boundary. A migration that can be retried safely can still leak secrets, query too much data, or move fields that should have been excluded.

Start by defining the destination table contract. Name the destination table, primary key, required columns, nullable columns, and fields derived from the source. If the primary key is unstable, stop there. Merge loading needs row identity, not a convenient handle.

Then implement the table migration with the smallest useful source query. Prefer an explicit column list after the initial discovery pass. Fill in the rename map and drop list so the legacy vocabulary disappears early and unwanted source fields disappear late.

Add deterministic transformations next. Keep them pure against the input dataset. If a transform needs reference data, load that reference data outside the transform and pass in a reviewed mapping.

After that, run the same batch twice against a disposable destination. Compare row counts and key-level values after each run. The expected result is not "two successful logs." The expected result is that the destination state after the second run matches the state after the first run for the same source data.

Finally, remove credentials and account-specific values before wider review or publication. Migration scripts often start as temporary tools, then become operational tooling. The moment a script can connect to a real legacy database, credential handling is part of the migration design.

Use this pattern when a legacy database move needs controlled retries, table-specific mapping, and a destination that can merge by primary key. It is a good fit for incremental migrations, rehearsals against disposable databases, and staged cutovers where the same data may be loaded more than once.

Avoid it when row identity is unclear, destination uniqueness is not enforced, transformations depend on outside mutable state, or the migration needs cross-table transactional guarantees that the pipeline does not provide. In those cases, solve the identity and consistency model before adding more table mappings.

If you are planning a legacy data move, bring one table and its failure cases. I can help turn it into a rerunnable migration contract before the migration grows around assumptions.

• Merge loading - dlt
• Resource - dlt
• dlt.pipeline API - dlt
• INSERT - PostgreSQL
• Engine Configuration - SQLAlchemy