Clickhouse^® Integration Delta Lake: Real-Time Lakehouse Queries

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 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

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 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.

<Clickhouse®>
<storage_configuration>
<disks>
<s3_cache>
<type>cache</type>
<disk>s3</disk>
<path>/var/lib/Clickhouse®/cache/s3/</path>
<max_size>100Gi</max_size>
</s3_cache>
</disks>
</storage_configuration>
</Clickhouse®>

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, 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, 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 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, 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.