Extension function manual

Public function and setting reference for duckdb-salesforce.

This is the English counterpart of docs/pt/function_manual.md. Keep both files aligned when public behavior changes.

How to read this manual

This manual is written for a beginner. For each important function and setting it follows the same four-part shape:

What it does — a one-line plain-English explanation.
How it works — the mechanics, plus the output columns or the type/default/allowed values.
Why use it — when and why you reach for it.
Daily use — a small, runnable SQL example.

Every example assumes you have already attached an org as sf (see the ATTACH section) and uses the familiar sf.Account / sf.Contact tables.

Use this document as a behavior reference, not as a product roadmap. Future functions mentioned in the roadmap are not available until they are implemented, tested, and documented here.

A clearly separated final section documents debug/test-only entry points that are not a stable API.

Discovering functions from SQL

Since v0.15.1 every function in this manual also documents itself inside DuckDB: duckdb_functions() exposes, for each salesforce_* / sf_* function, a one-sentence description, a runnable examples entry, and real parameters names — so SQL clients, notebooks, and AI agents can discover the surface without leaving the connection:

SELECT function_name, description, examples, parameters
FROM duckdb_functions()
WHERE function_name LIKE 'salesforce%' OR function_name = 'sf_url_encode';

This manual remains the full reference; the in-DB metadata is a one-line summary plus example per function.

Runtime requirement
Level 1 - Attached org
Level 2 - Session settings
Level 3 - Diagnostics and observability
Level 4 - Utility / standalone functions
Level 5 - Server-side aggregates
Pushdown reference
Debug / test-only functions

Runtime requirement

The extension talks to Salesforce over the REST and Bulk APIs using an OAuth refresh-token flow. You need a Connected App that issues a client_id, client_secret, and a refresh_token for a user with read access to the objects you query. See docs/CONNECTED_APP.md for how to provision those credentials.

The attached catalog is read-only. The extension issues SOQL queries and reads results; it never writes back to Salesforce.

Level 1 - Attached org (storage type `salesforce`)

`ATTACH 'salesforce://<org>' AS sf (TYPE salesforce, ...)`

What it does

Connects a Salesforce org to your DuckDB session as a read-only catalog named sf. After this runs, each Salesforce sObject looks like a normal table you can query — for example sf.Account or sf.Contact.

How it works

sObjects are resolved lazily: an sObject's schema is fetched only when you first reference it, not at attach time, so attaching even a huge org is cheap. Authentication uses the OAuth refresh-token flow — the extension exchanges your refresh token for a short-lived access token and the instance URL, and refreshes as needed. The catalog is read-only: no INSERT/UPDATE/DELETE/DDL is allowed against attached sObjects.

The salesforce://<org> string is a logical label for the attached org; <org> is an arbitrary identifier you choose (for example production or sandbox). It does not encode the instance host — the actual instance URL is discovered from the OAuth token exchange.

Parameters:

Parameter	Required	Default	Meaning
`TYPE salesforce`	yes	—	Selects this storage extension
`auth_source`	no	`'options'`	Where credentials come from: `'options'`, `'env'`, `'sfdx_url'`, or `'jwt'`
`client_id`	with `'options'`	—	Connected App consumer key
`client_secret`	with `'options'`	—	Connected App consumer secret
`refresh_token`	with `'options'`	—	OAuth refresh token for the read user
`login_url`	no	`https://login.salesforce.com`	OAuth host; use the My Domain / sandbox host where applicable
`api_version`	no	extension default	Salesforce API version, e.g. `60.0`

Use login_url to point at a sandbox (https://test.salesforce.com) or a My Domain login host.

Choosing where credentials come from (`auth_source`)

The auth_source option selects where ATTACH reads the OAuth credentials from. It does not change anything about the OAuth refresh-token flow itself — only the place the client_id, client_secret, and refresh_token are sourced from. The default is 'options', which is the inline behavior described above.

'options' (default): credentials are read from the inline ATTACH options client_id, client_secret, and refresh_token (all required), plus the optional login_url and api_version.
'env': credentials are read from environment variables instead of the SQL text. The required variables are SF_CLIENT_ID, SF_CLIENT_SECRET, and SF_REFRESH_TOKEN; SF_LOGIN_URL is optional. The inline credential options are not needed (the environment source wins).
```
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). The inline credential options are not needed (the source wins).
```
ATTACH 'salesforce://production' AS sf (TYPE salesforce, auth_source 'sfdx_url');
```
'jwt': the OAuth 2.0 JWT bearer flow, for headless / CI / pipeline use. Instead of a 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 with grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer. The returned access token is held in memory; there is no refresh token, so on a 401 the extension simply re-signs a fresh assertion. Required:
- client_id — the JWT issuer (the Connected App consumer key).
- username — the JWT subject, i.e. the Salesforce user to impersonate.
- a private key, supplied either through the SF_JWT_KEY_FILE environment variable (recommended — keeps key paths out of the SQL text) or through the inline private_key_file ATTACH option (accepted for local-dev ergonomics only).
login_url is optional (default https://login.salesforce.com; use https://test.salesforce.com for a sandbox) and api_version works as in the other modes. The key must be an unencrypted PKCS#1 or PKCS#8 PEM RSA key — encrypted keys are not supported.
```
-- key path from the environment (recommended for pipelines)
ATTACH 'salesforce://production' AS sf (
  TYPE salesforce,
  auth_source 'jwt',
  client_id   'YOUR_CONSUMER_KEY',
  username    'svc@example.com'
);
```
```
-- inline key path (local dev only; 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'
);
```
Prerequisite: the Connected App must be pre-authorized for this user — either admin-approved, or pre-authorized by the user through the digital certificate ("Use digital signatures") JWT setup. The token exchange fails otherwise.

The api_version option works in all four modes. With 'env', 'sfdx_url', and 'jwt' you do not pass the refresh-token credential options at all.

Security and error messages

Environment values and the SFDX auth URL are never logged, and tokens or secrets never appear in error messages. The errors are written to be actionable without leaking anything:

A missing environment variable names only the variable (for example, SF_REFRESH_TOKEN), never its value.
A malformed SF_SFDX_AUTH_URL reports that it is malformed without echoing the URL.
invalid_grant from the refresh-token exchange is reported as "refresh token is invalid, expired, or revoked".
invalid_client is reported 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 / environment variable that is 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 as such without echoing the key 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

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

Why use it

It is the entry point for everything else. Without an attached org you have no sf.Account / sf.Contact tables to query, and none of the session settings or diagnostics have anything to act on.

Daily use

ATTACH 'salesforce://production' AS sf (
  TYPE salesforce,
  client_id     'YOUR_CONSUMER_KEY',
  client_secret 'YOUR_CONSUMER_SECRET',
  refresh_token 'YOUR_REFRESH_TOKEN'
);

SELECT Id, Name, AnnualRevenue
FROM sf.Account
WHERE Industry = 'Technology';

Inspecting the catalog via `information_schema`

What it does

Lists the sObjects and columns the attached org exposes, using the standard DuckDB catalog views — no special Salesforce function needed.

How it works

Once attached, the org's sObjects appear in the normal DuckDB catalog. Filter information_schema.tables (and .columns) by the catalog name you used in ATTACH (here, sf).

Why use it

To discover which tables and columns are available before you write a query, or to confirm an sObject was resolved.

Daily use

SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'sf';

The listing also includes Custom Metadata Types (API names ending in __mdt) and queryable Custom Settings (API names ending in __c, both List and Hierarchy types) when the org exposes them: they are ordinary read-only sObjects, so DESCRIBE and SELECT work the same way and writes throw. This is data access, not the Salesforce Metadata API — the extension reads them as rows and never deploys or modifies metadata. Visibility follows the authenticated user's and org's permissions (protected entries may not appear or be readable, per Salesforce). After a new __mdt type or field is added mid-session, call salesforce_refresh_metadata() so the next reference re-describes it.

Level 2 - Session settings (`SET ...`)

These settings tune scan transport selection, the API-quota governor, schema discovery, relationship expansion, and Bulk chunking. Each SET applies to the current DuckDB session.

`sf_force_transport`

What it does

Chooses how a scan fetches rows from Salesforce: the REST API, the Bulk API, or an automatic per-scan decision.

How it works

Type: VARCHAR
Default: 'rest'
Allowed values: 'rest', 'bulk', 'auto'

With 'rest' every scan uses the REST API. With 'bulk' every scan uses the Bulk API. With 'auto' the extension decides per scan (see sf_auto_probe and sf_auto_bulk_threshold).

Why use it

REST is best for small/medium result sets and low latency; Bulk is best for very large extracts. Set this when you know the workload, or use 'auto' to let the planner pick.

Daily use

SET sf_force_transport = 'bulk';

SELECT Id, Name FROM sf.Account;

Blob/base64 guard

Bulk API 2.0 query results are CSV, which cannot carry blob/base64 fields (base64 Salesforce fields map to DuckDB BLOB). The extension detects a projected base64 field from metadata alone — at any depth, including inside a parent-relationship STRUCT:

With 'auto', a projected base64 field keeps the scan on REST (it never picks Bulk); the reason is recorded as auto: bulk-incompatible (projected base64 field 'NAME') -> rest.
With 'bulk' (forced), the scan fails before any job is created: projected base64 field 'NAME' is not supported by Bulk API 2.0 CSV; use 'rest' or 'auto'.
With 'rest', behavior is unchanged (REST returns the field as base64).

If the base64 field is not projected, Bulk is allowed as usual. The chosen transport and the reason appear in salesforce_last_transport() and salesforce_query_cost(). The guard is metadata-driven and covers base64 only; other Bulk-unsupported objects are not pre-listed and surface as a clear Salesforce Bulk-job error if forced.

The guard above concerns Bulk. REST has its own limitation for blob/base64 body fields (for example Attachment.Body or ContentVersion.VersionData): a REST query returns these as a URL reference rather than inline base64, and the scanner does not download blobs — it does not follow that URL. Selecting such a field over REST raises a clear error:

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 applies to blob body fields returned as URL references; small/inline base64 fields that REST returns inline still decode to BLOB normally. Across transports, blob body fields are not directly readable as bytes: Bulk rejects them (guard above) and REST returns a non-decodable URL reference. Fetch them out-of-band via the Salesforce API using the record Id.

Datetime decoding behaves the same across transports. The connector expects Salesforce datetime, date, and time values as ISO 8601 strings (for example 2024-01-15T10:20:30.000+0000), which decode to TIMESTAMP, DATE, and TIME — REST in JSON and Bulk in CSV use the same typed decode, and timestamps are normalized to their UTC wall-clock value (offset stripped, not shifted). A numeric/epoch value in such a field (for example 1705314030000) is not interpreted: rather than guess seconds-versus-milliseconds, the connector raises a clear, field-named error like field 'Whn' (Salesforce type 'datetime') could not be decoded as TIMESTAMP (the offending value is never echoed). To use an epoch value, transform it outside the scanner — for example with DuckDB's to_timestamp.

`sf_auto_bulk_threshold`

What it does

In 'auto' mode, sets the estimated row count above which Bulk is chosen instead of REST.

How it works

Type: BIGINT
Default: 50000

Only consulted when sf_force_transport = 'auto'. If the estimated row count for a scan exceeds this value, the planner chooses Bulk; otherwise it chooses REST.

Why use it

To tune where the REST/Bulk boundary sits for your org and network — raise it to keep more scans on REST, lower it to push more scans to Bulk.

Daily use

SET sf_force_transport = 'auto';
SET sf_auto_bulk_threshold = 100000;

SELECT Id, Name FROM sf.Account;

`sf_auto_probe`

What it does

In 'auto' mode, decides whether to run a COUNT() probe to estimate the row count before choosing a transport.

How it works

Type: BOOLEAN
Default: true

When true and sf_force_transport = 'auto', the extension runs a COUNT() probe and compares the estimate against sf_auto_bulk_threshold. When false, it skips the probe and simply defaults to REST.

Why use it

The probe costs one extra API call but gives an accurate transport decision. Disable it (false) to save that call when you would rather default to REST anyway.

Daily use

SET sf_force_transport = 'auto';
SET sf_auto_probe = false;   -- skip the COUNT() probe; default to REST

SELECT Id, Name FROM sf.Account;

`sf_aggregate_pushdown`

What it does

Lets the planner serve a no-group COUNT, COUNT_DISTINCT, MIN or MAX query with a single server-side aggregate SOQL query instead of fetching rows.

How it works

Type: BOOLEAN
Default: true

When such a query runs directly on an attached sObject and every WHERE predicate was already pushed to SOQL (zero residual filters), the extension rewrites the plan at optimization time: one SELECT <fn>(field) ... WHERE ... query runs against Salesforce and its single result row is returned to DuckDB, which no longer aggregates locally. Constraints:

COUNT(*) keeps its own existing zero-column path.
MIN/MAX require a sortable field of a numeric, temporal or boolean type; strings stay local (SOQL vs DuckDB collation semantics are not proven equivalent).
The rewrite never fires for GROUP BY, aggregate-over-expression, relationship or blob fields, or when any filter stays residual — those shapes keep the normal row scan plus local aggregation, which is always correct.
A transport failure of the aggregate query is a hard error (correctness depends on the server answer); the error message points at the kill-switch.

Why use it

It turns a full-row fetch into one tiny API call for the most common analytical aggregates. The kill-switch (false) restores the row-scan plan shape without a restart if you ever need to compare behaviors.

Daily use

SELECT COUNT(Name), MIN(Amount), MAX(CreatedDate)
FROM sf.Account WHERE Industry = 'Tech';
SELECT soql FROM salesforce_last_soql();
-- SELECT COUNT(Name) a0, MIN(Amount) a1, MAX(CreatedDate) a2
--   FROM Account WHERE Industry = 'Tech'

SET sf_aggregate_pushdown = false;  -- kill-switch: back to the row scan

`sf_retry_max` and `sf_retry_backoff_ms`

What they do

Tune how the extension retries transient HTTP failures (429, 5xx, connection errors) before a call fails.

How they work

sf_retry_max: BIGINT, default 3, clamped to [1,10] — attempts per call.
sf_retry_backoff_ms: BIGINT, default 200, clamped to [0,60000] — attempt N sleeps backoff_ms * N before the next try.

The 401 → token-refresh → retry path is separate and always applies. A call that exhausts the attempts fails and (for scans) the whole scan errors — raise sf_retry_max (and/or the backoff) for runs over flaky links so one network blip does not abort a long load.

Why use them

One network blip mid-load otherwise aborts the whole scan. With a higher retry budget, a transient failure costs seconds instead of re-running the load from the orchestrator.

Daily use

SET sf_retry_max = 5;            -- more attempts
SET sf_retry_backoff_ms = 1000;  -- 1s, 2s, 3s, 4s, 5s backoff

`sf_timeout_read_s`

What it does

HTTP read timeout in seconds per attempt (default 60, clamped to [1,3600]). Raise it when a server-side COUNT() or a scan on a very large object exceeds the default and fails instead of completing.

How it works

Applied to every HTTP attempt, retries included: a call tuned with sf_retry_max waits up to sf_timeout_read_s per attempt, so the worst-case wall time is roughly sf_retry_max x sf_timeout_read_s.

Why use it

A legitimate heavy COUNT() on a multi-million-row object can take minutes server-side. Without raising the timeout the attempt is cut at 60s and, once the retry budget is spent, the whole query fails even though the org was still working.

Daily use

SET sf_timeout_read_s = 300;  -- give a heavy COUNT() up to 5 min per attempt

API-quota governor

The governor consults the Salesforce /limits resource and the DailyApiRequests limit before a scan, to avoid exhausting the org's daily API allowance. The settings below control it.

The governor blocks a scan when the projected remaining requests would fall below either the reserve (sf_quota_reserve_pct of DailyApiRequests.Max) or the absolute floor (sf_quota_min_remaining).

`sf_quota_enabled`

What it does

Turns the API-quota governor on or off.

How it works

Type: BOOLEAN
Default: true

When true, the governor consults /limits before scans. When false, the /limits consultation is skipped entirely and no quota check happens.

Why use it

Leave it on to protect the org's daily API budget. Turn it off only when you are certain quota is not a concern and want to avoid the /limits call.

Daily use

SET sf_quota_enabled = false;   -- no quota consultation at all

SELECT Id, Name FROM sf.Account;

`sf_quota_enforce`

What it does

Chooses whether the governor actually blocks scans, or only warns.

How it works

Type: BOOLEAN
Default: true

When true, the governor can block a scan that would breach the limits. When false, it still consults /limits and records the decision but never blocks (warn-only).

Why use it

Use warn-only mode (false) to observe what the governor would do without risking a blocked query while you tune the thresholds.

Daily use

SET sf_quota_enforce = false;   -- consult limits but never block

SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_last_quota();

`sf_quota_fail_open`

What it does

Decides what happens when the /limits endpoint cannot be read.

How it works

Type: BOOLEAN
Default: true

When true, an unavailable /limits endpoint allows the scan to proceed (fail open). When false, an unavailable /limits endpoint blocks the scan (fail closed).

Why use it

Keep it true so a transient /limits outage does not break your queries; set it false when you would rather refuse to run than risk overshooting quota.

Daily use

SET sf_quota_fail_open = false;   -- block if /limits cannot be read

SELECT Id, Name FROM sf.Account;

`sf_quota_reserve_pct`

What it does

Reserves a percentage of the daily API maximum as an untouchable buffer.

How it works

Type: BIGINT
Default: 10

The governor blocks a scan whose projected remaining requests would drop below this percent of DailyApiRequests.Max.

Why use it

To keep a safety margin of daily API calls for other systems and ad-hoc work. Raise it to be more conservative.

Daily use

SET sf_quota_reserve_pct = 20;   -- keep 20% of the daily max in reserve

SELECT Id, Name FROM sf.Account;

`sf_quota_min_remaining`

What it does

Sets an absolute floor of remaining API requests below which scans are blocked.

How it works

Type: BIGINT
Default: 1000

Independent of the percentage reserve: if projected remaining requests would fall below this absolute number, the scan is blocked.

Why use it

For a hard, count-based guardrail that does not scale with the org's maximum — useful when you want a fixed cushion.

Daily use

SET sf_quota_min_remaining = 5000;   -- never let remaining drop below 5000

SELECT Id, Name FROM sf.Account;

`sf_quota_cache_seconds`

What it does

Caches the /limits response for a number of seconds to avoid re-fetching it on every scan.

How it works

Type: BIGINT
Default: 60

The /limits result is cached in memory per instance_url for this many seconds. A value of 0 disables the cache (every scan re-reads /limits).

Why use it

A short cache cuts repeated /limits calls during a burst of queries. Lower it (or set 0) when you need the freshest possible remaining-quota reading.

Daily use

SET sf_quota_cache_seconds = 0;   -- always read fresh /limits

SELECT Id, Name FROM sf.Account;

`sf_schema_source`

What it does

Chooses which Salesforce API supplies field metadata when an sObject's schema is resolved.

How it works

Type: VARCHAR
Default: 'describe'
Allowed values: 'describe', 'tooling'

With 'describe' the standard Describe API supplies field metadata; with 'tooling' the Tooling API does.

Why use it

Switch to 'tooling' when you need metadata the standard Describe API does not surface; otherwise leave it on 'describe'.

Daily use

SET sf_schema_source = 'tooling';

SELECT Id, Name FROM sf.Account;

`sf_relationships`

What it does

Controls whether parent relationships are exposed as nested STRUCT columns.

How it works

Type: VARCHAR
Default: 'off'
Allowed values: 'off', 'parent'

With 'off' only flat fields are exposed. With 'parent', parent relationships are surfaced as nested STRUCT columns alongside the flat fields. By default this is one level deep (the direct parent); to also expand the grandparent, raise sf_relationship_depth to 2.

Why use it

Enable 'parent' when you want to read fields from a parent record (for example a Contact's parent Account) without writing your own join.

Daily use

SET sf_relationships = 'parent';

SELECT Id, Name FROM sf.Contact;

`sf_relationship_depth`

What it does

Controls how many levels of parent relationships are expanded as nested STRUCT columns. Only meaningful when sf_relationships = 'parent'.

How it works

Type: BIGINT
Default: 1
Capped at 2

With 1 (the default) only the direct parent is expanded — one level of parent STRUCT columns, exactly the behavior described under sf_relationships. With 2, a single-target parent relationship's own single-target parent (the grandparent) is also expanded, becoming a nested STRUCT child of the parent STRUCT — for example Contact → Account → Owner (User).

Expansion is single-target only at each hop: polymorphic relationships are skipped at every level, and self-references or cycles are skipped. The depth is strictly capped at 2 (it does not follow Salesforce's full 5-level relationship chains). 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 — a documented trade-off, the same as at depth 1. Grandparent expansion is describe-source only (the Tooling schema stays flat, with no relationship expansion); the parent and grandparent describes reuse the per-attach cache. On the wire, REST decodes the nested JSON and Bulk decodes the nested CSV headers (Account.Owner.Name).

Why use it

Raise it to 2 when you need a field two hops up the parent chain (for example a Contact's Account's Owner) without writing your own join, while keeping the read scoped and predictable.

Daily use

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

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

`salesforce_bulk_resume(catalog, job_id)`

What it does

Re-streams the result rows of an existing Bulk API 2.0 query job by id — the recovery path when a load dies mid-stream of a job that already completed server-side (the retry does not re-create the job or re-run the query).

How it works

Arguments: attached catalog, Bulk job_id (15-32 alphanumeric chars).
The first result page is fetched at bind; its CSV header defines the output schema (one VARCHAR column per field — Bulk CSV carries no type metadata).
Rows stream lazily page-by-page following the Sforce-Locator.
Invalid or expired job ids fail fast with the surfaced errorCode + remedy hint.

Why use it

A Bulk load that dies mid-stream no longer forces a full re-run: point this function at the completed job and keep going.

Daily use

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

`salesforce_deleted_ids(catalog, object [, since] [, until] [, source])`

What it does

Returns the ids deleted in the org for an sObject within a time window — the sync primitive that watermark-based incremental loads need. A delete updates SystemModstamp to the deletion time, and the deleted row stays visible via queryAll (IsDeleted = true) until purged (~15 days) — so a watermark load running queryAll inside that window captures deletes within the watermark itself. What it misses: purges (bin emptied before the next load), objects with no IsDeleted field (e.g. User), and exact deletion timestamps — covered by this function's getDeleted() source.

How it works

Arguments: attached catalog, sObject object; optional named since / until ISO-8601 timestamps (defaults: last 15 minutes → now); optional source override.
Default source (getDeleted): Replication API getDeleted() — exact deleted_date per id, any window width (Salesforce serves the window and the function resumes from latestDateCovered until fully covered), and works for objects with no IsDeleted field (e.g. User). Duplicate ids across resume slices are deduplicated.
Opt-in source (source := 'queryAll'): one paginated queryAll scan (WHERE IsDeleted = true AND SystemModstamp >= since) — deleted_date is NULL (Salesforce does not expose deletion timestamps there); the recycle bin holds rows only ~15 days, so sweep periodically. Objects with no IsDeleted field (e.g. User) fail with INVALID_FIELD — use the default source for those.
Output columns: id, deleted_date, source, latest_date_covered (the coverage frontier the API confirmed; NULL for the queryAll source — a value earlier than until means the window was NOT fully covered).

Why use it

The watermark upsert pattern captures in-window deletes when running queryAll, but misses purges and objects without IsDeleted. Pair it with a periodic delete sweep: upsert the changed rows, then mark/remove the ids this function returns.

Daily use

-- any window (getDeleted, exact dates)
SELECT id, deleted_date FROM salesforce_deleted_ids('sf', 'Lead',
    since := '2026-09-15T00:00:00');

-- sweep-style (queryAll, no exact dates)
SELECT id FROM salesforce_deleted_ids('sf', 'Lead',
    since := '2026-09-20T00:00:00', source := 'queryAll');

`sf_query_mode`

Per-catalog ATTACH overrides: query_mode, transport and bulk_chunks can also be set as ATTACH options — ATTACH 'salesforce://org' AS sf (TYPE salesforce, ..., query_mode 'queryAll') — and then apply to that catalog only, independent of the session settings (concurrent catalogs on one connection cannot leak scan mode into each other).

What it does

Chooses the Salesforce read capability a scan uses, and so whether archived and soft-deleted records are returned alongside live rows.

How it works

Type: VARCHAR
Default: 'query'
Allowed values: 'query', 'queryAll'

With 'query' (the default) a scan returns only live records — unchanged behavior. With 'queryAll' the scan reads via Salesforce's queryAll capability, which also returns archived and soft-deleted records (IsDeleted = true) in addition to live rows. The setting flows through every part of a scan: the REST transport uses the /queryAll endpoint, the Bulk transport 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 as well.

It is not history, CDC, or replication, and it is not a local snapshot: it only exposes the Salesforce read capability for that scan. An invalid value raises a clear BinderException (sf_query_mode must be 'query' or 'queryAll').

Why use it

Set 'queryAll' when you need to see records that have been archived or soft-deleted — for example to reconcile counts or audit deletions — without leaving the normal table-scan path.

Daily use

SET sf_query_mode = 'queryAll';

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

`sf_bulk_chunks`

What it does

Splits a Bulk scan into several primary-key ranges fetched in parallel.

How it works

Type: BIGINT
Default: 1
Cap: 8 (Bulk-only)

sf_bulk_chunks splits a Bulk scan into N primary-key ranges that are fetched in parallel (one thread per chunk). It is capped at 8 and applies only when the Bulk transport is used; it has no effect on REST scans.

Why use it

To speed up a large Bulk extract by parallelizing it across PK ranges.

Daily use

SET sf_force_transport = 'bulk';
SET sf_bulk_chunks = 4;

SELECT Id, Name FROM sf.Account;

`sf_bulk_poll_budget`

What it does

Bounds how many times a Bulk job is polled for completion before failing fast.

How it works

Type: BIGINT
Default: 600 (~250 ms per poll)

BulkStartJob polls the job status until JobComplete. When the count reaches the budget it raises a clear error naming this setting, instead of polling forever. The live count is surfaced as salesforce_query_cost() .bulk_polls, so a job that times out shows how far it got.

Why use it

A large backfill window can legitimately need more than 600 polls; raise the budget for it. Lower it to fail faster in tests or interactive sessions.

Daily use

SET sf_force_transport = 'bulk';
SET sf_bulk_poll_budget = 2000;   -- allow a long-running job to finish

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

SELECT bulk_polls FROM salesforce_query_cost();

`sf_bulk_require_predicate`

What it does

Refuses a Bulk read that pushes no predicate to SOQL (a full-object extraction), before any job is created.

How it works

Type: BOOLEAN
Default: false (guidance only)

When true, a Bulk scan whose where_pushed is empty fails fast with a BinderException. Default false leaves behavior unchanged — the salesforce_query_cost().guidance still warns about the unfiltered read. The guard checks the pushed predicate, not the SQL text: a WHERE that stays residual (e.g. a function on the column, see §14) does not satisfy it.

Why use it

For planned large backfills: it forces every Bulk read through a pushed CreatedDate / SystemModstamp window, so an accidental full-object scan surfaces as an error rather than a multi-million-row over-fetch.

Daily use

SET sf_force_transport = 'bulk';
SET sf_bulk_require_predicate = true;

-- fails fast: no predicate pushed
-- SELECT Id FROM sf.BigObject__c;

-- proceeds: CreatedDate range is pushed to SOQL
SELECT Id FROM sf.BigObject__c
WHERE CreatedDate >= TIMESTAMP '2024-01-01 00:00:00'
  AND CreatedDate <  TIMESTAMP '2024-02-01 00:00:00';

Level 3 - Diagnostics and observability

These are no-argument table functions that report on the last scan in the current session. They are best-effort and reflect a single-thread snapshot; under parallel Bulk execution they describe the coordinating thread's view.

The pattern is always the same: run a query, then call the diagnostic function to see what happened.

`salesforce_query_cost()`

What it does

Gives a single, complete summary of how the most recent scan was planned and executed — transport choice and reason, pushdown counts, paging, the quota decision, and a human-readable tuning hint.

How it works

Returns one row describing the last scan. Output columns:

Column	Type	Notes
`object`	VARCHAR	sObject scanned
`soql`	VARCHAR	SOQL sent to Salesforce
`transport`	VARCHAR	`rest` or `bulk`
`est_rows`	BIGINT	Estimated row count used for planning
`transport_reason`	VARCHAR	Why this transport was chosen
`projected_fields`	BIGINT	Fields requested in the SELECT
`total_fields`	BIGINT	Total fields on the sObject
`pushed_filters`	BIGINT	Predicates pushed into SOQL
`residual_filters`	BIGINT	Predicates kept and evaluated in DuckDB
`where_pushed`	VARCHAR	The WHERE clause actually pushed
`pages_fetched`	BIGINT	API result pages fetched
`rows_emitted`	BIGINT	Rows returned to DuckDB
`bulk`	BOOLEAN	Whether Bulk transport was used
`count_pushdown`	BOOLEAN	Whether a `COUNT()` pushdown was used
`query_mode`	VARCHAR	The read mode used (`query` / `queryAll`)
`bulk_chunks`	BIGINT	PK chunk count applied (Bulk)
`quota_remaining`	BIGINT	Remaining API requests at decision time
`quota_allowed`	BOOLEAN	Whether the quota governor allowed the scan
`guidance`	VARCHAR	Human-readable advice; states whether Salesforce filtered server-side or DuckDB filtered after a full remote scan
`bulk_polls`	BIGINT	Bulk job status-poll count (NULL for non-Bulk scans)

Last-scan, best-effort, single-thread snapshot.

Why use it

It is the one-stop diagnostic. When you want to understand or tune a scan, this row tells you almost everything — which transport ran, how much was pushed down, how many pages were fetched, and what to try next.

Daily use

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

`salesforce_query_explain()`

What it does

Explains the last scan field-by-field: one row per projected field and per conjunctive filter, plus meta rows for relationship traversal, count pushdown, and transport. Where salesforce_query_cost() is a single summary row, salesforce_query_explain() is the per-field breakdown — it tells you, field by field, whether Salesforce filtered server-side (pushed) or DuckDB filtered locally after a full remote scan (residual). A residual filter row is the warning sign of over-fetch.

It is read-only and diagnostic-only: it reflects a snapshot the scan records and changes nothing about execution. It is not a performance feature — it explains, it does not speed up.

How it works

No arguments. Returns one row per item of the last scan. Output columns:

Column	Type	Notes
`object_name`	VARCHAR	sObject scanned
`field_name`	VARCHAR	Field; NULL for a complex predicate or a meta row
`role`	VARCHAR	`projection` \| `filter` \| `relationship` \| `count` \| `transport`
`resolved`	BOOLEAN	Field resolved in metadata (NULL on `count`/`transport` rows)
`filterable`	BOOLEAN	Describe flag (NULL if unresolved / meta row)
`sortable`	BOOLEAN	Describe flag (NULL if unresolved / meta row)
`relationship_name`	VARCHAR	Single-hop relationship name when applicable
`reference_to`	LIST(VARCHAR)	Relationship target object(s); empty otherwise
`pushed`	BOOLEAN	Pushed into SOQL (SELECT for projection, WHERE for filter)
`residual`	BOOLEAN	Re-applied by DuckDB after the scan
`reason`	VARCHAR	Closed reason token (see below)
`guidance`	VARCHAR	Short, actionable hint

Roles: projection (a selected field), filter (a WHERE conjunct), relationship (a parent relationship the scan actually traversed), count (one row reporting count-pushdown state), transport (one row reporting REST/Bulk).

Reasons (closed set):

Reason	Meaning
`pushed_to_soql`	Filter translated into the SOQL WHERE (server-side)
`projected`	Column included in the SOQL SELECT
`not_filterable`	Field is not `filterable`; DuckDB filters residually (over-fetch)
`unsupported_operator`	Operator/predicate shape SOQL cannot express; residual
`complex_expression`	OR/NOT/cross-field/nested predicate kept residual (field NULL)
`unresolved_field`	Field not found in object metadata
`metadata_unavailable`	Catalog/engine unavailable; annotation degraded (no error)
`relationship_traversed`	A single-hop parent relationship was expanded
`count_pushdown`	Row count served by `SELECT COUNT()` (no records fetched)
`count_not_pushed`	Not a count-only scan
`transport_rest`	REST transport was used
`transport_bulk`	Bulk API 2.0 transport was used

Last-scan, no parameters, no-scan behavior

It reports the most recent scan in this session (process-scoped, like salesforce_query_cost()). It takes no parameters — explaining an arbitrary query is out of scope (it would require planning a query without running it).
Before any scan it returns zero rows — it never fabricates a default transport/count row.
Metadata annotation uses the shared Metadata Engine (see salesforce_metadata_fields()); if the owning catalog is detached or metadata is otherwise unavailable, the engine-annotated rows degrade to reason = metadata_unavailable (the count/transport rows still appear). It never throws.

Not diagnosable: `LIMIT`

LIMIT does not appear in salesforce_query_explain(). In this DuckDB build the table function is never given the query LIMIT — DuckDB applies it above the scan — so there is no scan state to report it from. It is intentionally omitted rather than guessed.

Why use it

salesforce_query_cost() tells you how much was pushed; query_explain() tells you which fields and why. Use it to confirm a predicate reached Salesforce (pushed_to_soql) instead of dragging the whole object across the wire (not_filterable / complex_expression residual rows).

Daily use

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;

`salesforce_relationships()`

What it does

Reports what parent-relationship expansion did the last time a Salesforce object's schema was resolved — which parents were expanded as nested STRUCT columns, which were skipped and why, and how much each expanded parent over-fetches. It is a pure diagnostic with zero behavior change.

How it works

Schema resolution happens on the first reference to an object (a SELECT or DESCRIBE). This function reflects the most recently resolved object; re-querying an already-cached schema does not re-resolve, so the report does not change until a new object's schema is resolved.

It has a different lifecycle from salesforce_query_cost() — that function reports scan cost (per scan), while this one reports schema-time relationship expansion (per schema resolution). They are deliberately kept as separate functions.

The output uses one uniform schema for two kinds of row, distinguished by row_type:

Exactly one config row is emitted first. It is always present — even when sf_relationships = 'off' — so an "off" result never looks empty or broken. When relationships are off, the config row is the only row.
Then one relationship row per reference field considered during resolution.

Columns not relevant to a given row are NULL:

Column	Type	`config` row	`relationship` row
`row_type`	VARCHAR	`'config'`	`'relationship'`
`object`	VARCHAR	resolved object	resolved object
`relationships_mode`	VARCHAR	`sf_relationships` value (`off` / `parent`)	NULL
`relationship_depth`	BIGINT	effective `sf_relationship_depth` (1..2)	NULL
`relationship_name`	VARCHAR	NULL	SF `relationshipName` (or field name)
`parent_object`	VARCHAR	NULL	target object; NULL if polymorphic
`depth_level`	BIGINT	NULL	1 = parent, 2 = grandparent
`status`	VARCHAR	NULL	`'expanded'` or `'skipped'`
`reason`	VARCHAR	NULL	NULL if expanded; else a skip reason
`field_count`	BIGINT	NULL	# STRUCT fields when expanded; NULL if skipped
`expanded_count`	BIGINT	# expanded	NULL
`skipped_count`	BIGINT	# skipped	NULL
`note`	VARCHAR	summary	over-fetch note (expanded rows)

A relationship row that was skipped carries one of these reasons in reason:

`reason`	Meaning
`polymorphic`	More than one possible target — no single STRUCT to build
`self_reference`	The field points back at the same object
`cycle`	The parent is already on the current expansion path
`name_collision`	The `relationshipName` clashes with an existing column
`parent_not_describable`	The parent describe failed
`no_fields`	The parent exposed no usable fields
`no_relationship_name`	The reference field has no usable relationship name

Over-fetch (the key insight)

An expanded parent is fetched as a full STRUCT containing every queryable parent scalar field — including the foreign-key id columns. The field_count column reflects that full width. Nested projection is not pushed into SOQL, so selecting a single subfield (for example Account.Name) still fetches the whole parent STRUCT. The note on each expanded row states this. Reading field_count together with that note is how you spot — and quantify — over-fetch.

Why use it

To understand why a parent field did or did not appear as a nested STRUCT, to see at a glance which relationships were skipped and for what reason, to confirm the effective mode and depth, and to measure the over-fetch each expanded parent adds. Because the config row is always present, you also get an unambiguous answer when relationships are simply turned off.

Daily use

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();

A sample read of that result:

The config row (row_type = 'config') shows relationships_mode = 'parent', relationship_depth = 2, and the expanded_count / skipped_count totals with a one-line note summary.
An expanded Account row (depth_level = 1, status = 'expanded', reason NULL) with a field_count equal to the number of Account scalar fields, and a note explaining the full-STRUCT over-fetch.
A skipped polymorphic row such as What (status = 'skipped', reason = 'polymorphic', parent_object NULL).
A grandparent Owner row at depth_level = 2, expanded as a nested STRUCT child of its parent.

With sf_relationships = 'off', the same call returns just the single config row (relationships_mode = 'off'), and no relationship rows:

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

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

`salesforce_relationship_graph(catalog, object [, max_depth] [, include_children := false] [, direction := 'parent'])`

What it does

On-demand read-only enumerator of an object's relationships, sourced through the shared Metadata Engine (REST Describe). One row per edge, each with an explicit status. Unlike salesforce_relationships() (which reflects the last scan's parent expansion), this walks the graph for any object you name, independent of sf_relationships. Pure metadata; zero behavior change.

How it works

Parent relationships are walked depth-first up to max_depth (default 1, clamped to [1,4]). With include_children := true (or direction := 'child'/'both'), the object's child relationships are listed and recursed through resolved (named + queryable) children up to the same max_depth, with cycle protection — child relationships fan out heavily, so keep max_depth small (2 is usually plenty).

direction := 'parent' | 'child' | 'both' filters which sides are returned (case-insensitive). It wins over include_children when both are given; otherwise include_children := true means both and the default is parent. Use direction := 'child' to list only child relationships without parent noise. max_depth bounds BOTH directions; the default (1) keeps child rows at the root level only. Child traversal recurses only through resolved children — unnamed and not-queryable children are dead ends. Output columns:

Column	Type	Notes
`source_object`	VARCHAR	the object the edge departs from
`relationship_name`	VARCHAR	`relationshipName`; NULL for an unnamed child
`path`	VARCHAR	dotted path from the root (e.g. `Account.Owner`)
`depth_level`	INTEGER	1 = direct, 2 = grandparent, …
`target_object`	VARCHAR	resolved related object; NULL if polymorphic / unresolved
`reference_to`	LIST(VARCHAR)	parent target(s); for a child, the child's back-reference FK field
`direction`	VARCHAR	`parent` or `child`
`relationship_type`	VARCHAR	`reference` (parent) or `childRelationship`
`status`	VARCHAR	see below
`caveat`	VARCHAR	short reason; NULL when `resolved`

status (closed set): resolved, polymorphic (multiple referenceTo, reported but not traversed), self_reference (direct self-parent), cyclic (target already on the path), not_queryable (target not in Describe Global), not_describable (target Describe failed), unnamed_child (a child relationship with no relationshipName — not SOQL-subquery-addressable; diagnostic, not an error).

Why use it

Understand how an object connects before writing SOQL/joins: which parents are safe single-hops, which are polymorphic or cyclic, and which children exist. include_children is opt-in — omitted, the output is parent-only.

Daily use

-- parents only (default), two levels deep
SELECT path, target_object, status
FROM salesforce_relationship_graph('sf', 'Contact', 2)
ORDER BY path;

-- child relationships, two levels deep (Contacts.Orders = a child of a child)
SELECT path, target_object, direction, status
FROM salesforce_relationship_graph('sf', 'Account', 2, direction := 'child')
WHERE depth_level = 1 OR status = 'resolved'
ORDER BY path;

`salesforce_last_soql()`

What it does

Shows the exact SOQL string the extension sent to Salesforce for the last scan.

How it works

Returns one row. Output column:

Column	Type	Notes
`soql`	VARCHAR	The SOQL query string

Why use it

To see precisely how your DuckDB query was translated into SOQL, including the projected fields and the pushed WHERE clause.

Daily use

SELECT Id, Name FROM sf.Account WHERE Industry = 'Technology';
SELECT * FROM salesforce_last_soql();

`salesforce_last_transport()`

What it does

Reports which transport (REST or Bulk) the last scan used and why.

How it works

Returns one row. Output columns:

Column	Type	Notes
`transport`	VARCHAR	`rest` or `bulk`
`est_rows`	BIGINT	Estimated rows used in the decision
`reason`	VARCHAR	Why this transport was chosen

Why use it

To confirm whether 'auto' mode (or your explicit setting) picked the transport you expected, and to read the row estimate behind that choice.

Daily use

SET sf_force_transport = 'auto';
SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_last_transport();

`salesforce_last_quota()`

What it does

Reports the quota-governor's decision for the last scan.

How it works

Returns one row. Output columns:

Column	Type	Notes
`limit_name`	VARCHAR	The Salesforce limit consulted (e.g. `DailyApiRequests`)
`max`	BIGINT	The limit maximum
`remaining`	BIGINT	Remaining requests at decision time
`threshold`	BIGINT	Effective block threshold (reserve / floor)
`allowed`	BOOLEAN	Whether the scan was allowed
`reason`	VARCHAR	Explanation of the decision

Why use it

To see how close you are to the daily API limit and why the governor did or did not allow a scan — invaluable when tuning the sf_quota_* settings.

Daily use

SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_last_quota();

`salesforce_last_scan_pages()`

What it does

Reports how many API result pages were fetched during the last scan.

How it works

Returns one row. Output column:

Column	Type	Notes
`pages`	BIGINT	API result pages fetched

Why use it

A high page count means many round trips. Use it to gauge whether tighter filters, a smaller projection, or a different transport would reduce the work.

Daily use

SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_last_scan_pages();

Level 4 - Utility / standalone functions

These functions take credentials as named arguments and do not require an ATTACH. They are useful for one-off schema inspection or raw queries.

`salesforce_describe(object, client_id := ..., ...)`

What it does

Describes the schema of a single sObject without attaching the whole org — returns one row per field.

How it works

Pass the sObject name positionally and the credentials as named arguments.

Arguments:

Argument	Required	Meaning
`object`	yes	sObject API name to describe (positional)
`client_id :=`	yes	Connected App consumer key
`client_secret :=`	yes	Connected App consumer secret
`refresh_token :=`	yes	OAuth refresh token
`login_url :=`	no	OAuth host (default `https://login.salesforce.com`)
`api_version :=`	no	Salesforce API version

Output columns (names approximate):

Column	Type	Notes
`name`	VARCHAR	Field API name
`sf_type`	VARCHAR	Salesforce field type
`duckdb_type`	VARCHAR	Mapped DuckDB type
`nillable`	BOOLEAN	Whether the field accepts NULL
`length`	BIGINT	Declared length (text fields)
`precision`	BIGINT	Numeric precision
`scale`	BIGINT	Numeric scale
`filterable`	BOOLEAN	Whether the field can appear in a SOQL WHERE
`sortable`	BOOLEAN	Whether the field can appear in ORDER BY

Why use it

To inspect an sObject's fields and types without attaching the entire org catalog — handy for quick metadata checks or scripts.

Daily use

SELECT *
FROM salesforce_describe(
  'Account',
  client_id     := 'KEY',
  client_secret := 'SECRET',
  refresh_token := 'TOKEN'
);

`salesforce_query(soql, client_id := ..., ...)`

What it does

Runs a raw SOQL query and returns each result record as a raw JSON string, one per row, without attaching the org.

How it works

This is a low-level utility: no schema mapping or pushdown planning is applied. Arguments mirror salesforce_describe, except the first positional argument is the SOQL string instead of an object name; the same credential named arguments apply. It is query-only and does not honor sf_query_mode (it never reads archived or soft-deleted records).

Why use it

For one-off raw SOQL — for example a quick lookup or a query that uses SOQL features outside the normal table scan path.

Daily use

SELECT *
FROM salesforce_query(
  'SELECT Id, Name FROM Account LIMIT 10',
  client_id     := 'KEY',
  client_secret := 'SECRET',
  refresh_token := 'TOKEN'
);

`salesforce_metadata_objects(catalog)`

What it does

Lists every global sObject of the attached org with its queryable flag. A read-only metadata diagnostic for analysts — "what can I query here?".

How it works

Returns one row per sObject. Sourced through the shared Metadata Engine (one de-duped Describe Global per catalog), so repeated calls do not re-fetch until salesforce_refresh_metadata() invalidates the cache.

Column	Type	Notes
`object_name`	VARCHAR	sObject API name
`queryable`	BOOLEAN	Real Describe-Global flag (both `true` and `false` appear)

Daily use

SELECT object_name FROM salesforce_metadata_objects('sf') WHERE queryable ORDER BY object_name;

`salesforce_metadata_fields(catalog, object)`

What it does

Lists the fields of one sObject with the metadata that drives pushdown and relationship decisions — type, filterable, sortable, relationship name and target, and picklist values. Read-only.

How it works

Returns one row per field, sourced through the shared Metadata Engine (one per-object Describe, cached per catalog).

Column	Type	Notes
`object_name`	VARCHAR	The sObject
`field_name`	VARCHAR	Field API name
`type`	VARCHAR	Salesforce field type
`filterable`	BOOLEAN	Can be used in a pushed SOQL `WHERE`
`sortable`	BOOLEAN	Can be used in a pushed `ORDER BY`
`relationship_name`	VARCHAR	Parent relationship name (NULL if not a reference)
`reference_to`	LIST(VARCHAR)	Relationship target object(s); polymorphic targets listed, not resolved; empty when not a reference
`picklist_values`	LIST(VARCHAR)	Allowed picklist values; empty for non-picklist fields

A field with no relationship targets / no picklist values yields an empty list, never NULL.

Why use it

filterable tells you whether a WHERE on that field will be pushed to Salesforce or applied residually in DuckDB — exactly the distinction salesforce_query_explain() reports per query.

Daily use

SELECT field_name, type, filterable, reference_to
FROM salesforce_metadata_fields('sf', 'Account')
ORDER BY field_name;

`salesforce_refresh_metadata(catalog [, object])`

What it does

Clears the in-memory metadata cache the connector keeps for an attached Salesforce org, so the next reference re-fetches schema (and the object listing) fresh from Salesforce. It is how you pick up org schema changes — a new custom field, a new object — within a long-lived session without a DETACH / ATTACH.

How it works

The connector caches metadata in memory per ATTACH: each object's schema (resolved lazily on first reference), the global object listing, and the parent-object describes used for relationship expansion. There is no data cache and no on-disk cache — only this in-memory metadata.

This function clears that cache. It makes no network call itself; it only drops the cached entries so the next reference re-fetches. The scope depends on whether you pass object:

Call	Scope	What is cleared	Effect on the next reference
`salesforce_refresh_metadata(catalog)`	global	the object listing and every resolved object schema (and parent describes)	the next listing scan re-fetches; the next reference to any object re-describes
`salesforce_refresh_metadata(catalog, object)`	object	only that object's resolved schema (and its parent-describe entry)	only that object re-describes; other objects and the object listing are left intact

Arguments:

Argument	Required	Meaning
`catalog`	yes	The `ATTACH` alias of an attached Salesforce org (for example `'sf'`).
`object`	no	An sObject name. Omit it for a global refresh; pass it to refresh just that object.

It returns exactly one row:

Column	Type	Notes
`catalog`	VARCHAR	The alias you passed
`scope`	VARCHAR	`'global'` (no `object`) or `'object'` (`object` given)
`object`	VARCHAR	The object name, or `NULL` for a global refresh

Errors

The errors are clear and secret-free. The alias must be an attached Salesforce catalog:

If no catalog with that alias is attached: no attached catalog named '<x>'.
If the alias names a catalog that is not a Salesforce catalog: catalog '<x>' is not a Salesforce catalog.

Why use it

To pick up org schema changes within a long-lived session without tearing down the attachment. When someone adds a custom field to Account or creates a new object, refresh the cache and the next query sees the change — no DETACH / ATTACH round trip needed.

Daily use

-- 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');

Level 4b - Picklist values and record types

Two read-only metadata table functions expose, as rows, the picklist catalogs and record types of an sObject. Both read from the standard REST describe of the object — the same describe response that carries picklistValues per field and recordTypeInfos per object. Neither uses the Salesforce Metadata API, SOAP, or the Tooling API, and neither deploys, retrieves, or changes anything: they are strictly read-only.

Both reuse an org you have already attached — the first argument is the ATTACH alias, and the function reuses that catalog's authenticated session (no re-auth, no credentials in the call).

The describe is cached per ATTACH: the connector fetches the raw describe for an object once and reuses it across both functions and repeat calls. It is an in-memory cache only — there is no on-disk cache. To pick up a schema change (a new picklist value, a new record type) within a long-lived session, clear it with salesforce_refresh_metadata(catalog [, object]); the next call re-fetches.

`salesforce_picklist_values(catalog, object, field)`

What it does

Returns one row per picklist value of a field — the field's full value catalog, exactly as the describe reports it.

How it works

All arguments are VARCHAR string literals:

Argument	Required	Meaning
`catalog`	yes	The `ATTACH` alias of an already-attached Salesforce org (for example `'sf'`). Reuses that catalog's authenticated session.
`object`	yes	The sObject that owns the field (for example `'Account'`).
`field`	yes	The API name of the field (for example `'Industry'`).

It returns one row per value:

Column	Type	Notes
`value`	VARCHAR	The stored API value of the picklist entry
`label`	VARCHAR	The display label for that value
`active`	BOOLEAN	`true` for an active value, `false` for an inactive one
`is_default`	BOOLEAN	`true` for the field's default value

Scope

The result is the field's full catalog — both active and inactive values — and is_default flags the default. The set is not filtered by record type, and dependent picklists are not resolved (a documented limitation): you get the field's complete value list, not the subset a given record type or controlling field would allow. To see only active values, add WHERE active.

Errors

The errors are clear and secret-free:

If no catalog with that alias is attached, or the alias names a catalog that is not a Salesforce catalog: a clear error.
If the field is not found on the object: a clear error.
A field that exists but is not a picklist is not an error — the function simply returns 0 rows.

Daily use

-- 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;

`salesforce_record_types(catalog, object)`

What it does

Returns one row per record type defined on an sObject, read from the recordTypeInfos of the object's describe.

How it works

Both arguments are VARCHAR string literals:

Argument	Required	Meaning
`catalog`	yes	The `ATTACH` alias of an already-attached Salesforce org (for example `'sf'`). Reuses that catalog's authenticated session.
`object`	yes	The sObject whose record types you want (for example `'Account'`).

It returns one row per record type:

Column	Type	Notes
`developer_name`	VARCHAR	The record type's developer (API) name
`label`	VARCHAR	The record type's display label
`record_type_id`	VARCHAR	The 18- or 15-character record type Id
`active`	BOOLEAN	`true` if the record type is active
`is_default`	BOOLEAN	`true` for the object's default record type

Errors

The errors are clear and secret-free: an unknown catalog alias, or an alias that is not a Salesforce catalog, produces a clear error.

Daily use

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

Level 5 - Server-side aggregates

This is a single table function that runs a SOQL aggregate query for you and returns the computed result, rather than dragging the underlying rows down into DuckDB. It reuses an org you have already attached.

`salesforce_aggregate(catalog, object, aggregates [, filter [, group_by]])`

What it does

Runs an explicit, opt-in server-side SOQL aggregate over an attached org and returns exactly one row of the computed values. You choose to call it — it is not a transparent pushdown of MIN/MAX/SUM/AVG on a normal table scan.

How it works

All arguments are VARCHAR string literals:

Argument	Required	Meaning
`catalog`	yes	The ATTACH alias of an already-attached Salesforce org (for example `'sf'`). The function reuses that catalog's authenticated session — no re-auth, and no credentials in the call.
`object`	yes	The sObject to aggregate (for example `'Account'`). Must be a valid identifier.
`aggregates`	yes	Comma-separated SOQL aggregate terms (see below).
`filter`	no	A SOQL `WHERE` body without the `WHERE` keyword (for example `Industry = 'Technology'`).
`group_by`	no	Comma-separated simple field identifiers to group by (for example `'Industry'` or `'Industry, Type'`).

The aggregates argument is a comma-separated list of SOQL aggregate terms. The allowed aggregate functions are MIN, MAX, SUM, AVG, COUNT, and COUNT_DISTINCT. Each term may carry an alias in SOQL style — space-separated after the function — for example MIN(AnnualRevenue) minRev.

The optional fifth argument, group_by, is a comma-separated list of simple field identifiers (for example 'Industry' or 'Industry, Type'). It is positional after filter — to group without a filter, pass an empty string for filter:

salesforce_aggregate('sf', 'Account', 'COUNT(Id) n', '', 'Industry')

Only plain field names are accepted: no dotted / relationship fields, no expressions, and no ROLLUP / CUBE / HAVING in this cut — those are rejected with a clear error.

Internally it runs SELECT <group_by>, <aggregates> FROM <object> [WHERE <filter>] GROUP BY <group_by> over REST. Without group_by it runs SELECT <aggregates> FROM <object> [WHERE <filter>] and returns exactly one row.

The return model:

Without group_by the function returns exactly one row. With group_by it returns one row per group (multiple rows).
When grouping, the group columns come first, each named by its field, then the aggregate columns follow.
There is one output column per aggregate term.
Every output column is typed VARCHAR. You cast in DuckDB (for example CAST(n AS BIGINT) or CAST(minRev AS DECIMAL(18,2))).
An aggregate column is named by the term's alias when one is given; otherwise it is named expr0, expr1, ... in term order.

It honors sf_query_mode (query / queryAll), so the aggregate can include or exclude archived and soft-deleted records the same way a table scan does. The SOQL it sends is recorded in the diagnostics — see salesforce_last_soql() and salesforce_query_cost().

Validation and limitations

Every term must be an aggregate function. Bare fields are rejected; this is what keeps the single-row contract.
GROUP BY is supported via the optional group_by argument, but only for simple field identifiers — no dotted / relationship fields, no expressions. ROLLUP, CUBE, and HAVING remain out of scope and are 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, and the arguments are length-capped.
Relationship / polymorphic aggregates are passed through to SOQL as written; any Salesforce error surfaces verbatim. Errors never contain secrets.

Not a transparent pushdown

Transparent MIN/MAX/SUM/AVG pushdown — rewriting an ordinary aggregate query on sf.Account into server-side SOQL automatically — would require a DuckDB OptimizerExtension and plan rewrite, which is deferred (see the roadmap). This explicit function gives you the same benefit (the aggregation happens on Salesforce, so no rows are dragged down into DuckDB) without that optimizer machinery. There is no optimizer or plan rewrite involved: you opt in by calling the function.

Why use it

When you want a server-side aggregate — a MIN/MAX/SUM/AVG/COUNT over a large sObject — without fetching the underlying rows into DuckDB, and you are willing to ask for it explicitly. It is the available stand-in for transparent aggregate pushdown.

Daily use

ATTACH 'salesforce://production' AS sf (
  TYPE salesforce,
  client_id     'YOUR_CONSUMER_KEY',
  client_secret 'YOUR_CONSUMER_SECRET',
  refresh_token 'YOUR_REFRESH_TOKEN'
);

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''');

Group by a field to get one row per group. To group without a filter, pass 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;

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

Level 6 - Report Bridge

Three opt-in, read-only functions bridge Salesforce reports into DuckDB. They are for discovery and validation, not large extraction: a report is a business-authored definition, not a SOQL query, and the synchronous Reports API returns at most 2,000 rows. The intended flow is: list a report, run a small sample, reconstruct a candidate SOQL, validate the candidate against the sample, then materialize at scale through normal sf.<Object> scans.

`salesforce_reports(catalog)`

What it does

Lists report definitions (not data) from the attached org.

How it works

Queries the standard queryable Report sObject through the catalog's credentials. Returns Id, Name, DeveloperName, FolderName, Format. Equivalent raw query: SELECT Id, Name, DeveloperName, FolderName, Format FROM sf.Report.

Daily use

SELECT Id, Name, Format FROM salesforce_reports('sf') ORDER BY Name;

`salesforce_report(catalog, report_id)`

What it does

Runs a tabular report synchronously and returns its rows as a validation sample (max 2,000 rows, no pagination) — NOT a large-extraction path.

How it works

Columns come from the report's detailColumns, named by the reportExtendedMetadata label. A label that duplicates another, or that collides with a reserved __sf_report_* name, is disambiguated (duplicates get a _N suffix; reserved-prefixed labels fall back to the API name).
Four reserved diagnostic columns are appended to every row: __sf_report_truncated (BOOLEAN — the report did not return all data), __sf_report_all_data (BOOLEAN — the API's allData, NULL if absent), __sf_report_max_rows (BIGINT — 2000), __sf_report_guidance (VARCHAR).
Summary and matrix reports (no tabular T!T factMap) raise a clear error.

0-row caveat: a report that returns no rows yields no result rows, and therefore no diagnostic row. Truncation is only observable when the sample has at least one row.

Daily use

SELECT * FROM salesforce_report('sf', '00O...');
-- inspect __sf_report_truncated to see whether you are looking at a full result

`salesforce_report_soql(catalog, report_id)`

What it does

Returns the report's structured ingredients plus a best-effort candidate SOQL — a starting point you must validate, never an equivalence contract.

How it works

One row: report_id, report_name, report_type, base_object, columns LIST, filters LIST<STRUCT(field, op, value)>, soql, translatable BOOLEAN, caveats.

SOQL is not SQL, so synthesis is conservative. soql is produced (and translatable = true) only for a single-object tabular report whose identifiers are safe Salesforce names and whose filters use supported operators (=, !=, <, >, and contains → LIKE '%v%'). String literals are single-quoted and escaped; numerics are bare; date/boolean/null values, unsafe identifiers, unsupported operators, and OR/NOT/grouped reportBooleanFilter logic set translatable = false, soql = NULL, and explain why in caveats. base_object is derived from the report type (CustomEntity$X → X) and accepted only when X is a safe identifier that exists and is queryable in Describe Global. Every projected and filtered field is then validated against the sObject Describe; the SOQL projection is relativized to bare field names. A field used in a filter must additionally be filterable (a non-filterable field would make the candidate SOQL fail at Salesforce). Relationship-traversal (Rel.Field) and pseudo columns do not resolve. Anything that fails — an unsafe/internal base, a non-existent or non-queryable object, a field missing on the object, a cross filter, an unsupported operator, or OR/NOT/grouped filter logic — yields translatable = false with soql = NULL and an explaining caveat. No partial SOQL is ever emitted. The structured ingredients are always returned regardless of translatable.

Daily use

SELECT soql, translatable, caveats FROM salesforce_report_soql('sf', '00O...');
-- if translatable, run soql via sf.<Object> and compare to the salesforce_report() sample

Pushdown reference

The scan planner pushes as much of the query into SOQL as is safe, and keeps anything else as a residual filter evaluated in DuckDB. A residual filter never changes correctness — it only means more rows are fetched than strictly necessary.

Pushed to SOQL:

Projection — only the referenced fields are requested.
Comparisons — =, <>, <, <=, >, >=.
Null tests — IS NULL, IS NOT NULL.
AND of pushable predicates.

Pushed as a superset prefilter, then kept as a residual (DuckDB re-evaluates exactly):

IN — pushed as a superset prefilter, kept residual.
LIKE prefix / suffix / contains — pushed as a superset, kept residual.
OR — pushed only when all children are themselves safe to push; pushed as a superset, kept residual.

Kept fully residual (not pushed):

Function calls in predicates.
NOT.
Predicates on non-filterable fields.
A WHERE clause that would exceed 4000 characters once rendered as SOQL.

Transparent aggregate pushdown (since v0.17.0, kill-switch sf_aggregate_pushdown): a no-group COUNT, COUNT_DISTINCT, MIN or MAX whose query runs directly on an attached sObject column with zero residual filters is served by a single server-side aggregate SOQL query — no rows fetched. MIN/MAX require a sortable numeric, temporal or boolean field (strings stay local). See the sf_aggregate_pushdown setting. GROUP BY, aggregate-over-expression, relationship/blob fields and any residual-filter shape keep the normal row scan with local aggregation.

Aggregates:

COUNT(*) is pushed down as a SOQL COUNT().
Aggregates other than COUNT(*) are not pushed.

Use salesforce_query_cost() (columns pushed_filters, residual_filters, where_pushed, count_pushdown) to see exactly what reached Salesforce for a given scan.

Debug / test-only functions

DEBUG / TEST-ONLY — not a stable API. Everything in this section exists for the extension's own test suite and for low-level debugging. Names, arguments, output shapes, and existence may change without notice. Do not build production workflows on these.

`salesforce_describe_calls()`

DEBUG / TEST-ONLY — not a stable API.

What it does

Reports a call count for Describe API requests — instrumentation used to verify how often the extension hits the Describe API.

How it works

A no-argument table function returning the Describe call-count instrumentation. Shape is internal and may change.

Why use it

Only when debugging or testing schema-resolution behavior; it lets a test assert that describe calls were (or were not) made.

Daily use

SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_describe_calls();

`salesforce_global_describe_calls()`

DEBUG / TEST-ONLY — not a stable API.

What it does

Reports a call count for Global Describe API requests.

How it works

A no-argument table function returning the Global Describe call-count instrumentation. Shape is internal and may change.

Why use it

Only when debugging or testing how often the extension performs a global describe.

Daily use

SELECT * FROM salesforce_global_describe_calls();

`salesforce_tooling_calls()`

DEBUG / TEST-ONLY — not a stable API.

What it does

Reports a call count for Tooling API requests.

How it works

A no-argument table function returning the Tooling call-count instrumentation. Shape is internal and may change. Relevant when sf_schema_source = 'tooling'.

Why use it

Only when debugging or testing Tooling-API-based schema discovery.

Daily use

SET sf_schema_source = 'tooling';
SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_tooling_calls();

`salesforce_last_bulk_create_body()`

DEBUG / TEST-ONLY — not a stable API.

What it does

Returns the request body of the most recent Bulk job create call.

How it works

A no-argument table function exposing the raw body the extension last sent to create a Bulk job. Shape is internal and may change.

Why use it

Only when debugging or testing Bulk job creation — for example to confirm the SOQL or chunking embedded in the job request.

Daily use

SET sf_force_transport = 'bulk';
SELECT Id, Name FROM sf.Account;
SELECT * FROM salesforce_last_bulk_create_body();

Other debug helpers

DEBUG / TEST-ONLY — not a stable API.

salesforce_decode(fields_json, records_json) — decodes raw Salesforce field/record JSON into typed rows; used to test the decode path in isolation.
sf_url_encode(s) — URL-encodes a string; used to test query-string construction.

Mock settings (offline test suite)

A family of sf_mock_* settings exists to drive the extension against recorded fixtures for the offline test suite (no live org). These are not for production use and are intentionally undocumented as a stable surface; they exist only so the test harness can inject canned API responses.

Documentation premise

When public behavior changes:

update this file
update docs/pt/function_manual.md
update usage guides when examples or workflows change
update roadmap files when status changes

PT/EN docs must stay aligned in meaning, status, caveats, and examples.

FilesExpand file tree

function_manual.md

Latest commit

History

function_manual.md

File metadata and controls

Extension function manual

How to read this manual

Discovering functions from SQL

Table of contents

Runtime requirement

Level 1 - Attached org (storage type salesforce)

ATTACH 'salesforce://<org>' AS sf (TYPE salesforce, ...)

What it does

How it works

Choosing where credentials come from (auth_source)

Security and error messages

Not supported in this cut

Why use it

Daily use

Inspecting the catalog via information_schema

What it does

How it works

Why use it

Daily use

Level 2 - Session settings (SET ...)

sf_force_transport

What it does

How it works

Why use it

Daily use

Blob/base64 guard

sf_auto_bulk_threshold

What it does

How it works

Why use it

Daily use

sf_auto_probe

What it does

How it works

Why use it

Daily use

sf_aggregate_pushdown

What it does

How it works

Why use it

Daily use

sf_retry_max and sf_retry_backoff_ms

What they do

How they work

Why use them

Daily use

sf_timeout_read_s

What it does

How it works

Why use it

Daily use

API-quota governor

sf_quota_enabled

What it does

How it works

Why use it

Daily use

sf_quota_enforce

What it does

How it works

Why use it

Daily use

sf_quota_fail_open

What it does

How it works

Why use it

Daily use

sf_quota_reserve_pct

What it does

How it works

Why use it

Daily use

sf_quota_min_remaining

What it does

Level 1 - Attached org (storage type `salesforce`)

`ATTACH 'salesforce://<org>' AS sf (TYPE salesforce, ...)`

Choosing where credentials come from (`auth_source`)

Inspecting the catalog via `information_schema`

Level 2 - Session settings (`SET ...`)

`sf_force_transport`

`sf_auto_bulk_threshold`

`sf_auto_probe`

`sf_aggregate_pushdown`

`sf_retry_max` and `sf_retry_backoff_ms`

`sf_timeout_read_s`

`sf_quota_enabled`

`sf_quota_enforce`

`sf_quota_fail_open`

`sf_quota_reserve_pct`

`sf_quota_min_remaining`

`sf_quota_cache_seconds`

`sf_schema_source`

`sf_relationships`

`sf_relationship_depth`

`salesforce_bulk_resume(catalog, job_id)`

`salesforce_deleted_ids(catalog, object [, since] [, until] [, source])`

`sf_query_mode`

`sf_bulk_chunks`

`sf_bulk_poll_budget`

`sf_bulk_require_predicate`

`salesforce_query_cost()`

`salesforce_query_explain()`

Not diagnosable: `LIMIT`