Building a Salesforce to PostgreSQL ETL Pipeline That Survives Contact With Production

Pulling Accounts out of Salesforce and into Postgres is a two-day job. Most engineers who’ve done it once will tell you the same thing: the two days were never the problem. The problem was month eleven, when someone in RevOps added a custom field on a Tuesday afternoon and nobody noticed the pipeline had been silently dropping it for six weeks.

This is a guide to the parts that break. If you’re deciding whether to build this yourself or buy something, the honest answer depends entirely on how many of the four jobs below you actually need to solve.

“ETL from Salesforce” is four jobs, not one

1. Initial extract. Get everything out once.
2. Incremental extract. Get what changed since last time, on a schedule, forever.
3. Deletion handling. Notice when something stops existing.
4. Schema maintenance. Survive people changing the org underneath you.

Homegrown pipelines almost always solve the first one well, the second one adequately, and the third and fourth not at all. That’s not a criticism of the engineers who build them. It’s that jobs three and four don’t announce themselves until you’re already in production.

Extraction: Bulk API, and why the query shape matters

For anything beyond a handful of records, Bulk API is the right tool. It’s asynchronous: you create a job, poll until it finishes, and collect one or more result sets.

Some limits worth knowing before you design around it. Each HTTP request counts against your org’s standard API usage allocation, so polling behaviour is a real design decision rather than an afterthought. Job status and batch results stay available for seven days and then get deleted permanently, which matters if your error-handling story is “we’ll look at it Monday.” And Bulk API queries don’t support GROUP BY, OFFSET, TYPEOF, aggregate functions like COUNT(), or compound address and geolocation fields.

That last constraint shapes more than people expect. No OFFSET means you can’t paginate the obvious way, so chunking large objects has to be driven by a filter on an indexed field – usually a date range or an ID range – rather than by paging through a result set.

Incremental: use a watermark, not a guess

The pattern is straightforward. Track the high-water mark of your last successful pass, then query only records modified since. Store the watermark somewhere durable, and update it only after the write to Postgres has committed, not after the read from Salesforce succeeds.

Three boundary problems will find you:

Timezone handling. Salesforce timestamps are UTC. Your database session might not be. This bug is subtle, it only loses a handful of records, and it takes a week to find.

Records modified during the extract window. A record changed at the exact moment your job was reading is a coin flip. Don’t use a knife-edge cutoff. Overlap your window by a few minutes and rely on upsert semantics on the target, so reprocessing a record is harmless.

Failed passes. If a pass dies halfway, the watermark must not advance. This sounds obvious and is the single most common bug in hand-built extractors, usually because the watermark update lives in a finally block that somebody wrote at 6pm.

Why we don’t build Salesforce replication on Change Data Capture

Salesforce has a Change Data Capture feature. It publishes change events when records are modified, and you can subscribe to them over the Pub/Sub API. It’s a genuinely good tool, and it’s the wrong foundation for a replica of record.

Three reasons, all of them structural.

Retention is 72 hours. Change events and platform events live on the event bus for 72 hours and are then purged. Salesforce doesn’t guarantee storage beyond that window. A subscriber that dies on Friday evening and gets noticed on Monday morning has been down for around 60 hours. Add a holiday and you’ve quietly passed the point where the missing data is unrecoverable from the event bus. Your recovery path at that point is a full SOQL extract and a reconciliation job – which is to say, the thing you were trying to avoid building.

Delivery allocation is consumed per subscriber. One published event delivered to five subscribers consumes five deliveries against your daily allocation, not one. Fan-out architectures – a warehouse connector, a Kafka consumer, a monitoring tool, a backup subscriber – multiply consumption in a way that isn’t obvious until quarter-end data loads push you over. Check your org’s current allocation in the Salesforce limits documentation before you design around it; the number depends on your edition and any add-ons.

There’s no backfill. CDC generates events from the moment you enable it. It has no mechanism to produce events for records that already exist. So you still need a full Bulk API extract for history, which means you’re building and operating two extraction paths instead of one, and you’re reconciling between them.

None of that makes CDC bad. If you want to trigger a downstream action within seconds of a record changing, it’s exactly right. But a reporting replica has a different requirement: it has to be complete, and it has to be provably complete. Those are different problems, and the second one is better served by a scheduled incremental extract that can be re-run, verified, and reconciled.

So that’s our position, stated plainly: DBSync Cloud Replication does not use Salesforce Change Data Capture. Salesforce replication is query-based and incremental, deliberately. For database-to-database replication, where the source is a database you control and log-based capture is available to you, change data capture is part of the platform. Different source, different mechanism, different guarantees.

Deletes are the bug you find six months later

A watermark query returns records modified since a timestamp. It cannot return a record that no longer exists. So unless you do something specific about it, deleted records live in your Postgres copy forever, and your dashboards slowly diverge from Salesforce in a direction nobody notices because the numbers only go up.

Two approaches, and you probably want both:

Periodic reconciliation. On a slower cadence – nightly or weekly – compare the set of IDs in the target against the source and resolve the difference. It’s more expensive than an incremental pass, which is why you don’t run it every fifteen minutes.

Soft deletes on the target. Rather than removing the row, flag it. For a reporting database this is usually the better behaviour: history survives, audit questions stay answerable, and analysts can filter on the flag. It also means your target schema shouldn’t be a pure mirror of the source, which is a good thing to accept early.

Schema drift: the reason hand-built pipelines rot

Here’s the scenario every data engineer recognises. Someone adds a picklist field to Opportunity. Nobody tells you. Your extractor has a hardcoded field list, so the column never appears. Six weeks later an analyst asks why they can’t segment by it.

This is where buying beats building for most teams. DBSync replicates the source schema into the database for the objects you’ve selected, evaluates schema differences, and generates the corresponding columns automatically. Custom objects and custom fields are handled the same way as standard ones. Existing columns are never removed, so a field deleted in Salesforce doesn’t take two years of history in your warehouse with it.

For a three-object pipeline, that’s a nice-to-have. For an org with sixty objects and an active admin team, it’s the difference between a pipeline you maintain and a pipeline that maintains itself.

Design the target for the people querying it

A replicated schema that mirrors Salesforce exactly is easy to generate and unpleasant to query. A few decisions pay for themselves within a month.

Index for the joins your analysts actually write. Salesforce relationships come across as ID columns. Nothing indexes them for you, and the first time somebody joins Opportunity to Account across two million rows without an index on AccountId, you’ll hear about it. Look at the queries your BI tool generates and index accordingly.

Keep the deleted flag queryable. If you’re soft-deleting, put the flag in the index strategy and make sure your BI layer filters on it by default. Otherwise you’ve replaced one reconciliation problem with a subtler one where half your dashboards include deleted records and half don’t.

Decide what happens to formula and rollup fields. They’re calculated in Salesforce and arrive as values. That means they’re a snapshot as of the last extract, not a live calculation, and they can drift from what a user sees in the Salesforce UI without any record being modified. Either accept that and document it, or recalculate downstream. What you shouldn’t do is let an analyst discover it during a board deck review.

Have an answer for the reconciliation question before it’s asked. When a dashboard disagrees with a Salesforce report, someone has to determine which is right. If you can point to a comparison run between source and target, that conversation takes ten minutes. If you can’t, it takes a week and costs you the warehouse’s credibility.

Run modes and scheduling

In practice you’ll use a few different operations rather than one:

• A clean copy to rebuild a target from scratch.
• A schema update when the org changes and you want the target caught up structurally.
• Source to database for ongoing incremental replication.
• Database to source when you need to push corrections back into Salesforce – a real requirement for teams migrating off a bi-directional sync layer.
• A snapshot for point-in-time copies, which is what you want when the requirement is really “we need to be able to prove what the data looked like on the 31st.”

Schedule the incremental job on the interval your business actually needs, then check it against your API allocation. Every-fifteen-minutes on sixty objects is a different API footprint than hourly, and the difference shows up in someone else’s integration failing.

Build or buy – the honest version

Build it if: you need three or four objects, one direction, and the schema is stable. A Python script with Bulk API, a watermark table, and a nightly cron job is perfectly respectable engineering. Don’t buy a platform for that.

Buy it if: you need the whole org, custom objects included, with deletes handled, drift handled, and someone to call when it fails at 2am. The cost of a homegrown pipeline isn’t the two days to write it. It’s the permanent, unbudgeted, single-person maintenance load that follows.

The tell is usually the schema drift question. If you can’t say with confidence what happens to your pipeline when an admin adds a field next Tuesday, you’ve already found your answer.

---

Want to see what a Salesforce to PostgreSQL replication looks like end to end, including what happens when the schema changes mid-run? Try it in the DBSync Cloud Replication Playground – no download, no setup.

FAQs