--- title: "Clickhouse® Integration Delta Lake: Real-Time Lakehouse Queries" excerpt: "Integrate Clickhouse® with Delta Lake for real-time lakehouse queries, improving latency and ensuring data consistency." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-02-08 00:00:00" publishedOn: "2026-02-08 00:00:00" updatedOn: "2026-02-08 00:00:00" status: "published" --- Clickhouse® integration with Delta Lake addresses distinct architectural choices: **(1) querying Delta tables directly** (zero-copy reads), **(2) caching for accelerated repeated queries**, **(3) CDC ingestion** via Change Data Feed, or **(4) materializing hot slices** in Clickhouse® while keeping cold data in Delta. The critical decision depends on **latency objectives**, **storage duplication costs**, and **how frequently Delta tables change**. Integration matters in 2025 because Delta Lake has become the default lakehouse format on Databricks and increasingly on open-source and [cloud computing](https://www.ibm.com/think/topics/cloud-computing) platforms. Teams need sub-100ms queries over lakehouse data without abandoning Delta's ACID guarantees, schema evolution, and time-travel capabilities. Delta provides transactional consistency and batch optimization, while ClickHouse® delivers interactive analytical performance impossible with Spark batch queries. Common patterns include: **zero-copy queries** (Delta as source, Clickhouse® as engine), **catalog-based integration** (Glue, Unity Catalog, HMS), **CDC streaming** (Change Data Feed to Clickhouse®), **hybrid architectures** (hot data in Clickhouse®, cold in Delta), and **cached reads** (filesystem cache over object storage). This article examines integration approaches, Delta Lake protocol details, and operational practices for production deployments. ## **Integration Patterns Overview** | Pattern | Use Case | Latency Profile | Best For | | ----- | ----- | ----- | ----- | | deltaLake() Function | Query Delta tables without copying | Variable (S3 \+ log resolution) | Exploration, ad-hoc analytics | | DeltaLake Table Engine | Persistent table pointing to Delta | Variable (depends on cache) | Regular queries with caching | | DataLakeCatalog (Glue/Unity) | Catalog-based Delta table discovery | Optimized (metadata via catalog) | Governed lakehouse architectures | | deltaLakeCluster() Parallel Reads | Distributed reads across cluster | Improved (parallel file processing) | Large Delta tables, multi-TB scans | | Change Data Feed (CDC) | Incremental Delta changes to Clickhouse® | Real-time (streaming ingestion) | Low-latency serving, event processing | | Materialized Hot Slices | Load recent partitions to MergeTree | Sub-100ms (local storage) | Production dashboards, high concurrency | ## **Delta Lake Architecture: Transaction Log Fundamentals** Delta Lake is **not just Parquet with extra folders**. The critical component is the **transaction log** (`_delta_log/`) storing the **source of truth** for which Parquet files compose each table version. This log enables **ACID transactions**, **time travel**, and **optimistic concurrency control** via commits (JSON files) and checkpoints (Parquet metadata snapshots). Understanding how a [database](https://www.oracle.com/database/what-is-database/) structures and manages such transactional consistency is essential to appreciate how Delta Lake ensures reliability and integrity across distributed storage systems. **Transaction Log Mechanics**: Each write creates a **commit file** (`00000000000000000000.json`) with operations (add file, remove file, metadata changes). Commits are **atomic**—either fully applied or not. **Checkpoints** (every 10 commits default) aggregate commit history into Parquet files, avoiding expensive replay of thousands of commits. **Protocol Versioning**: Delta tables declare **reader protocol** and **writer protocol** versions. Reader version determines which features external readers must support (column mapping, deletion vectors, etc.). Writer version determines allowed write operations. **Table features** (post-protocol 3\) enable fine-grained capabilities. **Why This Matters for Clickhouse®**: If Clickhouse® "understands" the log, it resolves consistent snapshots reading correct Parquet files. If it ignores the log reading Parquet directly, guarantees break—mixing old and new files, reading files removed by rewrites/compactions. ## **Direct Queries: deltaLake() Table Function** The `deltaLake()` table function enables **querying Delta tables** on **S3, Azure Blob, or local filesystem** without copying data. Clickhouse® resolves table state via transaction log, reads matching Parquet files. ### **Query Syntax** SELECT count() AS total\_events, toYYYYMMDD(event\_time) AS date, uniq(user\_id) AS unique\_users FROM deltaLake('https://my-bucket.s3.amazonaws.com/warehouse/events/') WHERE event\_time \>= '2026-01-01' GROUP BY date ORDER BY date; **S3 variant**: Default `deltaLake()` for S3 paths. Supports AWS credentials via parameters or environment. **Azure variant**: `deltaLakeAzure()` for Azure Blob Storage with connection string or SAS token. **Local variant**: `deltaLakeLocal()` for local filesystem paths (testing, development). ### **When Direct Queries Make Sense** **Exploratory analytics**: Ad-hoc queries over lakehouse data without loading to Clickhouse®. Schema validation, data quality checks, sampling before migration. **Zero duplication**: Avoid storage costs of maintaining same data in Delta and Clickhouse®. Single source of truth in lakehouse. **Cold data analysis**: Queries over historical data accessed infrequently. Accept higher latency for cost savings. **Lakehouse queries**: Teams standardizing on Delta as primary storage. Clickhouse® as query engine over shared lakehouse. ### **When Direct Queries Don't Make Sense** **Production dashboards**: Object storage latency variability (network, throttling, cold reads) breaks sub-100ms SLAs. User-facing analytics require local MergeTree tables. **High concurrency**: Multiple simultaneous queries create S3 request storms and unpredictable costs. Not designed for 100+ concurrent dashboard users. **Complex queries**: Heavy aggregations, multiple joins, window functions over remote Delta perform poorly. Load to MergeTree for production analytics. ## **DeltaLake Table Engine: Persistent Mapping** `DeltaLake` table engine creates **persistent Clickhouse® table** mapping to Delta table. Read-only—no writes to Delta from Clickhouse®. ### **Table Configuration** CREATE TABLE analytics.delta\_events ENGINE \= DeltaLake('https://my-bucket.s3.amazonaws.com/warehouse/events/'); **Read-only**: `SELECT` queries only. Cannot `INSERT`, `UPDATE`, or `DELETE` via Clickhouse®. **S3-focused**: Documentation specifies S3. Other object stores may require `deltaLake()` function instead of engine. **Automatic schema**: Clickhouse® infers schema from Delta metadata. No manual column definitions required (though can specify for type control). ### **Filesystem Cache Critical for Performance** Enable **filesystem cache** for object storage reads. Repeated queries hit local cache instead of S3. \ \ \ \ \cache\ \s3\ \/var/lib/Clickhouse®/cache/s3/\ \100Gi\ \ \ \ \ **Cache mechanics**: Path \+ ETag validation. Changed Delta files (new ETag) invalidate cache automatically. LRU eviction when cache full. **Performance impact**: Documentation reports **\~2x improvement** with cache enabled. Critical for dashboard queries hitting same Delta tables repeatedly. ## **DataLakeCatalog: Catalog-Based Integration** **DataLakeCatalog** database engine enables querying Delta (and Iceberg) tables via **external catalogs**—AWS Glue, Databricks Unity Catalog, Hive Metastore, REST catalogs. ### **Catalog Configuration** SET allow\_experimental\_database\_hms\_catalog \= 1; CREATE DATABASE lakehouse ENGINE \= DataLakeCatalog('thrift://hive-metastore:9083', '', '') SETTINGS catalog\_type \= 'hive', warehouse \= 'delta\_warehouse', storage\_endpoint \= 's3://data-lake/'; **Catalog types**: `hive` (HMS), `glue` (AWS Glue), `unity` (Databricks Unity Catalog), `rest` (REST catalog). **Table discovery**: Query tables by name without hardcoded paths: SELECT \* FROM lakehouse.events WHERE dt \= '2026-01-31'; **Unity Catalog limitations**: Marked **beta** in documentation with conditional AWS support. Validate compatibility for production use. ### **Why Catalog Integration** **Governance**: Centralized metadata, access control, lineage tracking. Tables discovered via catalog, not filesystem paths. **Credential management**: Catalog handles authentication to storage. Clickhouse® receives pre-authenticated paths. **Multi-format**: Same catalog may contain Delta, Iceberg, Hive tables. Unified query interface across formats. **Schema evolution**: Catalog tracks schema changes. Clickhouse® queries always see current schema without manual updates. ## **Parallel Reads: deltaLakeCluster() Function** For **large Delta tables**, `deltaLakeCluster()` distributes file processing across Clickhouse® cluster nodes. Initiator resolves transaction log, assigns Parquet files to workers dynamically. ### **Cluster Query Example** SELECT toYYYYMMDD(event\_time) AS date, count() AS events, uniq(user\_id) AS users FROM deltaLakeCluster( 'analytics\_cluster', 'https://my-bucket.s3.amazonaws.com/warehouse/events/' ) WHERE event\_time \>= '2026-01-01' GROUP BY date; **Work distribution**: Workers read assigned Parquet files in parallel. Critical for **multi-TB Delta tables** where single-node I/O insufficient. **File granularity**: Works best with **many Parquet files** per table (100s-1000s). Few large files limit parallelism—single file read by single node. **Row group parallelism**: For few large files, configure settings enabling **row group-level parallelism** within Parquet files. Documentation mentions "bucket granularity" for processing row groups independently. ## **Delta Lake Protocol Features: Compatibility Challenges** Delta tables declare **read/write protocol versions** and **table features** determining compatibility. External readers like Clickhouse® must support features table uses—otherwise reads fail or return incorrect results. ### **Column Mapping** **Column mapping** decouples logical column names from physical Parquet column names/IDs. Enables **RENAME COLUMN** and **DROP COLUMN** without rewriting files. **Compatibility challenge**: Readers expecting logical schema to match physical schema break. Clickhouse® must interpret mapping from transaction log. **Operational impact**: Frequent schema changes (renames, reorders, drops) create compatibility surface area. Limit to **additive changes** (append columns) for maximum external reader compatibility. ### **Deletion Vectors** **Deletion vectors** mark rows as deleted without rewriting Parquet files. Accelerates `DELETE`, `UPDATE`, `MERGE` by avoiding expensive file rewrites. **Compatibility challenge**: Readers must apply deletion mask when reading. Without support, returns "deleted" rows. **Clickhouse® support**: Recent changelog shows **deletion vectors support** added to `DeltaLake` engine and `deltaLakeCluster`. Indicates this was compatibility gap now addressed. **Performance impact**: Applying deletion vectors adds overhead versus reading plain Parquet. Cost depends on deletion vector storage (inline vs separate file). ### **Protocol Validation** **Before production**: Check Delta table features active on tables Clickhouse® will query: \-- In Databricks/Spark DESCRIBE DETAIL delta\_table; \-- Check readerFeatures and writerFeatures **Common breaking features**: Column mapping (name mode, id mode), deletion vectors, generated columns (depends on implementation). **Mitigation**: Create **read-compatible views** from Delta tables with complex features. Or migrate to Clickhouse®-friendly Delta table settings. ## **Change Data Feed: CDC to Clickhouse®** Delta Lake **Change Data Feed (CDF)** tracks **row-level changes** between versions—inserts, updates, deletes with before/after values. Enables **incremental CDC** from Delta to Clickhouse®. ### **Enabling CDF** Enable on Delta table: \-- Databricks/Spark ALTER TABLE events SET TBLPROPERTIES (delta.enableChangeDataFeed \= true); **Historical changes**: CDF only captures changes after enabling. Historical data requires full snapshot load. ### **Reading Changes** Query changes between versions: \# Spark changes \= spark.read.format("delta") \\ .option("readChangeData", "true") \\ .option("startingVersion", 10\) \\ .option("endingVersion", 20\) \\ .table("events") **Change types**: `_change_type` column indicates `insert`, `update_preimage`, `update_postimage`, `delete`. ### **CDC Pipeline to Clickhouse®** **Batch CDC**: Periodically read changes, transform, apply to Clickhouse®: \-- Clickhouse® \- apply inserts INSERT INTO analytics.events SELECT \* FROM changes WHERE \_change\_type \= 'insert'; \-- Handle updates (ReplacingMergeTree) INSERT INTO analytics.events SELECT \*, \_commit\_version AS version FROM changes WHERE \_change\_type IN ('update\_postimage', 'insert'); **Streaming CDC**: Spark Structured Streaming or Kafka integration consuming CDF changes, publishing to Clickhouse® via Kafka connector. **Deduplication**: Use **ReplacingMergeTree** with version column (`_commit_version`) for upsert semantics. Or application-level deduplication tracking processed commit versions. ### **When CDC Makes Sense** **Low-latency serving**: Delta as batch lake (hourly/daily writes), Clickhouse® as real-time serving layer (sub-100ms queries). CDC keeps Clickhouse® current. This setup also enables use cases such as [real-time personalization](https://www.tinybird.co/blog/real-time-personalization), where analytics-driven responses adapt instantly to user interactions. **Large tables with small changes**: Full snapshot loads expensive. Incremental CDC transfers only changed rows. **Event-driven architectures**: CDF changes trigger downstream processing, analytics updates, notifications. This design ensures that updates flow seamlessly into every [downstream system](https://medium.com/@ogunodabas/downstream-upstream-system-c1dc6cf4b59e), maintaining consistency across analytical and operational layers. ## **Small Files and Compaction: Performance Killer** Delta tables with **small files** (thousands of files \<10 MB) create performance disaster for Clickhouse®: * **Metadata overhead**: S3 LIST operations expensive with many objects * **Request overhead**: HTTP request per file for headers/footers * **Planning overhead**: Resolving snapshot with thousands of files slow * **Cache inefficiency**: LRU cache thrashes with many small objects ### **Delta Lake OPTIMIZE** Compact small files into larger files: \-- Databricks OPTIMIZE events; \-- With Z-Order clustering OPTIMIZE events ZORDER BY (tenant\_id, event\_time); **Target file size**: Default \~1 GB configurable. Balance metadata overhead (prefer larger) versus parallelism (prefer smaller). **Z-Order clustering**: Co-locates rows with similar values in multiple columns. Maximizes **data skipping** effectiveness for multi-dimensional filters. ### **Compaction Best Practices** **Daily compaction**: Run OPTIMIZE during low-traffic windows. Streaming tables accumulate small files—daily compaction maintains query performance. **Partition-aware**: OPTIMIZE operates per partition. Large partitioned tables compact incrementally (recent partitions only). **Liquid clustering**: Modern alternative to Z-Order. Automatically maintains optimal clustering without manual OPTIMIZE commands (Databricks runtime ≥13.3). ## **Data Skipping and Statistics** Delta Lake collects **file-level statistics** during writes—min, max, null count, total records per column. Used for **data skipping**—pruning files before read based on query predicates. ### **Statistics Configuration** **Unity Catalog tables**: Statistics collected on **first 32 columns** by default. Configurable via table properties. **External tables**: May require explicit statistics collection depending on write source. **Critical columns**: Ensure statistics collected on **filter columns** (timestamps, IDs, dimensions). Queries filtering unstated columns scan all files. ### **Z-Order and Liquid Clustering** **Z-Order**: Multi-dimensional clustering co-locating rows with similar values. Example: `ZORDER BY (tenant_id, event_time)` clusters events from same tenant in same time range together. **Effectiveness**: Maximizes skipping when queries filter on **multiple dimensions**. Example: `WHERE tenant_id = 'acme' AND event_time > '2026-01-01'` reads only files containing matching tenant/time combinations. **Liquid clustering**: Automatic incremental clustering without manual OPTIMIZE. Maintains optimal layout as data arrives. ### **Parquet Statistics** Delta statistics at file level complement **Parquet statistics** at row group level. Clickhouse® uses both: * **Delta file stats**: Skip entire files outside query range * **Parquet row group stats**: Skip row groups within files outside range **Sorting matters**: Write Parquet sorted by filter columns. Maximizes min/max statistics effectiveness enabling row group pruning. ## **Retention and VACUUM: Consistency Guarantees** Delta Lake **retention policies** determine how long old files remain after logical deletion. **VACUUM** physically removes files beyond retention. ### **Retention Configuration** **Deleted file retention**: Default **7 days** in Databricks. Configurable via `delta.deletedFileRetentionDuration`. **Log retention**: Default **30 days**. Configurable via `delta.logRetentionDuration`. Determines time-travel window. **Why it matters**: VACUUM removing files before external readers finish queries causes **file not found errors**. Or removes files preventing time-travel queries. ### **Safe VACUUM Strategy** **Increase retention for external readers**: 14+ days retention provides buffer for Clickhouse® queries potentially spanning hours/days via caching. **Coordinate with downstream**: Clickhouse® cached queries may reference old Parquet files. Cache TTL \+ query duration must fit within retention window. **Monitor reader lag**: Track Clickhouse® query patterns. If queries reference snapshots \>7 days old (via cache or explicit version), increase retention. ## **Observability: Diagnosing Integration Issues** Clickhouse® provides **system tables** for Delta integration debugging. ### **Delta Metadata Log** `system.delta_lake_metadata_log` records Delta transaction log files Clickhouse® reads: SELECT path, size, timestamp, last\_modified FROM system.delta\_lake\_metadata\_log WHERE database \= 'analytics' AND table \= 'events' ORDER BY timestamp DESC LIMIT 100; **Diagnostic value**: See which commits and checkpoints read. Identify if snapshot resolution slow due to missing checkpoints or excessive commits. ### **Filesystem Cache Metrics** Monitor cache effectiveness: SELECT metric, value FROM system.metrics WHERE metric LIKE '%FilesystemCache%'; **Key metrics**: `FilesystemCacheSize` (bytes cached), `FilesystemCacheElements` (objects cached), hit/miss ratios. **Cache log**: Enable `system.filesystem_cache_log` for per-query cache behavior. Shows which files cached, which hit S3. ### **Performance Validation** **Test cold vs warm**: Clear cache (`SYSTEM DROP FILESYSTEM CACHE`) and compare query times. Large delta indicates caching critical for workload. **Validate skipping**: Check `EXPLAIN` output and S3 metrics. Many files read despite selective filters indicates poor data layout or missing statistics. Efficient data [projections](https://www.tinybird.co/blog/projections) can further optimize analytical queries by minimizing unnecessary reads and precomputing metrics relevant to frequent access patterns. ## **Decision Framework** ### **Choose deltaLake() Function when:** * **Exploratory analytics** without permanent Clickhouse® tables * **Zero duplication** critical—storage cost sensitive * **Infrequent queries** over lakehouse data acceptable latency * **Cold data analysis** accessed weekly/monthly * **Not recommended**: Production dashboards (latency variability), high concurrency (S3 costs) ### **Choose DeltaLake Table Engine when:** * **Regular queries** over same Delta tables * **Filesystem cache** deployed for performance * **Persistent table** mapping preferred over function calls * **Schema stability** expected—infrequent breaking changes * **Not recommended**: Clickhouse® Cloud (consider function instead), frequently changing schemas ### **Choose DataLakeCatalog when:** * **Governed lakehouse** with Glue/Unity/HMS catalog * **Table discovery** by name required—not hardcoded paths * **Multi-format lakehouse** (Delta \+ Iceberg \+ Hive) * **Centralized metadata** and access control critical * **Not recommended**: Simple use cases without catalog, unstable catalog metadata ### **Choose deltaLakeCluster() when:** * **Large Delta tables** (multi-TB) requiring distributed reads * **Clickhouse® cluster** available for parallel processing * **Many Parquet files** per table (100s-1000s enabling parallelism) * **Batch analytics** where latency acceptable * **Not recommended**: Single-node Clickhouse®, few large files (limited parallelism benefit) ### **Choose Change Data Feed when:** * **Low-latency serving** required from batch lakehouse * **Large tables** with **small change rate** (incremental cheaper than full snapshot) * **Event-driven** downstream processing from Delta changes * **Separation of concerns**: Delta for batch, Clickhouse® for serving * **Not recommended**: Full snapshot cheaper (small tables), real-time writes directly to Clickhouse® faster ### **Choose Materialized Hot Slices when:** * **Production dashboards** requiring sub-100ms P95 latency * **High query concurrency** (100+ concurrent users) * **Recent data** (last 30/90 days) accounts for 90%+ queries * **Predictable costs** prioritized over zero duplication * **Not recommended**: Storage costs prohibitive, all historical data queried equally ## **Conclusion** Clickhouse® integration with Delta Lake enables **real-time queries** over **transactional lakehouse data** while maintaining Delta's ACID guarantees, schema evolution, and time-travel capabilities. The key patterns—**zero-copy queries**, **catalog integration**, **CDC ingestion**, **hybrid architectures**—each solve specific challenges while respecting Delta Lake's transaction log model and protocol features. However, a critical principle remains: **Delta Lake optimizes for batch consistency and cost-effective storage, not interactive query latency**. For teams that depend on [real-time data platforms](https://www.tinybird.co/blog/real-time-data-platforms), integrating ClickHouse allows bridging this latency gap while maintaining the integrity and structure of Delta-based storage. Teams should architect recognizing this trade-off: **Delta for lakehouse foundation** (ACID transactions, schema evolution, cost-effective storage), **Clickhouse® for query serving** (sub-100ms latency, high concurrency), and **integration patterns minimizing object storage overhead** (filesystem cache, compaction, statistics-based skipping). By choosing the right pattern for each use case—zero-copy for exploration, CDC for incremental serving, materialization for production—teams deliver both the **transactional consistency of Delta** and the **sub-100ms query performance** of Clickhouse®. The integration succeeds when teams understand they're **composing complementary systems**: Delta for **ACID lakehouse storage**, Clickhouse® for **real-time analytical queries**, and patterns that **respect protocol features** (column mapping, deletion vectors) while **maximizing cache efficiency**. This separation delivers both lakehouse governance and interactive performance without compromising either.