Rivet type mapping contract

This document describes how Rivet maps source column types to logical RivetType values and Arrow/Parquet/CSV representations. It is aligned with the automated suite in tests/type_roundtrip/ and tests/live_type_golden.rs.

The short version

Wondering “will my decimals / UUIDs / JSON / timestamps silently break on the way out?” — the short answer is no:

• Decimals never become floats — DECIMAL/NUMERIC export as exact Arrow Decimal128/Decimal256 when precision and scale are known.
• UUID and JSON keep native types — Parquet gets native LogicalType::Uuid / LogicalType::Json, not opaque strings.
• Timestamps preserve the instant — the point in time round-trips (Parquet keeps the zone; CSV emits the instant normalised to UTC with a trailing Z, while naive timestamps render bare).
• Rivet fails loud, not silent — a lossy or unmapped type is named by rivet check and aborts the run, never quietly truncated.

The per-engine tables below are the precise contract; this is just the gist.

Guarantees (v0.18.0)

• DECIMAL / NUMERIC are never silently converted to float. They export as Arrow Decimal128 / Decimal256 in Parquet when precision and scale are known (column override, catalog hint, or PostgreSQL wire metadata).
• Binary (BYTEA, BLOB, BINARY/VARBINARY with charset 63) stays Arrow Binary in Parquet.
• Float NaN / ±Infinity are preserved. Parquet stores them natively (IEEE-754); CSV emits the literal NaN / inf / -inf (and -0 keeps its sign) rather than an empty cell — an empty cell would silently conflate a real special value with NULL. Note these are float values: decimal / NUMERIC has no NaN representation and a NaN/Infinity payload there is rejected at extract time, not coerced. Strict CSV loaders that expect Infinity over inf should configure their float parser accordingly; Parquet needs no such care.
• JSON / JSONB is valid JSON text in the file, paired with the Arrow arrow.json canonical extension type so parquet-rs emits native LogicalType::Json in the Parquet footer. The Rivet field metadata (rivet.logical_type=json) stays for Rivet-aware consumers.
• UUID exports as canonical 16-byte FixedSizeBinary(16) paired with the Arrow arrow.uuid canonical extension; parquet-rs emits native LogicalType::Uuid. Downstream Parquet readers (DuckDB, ClickHouse 25.x+, pyarrow, BigQuery autodetect) recover the UUID type without a cast.
• Timestamps: TIMESTAMPTZ and MySQL TIMESTAMP use UTC semantics (timezone: Some("UTC")); naive TIMESTAMP / DATETIME have no timezone.
• Nullability is preserved in Arrow schema and export.

What rivet init auto-detects (and what needs an override)

rivet init reads the source database’s catalog / wire-protocol metadata to build the initial rivet.yaml. Whether a column lands as a native logical type in the resulting Parquet depends on whether the source server advertises the semantic — Rivet never guesses from column names or sample values, by design (a wrong guess is silent corruption; an honest “I don’t know” is a config knob).

Adding overrides looks like:

exports:
  - name: users
    query: "SELECT id, uid, amount FROM users"
    columns:
      uid: uuid              # MySQL VARCHAR(36) → UUID semantic
      amount: decimal(18,2)  # also valid for MySQL DECIMAL without catalog
      event_ts: timestamp_ns # keep SQL Server datetime2(7)'s 100 ns tick (see gap 4)

Supported override types: bool, int2/int4/int8, float4/float8, decimal(p,s), date, timestamp, timestamp_ns, timestamp_tz, timestamp_tz_ns, text, binary, json, uuid. The _ns timestamp variants preserve sub-microsecond precision (range 1677–2262; see gap 4) — the plain timestamp is microsecond with full date range.

The override path is safe: a uid: uuid declaration tries to parse each cell — either 16 raw bytes (BINARY(16) layout) or a 36-char canonical text form. Parse failures emit NULL, never silent garbage. Same convention as decimal overrides whose values do not parse cleanly.

rivet init does not apply heuristics (“column is 36 chars wide and named uid_* → probably UUID”) — guessing risks misclassifying a non-UUID varchar and silently producing wrong-shape Parquet. Operators who want UUID semantics on a MySQL column add the override explicitly.

Test commands

make test-types              # offline mapping contracts (PR-fast)
make test-types-live         # full matrix; requires docker compose
make test-types-validators   # PG/MySQL/SQL Server → Parquet → {DuckDB, ClickHouse} round-trip

test-types-validators re-runs the same canonical PG / MySQL / SQL Server type matrix used by test-types-live, but writes the Parquet into the shared bind-mount under tests/.live-tmp/ and feeds it through three independent readers: DuckDB, ClickHouse (both from docker-compose.yaml), and pyarrow (installed alongside duckdb in the same container). Each reader catches what the others cannot:

Local-reader type fidelity is now covered by the cross-tool harness’ type-loss matrix (dev/bench/smoke.py, rendered in report.html): every source column vs each tool’s Parquet type family. A BigQuery cloud-load type-diff (load each Rivet Parquet via bq load --autodetect, assert decimal sums round-trip) is not currently in the harness — it needs a GCP project + bq auth and is tracked for a future cloud dimension (notably the earlier findings were: LogicalType::Json does not autoload as native BQ JSON — it falls back to BYTES/STRING; values are valid JSON but operators need an explicit --schema='attrs:JSON,...' to query the structure).

In addition, two structural-only tests pin the Parquet layout itself — parquet_schema.rs for physical + logical types per column, and parquet_metadata.rs for the rivet.native_type / rivet.fidelity / rivet.logical_type field metadata.

Coverage extensions live in dedicated files: compression_matrix.rs re-runs the export under zstd / snappy / gzip / none and asserts value parity across all four codecs; csv_load.rs replays the matrix through DuckDB read_csv_auto; pg_edge_cases.rs covers decimal precision boundaries (38, 39 / Decimal128↔Decimal256), tz timestamps pre-epoch and far-future, JSON deep nesting + unicode keys + i64 edges, arrays with NULL elements, large single-cell strings.

v0.7.8 breaking change: UUID export layout

UUID columns (PG native uuid type and MySQL columns explicitly overridden as columns: { col: uuid }) now export as FixedSizeBinary(16) instead of hyphenated Utf8. This is what lets parquet-rs emit native LogicalType::Uuid so downstream readers (DuckDB, ClickHouse 25.x+, pyarrow, BigQuery autodetect) recover the UUID type without a cast.

What this changes:

• Parquet schema: BYTE_ARRAY + LogicalType::String → FIXED_LEN_BYTE_ARRAY + LogicalType::Uuid.
• On-disk bytes: 36-char canonical ASCII (a0eebc99-9c0b-…) → 16 raw bytes (the same UUID, compact encoding).
• CSV: unchanged — the CSV writer still emits hyphenated lowercase text.
• DuckDB autoload: VARCHAR → UUID native type. A view like SELECT uid::VARCHAR FROM read_parquet(...) keeps working because DuckDB’s UUID::VARCHAR cast yields the canonical form.
• ClickHouse 24.8 autoload: String → FixedString(16) (the bytes, not the text). To get back the canonical string, use lower(hex(uid)) and reformat, or upgrade to ClickHouse 25.x which decodes LogicalType::Uuid directly.

Consumers reading the old Utf8-shaped Parquet files keep working — only files produced by Rivet ≥ v0.7.8 carry the new layout.

Findings & fixes from triangulating against external readers

Driving the matrix through DuckDB + ClickHouse + pyarrow exposed three real defects in the PG / MySQL drivers; all have been fixed in v0.7.8:

• PG arrays with NULL elements were silently lost. The driver decoded ARRAY[1, NULL, 3] via try_get::<Vec<i32>>, which errors on a NULL element; the error was swallowed and a whole-row NULL was written. Fixed by deserializing as Vec<Option<T>> and pushing nulls through ListBuilder::append_null (src/source/postgres/arrow_convert.rs).
• MySQL ENUM / SET were misclassified as String. They arrive on the wire as MYSQL_TYPE_STRING / MYSQL_TYPE_VAR_STRING with the ENUM_FLAG / SET_FLAG set, not as MYSQL_TYPE_ENUM. The mapper now checks the flag and emits RivetType::Enum so the rivet.logical_type=enum Parquet metadata is preserved.
• MySQL native_type lost precision. tinyint unsigned, tinyint(1), bit(1), char vs varchar, binary vs varbinary all collapsed to a single label. The mapper now distinguishes them via column_type() + flags() + character_set.

Known limitations (pinned by *currently_fails* tests)

• PG numeric(p, -s) (negative scale) cannot be written to Parquet — the spec requires non-negative DECIMAL scale. Test pg_edge_decimal_negative_scale_currently_fails_at_parquet_write pins the failure with a friendly error message so any future workaround must update the test deliberately.
• DuckDB’s DECIMAL is capped at precision 38 (HUGEINT-backed). For Parquet files with precision > 38 DuckDB silently returns DOUBLE. Our file still carries LogicalType::Decimal(p, s) correctly — pyarrow decodes it as Decimal256. Verified in pg_edge_decimal_boundaries_round_trip.

PostgreSQL

MySQL

SQL Server (MSSQL)

Unmapped SQL Server types resolve to Unsupported and fail loudly at schema build unless a columns: override maps them.

MongoDB (JSON-blob model)

MongoDB has no fixed per-collection schema and no information_schema, so Rivet does not map per-field SQL types the way the three SQL engines do. Every document exports as exactly two columns:

Per-field typing is deferred to the warehouse (PARSE_JSON → VARIANT on Snowflake, native JSON on BigQuery / DuckDB). This is lossless and schema-drift-proof: a new field in a document never breaks a load. Schema inference / auto-discovery into typed columns is deliberately out of OSS scope. CDC (change streams) emits the same two-column shape prefixed with the __op / __pos / __seq meta columns. See reference/mongodb.md for the full contract.

Known gaps (tracked)

1. Nested arrays, ranges, inet, PostGIS, geometry: not in the type matrix — they resolve to Unsupported and fail at schema build unless a columns: override maps them.

2. Nullability: every exported column is OPTIONAL; a source NOT NULL constraint is not propagated into the Parquet schema (ADR-0016, deferred to v0.8 Phase A).

3. CSV complex types: arrays (List), Decimal256 (precision > 38), and non-UUID fixed binary have no CSV cell — the export fails loudly naming the column (see CSV serialization) rather than silently writing empty values.

4. SQL Server datetime2 sub-microsecond precision — default is microsecond; nanosecond is opt-in. rivet maps datetime2 to Timestamp(µs) by default, because Arrow nanosecond timestamps are i64 ns and span only 1677-09-21 .. 2262-04-11, while datetime2 spans 0001–9999 — a blanket ns mapping would silently corrupt any value outside that window (a far worse bug than the precision gap). So the 7th fractional digit of a datetime2(7) (100 ns) is truncated to µs by default; lossless for datetime2(6) and below.

   To preserve the 100 ns tick on a column whose data is inside the ns range, opt in per column with a timestamp_ns override:

   columns:
     event_ts: timestamp_ns       # naive; use timestamp_tz_ns for datetimeoffset
   

   The Parquet file then carries Timestamp(ns) and the full precision survives (verified live 2026-06-07: DuckDB reads it natively as TIMESTAMP_NS, …12:00:00.1234567 intact; the default µs path truncates to .123456). Caveats (all verified live 2026-06-07):

   • A value outside 1677–2262 exports as NULL (Arrow ns range).
   • DuckDB — native TIMESTAMP_NS, fully lossless.
   • Snowflake — autoloads as NUMBER(38,0) (raw nanos); TO_TIMESTAMP_NTZ(col, 9) recovers a lossless TIMESTAMP_NTZ (it holds 9 digits — the 7th survives).
   • BigQuery — autoloads as INT64 (raw nanos, lossless as an integer); a native TIMESTAMP_MICROS(DIV(col,1000)) is lossy (BigQuery TIMESTAMP is microsecond — the 7th digit drops). Keep the default timestamp for BigQuery unless you carry the raw nanos.

   Incremental mode: the default µs cursor on a datetime2(7) lands one tick below the source max, re-exporting the boundary row every run — use timestamp_ns (the cursor literal then carries all 9 digits), a datetime2(6) (or coarser) cursor, or an integer/identity cursor.

(MySQL DECIMAL now resolves its precision/scale from the wire column definition — no override needed; see the MySQL section.)

Fidelity labels

See src/types/fidelity.rs.

CSV serialization

CSV shares the same Arrow RecordBatch as Parquet, so values are identical — only the text rendering differs (src/format/csv.rs):

CSV has no cell representation for list (arrays), Decimal256 (precision > 38), or non-UUID fixed binary. Rather than silently write an empty value, the export fails at writer creation naming the column — use format: parquet or drop the column from the query.

Loading CSV into a warehouse: unlike Parquet (whose loader will not coerce a declared type — see below), BigQuery’s CSV loader honors a declared --schema. Bare --autodetect infers decimal text as FLOAT (precision-lossy) and every semantic type (uuid / json / bytea) as STRING; declare NUMERIC / JSON in the load schema, or recover post-load with PARSE_JSON / FROM_HEX as for Parquet.

Downstream targets (autoload vs native)

Parquet/CSV preserve values and Arrow physical types. Warehouse engines infer types from physical schema on autoload (e.g. JSON columns appear as STRING / VARCHAR). Native target types (JSON, VARIANT, UUID, …) require a materialization step (cast SQL, load schema, or typed view).

See ADR-0014: Target type materialization for the full matrix (DuckDB, BigQuery, Snowflake, ClickHouse) and planned rivet check --type-report --target <engine> extensions.

Verified physical autoload (DuckDB + ClickHouse, v0.18.0)

make test-types-validators re-reads every PG / MySQL matrix column through two independent engines and pins the autoload type. The full matrix:

Round-trip values that the suite pins per row: sum(decimal * 10^scale) matches the in-process Arrow check; json_valid / isValidJSON returns true for every row; TRY_CAST(uid AS UUID) / toUUID(uid) parses every row; BLOB columns compare byte-for-byte (hex(...)); the empty list survives; null bitmaps propagate. The MySQL BIGINT UNSIGNED max value (2^64 − 1) is the load-bearing assertion that exact-width unsigned ints are not silently overflowed to i64.

BigQuery autoload & recovery (verified live)

BigQuery’s Parquet loader is weaker than DuckDB’s: it ignores several Parquet logical types on autoload and — critically — will not coerce a column to a different declared type on load. A bq load into a table that declares JSON/DATETIME is rejected (Field x has changed type from JSON to BYTES), so native types are recovered with a post-load transform, not a load schema.

Rivet writes the Parquet list element as item (arrow-rs default, not the spec’s element), so even --parquet_enable_list_inference yields REPEATED RECORD{item} rather than a clean REPEATED <scalar> — the UNNEST flatten above is required.

rivet check --type-report --target bigquery prints the per-column autoload type, the native type, and a ready-to-run recovery CREATE TABLE … AS SELECT over the autoloaded <table>__staging. Set exports[].target: bigquery to get it without the CLI flag. DuckDB needs none of this — it autoloads every logical type natively.

Snowflake autoload & recovery (verified live)

Snowflake’s INFER_SCHEMA + COPY infers physical types only, so the same semantic types degrade — and the INFER_SCHEMA column names come back lowercase and case-sensitive, so the recovery SELECT must double-quote every source reference ("col").

The load preamble the recovery depends on: CREATE FILE FORMAT … TYPE=PARQUET BINARY_AS_TEXT=FALSE, ALTER SESSION SET TIMEZONE='UTC', CREATE TABLE … USING TEMPLATE (… INFER_SCHEMA …), COPY … MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE.

rivet check --type-report --target snowflake (--target sf) emits the per-column autoload/native types and the post-load CREATE OR REPLACE TABLE … recovery over <table>__staging. Set exports[].target: snowflake to skip the flag.