Querying Iceberg and Delta Lake data lakes from ClickHouse: when teams need it
Run a self-hosted ClickHouse query engine over an existing lakehouse when your Iceberg or Delta Lake tables already sit on object storage, your teams need fast SQL analytics on them, and copying everything into a second store would cost more in duplicated data and pipeline work than it saves. The question to answer first is whether the data must stay where it is because other engines already read it. If Spark, Trino, Snowflake or Athena jobs depend on the same tables, a lake query path lets ClickHouse join that data without becoming the owner of it. If ClickHouse is the only consumer and you control the ingest, loading into MergeTree is usually the simpler path.
Querying a lake means ClickHouse reads table metadata and Parquet data files in place, through table functions or catalog connections, and the table format's own transaction log decides which files are current. MergeTree means ClickHouse owns the storage layout, sorting keys and indexes, so queries on hot data are typically faster and more predictable. The trade-off is a second copy with its own retention and backup rules. Both open table formats are published under the Apache License 2.0 and are maintained as independent projects: Apache Iceberg describes itself as a high-performance format for huge analytic tables that many engines can share at the same time, and the delta-io/delta repository is released under the same license. Confirm the exact Iceberg or Delta feature set each ClickHouse build supports before you commit to a design.
Which access pattern fits each lakehouse situation
This table compares four ways to reach Iceberg or Delta Lake data from self-hosted ClickHouse: direct table functions, catalog-backed connections, copying hot data into MergeTree, and keeping data only in the lake. Read each row against your freshness needs, query latency targets and write requirements, then confirm the listed items against the ClickHouse release you plan to run.
| Access pattern | What it suits | Cost in freshness and duration | What to confirm |
|---|---|---|---|
| Direct table functions | Ad hoc queries and exploration over a known table location, with no extra copy of the data | Reads the lake on each query, so latency depends on object storage and file layout; freshness is current with the table | Which table function and format support the release in use, and whether the table location is reachable from ClickHouse nodes |
| Catalog-backed connections | Shared catalogs where several engines already maintain table metadata and teams expect one table list | Adds catalog round trips and a dependency on catalog availability; freshness follows the catalog's latest snapshot | Which catalog types the ClickHouse release supports (for example Hive metastore, AWS Glue, JDBC, REST, Nessie or Snowflake, which are Trino catalog types and must be checked separately), write support, and how catalog credentials are stored and rotated |
| Copying hot data into MergeTree | Dashboards and repeated queries that need low latency over a recent or frequently queried subset | Requires an ingestion job, storage for the copy and a refresh schedule; freshness lags by the refresh interval, and the copy must be reconciled with lake snapshots | The ingestion method, how deletes and schema changes in the lake propagate, and how the copy is backed up under your ClickHouse backup plan |
| Keeping data only in the lake | Large, infrequently queried history where the lake is the system of record and other engines also write | No copy to maintain, but query speed depends on the lake layout, file sizes and caching; time travel queries depend on what the engine exposes | Whether the lake format and catalog are readable by your ClickHouse version, which time travel syntax applies, and who owns retention and compaction of lake files |
Which catalog and table format the lake uses
Before any ClickHouse query runs, the team has to know which table format the lake uses, which catalog tracks its metadata, and which engines already read it. These choices decide which access path is supported and which combinations need confirmation against ClickHouse documentation before the design is fixed.
Iceberg or Delta Lake origin
Iceberg is an Apache project with a multi-engine design, and Delta Lake is an open-source storage framework that is a Linux Foundation sub-project and originated at Databricks. Both use Parquet data files, but their metadata layouts differ. Ask which format each existing table uses, because that decides which ClickHouse access path applies. Confirm the current Iceberg and Delta support in the ClickHouse documentation for your version rather than assuming parity.
Catalog choice for Iceberg tables
Iceberg tables are found through a catalog, such as a REST catalog, AWS Glue, a Hive metastore, JDBC or Nessie. The catalog is the point where schema, snapshots and access checks meet, so a mismatch leaves ClickHouse unable to locate current table state. List the catalog your lake already uses, confirm that ClickHouse supports that catalog type for your release, and check who owns catalog credentials.
Managed catalogs and Unity
Some lakes sit behind a managed catalog, such as Unity Catalog, or a vendor catalog such as Snowflake's Horizon Iceberg REST endpoint. Access may depend on vendor-specific credential vending and table ownership rules. Confirm with ClickHouse documentation whether a given managed catalog is supported, and with the platform owner which principals may read which tables before any connection is built.
UniForm for Delta tables
UniForm, short for Delta Universal Format, lets Iceberg and Hudi clients read Delta tables by writing Iceberg metadata alongside the Delta log. This can let ClickHouse read a Delta table through an Iceberg path, but it adds a metadata step that must stay in sync. Ask whether UniForm is enabled on each table, who maintains that setting, and whether ClickHouse's Iceberg support has been tested against the same table version.
OneLake and Athena exposure
Microsoft OneLake can expose Delta tables to external Iceberg readers through metadata virtualization, and Amazon Athena engine version 3 queries Iceberg tables with time travel clauses. These paths determine whether a table is reachable from ClickHouse at all and which snapshot semantics apply. Confirm the exact combination you need, including the engine version and the metadata path, against ClickHouse's current table function and catalog documentation.
Confirm the combination before building
Each card above changes the access path, so the final design depends on the specific pairing of format, catalog and reader. Write down one row per table group: format, catalog type, metadata path, owning team and ClickHouse version. Verify every row against the ClickHouse documentation for the release you will run, and test a sample read in a pre-production environment before committing to a pattern.
Setting up lake queries on a self-hosted ClickHouse cluster
This sequence takes an existing Iceberg or Delta Lake estate from inventory to a documented, tested query path in ClickHouse, with each choice checked against known table versions before any hot data is copied.
Inventory tables, formats and access patterns
The data owner and the platform team list every lake table that ClickHouse should read, with its table format (Iceberg or Delta Lake), the catalog that holds it, the storage location, partition layout, row counts, retention rules and the queries analysts run most often. Record which tables carry personal or regulated data, because those need stricter access rules later. Confirm with each table owner whether Iceberg tables are written by Spark, Trino or another engine, and whether Delta tables use UniForm. The step is done when the inventory is a reviewed document that the table owners have signed off, and every table has a named owner and a stated format.
Choose the catalog and access method
Match each table to a catalog and a ClickHouse access method. A direct table function reads a table at a storage path and suits ad hoc exploration or small tables. A catalog connection suits tables that other engines also change, because the catalog tells ClickHouse which snapshot or version is current. Check the ClickHouse release you run against the table function and catalog support in its documentation, since these features change between versions. Confirm which catalogs your existing engines use, such as a Hive metastore, AWS Glue, a REST catalog or Unity Catalog, and whether the network path from ClickHouse to each catalog and storage endpoint is allowed. The check is a written decision per table and a confirmed network route for each one.
Create least-privilege storage and catalog credentials
The security team creates a dedicated service identity for ClickHouse with read-only access to the lake prefixes and catalog namespaces in the inventory, and nothing else. Writes, table creation and deletes stay with the engines that own the tables. Store the credentials in your secrets manager, not in configuration files checked into source control, and give ClickHouse users access through its access control model with roles and row policies where analysts should see only part of a table. Restrict the default user, which starts with all permissions. The check is a test that the service identity can read the approved tables, and an attempted write or read outside the approved prefixes fails.
Configure ClickHouse connections on a pre-production cluster
The platform team sets up the catalog connection or table function definitions on a pre-production ClickHouse cluster that holds representative data and matching network access, not on production. Keep the configuration in version control so the same definitions can be applied to production later. Enable TLS for connections to catalogs and storage where the endpoints support it, and confirm how credentials are passed to the cluster without appearing in logs or query text. Run a small set of read queries from a test user to confirm the connection works end to end. The step is done when a read from each approved table succeeds on the pre-production cluster and the configuration is stored with a reviewed change record.
Validate reads against known snapshots or versions
Choose a historical state for each table and compare ClickHouse results with the engine that owns it. For Iceberg, use a known snapshot ID or timestamp. For Delta Lake, use a known table version. Run the same query in both engines and compare row counts, aggregates and a sample of rows. Confirm that schema changes made with schema evolution appear correctly, and that deletes and merges in the source table are reflected after the catalog refreshes. Record the snapshot or version used for each comparison so that the result can be repeated. The check is a matching result for every table at the chosen states, with any mismatch traced to a cause before the next step.
Measure latency and cost on representative queries
Run a representative set of queries that reflect the real dashboards and investigations, using realistic filters and date ranges from the inventory. Measure response time, data read from storage, and the number of requests sent to the catalog and object storage at a stable data state. Repeat runs to separate cold reads from cached reads, and check what caching settings the release provides. Ask the storage provider how request and egress charges apply to these reads, because the cost of scanning lake files repeatedly is usually driven by storage requests and bytes read. The output is a table of queries with timings, bytes read and a note on cost drivers that the team can compare with the MergeTree option.
Decide which tables to copy into MergeTree
For each table, decide whether it stays in the lake or is copied into a native MergeTree table on the cluster. Copy tables that are queried often, need predictable latency, or are joined heavily with other ClickHouse data. Leave tables that are large, rarely queried or owned by another team in the lake. The copied tables then need their own ordering key, partitioning, retention and replication, which the ClickHouse storage and replication guides cover. Record the decision and the reason for each table in the inventory. The check is a list where every table has a stated location, either lake or MergeTree, and each MergeTree table has a named owner for its copy.
Document and schedule the refresh job
Write a runbook for the lake connections and any MergeTree copies. The runbook covers which catalog and credentials each connection uses, how often copied tables refresh and from which snapshot or version, how a failed refresh is detected and retried, who is paged, and how to roll a copy back to the previous state. Schedule the refresh job with your existing orchestration tool, have it write the snapshot or version it loaded into a control table, and alert when the copy falls behind the source. Test one failed refresh and one rollback on the pre-production cluster before production use. The step is done when the runbook is reviewed and the test results are attached to the change record.
Security and access controls for lake queries
A lake query from ClickHouse touches three layers: the object storage and catalog credentials ClickHouse uses, the ClickHouse users and policies that decide who can ask for what, and the record of which snapshot each reader saw. Each layer needs its own owner and its own checks, and none of them protects the others.
Scope storage and catalog credentials
ClickHouse reads Iceberg or Delta files with the credentials configured on the server, not with the identity of the person running the query. Give that service identity read-only access to the specific buckets, prefixes and catalog namespaces the workload needs, and confirm that write and delete permissions are absent. Check that a query from a restricted user cannot reach a table outside its grant, because the storage identity will often see more than the user should.
Restrict the default user
The ClickHouse access control documentation recommends the SQL-driven workflow and restricting the default user, which starts with all permissions. Disable or lock the default account before any lake catalog is connected, then create named users and roles for analysts, dashboards and pipelines. Confirm the change by logging in as each role and attempting a read it should not have.
Apply row policies to lake-backed reads
Row policies and grants defined for native MergeTree tables do not automatically describe what a table function or catalog-backed table returns. Write each policy against the exposed lake table or view, then run the same query as a restricted user and compare row counts against a known-good export. Re-test after any schema change in the lake, since a renamed column can silently bypass a filter that depends on it.
Choose user authentication method
ClickHouse supports LDAP directories, Kerberos, SSL X.509 certificates and JWT as external authenticators, along with an HTTP authenticator. Pick the method that matches your identity system so that removing a person from the directory also removes their query access. Confirm with your identity team how group membership maps to ClickHouse roles, and test a deprovisioned account before go-live.
Audit which snapshot each reader used
Lake tables are read at a point in time through snapshots or table versions, so an audit trail should show the user, the query and the table version or timestamp that was read. ClickHouse query logs record who ran a query, but confirm with your own testing that the logs capture the snapshot pinned in the query text. Keep these records under your retention rules, because a dispute about a report often depends on what the data looked like at that moment.
Questions teams ask before querying a lakehouse from ClickHouse
Does querying Iceberg or Delta Lake data in place replace copying data into MergeTree?
No, not for every workload. Querying the lake in place keeps one copy of the data and avoids a second pipeline, while copying hot data into MergeTree usually gives faster repeated queries because ClickHouse's own storage, ordering keys and indexes are used. Most teams end up with both: lake tables for broad, infrequent exploration and MergeTree tables for dashboards and queries that run constantly. The split depends on query frequency, latency targets and how often the lake schema changes. Ask your team to list the ten to twenty queries that matter most, then measure them against each path before you decide which tables to copy.
How long does a pilot take, and what drives the duration?
The duration depends mainly on how many table formats and catalogs are involved, how clean the lake metadata is, and how many queries must be validated. A pilot against one catalog and one Iceberg or Delta table with a short list of queries is much smaller than a pilot covering several catalogs, both formats and access controls. Other drivers include whether storage credentials and network paths already exist, whether the ClickHouse cluster is already running, and how long representative data takes to load. Ask any partner for a written scope that names the catalogs, table formats, query list and acceptance criteria, and a timeline tied to those items.
What must our team provide before a lakehouse integration starts?
Three inputs gate most of the work: read access to the catalog, storage credentials for the object store that holds the data files, and a set of representative queries with expected results. Catalog access depends on the type in use, such as a Hive metastore, AWS Glue, a JDBC catalog, a REST catalog or Nessie, so the team should confirm which one holds the tables and who administers it. Storage credentials need least-privilege read access to the relevant buckets or paths. Representative queries show which filters, joins and time ranges matter, and they define what success looks like. Ask for a written list of these inputs with owners and due dates in the quote.
Can our own engineers build and run this without a partner?
Yes, if your team already runs ClickHouse, understands the table format and catalog in use, and has someone who can own credentials and network access. The work is mostly configuration, query testing and data modeling rather than new platform code. A partner adds value when several formats and catalogs must be supported together, when access control and encryption must be designed with your security team, or when the team lacks time to validate results and performance. Decide by listing the skills you already have against the skills in the scope. Whichever path you choose, require a written record of configuration, tested queries and rollback steps.
How do we keep MergeTree copies of lake data fresh?
Freshness comes from the refresh method you choose, and each method has a different operational cost. A scheduled job can reload a partition or date range on a fixed interval, which is simple to reason about but leaves a known lag between the lake and the copy. An event-driven or streaming load can narrow that lag but needs more monitoring and handling for late or changed data. Both approaches should record which lake snapshot or table version each copy reflects, so that queries can be checked against the source. Ask for the refresh schedule, the lag you can accept, how failed loads are retried, and how the team detects a copy that has silently stopped updating.
How do ClickHouse upgrades change how lake queries behave?
An upgrade can change query planning, function behavior, supported table features and default settings, so lake queries should be retested after every version change. The ClickHouse production guidance recommends stable releases for most production use, a pre-production environment with representative data and queries, and reading the changelog before upgrading because minor backward-incompatible changes can occur. The ClickHouse security policy lists which versions receive security updates, which affects how long a given release line can stay in service. Keep a fixed regression suite of lake queries with expected row counts and results, run it on a staging cluster before each upgrade, and confirm with your vendor which catalog and format features are supported in the target version.
What should we ask for in a quote for a lakehouse integration?
Ask for a written scope that names each catalog, table format, storage location and query in the acceptance set, plus the access model and the copies to be built into MergeTree. The quote should separate one-time work, such as design, configuration, credential setup and validation, from recurring work, such as refresh monitoring, upgrades and regression testing. It should list what your team must provide and when, because delays in catalog or credential access are a common cause of schedule slip. Ask how changes to scope are priced, what documentation and runbooks you receive, and what evidence shows that queries return correct results. DeployOpen does not publish a price list, and its quotes follow from the written scope.
How do we secure access to lake data from ClickHouse?
Start with the principle that ClickHouse should hold only the read permissions it needs on the lake, and that users should reach lake tables through roles rather than shared credentials. ClickHouse access control covers users, roles, row policies, settings profiles and quotas, and its documentation recommends an SQL-driven workflow and restricting the default user, which starts with all permissions. External authentication through LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator or JWT can tie users to your existing directory. Storage and catalog secrets should be kept out of query text and stored in your secrets system. Ask your security team to review the grants, the network paths to the catalog and the object store, and the logging of who queried which table.
Running lake queries after go-live
Once lake queries are in production, the first thing to watch is whether they stay fast and whether the catalog and object storage behind them stay healthy. Track query latency per table function and per catalog connection, not only cluster-wide, because a slow Iceberg or Delta scan usually shows up as one query pattern rather than a general slowdown. Watch cache hit behavior over time as well: a falling hit rate after a lake compaction, a schema change or a traffic shift tells you the cache is no longer serving the working set you planned for. Keep the ClickHouse system tables and the Prometheus metrics your monitoring team already collects in view, and set alerts on failed catalog calls and object storage errors so that a credential expiry or a throttled bucket is caught before analysts report it. Confirm the exact metric names and cache settings for your ClickHouse version in the release documentation before building dashboards on them.
Plan ClickHouse upgrades with lake tests included. Each new stable release can change how lake table functions, catalog clients and the query analyzer behave, so run your saved lake queries, catalog connection checks and copied MergeTree workloads against the upgraded version in a pre-production environment with representative data before touching production. The ClickHouse production FAQ recommends reading the changelog first, because minor backward-incompatible changes can occur. Confirm which versions receive security updates from the ClickHouse security policy before you choose a target. Treat the lake's own table format versions as a separate change: the Iceberg or Delta engine you query through must still read the snapshots your producers write.
Backups divide cleanly into two groups. Lake data stays protected by the lake's own storage and versioning, such as snapshot retention in Iceberg or the transaction log and time travel in Delta Lake, so do not copy it into ClickHouse backups as your only protection. Your recovery plan for those tables is about retention settings and object storage protection in the lake platform. MergeTree copies you created for speed are different: they are ordinary ClickHouse tables and follow ClickHouse backup and restore with BACKUP and RESTORE to local disk, S3-compatible storage or Azure Blob Storage. The documentation notes that replication alone does not protect against human error or corruption, and that a backup never test-restored will probably not restore properly, so schedule restores to a spare cluster and record how long they take.
How DeployOpen works on this
DeployOpen plans, implements, integrates, documents and supports self-hosted open-source platforms inside your own cloud accounts, data centers or air-gapped networks. For lakehouse query setups, we start with a written scope that names the table formats, catalogs, storage locations and access rules involved before any build begins.
How we scope
- Inventory which Iceberg or Delta tables, catalogs and storage locations ClickHouse must read, and who owns each one.
- Agree in writing which query paths are in scope: direct table functions, catalog connections, or copies into MergeTree.
- Confirm the ClickHouse version line, network paths to object storage, and the credential model before engagement starts.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing. Ongoing support is available as a retainer.
How we build
- Stand up a pre-production ClickHouse environment with representative lakehouse data and the queries your analysts actually run.
- Configure catalog connections and credentials with least-privilege access, then validate reads against the table snapshots you expect.
- Define which hot datasets are copied into MergeTree, with refresh logic, and measure where the copy is worth its storage cost.
- Test schema evolution, time travel and cache behavior against your catalog, and record results before go-live.
How we hand over
- Deliver runbooks covering catalog configuration, credential rotation, cache settings and the steps to add a new table.
- Document the access model, including ClickHouse users, roles and row policies applied to lakehouse-backed queries.
- Walk your platform and data teams through query troubleshooting, version upgrade checks and the changelog review process.
- Hand over configuration as code and a list of open items, with owners confirmed in the written scope.
How we support
- Review ClickHouse and catalog version changes against the security policy and release notes before you upgrade.
- Re-test lakehouse queries after catalog, format or engine upgrades, including the Iceberg and Delta versions you run.
- Run retainer-based support for incidents, configuration changes and periodic reviews of cache and copy strategy.
- Keep the scope document current, so changes to tables or catalogs are priced and planned through the same written process.
