Skip to content

Targets

DuckDB

Load into DuckDB: Arrow appends, and Parquet files DuckDB reads itself.

Rows from Postgres and Python sources are appended to DuckDB as Arrow, inside the batch’s transaction.

Parquet files in S3, GCS or a local folder load directly: DuckDB reads them with read_parquet, with the same values SQLLoad’s own reader gives:

$ sqlload plan -v
…
├── route DuckDB reads directly from lake/events/ (nothing through the loader)

SQLLoad’s reader loads a files table instead when DuckDB can’t load it exactly:

  • another format (CSV, JSON, NDJSON);
  • a custom S3 endpoint (in the connection, or AWS_ENDPOINT or AWS_ENDPOINT_URL);
  • masked columns;
  • a file column named filename or file_row_number;
  • nanosecond or epoch timestamps, and other types DuckDB wouldn’t read exactly;
  • a column loaded as a different type than the files hold;
  • glob characters in a path.

A later batch whose files hold a column differently fails the load, naming the column.

Parquet files in a gcs connection load the same way when the connection sets an HMAC key (hmac_key_id and hmac_secret): DuckDB reads gs:// with it, as a secret that lasts only for the load’s session. DuckDB takes no service-account key, so without an HMAC key, or with a custom endpoint, SQLLoad reads the files instead, and sqlload plan -v <table> says why. plan reads the first file to load with DuckDB, and fails the table with the fix when it can’t, since the HMAC key isn’t what SQLLoad’s own reader uses:

$ sqlload plan -v
…
├── route DuckDB reads directly from gs://example-bucket/events/ (nothing through the loader)

DuckDB reads S3 with the keys SQLLoad finds for the s3 connection: from the environment or the shared credentials file. Without either, it uses the AWS credential chain, which it resolves again every four minutes as its keys may expire. The first load from S3 installs DuckDB’s httpfs extension into ~/.duckdb, and aws too when it uses the chain. That needs the network once. Local folders need nothing extra.

json_type = "variant" on a DuckDB or MotherDuck target creates json columns as DuckDB’s VARIANT instead of JSON, which holds each value as its text:

[targets.dev]
connection = "local"
loader_schema = "raw"
json_type = "variant"

Each value loads parsed, so objects, arrays, numbers and booleans keep their types: variant_typeof(payload) is OBJECT(user) and payload.user.id is 42. It applies to every json column: Python dicts and lists, JSON and NDJSON objects, shape = "record" records, nested values not unnested, and lists loaded as json. A column pinned native("VARIANT") loads the same way on any DuckDB or MotherDuck target. The default, json_type = "json", keeps JSON.

Changing json_type for a table that exists changes its json columns’ type. plan shows each one, such as payload json -> native("VARIANT") needs a table rebuild, and it runs as type_change_rebuild allows: by default with --allow-type-change-rebuild. The column keeps its values.

A DuckDB target opens with its part of the run’s memory budget: a memory_limit of half that part, at least 256MB, and one thread per 128 MiB of it, between two and the machine’s cores. plan shows both on its Memory line.

DuckDB keeps values of about 85 KiB and more in memory from their insert until a checkpoint writes them to the file, and a checkpoint rewrites the table’s last, unfinished row group (up to 122,880 rows) whole. So a table of few, wide rows needs memory that its commits’ bytes set, not its fetch budget. SQLLoad handles this for you:

  • Its commits are cut into pieces, each holding large values of at most a quarter of the memory_limit. See Large rows in DuckDB.
  • DuckDB’s automatic checkpoint is off during a load, since one that runs out of memory makes the database unusable until it’s opened with more memory. SQLLoad checkpoints after a piece when the large values waiting for one reach 40% of the memory_limit, and after the load of each table that held large values.
  • A database left with a write-ahead log, as after a crash, is checkpointed when the target opens, before any table loads.
  • A table whose last row group would hold more large values than a checkpoint can rewrite within the memory_limit fails before it commits, naming the run’s memory_limit that would fit it.

With memory_limit = "off", DuckDB keeps its own memory_limit, threads and automatic checkpoints, and commits aren’t cut into pieces. Only the row-group check stays, against DuckDB’s own memory_limit.

Loading 300 rows of 1 MiB each with a 256MB memory_limit succeeds for every write strategy, in pieces. A Postgres target stays bounded without pieces: the same 286 MB loads in about 225 MB with send = "at_commit" and 410 MB with send = "per_batch".