Stage, Then MERGE: How to Sync Salesforce With MotherDuck

Why the CSV export stops working

Almost every MotherDuck and Salesforce project starts the same way. Somebody exports Accounts and Opportunities from a report, points read_csv_auto at the file, and has a queryable table in about four minutes. That is genuinely the fastest path to a first chart, and it is why so many teams stop there.

The trouble arrives in week two. An export is a snapshot, so last week's numbers are gone unless somebody kept the file. A rep renames a picklist value and the column quietly gains a new category. Two accounts get merged and the losing record Id stays in your table forever, double counting revenue. Nothing was ever deleted in MotherDuck, because a CSV cannot tell you what was deleted.

The second attempt is usually a Python script on a schedule: ask the Salesforce REST API for rows where LastModifiedDate is greater than the last run, then write them into MotherDuck. Closer, and it fails in more interesting ways. LastModifiedDate only moves when a user edits the record, so a lead conversion, a rollup recalculation or a cascading owner change modifies a row without touching it. SystemModstamp moves on both, which is why an incremental read should watermark on that column instead.

Then there is the write pattern. A script that loops over the batch and issues one UPDATE per record is fine against Postgres and falls over on a columnar store. The gap between those two shapes is wide enough that it gets its own post on two-way sync between MotherDuck and PostgreSQL, and the more general version of the argument lives in real-time sync versus batch ETL.

Authenticating both sides

Salesforce first. Server-to-server access goes through a connected app: create one in Setup, enable OAuth settings, add the api and refresh_token offline_access scopes, and keep the consumer key and secret somewhere the job can read them. For an unattended sync the JWT bearer flow with a certificate is the usual pick, because there is no browser in the loop and no password to rotate. Client credentials is simpler where your org allows it and you can nominate a run-as user.

Whichever flow you use, create a dedicated integration user rather than borrowing an admin login. Give it a permission set with API Enabled, read access to exactly the objects in scope, and write access only to the fields you intend to push back. It is boring work and it is the single control that stops a sync bug from becoming a data incident. The Salesforce connector page lists the permissions a managed platform asks for.

The MotherDuck side is shorter. Authentication is a service token: generate one, keep it out of the repository, and pass it as motherduck_token when the client opens md:my_database. There is no cluster to size and no VPC to open, which removes most of the network work a warehouse project carries. The tradeoff is that you connect through a DuckDB client rather than a generic JDBC or ODBC endpoint, so older ETL tooling often cannot point at MotherDuck at all. Check that before you plan around one.

Give the sync its own token and its own schema. Compute in MotherDuck is allocated per user, so a heavy load does not queue behind an analyst's dashboard query, and revoking a token later locks nobody else out. The MotherDuck connector page covers what the platform expects on that side.

Choosing how you read changes out of Salesforce

Salesforce gives you three realistic ways to learn what changed, and the choice sets the latency, the API cost and the failure modes of everything downstream.

The bulk export is the honest answer for a table that moves slowly and is read once a day. An Account object with 20,000 rows and no history requirement is fine as a full replace. Opportunity and Task are not, because the row count and the edit rate both keep growing.

Change Data Capture is what you want when freshness matters. Salesforce publishes record changes to an event channel, your subscriber holds a replay position, and it resumes from there after a disconnect. The limit worth writing on a whiteboard is retention: events stay on the bus for up to 72 hours, so a subscriber that has been down over a long weekend has to fall back to a SystemModstamp window or a fresh backfill. Design that fallback before the incident, not during it.

Either way, one cycle looks the same from the outside.

The write-back leg at the end is optional but common: a churn score or a usage total computed in MotherDuck and pushed onto the Account record. The moment you add it you are running a round trip, and every write needs an origin tag so the value you just wrote is not read back a second later as a fresh change. Bidirectional sync explained shows what that looks like on real records.

Mapping Salesforce objects onto columnar tables

One object becomes one table. Keep the 18 character record Id as the key column and use it as the join key everywhere, because it is what every later MERGE matches on. Resist the urge to invent a surrogate key, because you will only have to map it back later.

Types need decisions rather than defaults. These are the ones that are cheap now and expensive after the first six months of history.

Formula and rollup fields deserve a second look. A change event on the child record that feeds a rollup does not reliably announce that the parent's value moved. If a formula field matters for reporting, either recompute it in MotherDuck from the underlying columns or refresh the parent on a schedule. Treating it as a stored column is a slow, quiet source of drift.

Start narrow. Account, Contact and Opportunity cover most first projects, and adding an object later costs an afternoon. Selecting every object in the org costs you a backfill nobody asked for and a schema nobody understands. The MotherDuck and Salesforce integration page shows which objects the connector exposes.

Stage the batch, then MERGE

This is the part that separates a load which stays fast from one that gets slower every week. DuckDB, and therefore MotherDuck, stores data column by column in compressed row groups of roughly 120,000 rows. An update is not an in-place edit of one value, it rewrites the parts it touches. Issue 5,000 single-row UPDATE statements and you pay that cost 5,000 times, plus 5,000 round trips.

The pattern that works is the one warehouses have used for years, and it is three steps.

• Load a staging table. One bulk insert of the batch into stg_account, with no constraints and no indexes to maintain. Rebuild it every cycle rather than appending to it.
• Apply it in one statement. MERGE INTO account USING stg_account ON account.id = stg_account.id where your DuckDB version supports it, or INSERT INTO account SELECT * FROM stg_account ON CONFLICT (id) DO UPDATE SET ... when the target carries a primary key on the Id. Either way the whole batch lands as one operation.
• Move the watermark last. Record the new high water mark only after that statement commits. If the job dies halfway you replay the window instead of losing it.

Batch size is a tuning knob, not a constant. A few thousand rows per statement is a sensible start: much smaller and you pay per-statement overhead again, much larger and a failure costs a bigger replay.

If you are also pushing values back into Salesforce, batch that side too. Salesforce meters API requests per org over a rolling 24 hours, and the allocation depends on edition and licence count, so a write-back that fires one call per record is the fastest way to find the ceiling. The arithmetic is worked through in the guide to syncing Salesforce without hitting API limits.

Getting it running

Syncing Salesforce with MotherDuck means respecting two constraints at once. Salesforce decides what you can learn about a change and how often you may ask; MotherDuck decides how you write it down. Watermark on SystemModstamp, stage every batch and apply it with one statement, handle deletes and merges explicitly, and the rest is maintenance.

The alternative to building it is not building it. Stacksync reads Salesforce change events, maps the objects and picklists, applies each batch as a staged MERGE, and handles the return leg under one conflict policy. To see it against a real org, book a demo, read the MotherDuck connector announcement, or take the companion guides on an enterprise iPaaS for MotherDuck and two-way sync between MotherDuck and PostgreSQL. If the warehouse choice is still open, MotherDuck and Snowflake and MotherDuck and PostgreSQL are the two comparisons that come up most often.