How ClickHouse integrates with Delta Lake through the DeltaLake engine
If Delta tables already sit in object storage and your team wants ClickHouse to query them, the supported route is the DeltaLake table engine. It reads existing Delta Lake tables in S3, Google Cloud Storage and Azure storage, and it can also write to them, but write support depends on where the table lives: writes to S3 and GCS are supported from ClickHouse version 25.10, and writes to Azure from version 26.9. Confirm the exact release you plan to run against the ClickHouse DeltaLake engine documentation before you enable writes, because a cluster on an older release can read a table that it cannot safely write to.
The format itself is *Delta Lake*, an open-source storage framework released under the Apache License 2.0. Its source is maintained in the delta-io/delta repository, and delta.io states that the project has been a Linux Foundation sub-project since 2019 and is not controlled by any single company. That history matters for planning because the table layout and transaction log are not tied to ClickHouse, so other engines in your estate can keep using the same tables. Check which Delta Lake protocol features your writers and readers use, since engines differ in what they support.
Two settings shape the work. Creating a new Delta table from ClickHouse requires allow_delta_lake_create_table set to 1, so decide whether application teams should be able to create tables at all. For Google Cloud Storage, the engine does not accept gs:// URIs and expects ` URLs, so every table definition and migration script must be checked for that form. Ask your team to test reads and writes from a non-production cluster that holds a copy of representative tables before any production bucket is opened for writes.
What you need before connecting ClickHouse to Delta Lake
Four things decide whether the DeltaLake engine works on the first attempt: the ClickHouse build, the storage credentials, the network path to the storage endpoint and a clear picture of the Delta table. Work through the cards in order, because the version check determines whether writes are possible at all.
ClickHouse version for reads and writes
Reads of existing Delta tables are available across the supported builds, but writes depend on the storage provider. Writes to S3 and Google Cloud Storage require version 25.10 or later, and writes to Azure require version 26.9 or later. Confirm the exact build you run against the releases page and the supported-versions list in the security policy, since a build outside the supported lines gets no security updates.
Storage credentials and bucket access
ClickHouse needs credentials that can list the table prefix, read Parquet data files and the _delta_log directory, and, for writes, add new files and commit log entries. Read-only and write grants should be separate identities so analysts cannot change production tables. Ask the storage team which principal ClickHouse will use and whether bucket policies or object-level permissions restrict prefixes.
Network path to storage endpoints
Every ClickHouse node that runs a query against the table must reach the storage endpoint directly, so firewall rules, proxies, egress policies and private endpoints need checking on each node, not only on one coordinator. Confirm DNS resolution and TLS trust for the endpoint from inside the restricted network, and test with the same node pool that will run production queries.
Google Cloud Storage URL format
The DeltaLake engine does not accept gs:// URIs from gsutil. Google Cloud Storage paths must be written as URLs beginning with Teams that already keep table locations in gs://` form in pipeline configuration should convert them before wiring any new table definition, and should check that every consumer uses the same converted path.
Delta table location and schema
Know the exact table root path, the current schema and which columns analysts will filter on. The engine reads the transaction log at that location, so a path pointing at a parent folder or a copy can return unexpected data. Record whether the table is partitioned, whether its schema evolves, and which Delta table version your reports expect, because time travel and schema changes affect what a query returns.
Create-table permission and ownership
Creating a new Delta table from ClickHouse requires setting allow_delta_lake_create_table to 1, which should be a deliberate decision rather than a default. Decide who owns table creation, who may write to existing tables and whether a Delta writer such as Spark or Databricks also changes the same tables. Concurrent writers from different engines need an agreed ownership model before the first write.
How to configure the DeltaLake engine step by step
This sequence takes a self-hosted ClickHouse cluster from a version check to a verified read-only Delta Lake table, and only then to optional table creation, so each stage has a row count or schema check before the next one begins.
Confirm ClickHouse version and storage provider
The platform team confirms which ClickHouse version runs in production and which storage provider holds the Delta tables: S3, Google Cloud Storage or Azure. Writes are supported for S3 and GCS from version 25.10 and for Azure from version 26.9, so a read-only plan works on a broader range of versions than a write plan. Confirm the running version on every node, not only the one you connect to, and check it against the supported-versions list in the ClickHouse security policy. The step is done when the version, provider and intended access mode (read or write) are written down and agreed by the data owner.
Set storage credentials with least privilege
A cloud or storage administrator creates an identity that can list and read the Delta table prefix, and can write to it only if the write path is approved. Store the keys or service credentials in your secrets system or ClickHouse named collections rather than in query text or shared scripts, and restrict the ClickHouse user that runs the queries. The check is a test read from a ClickHouse client using that identity, with a deliberately wrong path confirmed to fail, which proves the credential is scoped and working.
Use the correct URL format for each provider
The engine needs the storage URL in the form each provider accepts. For Google Cloud Storage, use the form and not gsutil-style gs:// URIs, which the DeltaLake engine does not accept. Write the URL for S3 or Azure in the form the ClickHouse DeltaLake engine reference describes for that provider. Verify each URL by listing the table's root folder from a storage client outside ClickHouse before you use it in DDL, so a path mistake is caught before it reaches the engine.
Create a read-only DeltaLake table
A ClickHouse administrator creates a table with the DeltaLake engine that points at the existing Delta table, without enabling table creation. Keep the table in a database that only analysts and pipeline users can query, and use a read-only ClickHouse user for queries. Confirm the table exists by running a simple SELECT of a few rows and checking the column names and types that ClickHouse reports against the Delta schema from the source system, so type mismatches surface immediately.
Compare row counts and schema with the source
The data owner and the platform engineer compare the ClickHouse table with the source before any further work. Run a row count on the DeltaLake table and on the same query in the source engine, compare the column list and types, and check a sample of values for a few known keys. Time travel by version is also useful here if the source supports it, since it lets you compare a fixed snapshot. The step is complete only when counts and schema match or every difference is explained and accepted in writing.
Decide whether to copy data into MergeTree
Using the results of the comparison, the team decides whether queries should keep reading Delta files directly or whether a MergeTree copy is needed for speed. Repeated dashboard queries over large tables usually benefit from a MergeTree table loaded from the Delta source, with a scheduled refresh owned by a named team. Record the refresh method, the freshness the consumers need, and who monitors the load job. The check is a timed test of the target query against both the engine table and the MergeTree copy, using the same filters.
Enable table creation only when needed
Only after the read-only table and comparisons pass, and only if the team needs ClickHouse to create new Delta tables, set allow_delta_lake_create_table = 1 for that session or profile as scoped by your change process. Create the new table in a non-production path first, then run the same row count and schema checks on it. Afterwards, restore the setting to its default for general users so that table creation stays limited to the people who own that task.
Querying Delta tables live or copying them into MergeTree
Read-only exploration is the simplest use of the DeltaLake engine. The table stays where the data producer wrote it, ClickHouse reads the Delta transaction log and Parquet files on demand, and nothing is duplicated into a ClickHouse table. This suits ad hoc investigation, validating a new producer's output, and checking what another engine such as Spark or Databricks has committed. Every query goes back to object storage, so latency and request volume depend on the table layout and the data scanned, not on a local copy.
- Freshness: queries see the latest committed table version without a load step.
- Repeated reads: each query reads object storage again, so frequent identical queries multiply those requests.
- Schema control: the schema is owned by the writer, so ClickHouse cannot add indexes or change column types for you.
- Ownership: the copy does not exist, so there is nothing for ClickHouse to back up or retain.
- Access: use read-only credentials scoped to the table path.
Before relying on this pattern, confirm with the Delta writer which table versions and features your ClickHouse release can read.
ClickHouse can also write Delta tables, which matters when a ClickHouse pipeline should publish results for other engines to read. Write support depends on the storage provider. Per the ClickHouse DeltaLake engine documentation, writes are supported for S3 and GCS from version 25.10 and for Azure from version 26.9. Creating a new Delta table additionally requires the setting allow_delta_lake_create_table, which is off by default.
- Version gate: confirm the cluster runs 25.10 or later for S3 or GCS, or 26.9 or later for Azure, before planning writes there.
- Google Cloud Storage URLs: use the form rather than gs:// URIs.
- Table creation: enable allow_delta_lake_create_table only for the specific sessions that need it, and keep it off for routine users.
- Concurrency: check how other writers commit to the same table before adding a second producer.
- Ownership: the written table becomes a shared asset, so the data team must agree on its schema and retention.
Confirm the exact write behavior, supported features and any limits for your storage provider in the ClickHouse DeltaLake documentation before you commit to a design.
High-frequency dashboards often run the same aggregations many times a minute. Reading the Delta table live means every refresh repeats object storage reads and Parquet decoding. A MergeTree copy, loaded from the Delta table on a schedule or by a pipeline, moves that data into ClickHouse storage where you choose the sorting key, partitioning and indexes. The trade-off is that the copy is a second version of the data, and it is only as fresh as its last load.
- Freshness: a copy lags the Delta table by the load interval, so agree on an acceptable delay with dashboard owners.
- Repeated reads: the copy removes object storage reads from the dashboard path, while the load still reads from storage.
- Schema control: you can tune the MergeTree table, but you must map schema changes from the Delta writer into it.
- Ownership: the copy needs its own retention, backups, access rules and reconciliation with the source.
- Failure handling: define what dashboards show when a load fails.
Before choosing this path, ask the team to confirm the required freshness, the expected number of refreshes and who will own the load job.
Most teams combine the patterns. Exploration stays live, production dashboards read a MergeTree copy, and writes from ClickHouse are reserved for outputs that other engines must consume. The decision rests on four questions: how fresh the answer must be, how often the same data is read, how much control the team needs over the table structure, and who is accountable for the copy. None of these questions can be answered from a performance estimate alone, so test with representative queries.
- Freshness requirement: live reads are current, while copies carry a defined delay.
- Read frequency: the more often identical queries run, the stronger the case for a copy.
- Schema needs: choose a copy when the team needs its own sorting key, indexes or types.
- Governance: a copy adds a second data store that must be documented and access-controlled.
- Testing: run the target queries against both options on a pre-production cluster with realistic data volumes.
Ask for a written comparison of the two options on your own table sizes and refresh schedule before committing to either.
How Delta schema, types and table versions map into ClickHouse
This table compares how Delta Lake schema elements, partition columns and table versions appear when queried from ClickHouse through the DeltaLake engine. Use it to plan column checks and time travel tests before you point production queries at a Delta table. Confirm every type mapping against the DeltaLake engine documentation for the ClickHouse version you run, because mappings and feature support change between releases.
| Delta element | How it appears in ClickHouse | What to confirm before go-live |
|---|---|---|
| Primitive column types | Mapped to ClickHouse column types through the DeltaLake engine | Check each Delta type against the type mapping table for your version |
| Nullable columns | Delta nullability should carry into ClickHouse Nullable semantics | Test null counts and defaults on a copy of real data |
| Complex types (struct, array, map) | Support depends on the ClickHouse version and the engine's mapping rules | Query nested fields in a staging table and compare results with Spark |
| Partition columns | Available as columns that can be filtered, with pruning depending on the engine | Run filtered queries and check the files read against the Delta log |
| Time travel versions | Older table versions are reached through version settings on the engine, per the engine documentation | Confirm the setting name and read a known earlier version against a reference export |
| Schema evolution | Columns added in Delta after table creation may not appear until the table definition is refreshed | Test a column addition in staging and confirm queries see it |
| Unsupported Delta features | Features such as some column mapping or deletion vector behavior may be unavailable or restricted | List every table feature in use (from the Delta protocol) and check each against the engine docs |
| Write behavior | Writes depend on the version: S3 and GCS from 25.10, Azure from 26.9 | Confirm the write version and that allow_delta_lake_create_table is set only where new tables are intended |
Securing storage credentials and write permissions
Reading a Delta Lake table from ClickHouse is mostly a storage permission question, while writing or creating tables adds a second layer of ClickHouse-side controls. The cards below separate the two so each path gets its own bucket policy, setting and database account. Check each one before the first production table is created.
Split read and write bucket policies
Give the ClickHouse service identity read-only access to the Delta table prefix for query workloads, and grant write actions only to the identity used by ingestion jobs. Scope both to the table's path rather than the whole bucket, so a misconfigured table cannot reach neighboring datasets. Verify the policy with a test read and a denied write before go-live, since a permissive bucket role will quietly allow writes from every query user.
Keep table creation disabled
The setting allow_delta_lake_create_table should remain at its default of 0 in user profiles and server configuration. Enable it only for a named maintenance session or a dedicated provisioning account, then turn it off again. An unexpected CREATE TABLE against a shared bucket can register a new Delta table in a location that other teams already read from.
Restrict who runs CREATE TABLE
Limit table creation to a small set of administrator accounts, and grant them through a role rather than by individual user. Confirm that analysts and dashboard service accounts have no grant on CREATE TABLE in the databases that map to Delta locations. Review the grant list after every onboarding, because a broad grant on a database is easy to forget and hard to notice in query logs.
Restrict the default user
The default user starts with all permissions, so a Delta-enabled cluster should disable or lock it before any table points at object storage. Create named users for readers and writers, and give each one only the database and table grants it needs. Confirm the change in the access control documentation before you rely on it, because a forgotten default account would bypass every role you define.
Use roles and row policies for scope
Bundle grants into roles such as delta_reader and delta_writer, and apply row policies where one Delta table holds data for several tenants or business units. Row policies filter results at query time, so they limit what a reader sees but do not change what the storage identity can read. Pair them with bucket-level scoping, and test each policy with a user who should be excluded.
Protect credentials in configuration
Storage keys and service account secrets should not appear in table DDL, query logs or shared configuration files. Prefer named collections or environment-based secrets, restrict file permissions on the server configuration, and rotate keys on a schedule your security team sets. Ask whether the chosen identity method (access keys, workload identity or managed identity) fits your cloud provider's policy before building the first table.
How to test reads, writes and copies before production
Test the DeltaLake engine against a representative Delta table before any production query depends on it. Pick a table that reflects real partitioning, column types and file counts, and compare it with the same data read through the source system: run matching count() results, sum and min/max aggregates on key columns, and grouped totals by the partition column. Any mismatch in row counts or types points to a schema or type-mapping issue that is cheaper to find now. Use the ClickHouse production FAQ as the baseline for this work: it recommends a pre-production environment with representative data and queries, and that is the environment these checks belong in.
Verify time travel explicitly rather than assuming it behaves like the source engine. Choose a known earlier table version, query it through the DeltaLake engine, and confirm the row counts and sample values match what the Delta table history reports for that version. Confirm the version reads are pinned in the queries your dashboards or jobs will run, because a query without a pinned version reads the latest state. For writes, run a test insert or create-table operation only against a non-production bucket with allow_delta_lake_create_table enabled for that session, then confirm the new table is readable by at least one other engine. Confirm the write version support for your storage target (S3, Google Cloud Storage or Azure) against the ClickHouse DeltaLake engine docs before you schedule anything.
Test any MergeTree copy as a separate pipeline. Load a copy from the Delta table, then add or change rows in the Delta table and run the refresh job you plan to use in production. Confirm the copy matches the source after the refresh, that a failed or partial run leaves the previous copy usable, and that the refresh schedule is recorded with the owner who will maintain it. Record the results as an acceptance checklist covering counts, aggregates, time travel reads, write behavior and copy freshness, and have the data owner sign it before the cutover. Ask your team or partner to confirm the exact Delta Lake and ClickHouse versions used in testing, since those versions must match the production plan.
Why ClickHouse fails to read or write Delta Lake tables
This table lists the failures teams hit most often when the DeltaLake engine reads or writes tables in S3, Google Cloud Storage or Azure. Each row gives the likely cause and the fix to check first. Start with the version and URL rows, since they account for many first-day errors.
| Failure | Likely cause | Fix |
|---|---|---|
| Google Cloud Storage path rejected | A gsutil URI such as gs://bucket was used. The DeltaLake engine expects URLs beginning with | Rewrite the table path as an URL and retest the query |
| Write fails on an older server | Writes to S3 or GCS need version 25.10 or later, and Azure writes need 26.9 or later. The server is older than the write support for that provider | Check the server version with SELECT version(). Upgrade to a release that supports writes for your storage provider, or keep the table read-only |
| CREATE TABLE fails for a new Delta table | allow_delta_lake_create_table is not set to 1, so ClickHouse refuses to create the Delta table | Set allow_delta_lake_create_table = 1 for the session or profile that runs the statement, then create the table again |
| Access denied on the storage path | Credentials are missing, expired or lack list and read permissions on the bucket or container, or write permission on the table prefix for writes | Confirm the identity used by ClickHouse can list the table path, read the _delta_log directory and write data files where needed. Test with the storage provider's own CLI first |
| Connection timeouts or refused connections | Network rules, proxies or private endpoints block the storage endpoint from the ClickHouse nodes, often only on some nodes | Test HTTPS reachability to the storage endpoint from every replica. Check egress rules, DNS resolution and proxy settings in restricted networks |
| Schema mismatch on query | The table definition in ClickHouse no longer matches the schema recorded in the Delta transaction log after a schema change upstream | Compare the column list in the Delta log with the ClickHouse table definition. Recreate the table definition or pin the query to columns that still match |
| Stale or unexpected rows | The Delta table changed after a copy was made, or a MergeTree copy was loaded from an earlier table version | Record the table version used for each load, reload the copy after changes, and confirm the load job covers the expected version |
Questions teams ask before connecting ClickHouse to Delta Lake
Can an internal team connect ClickHouse to Delta Lake without a partner?
Yes, a team with ClickHouse and object storage experience can read existing Delta tables with the DeltaLake engine on its own, and many teams start there. The harder parts are write paths, credential handling, copy strategy and verifying results against the source tables. A partner is most useful when several providers, many tables or strict change control are involved. Confirm whether your engineers have time to build and test a pre-production setup before deciding.
What drives the effort of a Delta Lake integration?
Four factors move the effort most: the number of tables, the storage providers involved, whether ClickHouse must write to Delta or only read from it, and whether queries run directly against Delta or against MergeTree copies. Each additional table adds schema checks and test queries. Writes add version gating and rollback planning. Copies add refresh logic and storage cost. Ask for a written scope that lists each of these factors separately.
What does the work need from our team?
Expect to provide access to the storage accounts or buckets, named owners for each Delta table who can confirm schemas and business meaning, representative test data for a pre-production environment, and change windows for any cluster or table changes. Credentials for storage should be issued for this purpose and reviewed by your security team. Your owners also need to agree on acceptance checks, such as row counts and sample queries, before cutover.
Can ClickHouse write to Delta Lake on my storage provider, and which version applies?
According to ClickHouse's DeltaLake engine documentation, writes are supported for Amazon S3 and Google Cloud Storage from version 25.10, and for Azure from version 26.9. Reads from existing Delta tables are covered by the engine on all three providers. Google Cloud Storage requires URLs rather than gs:// URIs. Confirm the exact version on your own cluster before design, because the version you run determines which write paths are available.
Does creating a new Delta table from ClickHouse need extra settings?
Yes. According to the DeltaLake engine documentation, the setting allow_delta_lake_create_table must be set to 1 before a CREATE TABLE statement can create a new Delta table. Keep this setting out of default profiles unless your change process allows table creation by those users. Restrict which users can run it, and record which team owns each new table so schema changes have a clear approver.
How do ClickHouse and Delta Lake upgrades affect the integration?
Upgrades affect two sides: the ClickHouse server and the Delta Lake tables and protocol. ClickHouse publishes stable releases roughly monthly and LTS releases twice a year, and its production guidance recommends reading the changelog before each upgrade because minor backward-incompatible changes can occur. Write support depends on the version, so a ClickHouse upgrade can change what you are allowed to do. Test queries and writes against a pre-production copy after every upgrade, and check the security policy for which versions still receive fixes.
How do we confirm the cost of a Delta Lake integration?
Cost has a one-time part (scoping, build, testing and cutover) and a recurring part (storage, compute for queries and copies, and any support retainer). Storage and egress charges come from your cloud provider and depend on table size and refresh frequency, so ask your provider for those figures. DeployOpen issues a quote from a written scope, so ask for the fixed deliverables, the assumptions behind each table count and provider, and which items are excluded.
Should we query Delta tables directly or copy them into MergeTree?
Query Delta tables directly when data is read occasionally, when the table is owned elsewhere, or when you want a single copy of the data. Copy into MergeTree when dashboards or many users need fast, repeated queries, because MergeTree tables use ClickHouse's own storage layout and indexes. A copy adds refresh logic, storage cost and a lag between the source and the copy. Decide per table, and document the refresh schedule and the owner who accepts stale data.
How DeployOpen scopes and delivers ClickHouse and Delta Lake integrations
DeployOpen plans, builds, tests and documents ClickHouse and Delta Lake integrations inside your own cloud accounts, data centers or restricted networks. Engagements are fixed-scope, with the scope, deliverables and timeline agreed in writing before work starts, and ongoing support is available as a retainer.
How we scope
- Inventory the Delta tables, storage locations and cloud providers the cluster must read or write.
- Confirm the ClickHouse version and whether writes to S3, GCS or Azure are required.
- Define which tables stay as live DeltaLake engine queries and which are copied into MergeTree.
How we build
- Configure the DeltaLake engine, storage credentials and URL formats for each storage provider.
- Enable table creation only where the scope requires it, with allow_delta_lake_create_table set deliberately.
- Build MergeTree copies and refresh jobs where query performance on large tables needs them.
How we hand over
- Deliver written runbooks covering table definitions, credentials, URL conventions and refresh schedules.
- Run documented verification queries that compare Delta source results with ClickHouse results.
- Walk your platform and data teams through version checks and the steps to add a new table.
How we support
- Review ClickHouse and Delta Lake release notes before each planned upgrade of either side.
- Retest read and write paths in a pre-production environment with representative data and queries.
- Offer ongoing support, upgrades and continuous testing as a retainer aligned to your scope.
