ClickHouse resilience and recovery for DORA

A backup that has never been restored is an unproven assumption, and DORA-style resilience testing exists to expose that gap. ClickHouse replication does not solve this problem. The ClickHouse backup documentation states plainly that replication alone does not protect against human error or corruption: a DROP TABLE or a bad ALTER runs on every replica, and a recovered replica simply syncs the damaged state. So the cluster needs `BACKUP` and `RESTORE` output stored off-cluster, to local disk, S3-compatible storage or Azure Blob Storage, alongside the ReplicatedMergeTree replicas. Full and incremental backups can cover tables, partitions or whole databases, and the destination should sit in a failure domain the entity controls and that a single incident cannot reach.

The same documentation warns that a backup never test-restored will probably not restore properly when needed, and recommends regular restore tests on spare clusters. Treat that as the test design. A restore test on a spare cluster should prove at least four things: that the restored schema matches production, that row counts match the source at the backup point, that representative queries return the expected results, and that the elapsed time to restore fits the entity's own recovery time objective. Record the backup identifier, the restore command, the operator and the outcome each time, because those records become the evidence that supervisors and auditors will ask for.

Recovery objectives must be set by the entity, not by the platform. ClickHouse does not know how much data loss a payment or account-reporting service can tolerate, or how long it may be down before a supervisor must be informed. Ask the business owners and risk function for a recovery time objective and a recovery point objective per dataset, then check whether the backup frequency and restore duration measured in testing meet them. If they do not, the gap belongs in the ICT risk register, not in a footnote to the backup job.

Article 28(8) of DORA requires a financial entity to have documented, tested exit strategies and transition plans for ICT services that support critical or important functions. For a ClickHouse cluster, that means being able to move the analytical data and the workloads that depend on it to another environment without losing the ability to meet recovery and reporting obligations. Article 28(3) adds that the entity keeps a register of information covering all contractual arrangements for ICT services, so the ClickHouse dependency must be recorded there as well. The exit plan should name the tables, retention periods, Keeper and replica topology, backup locations and the people who run the migration, and the plan should be exercised, not just written.

Self-managed ClickHouse gives the entity direct control over where data lives and how it leaves. The server is open source under the Apache License 2.0 (SPDX: Apache-2.0), so the entity can run it in its own accounts or data centers and export data with BACKUP and RESTORE to storage it chooses. The license does not satisfy exit obligations by itself, though. It removes a licensing barrier, while the entity still has to prove that its operational tooling, backups and configurations can be reproduced elsewhere. A managed ClickHouse Cloud service, offered by ClickHouse Inc., would require the entity to check the contract against Article 30(2), which calls for stating the locations where services are provided and data processed, advance notice of changes, and data return on insolvency or termination. Ask the provider in writing for the processing and storage regions, sub-processor notice terms, and the format and timeline for returning data at exit.

Before relying on either model, confirm three points in writing. First, that the export path works at the volume and format the entity needs, which means running a restore into a separate environment and timing it. Second, that the register of information and the exit plan reference the same data inventory. Third, that the contract terms for any managed service match what the exit plan assumes. Tests for these points belong on a schedule with named owners, so the evidence can be shown to a competent authority that asks for it.

The database does not decide what counts as an ICT incident, how it is classified, or when it must be reported. Those questions sit with the financial entity's incident management process under Chapter III of DORA, and with the reporting timelines and content its competent authority expects. What ClickHouse can do is supply evidence that the incident register and classification process can rely on. Before the first incident, agree with the incident response owner which events in the cluster are in scope: failed or rejected logins, changes to users, roles, row policies and settings profiles, replica lag or readonly replicas, Keeper session loss, failed or partial BACKUP jobs, and unexpected restores. Map each one to the register field it will populate.

The primary sources are the system.query_log and system.session_log tables, the server logs under the configured log path, and the system.replicas view for replication state. Query and session logs are written in the server's own tables, so they can be lost if the cluster that holds them fails with its data. Decide where they are exported, for example to a SIEM or an append-only store outside the cluster, and confirm that the export is also subject to the retention rule. Note that these logs can contain query text with sensitive values, so the masking and access rules for the log store belong in the same decision.

Retention periods for these records are not set by ClickHouse or by DORA's technical standards alone. Confirm the required period with the entity's own record-keeping policy, its legal function and its national competent authority, and write the number into the logging standard along with who approves changes to it. Test the chain once: pick a past incident in a non-production copy, trace it from the alert through the logs to the register entry, and confirm that the evidence still exists at the end of the retention window. Document the result, because an auditor or supervisor will ask how the entity knows its logs were complete.