Data Migration Mapping Cheat Sheet + Sample Scripts

A data migration mapping cheat sheet is a version-controlled document that defines every field-level mapping, value translation, and transformation rule between your source and target systems. Without one, your migration runs on tribal knowledge and Slack threads — and your team discovers broken status fields, orphaned relationships, and garbled date formats after cutover when the cost to fix is 10x higher.

This guide gives you the exact structure for a production-grade mapping document, field mapping tables for common SaaS migration scenarios, sample Python scripts for transformation and validation, and the edge cases that catch teams mid-migration.

If you're migrating a help desk specifically, our data mapping guide for help desk migrations goes deeper on ticket and agent field mappings with CSV templates.

What Goes Into a Mapping Cheat Sheet

A mapping cheat sheet is not a spreadsheet someone fills out once and forgets. It is the executable spec your migration scripts are built from. Every row becomes a line of code. Every omission becomes a bug.

A complete mapping document covers three distinct layers:

• Schema mapping — matching field names and structures between systems. Your source requester_email becomes the target's customer.email. This is the structural translation.
• Value mapping — translating allowed values within a field. Source status Pending maps to target status On-hold. The field name may stay the same, but the valid values differ.
• Data transformation — converting formats or restructuring data. Date formats (MM/DD/YYYY → YYYY-MM-DD), HTML-to-Markdown conversion, timezone normalization, or splitting full_name into first_name + last_name.

Getting any one of these wrong produces different failure modes. Schema errors cause import failures you catch immediately. Value mapping errors cause silent data corruption — records import successfully but with wrong statuses. Transformation errors surface weeks later when reports break.

Beyond these three layers, a complete cheat sheet also documents:

• Identity strategy — how records match across systems: email, domain, vendor record ID, or a custom external ID.
• Dependency order — parent objects before children, attachments after parent records, associations after both sides exist.
• Loss accounting — fields or behaviors that will not survive the migration and why.

If your document does not name the matching key for every object, it is not a mapping cheat sheet yet. It is a field list.

The Mapping Document Structure

Your mapping spreadsheet needs these columns at minimum. Every migration we run at ClonePartner starts with this structure, whether the target is Zendesk, HubSpot, Salesforce, or a custom API.

Publish the cheat sheet in two formats. Humans review a spreadsheet or markdown table faster. Scripts need YAML or JSON. Both should describe the same rules.

Why Spreadsheets Alone Fail

Spreadsheets are fine for initial discovery and stakeholder review. But they become a liability when your migration scripts reference them:

1. No validation — nothing stops someone from entering an invalid target field name
2. No version history — "final_v3_REAL_final.xlsx" is not version control
3. No machine-readability — your script has to parse it, and parsing spreadsheets is fragile

Once your mapping is agreed upon, convert it to a machine-readable format your scripts consume directly. Be explicit about file-format details too. If your importer expects tab delimiters, LF line endings, UTF-8 encoding, or headerless files, document that in the cheat sheet. Hidden format assumptions waste more time than most field transforms. (datatracker.ietf.org)

Common Field Mapping Tables

These tables reflect real mappings we have built across 1,500+ migrations. Your custom fields will differ, but the structural patterns are consistent.

Users and Contacts

User records are the foundational layer of any migration. If user IDs are not mapped correctly, every subsequent ticket, article, or deal you migrate will be orphaned.

Tickets and Conversations

Support tickets contain complex relational data. A single ticket links to a requester, an assignee, multiple tags, and an array of comments.

For help desk status mappings specifically, here is a real-world example between Freshdesk and Zendesk:

Knowledge Base Articles

Knowledge base content migrations are notorious for breaking formatting and destroying SEO rankings.

For more on knowledge base field mapping, see our knowledge base migration checklist.

CRM: Deal Stage Mapping

For a deeper walkthrough of relationship handling in CRM migrations, see The Ultimate CRM Data Migration Checklist.

YAML Mapping Config: From Spreadsheet to Executable Spec

A YAML mapping config turns your spreadsheet into something your migration script can consume without manual translation. Here is a pattern we use for SaaS-to-SaaS migrations:

# mapping.yaml — Freshdesk to Zendesk ticket migration
version: "2.1"
source: freshdesk
target: zendesk
 
objects:
  ticket:
    fields:
      - source: subject
        target: subject
        type: string
        required: true
 
      - source: description_html
        target: comment.html_body
        type: html
        transform: sanitize_html
        required: true
 
      - source: status
        target: status
        type: enum
        value_map:
          2: "open"
          3: "pending"
          4: "resolved"
          5: "closed"
        default: "open"
 
      - source: priority
        target: priority
        type: enum
        value_map:
          1: "low"
          2: "normal"
          3: "high"
          4: "urgent"
        default: "normal"
 
      - source: created_at
        target: created_at
        type: datetime
        transform: iso8601_utc
 
      - source: custom_fields.cf_account_id
        target: custom_fields.account_id
        type: string
        required: false
 
      - source: tags
        target: tags
        type: array
        transform: lowercase_trim

This pattern gives you three things spreadsheets don't: your migration script loads it directly, Git tracks every change, and you can validate it with a JSON Schema before any code runs.

Sample Scripts

Loading and Applying a YAML Mapping

This Python script reads the YAML config and transforms source records into the target schema. The same structure scales whether you are migrating 500 tickets or 500,000.

import yaml
from datetime import datetime, timezone
import re
 
def load_mapping(path: str) -> dict:
    with open(path) as f:
        return yaml.safe_load(f)
 
def get_nested(obj: dict, dotted_key: str):
    """Safely traverse nested dicts with dot notation."""
    keys = dotted_key.split(".")
    for k in keys:
        if isinstance(obj, dict):
            obj = obj.get(k)
        else:
            return None
    return obj
 
def set_nested(obj: dict, dotted_key: str, value):
    """Set a value in a nested dict using dot notation."""
    keys = dotted_key.split(".")
    for k in keys[:-1]:
        obj = obj.setdefault(k, {})
    obj[keys[-1]] = value
 
def apply_transform(value, transform_name: str):
    """Apply named transformation to a field value."""
    if value is None:
        return None
    transforms = {
        "iso8601_utc": lambda v: (
            datetime.fromisoformat(str(v))
            .astimezone(timezone.utc)
            .strftime("%Y-%m-%dT%H:%M:%SZ")
        ),
        "sanitize_html": lambda v: re.sub(
            r"<script[^>]*>.*?</script>", "", str(v), flags=re.DOTALL
        ),
        "lowercase_trim": lambda v: (
            [tag.strip().lower() for tag in v] if isinstance(v, list) else v
        ),
    }
    fn = transforms.get(transform_name)
    return fn(value) if fn else value
 
def transform_record(source_record: dict, mapping: dict) -> dict:
    """Transform a single source record using the mapping config."""
    target = {}
    errors = []
 
    for field_map in mapping["fields"]:
        src_key = field_map["source"]
        tgt_key = field_map["target"]
        value = get_nested(source_record, src_key)
 
        # Apply value mapping (enum translation)
        if "value_map" in field_map and value is not None:
            mapped = field_map["value_map"].get(value)
            if mapped is None:
                mapped = field_map.get("default")
                if mapped is None:
                    errors.append(
                        f"No mapping for {src_key}={value}, no default set"
                    )
                    continue
            value = mapped
 
        # Apply transformation
        if "transform" in field_map:
            try:
                value = apply_transform(value, field_map["transform"])
            except Exception as e:
                errors.append(f"Transform failed for {src_key}: {e}")
                continue
 
        # Apply default for missing required fields
        if value is None and field_map.get("required"):
            value = field_map.get("default")
            if value is None:
                errors.append(f"Required field {tgt_key} is null, no default")
                continue
 
        if value is not None:
            set_nested(target, tgt_key, value)
 
    return {"data": target, "errors": errors}

API Rate Limiting with Exponential Backoff

When migrating thousands of records, you will hit API rate limits. Zendesk allows 700 requests per minute on certain plans. HubSpot limits you to 100 requests per 10 seconds. Notion documents an average rate limit of three requests per second. (developers.notion.com)

If your script does not handle 429 Too Many Requests responses, it will drop records.

import time
import random
import requests
 
def push_data_with_retry(url, payload, headers, max_retries=5):
    """
    Push data to an API with rate-limit handling
    using exponential backoff with jitter.
    """
    backoff_time = 1
 
    for attempt in range(max_retries):
        response = requests.post(url, json=payload, headers=headers)
 
        if response.status_code in [200, 201]:
            return response.json()
 
        if response.status_code == 429:
            retry_after = response.headers.get("Retry-After")
 
            if retry_after:
                sleep_time = int(retry_after)
            else:
                sleep_time = min(backoff_time * (2 ** attempt), 30)
 
            # Add jitter to prevent thundering herd
            sleep_time += random.random()
            print(f"Rate limited. Waiting {sleep_time:.1f}s (attempt {attempt + 1})")
            time.sleep(sleep_time)
            continue
 
        # Handle other errors
        print(f"Failed: {response.status_code} - {response.text}")
        response.raise_for_status()
 
    raise RuntimeError("Max retries exceeded — rate limit did not recover")

Post-Migration Reconciliation

The migration is not done when records land in the target system. You need to prove the data is correct. This script compares source and target record counts, validates a random sample, and flags mismatches.

import json
import hashlib
import random
 
def reconcile_counts(source_records: list, target_records: list) -> dict:
    """Compare record counts between source and target."""
    return {
        "source_count": len(source_records),
        "target_count": len(target_records),
        "delta": len(source_records) - len(target_records),
        "pass": len(source_records) == len(target_records),
    }
 
def run_sample_validation(
    source_records: list,
    target_records: list,
    id_field: str,
    sample_size: int = 100,
) -> dict:
    """Validate a random sample of records post-migration."""
    sample = random.sample(
        source_records, min(sample_size, len(source_records))
    )
    target_index = {r.get(id_field): r for r in target_records}
 
    missing = []
    validated = 0
    for src in sample:
        src_id = src.get(id_field)
        if src_id not in target_index:
            missing.append(src_id)
        else:
            validated += 1
 
    return {
        "sampled": len(sample),
        "validated": validated,
        "missing_in_target": missing,
        "pass": len(missing) == 0,
    }
 
def checksum_record(record: dict) -> str:
    """Generate a deterministic hash for record comparison."""
    serialized = json.dumps(record, sort_keys=True, default=str)
    return hashlib.sha256(serialized.encode()).hexdigest()

This is not a production monitoring system — it is a gate check. Run it after every pilot batch before proceeding to the full migration. If the counts don't match or field values drift, stop and investigate before scaling up.

Your validation should check:

1. Record counts — does source total equal target total?
2. Orphan checks — are there tickets assigned to a default fallback user?
3. Data truncation — did long text fields get cut off by character limits?
4. Relational integrity — is every ticket still linked to the correct user?

Edge Cases That Break Migrations

These are not theoretical. They are bugs we have fixed in real client migrations.

Enum Values Without Target Equivalents

Custom statuses, priorities, and ticket types in the source rarely have 1:1 equivalents in the target. If your value map does not handle every source value, the import either rejects the record or silently assigns a default.

Fix: Add a catch-all rule that maps unknown values to a safe default and appends the original value as a tag for later triage.

DateTime Timezone Drift

Source exports dates in local timezone. Target API expects UTC. You import 10,000 tickets and every created_at timestamp is off by 5 hours. SLA calculations break. Reports show tickets created at 3 AM that were actually created at 10 PM.

Fix: Normalize all datetimes to UTC during transformation. If the source does not include timezone info, confirm the timezone with the source platform's settings API — do not assume.

Required Target Fields That Don't Exist in Source

The target system requires a priority field, but your source platform never used priority. Your import fails on every record, or the target API silently assigns a default and you don't notice.

Fix: Your mapping document must have a default_value column. Review every required: true target field before your first pilot run.

Rich Text and Body Content

Source stores rich text as HTML. Target expects Markdown. Or the target accepts HTML but chokes on <style> tags, inline CSS, or <iframe> embeds. Confluence stores page bodies in specific representations like storage and atlas_doc_format with conversion endpoints between them. (developer.atlassian.com) If the target editor does not speak the same format, you need a body-conversion rule and a macro fallback strategy.

Fix: Build a dedicated body transformation function that strips unsupported tags, converts formatting, and re-hosts inline images. Test it against your worst-case content — the article with 47 embedded screenshots and a table inside a table. Regular expressions are not sufficient for parsing complex, nested HTML. Use a proper parser like markdownify or beautifulsoup.

Relationship IDs That Don't Carry Over

Source ticket has assignee_id: 8834. That ID means nothing in the target system. Without a user ID lookup map, every ticket imports as unassigned.

HubSpot illustrates the complexity well: in multi-file imports, the common column used for associations must be hs_object_id, a built-in secondary identifier like email or domain, or a custom property with hasUniqueValue=true. (developers.hubspot.com)

Fix: Pre-build a source_id → target_id lookup table for every entity referenced by other records: users, groups, organizations, categories, pipelines, deal stages. Load this map before you start transforming records.

Attachments

Attachments need their own mapping rule. In Zendesk, files are uploaded first, then attached to a ticket comment using a single-use token. You cannot attach a file directly to a ticket or to an existing comment, and the file size limit is 50 MB. (developer.zendesk.com)

Most source attachment URLs expire or require authentication, so you must download and re-host them as part of the migration — not just copy the URL.

How to Version and Maintain Your Mapping

Mapping documents rot fast. Someone changes a custom field in the target during UAT. A new status gets added to the source. A stakeholder renames a category. If your mapping file does not track these changes, your migration script is running against a stale spec.

Rules we follow on every migration:

• Store mapping files in the same repo as migration scripts. One PR updates both the mapping and the code that consumes it.
• Tag mapping versions to pilot runs. Pilot 1 used mapping v1.3. Pilot 2 used v1.7. When comparing results, you need to know which rules produced which output.
• Require sign-off before each pilot. The admin who owns the target system should review and approve the mapping. If they haven't seen it, they will find the problems post-cutover.
• Lock the mapping 48 hours before final migration. No field changes, no value map tweaks, no "one small update." Every change after lock requires a new pilot run.

When to Use Scripts vs. Migration Tools

Not every migration needs custom code.

Vendor importers are good at loading supported rows, not at defining cross-object strategy. Salesforce's Data Import Wizard is capped at 50,000 records and only supports a subset of objects. (help.salesforce.com) Platform import APIs like HubSpot's require explicit columnMappings and approved association identifiers. (developers.hubspot.com) Low-code mappers like Workato can auto-match one-to-one fields by label and API name, but they do not define ID strategy, enum translations, or load order. (docs.workato.com)

Pre-Migration Checklist

Before your first pilot run, verify:

• Every source field is mapped to a target field or explicitly marked as "skip"
• Every enum field has a complete value map with a default for unknown values
• Every required target field has a source or a default
• DateTime fields have timezone handling documented
• Relationship fields (user IDs, category IDs) have lookup tables built
• HTML/rich text fields have a transformation function tested against real content
• The mapping file is version-controlled and tagged to this pilot
• Your reconciliation script is ready to run immediately after import
• Stakeholders have reviewed and signed off on the mapping

If any box is unchecked, you are not ready to run a pilot. You are ready to generate a bug list.

For a full migration playbook that wraps around this mapping process, see How to Build a Data Migration Playbook.