How one-shot imports go wrong

A one-shot import is written to run once. After it finishes, the checking starts, and problems turn up: order dates in the wrong field, products linked to the wrong vendor, customers loaded twice. The quickest fix is to correct those records by hand in the new system.

Each hand fix makes the next run more dangerous. If a bigger problem turns up later and the import has to run again, it overwrites the hand fixes, or someone has to repeat them. So the import is never run again, and every error found after that point is fixed by hand too. The new system starts out with corrections nobody can trace.

One-shot imports also fail badly halfway through. If the import stops after loading customers but before loading orders, the database holds part of the data, and someone has to work out what was written before anything can be tried again.

Illustration titled One-shot import or rerunnable import. On the left, under One-shot import, a plain file goes into a database and a pencil marks records Fixed by hand, with a warning: Hand fixes are lost on the next run. On the right, under Rerunnable import, a plain file goes through the import into a database, and a loop arrow labeled Fix the mapping, run again leads back to the import, with a check mark: Every fix is kept. A bar at the bottom reads: Every correction lives in the import and survives the next run.
Figure 1. A one-shot import pushes corrections into hand edits. A rerunnable import keeps them in the import itself.

What makes an import rerunnable

Four habits make an import safe to run as often as needed:

  1. Check the input before writing anything. The import records a fingerprint (a checksum) of each source file and the number of rows it expects. If a file has changed or a count is off, it stops before touching the database.
  2. Back up first and verify the backup. Before writing to the live database, take a backup, verify its checksum and store a copy off the server. If anything goes wrong, the database can be put back exactly as it was.
  3. Apply everything in one transaction. The import saves all of its changes or none of them, so a failure halfway leaves the database as it was instead of half loaded. It should also confirm that it is writing to the database it was meant for.
  4. Run it again and expect zero changes. Each record is matched to its source by a key that does not change, such as the old system's record ID. A second run on the same files finds every record already in place and reports zero inserts, zero updates and zero deletes. Any other result means the import is not doing what you think.

One rule sits on top of all four: corrections go into the import, never into the records. If a date is wrong, fix the mapping that produced it and run the import again.

Illustration titled Four checks that make an import safe to rerun. Four cards, joined by arrows, in reading order: Check the files, with the note Stop if a file changed; Back up first, with the note Verified copy kept off the server; All or nothing, with the note One transaction; Run it again, with the note Zero changes. A bar at the bottom reads: A second run that changes nothing proves the first one worked.
Figure 2. The four checks, in the order they run.

What a rerunnable import lets you do

  • Fix and rerun. When a check finds an error, fix the mapping and run the import again from the start. The fix is permanent, because it lives in the import.
  • Rehearse on a copy. Run the full import against a fresh copy of production before the real run, with the same checks you plan to use afterward.
  • Load data that arrives later. If more files turn up before cutover, add them and run again. Records already in place are left alone.
  • Know when you are done. The import is finished when a comparison with the old data shows no differences and a second run changes nothing.

Speed matters here. An import that takes minutes can be rerun after every correction. One that takes days will not be, and corrections will drift back to hand edits.

Two migrations that used this method

The first was a flooring wholesaler leaving 15 years of Zoho customizations. An automated pipeline loaded 15 years of data, hundreds of thousands of records, in minutes, so it could be rerun after every correction. Every field brought across was compared with a backup of the old data: 293,986 fields, with zero differences at the end. Reaching zero meant tracing 43 exceptions. For 35 orders the correct date was missing from the old records, so it was recovered from the time the original order email was received. Five products were traced through vendor part numbers, and the other three were traced one by one. Each answer went into the import, not into a hand edit. How a flooring wholesaler left 15 years of Zoho patches in one month tells the full story, and Moving off Zoho without losing your history covers the method.

The second was the wholesaler's sister company, which migrated right after. Its records arrived as spreadsheet exports. The import checked each file's fingerprint and expected row count before touching any database, ran in full on a fresh copy of production, took a verified backup stored off the server, wrote everything in one transaction and was checked by a separate validation. It was then run again straight away and reported zero inserts, zero updates and zero deletes. The second migration was the easy one describes that import.

Keeping corrections in the mapping also matters as the number of sources grows. Intelligence Factory's published case study on integrating dozens of EHR systems describes data from nearly three dozen EHR and EMR systems mapped to one internal ontology, with the mappings for each source kept separate from the rules that use the data for eligibility, billing and reporting. When each source has its own mapping, there is one place to put a correction for that source, and the import can be run again with it.

Questions to ask your developer

  • How long does one full import take? If the answer is days, corrections will end up being made by hand.
  • Can it be run again from scratch, and what does a second run on the same files report?
  • Does it check the source files before it writes anything?
  • Is a backup taken and verified before the real run, and where is the copy kept?
  • Does it write in one transaction? What does the database look like if it stops halfway?
  • Where do corrections live: in the import, or as edits in the new system?
  • Will the full import be rehearsed on a copy of production first?

Planning a migration

If you are moving off Zoho or another system and want an import you can run as often as you need, tell us where your data lives today. We will tell you how we would load it and check it. Tell us about your current system, or read how full service works.