Skip to content
PipelineZ
Get Started

DuckDB

The duckdb connector reads and writes a DuckDB database file. The engine’s own DuckDB session attaches the file once per connection, and every read and write is a plain SQL statement against that attach. There is no server, no driver, and no credential: the connection is a file path, and the connector runs on the native tier only.

Connection

Section titled “Connection”
connections.yml
warehouse:
  connector: duckdb
  path: data/warehouse.duckdb
Key Required Default Meaning
path Yes – Path to the DuckDB database file. Relative paths resolve against the project directory. A missing file is created by the first write.

path is the only connection key. There is no base_dir option: relative-path resolution against the project directory happens internally. A path inside the project’s .pz/ directory is refused, since that is the run’s own staging and state area.

An entity is table or schema.table, named the way DuckDB names it. There are no separate schema: or table: options. The connector does not create schemas: a schema.table entity’s schema must already exist in the file.

Read options

Section titled “Read options”
Key Required Default Meaning
columns No – Column name to type map. When declared, the read projects only these columns.

A read compiles to a select over the attached table. An incremental watermark and a bounded window are pushed into that query, so DuckDB reads only the rows the run needs.

sync and retry under read: are the shared keys documented in the connections.yml reference.

Write options

Section titled “Write options”

duckdb takes no connector-specific write options. It supports every strategy:

Strategy What runs
append CREATE TABLE IF NOT EXISTS from the staged rows’ shape, then insert, matched by column name rather than position.
replace One CREATE OR REPLACE TABLE … as SELECT.
merge CREATE TABLE IF NOT EXISTS, then DuckDB’s own MERGE INTO, matched on keys:. Matched rows update, unmatched rows insert.
connections.yml
warehouse:
  connector: duckdb
  path: data/warehouse.duckdb
  entities:
    orders_current:
      write:
        strategy: merge
        keys: [order_id]

strategy, keys, schema_policy, and retry under write: are the shared keys documented in the connections.yml reference.

Capabilities

Section titled “Capabilities”
Flag Meaning
NativeScan Reads compile to a select over the attached file.
NativeCopy Writes compile to native insert, create-or-replace, or merge statements.
ReplaceWrites Supports strategy: replace.
Merge Supports strategy: merge through DuckDB’s merge into.
Transactional Each generated statement commits atomically inside the file.
BoundedWindow Pushes a watermark upper bound into the generated query.
InclusiveWatermarkBound Accepts an inclusive lower watermark bound (cursor >= value).

Notes

Section titled “Notes”
  • duckdb is native-only. Declaring engine.force_universal on a duckdb entity fails at plan time; remove that setting instead.
  • One writer per file. A run holds the file attached read-write for its whole duration. Another process holding the same file open when the run starts makes the attach fail. Close it first, or point the run at a copy.
  • A read of a missing file is refused at plan time. Attaching a path that does not exist would create an empty database and read zero rows, which is indistinguishable from a typo in path. Run a write against that path first, or fix the connection. The refusal applies to nodes the run executes: when one flow of a project writes the file and another reads it, pz run <writer> records the reader as refused and deferred in plan.json and runs, so the project bootstraps in one place. pz run --all on the fresh file still refuses, because the reader would execute.
  • Use one connection per file. Two connections that name the same file each try their own attach, and DuckDB refuses to attach one file twice in a session. Put the file’s reads and writes on the same connection.
  • append matches columns by name, not position. Appending into a table that predates the pipeline, or whose column order differs from the pipeline’s select list, lands every value under its own column name rather than by position. A target column the pipeline does not produce keeps its existing default; a column the pipeline produces that the target lacks is an error naming it.
  • Duplicate keys within a merge batch collapse to one survivor. DuckDB’s MERGE INTO matches every staged row on its own against the target as it stood before the statement, so the generated statement keys the staged side unique first. Which duplicate survives is not defined; the engine warns with PZ0522 so the pipeline can deduplicate deterministically when a specific row must win.
  • pz validate --connect checks an existing file for the DuckDB header, reports a missing file as will-be-created, and fails on a missing parent directory.
Code When
PZ0209 A strategy: merge write declares no keys:.
PZ0311 The attach failed at run time, for example because another process holds the file.
PZ0312 engine.force_universal is set on a duckdb entity.
PZ0353 A read the run executes names a file that does not exist yet.
PZ0606 Under pz mcp, path resolves outside the project directory.
Section titled “Related”