DuckDB

DuckDB#

Querona connects to DuckDB with a built-in provider that embeds the DuckDB.NET engine, so no server and no separate driver installation are required. A DuckDB source is a single database file (or an in-memory database) that Querona attaches directly.

Note

DuckDB is an in-process analytical database. Because the engine is embedded, a DuckDB linked server points at a database file path on a location the Querona engine can reach - there is no host or port.

Connection settings#

Select the built-in DuckDB provider and set the server name to the database file.

Setting name	Value
Server name	Full path to the DuckDB database file, e.g. `C:\\data\\sales.duckdb`. Leave empty or use `:memory:` for an in-memory database.
User name / Password	Not used (DuckDB files are not credentialed).

The DuckDB catalog exposed by the linked server is the attached file’s catalog: it is named after the file’s base name (sales.duckdb -> sales), and an in-memory database is named memory.

Access mode (read-only by default)#

A DuckDB file linked server is read-only by default. To allow writes, enable Allow writes (READ_WRITE) on the connection form, or set the accessMode provider option:

`accessMode`	Default	Behaviour
`READ_ONLY`	yes	Attach the file read-only. Multiple Querona sessions (and other processes) can read the same file at once.
`READ_WRITE`	no	Attach the file writable. Enables all write features below. Takes exclusive ownership of the file.

This is a declared, per-linked-server property, not a per-query or per-session toggle, because of how DuckDB opens files (see Single-writer model below). An unrecognised value falls back to the safe READ_ONLY default. The accessMode option does not apply to :memory: databases, which are always private to the session.

Writing to DuckDB#

With accessMode=READ_WRITE declared, a DuckDB linked server supports the following write surfaces:

Write feature	Notes
`INSERT` / `UPDATE` / `DELETE`	Pushed down to DuckDB as native statements.
`CREATE TABLE` / `DROP TABLE`	DDL push-down; the `dbo` schema maps to DuckDB’s `main`.
`SELECT INTO` / `CREATE TABLE ... AS SELECT`	Create-and-fill in one statement.
Federated `INSERT ... SELECT`	Rows from any Querona source are streamed into the DuckDB table with DuckDB’s high-throughput Appender bulk-load path.
Managed materialization	A Querona-managed table can be materialized into a writable DuckDB linked server (create + bulk fill).

MERGE is not supported as a DuckDB write target and is rejected at planning time rather than pushed down.

Writable column types cover the full scalar matrix: boolean, every integer width (including HUGEINT/UHUGEINT), DECIMAL, REAL/DOUBLE, VARCHAR, UUID, DATE, TIME, TIMESTAMP, TIMESTAMPTZ and BLOB. Nested/complex types (LIST, STRUCT, MAP, UNION) can be read but not written; an attempt to write one fails cleanly before any rows are inserted.

Single-writer model and concurrent writes#

DuckDB opens each database file with one native instance and a process-level file lock. The practical consequences of accessMode=READ_WRITE are:

Exclusive file ownership across processes. While a writable DuckDB linked server is attached, the Querona engine process holds the file. No other process (another Querona node, the DuckDB CLI, a Python script) can open the same file - not even read-only - until it is released. Conversely, if another process already holds the file, attaching it fails with a clear error (see below). DuckDB’s cross-process rule is one writer XOR many readers.
Shared, isolated view within the engine. Multiple Querona sessions writing to the same file share the one native instance but each sees a consistent snapshot; an uncommitted change in one session is not visible to another until it commits.
Concurrent inserts compose. Two concurrent bulk loads (or inserts) into the same table both succeed - appends never conflict, so no serialization is imposed on the common write path.
Row conflicts are rejected cleanly. If two overlapping transactions update or delete the same rows, DuckDB rejects the losing statement outright. No rows are changed by the failed statement and the file is never left in a partial state, so the operation is safe to retry.

Because DuckDB itself guarantees these outcomes - no corruption, and a clean deterministic error on conflict - Querona does not serialize writers behind a lock; it surfaces the condition as an actionable error and lets you retry.

Hosting federated queries#

Beyond serving data, a DuckDB linked server can act as a federator: the engine that hosts a cross-source join when a single query combines data from more than one source (see Federation). Because the DuckDB engine is embedded in the Querona process, this provides a federation engine with no external moving part - for deployments where no connected federation-capable engine is available to take that role, including air-gapped installations.

A DuckDB linked server comes to host a federated query in either of two ways:

Nomination as the default federator - the per-instance setting that names the engine used when a federated statement needs one.
In the GUI

Go to Administer ‣ System Instances, edit the instance, and pick the DuckDB connection in Connection to default federator.

In SQL
EXEC qua_set_default_federator @connectionName = 'MyDuckDB', @agentName = 'Root';
See qua_set_default_federator.
Biggest-scope election - independently of the nomination, when most of a federated query’s data already lives on a DuckDB source, the planner can run the whole query on that source, as with any other federation-capable source (see SET SINGLE_SOURCE_FEDERATION).

How federated data reaches DuckDB (streaming)#

When DuckDB hosts a federated query, Querona retrieves each remote side of the join (each remote input) - already filtered and projected by push-down - and streams it into the running DuckDB query:

Remote-input rows flow through bounded in-memory buffers directly into DuckDB’s scan. No input is staged as a table, written into the attached database file, or held in full in memory, and the joined result streams back to the client the same way, so memory use stays bounded regardless of input size.
The remote inputs are exposed to DuckDB as temporary, connection-local views. Nothing is created inside the attached DuckDB file - a read-only file (the default accessMode) works unchanged as a federator.
Querona hands DuckDB each remote input’s estimated row count, which drives DuckDB’s join planning. A remote input with no usable size estimate is assumed to hold 100,000 rows.

Federator limitations#

Note

String comparison inside a DuckDB-hosted query follows DuckDB’s byte-wise semantics, not the server collation. Join keys that differ only in case or accents match under Querona’s default case-insensitive collation but stay distinct inside DuckDB, so a join or GROUP BY on string keys that relies on case- or accent-insensitive matching returns different rows when DuckDB hosts it. Use another federator when server-collation string matching across sources is required.

Predicates are placed by |Product| before the join starts. Every filter that can narrow a remote input is pushed to that input’s source; an input without such a predicate is read in full (streamed, not staged). DuckDB has no way to ask a remote input for specific join keys, so a plan cannot rely on a small side of a join to narrow a large one - the large input still streams completely.
Aggregates. SUM, AVG, MIN, MAX, COUNT, STDEV/STDEVP and VAR/VARP are computed by DuckDB with SQL Server result semantics: SUM keeps the SQL Server result type (a bigint SUM that overflows fails with an arithmetic overflow instead of silently widening), and AVG over integers truncates toward zero. Two families are deliberately not delegated to DuckDB and are evaluated by the Querona engine instead: MIN/MAX over strings or uniqueidentifier (their ordering follows the server collation and GUID ordering, which DuckDB does not reproduce) and AVG over decimal/money (DuckDB’s double-precision accumulator cannot carry the full decimal precision). An integer AVG whose sum exceeds 2^53 can diverge from SQL Server by one unit - the same double-precision boundary.
datetimeoffset. A streamed datetimeoffset value keeps its instant (point in time), but the original offset is not recoverable inside the federated query - matching the behaviour of datetimeoffset columns stored in a DuckDB source.
Column types. varbinary inputs are supported. A remote-input column of type geometry, xml or sql_variant cannot be carried into DuckDB and fails with a clear error naming the column and type.
Temp-table data push-down is rejected. DuckDB receives federated data only as a streaming feed. A DuckDB connection whose provider configuration declares dataPushDownMethod = TEMPTABLE fails at planning time with a clear error (see Error reference below) rather than staging tables into the attached file.
Size estimates drive join planning. DuckDB plans the cross-source join from the remote-input sizes Querona reports, so statistics on the underlying sources improve the join order; a remote input without any estimate is planned as 100,000 rows.

Bundled extensions (offline)#

Querona ships a fixed set of official, signed DuckDB extensions pre-installed on disk, so they load without any runtime download. This keeps DuckDB usable on air-gapped / on-premises installations: the provider points DuckDB’s extension_directory at the installed location and opens every connection with autoinstall_known_extensions=false, so a missing extension fails locally and loudly and never attempts to reach the network.

The bundled ship set:

Extension	What it enables
`httpfs`	Read remote files over HTTP(S) and S3 (e.g. Parquet/CSV/JSON on an S3-compatible object store).
`azure`	Read files from Azure Blob Storage / ADLS Gen2.
`aws`	Resolve AWS credentials (environment, profile, SSO) to authenticate `httpfs` S3 access.

In addition, parquet, json, icu, core_functions and autocomplete are compiled into the engine and are always available.

Loading. By default the bundled extensions are autoloaded on demand - the first use of a feature that needs one loads it from the local directory. To load a specific set eagerly when the linked server connects (for example to surface a configuration problem up front), set the extensions provider option to a comma-separated list:

Provider option	Value
`extensions`	Comma-separated list of bundled extensions to load at connect time, e.g. `httpfs,azure,aws`. Absent or empty relies on autoload. Each name must be in the bundled ship set above; an unbundled name fails the connection with a clear error.

Air-gap guarantee. Because autoinstall_known_extensions is always off, Querona never downloads a DuckDB extension at runtime. A request for an extension that is not bundled (for example iceberg or a database scanner) fails immediately with an actionable message naming the bundled set, rather than hanging on a network call.

Error reference#

Querona translates DuckDB’s low-level driver errors into actionable messages:

Condition	What it means / what to do
Write to a read-only linked server	The linked server is declared `READ_ONLY`. Set `accessMode=READ_WRITE` to enable writes.
File held by another process	The DuckDB file is open in another process. A `READ_WRITE` attach needs exclusive access and any open handle blocks it. Close the other process, or declare this linked server `READ_ONLY` if the holder only reads. The message reports the file and the holding process.
Concurrent update conflict	Another transaction updated the same rows first. Nothing was changed by the failed statement; retry it. Concurrent inserts and writes to different rows do not conflict.
Extension not bundled	A feature requires a DuckDB extension that is not part of the bundled ship set (`httpfs`, `azure`, `aws`). It is unavailable in this installation, which runs air-gapped and never downloads extensions at runtime. The message names the bundled set.
Extension directory missing	An eager `extensions` load was requested but the DuckDB extensions directory, or its `version`/`platform` layout, is absent, so the declared extensions cannot be loaded. The installation is incomplete - the installer was not run, or the build produced an empty extension staging. Reinstall Querona or re-run the DuckDB extension staging so the bundled extension binaries are present on disk.
Extension binary missing	The extensions directory and its `version`/`platform` layout exist, but a specific extension declared for eager loading is not present at its expected path. This is not a configuration mistake - it indicates an incomplete installation or a partial extension staging where that one binary was not staged. Reinstall Querona or re-run the DuckDB extension staging so the bundled extension binaries are present on disk.
Federated query on a connection declaring `dataPushDownMethod` = `TEMPTABLE`	A DuckDB federator receives federated data as a streaming feed and has no local temporary-table staging; staged tables would be created permanently inside the attached database file, so the configuration is rejected at planning time, before anything is sent to DuckDB. Remove the `dataPushDownMethod` option from the connection’s provider configuration.
Federated remote-input column of an unsupported type	A cross-source query hosted by DuckDB carries a column of a type DuckDB cannot preserve (`geometry`, `xml` or `sql_variant`). The error names the column and its type; rewrite the query to exclude or convert that column, or use another federator.