Example 10: MSSQL <-> Salesforce reconciliation
Why this example
Operations writes orders into an internal MSSQL ledger; the sales team writes the same orders into Salesforce. Drift between the two systems is constant: closed orders flipping back to open, amounts edited on one side but not the other. This reconciliation data pipeline runs nightly, detects the drift, picks the winner with a deterministic rule, and writes the converged row back to both sides.
Helix Beverages context: order rows drift between the MSSQL ERP and Salesforce constantly at Helix (named problem 1 in the Helix overview). The 15-minute Contact sync in example 2 covers the high-frequency case; this nightly reconciliation is the deeper sweep over the order ledger, where Finance needs MSSQL to win on ties.
At a glance
- Connectors used: MSSQL, Salesforce
- Nodes used: Start, DbScript (x2), Source (x2), Cache, Transform, Load (x2)
- Schedule: nightly at 04:00 UTC
- Direction: Two-way
Canvas
The rule for "winner": the row with the latest LastModifiedDate on either side wins; ties are broken in favour of MSSQL (the system of record for finance).
Step-by-step
| # | Node | Action | Key configuration | Output shape |
|---|---|---|---|---|
| 1 | Start | None | None | None |
| 2 | DbScript: ensure DELTA | MSSQL DbScript | IF NOT EXISTS ... CREATE TABLE dbo.OrdersDelta (...). Idempotent. | None |
| 3 | Source: MSSQL | MSSQL: query | SELECT * FROM dbo.Orders WHERE last_modified > \{\{var.global.lastReconcileAt\}\}. | MSSQL order row |
| 4 | Cache | Cache: write | Cache namespace mssql_orders, key field external_id. | None |
| 5 | Source: Salesforce | Salesforce: query | SELECT Id, external_id__c, Amount__c, Status__c, LastModifiedDate FROM Orders__c WHERE LastModifiedDate > \{\{var.global.lastReconcileAt\}\}. | SF Order row |
| 6 | Transform | None | For each SF row, look up the matching MSSQL row in the Cache (by external_id__c). Apply the winner rule. Emit ONE row per pair shaped as \{ external_id, amount, status, last_modified, sourceOfTruth: 'mssql' or 'sf' \}. | Converged row |
| 7 | Load: MSSQL | MSSQL: Merge | Target dbo.Orders. Merge key external_id. Skip the merge when sourceOfTruth === 'mssql' (already current). | None |
| 8 | Load: Salesforce | Salesforce: Upsert | External Id field external_id__c. Skip when sourceOfTruth === 'sf'. | None |
| 9 | DbScript: watermark | MSSQL DbScript | UPDATE dbo.SyncWatermarks SET last_reconcile_at = SYSUTCDATETIME() WHERE pipeline = 'salesforce_orders'. | None |
Variations
- More than two systems: chain a third Source + Cache pair into the Transform (e.g. NetSuite). The winner rule then compares three timestamps; the shape stays the same.
- Different DB family on the system-of-record side: swap MSSQL for PostgreSQL / Oracle / Snowflake. The DbScript syntax shifts; the rest of the canvas is identical.
- Audit trail: add a Load: Snowflake step that records every reconcile decision into a
WH.AUDIT.RECONCILE_DECISIONStable for compliance review. - Manual hold: if a row matches a "do not auto-reconcile" tag, route it via a Filter to a Notification node that emails the data-quality team instead of overwriting either side.