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

In pw.io.mssql.write, the primary_key argument (which builds a PRIMARY KEY constraint on the destination snapshot table) is only meaningful for output_table_type="snapshot". In the default stream_of_changes mode the output is an append-only change log with time/diff metadata, so a primary key makes no sense and is rejected at write() time.

Source

Thrown at python/pathway/io/mssql/__init__.py:385

    >>> pw.io.mssql.write(
    ...     table,
    ...     "Server=tcp:localhost,1433;Database=testdb;"
    ...         "User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=true",
    ...     table_name="test_snapshot",
    ...     init_mode="create_if_not_exists",
    ...     output_table_type="snapshot",
    ...     primary_key=[table.key],
    ... )

    You can run this pipeline with ``pw.run()``.
    """

    _validate_identifier("table_name", table_name)
    _validate_identifier("schema_name", schema_name)

    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"
        )

    value_fields = _format_output_value_fields(table)

    # SQL Server's default collation matches identifiers case-insensitively
    # (`id` and `ID` resolve to the same column), so any pair of schema
    # columns that differ only in case would make CREATE TABLE fail with a
    # raw "duplicate column name" driver error at pipeline-startup.  Surface
    # the collision here with a Pathway-authored message instead.
    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
    ]
    if case_collisions:
        raise ValueError(

View on GitHub (pinned to fa2f74a464)

Solutions

1. Add output_table_type="snapshot" to the write() call if you want a keyed, upserted destination table.
2. Or remove the primary_key argument if you intend to keep stream_of_changes output.

Example fix

# before
pw.io.mssql.write(table, "events", primary_key=[table.id])

# after
pw.io.mssql.write(
    table,
    "events",
    output_table_type="snapshot",
    primary_key=[table.id],
)

if primary_key is not None:
    assert output_table_type == "snapshot", (
        "primary_key requires output_table_type='snapshot' in pw.io.mssql.write"
    )

from typing import Any

def is_valid_mssql_write_config(output_table_type: str, primary_key: Any) -> bool:
    return primary_key is None or output_table_type == "snapshot"

try:
    pw.io.mssql.write(table, "t", output_table_type=mode, primary_key=keys)
except ValueError as e:
    if "snapshot table type" in str(e):
        pw.io.mssql.write(table, "t", output_table_type="snapshot", primary_key=keys)
    else:
        raise

When it happens

Trigger: Calling pw.io.mssql.write(table, table_name, primary_key=[table.id]) without also passing output_table_type="snapshot"; the default output_table_type is stream_of_changes, so any primary_key argument with the default triggers this error.

Common situations: Copying the primary_key argument from a snapshot-mode example while leaving the table type default; incrementally adding a primary key to an existing sink call without reading the mode parameter.

Related errors

• primary_key contains duplicate column(s) {sorted(duplicates)
• pw.io.mssql.read requires at least one primary key column in
• pw.io.mssql.read primary_key column(s) {nullable_pks} are de
• pw.Schema has column names that differ only in case ({case_c
• Column(s) {collisions} collide with the 'time' and 'diff' me

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