duckdb-salesforce - Usage guide for analysts

This guide shows how to query a Salesforce org directly from DuckDB using the duckdb-salesforce extension. The extension attaches your org as a read-only catalog: Salesforce objects (sObjects) appear as tables you can SELECT from with plain SQL, while the extension translates your query into SOQL and the right Salesforce API (REST or Bulk) behind the scenes.

It is practical and example-driven. Every setting and function mentioned here is real; nothing is invented.

Official DuckDB references

This guide follows concepts documented by DuckDB:

ATTACH: attaches another catalog to DuckDB. This extension uses the same model to expose a Salesforce org as a read-only catalog.
SELECT: the only statement you run against the Salesforce catalog. All writes and DDL throw.
CREATE TABLE: CREATE TABLE ... AS SELECT materializes a Salesforce query into a local DuckDB table.
Table functions: this extension ships diagnostic table functions such as salesforce_query_cost().

1. Concepts before you start

Read-only catalog

ATTACH exposes the org as a catalog. You can only read from it: every INSERT, UPDATE, DELETE, and DDL statement against the Salesforce catalog throws an error. If you need a local, writable copy, materialize a query into a DuckDB table (see section 5).

Tables are sObjects

Each table in the attached catalog is a Salesforce sObject (Account, Contact, Opportunity, custom objects ending in __c, and so on). Schema is resolved lazily the first time you reference an object, so attaching is cheap and fast.

Authentication and security

Authentication is OAuth 2.0 refresh-token only. You supply a client_id, client_secret, and refresh_token. Credentials live in memory only and are never logged. TLS certificate verification is always on.

2. Installation and loading

A local build is unsigned, so DuckDB will not load it unless you opt in:

SET allow_unsigned_extensions=true;
LOAD 'path/to/salesforce.duckdb_extension';

For platform-specific build and install steps, see docs/en/guide_windows.md (Windows) and docs/en/guide_linux.md (Linux).

CI-validated platforms today are linux_amd64 + windows_amd64 (baseline) and osx_arm64 (extra). The extension is not yet published to community-extensions, so there is no INSTALL ... FROM community yet.

macOS: TLS certificate bundle

The extension always verifies the TLS server certificate when it talks to Salesforce. There is no insecure flag, ever. On macOS the OpenSSL that the extension is built against (via vcpkg) ships no default CA bundle and does not read the macOS Keychain, so a live ATTACH can fail certificate verification with no trust anchors to check against.

The fix is to point OpenSSL at a CA bundle through the SSL_CERT_FILE environment variable (OpenSSL also honors SSL_CERT_DIR for a directory of certificates). This only selects the trust anchors — verification stays ON, it is not a bypass:

export SSL_CERT_FILE=$(brew --prefix)/etc/openssl@3/cert.pem   # Homebrew OpenSSL bundle
export SSL_CERT_FILE=$(python3 -m certifi)                     # Python certifi bundle

A live ATTACH that fails verification on macOS now prints this exact suggestion in the error message. Linux and Windows need no such step: Linux uses the system bundle and Windows uses the OS trust store. A zero-config macOS Keychain trust store is a planned follow-up, not yet shipped.

3. Connect to Salesforce

Attach the org with the salesforce catalog type and your OAuth credentials:

ATTACH 'salesforce://<org>' AS sf (
    TYPE salesforce,
    client_id 'your_consumer_key',
    client_secret 'your_consumer_secret',
    refresh_token 'your_refresh_token'
);

Two optional parameters cover sandboxes and pinned API versions:

ATTACH 'salesforce://<org>' AS sf (
    TYPE salesforce,
    client_id 'your_consumer_key',
    client_secret 'your_consumer_secret',
    refresh_token 'your_refresh_token',
    login_url 'https://login.salesforce.com',
    api_version 'v60.0'
);

Use login_url 'https://test.salesforce.com' for a sandbox. To obtain the client_id, client_secret, and refresh_token, set up a Connected App in your org and complete the OAuth flow as described in docs/CONNECTED_APP.md.

For policy standardization the connector also accepts read_only as an ATTACH option (e.g. when your tooling appends READ_ONLY TRUE to every source). It is accepted and ignored: the catalog is read-only by design - every mutating operation throws - so the flag changes nothing.

Auth sources

The auth_source ATTACH option chooses where the OAuth credentials come from. The OAuth refresh-token flow is unchanged — only the source differs. There are four modes:

options (default): credentials are read from the inline ATTACH options client_id, client_secret, and refresh_token (all required), plus optional login_url and api_version. This is exactly the behavior shown above; you can omit auth_source entirely.
env: credentials are read from environment variables. The required ones are SF_CLIENT_ID, SF_CLIENT_SECRET, and SF_REFRESH_TOKEN; SF_LOGIN_URL is optional. You do not pass the credential options in SQL.
```
ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'env');
```
sfdx_url: credentials are read from a single environment variable, SF_SFDX_AUTH_URL, in the Salesforce CLI auth-URL format force://<clientId>:<clientSecret>:<refreshToken>@<instance-host> (the <clientSecret> segment may be empty). You do not pass the credential options in SQL.
```
ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'sfdx_url');
```
jwt: the OAuth 2.0 JWT bearer flow, ideal for headless / CI / pipeline use. There is no refresh token: the extension RS256-signs a short-lived JWT assertion (iss = client_id, sub = username, aud = login_url, exp = now + 300s) and exchanges it at <login_url>/services/oauth2/token. The access token is held in memory; on a 401 the extension simply re-signs a fresh assertion. You provide client_id (the issuer) and username (the Salesforce user to impersonate) in SQL, and a private key through the SF_JWT_KEY_FILE environment variable (recommended) or the inline private_key_file option (local dev only). The key must be an unencrypted PKCS#1 or PKCS#8 PEM RSA key. login_url (default https://login.salesforce.com; use https://test.salesforce.com for a sandbox) and api_version work as in the other modes.
```
ATTACH 'salesforce://production' AS sf (
  TYPE salesforce, auth_source 'jwt',
  client_id 'YOUR_CONSUMER_KEY', username 'svc@example.com'
);
```
Prerequisite: the Connected App must be pre-authorized for the user — either admin-approved, or pre-authorized by the user through the digital certificate ("Use digital signatures") JWT setup.

The api_version option works in all four modes. With env, sfdx_url, and jwt, the refresh-token credential options are not needed (the source wins).

Example: terminal

Export the variables in your shell, then start DuckDB and attach:

export SF_CLIENT_ID=your_consumer_key
export SF_CLIENT_SECRET=your_consumer_secret
export SF_REFRESH_TOKEN=your_refresh_token
# optional: export SF_LOGIN_URL=https://test.salesforce.com

duckdb

ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'env');

Or, using a Salesforce CLI auth URL captured from an authorized org:

export SF_SFDX_AUTH_URL=$(sf org display --verbose --json | jq -r .result.sfdxAuthUrl)

duckdb

ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'sfdx_url');

Example: web UI / backend service

The application or container injects the SF_* environment variables (for example, pulled from a secret manager at startup). The backend opens DuckDB and attaches with auth_source 'env', so no credentials ever appear in the SQL text:

ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'env');

Example: Python / pipeline

import os
import duckdb

os.environ["SF_CLIENT_ID"] = "your_consumer_key"
os.environ["SF_CLIENT_SECRET"] = "your_consumer_secret"
os.environ["SF_REFRESH_TOKEN"] = "your_refresh_token"

con = duckdb.connect()
con.execute(
    "ATTACH 'salesforce://prod' AS sf (TYPE salesforce, auth_source 'env')"
)

Example: JWT bearer (`auth_source 'jwt'`)

The JWT mode needs no refresh token — only client_id, username, and the private key. Keep the key path in SF_JWT_KEY_FILE so it never appears in the SQL. Prerequisite: the Connected App must be pre-authorized for the user (admin-approved, or pre-authorized via the digital certificate / "Use digital signatures" JWT setup).

Terminal — export the key path, then start DuckDB and attach:

export SF_JWT_KEY_FILE=/secure/server.key
# optional: export SF_LOGIN_URL=https://test.salesforce.com

duckdb

ATTACH 'salesforce://production' AS sf (
  TYPE salesforce, auth_source 'jwt',
  client_id 'YOUR_CONSUMER_KEY', username 'svc@example.com'
);

Web UI / backend service — the application or container injects SF_JWT_KEY_FILE (for example, a mounted secret). The backend attaches with auth_source 'jwt', so no key path or secret ever appears in the SQL:

ATTACH 'salesforce://production' AS sf (
  TYPE salesforce, auth_source 'jwt',
  client_id 'YOUR_CONSUMER_KEY', username 'svc@example.com'
);

Python / pipeline:

import os
import duckdb

os.environ["SF_JWT_KEY_FILE"] = "/secure/server.key"

con = duckdb.connect()
con.execute(
    "ATTACH 'salesforce://prod' AS sf (TYPE salesforce, auth_source 'jwt', "
    "client_id '...', username 'svc@example.com')"
)

Local dev (inline key) — for local development only, you can pass the key path inline instead of through the environment; prefer SF_JWT_KEY_FILE in pipelines:

ATTACH 'salesforce://production' AS sf (
  TYPE salesforce, auth_source 'jwt',
  client_id 'YOUR_CONSUMER_KEY', username 'svc@example.com',
  private_key_file '/path/to/server.key'
);

Security note

Environment values and the SFDX auth URL are never logged, and tokens or secrets never appear in error messages. A missing environment variable names only the variable; a malformed SF_SFDX_AUTH_URL is reported as malformed without echoing it; invalid_grant is reported as "refresh token is invalid, expired, or revoked"; invalid_client as "client_id / client_secret is incorrect".

For jwt, the private key, the assembled JWT, and the signed assertion are never logged and never appear in error messages. A missing key path names the option / variable expected (private_key_file or SF_JWT_KEY_FILE); a missing key file names only the path; a non-PEM, unparseable, or encrypted key is reported without echoing its contents; invalid_grant from the JWT exchange is reported as "invalid, expired, or the user is not authorized for this Connected App".

Not supported in this cut

These are out of scope for this release: a browser / web OAuth flow, secret storage, encrypted private keys, an OS credential manager, and token / key persistence.

4. First queries

Once attached, query sObjects like any other table:

SELECT Id, Name
FROM sf.Account
WHERE Name = 'Acme'
LIMIT 10;

Id literals in filters are validated locally at plan time: an =, !=, or IN literal compared to an Id-type field must be a real Salesforce Id (15 or 18 alphanumeric characters). A malformed value fails immediately with a clear error instead of Salesforce's generic INVALID_QUERY_FILTER_OPERATOR.

Schema is resolved lazily on first reference. To list the objects you can query, run a single global describe via:

SHOW TABLES;
-- or
SELECT * FROM duckdb_tables();

Custom Metadata and Custom Settings

Custom Metadata Types (objects whose API name ends in __mdt) and queryable Custom Settings (objects whose API name ends in __c, both List and Hierarchy types) need no special syntax: they appear as ordinary read-only sObject tables in the attached catalog. When they are queryable they show up in the global object listing (SHOW TABLES / duckdb_tables()), and DESCRIBE plus SELECT work exactly like any other object — projection and predicate pushdown apply just the same:

-- a Custom Metadata Type
SELECT DeveloperName, Label
FROM sf.Shipping_Rate__mdt
WHERE Label = 'Domestic';

-- a Custom Setting
SELECT Name, SetupOwnerId
FROM sf.Feature_Flag__c
LIMIT 10;

Keep these limitations in mind:

This is data access, not the Salesforce Metadata API. The connector reads these objects as rows; it does not deploy, create, or modify metadata.
Visibility follows the authenticated user's and org's permissions. Protected Custom Metadata and protected Custom Settings may not appear or may not be readable, per Salesforce.
All writes throw, the same as for any sObject (the catalog is read-only).
If you add a new __mdt type or a new field mid-session, call salesforce_refresh_metadata('<catalog>') (or salesforce_refresh_metadata('<catalog>', '<Object>')) so the next reference re-describes it.

5. Materialize results locally

To keep a local, queryable snapshot (and to escape read-only limits), copy a Salesforce query into a DuckDB table:

CREATE TABLE local_accounts AS
SELECT Id, Name, Industry, AnnualRevenue
FROM sf.Account
WHERE Industry = 'Manufacturing';

From then on you can query, join, and aggregate local_accounts with no further API calls.

6. Transports: REST, Bulk, and auto

The extension can fetch rows over different Salesforce APIs. Choose with sf_force_transport:

-- REST (default): lazy /query + queryMore paging
SET sf_force_transport='rest';

-- Bulk API 2.0: lazy-streamed result pages, optional PK chunking
SET sf_force_transport='bulk';

-- auto: probe row count, then pick rest or bulk
SET sf_force_transport='auto';

rest (default) uses the REST /query + queryMore paging path, fetched lazily.
bulk uses Bulk API 2.0. Result pages are streamed lazily, with optional PK chunking (section 7).
auto runs one SELECT COUNT() to probe the row count, then picks rest or bulk based on sf_auto_bulk_threshold (default 50000). The probe is controlled by sf_auto_probe (default true).

Important about LIMIT with Bulk: a Bulk LIMIT is not server-side, so the job runs fully. However, because result pages are streamed lazily, a small LIMIT stops downloading later pages. Note that auto cannot see LIMIT when probing, so for a small-LIMIT query against a huge object, force rest explicitly:

SET sf_force_transport='rest';
SELECT Id, Name FROM sf.Account ORDER BY CreatedDate DESC LIMIT 20;

COUNT pushdown

COUNT(*) and zero-column scans run a SELECT COUNT() (no record paging) when there is no residual filter and the scan is not forced to bulk:

SELECT COUNT(*) FROM sf.Contact;

Bulk and blob/base64 fields

Salesforce Bulk API 2.0 query results are delivered as CSV, and that CSV does not return blob/base64 fields. Forcing such a field through Bulk yields the Salesforce error "Blob field not supported in Bulk V2 Query with CSV content type". In DuckDB, base64 Salesforce fields map to the BLOB type.

The extension guards against this from metadata alone: if a projected field is of the base64 type — at any depth, including a base64 field inside a parent-relationship STRUCT — the three transports behave as follows:

auto: the scan stays on REST and never picks Bulk. The recorded reason is auto: bulk-incompatible (projected base64 field 'NAME') -> rest.
bulk (forced): the scan fails with a clear error before any Bulk job is created — projected base64 field 'NAME' is not supported by Bulk API 2.0 CSV; use 'rest' or 'auto'.
rest (default): unchanged — REST returns blob fields as base64 in JSON.

If the base64 field is not projected, Bulk is allowed as usual. The decision and its reason are visible in salesforce_last_transport() and salesforce_query_cost() (both report the transport and the reason).

Practical guidance: to read a blob/base64 column, use REST (the default), or simply don't project that column in a Bulk scan.

This guard is metadata-driven and covers base64 fields only. Other Bulk-unsupported objects are not pre-listed; if forced to Bulk, they surface as a clear Salesforce Bulk-job error instead.

REST and blob/base64 body fields (#v1.3 §11a)

The §11 guard above is about the Bulk transport. REST behaves differently for blob/base64 BODY fields — for example Attachment.Body or ContentVersion.VersionData. A REST query returns those fields not as inline base64, but as a URL reference that points to the binary payload. The scanner does not follow that URL: there is no automatic attachment/blob download.

Selecting such a field over REST therefore raises a clear, documented error instead of silently returning the reference:

Salesforce returned a URL reference for blob/base64 field 'NAME'; inline
BLOB decoding is not supported by REST query. Select non-blob fields or
fetch the blob URL outside the scanner.

This limitation applies specifically to blob body fields returned as URL references. Small/inline base64 fields that REST returns inline still decode to BLOB normally.

Putting both transports together for blob/base64 body fields:

Bulk (§11): not supported at all — the guard errors, and auto stays on REST.
REST (§11a): returns a URL reference, which is not decodable inline — the scanner raises the clear error above.

Net: blob body fields are not directly readable as bytes through this connector on either transport. Practical guidance: select the non-blob fields you need (so the scan succeeds), and fetch the blob out-of-band via the Salesforce API using the record Id.

Datetime, date, and time decoding (#v1.3 §13)

The connector expects Salesforce datetime, date, and time values as ISO 8601 strings — for example the datetime 2024-01-15T10:20:30.000+0000, the date 2024-01-15, and the time 10:20:30.000Z. These decode to the DuckDB TIMESTAMP, DATE, and TIME types respectively. The typed decode is the same on both transports: REST returns the value in JSON and Bulk returns it in CSV, and either way it goes through one decode path.

Timestamps are normalized to the UTC wall-clock value: the timezone offset is stripped, not shifted. So 2024-01-15T10:20:30.000+0000 and a value with a non-zero offset both surface their UTC wall-clock reading as the TIMESTAMP.

A numeric / epoch value in a datetime/date/time field — for example 1705314030000 — is not interpreted. The connector does not guess seconds-versus-milliseconds; instead it raises a clear, field-named error such as:

field 'Whn' (Salesforce type 'datetime') could not be decoded as TIMESTAMP

The offending value is never echoed in the message. This is intentional: the connector fails clearly rather than produce a bogus timestamp. There is no confirmed case of Bulk API 2.0 query returning epoch datetimes; if a real org ever does, that is a bug to report with a (structural) example, not something the connector guesses at.

Practical guidance: if you must handle a numeric/epoch time value, transform it outside the scanner — for example fetch the raw number some other way and apply DuckDB's to_timestamp to it.

Retries and read timeout

Transient HTTP failures (429, 5xx, connection errors) are retried automatically: sf_retry_max attempts (default 3) with sf_retry_backoff_ms linear backoff (default 200 ms). Each attempt waits at most sf_timeout_read_s seconds (default 60, cap 3600) for the server's response.

Raise the timeout when a legitimate server-side COUNT() or a scan on a very large object exceeds it and fails instead of completing - for example SET sf_timeout_read_s = 300. See the function manual for the full semantics.

7. Large extractions: Bulk + PK chunking

For very large objects, Bulk API 2.0 plus PK chunking parallelizes the extraction. sf_bulk_chunks (default 1 = off, cap 8, Bulk-only) splits a Bulk scan into N disjoint Id ranges. The extension probes MIN(Id) / MAX(Id), performs a lexical split, and runs one Bulk job per chunk in parallel (up to one thread per chunk):

SET sf_force_transport='bulk';
SET sf_bulk_chunks=8;

CREATE TABLE all_opps AS
SELECT Id, Name, Amount, StageName, CloseDate
FROM sf.Opportunity;

Caveats: there is no global row order across chunks, and chunks may be uneven or even empty depending on how Id values distribute.

Datalake seed and backfill recipe

A first full load of a large object is the failure mode that motivated the range-pushdown work: a single ~millions-of-rows Bulk read can exhaust the poll budget, and a CreatedDate >= X AND CreatedDate < Y window only helps if that range actually pushes to SOQL. Follow this sequence instead of pointing Bulk at the whole object.

1. Prove the predicate pushes before you extract. A selective-looking WHERE is worthless if DuckDB is filtering after a full remote scan. Run the window through salesforce_query_cost() and confirm the range is server- side:

SELECT Id FROM sf.Produto_Oferta__c
WHERE CreatedDate >= TIMESTAMP '2024-01-01 00:00:00'
  AND CreatedDate <  TIMESTAMP '2024-02-01 00:00:00'
LIMIT 0;

SELECT where_pushed, pushed_filters, residual_filters, guidance
FROM salesforce_query_cost();
-- want: where_pushed contains the CreatedDate range, residual_filters = 0,
-- guidance = "Salesforce is filtering server-side". If guidance instead says
-- "DuckDB is filtering after a full remote scan", the range did NOT push —
-- fix the predicate before extracting (see §14 for what stays residual).

COUNT(*) over the same window pushes to a single SELECT COUNT() and returns the per-window row count for free (zero record egress), so you can size each window:

SELECT COUNT(*) FROM sf.Produto_Oferta__c
WHERE CreatedDate >= TIMESTAMP '2024-01-01 00:00:00'
  AND CreatedDate <  TIMESTAMP '2024-02-01 00:00:00';

salesforce_aggregate() (§13) can also validate server-side selectivity, but it proves only that Salesforce can filter the range — a normal extraction still has to prove the scan pushes the same predicate through salesforce_query_cost(). They are different code paths.

2. Split the seed into pushed CreatedDate (or SystemModstamp) windows and write each through DuckDB to Parquet or a lake table. Each window is its own bounded Bulk read:

SET sf_force_transport='bulk';
SET sf_bulk_require_predicate=true;   -- refuse an accidental full-object read
SET sf_bulk_poll_budget=2000;         -- a large window may need > 600 polls

COPY (
  SELECT Id, Name, Amount, CreatedDate, SystemModstamp
  FROM sf.Produto_Oferta__c
  WHERE CreatedDate >= TIMESTAMP '2024-01-01 00:00:00'
    AND CreatedDate <  TIMESTAMP '2024-02-01 00:00:00'
) TO 'lake/produto_oferta/2024-01.parquet' (FORMAT parquet);

With sf_bulk_require_predicate=true, a Bulk read whose predicate did not push fails fast before a job is created — so a mistake surfaces as an error, not a multi-million-row over-fetch. After each window, check salesforce_query_cost().bulk_polls to see how close the job ran to the budget and raise sf_bulk_poll_budget if needed.

3. Maintain incrementally on SystemModstamp. Once seeded, refresh only the changed rows with a half-open window from the last watermark, re-reading a short lookback to absorb clock skew:

COPY (
  SELECT Id, Name, Amount, CreatedDate, SystemModstamp
  FROM sf.Produto_Oferta__c
  WHERE SystemModstamp >= TIMESTAMP '2024-06-01 00:00:00'  -- last watermark - lookback
) TO 'lake/produto_oferta/incr-2024-06.parquet' (FORMAT parquet);

Drive the windows and watermarks from your orchestrator (dbt, a scheduler, etc.); the extension itself does not manage checkpoints or schedules.

8. Quota governor

To avoid burning your daily API allotment, a quota governor gates Bulk job starts (REST is not gated). Before starting a Bulk job, the extension reads GET /limits and refuses the job when remaining requests are at or below a threshold:

threshold = max(sf_quota_min_remaining, sf_quota_reserve_pct% of DailyApiRequests.Max)

Defaults: sf_quota_min_remaining is 1000 and sf_quota_reserve_pct is 10 (percent). Relevant settings:

SET sf_quota_enabled=true;       -- master switch (default true)
SET sf_quota_enforce=true;       -- false = warn only (default true)
SET sf_quota_fail_open=true;     -- /limits unavailable -> allow (default true)
SET sf_quota_cache_seconds=60;   -- cache /limits result (default 60)
SET sf_quota_min_remaining=1000; -- absolute floor (default 1000)
SET sf_quota_reserve_pct=10;     -- reserve % of daily max (default 10)

HTTP 429 responses are retried automatically; REQUEST_LIMIT_EXCEEDED is terminal.

9. Relationships (opt-in)

By default, relationship columns are off. Set sf_relationships='parent' to expose single-target parent lookups as STRUCT columns:

SET sf_relationships='parent';

SELECT Account.Name
FROM sf.Contact
LIMIT 10;

Scope and caveats:

Depth is 1 (direct parent only) by default; raise sf_relationship_depth to 2 to also expand the grandparent.
Polymorphic lookups are skipped.
Predicates on subfields are evaluated as residual filters (in DuckDB).
The default is 'off'.

Grandparent traversal (depth 2)

Set sf_relationship_depth=2 to expand a single-target parent relationship's own single-target parent (the grandparent) as a nested STRUCT child. For example, Contact → Account → Owner (User):

SET sf_relationships='parent';
SET sf_relationship_depth=2;

SELECT Account.Owner.Name
FROM sf.Contact
LIMIT 10;

Scope and caveats:

Depth is strictly capped at 2 (not Salesforce's full 5-level chains).
Single-target only at each hop: polymorphic relationships are skipped at every level, and self-references or cycles are skipped.
Predicates on subfields stay residual — there is no pushdown on Account.Owner.Name in WHERE, the same as at depth 1.
Over-fetch grows with depth: selecting the STRUCT fetches all scalar fields at every expanded level.

10. Schema source

sf_schema_source controls how object schema is discovered:

-- describe (default): REST describe, authoritative
SET sf_schema_source='describe';

-- tooling: fast batched Tooling FieldDefinition
SET sf_schema_source='tooling';

describe (default) uses the REST describe call and is authoritative.
tooling uses the fast, batched Tooling FieldDefinition API, with a per-object REST fallback. It produces coarser types and reduces pushdown, so prefer it only when describe is too slow for your workflow.

11. Reading archived & deleted records (queryAll)

By default a scan returns only live records. Set sf_query_mode='queryAll' to read via Salesforce's queryAll capability, which also returns archived and soft-deleted records (IsDeleted = true) alongside live rows:

SET sf_query_mode='queryAll';

SELECT Id, Name, IsDeleted
FROM sf.Account
LIMIT 10;

Scope and caveats:

Allowed values are 'query' (default) and 'queryAll'.
The mode applies across the whole scan: REST uses the /queryAll endpoint, Bulk submits a job with operation: "queryAll", and the COUNT()/MIN-MAX(Id) probes use it too — so COUNT pushdown, auto transport selection, and PK-chunk ranges all reflect deleted and archived rows.
Use the IsDeleted column to tell deleted/archived rows apart from live ones.
This is not history, CDC, or replication, and not a local snapshot — it only exposes the Salesforce read capability for that scan.
The salesforce_query() utility is query-only and ignores this setting.

11.1 The delete-sync pattern for incremental loads

A delete updates SystemModstamp to the deletion time, and the deleted row stays visible via queryAll (IsDeleted = true) until purged (~15 days) — a watermark load running queryAll inside that window captures deletes within the watermark itself. What it misses: purges (bin emptied before the next load) and objects with no IsDeleted field (e.g. User) — covered by salesforce_deleted_ids() (getDeleted source). A watermark-based incremental load (WHERE SystemModstamp > :last_watermark) never notices them — deleted rows stay alive in your target table forever. Pair the watermark with a periodic delete sweep:

-- option 1: delete sweep via queryAll (server-side predicate; rows stay
-- sweepable for ~15 days in the recycle bin)
SET sf_query_mode='queryAll';
SELECT Id FROM sf.Lead WHERE IsDeleted = true;
-- -> mark `is_deleted = true` (or delete) these ids in your target, then
--    reset sf_query_mode='query'

-- option 2: exact window via the Replication API (getDeleted), with
-- deletion timestamps (windows up to 15 minutes per call)
SELECT id, deleted_date, source
FROM salesforce_deleted_ids('sf', 'Lead',
    since := '2026-09-30T00:00:00', until := '2026-09-30T00:15:00');

-- longer horizon: queryAll fallback, deleted_date is NULL (~15-day retention)
SELECT id FROM salesforce_deleted_ids('sf', 'Lead', since := '2026-09-20T00:00:00');

Run the sweep on a schedule (e.g. daily): upsert the changed rows from the watermark load, then mark/remove the ids the sweep returns.

11.2 Direct scans without the catalog layer

salesforce_scan(catalog, object) scans an sObject using an attached catalog's credentials — useful for per-query configuration without touching session settings (e.g. from orchestrators that share connections):

-- raw SOQL predicate appended server-side (validated: no ';', no nested
-- SELECT, <= 4000 chars)
SELECT Name FROM salesforce_scan('sf', 'Lead', filter := 'Status = ''Open''')
LIMIT 10;

-- per-call mode/transport/chunks overrides
SELECT count(*) FROM salesforce_scan('sf', 'Lead', query_mode := 'queryAll');
SELECT Id FROM salesforce_scan('sf', 'Account', transport := 'bulk', chunks := 4);

Projection and plan-predicate pushdown work exactly as on catalog tables, and the generated SOQL is visible via salesforce_last_soql().

11.3 Resuming a completed Bulk job

If a load dies mid-stream of a Bulk job that already completed server-side, salesforce_bulk_resume(catalog, job_id) re-streams the result rows without re-running the query. Columns come from the job's CSV header (VARCHAR):

SELECT Id, Name
FROM salesforce_bulk_resume('sf', '7504x00000ABCDef');

12. Diagnostics

The extension ships user-facing table functions that explain what the last scan did. The richest is salesforce_query_cost():

SELECT * FROM salesforce_query_cost();

It returns, among other columns: object, soql, transport, est_rows, transport_reason, projected_fields, total_fields, pushed_filters, residual_filters, where_pushed, pages_fetched, rows_emitted, bulk, count_pushdown, bulk_chunks, bulk_polls, quota_remaining, quota_allowed, and guidance. guidance states whether Salesforce filtered server-side or DuckDB filtered after a full remote scan; bulk_polls is the Bulk job status-poll count.

Focused helpers return a single aspect of the last scan:

SELECT * FROM salesforce_last_transport();    -- transport actually used
SELECT * FROM salesforce_last_quota();        -- quota snapshot
SELECT * FROM salesforce_last_soql();         -- SOQL that was sent
SELECT * FROM salesforce_last_scan_pages();   -- pages fetched

A typical workflow is to run your query, then inspect salesforce_query_cost() to confirm the transport, the pushed vs. residual filters, and the guidance column for tuning hints.

Browsing metadata (objects and fields)

Two read-only metadata diagnostics, sourced through the shared Metadata Engine (one de-duped Describe Global + per-object Describe per catalog), let you explore the org schema without leaving DuckDB:

-- What can I query here?
SELECT object_name FROM salesforce_metadata_objects('sf')
WHERE queryable ORDER BY object_name;

-- What are the fields of Account, and which can be filtered server-side?
SELECT field_name, type, filterable, sortable, relationship_name, reference_to, picklist_values
FROM salesforce_metadata_fields('sf', 'Account')
ORDER BY field_name;

filterable is the key column for tuning: a WHERE on a non-filterable field cannot be pushed to Salesforce and is applied residually in DuckDB. The cache is invalidated by salesforce_refresh_metadata() (see below).

Explaining a scan field-by-field

salesforce_query_cost() summarizes a scan in one row; salesforce_query_explain() breaks the same last scan down field by field, so you can see exactly which predicate was pushed to Salesforce and which was left for DuckDB:

SELECT count(*) FROM sf.Account WHERE Industry = 'Technology';

SELECT object_name, field_name, role, pushed, residual, reason
FROM salesforce_query_explain()
ORDER BY role, field_name;

Read the result by role:

filter rows — pushed=true, reason='pushed_to_soql' means Salesforce filtered server-side (good). residual=true with reason='not_filterable' means the field is not filterable, so DuckDB filtered after a full remote scan — an over-fetch worth avoiding. complex_expression (an OR/NOT/ cross-field predicate, field_name NULL) and unsupported_operator are also residual.
projection rows — one per selected field (reason='projected'); a reference field also shows relationship_name + reference_to.
relationship rows — one per parent relationship the scan actually traversed (reason='relationship_traversed').
count row — count_pushdown (served by SELECT COUNT(), no records fetched) or count_not_pushed.
transport row — transport_rest or transport_bulk; the guidance column carries the reason, queryAll, and est_rows.

Comparing the two diagnostics:

-- one-row summary
SELECT object, transport, pushed_filters, residual_filters, where_pushed, count_pushdown
FROM salesforce_query_cost();

-- per-field breakdown of the SAME scan
SELECT field_name, role, pushed, residual, reason FROM salesforce_query_explain();

Notes:

Last-scan, no parameters. salesforce_query_explain() reflects the most recent scan in the session and takes no arguments. Before any scan it returns zero rows (it never invents a default row).
metadata_unavailable. If the catalog was detached (or metadata is otherwise unavailable), the engine-annotated rows degrade to reason='metadata_unavailable' with resolved=false; the count/transport rows still appear. The function never errors.
LIMIT is not shown. In this DuckDB build the table function never receives the query LIMIT (DuckDB applies it above the scan), so it is not diagnosable and is omitted rather than guessed.
Diagnostic-only. Neither function changes how scans run; they explain, they do not speed up. Scan behavior is unchanged.

Inspecting relationship expansion

salesforce_relationships() is a separate diagnostic that reports what parent-relationship expansion did the last time an object's schema was resolved (schema resolution happens on the first SELECT or DESCRIBE of an object — re-querying a cached schema does not re-resolve). It complements salesforce_query_cost(), which reports per-scan cost; this one reports per-schema-resolution relationship expansion.

Resolve an object's schema first, then call it:

SET sf_relationships='parent';
SET sf_relationship_depth=2;
SELECT Id FROM sf.Contact LIMIT 1;   -- triggers schema resolution

SELECT row_type, relationship_name, parent_object, depth_level, status, reason, field_count
FROM salesforce_relationships();

The result always starts with exactly one config row (mode, effective depth, and the expanded_count / skipped_count totals), followed by one relationship row per reference field considered. Read it to answer four questions:

Over-fetch. An expanded parent is fetched as a full STRUCT of every queryable parent scalar field, and nested projection is not pushed into SOQL — so selecting Account.Name still fetches the whole parent. The field_count column (with the note on expanded rows) shows how wide that over-fetch is.
Skipped relationships. A relationship row with status='skipped' carries a reason — polymorphic (for example a What lookup, with parent_object NULL), self_reference, cycle, name_collision, parent_not_describable, no_fields, or no_relationship_name.
Depth. depth_level=1 is the direct parent and depth_level=2 is the grandparent (for example Contact → Account → Owner).
The always-present config row. Even with sf_relationships='off' you still get the single config row (relationships_mode='off') and no relationship rows — so "off" never looks like an empty or broken result:

SET sf_relationships='off';
SELECT Id FROM sf.Account LIMIT 1;

SELECT * FROM salesforce_relationships();   -- one config row only

Refreshing the metadata cache

The connector caches object metadata in memory per ATTACH: each object's schema (resolved lazily on first reference), the global object listing, and the parent describes used for relationship expansion. There is no data cache and no on-disk cache. That cache is what makes a long session fast, but it also means a schema change made in Salesforce — a new custom field, a new object — is not seen until the cache is cleared.

salesforce_refresh_metadata(catalog [, object]) clears that cache so the next reference re-fetches from Salesforce. It makes no network call itself — it only drops cached entries. Use it to pick up org schema drift within a long-lived session without a DETACH / ATTACH.

The optional object argument is the global-vs-object distinction:

Global — omit object to drop the object listing and every resolved object schema (and parent describes). The next listing scan re-fetches, and the next reference to any object re-describes.
Object — pass an sObject name to clear only that object's resolved schema (and its parent-describe entry). Other objects and the object listing are left intact.

-- after adding a field to Account in Salesforce:
SELECT * FROM salesforce_refresh_metadata('sf', 'Account');  -- re-describe Account on next query
-- or refresh everything (schemas + object listing):
SELECT * FROM salesforce_refresh_metadata('sf');

It returns one row: catalog (the alias), scope ('global' or 'object'), and object (the object name, or NULL for a global refresh). The catalog must be an attached Salesforce catalog — otherwise you get a clear, secret-free error (no attached catalog named '<x>' or catalog '<x>' is not a Salesforce catalog).

Picklist values and record types

Two read-only metadata table functions expose a field's picklist values and an object's record types as rows. Both read from the object's standard REST describe — no Metadata API, no SOAP, no Tooling — so they never deploy or change anything. Both reuse an org you have already attached (the first argument is the ATTACH alias), and the describe is cached per ATTACH and shared by both; clear it with salesforce_refresh_metadata() to pick up a new value or record type mid-session.

salesforce_picklist_values(catalog, object, field) returns one row per picklist value of a field — value, label, active, and is_default. It is the field's full catalog: active and inactive values, not filtered by record type and without resolving dependent picklists. Add WHERE active for the active subset:

-- all Industry picklist values (active + inactive)
SELECT value, label FROM salesforce_picklist_values('sf', 'Account', 'Industry');
-- active only
SELECT value FROM salesforce_picklist_values('sf', 'Account', 'Industry') WHERE active;

A field that exists but is not a picklist returns 0 rows (not an error). An unknown catalog alias, an alias that is not a Salesforce catalog, or a field not found on the object gives a clear, secret-free error.

salesforce_record_types(catalog, object) returns one row per record type — developer_name, label, record_type_id, active, and is_default:

-- record types
SELECT developer_name, label, is_default FROM salesforce_record_types('sf', 'Account');

13. Server-side aggregates (opt-in)

Transparent MIN/MAX/SUM/AVG pushdown is not available yet (aggregate pushdown is COUNT-only on a normal scan). When you want an aggregate computed on Salesforce — so the rows are never dragged down into DuckDB — call the salesforce_aggregate() table function explicitly. It reuses an org you have already attached, so there is no re-auth and no credentials in the call.

The arguments are salesforce_aggregate(catalog, object, aggregates [, filter [, group_by]]), all VARCHAR string literals: the ATTACH alias (here 'sf'), the sObject, a comma-separated list of SOQL aggregate terms, an optional WHERE body (without the WHERE keyword), and an optional group_by. The allowed aggregate functions are MIN, MAX, SUM, AVG, COUNT, and COUNT_DISTINCT.

Without group_by it returns exactly one row, one VARCHAR column per term. Cast the results in DuckDB:

SELECT CAST(n AS BIGINT) AS n
FROM salesforce_aggregate('sf', 'Account', 'COUNT(Id) n');

Give each term an alias (SOQL style, space-separated) to name its column; without an alias the columns are expr0, expr1, ... in order. Add a filter as the fourth argument — note the doubled single quotes for the inner string literal:

SELECT
  CAST(minRev AS DECIMAL(18,2)) AS min_rev,
  CAST(maxRev AS DECIMAL(18,2)) AS max_rev,
  CAST(n AS BIGINT)             AS n
FROM salesforce_aggregate(
  'sf', 'Account',
  'MIN(AnnualRevenue) minRev, MAX(AnnualRevenue) maxRev, COUNT(Id) n',
  'Industry = ''Technology''');

Add a fifth argument, group_by, to aggregate per group instead of over the whole object. It is a comma-separated list of simple field identifiers (for example 'Industry' or 'Industry, Type') and the function builds SELECT <group_by>, <aggregates> FROM <object> [WHERE <filter>] GROUP BY <group_by>. It now returns one row per group: the group columns come first (named by field), then the aggregate columns. Because group_by is positional after filter, group without a filter by passing an empty string for the filter:

SELECT Industry, CAST(n AS BIGINT) AS account_count
FROM salesforce_aggregate('sf', 'Account', 'COUNT(Id) n', '', 'Industry')
ORDER BY account_count DESC;

To group only the matching rows, pass a non-empty filter together with group_by — for example salesforce_aggregate('sf', 'Account', 'COUNT(Id) n', 'AnnualRevenue > 0', 'Industry').

It honors sf_query_mode, and the SOQL it sends shows up in salesforce_last_soql() and salesforce_query_cost().

Scope and caveats:

Every term must be an aggregate function — bare fields are rejected, which is what keeps the single-row result.
GROUP BY is supported through the optional group_by argument, but only for simple field identifiers (no dotted / relationship fields, no expressions). ROLLUP, CUBE, and HAVING are out of scope and rejected with a clear error. Without group_by the function still returns exactly one row.
; and nested SELECT are rejected; the object must be a valid identifier; arguments are length-capped.
This is not transparent pushdown — you opt in by calling the function; there is no optimizer or plan rewrite. Any Salesforce error from a relationship / polymorphic aggregate surfaces verbatim.

14. Report Bridge (opt-in)

Bridge Salesforce reports into DuckDB for discovery and validation — not large extraction. A report is a business-authored definition, not a SOQL query, and the synchronous Reports API caps at 2,000 rows. Three read-only functions:

-- 1. discover report definitions (not data)
SELECT Id, Name, Format FROM salesforce_reports('sf');

-- 2. run a small TABULAR sample (max 2000 rows); reserved __sf_report_* columns
--    carry diagnostics (truncated / all_data / max_rows / guidance)
SELECT * FROM salesforce_report('sf', '00O...');

-- 3. structured ingredients + a best-effort candidate SOQL to validate
SELECT soql, translatable, caveats FROM salesforce_report_soql('sf', '00O...');

Intended flow: validate the report in Salesforce → run a ground-truth sample with salesforce_report() → inspect field names + candidate SOQL via salesforce_report_soql() → validate the candidate against the sample → then materialize at scale through normal sf.<Object> scans (Bulk, PK chunking, pushdown — §7).

Key caveats:

salesforce_report() is a sample/oracle, not an extraction path. Check __sf_report_truncated. A report returning 0 rows yields 0 rows and no diagnostic row — truncation is only visible when the sample is non-empty.
salesforce_report_soql().soql is a candidate, not an equivalence contract: it is produced only for single-object tabular reports with safe identifiers and supported operators; otherwise translatable = false, soql = NULL, and caveats explain why. Always validate it against the sample before scaling.
Summary/matrix reports are unsupported in this cut (tabular only).

15. Limitations

Know these boundaries before you build on the extension:

Read-only. All writes and DDL against the Salesforce catalog throw.
Bulk LIMIT is not server-side; the Bulk job runs fully (lazy streaming still stops later page downloads on a small LIMIT).
auto cannot see LIMIT when probing. For a small-LIMIT query on a huge object, force rest.
Relationships are parent-only, depth 1–2 (sf_relationship_depth, capped at 2); polymorphic lookups are skipped and subfield predicates are residual.
Tooling schema source produces coarser types and reduces pushdown.
Aggregate pushdown is COUNT-only.
PK chunks have no global order, and may be uneven or empty.
CI-validated platforms are linux_amd64 + windows_amd64 (baseline) and osx_arm64 (extra); osx_amd64, other ARM, and wasm are not validated yet. Live Salesforce TLS on macOS is unverified (use SSL_CERT_FILE).
Not yet on community-extensions, and the project is pre-1.0.

FilesExpand file tree

usage_guide.md

Latest commit

History

usage_guide.md

File metadata and controls

duckdb-salesforce - Usage guide for analysts

Official DuckDB references

1. Concepts before you start

Read-only catalog

Tables are sObjects

Authentication and security

2. Installation and loading

macOS: TLS certificate bundle

3. Connect to Salesforce

Auth sources

Example: terminal

Example: web UI / backend service

Example: Python / pipeline

Example: JWT bearer (auth_source 'jwt')

Security note

Not supported in this cut

4. First queries

Custom Metadata and Custom Settings

5. Materialize results locally

6. Transports: REST, Bulk, and auto

COUNT pushdown

Bulk and blob/base64 fields

REST and blob/base64 body fields (#v1.3 §11a)

Datetime, date, and time decoding (#v1.3 §13)

Retries and read timeout

7. Large extractions: Bulk + PK chunking

Datalake seed and backfill recipe

8. Quota governor

9. Relationships (opt-in)

Grandparent traversal (depth 2)

10. Schema source

11. Reading archived & deleted records (queryAll)

11.1 The delete-sync pattern for incremental loads

11.2 Direct scans without the catalog layer

11.3 Resuming a completed Bulk job

12. Diagnostics

Browsing metadata (objects and fields)

Explaining a scan field-by-field

Inspecting relationship expansion

Refreshing the metadata cache

Picklist values and record types

13. Server-side aggregates (opt-in)

14. Report Bridge (opt-in)

15. Limitations

Example: JWT bearer (`auth_source 'jwt'`)