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 the default stream_of_changes mode the SQLite connector appends two INTEGER metadata columns, `time` and `diff`, to the destination table so the output can be replayed as a change log. If the Pathway table's own schema already contains a column named time or diff (matching is case-insensitive, consistent with SQLite identifier rules), the generated CREATE TABLE would declare that column twice and SQLite would reject it. write() detects the collision and raises this ValueError at call time instead of an opaque duplicate-column failure at pipeline start.

Source

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

    if not is_snapshot_mode:
        # Stream-of-changes mode appends `time` / `diff` metadata columns
        # to the destination table so the output can be replayed as a
        # change log. If the user's own schema already has a column with
        # one of those names, the generated CREATE TABLE would declare
        # that column twice and SQLite would reject it. Catch this at
        # write() time with a clear message instead of letting the user
        # hit an opaque "duplicate column name" error at start-up.
        # Matching is case-insensitive, consistent with SQLite's identifier
        # comparison rules.
        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".'
            )

    key_field_names: list[str] | None = None
    if primary_key is not None:
        # Duplicate entries in `primary_key` produce a nonsensical SQL
        # template — e.g. `PRIMARY KEY ("k", "k")` — which SQLite tolerates,
        # but the shared `SqlQueryTemplate` then reorders DELETE bindings
        # using the duplicated index and retractions silently match no
        # rows. Reject here with a clear message.
        names_seen: set[str] = set()
        duplicates: list[str] = []
        for pkey in primary_key:
            if pkey.name in names_seen and pkey.name not in duplicates:
                duplicates.append(pkey.name)

View on GitHub (pinned to fa2f74a464)

Solutions

Rename the offending column in the Pathway table before writing, e.g. t.select(**{**{c: c for c in t.schema.columns()}, 'time': 'event_time'}) or table.rename_columns().
Switch to output_table_type="snapshot" (with a required primary_key), which does not append time/diff columns.

Example fix

# before
pw.io.sqlite.write(t_with_time_col, "db.sqlite", "events")

# after
t = t_with_time_col.rename_columns(event_time=pw.this.time)
pw.io.sqlite.write(t, "db.sqlite", "events")

Defensive patterns

Strategy: validation

Validate before calling

RESERVED = {'time', 'diff'}
def check_reserved_columns(table, output_table_type="stream_of_changes"):
    if output_table_type != "stream_of_changes":
        return True
    hits = [c for c in table.schema.column_names() if c.lower() in RESERVED]
    if hits:
        raise ValueError(f"columns {hits} collide with time/diff metadata; rename them")
    return True

Prevention

Avoid naming data columns `time` or `diff` in schemas destined for the SQLite change-log sink; prefer event_time/change.
Add a schema lint step in CI that flags reserved sink-specific column names per connector.

When it happens

Trigger: Calling pw.io.sqlite.write(table, ...) with output_table_type left at the default "stream_of_changes" (or passed explicitly) on a table that has a column named time, diff, TIME, Diff, etc.

Common situations: Event/log tables that naturally carry a `time` or `diff` column; porting a pipeline from another sink (e.g. Postgres or CSV output) that allowed those names; snapshot-mode examples that were switched back to change-log mode without renaming.

Related errors

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