How to Build a Data Migration Playbook (Tools & Validation Tests)

A migration playbook is the operational runbook that tells your team exactly what to move, how to validate it, when to roll back, and who owns each decision. Without one, you're relying on tribal knowledge and ad-hoc Slack threads during cutover — which is how migrations run over budget, past deadline, or fail outright.

According to Gartner, 83% of data migration projects either fail or exceed their budgets and timelines. 31% of migrations miss their planned timeline, with complexity of legacy applications as the #1 cause. Organizations that conduct a formal readiness assessment before migrating have 2.4x higher success rates.

This guide covers:

• A phase-gated framework from discovery through decommission
• Specific validation tests with SQL examples and pass/fail criteria
• A tool-by-tool breakdown for each migration shape
• Rollback triggers and dry-run procedures
• Vendor API constraints that change your plan

It's built for SaaS admins and IT teams managing real platform transitions — CRM swaps, help desk moves, ERP cutovers, knowledge base migrations. If you need a more targeted checklist for a single system, start with our data migration checklist template.

Why Most Migrations Fail Before Validation Starts

The failure pattern is consistent. Teams pick tools, build ETL pipelines, and schedule a cutover weekend — then discover on Sunday night that 12% of records didn't map correctly.

The most common mistake is treating migration as an infrastructure exercise — teams focus on the pipe rather than the payload, selecting tools and platforms before they have any real understanding of what the data actually looks like.

A playbook forces the assessment upfront. It makes every assumption about field mappings, data quality, and system behavior explicit and testable before anyone touches production.

A project plan says when the migration happens. A playbook says exactly how it is executed and what evidence is required to keep going. AWS draws the same distinction in its guidance, separating detailed runbooks for repeatable patterns from lighter task lists for one-offs. (docs.aws.amazon.com)

Here's the phase-gated structure you can copy and adapt:

┌─────────────────────────────────────────────────────┐
│  PHASE 1: DISCOVER          Gate: Inventory signed off        │
│  PHASE 2: MAP & TRANSFORM   Gate: Mapping doc reviewed        │
│  PHASE 3: VALIDATE SOURCE   Gate: Data quality tests pass     │
│  PHASE 4: DRY RUN           Gate: All validation tests pass   │
│  PHASE 5: GO/NO-GO          Gate: Stakeholder sign-off        │
│  PHASE 6: CUTOVER           Gate: Post-migration tests pass   │
│  PHASE 7: AUDIT             Gate: 2–4 weeks clean operation   │
│  PHASE 8: DECOMMISSION      Gate: Source system retired       │
└─────────────────────────────────────────────────────┘

Each phase has a gate. You don't advance until the gate passes. No exceptions, no "we'll fix it after cutover."

Phase 1: Discovery and Inventory

Objective: Know what you have before you move it.

This is where most teams cut corners, and it costs them later. Before any data moves, you need a complete inventory of every data entity, its volume, its owner, and its dependencies. SaaS platforms handle relational data differently — moving from Zendesk to Freshdesk, for example, requires mapping Zendesk's flat organization structure to Freshdesk's multi-level company hierarchy.

What to capture

entity_name          (e.g., tickets, contacts, articles)
record_count         (source system count, with timestamp)
field_count          (total fields per entity)
custom_fields        (names, types, enum values)
attachment_count     (files, inline images, embedded media)
dependencies         (parent-child relationships, foreign keys)
key_strategy         (primary key, external ID, dedupe rule)
sensitivity          (PII, PHI, financial, public)
api_endpoint         (source extraction path)
rate_limits          (requests/min, concurrent connections)

Discovery tools

Phase 2: Data Mapping and Transformation Rules

Objective: Define exactly how every field in the source maps to a field in the target — and what happens when it doesn't.

A mapping document is not optional. Ensure accurate mapping of source fields to target fields. Test transformation rules to verify that data is converted correctly without losing meaning or structure.

Your mapping file should include:

• Source field → Target field (name, type, max length)
• Transformation logic (e.g., status: "open" → "active", date format conversion, timezone normalization)
• Unmapped fields (explicitly listed with a decision: drop, archive, or custom-field)
• Default values for required target fields that don't exist in the source
• Dependency order — which objects must be migrated first? You cannot migrate support tickets before migrating the users and organizations attached to them.

source_field,target_field,transformation,notes
ticket_status,status,"map: open→active, closed→resolved",Target has no 'pending' status
created_at,created_date,"ISO 8601 → Unix epoch",Timezone: UTC
agent_name,assignee_id,"lookup against target user table",Requires pre-migration user sync
custom_priority,priority_level,"1:1 if values match; else default to 'medium'",Validate enum values
description,body,"preserve HTML; truncate if > 32000 chars",Source allows 65000 chars

For a deeper dive on mapping, see our guide to data mapping for help desk migrations.

Phase 3: Validation Tests — The Core of the Playbook

Validation is not one test you run after cutover. It's a layered set of checks that run before, during, and after the migration. Embedding validation into every stage of the migration lifecycle is what separates a controlled migration from a gamble.

Pre-migration validation

These tests confirm your source data is clean and your mapping rules work before you move anything.

Example SQL for null-rate checking:

SELECT
  'contacts' AS entity,
  COUNT(*) AS total_rows,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_email,
  ROUND(100.0 * SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_email_pct
FROM source.contacts;

During-migration validation

While data is being migrated, continuously verify: record-level checks to ensure every record is moved and transformed correctly, row counts and checksum validation using hash totals to verify data integrity, and ETL pipeline accuracy.

For API-based SaaS migrations, this means logging every API response and tracking:

• Records submitted vs. records accepted
• Records rejected (with error codes — categorize into data errors like invalid email format vs. system errors like 429 Too Many Requests)
• Rate-limit hits and retry counts
• Elapsed time per batch

Post-migration validation

This is the gate that determines whether you go live or roll back.

Example SQL for post-migration reconciliation:

-- Count parity by business bucket
SELECT status, date_trunc('day', created_at) AS day_bucket, count(*) AS row_count
FROM source_tickets
GROUP BY 1, 2
EXCEPT
SELECT status, date_trunc('day', created_at) AS day_bucket, count(*) AS row_count
FROM target_tickets
GROUP BY 1, 2;
 
-- Orphaned children
SELECT c.id
FROM target_comments c
LEFT JOIN target_tickets t ON t.id = c.ticket_id
WHERE t.id IS NULL;
 
-- Duplicate external IDs
SELECT external_id, count(*) AS dupes
FROM target_contacts
GROUP BY 1
HAVING count(*) > 1;

For a detailed post-migration QA process, see our 20-test QA checklist.

Migration and Validation Tools: What to Use and When

The tools you choose define the constraints of your playbook. Pick based on your migration shape, not general preference.

Validation frameworks

Great Expectations (GX) — An open-source Python framework that treats data validation like unit tests for your tables, files, or streams. Define expectations like expect_column_values_to_not_be_null and run them against source and target datasets. Best for teams already using Python.

dbt + dbt-expectations — The dbt-expectations package extends dbt's testing capabilities by providing a collection of pre-built, customizable data quality tests inspired by Great Expectations. Tests are defined in YAML and execute as SQL queries against your data warehouse. Best for teams already using dbt as their transformation layer.

Datafold / data-diff — Tools and scripts that check that the data in the source and target databases are synchronized, involving checksums, row counts, or more complex data comparisons. data-diff uses hash-based divide-and-conquer for cross-database diffs — more useful than count-only checks because it isolates the specific differing rows. (data-diff.readthedocs.io)

Database-to-database migrations

AWS DMS — Lets you move data in real time with little downtime. Checks that data stays the same with built-in monitoring. Its validation compares source and target rows and can surface mismatches automatically, but validation adds query load, requires a primary key or unique index, won't validate views, and stops after 10,000 failed or suspended records. Best for lift-and-shift. (docs.aws.amazon.com)

ETL/ELT tools (Fivetran, Airbyte)

These tools are strong for landing source data into a warehouse or staging database. Fivetran starts with a historical sync and switches to incremental syncs; Airbyte supports Full Refresh and Incremental sync modes. They do not automatically recreate target-side behaviors like workflow ownership, permission models, or app-specific attachment handling. Standard ETL tools are generally poor at SaaS-to-SaaS migrations — they expect a relational database destination and often fail to maintain relational integrity when migrating between two complex APIs.

Custom scripts (often the right answer for SaaS-to-SaaS)

For SaaS API migrations, off-the-shelf tools rarely cover the full validation surface. Custom Python or Node.js scripts that pull records from source and target APIs, compare field-by-field, and output a reconciliation report are often faster to build and more accurate than shoehorning a general-purpose ETL tool into API-level validation.

Trade-off: Custom scripts require significant engineering hours to build, test, and maintain. When a target API throws an undocumented 500 error on a specific attachment type, your engineering team has to debug it.

Managed services (ClonePartner)

For mission-critical transitions, a specialized team eliminates the learning curve. We already know the undocumented limits of the APIs you're moving between, and our infrastructure handles delta syncs automatically. If your engineering team needs to focus on product velocity rather than writing disposable migration scripts, this is the most efficient path. See how we run migrations at ClonePartner for our exact methodology.

The Dry Run: Your Most Important Pre-Cutover Activity

Execute a full dry run. Run the complete migration against a staging environment with production-volume data. Validate results against your acceptance criteria. Fix any issues and run again until the dry run passes cleanly.

A dry run is not a "test with sample data." It's the full migration, against a production-volume copy, timed end to end.

What the dry run should prove

• Total elapsed time — Will it fit in your cutover window?
• Validation pass rate — Do all post-migration tests pass?
• Rollback procedure — Can you actually revert? How long does it take?
• Rate-limit behavior — Do API throttles extend the timeline beyond acceptable limits?
• Automation suppression — Confirm all webhooks, email triggers, and downstream syncs are disabled in the target sandbox
• Edge cases — Records with special characters, empty fields, massive attachments, circular references

Running your migration cutover plan once in production without a rehearsal is the fastest way to discover gaps at the worst possible time. A dry run forces every team member to execute their assigned tasks against real timing constraints, surfaces hidden dependencies, and gives your rollback procedures a real test before they actually matter.

AWS reports that in one large migration program, regular retrospectives and script improvements after each rehearsal drove average migration time down by 40% over the course of the program. (docs.aws.amazon.com) Treat each rehearsal as an input to the next version of the playbook.

Handling Automations and Webhooks During Migration

One of the most destructive mistakes a team can make is migrating historical data into a live system without disabling automations.

If your target CRM has a rule that says "Send a welcome email when a new Contact is created," and you migrate 50,000 historical contacts via the API, the system will execute that rule 50,000 times.

Your playbook must include a strict pre-migration checklist:

• Disable all email triggers
• Pause all routing rules (e.g., round-robin lead assignment)
• Disable webhooks pointing to external systems (Slack notifications, billing syncs)
• Ensure API ingestion is flagged with a migration_override parameter if the target system supports it, which bypasses standard trigger execution

For more on this failure mode, read our guide on migrating automations and workflows.

Rollback Plan: Define Triggers Before Cutover

Every playbook needs a rollback section with concrete, pre-agreed triggers. Define your rollback threshold before go-live, not during it.

Two types of rollback

Soft rollback: The migration is paused, but no data is deleted. Used when a non-critical error occurs (e.g., a specific custom field maps incorrectly). You patch the script, update the affected records via an API PUT request, and resume.

Hard rollback: The target system is purged, and the migration is aborted. Required when relational integrity is fundamentally broken or data corruption is widespread. Your playbook must include the exact API scripts to safely bulk-delete migrated data without impacting pre-existing configurations in the target.

Rollback trigger template

ROLLBACK TRIGGERS (any one = immediate rollback)
 
1. Record count variance > 0.5% on any primary entity
2. Hash mismatch on financial/billing data
3. Cutover window exceeded by > 30 minutes
4. Any critical integration (payments, auth, SSO) fails functional test
5. More than 3 unresolved P1 issues during validation
 
ROLLBACK PROCEDURE:
- Revert DNS / routing to source system
- Restore target from pre-migration snapshot
- Notify stakeholders via [channel]
- Document all issues for post-mortem
 
ROLLBACK OWNER: [Name]
ROLLBACK DECISION AUTHORITY: [Name]

Rollback procedures should be as detailed as migration procedures. If your rollback plan is "restore from backup," your dry run needs to include actually restoring from backup and measuring how long it takes. An untested rollback plan is worse than no plan. It breeds a false sense of security that evaporates during a crisis.

If you cannot confidently execute a hard rollback, you are not ready for cutover.

For a detailed breakdown, see our guide on how to roll back a failed migration after go-live.

API Limits and Vendor Constraints That Change the Plan

These details belong in the playbook before cutover week, not after the first throttle error:

• Zendesk: Support and Help Center API limits vary by plan (200–700 requests per minute for standard Support plans). Incremental Exports have their own lower global limit. Build cursor-based delta exports and respect 429 responses. (developer.zendesk.com)
• HubSpot: Private app limits differ by tier (100 requests per 10 seconds on Free/Starter, 190 on Professional/Enterprise). Use batch writes, cache metadata like owners and properties, and avoid chatty per-record lookups during load. (developers.hubspot.com)
• Salesforce: Bulk API 2.0 is asynchronous, single-object-per-job, and designed for large datasets (2,000+ records per request). Use external IDs for idempotent upserts. Salesforce recommends keeping uploaded CSV data around 100 MB for best results. (developer.salesforce.com)
• Jira: CSV imports work well when you honor the importer's structure (required Summary column). Atlassian recommends splitting larger loads into ~1,500 work items per file. Save and reuse the configuration file so mappings stay stable between rehearsals and production. (support.atlassian.com)

Cutover Sequence and Post-Migration Audit

The go-live sequence

The final section of your playbook is a minute-by-minute runbook for cutover day:

1. System freeze: Lock the source system to read-only.
2. Delta sync execution: Run the final delta scripts to catch any records created or updated since the initial load.
3. Validation execution: Run all automated validation tests.
4. Sign-off: The technical lead reviews the validation logs and formally approves the cutover.
5. DNS/routing updates: Switch user access and integrations to point to the new system.
6. Re-enable automations: Turn webhooks, triggers, and routing rules back on.
7. Post-migration monitoring: Watch API error rates and user-reported issues for the first 48 hours.

The audit period

Monitor during the audit period. Track error rates, user-reported issues, and data inconsistencies for two to four weeks post-cutover. Keep the rollback option available until the audit period closes. Decommission the source system only after the audit period passes without critical issues.

What to monitor:

• Error rates in the target application (API errors, failed webhook deliveries)
• User-reported issues (missing data, wrong assignments, broken automations)
• Integration health (are downstream systems receiving data correctly?)
• Report accuracy (do dashboards and reports match expected outputs?)
• Performance (response times, search speed, bulk operation throughput)

Set up a shared issue tracker where anyone can log post-migration anomalies. Review it daily for the first week, then twice weekly through the end of the audit period.

Common Validation Failures We See in the Field

After 1,500+ migrations, these are the validation failures that come up most often:

1. Timestamp timezone drift — Source stores in local time, target expects UTC. Every date-based report breaks silently. Consistent timestamp zones prevent audit disputes.
2. Enum value mismatch — Source has 14 ticket statuses, target supports 6. Unmapped values get silently dropped or default-bucketed.
3. Attachment orphaning — Ticket attachments migrate, but the parent-child link breaks. Files exist but aren't accessible from the record.
4. HTML/Markdown formatting loss — Rich-text fields get stripped or corrupted during conversion between editor formats.
5. User ID resolution failures — Agent/assignee references point to source-system IDs that don't exist in the target. Every assigned ticket becomes unassigned.

Every one of these is preventable with the right pre-migration validation test. The playbook is what makes sure those tests actually get written and run.

When to Bring in a Migration Partner

A playbook is only as good as the team executing it. If your migration involves:

• Multiple source systems feeding into a single target
• API rate limits that extend the cutover window beyond business tolerance
• Regulatory data (HIPAA, GDPR, SOX) where validation evidence is auditable
• Custom field mappings that require transformation logic beyond CSV import
• Zero-downtime requirements where parallel operation is mandatory

...then the cost of building and testing custom migration scripts in-house often exceeds the cost of engaging a team that's already done it. We've written about this trade-off in our in-house vs. outsourced migration analysis.