pathwaycom/pathway · error · ValueError

primary_key can only be specified for the snapshot table typ

Error message

primary_key can only be specified for the snapshot table type

What it means

Pathway's SQLite output connector accepts a `primary_key` argument only in snapshot mode (output_table_type="snapshot"), where it drives the INSERT ... ON CONFLICT UPSERT and DELETE statements. In the default stream_of_changes mode the connector appends `time`/`diff` metadata columns and appends every change, so a primary key has no meaning. Passing primary_key together with the default output_table_type therefore raises this ValueError at write() call time, before any data flows.

Source

Thrown at python/pathway/io/sqlite/__init__.py:299

    key and every ``-1`` event issues a DELETE against the matching row.
    A primary key must be supplied via ``primary_key``:

    >>> pw.io.sqlite.write(
    ...     t,
    ...     "pets.db",
    ...     "pets_snapshot",
    ...     output_table_type="snapshot",
    ...     primary_key=[t.owner, t.pet],
    ...     init_mode="replace",
    ... )

    Here ``(owner, pet)`` is the primary key, so at any point in time the
    ``pets_snapshot`` table contains one row per live ``(owner, pet)``
    pair — no history, no ``time`` / ``diff`` columns.
    """
    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")

    path_str = fspath(path)
    _reject_directory_path(path_str)

    value_fields = _format_output_value_fields(table)

    # SQLite identifier matching is case-insensitive (`ID` and `id` are
    # the same column), so any pair of schema columns whose names
    # differ only in case would make ``CREATE TABLE`` fail with a raw
    # ``duplicate column name`` driver error at pipeline-start. Surface
    # the collision here with a clear, Pathway-authored message
    # instead, matching how the ``time`` / ``diff`` reserved-name check
    # below works.
    case_groups: dict[str, list[str]] = {}

View on GitHub (pinned to fa2f74a464)

Solutions

1. Add output_table_type="snapshot" to the pw.io.sqlite.write() call if you want keyed upsert/delete semantics.
2. Remove the primary_key argument if you actually want the default append-only change log with time/diff columns.

Example fix

// before
pw.io.sqlite.write(t, "pets.db", "pets", primary_key=[t.owner, t.pet])

// after
pw.io.sqlite.write(
    t, "pets.db", "pets_snapshot",
    output_table_type="snapshot",
    primary_key=[t.owner, t.pet],
)

def validate_sqlite_write_args(output_table_type, primary_key):
    snapshot = output_table_type == "snapshot"
    if primary_key is not None and not snapshot:
        raise ValueError("primary_key requires output_table_type='snapshot'")
    return True

try:
    pw.io.sqlite.write(t, path, name, primary_key=keys)
except ValueError as e:
    if "primary_key can only be specified" in str(e):
        # retry in snapshot mode or drop the key
        pw.io.sqlite.write(t, path, name, output_table_type="snapshot", primary_key=keys)
    else:
        raise

When it happens

Trigger: Calling pw.io.sqlite.write(table, path, table_name, primary_key=[...]) without also passing output_table_type="snapshot". Any primary_key value (list of column references or a single reference) combined with output_table_type="stream_of_changes" (the default) triggers it.

Common situations: A developer copies the snapshot example from the docs but deletes or forgets the output_table_type="snapshot" line; or upgrades code that previously used a primary-key-like dedup pattern and assumes the connector infers snapshot mode from primary_key.

Related errors

• primary_key must be specified for the snapshot table type
• path {path_str!r} is an existing directory, not a SQLite dat
• Failed to install dependencies
• Column {api.TIME_PSEUDOCOLUMN} cannot contain negative times
• Column {api.DIFF_PSEUDOCOLUMN} can only have 1 and -1 values

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