Data lake best practices

The getting started guide walks you through querying Apache Iceberg, Delta Lake, Apache Hudi, and Apache Paimon for the first time. Once you're past setup, use this page to choose the right access pattern, tune query performance, and debug lake queries in production.

Choose an access method

Access method	When to use it	Examples
Table function	Ad hoc queries on a known path	icebergS3(), deltaLake(), hudi(), paimon()
Table engine	Repeated queries on the same path without a catalog	IcebergS3, DeltaLake, Hudi
DataLakeCatalog database engine	Production workloads with a catalog; federated queries across many tables	AWS Glue, Unity Catalog, REST catalog

Table functions

Pass the storage path and credentials inline when you know the location and don't need a persistent table definition.

SELECT count()
FROM icebergS3('https://my-bucket.s3.amazonaws.com/warehouse/my_table/')
WHERE event_date >= today() - 7

Use the S3 variant for AWS S3 and GCS. Azure and local filesystem have dedicated variants (icebergAzure, icebergLocal, and equivalents for other formats). See Querying directly for the full list.

Paimon has table functions only.

Table engines

Create a table with the table engine when you'll query the same path repeatedly. ClickHouse stores the path and credentials in table metadata, so you query a normal table name instead of reconstructing the function call each time.

CREATE TABLE events
    ENGINE = IcebergS3('https://my-bucket.s3.amazonaws.com/warehouse/events/')

SELECT count() FROM events WHERE event_date = today()

Table engines support the same read features as table functions, including data caching and metadata caching. Data is never duplicated in ClickHouse. A table engine is useful when you share access with a team or run scheduled jobs against the same table.

DataLakeCatalog database engine

Connect ClickHouse once when tables are registered in an external data catalog. Every catalog table appears as a ClickHouse table automatically, including tables added upstream after you create the connection.

CREATE DATABASE my_lake
ENGINE = DataLakeCatalog
SETTINGS
    catalog_type = 'glue',
    region = 'us-east-1',
    aws_access_key_id = '<key>',
    aws_secret_access_key = '<secret>'

SELECT count() FROM my_lake.`analytics.events`

This scales better than creating individual table definitions when you manage many tables or multiple catalogs. See Connecting to catalogs and the catalog guides.

Backticks for multi-part table names

Catalogs often use database.table naming. Surround the database-qualified name with backticks, as in the example above.

Required settings

Many integrations require a feature flag before first use. Check your service version if CREATE DATABASE fails with a permissions error.

For catalog connections, each catalog type has its own flag. See Connecting to catalogs for an overview and the DataLakeCatalog reference for setting details. Per-catalog setup lives in the catalog guides.

For writes, Iceberg requires allow_insert_into_iceberg (25.7+, Beta from 26.2). See Writing to data lakes. Delta Lake requires allow_delta_lake_writes (25.9+). The support matrix lists which flags apply to each format and operation.

Improve query performance

Version numbers on this page match ClickHouse release versions (Cloud and self-managed). Check your service version before enabling a setting or feature.

Lake query performance depends on how much metadata and how many Parquet files ClickHouse reads from object storage. As with ClickHouse, query performance is improved by filtering on partition columns and selecting fewer columns.

Query habits

Filter on partition columns in WHERE. Iceberg and Delta Lake store partition metadata that lets ClickHouse skip irrelevant files during query planning. If your filter targets a column outside the partition spec, ClickHouse scans every matching file.

For Iceberg tables with hidden partitioning, filter on the source column in the table schema—not a separate partition column or transformed field name. If the table is partitioned by day(event_time), add a predicate on event_time. ClickHouse derives partition pruning from that filter using the Iceberg partition spec. See Partition pruning and the Iceberg spec.

SELECT count()
FROM my_lake.`logs.application`
WHERE event_time >= '2026-03-01'
  AND event_time < '2026-03-02'

List only the columns you need instead of SELECT *. ClickHouse reads Parquet column-by-column from object storage, so narrower selects reduce bytes transferred and decompressed.

Put selective filters in WHERE. From ClickHouse 26.2+, PREWHERE is also supported on Iceberg and other lake table reads, where it filters at the Parquet layer before reading remaining columns. Partition pruning still depends on filtering partition source columns, not on PREWHERE alone.

Iceberg tables with heavy position or equality deletes apply merge-on-read filtering during scans. Expect more work per file than manifest pruning alone suggests.

On multi-node deployments, use cluster table functions to distribute file reads across replicas.

Parallel reads on multi-node clusters

On ClickHouse Cloud and self-managed multi-node services, cluster variants of lake table functions distribute Parquet file reads across replicas. The initiator node dispatches files to workers in parallel. Use cluster variants for batch reads and scheduled loads over large tables. On single-node deployments, the standard table function is enough.

Pass your cluster name as the first argument ('default' on ClickHouse Cloud). Cluster variants exist for all supported formats:

Format	Cluster functions
Iceberg	icebergS3Cluster(), icebergAzureCluster()
Delta Lake	deltaLakeCluster(), deltaLakeAzureCluster()
Hudi	hudiCluster()
Paimon	paimonS3Cluster()

You can combine cluster reads with other performance settings.

Bound batch reads to snapshots

For repeated batch loads from lake tables, scope each run to a snapshot range instead of re-reading the full table. Without bounds, ClickHouse may scan all versions and files on every run, which increases object storage reads and query time.

Store the snapshot identifier from your last successful load and use it as the lower bound on the next run.

• For Iceberg, read a point-in-time view with iceberg_snapshot_id or iceberg_timestamp_ms (25.4+). For append-only tables, combine snapshot settings with partition filters in WHERE. Use system.iceberg_history (25.6+) to look up snapshot IDs between runs.
• For Delta Lake, read changes between two versions with delta_lake_snapshot_start_version and delta_lake_snapshot_end_version (25.12+). Read a single snapshot with delta_lake_snapshot_version (25.8+). See Delta change data feed for a CDF example.

Cache Parquet files locally

Both formats honor enable_filesystem_cache to keep hot Parquet files on local disk between queries. On self-managed deployments, configure a filesystem cache disk in server config so the setting has storage to write to. ClickHouse Cloud manages caching automatically. Set enable_filesystem_cache = 0 when benchmarking so cache hits don't mask changes between runs.

Apache Iceberg

Most Iceberg read optimizations are on by default. The settings below control partition pruning, metadata caching, and catalog roundtrips.

Read settings

Setting	Since	Default	Notes
use_iceberg_partition_pruning	25.1	1 from 25.6	Skips data files using partition metadata in manifests
use_iceberg_metadata_files_cache	25.4	1	Caches manifest lists and metadata JSON in memory
iceberg_metadata_staleness_ms	26.3	0	Query setting. Use cached metadata when fresher than this window instead of calling the catalog on every query
iceberg_enable_version_hint	25.6	—	Reads version-hint.text for faster metadata resolution on direct path access

Cut catalog latency

Catalog-connected Iceberg tables pay a metadata fetch on each query unless you cache it. Pair two settings (26.4+):

1. Set iceberg_metadata_async_prefetch_period_ms at table creation to prefetch metadata in the background.
2. Set iceberg_metadata_staleness_ms (26.3+) on queries to accept slightly stale metadata in exchange for skipping the catalog roundtrip.

CREATE TABLE events
    ENGINE = IcebergS3('https://my-bucket.s3.amazonaws.com/warehouse/events/')
SETTINGS iceberg_metadata_async_prefetch_period_ms = 60000;

SELECT count()
FROM events
SETTINGS iceberg_metadata_staleness_ms = 60000;

A staleness value of 0 always fetches the latest metadata. Increase the window for read-heavy workloads where tables change infrequently.

When ClickHouse picks the wrong metadata file (multiple .metadata.json files in the table path), pin resolution with iceberg_metadata_file_path (25.4+) or iceberg_metadata_table_uuid at table creation. See Metadata file resolution.

Time travel

Read a historical snapshot with iceberg_timestamp_ms or iceberg_snapshot_id (both 25.4+). Don't set both in the same query. Inspect snapshot lineage in system.iceberg_history (25.6+) before choosing an ID. For repeated batch loads, see Bound batch reads to snapshots.

SELECT count()
FROM my_iceberg_table
SETTINGS iceberg_timestamp_ms = 1714636800000

Iceberg writes

Beyond allow_insert_into_iceberg (25.7+, Beta from 26.2), control output file size and partition count on insert:

Setting	Since	Purpose
iceberg_insert_max_rows_in_data_file	25.9	Row limit per output data file
iceberg_insert_max_bytes_in_data_file	25.9	Byte limit per output data file
iceberg_insert_max_partitions	25.12	Cap on partitions written in a single insert

See Writing to data lakes and the Iceberg engine reference.

Delta Lake

From version 25.6, ClickHouse reads Delta Lake on S3 and GCS through the Delta Lake Rust kernel (allow_experimental_delta_kernel_rs, 25.5+). On Azure Blob Storage, use deltaLakeAzure() with the legacy reader because the kernel is disabled there. Without the kernel, partition pruning, change data feed, and snapshot version reads aren't available.

Delta Kernel

allow_experimental_delta_kernel_rs must be enabled for partition pruning, change data feed, and snapshot version reads. It's on by default on S3 and GCS from 25.5. Enable it explicitly on older versions or when troubleshooting:

SET allow_experimental_delta_kernel_rs = 1;

Read settings

Setting	Since	Default	Notes
delta_lake_enable_engine_predicate	25.8	1	Pushes filters into the kernel for partition pruning. Requires Delta Kernel
delta_lake_reload_schema_for_consistency	26.3	0	Reloads schema before each query when concurrent writers evolve schema
delta_lake_snapshot_start_version / delta_lake_snapshot_end_version	25.12	-1	Read CDF changes between two snapshot versions. Requires CDF enabled upstream
delta_lake_snapshot_version	25.8	-1	Read a single historical snapshot. Set -1 for latest (0 is valid)

Tables with deletion vectors (26.2+) apply row-level filtering during reads. ClickHouse handles this automatically, but scans on DV-heavy tables do more work per file.

Delta change data feed

To read only the rows that changed between two Delta snapshots, set delta_lake_snapshot_start_version and delta_lake_snapshot_end_version (25.12+). The table must have change data feed enabled upstream (delta.enableChangeDataFeed). Set both start and end version in query settings. Setting only the end version produces an error.

SELECT *
FROM deltaLake('s3://my-bucket/warehouse/ga4_events/')
SETTINGS
    delta_lake_snapshot_start_version = 42,
    delta_lake_snapshot_end_version = 47

Store the end version after each successful load and pass it as the start version on the next run. The result includes CDF columns (_change_type, _commit_version, _commit_timestamp). Handle these before loading into your target table. For the general snapshot pattern, see Bound batch reads to snapshots.

Delta Lake writes

Beyond allow_delta_lake_writes (25.9+), control output file size on insert:

Setting	Since	Purpose
delta_lake_insert_max_rows_in_data_file	25.9	Row limit per output data file
delta_lake_insert_max_bytes_in_data_file	25.9	Byte limit per output data file

SET allow_delta_lake_writes = 1;

INSERT INTO my_delta_table
SETTINGS
    delta_lake_insert_max_rows_in_data_file = 1000000,
    delta_lake_insert_max_bytes_in_data_file = 134217728
SELECT * FROM source_table

Writes require the Delta Kernel on S3 or GCS. See the DeltaLake engine reference for examples.

Debug lake queries

Lake queries that are slow or return unexpected results usually trace back to metadata reads, partition pruning, or catalog connectivity. Start with the checks below, then use format-specific metadata logs if needed.

Verify catalog connectivity

CREATE DATABASE with DataLakeCatalog doesn't validate credentials. A database can exist while the catalog connection is broken. From ClickHouse 26.4, run a lightweight health check:

CHECK DATABASE my_lake;

On earlier versions, confirm connectivity with SHOW TABLES FROM my_lake and inspect the error message. Use SHOW CREATE TABLE with a backtick-quoted table name to verify the resolved storage path and engine type:

SHOW CREATE TABLE my_lake.`db.table`;

If catalog tables don't appear in system.tables, enable show_data_lake_catalogs_in_system_tables (25.8+). Catalog tables are hidden from system introspection by default.

See which files are read

Iceberg and Delta Lake expose virtual columns (_path, _file, _size, _time, _etag) on every read. Group by _path to see whether partition pruning is working or a query is scanning more files than expected. For Iceberg tables with hidden partitioning, filter on the source column (for example event_time), not a separate partition column:

SELECT _path, count() AS rows
FROM my_lake.`logs.application`
WHERE event_time >= '2026-03-01'
  AND event_time < '2026-03-02'
GROUP BY _path
ORDER BY rows DESC;

Check scan volume

Compare read_rows and read_bytes in system.query_log before and after adding filters or tuning settings. ProfileEvents like ReadBufferFromS3Bytes and CachedReadBufferReadFromCacheBytes show how much data came from object storage versus the local cache. See Query optimization for a full walkthrough of query_log and EXPLAIN.

Disable enable_filesystem_cache when benchmarking so cache hits don't mask changes between runs.

Metadata logs

ClickHouse exposes three system tables for metadata-level debugging. Enable logging at query time only. They're not for continuous monitoring.

System table	Format	Since	Enable with	Use it to
system.iceberg_metadata_log	Iceberg	25.9	iceberg_metadata_log_level on the query	Trace metadata files read and partition pruning decisions
system.iceberg_history	Iceberg	25.6	Populated automatically for Iceberg tables in ClickHouse	Inspect snapshot lineage before time travel queries
system.delta_lake_metadata_log	Delta Lake	25.10	delta_lake_log_metadata = 1 on the query	Trace Delta metadata files and snapshot resolution

Run a query with logging enabled, flush the log, then inspect entries for that query_id:

SELECT count() FROM my_iceberg_table
SETTINGS iceberg_metadata_log_level = 'manifest_file_entry';

SYSTEM FLUSH LOGS iceberg_metadata_log;

SELECT content_type, file_path, pruning_status
FROM system.iceberg_metadata_log
WHERE query_id = '<previous_query_id>';

On ClickHouse Cloud, log data is local to each node. Use clusterAllReplicas to see the full picture across replicas.

Verbose Iceberg log levels disable metadata caching for manifest lists and files, which slows subsequent queries on the same table. Use high verbosity only while actively investigating. For Delta Lake predicate issues, enable delta_lake_throw_on_engine_predicate_error (25.8+) to fail fast when the kernel can't push a filter down.

See the iceberg_metadata_log and delta_lake_metadata_log reference pages for column details and verbosity options.

Next steps

• Getting started — End-to-end walkthrough from direct querying to writing back
• Querying directly — Table functions, engines, and cluster variants for all four formats
• Connecting to catalogs — DataLakeCatalog setup with Unity Catalog
• Writing to data lakes — Write data back to Iceberg and Delta Lake
• Support matrix — Feature comparison across formats, catalogs, and storage backends