Example 1: Salesforce -> Snowflake nightly sync
Why this example
Sales and customer-success teams want BI dashboards driven by Salesforce records, but Tableau / Looker / Power BI cannot query Salesforce efficiently at scale. The standard pattern is a nightly batch that lands Salesforce Accounts and Opportunities into a Snowflake warehouse, where the BI tools already point.
Helix Beverages context: this is the data pipeline the Sales Ops team built to address BI lag (named problem 2 in the Helix overview). Sales and CS dashboards run a day behind because nobody can join Salesforce to MSSQL fast enough; this nightly batch lands the Salesforce tables in the Snowflake warehouse that feeds the leadership team's Monday review.
At a glance
- Connectors used: Salesforce, Snowflake
- Nodes used: Start, Source, Transform, Load
- Schedule: daily at 02:00 UTC (low Salesforce-API quota window)
- Direction: One-way (Salesforce -> Snowflake)
Canvas
Both source branches run in parallel; the single Start node fans out to both Source nodes.
Step-by-step
| # | Node | Action | Key configuration | Output shape |
|---|---|---|---|---|
| 1 | Start | None | None | None |
| 2 | Source: SF Accounts | Salesforce: query Account | SOQL: SELECT Id, Name, Industry, AnnualRevenue, LastModifiedDate FROM Account WHERE LastModifiedDate > YESTERDAY | { Id, Name, Industry, AnnualRevenue, LastModifiedDate } |
| 3 | Transform: SF Accounts | None | Rename Id to salesforce_id, cast AnnualRevenue to NUMBER(18,2), pass LastModifiedDate through. | Snowflake-shaped row |
| 4 | Load: Snowflake Accounts | Snowflake: Merge | Target table WH.SALES.ACCOUNTS, merge key salesforce_id. Mode: Merge. | None |
| 5 | Source: SF Opportunities | Salesforce: query Opportunity | SOQL with same LastModifiedDate > YESTERDAY predicate. | { Id, Name, Amount, CloseDate, StageName, AccountId, LastModifiedDate } |
| 6 | Transform: SF Opportunities | None | Rename + cast as above, map AccountId -> account_salesforce_id. | Snowflake-shaped row |
| 7 | Load: Snowflake Opportunities | Snowflake: Merge | Target table WH.SALES.OPPORTUNITIES, merge key salesforce_id. | None |
Variations
- Replace Snowflake with PostgreSQL or MSSQL: swap the Load action's connector. The Transform stays identical; database targets share the same data-shape contract.
- Run hourly instead of nightly: bump the schedule and tighten the SOQL predicate to
LastModifiedDate > LAST_N_HOURS:1. - Add a Notification node on the Snowflake Merge result: wire the Load's error port to a Notification (SMTP) node that emails ops on failure.
- Backfill mode: drop the date predicate and switch the Load mode to
Replacefor a clean full reload.