{"record":{"id":"cf6fe9497e7edc59","repo":"pathwaycom/pathway","slug":"primary-key-must-be-specified-for-the-snapshot-tab-cf6fe9","errorCode":null,"errorMessage":"primary_key must be specified for the snapshot table type","messagePattern":"primary_key must be specified for the snapshot table type","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/sqlite/__init__.py","lineNumber":303,"sourceCode":"    ...     t,\n    ...     \"pets.db\",\n    ...     \"pets_snapshot\",\n    ...     output_table_type=\"snapshot\",\n    ...     primary_key=[t.owner, t.pet],\n    ...     init_mode=\"replace\",\n    ... )\n\n    Here ``(owner, pet)`` is the primary key, so at any point in time the\n    ``pets_snapshot`` table contains one row per live ``(owner, pet)``\n    pair — no history, no ``time`` / ``diff`` columns.\n    \"\"\"\n    is_snapshot_mode = output_table_type == SNAPSHOT_OUTPUT_TABLE_TYPE\n    if not is_snapshot_mode and primary_key is not None:\n        raise ValueError(\n            \"primary_key can only be specified for the snapshot table type\"\n        )\n    if is_snapshot_mode and not primary_key:\n        raise ValueError(\"primary_key must be specified for the snapshot table type\")\n\n    path_str = fspath(path)\n    _reject_directory_path(path_str)\n\n    value_fields = _format_output_value_fields(table)\n\n    # SQLite identifier matching is case-insensitive (`ID` and `id` are\n    # the same column), so any pair of schema columns whose names\n    # differ only in case would make ``CREATE TABLE`` fail with a raw\n    # ``duplicate column name`` driver error at pipeline-start. Surface\n    # the collision here with a clear, Pathway-authored message\n    # instead, matching how the ``time`` / ``diff`` reserved-name check\n    # below works.\n    case_groups: dict[str, list[str]] = {}\n    for field in value_fields:\n        case_groups.setdefault(field.name.lower(), []).append(field.name)\n    case_collisions = [\n        sorted(names) for names in case_groups.values() if len(names) > 1","sourceCodeStart":285,"sourceCodeEnd":321,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/sqlite/__init__.py#L285-L321","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\npw.io.sqlite.write(t, \"pets.db\", \"pets\", output_table_type=\"snapshot\")\n\n// after\npw.io.sqlite.write(\n    t, \"pets.db\", \"pets\",\n    output_table_type=\"snapshot\",\n    primary_key=[t.owner, t.pet],\n)","handlingStrategy":"validation","validationCode":"def validate_sqlite_write_args(output_table_type, primary_key):\n    if output_table_type == \"snapshot\" and not primary_key:\n        raise ValueError(\"snapshot mode requires a non-empty primary_key\")\n    return True","typeGuard":null,"tryCatchPattern":"try:\n    pw.io.sqlite.write(t, path, name, output_table_type=\"snapshot\")\nexcept ValueError as e:\n    if \"must be specified for the snapshot\" in str(e):\n        raise ValueError(f\"Missing primary key for snapshot of {name}\") from e\n    raise","preventionTips":["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."],"tags":["pathway","sqlite","output-connector","configuration"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}