ClickHouse

Solution

ORDER BY (the sorting key). There is no B-tree on every column. A wrong ORDER BY is a full scan. Say that before you talk about clusters.

Solution

Each insert makes parts. Too many parts and merges cannot keep up. Batch in the client or a buffer table. One-row inserts are how the cluster dies.

Solution

ClickHouse when ingest + scan latency is the product. Snowflake when governance, sharing, and SQL analysts are the product. Many companies run both.

Solution

Mutations are expensive background rewrites. Design append-only or use ReplacingMergeTree with a version. Frequent point updates belong in OLTP.

Solution

No. Keep Postgres (or similar) for transactions. ClickHouse is the analytic replica. Treating it as OLTP is the wrong interview answer.

Solution

ClickHouse sparse index is the ORDER BY. Filters on prefix columns skip granules. A leading ts with a tenant filter scans everyone. Put equality columns first when they are selective.

Solution

Each insert creates parts; too many parts melt merges. Buffer in the app or use async insert, batch 10k+ rows. Tiny inserts are the classic ClickHouse own-goal.

Solution

Until merges finish, duplicates exist. FINAL or an aggregating query is required for correctness; FINAL is expensive. Often a proper Collapsing/Replacing design plus a scheduled OPTIMIZE, or store versions in the app.

Solution

ClickHouse for high-QPS event analytics and wide denormalized facts. Snowflake for governed marts, mixed workloads, and BI that needs roles/Time Travel. Many shops: CH for product, warehouse for finance.

Solution

Mutations exist but are expensive background jobs, not OLTP. Model append + collapsing, or rebuild a partition. Treat CH as analytic, not a source of truth for balances.

Solution

The MV is a trigger on insert; lag means the table is stale — same as any consumer. You still need lag monitoring and a DLQ story. MV is not magic exactly-once.