Skip to content

DuckDB connector

DuckDB is the default execution engine and needs nothing installed in the default build: DuckDB's official library ships with the download, sits next to the executable, and runs in process. (The lean build is the exception — see Limitations.) One connection is opened lazily per executor, results are decoded once as Arrow RecordBatches (query_arrow) and rows are derived from those batches, so DuckDB is Arrow-native end to end. Sessions are pinned to UTC (SET GLOBAL TimeZone='UTC') so timestamps come back zone-less and comparable with every other engine.

DuckDB is also the reference the other connectors are compared against, which is why it is the fallback for dosi query --execute when no profile is named.

Gated by the exec-duckdb cargo feature, which is on by default — the lean artifact is what a build without it looks like.

Connection profile

datasources:
  local_duckdb:
    type: duckdb
    uri: duckdb:////absolute/path/warehouse.duckdb

Omit uri: entirely for an in-memory database. execute_batch works there too — state persists on the connection for the life of the executor.

Parameters

Key Type Required Default Notes
type string yes — duckdb
uri string no in-memory duckdb:////abs/path is absolute, duckdb:///rel/path is relative to the working directory, and a bare filesystem path is accepted as-is.
default bool no false See connection profiles.

Parsed and ignored on this connector: host, port, username, password, database, schema, sslmode, sslrootcert, arrow_flight_port, compat_mode. They are accepted so one file can describe several warehouses, but the DuckDB executor never reads them.

The CLI's --db <file> is the same thing without a profile: it points --execute at a DuckDB file directly.

Arrow-native results

DuckDB needs no configuration to stream Arrow — it is the native path, and the row shape is derived from the batches rather than the other way round. See Arrow for what that buys over the REST and CLI surfaces.

Limitations

None declared. DuckDB runs the whole corpus as the cross-engine reference, so where an expression is refused on other engines it is a limitation of that engine, not of the model.

The one caveat is the shape you downloaded, not a build-time detail: the lean artifact has no libduckdb and shells out to a duckdb CLI on your PATH instead (JSON mode, a fresh process per query). Consequences there:

  • a duckdb CLI must be installed — dosi reports cannot start duckdb CLI otherwise;
  • the shell-out is verified against the DuckDB v1.4.x series, and dosi warns once if the CLI it finds is outside it. Results come back through JSON on that path, so type fidelity (large integers, DECIMAL, TIMESTAMPTZ) is version-sensitive in a way the in-process Arrow path is not;
  • batch execution against an in-memory database is a config error, because nothing persists between processes.

Verify the connection

$ dosi query --model $DOSI_EXAMPLES/orders/model.yaml \
    --metrics revenue --group-by orders.status --execute --connection local_duckdb
status     revenue
completed  350
cancelled  100
2 rows

Troubleshooting

Message Cause and fix
cannot open duckdb database: <e> The path in uri: is not writable, or the file is not a DuckDB database. A DuckDB file written by a newer DuckDB version also lands here.
duckdb rejected SQL: <e> The compiled SQL reached DuckDB and DuckDB refused it; the message is DuckDB's own.
cannot clone duckdb connection: <e> The underlying connection was closed — usually the database file disappeared mid-run.
cannot start duckdb CLI: <e> Only in the lean build: install the DuckDB CLI or switch to the default download.
in-memory duckdb cannot persist a batch script across calls Same build: point --db at a file instead of using an in-memory database.

Reference