Why Azure Blob Storage backups for ClickHouse need a restore test from day one
A ClickHouse backup on Azure Blob Storage is only worth its cost once a restore from it has been proven. Teams running self-managed ClickHouse on Azure often assume that replicated ReplicatedMergeTree tables are their safety net. They are not. According to the ClickHouse backup documentation, replication alone does not protect against human error or data corruption, because a bad DROP TABLE, a faulty mutation or a corrupted part is copied to every replica. Off-cluster copies are what let you recover from those events, and ClickHouse BACKUP and RESTORE can write those copies directly to Azure Blob Storage, alongside local disk and S3-compatible storage. The same documentation warns that a backup never test-restored will probably not restore properly when it is needed, so the restore test belongs in the first version of the design, not after the first incident.
This page is for teams that run ClickHouse clusters hosted on Azure and need copies of their data outside those clusters, whether to meet a recovery objective, a retention policy or an assessor's expectation. The first decision is not the container name. It is how often you will run a restore into a spare cluster, who owns that test, and what evidence it leaves behind. Confirm with your own Azure subscription owners where the storage account will sit, which identity will write backups, and whether the restore environment can reach the same container. This page does not cover the choice of backup frequency and retention, incremental chains, the S3 or local disk paths, or the broader backup strategy, which are handled in the sibling sections of this group.
Authentication and destination options for Azure Blob backups
This table compares the ways a ClickHouse cluster can authenticate to Azure Blob Storage for BACKUP and RESTORE, with the fit, exposure and version checks for each. Read each row against your own identity model, and treat the confirmation column as the list to verify in your ClickHouse version before any credential goes into production. The main takeaway is that a stored static secret is the easiest to set up and the easiest to leak, so the choice should follow how far a leaked credential could reach.
| Option | When it fits | What it exposes if leaked | What to confirm in your ClickHouse version |
|---|---|---|---|
| Connection string with account key | Lab clusters and proofs of concept where no Entra ID setup exists yet | The whole storage account, including every container, with read, write and delete rights | Whether your version accepts the connection string in the BACKUP statement, and whether it can be stored in config or named collections instead of SQL text |
| Shared access signature (SAS) | Access limited to one backup container for a fixed period, with rotation planned | Only the permissions and expiry set on the token, but any holder can use it until it expires | Whether your version passes SAS tokens to the Azure backup engine, and that token lifetime exceeds the longest backup or restore run |
| Managed identity or Entra ID | Clusters on Azure VMs or AKS where the platform can attach an identity and no static secret is wanted | No secret to copy from files, but any process on the host inherits the identity's role assignments | Whether your version supports managed identity or Entra ID for Azure backups, and which blob data role must be assigned to the backup scope |
| Account key held in a secret manager | Teams that must keep static keys but want them out of SQL, logs and config files | The same full account access as a direct key, reachable by anyone who can read the secret store entry | How your deployment injects the secret into the server, and whether the key appears in system.query_log or backup status output |
| Container layout per cluster and environment | Multiple clusters or environments sharing one storage account | A credential scoped to a container exposes every backup inside it, so separation limits blast radius | Whether separate containers or prefixes can each carry their own credential, and how retention rules apply per container |
| Private network path to the storage account | Restricted networks where backups must not cross the public internet | Reduces who can reach the endpoint, but does not replace credential control | Which endpoint form your version uses for Azure, and that DNS resolves the private endpoint from every backup node |
How to lay out containers and paths for ClickHouse backups
Give each cluster and environment its own container or clearly separated prefix before the first BACKUP runs, because the path is the only thing that tells a restore operator which cluster, environment and point in time a backup belongs to. A layout such as a container per environment, with a prefix per cluster and a dated folder per run, keeps production, staging and spare restore clusters from writing into each other's history. Use a path convention that encodes the backup type, for example a full or incremental segment plus a timestamp, so an incremental backup is never mistaken for a standalone copy. Incremental backups depend on their base, so the restore runbook should name the base path explicitly. Confirm the exact BACKUP ... TO AzureBlobStorage(...) syntax, argument order and any path limits against the backup documentation for the ClickHouse version you run, and test the convention on a non-production cluster first.
Keep retention and lifecycle rules in Azure, not in ClickHouse. ClickHouse writes backups but does not expire them on your schedule, so blob lifecycle management rules on the container or prefix should delete or tier old backups according to the retention you have agreed. Write those rules down with the backup frequency, so that a full backup is never removed while dependent incremental backups still need it. Use immutability or soft-delete settings where your policy requires protection against an operator or a compromised cluster deleting history, and confirm which of these features your subscription and region support before you rely on them. Production backups should live in a separate storage account, and preferably a separate subscription, from the cluster that produces them, with its own access policy. A credential that can drop the database should not also be able to erase its backups. Ask your Azure administrator to confirm the role assignments and key or identity model for that account before the design is approved.
Setting up BACKUP and RESTORE to Azure Blob Storage
This sequence takes a self-managed ClickHouse cluster from an empty Azure Blob Storage container to a verified restore on a spare cluster. Each step ends with a check, so the team confirms storage, credentials and the backup itself before any production schedule is set. The output is a tested full backup and a recorded comparison of restored data.
Create the storage account and container
The platform or cloud team creates a storage account and a dedicated private container for ClickHouse backups, kept apart from any container used for application data. Choose the region, redundancy and network rules with your own policies, and confirm that public blob access is disabled. Record the account name, container name and the path prefix you will use for test backups. Done when the container exists, is private, and can be listed from an administrator's workstation.
Grant least-privilege access to the container
The identity team creates the credential ClickHouse will use and scopes it to the backup container only. Confirm which Azure authentication method your ClickHouse version supports for BACKUP and RESTORE, then limit permissions to the operations the backup and restore need. Keep the backup writer separate from any reader used on the spare cluster where possible. Done when the credential can write and list under the test prefix and is refused on any other container in the same account.
Store credentials outside SQL history
The database administrator places the connection details in server-side configuration or a named collection, not in a query typed into a client. Restrict who can read that configuration, and set file permissions on the server so the secret is not world-readable. Check how your version logs statements, and confirm that secrets are masked in query logs before anyone runs a test. Done when the credential is referenced by name, a shell history search and a query log review show no plain secret, and only the administrator role can view the configuration.
Confirm network path from every node
The platform engineer tests that each ClickHouse node can reach the Azure Blob Storage endpoint over the route your network design allows, including any private endpoint or firewall rule. Run a small write and read from each node using the same credential the backup will use. Failures here are usually DNS, proxy or firewall issues, so fix them before the backup starts. Done when every node writes and reads a small test object, and the results are recorded with the node names and timestamps.
Run the first full backup to a test path
The database administrator runs a full BACKUP of one representative database or table set to a test prefix in the container, using the named credential rather than inline secrets. Running the backup asynchronously lets you track progress without holding a session open. Note the backup name that ClickHouse returns. Done when the backup command is accepted, and system.backups shows the entry moving to a created status for that backup name rather than a failed one.
Inspect the backup files in the container
The platform engineer lists the blobs under the backup path and confirms that metadata files and data parts appear for each table in the backup. Compare the object count and the total size against the source tables so you can see whether the expected data was written. If system.backups reports an error, read the error field before retrying, because a retry without a cause often fails the same way. Done when the listing matches the tables you backed up and the error field is empty for that backup.
Restore into a spare cluster
The database administrator restores the same backup into a spare cluster that has no production traffic, using a credential that can read the backup path. Run the RESTORE asynchronously and track it in system.backups on the spare cluster. Keep the spare cluster's version and configuration close to production so the test is meaningful. Done when the restore reaches a restored status in system.backups and the restored tables are queryable.
Compare row counts and record the evidence
The data owner compares row counts for each restored table with the source at the same point in time, using the backup's recorded time as the reference. Where the data is still changing, compare only tables that are frozen during the test or account for the ingestion window in the comparison. Save the queries, outputs, backup name, versions and the names of the people who ran the test. Done when every table matches or each difference has a documented explanation and the evidence is filed for the next audit or review.
Choosing full, incremental, table or database backups on Azure
A full database backup writes every table in a database to the Azure Blob Storage container in one self-contained set. Each full backup can be restored on its own, without reading any earlier backup, which makes it the simplest option to reason about during an incident. The cost is volume: every run copies the whole database again, so storage use and transfer time grow with the dataset. Full backups suit smaller databases, reference data and clusters where the nightly window is comfortable. They also give you a clean baseline that other backup types can build on.
- Self-contained restores: a single full backup carries everything needed to recreate the database.
- Storage growth: each run is a complete copy, so retention multiplies the footprint.
- Run duration: the job must finish inside your backup window, and large databases may not fit.
- Scope control: use the
DATABASEclause in theBACKUPstatement to select the databases you need. - Retention pairing: keep several full sets so an older restore point survives if the newest one is damaged.
Before committing to this pattern, confirm with your own measurements how long a full run takes at your data volume and whether the Azure egress and storage costs fit your budget.
An incremental backup stores only the data that changed since a base backup, referenced through the base_backup setting. It reduces storage use and shortens the run for large, slowly changing datasets. The trade-off is dependency: an incremental backup is not restorable by itself. Restoring it requires the base backup and every intermediate incremental in the chain, so losing or corrupting any link breaks the restores that depend on it. Each restore also reads from several locations, which can lengthen recovery time.
- Base dependency: the base backup must remain in the same container and path for the chain to restore.
- Chain length: longer chains increase restore time and the number of objects that must be intact.
- Periodic full backups: schedule a new full backup at a fixed interval to cap how many links depend on any one base.
- Retention rules: delete a chain only as a whole, never a base while later increments still reference it.
- Failure exposure: a damaged base affects every incremental built on it, so test restores across the whole chain.
Confirm the exact dependency behavior and the supported base_backup syntax for your ClickHouse version in the backup documentation before you script the schedule.
Backing up individual tables or partitions limits each job to the data that matters most or is hardest to rebuild. This is useful for very large event tables, for tables that need a shorter recovery point, or when only one table was damaged. A table-level backup can be restored into a different database name, which helps when you want to inspect data before replacing production. The cost is coverage: every table, dependent object and access setting must be listed explicitly, and a missed table is silently absent from the backup set.
- Targeted restores: recover one table or partition without touching the rest of the database.
- Inventory discipline: maintain a generated list of tables so new tables are not left out.
- Partition handling: partition-scoped backups suit time-partitioned data where older partitions rarely change.
- Mixed schedules: back up large tables more often while smaller reference tables run on a slower cadence.
- Coverage checks: compare the backup manifest against the live schema after each run.
Ask your team to confirm which tables must be covered and how quickly each one must be recoverable, then document that inventory alongside the backup job.
Most production plans combine these scopes instead of choosing one. A common pattern is a periodic full database backup as the anchor, incremental backups between anchors for large tables, and table-level jobs for the data with the tightest recovery targets. The anchor schedule sets the maximum length of any chain, which bounds restore complexity. The design works only when each job's dependencies are written down and the restore order is rehearsed. Combining scopes also multiplies the number of jobs to monitor, so naming and alerting conventions matter as much as the scope choices.
- Anchor cadence: the full backup interval defines how many increments a restore may need.
- Restore order: document which base, increment and table backups must be applied, and in what sequence.
- Naming conventions: use consistent backup names that show scope, date and parent backup.
- Monitoring: track job success, duration and size per scope, and alert on a missed anchor.
- Retention alignment: keep each chain intact until the newest restore point that depends on it expires.
Before you finalize the mix, ask your backup owner to write the restore sequence for the most important table and test it on a spare cluster.
The scope choice follows from two targets: how much data loss is tolerable and how long a restore may take. Frequent incremental runs and table-level jobs reduce potential data loss, while full backups keep restores simple and fast to start. Your recovery targets, the size of each table, and the rate at which data changes should drive the decision, not a default pattern copied from another deployment. Replication does not replace these choices, because it copies human errors and corruption to every replica. Record the targets and the chosen scope so auditors and operators can trace why each job exists.
- Data loss tolerance: shorter intervals between runs reduce how much recent data a restore may lose.
- Restore time tolerance: long chains and large full restores both extend the time to bring data back.
- Change rate: tables that change little suit incremental runs, while heavily rewritten tables may not benefit.
- Table size: measure the largest tables first, since they determine run duration and storage growth.
- Replication limits: replicas protect against node loss, not against deletes or corruption that replicate.
Ask your team to confirm the recovery targets in writing, then test whether the chosen scope meets them with a timed restore on a spare cluster.
Securing backup credentials and the Blob container
The backup identity and the Blob container are the two places where a leaked or misused credential can destroy both live data and its recovery copies. The cards below cover how to rotate and scope the credential, lock down the container, decide on immutability, encrypt the output and keep statements out of logs.
Rotate the storage credential on a schedule
ClickHouse BACKUP statements carry the storage credential, so a long-lived account key or SAS token in configuration becomes a standing risk. Decide on a rotation interval with your security team, then confirm that a rotation can be applied to the server configuration without a cluster outage. After each rotation, run a small test backup to confirm the new credential works before the old one is revoked. Ask your Azure administrator which rotation options your subscription supports for storage keys and SAS tokens.
Scope access to the backup identity only
Grant the identity used by ClickHouse write and read access only to the backup container, not to the whole storage account. Separate the identity that writes backups from the identity used during restore tests where your policy requires it, so a test environment cannot overwrite production backups. Review role assignments after each cluster change. Confirm with your Azure administrator whether the chosen authentication method allows this level of scoping.
Block public access to the container
Backup containers should not allow anonymous reads and should not be reachable from the public internet. Restrict network access to the storage account with private endpoints or an allow list covering the ClickHouse nodes, and disable anonymous container access. Test the restriction from a machine outside the allowed network to confirm that it fails. A public backup container exposes full table data, so treat this check as a release gate for the backup pipeline.
Decide on soft delete and immutability
Backups are often the target of ransomware and insider deletion, so ask whether your policy requires soft delete, versioning or immutable storage for the backup container. Soft delete gives a window to recover accidentally removed backup files, while immutability blocks changes for a fixed period. Note that incremental backups depend on earlier backups, so retention settings must keep the full base in place. Confirm the available options and their cost implications with your Azure administrator and your compliance lead.
Encrypt backups at rest and in transit
Azure Storage encrypts data at rest by default, but you should confirm whether your policy requires customer-managed keys and who controls key rotation and revocation. Require HTTPS for the connection to Blob Storage and check that the ClickHouse configuration does not allow plain HTTP endpoints. If backups must be protected with a password, test the ClickHouse password option on a non-production backup and record how the password is stored. Ask your security team where encryption key custody sits before the first production backup.
Keep backup statements out of logs
A BACKUP or RESTORE statement can include the storage connection string or key, and query logs record statement text. Review which ClickHouse log tables and text logs capture query text, then restrict read access to them and set masking rules where the server supports them. Check the system tables that track backup progress for the same exposure. Include a log review in each restore test so that a credential that reached a log is found and rotated.
Proving Azure backups restore with regular restore tests
A backup to Azure Blob Storage only protects you if it restores, and the ClickHouse backup documentation is direct about this: a backup that has never been test-restored will probably not restore properly when you need it. Treat the restore test as a recurring job with its own owner, not a one-off check at go-live. Run the restore into a spare cluster, never onto the production nodes, and point it at the same Azure container and backup path that the scheduled job writes to, so the test exercises the real artifacts rather than a copy you prepared by hand.
Define what the test compares before the first run. Row counts per table are the minimum, and they should be captured from the source at backup time and recorded alongside the backup name, since the source keeps changing. Add checksum-style comparisons on a few representative partitions, and run a fixed set of sample queries whose results you have already agreed with the data owners, such as a daily aggregate and a recent-window lookup. Include the system.backups view in the evidence so each restore shows its status, duration and any error text. Confirm with your own environment which restore settings you need for your table layout and whether your team wants partial or table-level restores in the rotation.
Record every run as an artifact: the backup name, the Azure container and prefix, the ClickHouse version on both sides, the commands used, the comparison results and the person who reviewed them. Store these records somewhere that outlives the test cluster, and keep failed runs as well as passed ones, because an auditor will ask how failures were handled. Schedule the test often enough that a broken backup chain or an expired credential is found while a healthy full backup still exists, and alert on a failed restore the same way you alert on a failed backup. Decide with your team who can authorize a test run against Azure credentials, and confirm that the spare cluster is torn down or access-restricted after each run.
Questions teams ask before backing up ClickHouse to Azure
What work and cost drivers shape a ClickHouse backup project on Azure?
The main cost drivers are the size and churn of the data, the number of clusters and tables in scope, the retention period, and how many environments need restore tests. Some items are one-time: designing the container layout, creating the Azure identities and storage access, wiring the BACKUP and RESTORE jobs, and building the first restore test. Others recur: Azure Blob Storage capacity and transactions, the scheduled full and incremental backup runs, monitoring of job success, and periodic restore tests on a spare cluster. Storage pricing is set by Azure, so ask your team or a partner to model it against your actual data volume and retention rules.
How long does setup and a first restore test take?
Duration depends mainly on data volume, the number of clusters, and how much of the restore path is already tested in your environment. ClickHouse documents that a backup which has never been test-restored will probably not restore properly when needed, so the first restore test is part of the project, not an optional extra. Large datasets take longer to back up and restore, and the time to restore a full cluster is what you should measure against your recovery objectives. Ask for a timeline that separates setup, first backup, first restore test on a spare cluster, and handover.
What must our team provide before the work starts?
You need to provide:
- Azure subscription access with rights to create or use storage accounts, containers and identities for the backup target
- A network design that lets ClickHouse nodes reach the Azure Blob endpoint, including private endpoints or firewall rules where your policy requires them
- Named data owners who can confirm that restored tables, row counts and checksums match what the business expects
- A spare cluster or restore environment where restore tests can run without touching production
Without the data owners, a restore may complete technically but nobody can say whether it is correct.
Can our in-house team set up ClickHouse backups to Azure by itself?
Yes, a team with ClickHouse administration experience and Azure storage skills can build this internally, because ClickHouse BACKUP and RESTORE support Azure Blob Storage natively. The harder parts are usually outside the SQL: credential handling, container layout and retention rules, scheduling, alerting on failed jobs, and proving restores on a regular basis. Check whether your team has run a restore into a spare cluster, since that is where gaps tend to appear. If the team lacks recent ClickHouse operations time, a fixed-scope partner engagement can cover the build and the first restore test while your staff take over operations.
Which Azure storage and credential choices affect the design?
Engineers should decide early how ClickHouse authenticates to Azure Blob Storage, how the connection details are stored, and whether backup data is protected by encryption and access policies that match your security standard. Keep credentials out of plain SQL history and configuration files where possible, and restrict the identities to the containers they need. Confirm the exact connection and authentication options against the ClickHouse backup documentation for your release, because these details change between versions. Also decide container layout per cluster and environment so that restores and access reviews stay simple.
What should a quote for this work include?
A quote should follow from a written scope and list the deliverables explicitly: the container and identity design, the BACKUP and RESTORE job configuration, scheduling and alerting, documentation, the first restore test with recorded evidence, and any handover training. It should state which clusters, databases and tables are in scope, what is excluded (for example users, roles and configuration files outside the database), the assumptions about your environment, and the client responsibilities listed above. Ask whether ongoing restore tests and upgrade-related backup checks are included or offered as a separate retainer.
Do backups to Azure satisfy our audit or compliance requirements on their own?
No. Backups to Azure are one control within a wider set. ClickHouse documents that replication alone does not protect against human error or corruption, and backups still need retention rules, access control, encryption and evidence of regular restore tests. DeployOpen prepares systems, controls and evidence for compliance assessments, but it does not issue audit reports or certifications. Independent auditors and certification bodies make those determinations, so confirm your specific obligations with them before the design is final.
How DeployOpen scopes and delivers ClickHouse backups on Azure
DeployOpen approaches Backing up ClickHouse to Azure Blob Storage as a planned platform build inside your own Azure subscriptions. We scope the backup design in writing, build and test it against your cluster, and hand over runbooks and restore evidence your team can operate.
How we scope
- Inventory the ClickHouse clusters, databases and tables that need Azure Blob Storage backups and their recovery expectations.
- Confirm the Azure subscription, storage account, container layout and network path the backups will use.
- Agree in writing which BACKUP and RESTORE methods, schedules and restore tests are in the fixed scope.
How we build
- Configure Azure Blob Storage backup destinations and credential handling for each ClickHouse cluster in your environment.
- Run full and incremental BACKUP jobs against a non-production cluster before enabling them in production.
- Test restores to a spare cluster and record row counts and checksum comparisons as evidence.
How we hand over
- Document the container layout, naming conventions, credential rotation steps and backup job definitions.
- Deliver restore runbooks that your operators can follow during an incident without DeployOpen present.
- Walk your platform and security teams through monitoring of backup status and failed restore test results.
How we support
- Ongoing support, ClickHouse version upgrades and recurring restore testing are available as retainers.
- Review backup jobs after ClickHouse upgrades, since release changelogs can include backward-incompatible changes.
- Confirm with your Azure team which storage redundancy, retention and access controls apply to your backup containers.
