pathwaycom/pathway · error · ValueError

sort_by cannot be used with the snapshot table type: a snaps

Error message

sort_by cannot be used with the snapshot table type: a snapshot reflects the current state of the table regardless of the order changes are applied within a minibatch, and reordering would corrupt the upsert/delete sequence. Remove sort_by, or use output_table_type="stream_of_changes".

What it means

Raised by pw.io.duckdb.write when sort_by is combined with output_table_type="snapshot". In snapshot mode an update is a deletion (-1) followed by an insertion (+1) of the same key; reordering changes within a minibatch can place the insertion before the deletion, so the upsert is silently wiped by the delete and rows are lost. sort_by has no effect on a snapshot's final state anyway.

Source

Thrown at python/pathway/io/duckdb/__init__.py:264

        LIMIT 5;
    """
    _check_entitlements("duckdb")

    is_snapshot_mode = output_table_type == SNAPSHOT_OUTPUT_TABLE_TYPE
    if not is_snapshot_mode and primary_key is not None:
        raise ValueError(
            "primary_key can only be specified for the snapshot table type"
        )
    if is_snapshot_mode and not primary_key:
        raise ValueError("primary_key must be specified for the snapshot table type")
    if is_snapshot_mode and sort_by is not None:
        # In snapshot mode an update is a deletion (-1) followed by an insertion
        # (+1) of the same key. sort_by reorders the changes within a minibatch
        # and can place the insertion before the deletion, so the upsert is
        # immediately wiped by the delete — silently losing rows. sort_by has no
        # effect on a snapshot's final (unordered, keyed) state anyway, so reject
        # the combination instead of corrupting the output.
        raise ValueError(
            "sort_by cannot be used with the snapshot table type: a snapshot "
            "reflects the current state of the table regardless of the order "
            "changes are applied within a minibatch, and reordering would corrupt "
            "the upsert/delete sequence. Remove sort_by, or use "
            'output_table_type="stream_of_changes".'
        )

    database_str = fspath(database)
    _reject_directory_path(database_str)

    if detach_between_batches and database_str == IN_MEMORY_DATABASE:
        # An in-memory database ceases to exist when its last connection closes,
        # so detaching after every batch would silently drop all written data.
        raise ValueError(
            'detach_between_batches=True cannot be used with database=":memory:": '
            "an in-memory DuckDB database is dropped when its last connection "
            "closes, so all data would be lost after every batch. Use an on-disk "
            "database file instead."

View on GitHub (pinned to fa2f74a464)

Solutions

  1. Remove the sort_by argument when using output_table_type="snapshot" — order does not affect the final snapshot.
  2. If ordering matters to you, switch back to output_table_type="stream_of_changes" and keep sort_by.

Example fix

# before
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.id, sort_by=t.ts)

# after
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.id)
Defensive patterns

Strategy: validation

Validate before calling

if output_table_type == "snapshot" and sort_by is not None:
    raise ValueError("sort_by is not applicable to snapshot output")

Prevention

When it happens

Trigger: pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.id, sort_by=t.ts) — any non-None sort_by with snapshot mode.

Common situations: Copying a sort_by argument from an existing stream_of_changes write when converting to snapshot mode; adding sort_by 'for deterministic output' without realizing snapshots are keyed and unordered.

Related errors

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/84ba1c14f2a53962. Report an issue: GitHub.