duckdb-salesforce

Query Salesforce directly from DuckDB.

Federated analytics over Salesforce, Parquet, CSV, S3, and local DuckDB tables, with REST/Bulk transport, pushdown, metadata helpers, OAuth/JWT auth, and native ATTACH.

Usage guide | Function manual | Roadmap | Changelog | Guia PT | Contributing | Code of conduct | Security

duckdb-salesforce is a DuckDB extension for read-only analytics on Salesforce data. It lets analysts keep Salesforce as the operational source of truth while using DuckDB as the local OLAP engine for ad hoc analysis, BI staging, Parquet exports, lakehouse-style snapshots, and cross-source joins.

duckdb-salesforce is the first Salesforce extension published in the DuckDB Community Extensions registry, added via duckdb/community-extensions#2037 and now serving v0.20.2 (update lineage #2850 → #2875 → #2948 → #3015 → #3064 → #3418).

The extension is a bridge, not an ETL platform: Salesforce access, transport, authentication, metadata, and safe pushdown live here; joins, aggregations, materialization, files, and downstream analytics stay in DuckDB.

Principles

• Read-only by design - optimized for analytics without mutating Salesforce records or metadata.
• Push work to Salesforce when safe - projection, predicates, COUNT(*), queryAll, explicit aggregates, Bulk streaming, and PK chunking reduce wire traffic.
• Keep DuckDB in charge of analytics - joins, BI extracts, Parquet/S3, materialized tables, and local transformations use DuckDB's vectorized engine.
• Respect Salesforce realities - API quota, Bulk job lifecycle, soft-deleted records, relationship describes, picklists, record types, blobs, and auth modes are surfaced explicitly.
• Secret-safe by default - credentials come from options, environment, SFDX auth URL, or JWT key files; tokens, keys, JWTs, and org data are never logged.
• Published in DuckDB Community Extensions - added through duckdb/community-extensions#2037, now serving v0.20.2 (lineage #2850 → #2875 → #2948 → #3015 → #3064 → #3418); every pinned ref is public-clone validated and CI green.

Features

Area	What works
Native DuckDB access	ATTACH ... (TYPE salesforce) exposes Salesforce objects as DuckDB tables.
Auth	OAuth refresh-token, SFDX auth URL, environment-based credentials, and JWT bearer.
REST scans	Lazy pagination, projection pushdown, predicate pushdown, LIMIT fetch reduction, queryAll.
Bulk scans	Bulk API 2.0 query, lazy result streaming, sequential/parallel PK chunking, quota guard.
Transport selection	sf_force_transport = 'rest' | 'bulk' | 'auto' with diagnostics explaining choices.
Relationships	Parent and grandparent traversal as nested STRUCT, with skip/expand diagnostics.
Metadata Engine v2	Shared per-catalog read-only metadata cache; salesforce_metadata_objects() / salesforce_metadata_fields() for analysts; salesforce_refresh_metadata() invalidation.
Aggregates	Transparent no-group COUNT, COUNT(DISTINCT), MIN and MAX pushdown (kill-switch sf_aggregate_pushdown), and explicit salesforce_aggregate() with optional GROUP BY.
Diagnostics	Last SOQL, transport, quota, query cost, page counts, relationship decisions.
Query explainability	salesforce_query_explain() — read-only, last-scan, field-by-field view of pushed vs. residual filters, projection, relationship, count, and transport (diagnostic-only; no scan behavior change).
In-DB discoverability	Every function self-documents through duckdb_functions(): description, runnable example, real parameter names, categories.
Testing	Offline mock suite plus validated live smoke; CI covers Linux, Windows, and macOS arm64.

Quick Start

Install the signed community artifact:

INSTALL salesforce FROM community;
LOAD salesforce;

For local builds, see docs/INSTALL.md.

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

Attach an org with inline options:

ATTACH 'salesforce://production' AS sf (TYPE salesforce,
  client_id '...',
  client_secret '...',
  refresh_token '...',
  login_url 'https://login.salesforce.com');

Or keep secrets out of SQL with environment variables:

$env:SF_CLIENT_ID='...'
$env:SF_CLIENT_SECRET='...'
$env:SF_REFRESH_TOKEN='...'
$env:SF_LOGIN_URL='https://login.salesforce.com'

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

Then query Salesforce like regular DuckDB tables:

SELECT Id, Name, Industry
FROM sf.Account
WHERE Name LIKE 'Acme%'
LIMIT 25;

Analyst Workflow

Join Salesforce to local files:

SELECT a.Name, a.Industry, t.segment
FROM sf.Account a
JOIN read_csv_auto('territories.csv') t
  ON a.BillingState = t.state;

Materialize an analytical slice in DuckDB:

CREATE TABLE account_snapshot AS
SELECT Id, Name, Industry, CreatedDate
FROM sf.Account
WHERE CreatedDate >= DATE '2024-01-01';

Export to Parquet:

COPY account_snapshot TO 'account_snapshot.parquet' (FORMAT PARQUET);

Run server-side aggregates explicitly:

SELECT *
FROM salesforce_aggregate(
  'sf',
  'Account',
  'COUNT(Id) n, MIN(CreatedDate) first_created, MAX(CreatedDate) last_created',
  'IsDeleted = false');

Group in Salesforce, then continue analysis in DuckDB:

SELECT *
FROM salesforce_aggregate('sf', 'Account', 'COUNT(Id) n', '', 'Industry');

Transport

REST is the default scan path for smaller or interactive queries. Bulk is useful for large extracts.

SET sf_force_transport = 'auto'; -- rest | bulk | auto
SET sf_bulk_chunks = 4;          -- opt-in PK chunking for Bulk

SELECT count(*) FROM sf.Account;
SELECT * FROM salesforce_query_cost();
SELECT * FROM salesforce_last_transport();

Important Bulk behavior:

• Bulk jobs complete server-side before result pages exist.
• Result CSV pages stream lazily after the job completes.
• LIMIT is not pushed into Bulk server-side, but lazy streaming stops downloading pages once DuckDB has enough rows.
• Blob/base64 body fields are not supported by Bulk API 2.0 CSV and are guarded before job creation.

Relationships

Enable parent relationship traversal:

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

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

Inspect what expanded and what was skipped:

SELECT *
FROM salesforce_relationships();

Metadata

Salesforce describe metadata is available without using the Metadata API. A shared per-catalog read-only Metadata Engine backs both the diagnostics and Report Bridge.

-- Browse the org schema
SELECT object_name FROM salesforce_metadata_objects('sf') WHERE queryable;
SELECT field_name, type, filterable, reference_to, picklist_values
FROM salesforce_metadata_fields('sf', 'Account');

SELECT * FROM salesforce_picklist_values('sf', 'Account', 'Industry')
WHERE active;

SELECT * FROM salesforce_record_types('sf', 'Account');

SELECT * FROM salesforce_refresh_metadata('sf', 'Account');

Custom Metadata (__mdt) and queryable Custom Settings (__c) appear as normal read-only sObject tables when visible to the authenticated user.

Credential Sources

auth_source	Use case
options	Local development with inline ATTACH options.
env	Terminals, Python jobs, notebooks, CI secrets.
sfdx_url	Reuse a Salesforce CLI auth URL (SF_SFDX_AUTH_URL).
jwt	Server-to-server jobs using a pre-authorized Connected App and RSA key.

See docs/en/function_manual.md for exact options, environment variables, and JWT requirements.

Current Status

Own-repo release: v0.21.0 (dual-line: DuckDB v1.5.6 and v2.0.0; v1.5.2-v1.5.5 dropped). Release assets are built against v1.5.6 — production 1.5.x environments are unaffected; v2.0.0 users build from source until the community registry migrates its baseline. Community baseline: v0.20.2, merged via duckdb/community-extensions#3418 on 2026-10-29 with green community CI and a successful isolated install/load smoke (lineage #2850 → #2875 → #2948 → #3015 → #3064 → #3418; updates follow the project's explicit-submission gate). Every extension function documents itself through duckdb_functions() (description, example, parameter names), and no-group COUNT, COUNT(DISTINCT), MIN and MAX queries push down transparently (kill-switch sf_aggregate_pushdown). salesforce_relationship_graph recurses child relationships up to max_depth, and the Report Bridge resolves a report's base object from the official reportTypeMetadata.

Area	Status
Native ATTACH ... (TYPE salesforce)	Done
REST read-only scans	Done
Bulk API 2.0 scans	Done
Lazy REST/Bulk streaming	Done
Auto REST/Bulk transport	Done
Bulk PK chunking + parallel execution	Done
API quota guard + query cost diagnostics	Done
queryAll archived/deleted reads	Done
Parent + grandparent relationship traversal	Done
Relationship diagnostics	Done
OAuth refresh-token / env / SFDX URL / JWT bearer	Done
Explicit server-side aggregates + GROUP BY	Done
Picklist and record type metadata helpers	Done
Manual metadata cache refresh	Done
Metadata Engine v2 + metadata_objects / metadata_fields	Done
Query explainability (salesforce_query_explain())	Done
Function metadata in duckdb_functions() (issue #66)	Done
Linux + Windows + macOS arm64 CI	Done
Transparent COUNT(field) pushdown (sf_aggregate_pushdown)	Done
Transparent COUNT(DISTINCT) / MIN / MAX pushdown	Done (sortable numeric/temporal/boolean fields; strings stay local)
Transparent SUM / AVG pushdown	Rejected by live evidence: Salesforce SUM is floating point and diverged ~1,160 on a 3.1M-row window; salesforce_aggregate() is the explicit opt-in
Official DuckDB v1.5.6 + v2.0.0 support (dual-line)	Done (CI matrix v1.5.6/v2.0.0 × 3 platforms; release assets build against v1.5.6)
Relationship-graph child recursion (max_depth)	Done (P2.3)
Report Bridge base object via reportTypeMetadata (cut 2)	Done (P2.2)
Delete sync (salesforce_deleted_ids: getDeleted any window + paginated queryAll sweep)	Done
Bulk job resume (salesforce_bulk_resume(catalog, job_id))	Done
User-callable salesforce_scan(catalog, object [, filter]) with per-call overrides	Done
DuckDB 2.0 projected STRUCT IS NULL	Done (v0.20.1; direct REST and Bulk regression coverage)
Salesforce writes / Metadata API deploy	Out of scope

Build

git submodule update --init --recursive
make release
make test_release

Windows MSVC and RTOOLS/MinGW local validation are documented in docs/INSTALL.md.

Supported: DuckDB v1.5.6 and v2.0.0. v1.5.2-v1.5.5 support was dropped (v1.5.2/v1.5.3 on 2026-09-14; v1.5.4/v1.5.5 on 2026-11-12, scope reduction now that v2.0.0 is a shipped LTS line and the production line is v1.5.6) — see "Dropped versions" below.

Going-forward CI matrix (official, from MainDistributionPipeline.yml). The last release validated across all of v1.5.4-v1.5.6 + v2.0.0 was v0.20.2 (see docs/RELEASE_NOTES_v0.20.2.md for that run record):

Platform	DuckDB v1.5.6	DuckDB v2.0.0
Linux x64	Pass	Pass
Windows x64	Pass	Pass
macOS arm64	Pass	Pass

Dropped versions (v1.5.2-v1.5.5)

No longer supported, tested, or built by this project as of 2026-09-14. They previously failed on Windows in official CI (DuckDB's vendored fmt header against the GitHub Windows toolchain) — root cause was never isolated, and rather than continue investigating a version this project doesn't need to keep supporting, the maintainer chose to drop them (scope reduction, not a code fix). Historical evidence of that failure and of prior local Windows passes for these versions remains in git history (see the docs/RELEASE_NOTES_*.md for the releases that covered them, and CI run 33175570085 from 2026-08-28) — not reproduced or re-explained here.

v1.5.4 and v1.5.5 were dropped 2026-11-12 (maintainer decision, scope reduction — both passed cleanly through v0.20.2). With DuckDB v2.0.0 now a shipped LTS line and the production environment on v1.5.6, the project tracks only v1.5.6 + v2.0.0 going forward; v1.5.4/v1.5.5 evidence remains in git history and in the docs/RELEASE_NOTES_*.md through v0.20.2.

DuckDB extensions are version-locked to the DuckDB release used at build time.

Documentation

Docs are split by language under docs/en/ (English, primary) and docs/pt/ (Portuguese translations).

• docs/en/usage_guide.md - analyst guide. (PT: docs/pt/usage_guide.md)
• docs/en/function_manual.md - function and setting reference. (PT: docs/pt/function_manual.md)
• docs/en/guide_windows.md - Windows build guide.
• docs/en/guide_linux.md - Linux build guide.
• docs/INSTALL.md - local installation and unsigned loading.
• docs/ROADMAP.md - bridge-first roadmap and deferred work.
• docs/DOCS_PARITY.md - PT/EN documentation parity map.
• CHANGELOG.md - per-release summary index; links each entry to its full docs/RELEASE_NOTES_*.md.

Repository Layout

src/                  DuckDB extension implementation
src/include/          public/internal headers
test/sql/             offline sqllogictest suite
scripts/              smoke/build helpers
docs/en/, docs/pt/    public documentation
docs/community/       community descriptor and update history
third_party/httplib/  vendored HTTP client

Governance

• CONTRIBUTING.md
• CODE_OF_CONDUCT.md
• SECURITY.md
• LICENSE
• THIRD_PARTY_NOTICES.md

Community Catalog

This repository is published in the DuckDB community catalog. Publication still does not happen automatically: future branch, push, PR, tag, or release actions against duckdb/community-extensions require explicit human approval.

The live descriptor mirror lives at docs/community/description.yml. Readiness evidence lives at docs/community/PR_READINESS.md.

Signed install path:

INSTALL salesforce FROM community;
LOAD salesforce;

Author

Fernando Lozer - GitHub @flozer - LinkedIn /fernandolozer

License

MIT - see LICENSE.

---

Support the project

If DuckDB Salesforce helps your work, you can support its continued development.