Example 2: Salesforce <-> MSSQL contact sync

Data Pipeline examples

Why this example

The operations team owns Contacts in an internal MSSQL database; the sales team owns the same Contacts in Salesforce. Updates flow both ways: a customer-service rep updates a phone number in MSSQL, a sales rep updates the same Contact's address in Salesforce, and both records need to converge. A parent data pipeline calls two data pipelines with Sub-workflow nodes so they run in a known order: first Salesforce to MSSQL, then MSSQL to Salesforce, both using a LastModifiedDate watermark.

Helix Beverages context: this is the recipe that fixes CRM and ERP drift (named problem 1 in the Helix overview). Sales reps edit the same Contact in Salesforce that customer-success agents edit in MSSQL, and by Friday neither side trusts the other. A 15-minute reconcile keeps both stores converged without anyone having to pick a "winner" system.

At a glance

• Connectors used: Salesforce, MSSQL
• Nodes used: Start, Source, Transform, Load, DbScript, Sub-workflow, Variable
• Schedule: every 15 minutes, on the parent data pipeline
• Direction: Two-way

Canvas

Two sync data pipelines, called in order by a parent data pipeline.

Data Pipeline A (Salesforce -> MSSQL)

Start

DbScript: ensure staging

Source: SF Contacts

Transform: SF -> MSSQL shape

Load: MSSQL Merge

Data Pipeline B (MSSQL -> Salesforce)

Start

Source: MSSQL Contacts

Transform: MSSQL -> SF shape

Load: Salesforce Upsert

Parent data pipeline (Contact sync)

Start

Sub-workflow: A

Sub-workflow: B

Variable: lastSyncAt

The parent runs Data Pipeline A first, then Data Pipeline B, then sets the watermark. With the parent's On failure setting left at Stop the run, a failure in A stops the run before B starts. Schedule the parent as the Job.

Step-by-step

Parent
1	Start	None	None	None
2	Sub-workflow	Data Pipeline A	Pass parent rows: Ignore.	None
3	Sub-workflow	Data Pipeline B	Pass parent rows: Ignore.	None
4	Variable	None	Record the run start time as lastSyncAt for the next run.	None

Data Pipeline A
1	Start	None	None	None
2	DbScript: ensure staging	MSSQL DbScript	IF NOT EXISTS ... CREATE TABLE dbo.ContactsStg (...). Idempotent.	None
3	Source	Salesforce: query Contact	SELECT Id, FirstName, LastName, Phone, Email, MailingAddress, LastModifiedDate FROM Contact WHERE LastModifiedDate > \{\{var.global.lastSyncAt\}\}	SF Contact row
4	Transform	None	Rename Id -> salesforce_id, flatten MailingAddress.\{street, city, state\} -> three columns.	MSSQL Contact row
5	Load	MSSQL: Merge	Target dbo.Contacts, merge key salesforce_id.	None

Data Pipeline B
1	Start	None	None	None
2	Source	MSSQL: query	SELECT contact_id, first_name, last_name, phone, email, address_street, address_city, address_state, last_modified FROM dbo.Contacts WHERE last_modified > \{\{var.global.lastSyncAt\}\}	MSSQL Contact row
3	Transform	None	Build SF Contact shape; include mssql_contact_id__c (an external-Id custom field) so the Load can upsert without dupes.	SF Contact row
4	Load	Salesforce: Upsert	External Id field mssql_contact_id__c.	None

Variations

• Conflict resolution: when both sides edit the same Contact between runs, the data pipeline that runs second wins. Add a Compute node before each Load that compares LastModifiedDate and skips the row when the destination is newer.
• Three-way with HubSpot: add Data Pipeline C (HubSpot to MSSQL) and Data Pipeline D (MSSQL to HubSpot), and add a Sub-workflow node for each to the parent.
• Bigger fields: if the row volume per run grows past a few thousand, switch the watermark from a single timestamp to a delta-table approach (Data Pipeline A appends to dbo.ContactsStg, Data Pipeline B reads from staging instead of from dbo.Contacts).