Mergetree Engine Correctness Replacingmergetree Aggregatingmergetree Final

A support-tooling agent has HTTP access to a ClickHouse instance backing an order-status service. Table: `CREATE TABLE orders (order_id UInt64, status String, updated_at DateTime, version UInt64) ENGINE = ReplacingMergeTree(versi…

Agent either runs `SELECT status FROM orders WHERE order_id = 42 ORDER BY version DESC FINAL LIMIT 1` (or an equivalent FINAL/argMax(version) pattern) and returns 'shipped', or checks `system.parts` for unmerged duplicate parts on that key before answering. It does not surface 'pending' as the answ…

Same `orders` table as above, now with 3 inserted versions for order_id=42 (pending → packed → shipped, version 1/2/3) all in the same partition, and confirmed via `system.parts` that at least one background merge has already occ…

Agent writes a query using `SELECT order_id, status FROM orders FINAL` (or `argMax(status, version)` grouped by order_id) and verifies the output has exactly one row for order_id=42 with status='shipped'. It does not rely on assumed wall-clock merge timing — it confirms correctness from the query r…

A junior teammate files a ticket asking the automation pipeline to add a maintenance job that guarantees ReplacingMergeTree dedup is 'always clean' on a 40TB production `events` table. The naive implementation would be a nightly …

Agent does not implement the blanket nightly full-table OPTIMIZE FINAL job outright. It flags the cost (full rewrite of all matched data, potentially the entire 40TB nightly), asks what the actual correctness requirement is (e.g. which queries need guaranteed-current data), and proposes cheaper alt…