Bidirectional sync between two-systems with different data models, using an intermediate database

‍

‍

Another one of the most challenging aspects of enterprise system integration is handling fundamentally different data models between systems. For example, Salesforce and NetSuite organize their data structures in distinct ways that cannot be mapped directly to each other. While Salesforce might treat a customer as a combination of Account and Contact objects, NetSuite might represent the same entity as a Customer record with different fields and relationships, potentially with custom objects. Furthermore, data models also differ between organizations using the same software due to business-specific customizations. These variations create integration complexities that pre-built solutions cannot fully address, requiring custom mappings by data teams with specialized expertise and causing implementation delays if using a fully event-driven integration approach.

‍

An effective solution to this challenge is implementing an intermediate database architecture that serves as a transformation layer between the systems. This approach uses a database (such as PostgreSQL) as a central hub where data from both systems can be stored, transformed, and synchronized bidirectionally.

‍

Core Architecture Components:

1. CRM Sync (e.g., Salesforce ↔ PostgreSQL)
   • Maintains real-time synchronization of Salesforce objects into corresponding PostgreSQL tables
   • Preserves all necessary Salesforce fields and relationships
   • Enables bidirectional updates between Salesforce and PostgreSQL
2. NetSuite Sync (e.g., PostgreSQL ↔ NetSuite)
   • Synchronizes NetSuite entities with their corresponding PostgreSQL tables
   • Maintains NetSuite's data structure and relationships
   • Enables bidirectional updates between PostgreSQL and NetSuite
3. Transformation Layer (within PostgreSQL)
   • Serves as the intermediate layer where data mapping and transformation occur
   • Uses SQL queries triggered by data changes to transform data between system formats
   • Transformations can also occur in a workflow automation tool and are triggered by sync event triggers
   • Maintains referential integrity across both systems

Example Implementation Flow:

1. When a Salesforce Account is created:
   • Record syncs to the `sf_accounts` table in PostgreSQL
   • Triggered SQL query transforms the data into NetSuite's format
   • Transformed data is written to the `ns_customers` table in Postgres
   • Two-way sync pushes the new customer record to NetSuite
2. When a NetSuite Customer is updated:
   • Changes sync to the `ns_customers` table in PostgreSQL
   • Triggered transformation updates the corresponding `sf_accounts` record
   • Two-way sync pushes changes back to Salesforce

‍

Benefits of this Architecture

• Decoupled Systems: Each system maintains its native data model
• Flexible Transformations: Complex data mappings can be implemented using SQL or workflow tools
• Real-time Updates: Changes propagate within milliseconds/seconds across all systems
• Data Integrity: Intermediate database provides audit trail and error handling
• Scalability: Can handle high volumes of data and complex transformation logic. All the data of both systems is fresh and entirely available in the same database. Complex aggregations can run instantly on the entire dataset without having to paginate over API calls.

‍

The transformation logic within PostgreSQL can be implemented using either:

1. Triggered SQL Queries
   • Automatically execute when data changes in source tables
   • Handle complex data mappings and calculations
   • Maintain referential integrity across systems
2. Workflow Automation Tools
   • Use tools like Stacksync workflows for complex transformation logic, including Python data transformation and more complex datatype manipulation
   • Handle edge cases and error conditions
   • Implement custom business rules and validations
   • Run real-time or cron-scheduled health checks on data

‍

Workflows are a popular automation tool to monitor, report and get alerted on data quality issues.

This architecture provides a robust foundation for integrating systems with heterogenous data models while maintaining data integrity and enabling real-time synchronization. The intermediate database serves as both a transformation layer and a system of record, ensuring consistent data representation across the integrated systems.

‍

Use cases:

1. Syncing CRM and ERPs (e.g. Salesforce and NetSuite sync)
2. Migrate between two CRMs and keep both systems alive for a prolonged period of time
3. Sync any system with different underlying data model