Why testing ClickHouse restores is the step teams skip
A backup is unproven until it has been restored. Your BACKUP jobs may report success every night, but that only shows ClickHouse wrote files somewhere. The ClickHouse backup documentation warns that a backup that has never been test-restored will probably not restore properly when you need it. Treat the first successful restore to a spare cluster as the real acceptance test for the backup process, and schedule restores on a recurring basis after that. Before you start, confirm the restore procedure in the ClickHouse backup documentation against the version you run, and check which objects your backup actually includes, since users, roles and settings stored outside the database need separate handling.
Restore testing becomes necessary at specific points, not only when something breaks. It is needed before the first production cluster goes live, when a disaster recovery commitment is written into a contract or internal policy, and when an auditor asks how you know backups are recoverable. Each of these moments creates a claim you must be able to prove with a dated, repeatable restore. Ask who owns the restore runbook, how often a restore is run, and what the recovery time observed in a test looked like compared with what the business expects.
Replication does not close this gap. ReplicatedMergeTree tables keep replicas in sync, and a recovered replica catches up from a healthy one without manual steps. That protects you from node loss, but a DROP TABLE or a bad DELETE runs on every replica, and corrupted data is copied as faithfully as good data. The ClickHouse documentation states that replication alone does not protect against human error or corruption. Only a backup taken before the damage, restored and checked, gives you a way back, which is why the restore test, not the replica count, is the control to plan for.
Choosing between full, partial and table-level restore tests
A full restore rebuilds every database and table from a backup onto a spare cluster. It is the test that matters most for site loss, because it shows that the backup is complete and that a spare environment can be brought up from the backup target plus your configuration. Run it at least for the failure scenario your backup strategy promises to recover from.
- Scope: Covers all databases and tables, and users and roles if they were included in the backup. Confirm which files under
config.dandusers.dsit outside the backup and must be restored separately. - Restore settings: Start from an empty target. Confirm how the restore behaves when the target already holds tables, because options such as
allow_non_empty_tableschange what is permitted in your ClickHouse version. - Effort drivers: Data volume, bandwidth between the cluster and backup storage, the shard and replica layout of the spare, and how many Keeper nodes the spare needs before replicated tables come online.
- Recovery evidence: Time the restore from the first command to the point where queries return correct results, and record that duration against the recovery time objective.
Before you mark the test as passed, confirm that every replicated table is writable, that Keeper coordination is healthy, and that row counts match the source of truth recorded before the backup.
A database-level restore recovers one database, which fits the case of an accidental drop. The usual approach is to restore into a separate or renamed target so you can compare the recovered data with what remains before you reattach anything. This keeps a bad restore from overwriting the objects you still need.
- Scope: Restores the named database and its tables. Check whether dependent objects such as materialized views, dictionaries or tables in other databases are included in the backup and what they point to after restore.
- Target naming: Plan a target name that will not collide with a live database. Confirm the rename options available in your version before you write the restore command.
- Access objects: Users, roles and row policies that grant access to the database are stored separately from its tables. Decide whether the test must also prove that access is restored correctly.
- Effort drivers: The number of tables and parts in the database, whether the database depends on other databases, and how much time your team needs to validate the restored target.
- Recovery evidence: Record which backup was used, the restore duration, and the comparison queries that showed the restored database matches the expected state.
Finish by confirming that the restored database can be queried by the application roles that depend on it, and that the drop cannot repeat through the same permissions path.
Table and partition restores target a narrow failure, such as corruption confined to one table or a bad data load into a few partitions. They are faster and use less storage than a cluster rebuild, but they depend on choosing the correct backup point. The backup documentation is clear that replication alone does not protect against human error or corruption, so a healthy replica may carry the same damage.
- Scope: Restore a single table, or specific partitions where the backup supports it. Confirm the exact clause for your ClickHouse version before scripting the test.
- Backup point: Pick a backup taken before the damage started. If you use incremental backups, confirm the base backup chain is complete, because a missing link blocks the restore.
- Replica behavior: A recovered replica syncs missing data from a healthy replica automatically. Test whether the restored table conflicts with replicas that already hold the damaged data.
- Effort drivers: The size of the affected table or partitions, how many partitions must be compared, and whether the damage was detected quickly enough to keep the backup chain short.
Close the test by comparing row counts and aggregates for the restored table against the last known good values, and by recording which backup point you chose and why.
Single-table spot checks support routine assurance. They restore one table to a scratch database on a schedule, compare it with its source, and produce evidence that restores still work between full tests. The backup documentation recommends regular restore tests on spare clusters, and spot checks are a practical way to run them more often without a full rebuild. They do not replace a full cluster restore.
- Sampling: Rotate through tables so that each critical table is checked within the cycle your strategy sets. Include the largest and most business-critical tables.
- Comparison method: Compare row counts and checksums or aggregates, such as sums over key numeric columns, between the restored copy and the source at the backup time. Account for data written after the backup.
- Restore settings: Restore into a scratch database name so the test cannot affect production tables. Confirm which options your version offers for restoring structure only.
- Effort drivers: Table count, the cost of checksum queries on large tables, and the work needed to store results so that auditors can trace each run.
- Evidence: Keep the backup identifier, the command, the timestamps, the comparison output and the person who reviewed the result.
Review failed checks as incidents, and confirm that the schedule and table list are approved by the owners of the data.
Restore test options and when each fits
This table compares four ways to prove a ClickHouse backup restores, from full rebuilds on a spare cluster to scheduled automated checks. Read each row for what the test shows, what it leaves untested, and the situations where it is the right first choice.
| Approach | What it proves | What it misses | When it fits |
|---|---|---|---|
| Spare cluster full restore | The complete backup set can rebuild a whole cluster, including schema, data and cluster-level objects, on separate hardware. | Cluster-specific settings, network paths and production load, plus any gap between the backup time and the test time. | Before go-live, after backup design changes, and at a set cadence for the most critical data. |
| Table-level restore to a scratch database | Named tables restore correctly into an isolated database and their contents match expectations. | Objects outside the chosen tables, such as users, roles and row policies, and cross-table dependencies that were not included. | Routine checks on high-value tables where a full rebuild is too costly to repeat often. |
| Partition restore with ATTACH PARTITION from frozen parts | Frozen parts copied off the server can be reattached and queried, which tests the path used for partition-level recovery. | Whether the BACKUP and RESTORE workflow works, and whether every partition or table in scope was captured. | When your recovery runbook depends on FREEZE and ATTACH rather than the BACKUP command. |
| Scheduled automated restore with checksum comparison | Restores run repeatedly without manual steps, and restored row counts and checksums match values recorded at backup time. | Gaps in the comparison itself, such as checksums that skip columns, and failure modes that appear only under real incident conditions. | As ongoing evidence for auditors and operators, once a manual restore has been proven at least once. |
How to set up an automated restore test on a spare cluster
This sequence proves that a ClickHouse backup can be restored into an isolated spare cluster, checked against the source schema and data, and recorded as evidence before the spare environment is reset.
Provision an isolated spare cluster
The platform team builds a spare cluster that matches the production ClickHouse version, server configuration, user and role layout, and storage layout as closely as the environment allows. Confirm the version with the server's version query on every node and compare the configuration files in config.d and users.d with production. The spare cluster must not be reachable from production write paths: no production DNS entries, no ingestion pipelines, no Kafka consumers or materialized view sources that point at production, and no replication to or from production hosts. Network rules should allow only the restore operator, the backup storage endpoint and monitoring. The step is done when a connectivity check confirms that production hosts cannot be reached from the spare cluster and the version check matches on every node.
Grant read-only access to the backup location
Create storage credentials for the spare cluster that can read the backup path but cannot write, overwrite or delete backups. For S3-compatible storage, this means a bucket policy or access key limited to read and list actions on the backup prefix. Store the credentials in the spare cluster's secret handling, not in SQL text that appears in query logs. Confirm the scope with the storage administrator, and check that the credentials fail when a test write is attempted. Ask your backup tooling and storage vendor which permissions a restore needs in your setup, because the exact set depends on the storage type and how the backup was written. The step is done when a list of the backup path succeeds and a write attempt is denied.
Confirm the backup is complete before restoring
Before starting a restore, check that the backup you intend to test exists and finished. Query the backup catalog and the backup status for the chosen backup name, or read the backup listing in your storage location, and confirm the backup reached a completed state. For incremental backups, confirm that the base backup it depends on is present and readable, because a missing base breaks the chain. Record the backup name, the time it was taken and the source cluster version in the test log. The step is done when the status is confirmed as complete and the dependency chain is verified.
Run RESTORE with the chosen scope
Start the restore with the scope defined in your test plan: a full database, specific tables, or a single partition. Use the RESTORE statement with the backup location and the settings your test requires, such as whether to restore into a new database name so that test data does not collide with anything already on the spare cluster. Run the restore asynchronously when the data volume is large, and keep the statement and its settings in version control so the same test can be repeated. The step is done when the restore is submitted and the operator has the backup identifier that will be tracked in the next step.
Track restore progress in system.backups
Watch the restore in the system.backups table, which reports the status, start and end times, and any error message for backup and restore operations. Poll until the status shows the restore finished, and treat any failed or partially completed state as a failed test, even if the server accepted the statement. Capture the error text for failed operations and keep it with the test record. Do not start verification queries until the status shows completion. The step is done when the restore status reads as completed with no error and the operation times are recorded.
Compare schema before comparing data
Check structure before content. Compare the restored table definitions with the production definitions for the same scope, including engines, ordering keys, partition keys, TTL clauses and column types, using the schema export from both clusters. A row count on a table with the wrong structure proves little. Record any differences and decide whether they are expected, such as a database name change in the test. The step is done when schema comparison shows no unexplained differences for the restored scope.
Run row count and checksum verification
Run the verification queries defined in your test plan against the restored tables and their production counterparts. Compare row counts per table and per partition, then compare checksums or aggregate fingerprints on key columns, such as a sum over a numeric column or a hash over ordered key values, for the same time range. Production data continues to change after the backup, so compare only the range that was frozen at backup time, or compare against the source data captured at the backup point. Store each query, its result and the timestamp of the source snapshot. The step is done when every table in scope has a recorded count and checksum match, or a documented explanation for any mismatch.
Store results and reset the spare cluster
Save the test evidence in a location separate from the spare cluster: the backup name, source and spare versions, scope, restore settings, system.backups output, schema comparison, verification query results, operator identity and pass or fail outcome. Keep the record in a store that auditors can read, and retain it according to your backup retention policy. Then drop the restored data or tear down the spare cluster, and revoke the read-only credentials. Confirm that the teardown left no restored data reachable from production. Ask your compliance lead which evidence format and retention period your assessors expect. The step is done when the evidence is stored, the restored data is removed, and the access check confirms that the spare cluster can no longer read the backup location.
Verifying row counts and checksums after a restore
A restore has succeeded only when the restored cluster matches what was backed up. The checks below compare the restored data, schema and access controls against a baseline captured when the backup was taken, so each restore test ends with a recorded pass or fail for every check.
Row counts against a recorded baseline
Record the row count for every table at backup time, together with the backup name and the cut-off used. After the restore, compare each table's count against that baseline. Any difference on a table that receives no writes is a failed restore. On tables that keep receiving inserts, expect counts to differ unless you restored to the same cut-off, so the baseline must state the cut-off.
Checksums per partition
Compute an aggregate fingerprint for each partition, such as a hash over ordered column values, on the source before the backup and on the restored table after. Partition-level fingerprints localize a mismatch to a month or day instead of forcing a full table comparison. Use a fixed column order and exclude columns that change on read, such as materialized values recomputed by the engine.
Schema and engine comparison
Compare the restored CREATE TABLE statements, engines, sorting keys, partition keys and TTL clauses with those recorded from the source. A table that restores with the right rows but a different sorting key or missing TTL will fail later in production queries and retention jobs, so treat schema drift as a failed check.
Known business totals
Choose a small set of queries whose answers the business already knows, such as daily order totals for a closed period or event counts reconciled with an upstream system. Run them on the restored cluster and compare results with the figures signed off in the source environment. These catch logical damage that row counts miss, such as duplicated or dropped rows that keep totals plausible.
Replicated table consistency
After restoring into a replicated setup, confirm that each replica of a ReplicatedMergeTree table reports the same row count and part set once replication settles. Check the replication queue for stuck entries. The replication guide notes that a recovered replica syncs missing data from a healthy replica automatically, so a persistent difference between replicas points to a restore or Keeper problem that needs investigation.
Access control objects present
Confirm that the users, roles, row policies, quotas and settings profiles recorded at backup time exist on the restored cluster and grant the same permissions. Data that restores without its row policies can expose rows to users who should not see them. Verify that the default user is still restricted, since a fresh cluster starts with all permissions for that account.
Security and operational considerations for restore tests
A restore test copies production data into a second environment, and that copy needs the same protection as the original. The cards below cover credentials, encryption and retention for restored data, network isolation, personal data handling and keeping the spare cluster separate from live replication. DeployOpen configures these controls, but your data protection policy decides how long restored copies are kept and who may see them.
Least-privilege backup credentials
The account that writes and reads backups should hold only the grants a backup or restore needs, and should be separate from application and administrator accounts. Store storage keys in named collections or your secrets manager rather than in query text or shell history. Check that a read-only backup role cannot drop tables or delete backup objects, and that the restore role cannot write to the production cluster. Confirm who can rotate these credentials and how rotation is recorded.
Same controls for restored copies
A restored table holds the same personal, financial or health data as production, so encryption at rest, access grants and logging should match the source. Treat the spare cluster as a regulated environment in its own right, with the same disk encryption and access review. Record which backup, restore timestamp and operator produced each copy so it appears in your data inventory.
Retention for restored copies
Restored data often lingers after a test because no one schedules its removal. Set a teardown step in the test runbook that drops restored databases and clears temporary storage, and confirm the removal in the evidence record. Your retention policy decides how long any restored copy, and the evidence it produced, may remain. DeployOpen can automate the teardown, but the retention period is a decision for your data owners.
Isolated spare cluster network
The spare cluster should have no route to production Keeper ensembles, production ClickHouse hosts or production application databases. Restrict egress to the backup storage endpoint and the test runner, and deny inbound access except from named administrators. Verify the isolation with a connectivity check at the start of each test run, not only at setup.
Protecting restored personal data
Restore tests should not turn into unmanaged copies of personal data. Where possible, test with the full schema and a representative sample, or apply masking to sensitive columns before anyone outside the data owner group queries them. Limit row policies and roles on the spare cluster to the people running the test, and log queries against restored personal data so access can be reviewed later.
Avoiding live replication paths
A restored replicated table can join a live Keeper ensemble if its ZooKeeper or Keeper paths and replica names match production. Use a separate Keeper ensemble or a distinct cluster name and macro set for the spare cluster, and do not restore replicated tables into production paths. Replication does not protect against human error or corruption, so a restore test is the only proof that backups are usable. Verify the path configuration before the first restore.
Recording restore test evidence for SOC 2 and ISO 27001 reviews
Restore test records are operational evidence, and DeployOpen prepares them without issuing any report. An auditor reviewing backup and availability controls will ask what was tested, when, by whom, and what happened. The answer should come from records your team produced during each test, not from a summary written later. DeployOpen can design the record format, the test runbook and the retention location, but independent auditors decide whether the evidence is sufficient for a SOC 2 report or an ISO/IEC 27001 certification audit. Ask your auditor early which record format and sample periods they expect.
Each restore test record should contain the test date, the backup identifier (the name or ID used in system.backups and in the BACKUP statement), the backup destination, and the scope: the database, tables or partitions restored, and whether the restore targeted a spare cluster. Record the operator, the ClickHouse version on the source and the spare cluster, and the RESTORE settings used. Verification results should include row counts and checksum comparisons per table against the source or the recorded baseline, with the query text saved. Exceptions and remediation matter as much as passes: a failed restore, a count mismatch or a missing partition should have a ticket, a root cause and a retest date. Store records in a system your auditor can access, with restricted write permissions so entries cannot be edited after the fact.
For SOC 2, the AICPA 2017 Trust Services Criteria treat security as the common criteria that apply to every report, while availability is an optional category included only when it is in scope. If your SOC 2 scope includes availability, restore tests are a natural source of evidence for the availability criteria, and your own control description should say how often tests run. For ISO/IEC 27001:2022, map your records to the Annex A controls covering information backup and ICT continuity by control ID and purpose, using your own wording rather than quoting the standard. Confirm the exact control set and the mapping with your certification body, since they decide what is in scope. Keep the mapping in the same repository as the records so the evidence trail stays connected.
What buyers ask about ClickHouse restore testing
How much effort does a ClickHouse restore test program take?
Effort depends mostly on how much data must be restored, how many tables and databases are in scope, how deep verification goes, and how often tests run. A first program includes designing the test cases, building a spare cluster or restore target, scripting the restore, writing the verification queries and producing the evidence record. After that, recurring work is running the tests, reviewing failures and keeping the scripts aligned with schema and version changes. Ask any provider for a written breakdown of one-time and recurring effort, and check whether the quote assumes your data volume and table count.
What drives the cost of restore testing up or down?
The main drivers are:
- Data volume and the time a restore takes to complete on the spare hardware
- Number of tables, databases and users or access objects included in the restore
- Verification depth: row counts only, or row counts plus checksums and business-level reconciliation
- Test frequency and whether tests are automated or run by hand
- Number of backup methods in use, such as full, incremental, partition-level or Kafka replay
Reduce cost by starting with the tables that matter most to recovery, automating the repeatable steps and agreeing on a clear pass criteria definition before work begins.
Can our own team run ClickHouse restore tests without a partner?
Yes, many teams run restore tests internally if they have someone who can operate ClickHouse, manage backup storage credentials and maintain a spare environment. The hard parts are usually not the RESTORE statement itself. They are keeping a spare cluster that matches production closely enough, writing verification that proves the data is complete, and recording results in a form an auditor can follow. A partner can build that harness and hand it over, or run it on a retainer. Confirm which skills your team has in ClickHouse operations, backup tooling and scripting before deciding.
What does the work need from our team?
Expect to provide access and ownership in four areas:
- Environment access: a spare cluster or cloud account, network paths to backup storage and change approval for the test environment
- Backup credentials: read access to the backup location, with credentials stored in a secrets manager rather than in scripts
- Baselines: row counts, checksums or reference queries taken from production at known points, plus agreed acceptance criteria
- Owners: a named person for the data, one for the platform and one who accepts the test results
Without baselines and owners, a restore can complete successfully and still leave the team unable to say whether the data is correct.
How long does it take to set up restore testing?
Setup time depends on scope, not on a fixed figure. It is shaped by the number of tables and backup methods covered, whether a spare cluster already exists, how much of the verification must be designed from scratch and how quickly your team can approve access. A narrow scope on an existing spare environment takes far less time than a full program covering several clusters and backup types. Ask for a written timeline tied to the agreed scope, and ask which steps depend on your approvals.
Do restore tests replace ClickHouse replication?
No. Replication and restore tests protect against different failures. ClickHouse replicas, coordinated by ClickHouse Keeper, resynchronize a recovered replica from a healthy one, but replication copies bad writes and accidental deletions to every replica. The ClickHouse backup documentation makes this point directly: replication alone does not protect against human error or corruption. Backups, tested by restoring them, are the control that lets you recover to an earlier state. Restore tests prove that the backups you rely on can actually be restored.
How often should we test ClickHouse restores?
Set the frequency from your recovery objectives and how fast the data changes, and confirm it against your backup strategy. Data that changes quickly, or that carries a tight recovery point objective, needs tests more often than slow-moving reference data. Many teams also test after significant changes such as ClickHouse version upgrades, schema changes or a change in backup method. The ClickHouse backup documentation recommends regular restore tests on spare clusters, because a backup that has never been restored will probably not restore correctly when it is needed. Record the chosen interval and the reason for it in the program documentation.
What evidence do restore tests produce for an audit?
A useful evidence record shows what was restored, when, from which backup, to which target, with which verification queries and what the results were. It should include the ClickHouse version, the backup identifier, the restore settings used, row count and checksum comparisons, and the named person who reviewed the outcome. DeployOpen prepares systems, controls and evidence for compliance assessments, but independent auditors decide whether that evidence meets the criteria that apply to you. Confirm with your auditor which records they expect before the first test runs.
How DeployOpen scopes a restore testing engagement
Restore testing engagements are usually fixed-scope, and the scope, deliverables and timeline are agreed in writing before any work starts. A quote follows from that written scope, so the first useful step is a scoping conversation about which clusters, tables and backup destinations the test must cover, and what the restored data is expected to prove. The reader should come to that conversation with the backup inventory they already have: where backups are written (local disk, S3-compatible storage or Azure Blob Storage), how often they run, and which ClickHouse version each cluster runs, since the restore procedure must match the version that took the backup.
Typical scope covers the spare cluster design, the restore automation and the verification layer. The spare cluster design sets how closely the test host matches production: server count, Keeper topology if replicated tables are restored, storage layout, and network isolation so a restored copy never reaches production users or ingestion. Restore automation usually means scripted RESTORE statements for full, partition and table-level cases, with the SETTINGS values used in each run recorded alongside the job. Verification queries compare row counts and checksums against the source, and evidence templates capture each run's backup identifier, timestamps, outcome and any exceptions, so the record can be handed to an internal auditor or an external one. DeployOpen prepares these controls and evidence; it does not issue audit reports or attestations.
Ongoing, scheduled restore testing can be run as a retainer after the initial build, since a backup that has been tested once still needs testing again as schemas, retention and versions change. Ask any partner to state in the written scope which party owns the spare cluster, who approves a failed test, how long test data is kept and who is paid for what if a restore fails because of a backup gap rather than a tooling fault. Confirm those points before signing, and confirm the vendor's own guidance on restore settings for your ClickHouse version.
How DeployOpen works on this
DeployOpen plans, builds and documents restore testing for self-managed ClickHouse inside your own infrastructure, from the first scope agreement to recurring test runs. Each engagement is fixed-scope, with the restore targets, evidence format and handover agreed in writing before any test runs.
How we scope
- Inventory which databases, tables and partitions carry the recovery priorities your business has set
- Agree the spare cluster topology, storage location and access path used for test restores
- Define row count, checksum and sampling checks as written acceptance criteria before testing begins
How we build
- Script automated restores of full, partial and table-level backups into an isolated spare cluster
- Configure restore settings such as database and table selection, and document each option used
- Run comparisons of row counts and checksums against source data, and log every mismatch for triage
How we hand over
- Deliver the restore runbook, scripts and configuration in your repositories with named owners
- Provide a test evidence template that records backup identifiers, restore times, results and reviewers
- Walk your operations team through a live restore so they can repeat the test without DeployOpen
How we support
- Offer ongoing restore testing as a retainer covering scheduled test runs and reports
- Re-run restore tests after ClickHouse version upgrades, backup layout changes or storage moves
- Confirm with your auditors which evidence they expect, since DeployOpen prepares records but does not issue audit reports
