# DuckDB

Connect DuckDB with Amfelt: setup, required privileges, configuration reference, and type mapping.

Available v2.15.0 Released October 31, 2026

## Overview

DuckDB is supported as both a **source** and a **target**. As a target, Amfelt loads
and historizes data into an embedded DuckDB database file — a fast, zero-administration
analytical warehouse that lives entirely inside your infrastructure. As a source, Amfelt
reads an existing DuckDB file, so a warehouse Amfelt built can be read back and a
DuckDB file produced elsewhere can be brought in. It suits development and test
environments, edge and single-node deployments, and lightweight analytical marts that do
not warrant a server-based warehouse.

DuckDB is an embedded, in-process database: there is no server to connect to. The
Amfelt agent opens the database file directly, so the agent and the warehouse always
run on the same host, and DuckDB's single-writer model applies (Amfelt caps the
connector at one connection automatically).

## Capabilities

| Feature | Source | Target |
|-|-|-|
| Full load | Yes | Yes |
| Incremental load | Yes | — |
| Change Data Capture (CDC) | — | — |
| Schema drift | Yes | — |

## Prerequisites

Before connecting DuckDB:

- **A writable location** on the Amfelt agent's host for the database file
  (e.g. `/data/warehouse.duckdb`). For a **target**, the agent creates the file if it
  does not exist.
- **Disk space** for the warehouse: staging, history, and bridge layers all live in the
  one file.
- **Single writer:** DuckDB allows one writing process at a time. Amfelt enforces
  `max_connections: 1` on the **target**; no other process may hold the file open for
  writing during a load.
- **No server, no credentials:** DuckDB is embedded — there is no network endpoint and
  no login role. Access control is the file system's.
- **Read-only source access:** a DuckDB source opens the database read-only. The file
  must already exist — a path that does not is an error, not a newly created empty
  database — and the source can never modify, or upgrade the format of, the file it
  reads.
- **Sources and targets cannot share a file concurrently:** DuckDB's read-write lock is
  exclusive, so an extract cannot run while a load holds the same database file. Schedule
  jobs that read and write one file apart from each other.

### Unsupported source column types

A DuckDB **source** cannot currently extract these column types. A table containing one
fails at extraction rather than returning partial data:

- `STRUCT` and `LIST` — DuckDB's nested types are not mapped yet. Support for them is
  planned; until it lands, flatten nested columns in a view and extract that instead.

Targets are unaffected: both write normally. Only extraction is blocked.

`INTERVAL` was listed here until the platform gained a logical category for the type.
Interval columns now extract, and their months, days and nanoseconds are fingerprinted
as separate components — a month is not a fixed number of days, so folding them into one
scalar would make `INTERVAL '1 month'` and `INTERVAL '30 days'` indistinguishable and
change detection would miss a row edited from one to the other.

### JSON numeric precision

`JSON` columns extract as text, but the DuckDB driver decodes JSON numbers through a
64-bit float before Amfelt sees them. An integer larger than 2^53 inside a JSON document
loses precision — `{"id":9007199254740993}` extracts as `{"id":9007199254740992}`. Store
large integers as their own typed column, not inside a JSON document, if exact values
matter.

## Connect

1. In Amfelt, add a DuckDB **target** and select the DuckDB connector.
2. Provide the **database** property: the path to the database file on the agent host
   (see *Configuration reference*). The agent creates the file on first use.
3. Validate the connection and deploy the warehouse layers.

A connection resolves to an ODCS server descriptor like:

```yaml
servers:
  - server: duckdb-local
    type: duckdb
    database: /data/warehouse.duckdb
```

### Adding DuckDB as a source

1. In Amfelt, add a DuckDB **source** and select the DuckDB connector.
2. Provide the **database** property: the path to an existing database file on the
   agent host. Unlike the target, the source does not create the file — it opens the
   database read-only, so a path that does not exist is reported as an error.
3. Choose **Discover schema** to read the source's schemas, objects, and properties.
4. Select the objects to extract and deploy the job.

The source performs full extracts and incremental extracts. An incremental extract reads
only the rows whose cursor column is at or after the lower bound of the run. The cursor is
a timestamp (a `TIMESTAMP` or `TIMESTAMP WITH TIME ZONE` column) or a sequence (a
`SMALLINT`, `INTEGER` or `BIGINT` column); a cursor on a column of any other type fails the
run instead of reading the object in full. A `TIMESTAMP` cursor is compared as a UTC wall
clock, whatever the time zone of the agent host. There is no change-data-capture path for
DuckDB.

### Authentication

DuckDB is an embedded database file: there are no credentials and no vault entry.
Protect the warehouse by restricting file-system access to the database file and the
agent's service account.

### Single-writer scheduling

Only one connection writes at a time (`max_connections: 1` on the target, enforced by
the platform). Schedule jobs that target the same DuckDB file apart from each other.

### State schemas

A target deploy that creates the history layer also creates the state schema `<prefix>_sys`
in the database file. It holds what each history apply commits with its rows: the watermark
of each object and the ledger of the files loaded. The first quarantined file of an object
creates `<prefix>_qrn`, when it does not exist yet, and the object's quarantine table in it,
which holds the quarantined rows until they are purged. No bridge view reads either schema.
DuckDB has no roles or grants, so whoever can open the file can read these schemas, as every
other schema in it.

## Sync modes

| Mode | What it does |
|-|-|
| Full load | Reloads all selected data on every run. |
| Incremental load | Loads only records changed since the last run, tracked by a watermark column (timestamp or sequence). |

## Target history modes

| Mode | What it does |
|-|-|
| SCD Type 2 (full history) | Keeps the full history. Each change writes a new versioned record with validity dates. |
| SCD Type 1 (overwrite) | Overwrites in place, keeping only the latest value, no history. |
| Append | Adds new records without updating or removing existing ones. |
| Deduplicated append | Appends new records, skipping duplicates already loaded. |
| Snapshot | Stores a full point-in-time copy of the source on each run. |
| Replace (truncate & load) | Replaces the entire target with the latest load on each run. |

## Configuration reference

| Property | Type | Required | Description |
|-|-|-|-|
| `database` | string | Yes | Path to duckdb database file. |
| `schema` | integer | No | The name of the schema. |

## Type mapping

Each DuckDB physical type maps to an Apache Arrow type (the data-plane intermediate) and then to an ODCS logical type. See the [official DuckDB data type reference](https://duckdb.org/docs/sql/data_types/overview.html).

| DuckDB type | Aliases | Apache Arrow | ODCS logical | Description |
|-|-|-|-|-|
| `bigint` | int8 | int64 | integer | signed eight-byte integer |
| `bit` | bitstring | utf8 | string | fixed-length bit string |
| `blob` | bytea, binary, varbinary | binary | object | variable-length binary data |
| `boolean` | bool | bool | boolean | logical Boolean (true/false) |
| `date` | — | date32 | date | calendar date (year, month, day) |
| `decimal` | numeric | — | number | exact numeric of selectable precision |
| `double` | float8 | float64 | number | double precision floating-point number (8 bytes) |
| `float` | float4 | float32 | number | single precision floating-point number (4 bytes) |
| `hugeint` | — | decimal256(39,0) | integer | signed sixteen-byte integer (DuckDB-specific) |
| `integer` | int, int4, signed | int32 | integer | signed four-byte integer |
| `interval` | — | month_day_nano_interval | object | time span |
| `json` | — | utf8 | string | JSON data |
| `smallint` | int2 | int16 | integer | signed two-byte integer |
| `time` | time without time zone | timestamp_us_utc | date | time of day (no time zone) |
| `timestamp` | datetime, timestamp without time zone | timestamp_us_utc | date | date and time (no time zone) |
| `timestamptz` | timestamp with time zone | timestamp_us_utc | date | date and time, including time zone |
| `timetz` | time with time zone | timestamp_us_utc | date | time of day, including time zone |
| `tinyint` | int1 | int8 | integer | signed one-byte integer |
| `ubigint` | — | uint64 | integer | unsigned eight-byte integer |
| `uinteger` | — | uint32 | integer | unsigned four-byte integer |
| `usmallint` | — | uint16 | integer | unsigned two-byte integer |
| `utinyint` | — | uint8 | integer | unsigned one-byte integer |
| `uuid` | — | utf8 | string | universally unique identifier |
| `varchar` | text, string, char, bpchar, character varying | utf8 | string | variable-length character string |

## Troubleshooting

### Could not set lock on file

Another process holds the database file open for writing. DuckDB is single-writer: close other DuckDB shells, notebooks, or BI tools using the same file, and make sure only one job targets it at a time.

### Permission denied opening the database file

The agent's service account cannot read or create the file at the configured path. Check directory existence and file-system permissions on the agent host.

### Database file grows unexpectedly

DuckDB reclaims space lazily. Space from deleted or re-loaded data (e.g. replace mode) is reused internally; to shrink the file itself, run CHECKPOINT or copy the database with the DuckDB CLI during a maintenance window.

### File written by a newer DuckDB version

DuckDB storage is not always forward-compatible. If the file was created by a newer DuckDB than the connector ships, upgrade the connector; if it was created by a much older version, export and re-import via the DuckDB CLI.

### Failed to delete all rows from index

A target written by an earlier release of the connector can have indexes that lost rows. Earlier releases left their writes in DuckDB's write-ahead log, and a DuckDB tool that replayed that log could drop rows from the target's indexes. A sync then fails with this error, and upgrading the connector does not repair the index. The rows themselves are intact, and rebuilding the indexes keeps both staging and history. Stop every sync that uses the file, open it with the DuckDB CLI (1.5.5 or 1.5.6), and list the indexes with `SELECT schema_name, index_name, sql FROM duckdb_indexes();`. Keep the `sql` column: it holds each index's definition. Drop every index with `DROP INDEX "<schema_name>"."<index_name>";`, run `CHECKPOINT;`, re-create each index by running its saved definition, and run `CHECKPOINT;` again. The next sync then runs normally. Current releases of the connector write every change into the database file before they finish, which prevents this.

### Failure while replaying WAL, or a sync that crashes

A target written by an earlier release of the connector can also hold a write-ahead log that DuckDB 1.5.5 cannot replay. The DuckDB 1.5.5 CLI then cannot open the file and reports `Failure while replaying WAL` followed by `Attempted to access index N within vector of size N`, and a sync with an earlier release fails or stops responding. Upgrade the connector: current releases use DuckDB 1.5.6, which replays such a log and writes it into the database file on the next sync, and the DuckDB 1.5.6 CLI opens the file. If a sync with the current release still fails, and the DuckDB plugin's output shows `SIGSEGV: segmentation violation`, DuckDB cannot write that log into the file as it stands. The sync's own error then only reports that the connection to the plugin was lost, for example `WriteRecords send: rpc error: code = Unavailable desc = error reading from server: EOF` (agents before v4.18.6 report `WriteRecords send: EOF`), and the DuckDB CLI likewise exits without a message when it runs `CHECKPOINT;` on the file. Rebuild the indexes as described under "Failed to delete all rows from index", with the DuckDB 1.5.5 or 1.5.6 CLI, and the next sync runs normally.

### checkpoint failed

Before a load starts, the connector writes any write-ahead log an earlier run left into the database file, and after every load and deploy it writes its own changes there. If DuckDB cannot do that before a load, the sync fails with `checkpoint failed`, followed by DuckDB's reason, and the load does not start, so staging and history keep their previous rows. Retrying does not make the file harder to repair. Fix the cause DuckDB names and run the sync again. If DuckDB names an internal error, rebuild the indexes as described under "Failed to delete all rows from index".

### Opening the target in another DuckDB tool after an upgrade

A target written by an earlier release of the connector can still hold that release's write-ahead log. The first sync after the upgrade writes it into the database file. Run that sync before you open the file in the DuckDB CLI, a notebook, or a BI tool: such a tool otherwise replays the log itself, which can damage the target's indexes, with DuckDB 1.5.6 as well. If that has happened, or that first sync fails, see the entries above.

## Changelog

Release history for the DuckDB connector, newest first.

### v2.15.0 (October 31, 2026)

**Features**

- Keep the state schema and the file ledger

### v2.14.0 (October 29, 2026)

**Features**

- Cursor binder for postgres and duckdb sources

### v2.13.1 (October 17, 2026)

**Bug Fixes**

- Preserve configured database file paths

### v2.13.0 (October 16, 2026)

**Features**

- Publish plugin releases on the connectors repo

### v2.12.2 (October 13, 2026)

**Bug Fixes**

- Move connector module paths to github.com/amfeltlabs

### v2.12.1 (October 11, 2026)

**Bug Fixes**

- Count scd1 overwrites as updates
- Scaffold rdbms connectors on pgx; require conformance v0.5.0

### v2.12.0 (October 11, 2026)

**Features**

- Open PostgreSQL with pgx; core v3.41.2, plugin-sdk v5.17.4

### v2.11.0 (October 10, 2026)

**Features**

- Write the amf_ metadata columns and name Amfelt in what connectors ship

### v2.10.1 (October 9, 2026)

**Bug Fixes**

- Bump golang.org/x/net to v0.60.0 (GO-2026-6617)

### v2.10.0 (October 7, 2026)

**Features**

- Ship third-party notices with the vendored-code addendum

### v2.9.4 (October 7, 2026)

**Bug Fixes**

- Checkpoint every write so no target replays a write-ahead log

### v2.9.1 (October 4, 2026)

**Bug Fixes**

- Load non-string values into varchar columns as exact text

### v2.9.0 (October 4, 2026)

**Features**

- Record third-party marks per connector

### v2.8.0 (October 4, 2026)

**Features**

- Compare scope in the postgres and duckdb templates

### v2.7.1 (October 3, 2026)

**Bug Fixes**

- ship plugin-sdk v5.12.2 to keep credentials out of exported contracts

### v2.7.0 (October 2, 2026)

**Features**

- Postgres and duckdb sources answer the source connection test

### v2.6.1 (September 25, 2026)

**Bug Fixes**

- Require plugin-sdk v5.9.3 so a read-only-default role is not reported ok

### v2.6.0 (September 25, 2026)

**Features**

- Answer the target connection test

### v2.5.0 (August 21, 2026)

**Features**

- Pass the declared override surface to the renderer (ADR-117)

### v2.4.0 (August 21, 2026)

**Features**

- Declare the override surface and govern the declared tier (ADR-117)

### v2.3.0 (August 7, 2026)

**Features**

- Publish darwin/amd64 and windows/amd64

### v2.1.0 (August 3, 2026)

**Features**

- Add the source connector

### v2.0.2 (August 3, 2026)

**Bug Fixes**

- Carry hugeint's full range into Arrow

### v2.0.1 (August 3, 2026)

**Bug Fixes**

- Pin arrow_type across the datatypes map

### v2.0.0 (August 3, 2026)

**Breaking Changes**

- Supersede infusal/target-duckdb

### v1.0.0 (August 2, 2026)

**Features**

- Target connector on plugin-sdk /v5

## Licensing

Third-party open-source packages bundled by this connector, with their licenses. Attribution only: informational, not an evaluation of the connector.

### Open-source dependency licenses (56 packages)

| Package | License |
|-|-|
| [github.com/Masterminds/semver](https://pkg.go.dev/github.com/Masterminds/semver@v1.5.0?tab=licenses) | MIT |
| [github.com/apache/arrow-go/v18](https://pkg.go.dev/github.com/apache/arrow-go/v18@v18.8.0?tab=licenses) | Apache-2.0 AND BSD-3-Clause |
| [github.com/apapsch/go-jsonmerge/v2](https://pkg.go.dev/github.com/apapsch/go-jsonmerge/v2@v2.0.0?tab=licenses) | MIT |
| [github.com/bmatcuk/doublestar/v4](https://pkg.go.dev/github.com/bmatcuk/doublestar/v4@v4.10.3?tab=licenses) | MIT |
| [github.com/duckdb/duckdb-go-bindings](https://pkg.go.dev/github.com/duckdb/duckdb-go-bindings@v0.10506.0?tab=licenses) | MIT |
| [github.com/duckdb/duckdb-go-bindings/lib/linux-amd64](https://pkg.go.dev/github.com/duckdb/duckdb-go-bindings/lib/linux-amd64@v0.10506.0?tab=licenses) | MIT |
| [github.com/duckdb/duckdb-go/v2](https://pkg.go.dev/github.com/duckdb/duckdb-go/v2@v2.10506.0?tab=licenses) | MIT |
| [github.com/fsnotify/fsnotify](https://pkg.go.dev/github.com/fsnotify/fsnotify@v1.10.1?tab=licenses) | BSD-3-Clause |
| [github.com/gabriel-vasile/mimetype](https://pkg.go.dev/github.com/gabriel-vasile/mimetype@v1.4.15?tab=licenses) | MIT |
| [github.com/go-cmd/cmd](https://pkg.go.dev/github.com/go-cmd/cmd@v1.4.4?tab=licenses) | MIT |
| [github.com/go-playground/locales](https://pkg.go.dev/github.com/go-playground/locales@v0.14.1?tab=licenses) | MIT |
| [github.com/go-playground/universal-translator](https://pkg.go.dev/github.com/go-playground/universal-translator@v0.18.1?tab=licenses) | MIT |
| [github.com/go-playground/validator](https://pkg.go.dev/github.com/go-playground/validator@v9.31.0+incompatible?tab=licenses) | MIT |
| [github.com/go-playground/validator/v10](https://pkg.go.dev/github.com/go-playground/validator/v10@v10.30.5?tab=licenses) | MIT |
| [github.com/go-viper/mapstructure/v2](https://pkg.go.dev/github.com/go-viper/mapstructure/v2@v2.5.0?tab=licenses) | MIT |
| [github.com/goccy/go-json](https://pkg.go.dev/github.com/goccy/go-json@v0.10.6?tab=licenses) | MIT |
| [github.com/goccy/go-yaml](https://pkg.go.dev/github.com/goccy/go-yaml@v1.19.2?tab=licenses) | MIT |
| [github.com/google/flatbuffers](https://pkg.go.dev/github.com/google/flatbuffers@v25.12.19+incompatible?tab=licenses) | Apache-2.0 |
| [github.com/google/uuid](https://pkg.go.dev/github.com/google/uuid@v1.6.0?tab=licenses) | BSD-3-Clause |
| [github.com/hashicorp/errwrap](https://pkg.go.dev/github.com/hashicorp/errwrap@v1.1.0?tab=licenses) | MPL-2.0 |
| [github.com/hashicorp/go-multierror](https://pkg.go.dev/github.com/hashicorp/go-multierror@v1.1.1?tab=licenses) | MPL-2.0 |
| [github.com/iancoleman/strcase](https://pkg.go.dev/github.com/iancoleman/strcase@v0.3.0?tab=licenses) | MIT |
| [github.com/jackc/pgpassfile](https://pkg.go.dev/github.com/jackc/pgpassfile@v1.0.0?tab=licenses) | MIT |
| [github.com/jackc/pgservicefile](https://pkg.go.dev/github.com/jackc/pgservicefile@v0.0.0-20240606120523-5a60cdf6a761?tab=licenses) | MIT |
| [github.com/jackc/pgx/v5](https://pkg.go.dev/github.com/jackc/pgx/v5@v5.11.0?tab=licenses) | MIT |
| [github.com/jackc/puddle/v2](https://pkg.go.dev/github.com/jackc/puddle/v2@v2.2.2?tab=licenses) | MIT |
| [github.com/jinzhu/copier](https://pkg.go.dev/github.com/jinzhu/copier@v0.4.0?tab=licenses) | MIT |
| [github.com/klauspost/compress](https://pkg.go.dev/github.com/klauspost/compress@v1.19.2?tab=licenses) | Apache-2.0 AND BSD-3-Clause AND MIT |
| [github.com/klauspost/cpuid/v2](https://pkg.go.dev/github.com/klauspost/cpuid/v2@v2.4.0?tab=licenses) | MIT |
| [github.com/leodido/go-urn](https://pkg.go.dev/github.com/leodido/go-urn@v1.5.0?tab=licenses) | MIT |
| [github.com/lmittmann/tint](https://pkg.go.dev/github.com/lmittmann/tint@v1.2.1?tab=licenses) | MIT |
| [github.com/mattn/go-isatty](https://pkg.go.dev/github.com/mattn/go-isatty@v0.0.24?tab=licenses) | MIT |
| [github.com/mitchellh/go-wordwrap](https://pkg.go.dev/github.com/mitchellh/go-wordwrap@v1.0.1?tab=licenses) | MIT |
| [github.com/oapi-codegen/runtime](https://pkg.go.dev/github.com/oapi-codegen/runtime@v1.7.0?tab=licenses) | Apache-2.0 |
| [github.com/pelletier/go-toml/v2](https://pkg.go.dev/github.com/pelletier/go-toml/v2@v2.3.1?tab=licenses) | MIT |
| [github.com/pierrec/lz4/v4](https://pkg.go.dev/github.com/pierrec/lz4/v4@v4.1.29?tab=licenses) | BSD-3-Clause |
| [github.com/sagikazarmark/locafero](https://pkg.go.dev/github.com/sagikazarmark/locafero@v0.12.0?tab=licenses) | MIT |
| [github.com/santhosh-tekuri/jsonschema/v6](https://pkg.go.dev/github.com/santhosh-tekuri/jsonschema/v6@v6.0.3?tab=licenses) | Apache-2.0 |
| [github.com/spf13/afero](https://pkg.go.dev/github.com/spf13/afero@v1.15.0?tab=licenses) | Apache-2.0 |
| [github.com/spf13/cast](https://pkg.go.dev/github.com/spf13/cast@v1.10.0?tab=licenses) | MIT |
| [github.com/spf13/cobra](https://pkg.go.dev/github.com/spf13/cobra@v1.10.2?tab=licenses) | Apache-2.0 |
| [github.com/spf13/pflag](https://pkg.go.dev/github.com/spf13/pflag@v1.0.10?tab=licenses) | BSD-3-Clause |
| [github.com/spf13/viper](https://pkg.go.dev/github.com/spf13/viper@v1.21.0?tab=licenses) | MIT |
| [github.com/subosito/gotenv](https://pkg.go.dev/github.com/subosito/gotenv@v1.6.0?tab=licenses) | MIT |
| [github.com/xo/dburl](https://pkg.go.dev/github.com/xo/dburl@v0.53.0?tab=licenses) | MIT |
| [github.com/zeebo/xxh3](https://pkg.go.dev/github.com/zeebo/xxh3@v1.1.0?tab=licenses) | BSD-2-Clause |
| [go.yaml.in/yaml/v3](https://pkg.go.dev/go.yaml.in/yaml/v3@v3.0.5?tab=licenses) | MIT |
| [golang.org/x/crypto](https://pkg.go.dev/golang.org/x/crypto@v0.57.0?tab=licenses) | BSD-3-Clause |
| [golang.org/x/exp](https://pkg.go.dev/golang.org/x/exp@v0.0.0-20260410095643-746e56fc9e2f?tab=licenses) | BSD-3-Clause |
| [golang.org/x/net](https://pkg.go.dev/golang.org/x/net@v0.60.0?tab=licenses) | BSD-3-Clause |
| [golang.org/x/sync](https://pkg.go.dev/golang.org/x/sync@v0.24.0?tab=licenses) | BSD-3-Clause |
| [golang.org/x/sys](https://pkg.go.dev/golang.org/x/sys@v0.48.0?tab=licenses) | BSD-3-Clause |
| [golang.org/x/text](https://pkg.go.dev/golang.org/x/text@v0.43.0?tab=licenses) | BSD-3-Clause |
| [google.golang.org/genproto/googleapis/rpc](https://pkg.go.dev/google.golang.org/genproto/googleapis/rpc@v0.0.0-20260928230214-8a89bd6388cc?tab=licenses) | Apache-2.0 |
| [google.golang.org/grpc](https://pkg.go.dev/google.golang.org/grpc@v1.85.0?tab=licenses) | Apache-2.0 |
| [google.golang.org/protobuf](https://pkg.go.dev/google.golang.org/protobuf@v1.36.12?tab=licenses) | BSD-3-Clause |