Best practices for Account API integration when syncing external CRM data

CRM Salesforce
, , , , , , , ,
1

We’re building a bidirectional sync between Salesforce and our legacy CRM system for account data. About 15K accounts need to sync daily with updates happening on both sides. I’m looking for best practices on field mapping strategies, external ID upsert logic, and error handling for sync operations.

My main concerns are:

  1. How to handle field mapping when field names and data types don’t match exactly between systems
  2. What’s the best approach for external ID management to prevent duplicates
  3. How to handle sync conflicts when the same account is updated in both systems simultaneously

Would appreciate hearing from others who’ve implemented similar Account API integrations. What patterns worked well for maintaining data quality during bidirectional sync?

system 2

Bidirectional Account sync at 15K records daily is well within Salesforce bulk API thresholds, but the architecture decisions you make upfront on external IDs and conflict resolution will define your data quality ceiling long-term.

Field Mapping Strategy

Create a canonical transformation layer in your middleware (MuleSoft, Boomi, or custom ETL) — never map directly field-to-field between systems. This gives you a single place to manage type coercions, picklist translations, and null handling.

Key patterns:

  • Data type mismatches: Handle phone/date normalization in the transformation layer before hitting either API. Salesforce’s Phone field is a free-text string; coerce legacy formats there.
  • Picklist divergence: Maintain a lookup table in middleware mapping legacy enum values to Salesforce picklist API values. Reject unmapped values and route to a dead-letter queue rather than silently dropping data.
  • Required field gaps: If the legacy CRM doesn’t populate Salesforce-required fields, define default injection rules in your transformer, not in Salesforce validation rules (which will just throw errors mid-batch).

External ID Management

Create a dedicated External ID field on the Account object — type Text, marked Unique and External ID in field settings. Use your legacy CRM’s primary key as the value.

Object: Account
Field API Name: Legacy_CRM_ID__c
Field Type: Text(18)
Unique: true (case-insensitive)
External ID: true

Use Upsert operations via the Bulk API 2.0 /jobs/ingest endpoint with externalIdFieldName=Legacy_CRM_ID__c. This prevents duplicate Account creation on retry scenarios.

POST /services/data/v59.0/jobs/ingest
{
  "object": "Account",
  "operation": "upsert",
  "externalIdFieldName": "Legacy_CRM_ID__c",
  "contentType": "CSV"
}

Verify endpoint version against your org’s API version — v59.0 used here as example.

Conflict Resolution

Last-write-wins is the default and will corrupt data in bidirectional sync. Implement timestamp-based conflict detection:

  1. Store LastModifiedDate from both systems in your middleware state store (Redis or database table) after each sync cycle.
  2. On inbound record, compare incoming LastModifiedDate against your stored last-sync timestamp per record.
  3. If both systems show modification since last sync cycle, apply a field-level merge rather than record-level overwrite — Salesforce-owned fields win on Salesforce side, legacy-owned fields win on legacy side.
  4. Flag true conflicts (same field modified in both systems) to a review queue via Platform Events or a custom Sync_Conflict__c object.

Define field ownership explicitly per field in your mapping config — avoid shared ownership on any single field.

Error Handling Pattern

Use Bulk API 2.0’s built-in failed results CSV (/jobs/ingest/{jobId}/failedResults) for batch error extraction. Don’t rely solely on job-level status.

Implement exponential backoff with jitter for UNABLE_TO_LOCK_ROW and REQUEST_LIMIT_EXCEEDED errors. DUPLICATE_VALUE on upsert indicates your External ID field isn’t populated correctly on the inbound record — treat as a data quality alert, not a retry candidate.

Monitor API usage via /services/data/vXX.X/limits — 15K daily upserts is low volume, but factor in retry headroom against your org’s daily API limit allocation (verify your org edition limits).

This draft is based on general Salesforce knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

For external ID management, create a custom field on Account like Legacy_CRM_ID__c and mark it as External ID. Use upsert operations with this field - Salesforce will automatically match existing records or create new ones. This prevents duplicate account creation.

4

Field mapping strategies are critical for data quality. I recommend creating a mapping configuration table that defines transformations between systems. For example, if your legacy CRM has ‘Company_Type’ as a picklist with values like ‘CORP’, ‘LLC’, ‘PART’, map these to Salesforce’s standard Industry picklist values. Use a middleware layer or transformation service to handle these conversions rather than hardcoding them in your integration code. This makes it easier to adjust mappings without redeploying.

5

For conflict resolution, implement a ‘last write wins’ strategy with timestamp tracking. Add a custom field Last_Sync_Timestamp__c to your Account object. Before updating, compare the last modified date in both systems. If the external system’s timestamp is newer, allow the update. If Salesforce is newer, skip the update and log a conflict for manual review. This prevents newer data from being overwritten by stale updates.

6

Don’t forget about error handling for sync failures. Network issues, API limits, and validation errors will happen. Build a retry queue for failed updates with exponential backoff. Also implement a reconciliation process that runs weekly to compare record counts and checksums between systems. We discovered thousands of missed updates because our error handling wasn’t robust enough initially.

7

The mapping configuration table approach sounds good. How do you handle field-level conflicts though? If Account Name is updated in both systems between sync cycles, which value should win?

8

Excellent question about Account API integration patterns. Here’s a comprehensive approach based on implementations I’ve led for several enterprise clients:

Field Mapping Strategies:

The key is treating field mapping as configuration, not code. Create three components:

  1. Field Mapping Registry: A custom metadata type or external configuration file that defines:

    • Source field → Target field mappings
    • Data type transformations (string to picklist, number formatting, etc.)
    • Default values when source is null
    • Validation rules specific to each field

  2. Transformation Layer: Build reusable transformation functions for common patterns:

    • Picklist value mapping (legacy codes → Salesforce values)
    • Phone number formatting (various formats → Salesforce standard)
    • Address standardization (street/city/state/zip normalization)
    • Currency conversion if systems use different currencies

  3. Field-Level Conflict Resolution: Different fields may need different strategies:

    • System of Record Fields: Some fields are authoritative in one system (e.g., Account Owner always from Salesforce, Billing Terms always from legacy CRM)
    • Timestamp-Based Fields: For fields that change frequently (Annual Revenue, Employee Count), use last-modified timestamp
    • Manual Review Fields: Critical fields like Account Name trigger alerts for manual resolution rather than auto-updating

External ID Upsert Logic:

For preventing duplicates and maintaining referential integrity:

  1. Composite External ID: If your legacy CRM has compound keys, create a formula field that concatenates them: `Legacy_System__c + ‘_’ + Legacy_Account_ID__c

  2. Upsert Operation Pattern: Always use upsert with the external ID field, never query-then-insert/update. This is atomic and prevents race conditions.

  3. Orphan Detection: Run periodic reconciliation jobs to find accounts in either system that don’t have matching external IDs. These indicate sync failures or manual record creation that bypassed integration.

  4. External ID Immutability: Once set, never change an external ID. If you need to merge accounts, update the external ID mapping in your middleware, not in Salesforce.

Error Handling for Sync Operations:

Robust error handling is what separates reliable integrations from fragile ones:

  1. Categorize Errors:

    • Transient: Network timeouts, API limits → Retry with exponential backoff
    • Data Quality: Validation errors, required fields missing → Send to error queue for data cleansing
    • Conflict: Simultaneous updates → Flag for manual review
    • System: Salesforce maintenance, authentication failures → Pause sync, alert operations team

  2. Sync State Management: Track sync status for each account:

    • Last_Successful_Sync__c (datetime)
    • Sync_Status__c (picklist: ‘Synced’, ‘Pending’, ‘Error’, ‘Conflict’)
    • Sync_Error_Message__c (long text for debugging)

  3. Retry Logic: Failed updates go into a retry queue:

    • Attempt 1: Immediate retry (catches transient network issues)
    • Attempt 2: 5-minute delay
    • Attempt 3: 30-minute delay
    • After 3 failures: Move to manual review queue

  4. Reconciliation Process: Daily job that compares:

    • Record counts between systems
    • Hash of key fields to detect data drift
    • Accounts modified in one system but not synced to the other

Bidirectional Sync Conflict Resolution:

For handling simultaneous updates:

  1. Timestamp Comparison: Before any update, check if the target record is newer than the source. If so, skip update and log conflict.

  2. Field-Level Mastering: Configure which system is authoritative for each field. Example:

    • Salesforce masters: Owner, Stage, Opportunity data
    • Legacy CRM masters: Billing information, payment terms, credit limit
    • Shared fields: Use timestamp-based resolution

  3. Conflict Queue: When conflicts occur, create a record in a custom Sync_Conflict__c object with:

    • Account ID
    • Field name
    • Salesforce value
    • External system value
    • Both timestamps
    • Recommended resolution (based on business rules)

  4. Business Rules Engine: Implement rules like:

    • If Annual Revenue differs by less than 10%, accept Salesforce value (likely just rounding)
    • If Account Name differs, always require manual review
    • If Industry changes, accept the change from whichever system updated most recently

Data Quality Maintenance:

  1. Pre-Sync Validation: Before sending data to Salesforce, validate:

    • Required fields are populated
    • Picklist values exist in target system
    • Lookup relationships can be resolved
    • Data formats match (phone, email, date)

  2. Post-Sync Verification: After successful sync:

    • Query the record back from Salesforce to confirm values persisted
    • Compare critical fields to ensure no unexpected transformations
    • Update sync status and timestamp

  3. Data Quality Metrics Dashboard: Track:

    • Sync success rate (target: >99%)
    • Average sync latency (target: <5 minutes)
    • Conflict rate by field
    • Error categories and trends

This architecture has successfully handled bidirectional syncs for organizations with 100K+ accounts. The key is treating data quality and error handling as first-class concerns, not afterthoughts.