pathwaycom/pathway · error · ValueError

primary_key must be specified for the snapshot table type

Error message

primary_key must be specified for the snapshot table type

What it means

In snapshot mode the SQLite connector maintains the current table state via UPSERT on the primary key and DELETE by primary key, so a primary key is mandatory. If output_table_type="snapshot" is set but primary_key is missing, None, or an empty list, write() raises this ValueError immediately, since there is no way to correlate retractions with prior inserts.

Source

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

    ...     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]] = {}
    for field in value_fields:
        case_groups.setdefault(field.name.lower(), []).append(field.name)
    case_collisions = [
        sorted(names) for names in case_groups.values() if len(names) > 1

View on GitHub (pinned to fa2f74a464)

Solutions

Pass primary_key as one column reference (e.g. primary_key=t.id) or a list of references (e.g. primary_key=[t.owner, t.pet]) whose columns uniquely identify rows.
If no natural key exists, stay in the default stream_of_changes mode (drop output_table_type="snapshot") instead of inventing a key.

Example fix

// before
pw.io.sqlite.write(t, "pets.db", "pets", output_table_type="snapshot")

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

Defensive patterns

Strategy: validation

Validate before calling

def validate_sqlite_write_args(output_table_type, primary_key):
    if output_table_type == "snapshot" and not primary_key:
        raise ValueError("snapshot mode requires a non-empty primary_key")
    return True

Try / catch

try:
    pw.io.sqlite.write(t, path, name, output_table_type="snapshot")
except ValueError as e:
    if "must be specified for the snapshot" in str(e):
        raise ValueError(f"Missing primary key for snapshot of {name}") from e
    raise

Prevention

Before enabling snapshot mode, confirm the table has a column (or set of columns) that uniquely identifies rows.
Encode the pairing in one config object (output_table_type + primary_key validated together) instead of loose kwargs.

When it happens

Trigger: Calling pw.io.sqlite.write(table, path, table_name, output_table_type="snapshot") with primary_key omitted, set to None, or passed as an empty list.

Common situations: A developer switches the connector to snapshot mode to avoid the time/diff columns but does not add a primary_key; or copies a snapshot example and deletes the primary_key line while refactoring column names.

Related errors

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