Data Synchronization Issues

What This Means

Data synchronization issues occur when information fails to stay consistent across multiple systems, databases, or platforms. These problems manifest as outdated records, duplicate data, missing updates, or conflicting information that creates discrepancies between your application and connected services.

Impact on Your Business

Data Inconsistency:

• Customers see outdated information
• Inventory counts incorrect
• Pricing mismatches across systems
• Orders missing or duplicated
• User profiles out of sync

Operational Problems:

• Manual reconciliation required
• Staff working with incorrect data
• Reporting inaccuracies
• Poor decision making
• Wasted time troubleshooting

Customer Experience:

• Confusion from conflicting information
• Failed transactions
• Duplicate charges
• Incorrect order status
• Lost customer trust

Common Causes

Timing Issues:

• Sync delays or lag
• Network latency
• Processing bottlenecks
• Race conditions
• Missed sync windows

Technical Problems:

• API failures during sync
• Database connection issues
• Insufficient resources (CPU, memory)
• Deadlocks and timeouts
• Transaction rollbacks

Logic Errors:

• Incorrect conflict resolution
• Missing error handling
• Data transformation bugs
• Field mapping errors
• Validation failures

How to Diagnose

Method 1: Compare Record Counts

Check if record counts match across systems:

-- In your application database
SELECT COUNT(*) FROM products WHERE updated_at > '2024-01-01';

-- Compare with external system
-- Should match total records synced

-- Check for duplicates
SELECT product_id, COUNT(*)
FROM products
GROUP BY product_id
HAVING COUNT(*) > 1;

What to Look For:

• Mismatched counts between systems
• Duplicate records
• Missing records
• Records with wrong status

Method 2: Check Sync Timestamps

Verify when records were last synchronized:

-- Find records not synced recently
SELECT id, name, last_synced_at
FROM products
WHERE last_synced_at < NOW() - INTERVAL '1 hour'
ORDER BY last_synced_at ASC;

-- Find records never synced
SELECT id, name
FROM products
WHERE last_synced_at IS NULL;

-- Check sync lag
SELECT
  AVG(EXTRACT(EPOCH FROM (NOW() - last_synced_at))) as avg_lag_seconds,
  MAX(EXTRACT(EPOCH FROM (NOW() - last_synced_at))) as max_lag_seconds
FROM products;

What to Look For:

• Records with old sync timestamps
• Records never synced (NULL timestamps)
• Increasing lag over time
• Sync timestamps in future (clock skew)

Method 3: Review Sync Logs

Check sync process logs for errors:

# Application sync logs
tail -f /var/log/app/sync.log | grep -i error

# Look for patterns
grep "sync failed" /var/log/app/sync.log | wc -l
grep "conflict detected" /var/log/app/sync.log
grep "timeout" /var/log/app/sync.log

Common log patterns:

[ERROR] Product sync failed: API timeout after 30s
[WARN] Conflict detected: Product 12345 updated in both systems
[ERROR] Database connection lost during sync
[INFO] Batch sync completed: 1000 records in 45s
[ERROR] Validation failed: Invalid price for product 67890

Method 4: Monitor Sync Queue

Check sync queue for backlog:

// Check queue depth
const queueDepth = await syncQueue.count();
console.log(`Pending sync jobs: ${queueDepth}`);

// Check oldest job
const oldestJob = await syncQueue.getOldest();
console.log(`Oldest job age: ${Date.now() - oldestJob.timestamp}ms`);

// Check failed jobs
const failedJobs = await syncQueue.getFailed();
console.log(`Failed jobs: ${failedJobs.length}`);
failedJobs.forEach(job => {
  console.log(`Job ${job.id}: ${job.failedReason}`);
});

What to Look For:

• Growing queue depth (backlog)
• Old jobs not processing
• High failure rate
• Jobs stuck in processing
• Memory usage increasing

Method 5: Test Data Consistency

Manually verify data matches across systems:

// Fetch record from your system
const localProduct = await db.products.findOne({ id: '12345' });

// Fetch same record from external API
const remoteProduct = await externalAPI.getProduct('12345');

// Compare key fields
const differences = [];
if (localProduct.name !== remoteProduct.name) {
  differences.push({
    field: 'name',
    local: localProduct.name,
    remote: remoteProduct.name
  });
}

if (localProduct.price !== remoteProduct.price) {
  differences.push({
    field: 'price',
    local: localProduct.price,
    remote: remoteProduct.price
  });
}

console.log('Data differences:', differences);

General Fixes

Fix 1: Implement Proper Sync Strategy

Choose the right sync approach for your use case:

Real-time sync (immediate consistency):

// Sync immediately after local changes
async function updateProduct(productId, updates) {
  try {
    // Update local database
    await db.products.update({ id: productId }, updates);

    // Sync to external system immediately
    await externalAPI.updateProduct(productId, updates);

    console.log(`Product ${productId} synced successfully`);

  } catch (err) {
    console.error('Sync failed:', err);

    // Queue for retry
    await syncQueue.add({
      type: 'product_update',
      productId,
      updates,
      retries: 0
    });
  }
}

Batch sync (eventual consistency):

// Collect changes and sync in batches
const syncBatchInterval = 5 * 60 * 1000; // 5 minutes

setInterval(async () => {
  try {
    // Find records updated since last sync
    const updatedProducts = await db.products.find({
      updated_at: { $gt: lastSyncTime },
      sync_status: 'pending'
    }).limit(100);

    // Sync batch
    for (const product of updatedProducts) {
      try {
        await externalAPI.updateProduct(product.id, product);

        // Mark as synced
        await db.products.update(
          { id: product.id },
          {
            sync_status: 'synced',
            last_synced_at: new Date()
          }
        );
      } catch (err) {
        console.error(`Failed to sync product ${product.id}:`, err);
      }
    }

    lastSyncTime = new Date();
    console.log(`Batch sync completed: ${updatedProducts.length} products`);

  } catch (err) {
    console.error('Batch sync failed:', err);
  }
}, syncBatchInterval);

Hybrid approach:

// Real-time for critical updates, batch for bulk changes
async function updateProduct(productId, updates, priority = 'normal') {
  // Update local database
  await db.products.update({ id: productId }, {
    ...updates,
    sync_status: 'pending'
  });

  if (priority === 'high') {
    // Sync immediately for high priority
    try {
      await externalAPI.updateProduct(productId, updates);
      await db.products.update({ id: productId }, {
        sync_status: 'synced',
        last_synced_at: new Date()
      });
    } catch (err) {
      console.error('Immediate sync failed, will retry in batch:', err);
    }
  }
  // else: will be picked up by batch sync
}

Fix 2: Implement Conflict Resolution

Handle conflicts when same record updated in multiple systems:

async function syncProductWithConflictResolution(productId) {
  // Fetch from both systems
  const localProduct = await db.products.findOne({ id: productId });
  const remoteProduct = await externalAPI.getProduct(productId);

  // Check if both were updated
  if (localProduct.updated_at && remoteProduct.updated_at) {

    // Strategy 1: Last write wins (timestamp-based)
    if (new Date(remoteProduct.updated_at) > new Date(localProduct.updated_at)) {
      // Remote is newer, update local
      await db.products.update({ id: productId }, {
        ...remoteProduct,
        last_synced_at: new Date()
      });
      console.log(`Conflict resolved: Used remote version (newer)`);
    } else {
      // Local is newer, update remote
      await externalAPI.updateProduct(productId, localProduct);
      await db.products.update({ id: productId }, {
        last_synced_at: new Date()
      });
      console.log(`Conflict resolved: Used local version (newer)`);
    }

  } else if (localProduct.version && remoteProduct.version) {

    // Strategy 2: Version-based (optimistic locking)
    if (remoteProduct.version > localProduct.version) {
      // Remote has higher version
      await db.products.update({ id: productId }, {
        ...remoteProduct,
        last_synced_at: new Date()
      });
    } else {
      // Try to update remote with local version
      try {
        await externalAPI.updateProduct(productId, {
          ...localProduct,
          version: localProduct.version
        });
      } catch (err) {
        if (err.code === 'VERSION_CONFLICT') {
          // Remote was updated, refetch and retry
          console.log('Version conflict, retrying...');
          await syncProductWithConflictResolution(productId);
        }
      }
    }

  } else {

    // Strategy 3: Field-level merge
    const merged = {
      id: productId,
      name: remoteProduct.name || localProduct.name,
      price: localProduct.price || remoteProduct.price, // Local price takes priority
      inventory: remoteProduct.inventory, // Remote inventory is source of truth
      description: localProduct.description || remoteProduct.description,
      updated_at: new Date()
    };

    // Update both systems with merged data
    await db.products.update({ id: productId }, merged);
    await externalAPI.updateProduct(productId, merged);

    console.log(`Conflict resolved: Merged fields`);
  }
}

Fix 3: Implement Sync Health Monitoring

Monitor sync health and alert on issues:

class SyncMonitor {
  constructor() {
    this.metrics = {
      successCount: 0,
      failureCount: 0,
      totalLatency: 0,
      lastSyncTime: null
    };
  }

  async recordSync(success, latency) {
    if (success) {
      this.metrics.successCount++;
    } else {
      this.metrics.failureCount++;
    }

    this.metrics.totalLatency += latency;
    this.metrics.lastSyncTime = new Date();

    // Check health
    await this.checkHealth();
  }

  async checkHealth() {
    const total = this.metrics.successCount + this.metrics.failureCount;
    const errorRate = this.metrics.failureCount / total;
    const avgLatency = this.metrics.totalLatency / total;

    // Alert on high error rate
    if (errorRate > 0.1) { // >10% errors
      await this.alert({
        type: 'high_error_rate',
        errorRate: errorRate,
        details: `${this.metrics.failureCount} failures out of ${total} syncs`
      });
    }

    // Alert on slow sync
    if (avgLatency > 5000) { // >5 seconds average
      await this.alert({
        type: 'slow_sync',
        avgLatency: avgLatency,
        details: `Average sync latency: ${avgLatency}ms`
      });
    }

    // Alert on stale data
    const timeSinceLastSync = Date.now() - this.metrics.lastSyncTime;
    if (timeSinceLastSync > 60 * 60 * 1000) { // >1 hour
      await this.alert({
        type: 'stale_data',
        lastSync: this.metrics.lastSyncTime,
        details: `No sync in ${timeSinceLastSync / 1000 / 60} minutes`
      });
    }
  }

  async alert(alert) {
    console.error('SYNC ALERT:', alert);

    // Send to monitoring service
    await sendAlert({
      service: 'data-sync',
      severity: 'high',
      ...alert
    });
  }

  getMetrics() {
    return {
      ...this.metrics,
      errorRate: this.metrics.failureCount /
        (this.metrics.successCount + this.metrics.failureCount),
      avgLatency: this.metrics.totalLatency /
        (this.metrics.successCount + this.metrics.failureCount)
    };
  }
}

// Usage
const monitor = new SyncMonitor();

async function syncProduct(productId) {
  const startTime = Date.now();

  try {
    await performSync(productId);
    await monitor.recordSync(true, Date.now() - startTime);
  } catch (err) {
    await monitor.recordSync(false, Date.now() - startTime);
    throw err;
  }
}

Fix 4: Use Idempotency Tokens

Prevent duplicate processing during retries:

const crypto = require('crypto');

async function syncWithIdempotency(record) {
  // Generate idempotency key
  const idempotencyKey = crypto
    .createHash('sha256')
    .update(`${record.id}-${record.updated_at}`)
    .digest('hex');

  // Check if already processed
  const existing = await db.sync_log.findOne({ idempotencyKey });

  if (existing) {
    console.log(`Already synced: ${idempotencyKey}`);
    return existing.result;
  }

  try {
    // Perform sync
    const result = await externalAPI.updateRecord(record.id, record);

    // Record successful sync
    await db.sync_log.insert({
      idempotencyKey,
      recordId: record.id,
      result,
      syncedAt: new Date(),
      status: 'success'
    });

    return result;

  } catch (err) {
    // Record failed sync
    await db.sync_log.insert({
      idempotencyKey,
      recordId: record.id,
      error: err.message,
      syncedAt: new Date(),
      status: 'failed'
    });

    throw err;
  }
}

// Clean up old sync logs
setInterval(async () => {
  await db.sync_log.deleteMany({
    syncedAt: { $lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) } // 7 days old
  });
}, 24 * 60 * 60 * 1000); // Daily cleanup

Fix 5: Implement Delta Sync

Sync only changed data to improve performance:

async function deltaSyncProducts() {
  try {
    // Get last successful sync timestamp
    const lastSync = await db.sync_metadata.findOne({ type: 'products' });
    const lastSyncTime = lastSync?.lastSyncTime || new Date(0);

    console.log(`Syncing products changed since ${lastSyncTime}`);

    // Fetch only changed records from external system
    const changedProducts = await externalAPI.getProducts({
      updated_since: lastSyncTime,
      limit: 100
    });

    console.log(`Found ${changedProducts.length} changed products`);

    // Process changes
    for (const product of changedProducts) {
      await processProductUpdate(product);
    }

    // Update sync metadata
    await db.sync_metadata.update(
      { type: 'products' },
      {
        lastSyncTime: new Date(),
        recordsSynced: changedProducts.length
      },
      { upsert: true }
    );

    console.log('Delta sync completed successfully');

  } catch (err) {
    console.error('Delta sync failed:', err);
    throw err;
  }
}

async function processProductUpdate(product) {
  const existing = await db.products.findOne({ id: product.id });

  if (!existing) {
    // New product
    await db.products.insert({
      ...product,
      createdAt: new Date(),
      lastSyncedAt: new Date()
    });
  } else {
    // Updated product
    await db.products.update(
      { id: product.id },
      {
        ...product,
        lastSyncedAt: new Date()
      }
    );
  }
}

Fix 6: Handle Sync Failures Gracefully

Implement robust error handling and retry logic:

const Queue = require('bull');
const syncQueue = new Queue('data-sync');

// Add failed sync to queue
async function queueFailedSync(type, data, error) {
  await syncQueue.add(
    {
      type,
      data,
      failedAt: new Date(),
      error: error.message
    },
    {
      attempts: 5,
      backoff: {
        type: 'exponential',
        delay: 2000
      },
      removeOnComplete: true,
      removeOnFail: false
    }
  );
}

// Process sync queue
syncQueue.process(async (job) => {
  const { type, data } = job.data;

  try {
    switch (type) {
      case 'product':
        await syncProduct(data);
        break;
      case 'order':
        await syncOrder(data);
        break;
      case 'customer':
        await syncCustomer(data);
        break;
      default:
        throw new Error(`Unknown sync type: ${type}`);
    }

    console.log(`Sync job ${job.id} completed successfully`);

  } catch (err) {
    console.error(`Sync job ${job.id} failed:`, err);

    // Re-throw to trigger retry
    throw err;
  }
});

// Monitor failed jobs
syncQueue.on('failed', async (job, err) => {
  console.error(`Job ${job.id} failed after ${job.attemptsMade} attempts:`, err);

  // If all retries exhausted, alert
  if (job.attemptsMade >= job.opts.attempts) {
    await sendAlert({
      type: 'sync_retry_exhausted',
      job: job.data,
      error: err.message
    });
  }
});

Platform-Specific Guides

Detailed sync implementation for your specific platform:

Verification

After implementing fixes:

1. Verify data consistency:

   • Compare records across systems
   • Check field values match
   • Verify timestamps align
   • Confirm no duplicates
   • Test with sample records

2. Monitor sync performance:

   • Track sync latency
   • Measure throughput (records/sec)
   • Check queue depth
   • Monitor error rate
   • Review resource usage

3. Test conflict resolution:

   • Update same record in both systems
   • Verify correct version chosen
   • Check no data loss
   • Test edge cases
   • Validate merged data

4. Test failure recovery:

   • Simulate API failures
   • Verify retries work
   • Check queue processing
   • Test max retry handling
   • Confirm alerts sent

5. Validate idempotency:

   • Send duplicate sync requests
   • Verify no duplicate processing
   • Check deduplication logs
   • Test concurrent syncs

Common Mistakes

1. No conflict resolution - Handle concurrent updates
2. Synchronous sync blocking - Use async/queues
3. No retry logic - Implement exponential backoff
4. Syncing all data always - Use delta sync
5. No idempotency - Handle duplicate requests
6. Missing error logging - Track all failures
7. No monitoring - Alert on sync issues
8. Wrong sync frequency - Balance real-time vs batch
9. No data validation - Validate before syncing
10. Ignoring timestamps - Track sync times

Troubleshooting Checklist

• Sync timestamps being updated
• No duplicate records created
• Conflict resolution working
• Error handling implemented
• Retry logic configured
• Idempotency in place
• Monitoring and alerting active
• Queue processing healthy
• Data validation working
• Performance acceptable
• No growing backlog
• Logs capturing issues

Further Reading

• Eventual Consistency
• Optimistic Locking
• Change Data Capture
• Integration Issues Overview
• API Authentication
• Webhook Failures