How do I move data between systems without losing records?

To move data between systems without losing records, the safest approach is to treat it like a controlled migration, not a copy-paste. The key is to make the process repeatable, verifiable, and restartable.

Core principles

1. Inventory everything first

   • Identify all source tables/files/entities
   • Note record counts, primary keys, timestamps, and dependencies
   • Find “hidden” data like attachments, audit logs, soft-deleted rows, and lookup tables

2. Use a stable unique identifier

   • Every record should have a consistent key that can be matched in both systems
   • If the destination generates new IDs, keep a mapping table from old IDs to new IDs

3. Migrate in phases

   • Initial load: move all existing data
   • Incremental sync: capture changes made during the migration
   • Cutover: switch traffic after the final sync

4. Make it idempotent

   • The migration should be safe to run more than once
   • Use upserts/merge operations instead of blind inserts when possible

5. Validate counts and checksums

   • Compare source and destination record counts
   • Validate key subsets and totals
   • Use hashes/checksums for critical fields to catch silent corruption

6. Handle dependencies in order

   • Load parent records before child records
   • Example: customers before orders, products before order lines

7. Pause writes or capture deltas

   • If the source keeps changing, either:
     • freeze writes during final copy, or
     • use change data capture / timestamp-based deltas / event logs

8. Log everything

   • Keep detailed logs of imported rows, failures, retries, and rejected records
   • Store error records in a dead-letter queue or error file for reprocessing

A practical migration workflow

1. Profile the source data
2. Map source fields to destination fields
3. Clean and normalize data rules
4. Perform a test migration in a sandbox
5. Compare source vs destination counts
6. Fix mapping/data issues
7. Run a full migration
8. Run incremental syncs
9. Do a final reconciliation
10. Cut over and monitor closely

Common failure points to watch

• Duplicate records caused by reruns
• Missing related records because of load order
• Data type mismatches
• Truncated fields
• Time zone/date conversions
• Deleted records not being handled
• Systems creating new IDs without preserving the old ones

Tools and techniques

• ETL/ELT tools: Airbyte, Fivetran, Talend, Informatica, dbt
• Database-native methods: replication, CDC, bulk export/import
• APIs: batch export/import with retry and pagination
• Validation: row counts, referential integrity checks, sampling, checksums

Simple rule of thumb

If you can:

• uniquely identify every record,
• run the migration more than once safely,
• track changes during the move,
• and verify source and destination match,

then you greatly reduce the chance of losing records.

If you want, I can also give you:

• a step-by-step migration checklist,
• a database-to-database migration plan, or
• a script/template for verifying record counts and duplicates.