Skip to content
PipelineZ
Get Started

Amazon S3

The s3 connector reads and writes Amazon S3 through DuckDB’s httpfs extension. Every read compiles to read_parquet/read_csv/read_json/read_xlsx/read_avro (avro read only) and every write to a native COPY … TO 's3://…', so data never passes through .NET. There is no universal-tier fallback: this connector is native-only in both directions.

Connection

Section titled “Connection”
connections.yml
lake:
  connector: s3
  access_key: ${AWS_ACCESS_KEY_ID}
  secret_key: ${AWS_SECRET_ACCESS_KEY}
  root: my-bucket/exports
  region: us-east-1
Key Required Default Meaning
access_key Yes — AWS access key id.
secret_key Yes — AWS secret access key.
root No — bucket or bucket/prefix. Fills the bucket and path prefix for every entity that omits its own bucket/path.
region No us-east-1 AWS region for the scoped DuckDB secret.
endpoint No AWS’s own endpoint Override host, for an S3-compatible store (for example MinIO).
url_style No vhost vhost or path.
use_ssl No true Whether requests use HTTPS.

Read options

Section titled “Read options”

Shared keys (columns, sync, retry under read:, and rate_limit on the connection) are documented in connections.yml reference.

Key Meaning
bucket Object bucket. Defaults to the connection’s root bucket. An entity naming its own bucket does not inherit the root’s prefix.
path Object key, relative to the resolved prefix. Defaults to <entity>.<format>. Supports globs and calendar tokens (see below), except xlsx: it reads exactly one workbook, so path must name a single object — a glob or date-templated path matching more than one object is PZ0361 (“xlsx reads one workbook per entity; ‘path:’ must name a single file, not a glob”).
format csv, tsv, parquet, json, xlsx, or avro. Defaults to parquet.
columns Column-to-type contract. With no contract, csv/tsv/json/xlsx auto-detect their schema; declaring one prunes the scan to just those columns. For xlsx/avro the contract is applied as a cast around the read, so a declared numeric type replaces read_xlsx’s default DOUBLE.
delimiter csv only, one ASCII character other than a quote, newline, or carriage return. Defaults to ,. tsv is fixed to tab; setting delimiter on it is PZ0362.
layout json only. ndjson (default, newline-delimited) or array (one top-level JSON array). Both read natively here — s3 has no universal tier to refuse array on.
sheet xlsx only. Sheet name to read. Defaults to the workbook’s first sheet.
header xlsx only. Boolean, defaults to true; false yields DuckDB’s generated A1/B1/… column names, so a declared columns: contract must use those names too.

A path with calendar tokens narrows a windowed or incremental read to the watermark’s cover instead of scanning every object under the prefix. See Path templating below.

connections.yml
lake:
  connector: s3
  access_key: ${AWS_ACCESS_KEY_ID}
  secret_key: ${AWS_SECRET_ACCESS_KEY}
  root: my-bucket
  entities:
    events:
      read:
        path: raw/events/{yyyy}/{MM}/{dd}/*.parquet
        sync:
          mode: incremental
          cursor: event_time

Write options

Section titled “Write options”
Key Required Default Meaning
bucket No The connection’s root bucket Destination bucket.
path No "" Destination prefix, relative to the resolved root.
format Yes — csv, tsv, parquet, or json. No default: every write must declare it. avro is read-only; writing it is PZ0361. xlsx write is localfiles-only (DuckDB’s excel writer aborts the whole process on a failed remote write); writing it here is PZ0361 too.
delimiter No , csv only, one ASCII character other than a quote, newline, or carriage return. tsv is fixed to tab; setting delimiter on it is PZ0362.
layout No ndjson json only. ndjson (newline-delimited) or array (one top-level JSON array). Both write natively here — s3 has no universal tier to refuse array on.

Only strategy: append and strategy: replace are supported; there is no merge for an object store. replace writes one stable object named <entity>.<format>; append writes a new, uniquely-named object per run so repeated runs accumulate objects rather than overwrite one.

partition_by is not available on this connector: writes go only through the native COPY path, which has no per-row fan-out. A single run writes to a single object or prefix.

Path templating

Section titled “Path templating”

path accepts the calendar tokens {yyyy}, {yy}, {MM}, {dd}, {HH}, {mm}, which must appear coarse to fine with no gaps (for example {yyyy}/{MM}/{dd}, never {MM}/{yyyy}). On a read, pz uses them only to narrow a windowed or incremental entity to the smallest set of objects that covers the watermark window, so a query never rescans objects outside it.

Capabilities

Section titled “Capabilities”
Flag Meaning
NativeScan Reads compile to a native DuckDB scan.
NativeCopy Writes compile to a native DuckDB COPY.
ReplaceWrites Supports strategy: replace.
BoundedWindow Honors an entity’s upper watermark bound.
PathTemplating Understands calendar tokens in path.

Notes

Section titled “Notes”
  • Forcing the universal tier (engine.force_universal, or setting files_per_partition) fails with PZ0312: this connector has no universal read or write path at all.
  • Any S3-compatible store, not just AWS, is reachable through the endpoint override.
  • An object key is an opaque slash-delimited string with no filesystem resolution behind it, so a .. segment anywhere in path is always refused with PZ0365, naming the connection and dataset/output — regardless of whether root: is declared.
  • xlsx and avro run through DuckDB’s excel/avro extensions, installed and loaded on first use — see DuckDB extensions for the one-time network download this needs.
  • xlsx reads over httpfs fine, but only localfiles can write one: “xlsx write is localfiles-only; DuckDB’s excel writer aborts the whole process when a remote write fails, so s3 refuses it – write the workbook with the localfiles connector, or choose parquet, csv or json.”
Section titled “Related”