Two-way sync with data event triggers

‍

‍

Benefits of two-way sync with data event triggers

As the bidirectional sync transfers data between systems, it is possible to capture data events via triggers and power event-driven automation flows. Event triggers are even more powerful when used with filtering such as “When a Contact is created or updated and email is a field that changed then do this”. Possible actions are:

• Call an HTTP Endpoint. Send data to services like AWS Lambda via customizable cURL requests. This executes a cURL request of any complexity
• Execute a query in any database (e.g. when a deal is created, recalculate customer LTV when deals update, with results syncing back to Salesforce)
• Trigger a workflow in Stacksync workflow automation. Stacksync workflows are a no-code tool which helps automate any process across any enterprise systems, databases, payment systems and much more.

‍

‍

Use cases:

1. Enrich CRM data in real-time
2. Enroll user in email sequence when product usage metric is reached
3. Recompute core customer metrics when key data events happen and sync them back to the CRM in real-time
4. Change Data Capture (CDC) on CRM data to a log table (without managing webhooks)
5. Trigger a Coalesce data transformation job

‍

Deep dive: triggering SQL queries when CRM data updates

Bidirectional synchronization between Salesforce and Postgres using Change Data Capture triggers that execute SQL queries. When changes occur, the system runs a SQL query that calculates the total value of all deals for the given Account in Postgres and sends this aggregated result back to Salesforce through a Postgres UPDATE operation.

‍

1. New deal created in Salesforce
2. Deal syncs to Postgres sf_deals table, triggering SQL query
3. Query sums all deal amounts for the account
4. Updates sf_accounts table with new LTV
5. Updated LTV syncs back to Salesforce Accounts

‍

In this example, we'll implement real-time customer Lifetime Value (LTV) updates in Salesforce by leveraging database calculations and bidirectional synchronization. The process works as follows:

When any deal is created or updated in the CRM, the system automatically triggers a SQL query in the database. This query calculates the updated LTV by summing all deals’ values associated with the corresponding account. Once calculated, this new LTV value automatically syncs back to the account record in Salesforce through the established bidirectional sync on the Accounts table.

This automation ensures that account managers and sales teams always have access to current LTV data in Salesforce, enabling better decision-making and customer relationship management. The entire process happens seamlessly in the background, requiring no manual intervention once configured.

‍

This solution's exceptional scalability comes from leveraging the database's native capabilities.

By performing calculations directly in Postgres, we can handle any volume of deals without worrying about API pagination or rate limits. The database efficiently aggregates all records in a single operation, making the entire workflow straightforward.

‍

Understanding the two-way sync requirement is crucial: Even though we're primarily pushing LTV updates from Postgres to Salesforce, we need bidirectional synchronization for the Accounts table. This is because we can only update records in Postgres if the records have been populated in Postgres at least once. The two-way sync ensures all Salesforce accounts are properly represented in Postgres before we attempt any updates.

‍

The SQL trigger system offers sophisticated event handling capabilities. Through the <<record>> and <<changes>> variables, triggers can access both the complete record data and specific field changes. This granular change detection sets Stacksync apart from traditional Change Data Capture (CDC) systems - instead of just knowing that a record changed, we can identify exactly which fields were modified in a given record.

In contrast, traditional CDC systems require complex and invasive setups. NetSuite would need SuiteScripts along with CI/CD tools for deployment and ongoing maintenance, and Salesforce follows a similar approach. Database integration requires reading and decrypting log files while relying on outdated tools like Debezium that must be hosted on a server maintained by DevOps teams.

‍

For more complex scenarios, SQL queries can incorporate joins and chain multiple operations within several transactions. When transformations become more intricate, tools like Stacksync workflows or Coalesce can manage complex data lineage.

‍

Common use case

Data teams want to log all updates originating from NetSuite or Salesforce to a Snowflake table for audit purposes - the field-level change detection makes this particularly detailed and valuable.

‍

‍

SQL queries from triggers can be executed in any database or data warehouse, even if it is not the database that is used within the sync itself. In the example above, even though our sync is between Salesforce and Postgres, our triggers can write data directly to Snowflake tables.

This design combines simplicity with power: a single SQL query creates an entire real-time data pipeline, while maintaining the flexibility to handle more complex business requirements as they arise.