pathwaycom/pathway · error · ValueError

Column(s) {collisions} collide with the 'time' and 'diff' me

Error message

Column(s) {collisions} collide with the 'time' and 'diff' metadata columns appended in stream_of_changes mode. Rename these columns in the Pathway table, or use output_table_type="snapshot".

What it means

In stream_of_changes mode, pw.io.mssql.write appends 'time' and 'diff' metadata columns to the destination table. If the Pathway table already has a column whose name matches 'time' or 'diff' (case-insensitively, per SQL Server collation), the generated CREATE TABLE would declare it twice and SQL Server would reject it with an opaque duplicate-column error. Pathway raises this ValueError at write() time instead.

Source

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

            "every column name is unique case-insensitively."
        )

    if not is_snapshot_mode:
        # Stream-of-changes mode appends `[time]` / `[diff]` metadata columns
        # to the destination table.  If the user's own schema already has a
        # column with one of those names, the generated CREATE TABLE would
        # declare it twice and SQL Server would reject it with an opaque
        # "duplicate column name" error at startup.  Comparison is
        # case-insensitive — SQL Server's default collation treats `Time` and
        # `time` as the same identifier.
        reserved_metadata_columns = {"time", "diff"}
        collisions = sorted(
            field.name
            for field in value_fields
            if field.name.lower() in reserved_metadata_columns
        )
        if collisions:
            raise ValueError(
                f"Column(s) {collisions} collide with the 'time' and 'diff' "
                "metadata columns appended in stream_of_changes mode. Rename "
                "these columns in the Pathway table, or use "
                'output_table_type="snapshot".'
            )

    data_storage = api.DataStorage(
        storage_type="mssql",
        connection_string=connection_string,
        max_batch_size=max_batch_size,
        table_name=table_name,
        schema_name=schema_name,
        table_writer_init_mode=init_mode_from_str(init_mode),
        snapshot_maintenance_on_output=is_snapshot_mode,
    )

    key_field_names = None
    if primary_key is not None:

View on GitHub (pinned to fa2f74a464)

Solutions

Rename the colliding column(s) before the sink, e.g. table.select(**{k: v for ...}) mapping time -> event_time or diff -> change_diff.
Or switch the sink to output_table_type="snapshot", which does not append the metadata columns.

Example fix

# before
table = table.select(table.id, table.time, table.value)
pw.io.mssql.write(table, "events")

# after
table = table.select(table.id, event_time=table.time, table.value)
pw.io.mssql.write(table, "events")

Defensive patterns

Strategy: validation

Validate before calling

RESERVED = {"time", "diff"}

bad = [n for n in table.schema.column_names() if n.lower() in RESERVED]
if bad and output_table_type != "snapshot":
    table = table.rename(**{n: f"pw_{n}" for n in bad})

Type guard

def is_safe_for_stream_sink(column_names: list[str]) -> bool:
    return all(n.lower() not in ("time", "diff") for n in column_names)

Try / catch

try:
    pw.io.mssql.write(table, "events")
except ValueError as e:
    if "metadata columns" in str(e):
        pw.io.mssql.write(table, "events", output_table_type="snapshot")
    else:
        raise

Prevention

Avoid naming sink-bound columns bare 'time' or 'diff' (any casing).
Prefer descriptive names like event_time / change_diff from the start.
If the names come from upstream data, rename in the last select before the sink.

When it happens

Trigger: Calling pw.io.mssql.write(table, name) with default output_table_type and a schema containing a column named time, Time, diff, or DIFF.

Common situations: Event/log tables that naturally carry a timestamp column named 'time'; CDC-style pipelines where 'diff' is a natural column name; porting schemas from systems where these names are unrestricted.

Related errors

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