Apache Airflow and ClickHouse integration

How Apache Airflow drives ClickHouse without doing the heavy work

If your team schedules pipelines in Apache Airflow and loads analytical data into ClickHouse, the first design decision is where the work happens. Airflow should decide when and in what order steps run, while ClickHouse should do the scanning, joining, aggregating and merging. The community provider package apache-airflow-providers-clickhousedb sits alongside common-sql in the Airflow providers list, so you drive ClickHouse through provider hooks and SQL operators rather than through a custom connector. This page covers that provider method. It does not cover building your own client library.

The ClickHouse server is open source under the Apache License 2.0, so the database itself can run in infrastructure you control. ClickHouse Inc. separately sells ClickHouse Cloud as a managed service. Choosing self-hosted ClickHouse does not depend on the provider, and the Airflow side works the same whichever way the cluster is operated. Confirm with the provider's packages reference which hook and operator classes the release you plan to pin exposes, because the names and supported behavior are set by that package, not by this page.

Airflow workers have limited memory and CPU and should not be the place where large result sets are pulled into Python. Keep each task to a SQL statement that ClickHouse executes, plus small checks that return counts or flags. Avoid returning big query results through XCom. Also keep top-level DAG code free of database calls, since the Airflow best practices say parse-time database access, heavy computation and network calls should not run when the DAG file is loaded. Write a short decision list for each pipeline: which transformations are SQL inside ClickHouse, which steps are only waiting on an upstream system, and which steps genuinely need Python on a worker.

What you need in place before connecting Airflow to ClickHouse

Most integration delays come from decisions made before the first DAG runs: which Airflow line you operate, which ClickHouse release you pin, how traffic reaches the cluster, and which accounts Airflow uses. Work through these cards with your platform, data and security owners before you install the provider.

Airflow 3 and its metadata database

Airflow 2 reached end of life on April 22, 2026, so a new connection should target Airflow 3, whose current patch release is 3.3.2. Confirm the Python version (3.10 to 3.14), a supported metadata database such as PostgreSQL 14 to 18 or MySQL, and the RAM for scheduler and workers. SQLite is for testing only, and MariaDB is not supported as the metadata database.

Provider release and compatibility

The ClickHouse integration ships as the community provider package apache-airflow-providers-clickhousedb, which sits alongside the common SQL provider. Pin the provider release against your exact Airflow version in the provider documentation before installing, and install through Airflow constraint files so dependencies do not drift. Record the chosen versions in your change record.

ClickHouse release line

Choose between the stable channel and an LTS line for production. Stable releases ship roughly monthly, and LTS releases ship twice a year with one year of support. Only versions 26.9, 26.8, 26.7 and 26.3 receive security updates today, so confirm the line you pick is on that list. Plan a pre-production cluster with representative data and read the changelog before every upgrade.

Network paths and TLS

Airflow workers, the triggerer and any DAG-processing hosts need a route to the ClickHouse HTTP or native port, and the route should be explicit in firewall rules rather than broad subnet access. Terminate TLS with certificates your internal CA issues, and confirm the provider connection settings accept your CA chain. Decide early whether ClickHouse Keeper nodes and backup targets sit on the same network segment.

Service accounts and grants

Create a dedicated ClickHouse user per pipeline domain instead of sharing the default account. Grant only the databases, tables and statements each DAG needs, such as INSERT on landing tables and SELECT on check targets, and avoid DDL rights for routine loads. Store the password in an Airflow connection backed by your secrets store, not in DAG code. Use the access control documentation to map roles to grants.

Authentication method and identity

Decide whether Airflow authenticates with local ClickHouse users or with an external source. ClickHouse supports LDAP, Kerberos, X.509 certificates, an HTTP authenticator and JWT as external authenticators, and each option changes how service identities are issued and rotated. Ask your identity team which option they can operate, and confirm the provider supports it before you commit to a design.

Configuring the provider, connection and first load in order

This sequence takes a self-hosted Airflow environment from an installed ClickHouse provider to a repeatable first load, with each step closed by a check that the platform team can rerun. It keeps credentials out of DAG code and confirms that a rerun does not duplicate data.

Confirm the target Airflow and ClickHouse versions
The platform team records the Airflow 3 patch release, the Python version on the scheduler and workers, and the ClickHouse release line that the cluster runs, then checks each against the vendor support tables before any package is installed. Confirm the ClickHouse version against the ClickHouse security policy, since only listed versions receive security updates. The step is done when the versions are written into the change record and the team agrees on which ClickHouse release line the provider will target.
Install the ClickHouse provider with constraints
The platform engineer installs the apache-airflow-providers-clickhousedb package into the same image or virtual environment as the Airflow core, using the Airflow constraints file for the exact Airflow version so that shared dependencies such as common-sql resolve to tested versions. Installing from an unconstrained requirements file can silently change the Airflow core or its dependencies. The step is done when the package list is pinned in the build and a fresh scheduler and worker both import the provider without errors.
Create the connection outside DAG code
An administrator creates the ClickHouse connection through the Airflow secrets backend or the connection store, with the host, port, database, and a dedicated ClickHouse user whose grants are limited to the target database. The password lives in the secrets store, and DAG files refer only to the connection ID. The step is done when the connection is visible to the scheduler, the password is not present in any DAG file or task log, and the user's grants are reviewed against the access model.
Test connectivity from the worker
The engineer runs a test from the same worker type that will execute the load, using a single read-only query such as selecting the server version or a row count from a small staging table. Testing from a laptop or the web interface alone can miss firewall rules and DNS differences that apply to workers. The step is done when the query returns the expected result from a worker process and the TLS settings in use match the policy the security team approved.
Write the DAG with a SQL step and a check
The data engineer writes a DAG that runs a SQL step through the provider's SQL operator, with the statement stored in a versioned SQL file, and a follow-on check task that compares a row count or a null count against a threshold agreed with the data owner. Top-level DAG code should only declare tasks and read no data, so the query runs inside ClickHouse rather than on the worker. The step is done when the DAG parses cleanly in the test environment and a manual run shows the SQL step and check both succeeding.
Batch the upstream extract into staging
The engineer moves the upstream records in batches into a staging table, sized to the data volume the source system can export without straining it, and avoids issuing one insert per row. Batch boundaries should be based on a timestamp or key range that the source can reproduce, so the same window can be extracted again. The step is done when a batch lands in staging with a row count that matches the source extract for the same window.
Compare row counts after the first load
After the batch moves from staging into the target table, the team runs a comparison of row counts and a checksum or aggregate on a key column between the source window and the ClickHouse target. Mismatches stop the DAG and alert the owner before downstream steps start. The step is done when the counts and aggregates match for the first window and the comparison result is stored in the run logs for audit.
Rerun the window to prove idempotency
The engineer reruns the same DAG run for the same window and confirms that the target table does not gain duplicate rows, either because the load deletes or replaces the partition first or because the target engine deduplicates on the chosen key. Teams should confirm the deduplication behavior with ClickHouse documentation for the table engine they chose, since merge timing affects what a query sees. The step is done when the second run leaves row counts and aggregates unchanged and the result is recorded before the DAG is scheduled.

Choosing between hooks, SQL operators and staged batches

Direct SQL through SQL operators suits work that ClickHouse can do by itself: creating or altering tables, running INSERT ... SELECT from tables already in the cluster, partition maintenance and data quality queries. The SQL stays in version control with the DAG, the heavy execution happens on the server, and Airflow only sends the statement and records the outcome. Idempotency depends entirely on how the statement is written, so that design needs to be deliberate.

Volume: Set-based statements move the work to the cluster, so row counts are handled by ClickHouse's own execution engine rather than by a worker.
Idempotency: A plain INSERT run twice duplicates rows. Use partition-scoped rebuilds, and if you rely on deduplicating table engines, confirm in the ClickHouse documentation for your version when deduplication takes effect.
Worker memory: Workers hold only the statement text and a status, so memory use stays flat from run to run.
Transactions: Confirm which statements can be grouped atomically in your version, and do not assume that a multi-statement task is all or nothing.
Limits: Logic that needs row-level handling in Python, calls to external APIs or file parsing does not fit this pattern.

Check that every statement can be rerun safely and that the SQL is reviewed like application code before scheduling.

Why transformations belong inside ClickHouse, not on Airflow workers

The core rule is that Airflow should orchestrate ClickHouse work, not perform it. The Airflow best practices say top-level DAG code should not run database access, heavy computation or network calls, because the scheduler parses DAG files repeatedly. A DAG that pulls rows from ClickHouse, reshapes them in Python and writes them back would run that work on worker memory and network, where it is hard to size, retry or observe. Instead, each task should issue a set-based SQL statement that ClickHouse executes server side, so the cluster does the heavy reads and writes and Airflow only waits for and records the outcome.

In practice, the transformation layer belongs in ClickHouse: INSERT ... SELECT statements that move and reshape data between tables, materialized views that process rows as they arrive, and partition-aware statements that rebuild only the partitions an upstream batch touched. Airflow keeps the parts it does well: dependency order, sequencing across systems, data quality checks, retries with bounded attempts, and alerting. Confirm with your team which steps are idempotent for a given partition before enabling retries, since a retried INSERT into a partition that was already loaded can duplicate rows unless the design prevents it.

Two details in DAG code matter here. Variables such as connection targets, database names or table lists must not be read at parse time, because every parse would query the metadata database; read them inside the task instead. Also keep the SQL itself in versioned files or templated tasks so reviewers can see exactly what runs in ClickHouse. Ask your team to confirm how many statements each task submits and whether any step still pulls large result sets back to a worker, since those are the places where work drifts back onto Airflow.

Mapping Airflow connections, ClickHouse users and data objects

This table maps each Airflow connection field to its ClickHouse counterpart, then sets out the ClickHouse users, grants and data objects each DAG team should receive. Confirm exact field names against the ClickHouse provider documentation for the version you install, since names can change between provider releases. The main takeaway is that every DAG team should have its own ClickHouse identity with write rights limited to its staging and target objects.

Item	What to set	Why it matters	What to confirm
Host and port	ClickHouse endpoint reachable from Airflow workers or the task execution path	Workers must reach the HTTP or native interface the provider uses, which is often a different port from the one your BI tools use	Port per protocol in your cluster and whether a load balancer sits in front
Database	Default database on the Airflow connection, set to a staging or reporting database	A default database that is too broad invites accidental writes to shared tables	Fully qualified table names in DAG SQL so the default does not matter
User and password	One ClickHouse user per DAG team, stored in an Airflow secrets backend or connection store	Shared users make it impossible to trace which team ran a bad insert or DROP	Whether credentials stay out of DAG code and environment variables
TLS settings	Secure connection enabled, CA certificate supplied, and certificate verification on	Insert traffic often carries regulated data across networks you do not fully control	Exact TLS field names and certificate paths in the installed provider
Team role	A ClickHouse role per DAG team holding only the grants that team needs	Roles let you change access once for every user in a team	Role syntax and grant options for your ClickHouse version in the access control documentation
Staging database	Write access limited to a staging database used for batch loads and swaps	Staging keeps partial loads away from consumers until checks pass	Whether your team can use atomic table swaps or partition replacement for each target
Target tables	INSERT or ALTER rights only on the named tables or partitions the team owns	Narrow object grants limit the blast radius of a faulty DAG	Which tables are shared, and who approves changes to them
Read-only checks	A separate read-only grant for data quality checks run against targets	Checks should not share write rights with loading tasks	Whether checks run under the same identity or a second read-only identity

Securing the ClickHouse connection and its credentials

The Airflow to ClickHouse connection touches credentials, database grants, network paths and backups, and each one needs an owner. The cards below cover the decisions that sit between a DAG author and a production ClickHouse cluster, and the checks to run before the first scheduled load.

Keep passwords out of DAG code

Store the ClickHouse username and password in an Airflow connection backed by a secrets backend that your organization already operates, rather than in DAG files, environment variables or task parameters. DAG files are readable by everyone who can merge to the repository, and they often end up in logs and tracebacks. Check who can read the secrets backend, how rotation is performed, and whether connection details appear in the Airflow UI for users who should not see them.

Create a dedicated least-privilege user

Give the Airflow pipelines their own ClickHouse user instead of reusing the default or an administrator account. Grant only the databases, tables and statements each pipeline needs, such as INSERT on staging tables and SELECT on the reporting layer, using the access control model. Separate the account that loads data from the one that runs transformations, so a faulty DAG cannot drop tables it was never meant to touch. Confirm grants with a review before each new DAG goes live.

Encrypt connections with X.509 certificates

Decide whether the Airflow workers connect to ClickHouse over TLS with X.509 certificates, and who issues and renews them. Certificates on the client side, combined with server certificate validation, keep credentials and query results off the network in clear text. Plan where the CA bundle lives, how it reaches each worker image, and how a certificate expiry will be detected before a scheduled load fails overnight.

Evaluate external authenticators

ClickHouse can delegate authentication to LDAP, Kerberos, an HTTP authenticator or JWT, which can centralize access for human and service accounts. Each option changes who must be available for a pipeline run and how service identities are issued and revoked. Confirm which mechanism your identity team supports, whether service accounts are allowed under it, and how the Airflow connection will supply the token or ticket. Test the failure path, since an identity outage will stop every load.

Restrict which workers reach ClickHouse

Limit the network paths from Airflow to ClickHouse to the worker pools and components that actually run ClickHouse tasks. Use firewall rules, security groups or network policies so that the scheduler, the API server and unrelated worker queues cannot open connections to the cluster. Review this boundary when you change executors, add a Kubernetes queue or run tasks in a new environment, because each new path widens the exposure.

Plan for replication and restore tests

Replication keeps data available when a node fails, but it does not protect against a bad DELETE or a corrupted table, because the damage is copied to every replica. Schedule BACKUP and RESTORE jobs to storage outside the cluster, and keep their credentials under the same control as the pipeline secrets. ClickHouse documentation warns that a backup never test-restored will probably not restore when needed, so run restores to a spare cluster on a regular cadence and record the results.

Testing loads, checks and recovery before production

Test every ClickHouse load in a pre-production environment loaded with representative data and the queries your reports actually run. ClickHouse's production guidance recommends this step, and it is the only place where schema choices, partition keys and merge behavior can be judged against realistic volumes. Keep the Airflow DAGs, connections and the ClickHouse provider version identical to production so that a failure reflects the pipeline rather than the environment. Confirm with your own data team which datasets and query patterns count as representative, since a sample that is too small will hide problems that appear only at full volume.

Treat idempotency as a requirement you test, not an assumption. Run each load task twice for the same logical date, then run a backfill over a past window, and compare results after each run. Inserts into MergeTree-family tables do not deduplicate on their own, so the design should write per partition or per batch identifier, and the test should confirm that a rerun replaces rather than duplicates rows. Add row-count checks and reconciliation queries that compare source extract totals with the rows visible in ClickHouse, failing the task when the counts disagree. Keep these checks as SQL steps in the DAG so that a failed check blocks downstream transformations.

Recovery needs the same discipline. ClickHouse documents that replication does not protect against human error or corruption, so schedule BACKUP and RESTORE jobs and then restore them onto a spare cluster on a regular basis. The backup documentation warns that a backup never test-restored will probably not restore properly when needed. Before go-live, confirm the restore time, the storage target and the access the restore job needs, and record the result. Airflow DAG tests should run in CI on every change, and ClickHouse recommends reading the changelog before any upgrade because minor backward-incompatible changes can occur, so put a changelog review and a pre-production load test into the upgrade checklist.

Failures that show up when Airflow and ClickHouse first meet

This table lists the failures teams most often hit when Airflow first writes to and reads from ClickHouse, with the likely cause, the fix and a check to run. Read each row as a pre-launch test: if the check fails, fix the cause before scheduling production DAGs. The main takeaway is that most failures come from network paths, retry behavior and data movement design rather than from ClickHouse itself.

Failure	Likely cause and fix	Check to run
Connection timeouts from workers	Workers sit in a network zone without a route to the ClickHouse HTTP or native port. Open the required ports between worker subnets and ClickHouse nodes, or route through an approved load balancer.	From a worker pod or host, run a TCP connect test to the exact host and port in the Airflow connection.
TLS or certificate mismatch	The connection uses a certificate whose hostname or certificate authority does not match what the worker trusts. Align the connection host with the certificate name and add the internal CA to the worker image.	Open a TLS session with the same host, port and CA bundle the connection uses, and confirm the certificate subject matches.
Duplicate rows after task retries	A retried insert task re-sends a batch that already committed. Use deterministic batch keys, write to staging partitions and swap or deduplicate on a stable key, and avoid blind appends in retried tasks.	Rerun one insert task on purpose in a test environment, then count rows per batch key and confirm the count is unchanged.
Memory pressure from full result sets	A SQL hook fetches an entire query result into worker memory before writing it elsewhere. Keep transformations as INSERT SELECT inside ClickHouse, and page or stream any data that must leave the cluster.	Run the largest expected query in staging while watching worker memory, and confirm the task does not fetch rows it only needs to pass as a reference.
Insert failures after a ClickHouse upgrade	A column type, default or table engine changed in the new release, or a backward-incompatible change in the changelog affects the insert. Review the changelog for the target version, run the insert in a pre-production cluster with representative data, then upgrade.	Compare DESCRIBE TABLE output before and after the upgrade in staging, and run the load DAG against that cluster before production.
Parse-time database calls slow the scheduler	A DAG file opens a ClickHouse connection or queries table metadata at import time, so every scheduler parse repeats the call. Move client creation and queries into task callables and avoid reading Variables at parse time.	Use the DAG import timing in your Airflow logs or run the DAG file directly with Python and confirm it makes no network calls.

Questions teams ask before connecting Airflow to ClickHouse

Is the ClickHouse provider for Airflow official or community maintained?

The ClickHouse provider for Airflow is distributed as the apache-airflow-providers-clickhousedb package, which the Airflow providers reference lists alongside the common SQL provider. Confirm the maintainer, release cadence and supported Airflow versions on the package page before you commit, because the package is not the same thing as ClickHouse Inc. support. For a self-hosted stack, support usually means the package maintainers for provider bugs, the Airflow project for core behavior, and your own team or a partner for the surrounding design. Ask any partner to state in writing which of those layers they support and which they only integrate.

How much effort does a ClickHouse integration take, and what drives it?

Effort is driven by the number of sources, the shape of the batches, the checks you need and the security review your organization requires. Each upstream system adds its own extraction logic, schema mapping and failure handling. Batch design decides how inserts are sized, retried and deduplicated, which matters more for analytical tables than most teams expect. Checks, such as row counts and freshness tests run as SQL steps, add scope in proportion to the number of tables they cover. No duration is given here because it depends on those inputs, so ask for a quote built from a written list of sources, tables and checks.

What does the work need from our team?

The work usually needs three roles to be available. ClickHouse administrators own the cluster, users, grants and replication, and they decide how the Airflow service account is limited. Airflow owners control the deployment, connections, secrets and scheduler capacity. Data owners define the sources, schemas, freshness targets and what counts as a passing check. Without the data owners, the checks tend to test the wrong things. Confirm early who signs off on schema changes and who can approve access to production ClickHouse.

Can our in-house team build this integration without a partner?

Yes, if your team already runs Airflow in production and has someone who understands ClickHouse table engines, replication and access control. The parts that most often need experience are batch sizing, idempotent loads, keeping transformations inside ClickHouse instead of on Airflow workers, and restore testing. The ClickHouse backup documentation notes that a backup never test-restored will probably not restore properly when needed, so an in-house team should plan restore tests from the start. A partner is most useful when the team needs a fixed scope, a review of the design, or help with a security review in a short window.

How is the work scoped and quoted?

DeployOpen engagements are usually fixed-scope, with scope, deliverables and timelines agreed in writing before work starts. For this integration, a useful scope lists the sources, target databases and tables, the load pattern for each source, the SQL checks, the connection and secrets model, the environments to build, and the handover documents. A quote follows from that written scope, and DeployOpen does not publish a price list. Ask for the quote to separate one-time build work from any recurring support, upgrades or testing retainers.

What changes when ClickHouse or Airflow is upgraded?

Both platforms change on their own schedules, so an upgrade plan should cover each one. ClickHouse publishes stable releases roughly monthly and LTS releases twice a year, and its production guidance recommends reading the changelog before upgrading because minor backward-incompatible changes can occur. Its security policy lists which versions currently receive security updates. Airflow 3 changes how tasks reach metadata, so provider code and DAGs need checking against the upgrade guide before a move. Test each upgrade in a pre-production environment with representative data and queries, and confirm that the provider version you use supports both the new Airflow and ClickHouse versions.

Should heavy transformations run in Airflow or inside ClickHouse?

Run heavy transformations inside ClickHouse and keep Airflow for orchestration, ordering, retries and checks. Airflow best practices say top-level DAG code should not run database access, heavy computation or network calls, and worker memory is limited compared with a database built for analytical queries. In practice, Airflow sends SQL to ClickHouse, waits for it, and reads back small results such as row counts or status values. Ask whether your design keeps large data movement out of worker memory and whether each task has a clear retry behavior.

Does ClickHouse access control need to change for the Airflow service account?

Yes, the Airflow service account should have only the grants that its loads and checks need. ClickHouse access control lets administrators define users, roles and grants, and the access rights documentation is the right starting point for the design. Keep separate accounts for loading, transformation and read-only checks where your risk model asks for it. Credentials belong in Airflow connections backed by a secrets store, not in DAG code. If your organization uses LDAP, Kerberos, X.509 certificates or JWT, confirm which external authenticator fits the account model, because the ClickHouse external authenticators documentation covers each option.

How DeployOpen scopes and delivers the Airflow and ClickHouse work

DeployOpen plans, implements, hardens, documents and supports self-hosted open-source platforms inside your own infrastructure, so the Airflow and ClickHouse integration runs in your cloud accounts, data centers or air-gapped networks. Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.

How we scope

Inventory the source systems, target ClickHouse tables and the Airflow version and executor already in place.
Agree in writing which DAGs, SQL steps, check operators and batch loads are in the fixed scope.
Confirm which ClickHouse release line you will run and the Keeper and backup topology it needs.

How we build

Configure the ClickHouse provider connection with credentials held in your secrets store, not in DAG code.
Build batching and insert patterns that keep transformation SQL running inside ClickHouse, not on Airflow workers.
Test DAG parsing, SQL steps and data checks in a pre-production environment with representative data and queries.

How we hand over

Document DAG structure, connection setup, retry and idempotency rules, and the ownership of each pipeline.
Walk your platform and data teams through runbooks for failed loads, backfills and schema changes.
Deliver restore test results for ClickHouse backups so recovery is verified, not assumed.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer after handover.
Review ClickHouse changelogs and Airflow provider updates before planned upgrades are applied.
Confirm with your team which support boundaries, response expectations and upgrade windows apply to your environment.