Get icon
Request icon
If icon
Write icon
JsonToIon icon
Batch icon
Set icon
Log icon
Schedule icon

Extract Salesforce Opportunities into Postgres Incrementally

Pull Salesforce records into Postgres on a schedule with Kestra. Watermarked incremental loads, idempotent upserts, and no Enterprise plugin required.

Categories
Data

Most Salesforce extracts fail in one of two ways: they re-read the entire object on every run, or they insert rather than upsert and quietly fill the warehouse with duplicates. This blueprint does neither. It keeps a high-water mark in Kestra's KV Store, asks Salesforce only for records modified since that mark, and loads them with a batched INSERT ... ON CONFLICT DO UPDATE keyed on the Salesforce record id. It also uses only the open-source HTTP and JDBC plugins, so no Enterprise-tier Salesforce plugin is required.

How it works

  1. The every_fifteen_minutes trigger (io.kestra.plugin.core.trigger.Schedule) starts the flow. It ships disabled so adding the blueprint never surprises you with traffic against your org.
  2. read_watermark (io.kestra.plugin.core.kv.Get) loads the newest LastModifiedDate from the previous successful run. errorOnMissing: false means the first run starts from scratch rather than failing.
  3. get_access_token exchanges a Connected App's client id and secret for an access token using the OAuth 2.0 client credentials grant, so the flow authenticates as a service and needs no interactive login.
  4. query_salesforce runs a SOQL query filtered on LastModifiedDate > watermark, ordered ascending and capped by batch_limit.
  5. has_new_records (io.kestra.plugin.core.flow.If) branches on totalSize. With nothing to do, the flow logs and exits rather than touching the database.
  6. write_records and to_ion stage the response as a file and convert it to Kestra's internal ION format, so the load streams instead of holding every record in the execution context.
  7. load_batch (io.kestra.plugin.jdbc.postgresql.Batch) performs one batched upsert with bound parameters.
  8. save_watermark advances the mark only after the load succeeds.
  9. The errors block reports a failure and states explicitly that the watermark did not move.

What you get

  • Incremental loads: each run transfers only what changed, not the whole object.
  • Idempotent writes: reruns and replays update in place instead of duplicating, because the Salesforce record id is the primary key.
  • No data loss on failure: the watermark advances only after a successful load, so a failed run is simply retried.
  • Bound parameters rather than interpolated SQL, so nulls and apostrophes in record names are handled correctly.
  • One batched round-trip to Postgres per run instead of one statement per record.
  • No Enterprise plugin dependency: the Salesforce side is the public REST API over the core HTTP task.

Who it's for

  • Analytics engineers who need CRM data in a warehouse alongside product and finance tables.
  • Revenue operations teams reporting on pipeline outside Salesforce dashboards.
  • Data platform teams on Kestra's open-source edition who need Salesforce ingestion without Enterprise plugins.

Why orchestrate this with Kestra

A cron'd script can call the Salesforce API, but it has nowhere durable to keep a watermark, no execution history when numbers look wrong, and no safe behaviour when a load half-fails. Kestra supplies the KV Store for cross-run state, a per-execution audit trail, retries, and an error path that tells an operator whether data was lost. The ordering guarantee that matters here, advancing the watermark only after a successful write, is expressed directly in the task graph.

Prerequisites

  • A Salesforce org with an External Client App (or Connected App) configured for the OAuth client credentials flow, with a Run As user set.

  • A reachable PostgreSQL database.

  • A destination table. The default schema is:

    CREATE TABLE IF NOT EXISTS sf_opportunity (
      id                 TEXT PRIMARY KEY,
      name               TEXT,
      stage_name         TEXT,
      amount             NUMERIC,
      close_date         DATE,
      account_id         TEXT,
      last_modified_date TIMESTAMPTZ,
      synced_at          TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    

Secrets

  • SF_DOMAIN: your Salesforce instance URL, including the scheme, for example https://example.my.salesforce.com.
  • SF_CLIENT_ID: Consumer Key of the Connected or External Client App.
  • SF_CLIENT_SECRET: Consumer Secret of that app.
  • POSTGRES_URL: JDBC URL of the destination database, for example jdbc:postgresql://postgres:5432/analytics.
  • POSTGRES_USERNAME: Postgres user with insert and update rights on the target table.
  • POSTGRES_PASSWORD: that user's password.

Quick start

  1. Create the destination table using the DDL above.
  2. Add the six secrets to your Kestra namespace.
  3. Execute the flow manually. The first run has no watermark, so it loads up to batch_limit records.
  4. Query the target table to confirm the rows landed, then execute again: the second run should take the else branch and report nothing to sync.
  5. Modify one record in Salesforce and execute once more. Exactly that record is re-fetched and upserted.
  6. Enable the every_fifteen_minutes trigger once you are satisfied with the cadence.

How to extend

  • Point sobject and fields at Account, Case, or Lead, and adjust the columns list and target table to match. Running it once per object gives you one table each, joinable on the foreign keys Salesforce already returns: SELECT o.name, a.name FROM sf_opportunity o LEFT JOIN sf_account a ON a.id = o.account_id.
  • Raise batch_limit toward 2000. Above that the SOQL response paginates and you would need to follow nextRecordsUrl, which this flow does not do; for very large extracts use the Salesforce Bulk API 2.0 instead.
  • Capture deletions by querying /services/data/v62.0/sobjects/{sobject}/deleted and soft-deleting the matching rows.
  • Replace the Postgres load with Snowflake, BigQuery, or an object store: only the final task changes, since the watermark logic lives in Kestra rather than in the destination.
  • Add a io.kestra.plugin.core.flow.Switch after the load to notify a channel when high-value records change stage.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.