Complexity, not volume, decides the method

Two migrations of identical size — 35TB each — produced opposite outcomes. One completed over a weekend with a script written that afternoon. The other took three months and three aborted attempts with the standard recommended toolset. The difference was not size, skill, budget or product. It was the data model. Field note: Two 35TB migrations

Why the difference is so large

The CDR data was trivially parallel. Every file had the same shape, no file depended on any other, nothing had to load before anything else, and the data was append-only with no history to maintain. Twenty thousand independent units of work is an embarrassingly parallel problem, and almost any approach will succeed at it — including a Heath Robinson loader written in a single afternoon.

A few hundred tables in a mature reporting model is a different class of problem. The objects depend on each other, so loading has an order. Referential integrity has to hold at defined points. Sequences, surrogate keys and slowly changing history carry state that must survive the move. There are views, materialised views, procedures, grants and synonyms that are not data at all. There is partitioning, compression, LOBs and platform-specific physical structure. And there is usually at least one feature with no clean equivalent on the target. Every one of those removes a degree of parallelism and adds a way to fail.

Volume determines how long the movement takes. Complexity determines whether the approach works at all. These are different questions and only the first one is easy to answer, which is why estimates are so often built on the wrong one.

The trap in the middle

Here is the part that is genuinely counterintuitive.

At low complexity, everything works — including crude approaches — and the fastest, simplest option is usually the right one. As complexity rises, the simple approaches fall away one by one, and you are pushed towards more capable tooling. But the more capable tools are not simply better: they bring their own constraints, prerequisites and failure modes. Compatibility rules, supported version combinations, provisioning requirements, self-containment conditions, network and host prerequisites. The tool that can handle your complexity also constrains what you are allowed to do.

So the relationship is not “more complexity, use a bigger tool.” It is that increasing complexity narrows your options from both ends at once — the simple methods stop being sufficient, and the sophisticated methods start imposing conditions you may not be able to meet. The viable set is at its widest for simple models and can get uncomfortably thin for complex ones. That is where the three aborted attempts come from.

Assess complexity before you choose

Which means the complexity assessment has to come first, and it needs to be more than a table count. What I would actually measure:

• Dependency depth and ordering. How much of the estate can load independently, and how deep is the longest chain that cannot? This is the single strongest predictor of difficulty.
• Uniformity. One repeated shape, or hundreds of distinct ones? Uniformity is what makes automation and parallelism cheap.
• Statefulness. Append-only history is easy. Sequences, surrogate key generation, slowly changing dimensions and in-place updates all carry state that has to arrive intact and consistent.
• Non-data objects. Views, materialised views, procedural code, grants, synonyms, scheduler definitions. These do not appear in a volume estimate and are frequently the reason a migration stalls.
• Physical and feature exotica. Partitioning schemes, compression, LOBs, index-organised tables, encryption, and anything with no direct equivalent on the target. One unsupported feature can invalidate an entire method.
• Referential and consistency requirements. At what points must integrity hold, and what is allowed to be temporarily inconsistent during the move?

Score these honestly and the method choice largely makes itself. Score them optimistically — or skip them and go straight to the recommended toolset because it is the recommended toolset — and you find out during the second aborted attempt.

Two closing cautions. Do not generalise from your easy wins. A team that has moved a large, simple dataset comfortably will estimate a complex one from that experience, and be wrong by an order of magnitude. And do not read the first story as an endorsement of hand-rolled tooling. It worked because the problem was simple enough to be solved simply — not because a script is a substitute for a migration tool. The lesson is not “roll your own” — it is that the tool should be matched to the complexity, in both directions. Under-tooling a complex model fails loudly. Over-tooling a simple one fails quietly, in schedule and cost, and nobody ever writes it up.

What that loader did have, because anything moving 35TB unattended over a weekend must, was modularity and restart. That is not a property of sophisticated tooling; it is a property of a competent design at any level of sophistication, and it is the subject of Nothing survives twelve hours.