Case Study: Salesforce to Commerce7 - Multi-Tenant Data Migration
Summary
This case study covers a data migration project connecting a single Salesforce CRM to 17 Commerce7 e-commerce tenants across multiple wine and beverage brands. The solution is an automated pipeline that extracts data from Salesforce, stages it in PostgreSQL, applies routing logic to determine which tenants should receive each record, and delivers it via HTTP. The architecture supports a one-time bulk load followed by daily delta syncs.
The Data Challenge
Business context
The client manages a portfolio of wine brands under a single Salesforce CRM. Each brand operates its e-commerce independently via its own Commerce7 tenant. As the business scaled, maintaining consistency across 17 separate tenants became unsustainable - customer profiles, order histories, and product data had no visibility across brands, creating friction for both staff and customers.
Key challenges
- Volume -Salesforce holds years of accumulated history. Fan-out multiplies write volume by up to 17×: a single Salesforce record may need to propagate to every tenant, so the initial bulk load spans over 800,000 records across customers, addresses, orders, and club memberships - distributed across all 17 tenants .
- Velocity -Daily delta syncs must identify and process only records modified since the last successful run and complete delivery across all 17 tenants within an operational window - without reprocessing the full history on every cycle.
Variety -Salesforce objects such as Accounts, Customer Addresses, Orders, Club Memberships do not map directly to Commerce7 entities. Each migration target requires a dedicated field-mapping layer and a custom SQL query to extract and transform the correct shape of data, while a sync-status table per object tracks delivery state independently for each tenant.
Solution Approach
Pipeline overview
The migration pipeline operates in two phases: an initial full sync executed once under manual supervision, and an ongoing daily delta sync that runs automatically on a schedule. Both phases share the same underlying components but differ in scope and execution mode. The data flow runs in sequence: Salesforce is queried by the Extractor, which stages results in PostgreSQL; the Distributor then reads from PostgreSQL and delivers records to each target Commerce7 tenant.
Component 1: Extractor (Salesforce to PostgreSQL)
Extracts data from Salesforce and stages it in PostgreSQL. Downloads object metadata, creates matching tables and a _syncstatus table per object, then queries Salesforce incrementally by LASTMODIFIEDDATE and inserts results into the staging tables. In initial sync mode, it runs with full field discovery; in delta mode uses a restricted field set for performance.
Component 2: Distributor (PostgreSQL to Commerce7)
Reads staged data and delivers it to the correct Commerce7 tenants. Loads per-object configuration from a JSON file, executes a SQL query that joins against syncstatus to select only unsynced records, maps the result to the Commerce7 API payload, and POSTs it to each target tenant. On success, writes the Commerce7 record ID and timestamp back to _syncstatus - ensuring re-runs never create duplicates.
Failure resilience
If one of the 17 tenants is unavailable or returns an error, the remaining 16 tenants continue processing unaffected. Failed records remain unmarked in _syncstatus and are automatically retried on the next scheduled run. This means a single tenant outage never blocks delivery to the rest of the portfolio.
Sync modes
The initial sync bulk-loads all Salesforce records with full field discovery. The daily delta sync uses a restricted field set and queries only records modified since the last run. Both modes use the same components, differing only in runtime flags.
Technology stack (overview)
Outcomes
At a platform level:
- Customer, address, order, and membership data is consistently available across all 17 brand tenants - eliminating the data silos that previously created friction for staff and customers.
- The pipeline processed over 800,000 records from years of Salesforce history in the initial bulk load, and processes only changed records in daily delta syncs - keeping API consumption proportional to change volume.
- The _syncstatus mechanism guarantees idempotency: re-runs after any failure class never create duplicate records across any of the 17 tenants.
- Per-tenant failure isolation ensures that an unavailable tenant does not block delivery to the other 16.
- New Salesforce object types can be onboarded via configuration files alone, without touching pipeline code.
Design Decisions
Why two-stage pipeline?
Extraction (Salesforce → PostgreSQL) and delivery (PostgreSQL → Commerce7) are implemented as separate, independently runnable components. This means delivery can be retried without re-querying Salesforce - preserving API quota and avoiding re-extraction of unchanged data. Conversely, Salesforce can be re-extracted without re-delivering records already confirmed in _syncstatus. Each stage can fail, be debugged, and be re-run in isolation.
Why PostgreSQL as an intermediate layer?
Routing data through PostgreSQL rather than pushing directly from Salesforce to Commerce7 decouples the two steps. Extraction and delivery can fail independently without requiring a full re-pull from Salesforce. The _syncstatus tables live in the same database as the staged data, enabling efficient SQL joins for incremental delivery. The staging database also serves as a queryable archive of Salesforce data for reporting and debugging without consuming Salesforce API quota.
Why configuration files instead of code?
Field mappings and tenant routing rules are stored in JSON configuration files and SQL query files rather than hard-coded in Python. This allows administrators to adjust mappings, add tenant targets, or tune queries without a developer code review and deployment cycle. The pipeline code is stable; the business logic is externalised and version-controlled separately.
