ClickHouse Cloud's Join table engine enables fast, updatable in-memory lookups for dimensional modeling. Unlike the open-source implementation — which lacks distribution and background compaction — ClickHouse Cloud transparently backs Join tables with a SharedJoin structure using ReplacingMergeTree for ANY joins and MergeTree for ALL joins. This provides automatic upserts, deduplication, and data compaction. A practical example demonstrates how inserting a new row with the same key performs an upsert, with the in-memory hash table always reflecting the latest value via FINAL queries on the underlying ReplacingMergeTree.
Table of contents
Dictionaries in ClickHouse #The Join table engine #Querying a Join table #Drawbacks in the open source implementation #Implementation in ClickHouse Cloud #Example: Data Enrichment #Conclusion #Sort: