{"record":{"id":"84ba1c14f2a53962","repo":"pathwaycom/pathway","slug":"sort-by-cannot-be-used-with-the-snapshot-table-typ","errorCode":null,"errorMessage":"sort_by cannot be used with the snapshot table type: a snapshot reflects the current state of the table regardless of the order changes are applied within a minibatch, and reordering would corrupt the upsert/delete sequence. Remove sort_by, or use output_table_type=\"stream_of_changes\".","messagePattern":"sort_by cannot be used with the snapshot table type: a snapshot reflects the current state of the table regardless of the order changes are applied within a minibatch, and reordering would corrupt the upsert/delete sequence\\. Remove sort_by, or use output_table_type=\"stream_of_changes\"\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/duckdb/__init__.py","lineNumber":264,"sourceCode":"        LIMIT 5;\n    \"\"\"\n    _check_entitlements(\"duckdb\")\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    if is_snapshot_mode and sort_by is not None:\n        # In snapshot mode an update is a deletion (-1) followed by an insertion\n        # (+1) of the same key. sort_by reorders the changes within a minibatch\n        # and can place the insertion before the deletion, so the upsert is\n        # immediately wiped by the delete — silently losing rows. sort_by has no\n        # effect on a snapshot's final (unordered, keyed) state anyway, so reject\n        # the combination instead of corrupting the output.\n        raise ValueError(\n            \"sort_by cannot be used with the snapshot table type: a snapshot \"\n            \"reflects the current state of the table regardless of the order \"\n            \"changes are applied within a minibatch, and reordering would corrupt \"\n            \"the upsert/delete sequence. Remove sort_by, or use \"\n            'output_table_type=\"stream_of_changes\".'\n        )\n\n    database_str = fspath(database)\n    _reject_directory_path(database_str)\n\n    if detach_between_batches and database_str == IN_MEMORY_DATABASE:\n        # An in-memory database ceases to exist when its last connection closes,\n        # so detaching after every batch would silently drop all written data.\n        raise ValueError(\n            'detach_between_batches=True cannot be used with database=\":memory:\": '\n            \"an in-memory DuckDB database is dropped when its last connection \"\n            \"closes, so all data would be lost after every batch. Use an on-disk \"\n            \"database file instead.\"","sourceCodeStart":246,"sourceCodeEnd":282,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/duckdb/__init__.py#L246-L282","documentation":"Raised by pw.io.duckdb.write when sort_by is combined with output_table_type=\"snapshot\". In snapshot mode an update is a deletion (-1) followed by an insertion (+1) of the same key; reordering changes within a minibatch can place the insertion before the deletion, so the upsert is silently wiped by the delete and rows are lost. sort_by has no effect on a snapshot's final state anyway.","triggerScenarios":"pw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.id, sort_by=t.ts) — any non-None sort_by with snapshot mode.","commonSituations":"Copying a sort_by argument from an existing stream_of_changes write when converting to snapshot mode; adding sort_by 'for deterministic output' without realizing snapshots are keyed and unordered.","solutions":["Remove the sort_by argument when using output_table_type=\"snapshot\" — order does not affect the final snapshot.","If ordering matters to you, switch back to output_table_type=\"stream_of_changes\" and keep sort_by."],"exampleFix":"# before\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.id, sort_by=t.ts)\n\n# after\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.id)","handlingStrategy":"validation","validationCode":"if output_table_type == \"snapshot\" and sort_by is not None:\n    raise ValueError(\"sort_by is not applicable to snapshot output\")","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Remember snapshots are keyed and unordered; ordering arguments are meaningless there.","Keep connector kwargs in a dict and .pop('sort_by') when switching to snapshot mode."],"tags":["duckdb","connector","data-loss","ordering"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}