Skip to main content

Troubleshooting ClickHouse Writer

This topic describes known issues with ClickHouse Writer, their symptoms, and how to resolve them.

CollapsingMergeTree shows incorrect results after an application restart

Symptom

After a Striim application restart or recovery, a table using CollapsingMergeTree shows rows that should have been deleted or updated still present, duplicate rows for what should be a unique key, or aggregate values (such as count() or sum()) that are too high or negative. The ClickHouse server log may show a logical error referencing an unexpected count of +1 or -1 rows for a sorting key.

Cause

ClickHouse Writer provides at-least-once processing. When an application recovers, it resumes from its last checkpoint and replays every event since that checkpoint, which can cause already-written events to be written again. CollapsingMergeTree tracks row state using only the Sign column and has no version or deduplication mechanism, so a replayed event re-emits an identical Sign row and can unbalance the +1/-1 count for a sorting key: an extra +1 leaves a phantom row visible and inflates counts, while an extra or orphaned -1 leaves a negative sum that does not self-correct.

Important: Running OPTIMIZE TABLE ... FINAL or querying with SELECT ... FINAL only collapses the Sign rows that are physically present; neither operation can invent a missing partner row or discard a genuine duplicate, so this kind of imbalance is not corrected automatically.

Detect the issue

Run the following query, replacing <sorting_key_cols> and <table> with your table's sorting key columns and table name. A sign_sum of 0 or 1 is healthy; a value greater than 1 indicates a phantom row; a value less than 0 indicates an orphaned row.

SELECT
    <sorting_key_cols>,
    sum(Sign) AS sign_sum,
    count()   AS rows
FROM <table>
GROUP BY <sorting_key_cols>
HAVING sign_sum < 0 OR sign_sum > 1
ORDER BY sign_sum;

Resolution

  • If your pipeline uses application recovery, prefer ReplacingMergeTree (which is idempotent under replay because it uses a monotonic version and a soft-delete flag) or MergeTree with Mode=MERGE (which reconciles to the same final state on replay) instead of CollapsingMergeTree.

  • If you must use CollapsingMergeTree, treat it as not safe to replay: after a recovery event, use the detection query above to find affected keys, and reload or re-snapshot the corresponding rows from the source. There is no automatic repair for this condition.

Target table or column not found because of identifier case mismatch

Symptom

A pipeline runs successfully against one ClickHouse server but fails against another with what appears to be the same schema, typically with a table-not-found error, or with a column silently left unmapped. This commonly appears when moving a pipeline built and tested on a developer workstation to a production Linux server.

Cause

Identifier case sensitivity is determined by ClickHouse and, for databases and tables, by the underlying server's file system, not by ClickHouse Writer; ClickHouse Writer matches and issues identifiers exactly as configured and never changes their case. Column names are always case-sensitive, regardless of operating system. Database and table names follow the case sensitivity of the ClickHouse server's file system: case-sensitive file systems (typical on Linux) treat differently cased names as different tables, while case-insensitive file systems (typical on macOS and Windows) resolve them to the same table.

Resolution

Configure database, table, and column names in ClickHouse Writer using the exact case defined in ClickHouse. Do not rely on case-insensitive matching for database or table names, since it holds only on case-insensitive file systems and fails against a case-sensitive production Linux server.