Cross-Account DynamoDB Migration: Prove Every Row, Recover Every Failure

A data migration is only done when every row can be proven to have arrived and every failure can be recovered without making things worse. We learned that moving a few hundred DynamoDB tables and millions of items into a fresh AWS account with a redesigned schema: every row transformed, tenant-scoped and written through a new repository layer. A dry run validates your read path. Only the cutover validates your write path. Everything expensive lives in the gap between the two.

This post sets out the five-clause contract every migration job had to obey, the six production scenarios where “validated” met reality, the four operator tools that paid for themselves within a day, and the positions we now defend in every migration review.

The short version

• Fix the contract before writing any script: cross-account by construction, resumable, idempotent, dry run first, provable.
• A dry run exercises traversal and transformation, not the target’s write semantics; push real rows through the real write path before any bulk run.
• Samples reassure, surveys inform: any attribute that becomes a key gets a full-column type survey.
• Idempotency needs a proof chosen per write path: put-if-absent proves itself on a second run; upserts need a content hash, a timestamp guard or a stable target count.

Why migrations fail in the mundane

Nobody loses data to the failure they rehearsed. Migrations fail on a duplicated attribute name, a list where a string should be, a credential that expires while everyone sleeps. Each is trivial on its own; at bulk-write scale, each one is a halted job at an unknown cursor position against a half-populated target. So the real engineering problem is not how to move the data. It is how to make every failure recoverable and every success provable. A migration you cannot resume, re-run and independently verify is not a migration; it is a bet.

The contract: five clauses fixed before any script

The contract was written after we discovered how many ways a migration could be “working” while still being wrong. Every job, regardless of table or author, had to obey it.

Once the contract was explicit, the migration stopped looking like a collection of scripts. Each job became a small state machine (scan, transform, write, checkpoint, verify) with failure states that had defined recovery paths. The same discipline applies to firmware fleets; the post on canary, stage, fleet rollouts describes the device-side version.

Once the contract was explicit, the migration stopped looking like a collection of scripts. Each job became a small state machine — scan, transform, write, checkpoint, verify — with failure states that had defined recovery paths. That reframing, more than any individual script, is what the rest of this post defends.

Six scenarios where “validated” met production

Scenario 1 in detail: the error that taught us the most

The first crash read:

ValidationException: Two document paths overlap with each other; path one: [created_at], path two: [created_at]

The fix was two lines. The lesson was structural. A dry run exercises source traversal, transformation logic and accounting. It does not exercise conditional expressions, repository-stamped attributes or index constraints on the target. From then on, every job pushed a handful of real rows through the real write path before any bulk run.

Scenario 2 — The column that lied about its type

Same evening, second crash: a GSI key expected a string, and the legacy attribute was a string on almost none of the rows — a single-element list on the vast majority, an empty list or absent on the rest. Years of schema drift, invisible in any hand-inspected sample: every row we had happened to look at was fine. Before fixing anything, we scanned the entire column and histogrammed its DynamoDB types. The survey proved no multi-owner lists existed — turning the fix into two safe lines instead of a product decision made blind. Samples reassure; surveys inform. Any attribute about to become a key gets surveyed across every row.

from collections import Counter
import boto3

def survey_attribute(session: boto3.Session, table: str, attr: str) -> Counter:
    """Histogram the DynamoDB type of `attr` across EVERY item, not a sample."""
    client = session.client("dynamodb")
    types = Counter()
    for page in client.get_paginator("scan").paginate(
        TableName=table,
        ProjectionExpression="#a",
        ExpressionAttributeNames={"#a": attr},
    ):
        for item in page["Items"]:
            value = item.get(attr)
            if value is None:
                types["<absent>"] += 1
            elif "L" in value:
                types[f"L(len={len(value['L'])})"] += 1
            else:
                types[next(iter(value))] += 1  # "S", "N", "M", ...
    return types

Scenario 3 — The checkpoint that would have poisoned another environment

Checkpoint files were keyed by job name alone, because when they were written only one target environment existed. Running the same job against a second environment would have silently resumed from the first one's cursor. The bug wasn't in checkpointing; the bug was in state identity. Any state a migration persists — checkpoints, quarantine lists, skip notes — must carry the environment in its identity, because you will always eventually run the job against a second environment.

import json
from pathlib import Path

def checkpoint_path(job: str, source_account: str, target_env: str) -> Path:
    # State identity = job + source + target environment.
    # Keyed by job name alone, a run against env B resumes from env A's cursor.
    return CHECKPOINT_DIR / f"{job}__{source_account}__{target_env}.json"

def save_checkpoint(path: Path, last_evaluated_key: dict | None, counters: dict) -> None:
    tmp = path.with_suffix(".tmp")
    tmp.write_text(json.dumps({"cursor": last_evaluated_key, "counters": counters}))
    tmp.replace(path)  # atomic rename: a kill mid-write never corrupts the checkpoint

The cursor itself is DynamoDB’s LastEvaluatedKey, fed back as ExclusiveStartKey on resume (see Scan pagination in the DynamoDB docs). Persisting it is cheap; persisting it under the wrong identity is the bug.

Scenario 4 — The credentials that didn't outlive the backfill

Credential lifetime is a migration dependency, not an operator detail. Long scans meet it head-on: first a login token expired mid-scan — retry logic re-resolved the session, the checkpoint held, resume worked. Then, overnight, the login session itself lapsed: every "refresh" returned the same dead token, and the auto-resume loop faithfully burned its retries against it while a human slept. Treat it as a dependency and the options rank themselves: credentials whose lifetime exceeds the run; failing that, re-authenticate immediately before launch; and size any retry loop so attempts × backoff outlives the hours nobody is watching. Write the resume loop anyway — checkpointed idempotent passes make retries free.

If your operators sign in through IAM Identity Center, check the permission set’s session duration before launch: it defaults to one hour and tops out at twelve, so an overnight backfill can outlive any single session.

Scenario 5 in detail: what counts actually prove

Counts prove population-level completeness; they do not prove row-level equivalence. Where the transformation was one-to-one, we also compared deterministic identifiers and selected content hashes. Where one source row fanned out into multiple target rows, the job declared its expected cardinality explicitly, which is also why a naive counter-versus-count reconciliation once flagged a phantom discrepancy of exactly the fanned-out rows. Idempotency needs a proof, not a claim, and the proof is chosen before the run.

import hashlib, json
from botocore.exceptions import ClientError

# Inside the repository layer: the two write paths and their no-op proofs.

def put_if_absent(table, item: dict) -> bool:
    """Insert-only. A re-run is a no-op: returns False when the row already exists."""
    try:
        table.put_item(Item=item, ConditionExpression="attribute_not_exists(pk)")
        return True
    except ClientError as e:
        if e.response["Error"]["Code"] == "ConditionalCheckFailedException":
            return False
        raise

def upsert_if_changed(table, item: dict) -> bool:
    """Upsert with an explicit no-op: skip when the stored content hash already matches."""
    digest = hashlib.sha256(json.dumps(item, sort_keys=True, default=str).encode()).hexdigest()
    try:
        table.put_item(
            Item={**item, "content_hash": digest},
            ConditionExpression="attribute_not_exists(pk) OR content_hash <> :h",
            ExpressionAttributeValues={":h": digest},
        )
        return True
    except ClientError as e:
        if e.response["Error"]["Code"] == "ConditionalCheckFailedException":
            return False  # identical row already present: counted as a skip
        raise

# The golden rule, as a check: the second run must prove itself a no-op.
second = run_job(job, mode="run")
assert second.counters["written"] == 0, f"not idempotent: {second.counters}"

Both write paths rest on DynamoDB condition expressions. We lean on the same primitive for exactly-once event publishing in The Outbox Without Postgres.

One boundary worth stating honestly: counts prove population-level completeness; they do not prove row-level equivalence. Where the transformation was one-to-one, we additionally compared deterministic identifiers and selected content hashes. Where one source row fanned out into multiple target rows, the job declared its expected cardinality explicitly — which is also why naive counter-vs-count reconciliation once flagged a phantom discrepancy of exactly the fanned-out rows. Idempotency needs a proof, not a claim — chosen per write path, before the run.

Scenario 6 — The plan that aged out

A migration plan is an executable assumption about a dataset whose shape keeps changing. Ours recommended bounding the largest table by a time window; by run day the table had grown enough that the window kept nearly all of it, and even our skip-lists grew between rehearsal and cutover. Plans decay; re-measure on run day.

Build for the operator, not only the run

Four investments, each under an hour, each repaid within a day.

1. A challenges log. One append-only Markdown file with every crash, count anomaly and decision, its fix and its lesson. It is the difference between a rehearsed production cutover and a re-discovery tour.
2. An interactive runner. It explains each step, asks before executing, tees output to timestamped logs and states each step’s pass condition, so a junior engineer knows what good looks like without paging anyone.
3. A resume mode distinct from run. Our run mode deliberately restarted counters; when a long job was killed mid-flight, restarting would have discarded the cursor. The mode you need at 2 a.m. should exist before 2 a.m.
4. Account guardrails in the wrapper. Assert that both AWS profiles resolve to the expected account IDs before any job runs. Four lines that make “migrated into the wrong account” structurally impossible.

The same operator-first thinking shaped our mobile API design, where idempotency keys make client retries free; see designing mobile-friendly APIs: idempotency, pagination, resilience.

import boto3

def assert_account(profile: str, expected_account_id: str) -> None:
    account = boto3.Session(profile_name=profile).client("sts").get_caller_identity()["Account"]
    if account != expected_account_id:
        raise SystemExit(f"{profile} resolves to {account}, expected {expected_account_id}")

assert_account(SOURCE_PROFILE, SOURCE_ACCOUNT_ID)
assert_account(TARGET_PROFILE, TARGET_ACCOUNT_ID)

Positions worth defending

A dry run validates logic, not the write path: real rows through the real write path before any bulk run. Samples reassure, surveys inform: any attribute becoming a key gets a full-column type survey. Idempotency needs a proof, not a claim: put-if-absent proves itself, upserts need a content hash, a timestamp guard or a stable target count. Persisted state carries its environment: checkpoints without scope are future cross-environment corruption. Plans decay: every volume-based number in a migration plan is wrong by cutover, and the only question is by how much. Nothing here is exotic. That is the point. The failures that happen are mundane: a duplicated attribute, a stale checkpoint, an expired credential, a type that was never what the sample said. The contract turns them from incidents into log entries. Nothing here is exotic. That's the point. The failures that actually happen are mundane. A duplicated attribute. A stale checkpoint. An expired credential. A type that was never what the sample said it was. The contract is what turns those failures from incidents into log entries.

What this protects at Hoomanely

The data layer we moved carries the record EverBowl™ builds of how a dog eats and drinks, and what the app knows about the pets in a household. EverBowl does not diagnose anything; the value is the record, and a migration is custody of that record. “Probably all arrived” is not a standard for it. Provably recoverable and provably complete is. The mobile side of keeping that record consistent is in offline-first with Isar, and more backend posts are in the software category.

Related reading

• The Outbox Without Postgres: Exactly-Once Events on DynamoDB
• From Scripts to DAGs: Architecting Predictable Multi-Step Pipelines
• We Chose Cassandra for device_events. Here’s What It Cost Us.

A note on stakes: at Hoomanely, the data layer we moved carries what our smart bowl and the EverWiz app know about the pets in our users' lives. When a migration is custody of a pet's history, "probably all arrived" is not a standard — provably recoverable and provably complete is.

Conclusion

Write the contract before the scripts, push real rows through the real write path before any bulk run, survey every attribute that becomes a key, choose the idempotency proof per write path, scope every piece of persisted state to its environment, and re-measure the plan on run day. Then build the log, the runner, the resume mode and the account guardrails, because the operator at 2 a.m. is part of the system.

FAQs

What is a migration contract?

A fixed set of rules every migration job must obey before any script is written. Ours has five clauses: cross-account by construction, resumable, idempotent, dry run first, and provable with a completion proof defined in advance.

Why is a dry run not enough to validate a data migration?

A dry run exercises reading, transforming and counting. It never exercises the target’s write semantics, such as conditional expressions, repository-stamped attributes or index constraints, which is where the first real run failed for us.

How do you prove a migration is idempotent?

Run it twice. For put-if-absent jobs, the second run must write zero rows. For upsert jobs, compare a content hash or modified timestamp before writing and count skips, or fall back to a stable target count plus spot-checked rows.

How should migration checkpoints be stored?

With the environment in their identity, alongside the job name. A checkpoint keyed by job name alone will one day resume a job against the wrong environment from the wrong cursor.

What tooling should a migration operator have?

An append-only challenges log, an interactive runner that states each step’s pass condition, a resume mode separate from run, and a wrapper that asserts the source and target account IDs before anything executes.

Do row counts prove a migration is complete?

They prove population-level completeness only. Row-level equivalence needs deterministic identifiers or content hashes for one-to-one transforms, and an explicitly declared cardinality where one source row fans out into several target rows.