CRM Migration Toolkit
An auditable ETL pipeline for moving contacts between CRMs and ESPs
View the repositoryContext
CRM and ESP migrations fail quietly: records drop between export and import, custom fields land in the wrong destination or the wrong format, consent provenance gets lost in translation, and deliverability suffers because a dirty list moves wholesale onto a fresh sending reputation. The industry default, export a CSV, massage it in a spreadsheet, import and hope, has no audit trail, no repeatability and no answer when the client asks "where did those 300 contacts go?".
This toolkit is the reference implementation of the alternative: a staged ETL pipeline where every record is normalized, de-duplicated, mapped, validated and accounted for, ending in a reconciliation report that ties input to output row by row. It is public on GitHub both as a working tool and as a demonstration of how migration engineering should behave.
The Challenge
- Every platform exports differently, encodings, date formats, multi-value field conventions, so normalization has to happen before any logic can be trusted.
- Duplicates are not identical rows: the same human appears with case variants, alias addresses and conflicting field values that need deterministic merge rules.
- Consent state is legally load-bearing and structurally different per platform, it must map explicitly, never by assumption.
- Bad records must be excluded without disappearing: silent drops are how migrations lose data and trust.
- The pipeline had to stay platform-agnostic: source and destination change per engagement, so platform quirks belong in configuration and mapping definitions, never hardcoded in stage logic.
Scope and Approach
-
01
Normalization
Canonicalizes encodings, trims and case-folds emails, unifies date and phone formats, and expands platform-specific multi-value conventions, so every downstream stage operates on one predictable shape regardless of source platform.
-
02
Deterministic deduplication
Resolves identities on deterministic keys with documented merge precedence, which source wins per field, how conflicting states reconcile, so the same input always produces the same output. No fuzzy guessing on data that feeds legal consent decisions.
-
03
Field and consent mapping
Maps custom fields and consent state across platform schemas through an explicit, reviewable mapping definition, with transformations documented per field. Structure and legal basis survive the move, not just email addresses.
-
04
Per-record validation gates
Every record passes field-level validation; failures route to a quarantine file with a machine-readable reason code per row. Bad data is excluded from the import set but never deleted, it remains visible, attributable and fixable.
-
05
Reconciliation reporting
The final stage reconciles input counts to output plus quarantine and emits the integrity report, the artifact that answers "did everything arrive?" with numbers instead of confidence. This is the document a regulated client keeps on file.
-
06
CLI, tests, idempotency
Ships as a command-line pipeline with a test suite. Stages are idempotent, re-running is safe by design, and composable, so the toolkit adapts as the backbone of bespoke client migrations rather than being a one-shape-fits-all script.
Architecture flow
Technical Deep Dive
Quarantine over silent drops
The single most damaging migration behavior is the silent drop: a row that fails a check and simply vanishes. Here every rejection carries a reason code and lands in a reviewable file, the difference between "the import lost contacts" and "these 31 rows failed validation for these reasons, here they are".
Consent as data, not metadata
Platforms encode consent in wildly different ways, subscription booleans, status enums, list membership, timestamped opt-ins. The pipeline treats consent as a first-class mapped field with its provenance preserved, because importing an EU contact without their opt-in evidence is a GDPR exposure, not a data quality nit.
Determinism as a design constraint
Every stage is deterministic: same input, same output, byte for byte. That makes dry runs meaningful, diffs reviewable, and the pipeline safe to re-run after a fix, properties a spreadsheet workflow can never offer.
The mapping definition as a reviewable contract
Field and consent mappings live in an explicit definition that can be reviewed and signed off before a single record moves, the migration equivalent of an API contract. When a field lands somewhere unexpected, the answer is in the mapping diff, not in someone's memory of a spreadsheet session.
Engineering Decisions
Staged pipeline over a monolithic script
Each stage reads and writes files, so any step can be inspected, re-run or replaced in isolation. Debugging happens at the stage boundary with real data visible, not inside a 500-line script that either works or does not.
Reason codes over boolean pass/fail
A rejected row with a reason code is actionable, fix the source and re-run. A rejected row with "invalid" is a support ticket. The extra design cost was trivial; the operational difference is not.
Local pipeline over SaaS migration tools
Client contact data never leaves the machine running the pipeline, no third-party migration SaaS holding a copy, no per-contact pricing, no black-box transform. For EU-adjacent clients that data locality is a compliance argument, not a convenience.
Results
- Migrations that are auditable end to end, reconciled counts, not a black-box export-import.
- Custom fields and consent provenance preserved across platform schemas.
- Bad records quarantined with reason codes instead of silently imported or silently dropped.
- Deterministic, idempotent stages, safe to dry-run, diff and re-run.
- A reusable backbone for real client migrations, public on GitHub.
Have a system that needs
this kind of work?
Tell me what is breaking or what you need built. I will reply with how I would approach it and what I would check first.