Survey the Data Before You Migrate It

I had a clean, confident map of a data migration. The code that used the table told me exactly which columns held encrypted values, which were keys, which were plaintext. Then, before writing a line of migration logic, I did the unglamorous thing and actually read the data — a read-only survey of a few thousand sampled values. Almost none of them matched what the map said should be there. The column that was “obviously” full of encrypted ciphertext was full of short random strings. The environment held synthetic test fixtures, not real data. My beautiful map described the design. The data described reality. They were not the same thing.

Schema is intent; data is reality

A schema, and the code that reads it, tells you what the system is designed to hold. That’s genuinely useful — it’s how I built the map of which column meant what. But design intent and actual contents drift apart constantly: test fixtures get loaded and never cleaned up, a field gets repurposed, an import populated something in an unexpected shape, a “required” column is mostly null, an environment was seeded with garbage to make it boot. The schema can’t tell you any of that, because the schema is a promise about the data, and promises about data are broken all the time.

The schema is what someone meant to store. The survey is what’s actually in there. Migrate against the first and you’re transforming a hope.

The failure mode: validating against garbage

The specific danger that survey saved me from is subtle and nasty. If I’d trusted the map and built the migration against it, the migration would have run — it would have processed rows, reported success, and “validated” cleanly. Against fixtures. Against data that had none of the real shapes, edge cases, or awkward values that production would throw at it. I’d have walked away with false confidence: a migration proven correct against data that didn’t resemble what it would actually face. The first real row would have found the bug my test never could, because my test ran on junk that agreed with my assumptions.

That’s the trap of skipping the survey: you don’t get a loud failure, you get a quiet false pass. The migration looks validated. It’s validated against the wrong thing.

What a survey actually checks

A data survey is read-only reconnaissance, and it’s asking blunt questions the schema can’t answer:

• Does this column actually contain what I think? Sample real values and check their shape. If the “encrypted” column doesn’t decrypt, or the “email” column is full of nulls, stop.
• How much is there, and where? Which tables are populated and which are empty. I found six of the tables I “needed to migrate” had zero rows — good to know before planning for them.
• What are the edge cases? The empty strings, the maximum lengths, the weird encodings, the one row that breaks the pattern. Those are what the migration will trip on, and they only show up in the real distribution.
• Is this even real data? The question I didn’t think to ask until the answer was “no.” Dev and test environments are full of synthetic data that looks plausible and behaves nothing like production.

The output is a corrected map — design intent confirmed (or refuted) against ground truth — and often a shopping list of surprises to handle before the migration is trustworthy.

Validate correctness against real shapes, not stored junk

The survey also changed how I proved the migration correct. Since the environment’s stored values were fixtures, decrypting them proved nothing — so the authoritative test became generating known real-shaped values, running them through the transformation, and checking the round-trip, rather than trusting whatever happened to be sitting in the table. The data you have on hand is not automatically a valid test corpus; sometimes you have to manufacture realistic data because what’s there is garbage. Knowing that is itself a product of the survey.

This is the data-layer version of trusting the system of record over the ticket: a document (or a schema) is a claim, and claims drift from reality, so you verify against the thing itself before you act. And it pairs with the mechanics of running the backfill safely — no amount of careful throttling and idempotency helps if you built the whole thing against data that isn’t real.

So now the first step of any migration, before design, before code, is: go look at the actual data, read-only, and find out whether it’s what everyone assumes. It’s an hour that routinely saves a disaster, because the alternative isn’t a failed migration — it’s a successful one against the wrong data. If you’ve had a survey save you from migrating a table full of surprises, I’d love to hear what you found.