Data Migration

Skill: Data Migration

When to Use

Trigger when migrating data into Dataverse from external systems, legacy CRM (D365 on-prem), CSV/Excel files, or other databases. Also trigger for data quality processes, bulk operations, and migration rollback planning.

Migration Approach Decision Table

Approach 1: Dataverse Import Wizard (< 10K)

Built-in CSV/Excel import in the Power Apps maker portal.

Copy code
Power Apps → Tables → [table] → Import → Import data
  → Select CSV or Excel file
  → Map columns (auto-mapping + manual correction)
  → Import

Limitations

Approach 2: Dataverse Dataflows (< 100K)

Power Query Online for ETL into Dataverse.

Copy code
Power Apps → Dataflows → + New dataflow
  → Source: CSV, Excel, SQL, SharePoint, OData, etc.
  → Transform: Power Query M transformations
  → Destination: Dataverse table
  → Map columns
  → Schedule refresh (or run once)

When to Use Dataflows

• Data needs transformation (rename, type conversion, filtering)
• Source is a supported Power Query connector
• Need scheduled refresh (ongoing sync)
• Volume is under ~100K rows (performance degrades above this)

Approach 3: Web API Bulk Operations (100K-10M)

Use CreateMultiple and UpdateMultiple for high-throughput Dataverse writes.

CreateMultiple

bashCopy code
# Batch create records
TOKEN=$(az account get-access-token --resource "https://${ORG}.crm6.dynamics.com/" --query accessToken -o tsv)

curl -s -X POST "https://${ORG}.api.crm6.dynamics.com/api/data/v9.2/contacts/Microsoft.Dynamics.CRM.CreateMultiple" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -H "OData-Version: 4.0" \
  -d '{
    "Targets": [
      {
        "@odata.type": "Microsoft.Dynamics.CRM.contact",
        "firstname": "John",
        "lastname": "Doe",
        "emailaddress1": "john@example.com"
      },
      {
        "@odata.type": "Microsoft.Dynamics.CRM.contact",
        "firstname": "Jane",
        "lastname": "Smith",
        "emailaddress1": "jane@example.com"
      }
    ]
  }'

Batch Size Recommendations

Migration Script Pattern

bashCopy code
#!/bin/bash
set -euo pipefail

ORG="contoso"
REGION="crm6"
TENANT_ID="..."
BATCH_SIZE=500
INPUT_FILE="contacts.json"

get_token() {
  az account get-access-token \
    --resource "https://${ORG}.${REGION}.dynamics.com/" \
    --tenant "${TENANT_ID}" \
    --query accessToken -o tsv
}

TOKEN=$(get_token)
BASE_URL="https://${ORG}.api.${REGION}.dynamics.com/api/data/v9.2"

# Read records from JSON file and batch
TOTAL=$(jq length "$INPUT_FILE")
BATCHES=$(( (TOTAL + BATCH_SIZE - 1) / BATCH_SIZE ))

echo "Migrating $TOTAL records in $BATCHES batches of $BATCH_SIZE"

for ((i=0; i<BATCHES; i++)); do
  OFFSET=$((i * BATCH_SIZE))

  # Refresh token every 50 batches (before expiry)
  if (( i % 50 == 0 && i > 0 )); then
    TOKEN=$(get_token)
  fi

  # Extract batch
  BATCH=$(jq --argjson offset "$OFFSET" --argjson size "$BATCH_SIZE" \
    '.[$offset:$offset+$size] | map(. + {"@odata.type": "Microsoft.Dynamics.CRM.contact"})' \
    "$INPUT_FILE")

  # Send batch
  RESPONSE=$(curl -s -w "\n%{http_code}" -X POST \
    "${BASE_URL}/contacts/Microsoft.Dynamics.CRM.CreateMultiple" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -H "OData-Version: 4.0" \
    -d "{\"Targets\": $BATCH}")

  HTTP_CODE=$(echo "$RESPONSE" | tail -n1)
  if [ "$HTTP_CODE" != "200" ] && [ "$HTTP_CODE" != "204" ]; then
    echo "ERROR batch $((i+1)): HTTP $HTTP_CODE"
    echo "$RESPONSE" | head -n-1 >> migration_errors.log
  else
    echo "Batch $((i+1))/$BATCHES complete ($((OFFSET + BATCH_SIZE)) / $TOTAL)"
  fi
done

echo "Migration complete. Check migration_errors.log for any failures."

Approach 4: Azure Data Factory (10M+)

For very large migrations, ADF provides enterprise-grade ETL.

Copy code
ADF Pipeline:
  Source: SQL Server / CSV / API
    → Data flow: Transform, cleanse, map
      → Sink: Dataverse (via Dataverse connector or Web API)
        → Elastic tables for staging (handles high write throughput)

ADF + Elastic Tables Pattern

Copy code
1. Create elastic table in Dataverse (handles high-volume writes)
2. ADF pipeline bulk-loads data into elastic table (staging)
3. Power Automate flow or plugin moves data from staging to production tables
4. Validate counts and data quality
5. Drop staging elastic table

Data Quality

Pre-Migration Quality Checks

Deduplication Rules

Copy code
Dataverse → Settings → Data Management → Duplicate Detection Rules

  Name: "Contact Email Duplicate"
  Base Entity: Contact
  Matching Entity: Contact
  Criteria:
    - emailaddress1 = emailaddress1 (exact match)
    - OR (firstname + lastname exact match)
  Action: Flag as potential duplicate (don't auto-merge)

Staging Table Pattern

Copy code
1. Create staging table (contoso_contactimport)
   - All columns as text (accept any input)
   - Add: import_status (choice: Pending/Processed/Failed)
   - Add: import_error (text, for error messages)
   - Add: import_batch (text, batch identifier)

2. Bulk load raw data into staging table
3. Validate each row (check data types, required fields, lookups)
4. Mark valid rows as "Ready"
5. Transform and insert valid rows into production table
6. Mark as "Processed" or "Failed" with error details
7. Review failed rows, fix, and re-process

Common Migration Challenges

Option Set Value Mapping

Source systems use text labels; Dataverse uses integer values.

jsonCopy code
// Mapping table
{
  "sourceStatus": {
    "Active": 1,
    "Inactive": 2,
    "On Hold": 3
  }
}

Lookup GUID Mapping

Source systems use names; Dataverse uses GUIDs for lookups.

bashCopy code
# Pre-build a lookup map: account name → GUID
curl -s "${BASE_URL}/accounts?$select=accountid,name" \
  -H "Authorization: Bearer $TOKEN" \
  | jq '[.value[] | {(.name): .accountid}] | add' > account_map.json

# During migration, resolve lookups
ACCOUNT_ID=$(jq -r ".[\"$ACCOUNT_NAME\"]" account_map.json)

Auto-Number Conflicts

If migrating records with existing auto-number values:

Copy code
Option A: Let Dataverse generate new numbers (safest)
Option B: Set auto-number seed above max migrated value
  POST /api/data/v9.2/SetAutoNumberSeed
  { "EntityName": "contact", "AttributeName": "contoso_refnumber", "Value": 100000 }

Timezone Handling

Copy code
Source data: "2026-03-23 14:30:00" (assumed local time — but which timezone?)

Rules:
  1. Always store as UTC in migration scripts
  2. Convert source timezone to UTC before writing
  3. Dataverse stores datetime in UTC, displays in user's timezone
  4. Document the source timezone assumption in migration plan

Rollback Strategy

Pre-Migration Backup

Copy code
Power Platform Admin Center → Environments → [env] → Backups
  → Create backup → Label: "Pre-migration-2026-03-23"

Batch Delete (Rollback)

bashCopy code
# Delete all records with a specific import batch ID
# Use BulkDelete API or DeleteMultiple

curl -X POST "${BASE_URL}/contacts/Microsoft.Dynamics.CRM.DeleteMultiple" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "Targets": [
      {"@odata.type": "Microsoft.Dynamics.CRM.contact", "contactid": "guid1"},
      {"@odata.type": "Microsoft.Dynamics.CRM.contact", "contactid": "guid2"}
    ]
  }'

Migration Checklist

markdownCopy code
## Data Migration Checklist

### Planning
- [ ] Source and target schema documented
- [ ] Column mapping complete (including option sets, lookups)
- [ ] Volume estimated per table
- [ ] Migration approach selected (wizard/dataflow/API/ADF)
- [ ] Rollback strategy documented
- [ ] Environment backup taken

### Data Quality
- [ ] Duplicate detection rules configured
- [ ] Required fields validated in source
- [ ] Option set values mapped
- [ ] Lookup references resolved (GUIDs mapped)
- [ ] Date/timezone handling documented
- [ ] Character encoding verified (UTF-8)

### Execution
- [ ] Staging table created (if using staging pattern)
- [ ] Test migration on dev environment with sample data
- [ ] Full migration on test environment
- [ ] Data validation (row counts, spot checks, aggregates)
- [ ] Performance acceptable (rows/second meets timeline)

### Post-Migration
- [ ] Row counts match source
- [ ] Spot check 5% of records for accuracy
- [ ] Lookup references verified (no orphaned records)
- [ ] Auto-number sequences set correctly
- [ ] Duplicate detection run on production data
- [ ] Audit logging enabled for migrated tables
- [ ] Migration logs archived

Cutover Rehearsal Checklist

Rehearse the full migration at least twice before production cutover. Each rehearsal validates timing, scripts, and rollback.

markdownCopy code
### Rehearsal Plan

| # | Activity | Owner | Target Duration | Actual (R1) | Actual (R2) |
|---|---|---|---|---|---|
| 1 | Take environment backup | Parvez | 15 min | | |
| 2 | Disable affected flows and plugins | Scott | 10 min | | |
| 3 | Run pre-migration validation queries | Scott | 20 min | | |
| 4 | Execute migration scripts (all phases) | Scott | [estimate] | | |
| 5 | Run post-migration validation queries | Scott | 30 min | | |
| 6 | Re-enable flows and plugins | Scott | 10 min | | |
| 7 | Smoke test key scenarios | Ava | 30 min | | |
| 8 | Stakeholder sign-off | Laura | 15 min | | |
| **Total** | | | [estimate] | | |

### Rehearsal Exit Criteria
- [ ] All scripts complete without error
- [ ] Post-migration validation queries pass (zero orphaned records, zero duplicates)
- [ ] Actual duration within 120% of target
- [ ] Rollback tested successfully (restore from backup, verify data integrity)
- [ ] Sign-off from data owner and product owner

Rollback Procedure Template

Every migration must have a documented rollback plan before execution begins.

markdownCopy code
### Rollback Procedure: [Migration Name]

**Pre-migration snapshot:**
- Environment backup taken at: [timestamp]
- Backup location: [admin center / Azure storage]
- Backup retention: [X days]
- Restore estimated time: [X minutes]

**Rollback triggers (any of these = rollback):**
- [ ] Migration script fails on a critical phase (lookup resolution, primary data load)
- [ ] Post-migration validation shows >1% data quality failures
- [ ] Key business process broken in smoke test
- [ ] Migration exceeds 150% of rehearsed duration (risk of extended downtime)

**Rollback steps:**
1. Stop all running migration scripts immediately
2. Disable any flows or plugins that were re-enabled
3. Initiate environment restore from pre-migration backup
4. Verify restore completed (check record counts, spot-check key records)
5. Re-enable flows and plugins to pre-migration state
6. Notify stakeholders of rollback and root cause
7. Schedule post-mortem to identify fix before next attempt

**Post-rollback validation:**
- [ ] Record counts match pre-migration baseline
- [ ] Key business processes functional (create, update, search)
- [ ] No orphaned records from partial migration
- [ ] Audit log shows restore event

Data Quality Validation Queries

Run these queries before and after migration to catch data quality issues.

Duplicate Detection (FetchXML)

Match on name + email to find potential duplicates:

xmlCopy code
<fetch aggregate="true">
  <entity name="contact">
    <attribute name="fullname" alias="name" groupby="true" />
    <attribute name="emailaddress1" alias="email" groupby="true" />
    <attribute name="contactid" alias="count" aggregate="count" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <having>
      <condition attribute="count" operator="gt" value="1" />
    </having>
  </entity>
</fetch>

Referential Integrity Check (Orphaned Lookups)

Find records with lookup values that point to non-existent parent records:

httpCopy code
# Find cases where the customer lookup points to a deleted contact
GET /api/data/v9.2/contoso_cases
  ?$select=contoso_name,_contoso_customerid_value
  &$filter=_contoso_customerid_value ne null
  &$expand=contoso_CustomerID($select=contactid)

Records where the expand returns null but the lookup value is not null indicate orphaned lookups.

Option Set Value Validation

Verify all migrated option set values are valid:

httpCopy code
# Get valid option set values for a column
GET /api/data/v9.2/EntityDefinitions(LogicalName='contoso_case')/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata
  ?$filter=LogicalName eq 'contoso_priority'
  &$select=LogicalName
  &$expand=OptionSet($select=Options)

# Then query for records with invalid values
GET /api/data/v9.2/contoso_cases
  ?$select=contoso_name,contoso_priority
  &$filter=contoso_priority ne 100 and contoso_priority ne 200 and contoso_priority ne 300

Date Range Validation

Check for dates outside expected ranges (common issue with timezone or format conversion errors):

httpCopy code
# Find records with dates before the business started or in the far future
GET /api/data/v9.2/contoso_cases
  ?$select=contoso_name,createdon,contoso_duedate
  &$filter=createdon lt 2015-01-01T00:00:00Z or contoso_duedate gt 2030-12-31T23:59:59Z
  &$top=50

Anti-Patterns

• Importing directly to production without staging (no validation, no rollback)
• No rollback plan (backup not taken before migration)
• Ignoring data quality (garbage in, garbage out)
• Not testing with production-volume data (script works with 100 rows, fails at 1M)
• Option set mapping by label instead of value (labels change, values don't)
• Lookup resolution during migration (slow -- pre-build lookup maps)
• No duplicate detection before import (creates duplicate records)
• Timezone assumption not documented (data silently shifted by hours)
• Auto-number conflicts not handled (collisions between migrated and new records)
• Migration without monitoring (no progress tracking, can't estimate completion)

Related Skills

• dataverse-web-api — CreateMultiple, batch operations, upsert patterns
• dataverse — Table design, relationships, column types
• alm — Solution deployment that includes schema changes
• perf-optimise — Optimizing bulk operations