Building a Data Migration Framework with Python and Airflow

Salesforce org consolidations are notoriously complex. You’re not just moving records—you’re reconciling data models, deduplicating across systems, maintaining relationships, and doing it all while the business operates.

We recently completed a migration for a major food delivery platform consolidating four Salesforce orgs. Here’s the framework we built.

The Architecture

Our migration framework follows a classic ETL pattern, but with some key enhancements for reliability and auditability.

1. Extraction Layer

We use Salesforce Bulk 2.0 API via simple-salesforce. The key insight: treat extraction as streaming, not batch.

from simple_salesforce import Salesforce

def extract_object(sf, object_name, query):
    """Extract with pagination and chunked writes"""
    bulk = sf.bulk2[object_name]
    job = bulk.query(query)

    for chunk in job.get_results():
        df = pd.DataFrame(chunk)
        write_to_s3(df, f"raw/{object_name}/{chunk_id}.parquet")
        yield len(chunk)

2. Transformation Layer

Transformations run in Airflow DAGs with:

• ID mapping tables for relationship preservation
• Fuzzy deduplication on business keys
• Audit columns tracking source system and migration batch

3. Load Layer

We use a staged approach: first to a staging org for validation, then to production with rollback capability.

Key Patterns

Change Data Capture (CDC)

For ongoing sync during the migration window, we implemented CDC using Salesforce Platform Events:

@task
def process_cdc_events():
    events = sf.query("SELECT ... FROM AccountChangeEvent")
    for event in events:
        update_staging_record(event)

Data Quality Profiling

Before migration, we profile every object:

• Completeness — % of records with required fields
• Uniqueness — Duplicate detection on business keys
• Referential integrity — Orphaned relationship checks

Results

• 2.5M records migrated across 4 orgs
• 99.7% data accuracy validated post-migration
• Zero business downtime during cutover

---

Planning a Salesforce consolidation? Let’s talk about your requirements.