Value-Based Output Partitioning (partition_by)

When to use

Set partition_by to split one export’s rows into one destination sub-folder per value bucket of a column — the Hive-style col=value/ layout that warehouses and query engines (Snowflake external tables, BigQuery external / Hive-partitioned tables, Spark, DuckDB, Athena) discover automatically. Best for:

• Daily/monthly snapshots that land each day’s rows under its own prefix
• Backfilling history into a partitioned lake layout in one command
• Feeding a warehouse that prunes partitions by a date column

partition_by is orthogonal to mode: each partition runs the export’s own mode, so mode: chunked chunks within a partition.

Required fields

• partition_by — the column whose value buckets the rows (a DATE / TIMESTAMP / TIMESTAMPTZ column).
• a {partition} token in destination.path or destination.prefix — Rivet refuses the run without it, because every partition would otherwise overwrite the same prefix.

Optional fields

• partition_granularity — day (default), month, or year.

Minimal config

source:
  type: postgres
  url: "postgresql://user:pass@host:5432/dbname"

exports:
  - name: events
    table: events
    partition_by: created_at
    partition_granularity: day
    format: parquet
    destination:
      type: s3
      bucket: my-bucket
      prefix: "events/{partition}/"      # → events/created_at=2023-01-01/

Run it

rivet run --config events.yaml --validate

What happens

1. Rivet reads the [min, max] span of partition_by from the source (SELECT min(col), SELECT max(col) over your query).
2. It generates one contiguous bucket per day/month/year across that span.
3. Each bucket becomes its own export: the query is wrapped as SELECT * FROM (<your query>) WHERE col >= '<lo>' AND col < '<hi>' (half-open: lo inclusive, hi exclusive — no row counted twice), and {partition} resolves to col=value.
4. Rows whose partition_by value is NULL land in col=__HIVE_DEFAULT_PARTITION__/ (the Hive default-partition convention) so no row is ever silently dropped.

Each partition is an independent, complete output prefix — its own manifest.json and _SUCCESS — so it can be validated and consumed on its own.

Example output layout

events/
  created_at=2023-01-01/  manifest.json  _SUCCESS  events__2023-01-01_*.parquet
  created_at=2023-01-02/  manifest.json  _SUCCESS  events__2023-01-02_*.parquet
  ...
  created_at=__HIVE_DEFAULT_PARTITION__/  manifest.json  _SUCCESS  ...

DuckDB reads the whole tree as one partitioned dataset and recovers the partition column from the path:

SELECT created_at, count(*)
FROM read_parquet('events/**/*.parquet', hive_partitioning = true)
GROUP BY 1;

Granularity

Notes and limits

• Cloud prefixes: put a / after {partition}. Object stores have no directories — the part filename is appended to the resolved prefix verbatim. prefix: "events/{partition}/" yields events/created_at=2023-01-01/part.parquet; omitting the trailing slash concatenates into …created_at=2023-01-01part.parquet. (Local path: joins as directories, so the slash is optional there.)
• Index the partition column. Expansion runs three probes over the base query (min, max, NULL-count). Without an index on partition_by those are up to three full scans before the first row is exported.
• Time zones. Bucket bounds are emitted as YYYY-MM-DD literals on PostgreSQL and MySQL; SQL Server gets the unseparated YYYYMMDD form, which T-SQL parses as ISO regardless of the session’s DATEFORMAT/language. For a TIMESTAMPTZ column the comparison happens at the session time zone — pin it (e.g. MySQL SET time_zone = '+00:00') when the exact day boundary matters.
• Not compatible with mode: time_window (time_window already filters by a rolling window). Use partition_by with full, chunked, or incremental.
• Not compatible with mode: cdc (CDC reads the log, not a query), a load: block (per-export or top-level: the loader would load a single partition’s manifest), or a MongoDB source (the bucket probes are SQL).
• Not compatible with chunk_by_key (keyset). Each partition reads its bucket as a subquery around the table, and keyset seek pagination over that shape is not supported — Rivet rejects the combination up front. A table: export keeps its table per partition, so a range chunk_column is type-checked and an unset one auto-resolves to the primary key as usual.
• --parallel-export-processes is disabled while partitioning is active (child processes re-load the config and can’t see the synthesised partitions); the run executes in-process.
• plan / check do not expand partitions yet. They report the parent export as one un-partitioned job (its {partition} token stays literal in the shown path, the row estimate is the whole span, strategy is the base mode). Treat their output as the per-partition shape, not the campaign.
• Validating a single partition today: point validate at the concrete prefix — rivet validate --config c.yaml --export events --prefix events/created_at=2023-01-01. Validating every partition of an export by its parent name in one command is not yet wired.

Choosing a chunking strategy per partition (large partitions)

partition_by is orthogonal to mode, so each partition runs the export’s mode inside itself. The right choice depends on how big a single partition is and whether the chunk key is dense within it. Consider a date that holds 100 M rows:

Row counts are always exact regardless of the choice — these trade-offs are about source load and file fan-out, not correctness. There is no keyset/seek option for a partition today (see the chunk_by_key limit above), so for a genuinely huge partition with a sparse key the practical levers are a correlated range key or a finer granularity.