ClickHouse Automated Backup Sentinel

id: clickhouse-automated-backup-and-disaster-recovery-sentinel
namespace: company.database

inputs:
  - id: clickhouse_host
    type: STRING
    displayName: ClickHouse Host
    description: Hostname or IP address of the target ClickHouse server or cluster proxy.
    defaults: "localhost"

  - id: clickhouse_port
    type: INT
    displayName: ClickHouse Native/HTTP Port
    description: TCP or HTTP port for the ClickHouse JDBC driver (default 8123 for
      HTTP protocol).
    defaults: 8123

  - id: database_name
    type: STRING
    displayName: Default Database
    description: ClickHouse database context used when establishing the initial connection.
    defaults: "system"

  - id: clickhouse_username
    type: STRING
    displayName: ClickHouse Username
    description: Database user with SELECT permissions on system.backups catalog.
    defaults: "default"

  - id: lookback_days
    type: INT
    displayName: Lookback Window (Days)
    description: Historical period in days over which automated backup jobs are audited.
    defaults: 7

  - id: max_stale_backup_hours
    type: INT
    displayName: Maximum Backup Staleness (Hours)
    description: Maximum allowable hours elapsed since the latest successful backup
      before raising an SLA breach alert.
    defaults: 26

triggers:
  - id: daily_backup_audit_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    description: Automated daily audit scheduled at 04:00 UTC to verify completion
      of nightly ClickHouse backup pipelines.
    cron: "0 4 * * *"
    disabled: true

tasks:
  - id: probe_backup_telemetry
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Execute analytical query against ClickHouse system.backups to
      evaluate backup successes, failures, and execution recency.
    url: "jdbc:clickhouse://{{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port
      }}/{{ inputs.database_name }}"
    username: "{{ inputs.clickhouse_username }}"
    password: "{{ secret('CLICKHOUSE_PASSWORD') }}"
    fetchType: FETCH_ONE
    allowFailure: true
    sql: |
      SELECT
          countIf(status NOT IN ('BACKUP_CREATED', 'CREATING_BACKUP') OR error != '') AS failed_backups_count,
          countIf(status = 'BACKUP_CREATED') AS successful_backups_count,
          count() AS total_backups_evaluated,
          argMax(name, start_time) AS latest_backup_name,
          argMax(status, start_time) AS latest_backup_status,
          argMax(error, start_time) AS latest_backup_error,
          argMax(total_size, start_time) AS latest_backup_size_bytes,
          argMax(num_files, start_time) AS latest_backup_files,
          max(start_time) AS latest_backup_start_time,
          dateDiff('hour', max(start_time), now()) AS hours_since_latest_backup
      FROM system.backups
      WHERE start_time >= now() - INTERVAL {{ inputs.lookback_days }} DAY

  - id: classify_backup_health
    type: io.kestra.plugin.core.debug.Return
    description: Classify operational backup posture based on database reachability,
      failure counts, and SLA recency thresholds.
    format: |
      {{
        (outputs.probe_backup_telemetry.row is not defined or outputs.probe_backup_telemetry.row is null) ? 'UNREACHABLE' :
        (outputs.probe_backup_telemetry.row.total_backups_evaluated == 0 or outputs.probe_backup_telemetry.row.successful_backups_count == 0) ? 'MISSING_BACKUP' :
        (outputs.probe_backup_telemetry.row.failed_backups_count > 0 or outputs.probe_backup_telemetry.row.latest_backup_status != 'BACKUP_CREATED') ? 'CRITICAL' :
        (outputs.probe_backup_telemetry.row.hours_since_latest_backup > inputs.max_stale_backup_hours) ? 'STALE' :
        'HEALTHY'
      }}

  - id: route_by_backup_health
    type: io.kestra.plugin.core.flow.Switch
    description: Route incident notifications and operational confirmations based on
      evaluated backup health classification.
    value: "{{ outputs.classify_backup_health.value }}"
    cases:
      HEALTHY:
        - id: log_healthy_status
          type: io.kestra.plugin.core.log.Log
          description: Log verified operational backup health with latest backup metadata
            and size metrics.
          message: "HEALTHY: ClickHouse backup pipeline nominal. Latest backup '{{
            outputs.probe_backup_telemetry.row.latest_backup_name }}' ({{
            outputs.probe_backup_telemetry.row.latest_backup_files }} files, {{
            outputs.probe_backup_telemetry.row.latest_backup_size_bytes }}
            bytes) completed {{
            outputs.probe_backup_telemetry.row.hours_since_latest_backup }}h
            ago. Zero failures across {{
            outputs.probe_backup_telemetry.row.total_backups_evaluated }}
            audited jobs."

      STALE:
        - id: alert_stale_backup
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          description: Dispatch Slack warning when no successful backup has executed
            within the configured SLA staleness threshold.
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: |
            {
              "text": ":warning: *ClickHouse Backup SLA Breach Detected*\n*Host:* `{{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port }}`\n*Last Successful Backup:* `{{ outputs.probe_backup_telemetry.row.latest_backup_name }}`\n*Hours Elapsed:* `{{ outputs.probe_backup_telemetry.row.hours_since_latest_backup }}` hours (SLA Limit: `{{ inputs.max_stale_backup_hours }}` hours)\n*Action Required:* Verify automated backup scheduling daemons and confirm target storage availability."
            }

      CRITICAL:
        - id: alert_backup_failure
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          description: Dispatch urgent Slack incident alert when ClickHouse system.backups
            detects failed backup jobs or error strings.
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: |
            {
              "text": ":rotating_light: *CRITICAL: ClickHouse Backup Execution Failure*\n*Host:* `{{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port }}`\n*Failed Backups Count:* `{{ outputs.probe_backup_telemetry.row.failed_backups_count }}`\n*Latest Backup Name:* `{{ outputs.probe_backup_telemetry.row.latest_backup_name }}`\n*Latest Status:* `{{ outputs.probe_backup_telemetry.row.latest_backup_status }}`\n*Error Detail:* ```{{ outputs.probe_backup_telemetry.row.latest_backup_error }}```\n*Action Required:* Inspect ClickHouse server logs, check disk/S3 permissions, and ensure backup storage has sufficient capacity."
            }

      MISSING_BACKUP:
        - id: alert_missing_backup
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          description: Dispatch critical alert when zero successful backups are found in
            the entire lookback window.
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: |
            {
              "text": ":sos: *CRITICAL: No Successful ClickHouse Backups Found*\n*Host:* `{{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port }}`\n*Audited Window:* Past `{{ inputs.lookback_days }}` days\n*Status:* Zero successful backups recorded in `system.backups` catalog. Disaster recovery recovery point objective (RPO) is compromised."
            }

      UNREACHABLE:
        - id: alert_unreachable_endpoint
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          description: Dispatch urgent Slack alert when ClickHouse server cannot be
            contacted via JDBC.
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: |
            {
              "text": ":satellite_antenna: *ClickHouse Server Unreachable*\n*Host:* `{{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port }}`\n*Status:* Unable to establish JDBC connection to query `system.backups`. Ensure ClickHouse server is running and network security groups permit traffic."
            }

  - id: log_audit_summary
    type: io.kestra.plugin.core.log.Log
    description: Record immutable audit trail of ClickHouse backup verification for
      disaster recovery compliance.
    message: |
      [AUDIT] ClickHouse Automated Backup Sentinel completed.
      Host: {{ inputs.clickhouse_host }}:{{ inputs.clickhouse_port }} | Database: {{ inputs.database_name }}
      Classification: {{ outputs.classify_backup_health.value }}
      Total Evaluated: {{ outputs.probe_backup_telemetry.row.total_backups_evaluated ?? 0 }}
      Failed Backups: {{ outputs.probe_backup_telemetry.row.failed_backups_count ?? 0 }}
      Successful Backups: {{ outputs.probe_backup_telemetry.row.successful_backups_count ?? 0 }}
      Latest Backup: {{ outputs.probe_backup_telemetry.row.latest_backup_name ?? 'NONE' }} (Status: {{ outputs.probe_backup_telemetry.row.latest_backup_status ?? 'NONE' }})
      Hours Since Latest: {{ outputs.probe_backup_telemetry.row.hours_since_latest_backup ?? 'N/A' }}h (Max Allowed: {{ inputs.max_stale_backup_hours }}h)
      Execution ID: {{ execution.id }}

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Alert Slack if the ClickHouse sentinel flow encounters an unhandled
      runtime error.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {
        "text": ":rotating_light: *ClickHouse Sentinel Flow Error*\nFlow `{{ flow.id }}` failed unexpectedly during execution `{{ execution.id }}`. Backup monitoring is interrupted; inspect task execution logs."
      }

outputs:
  - id: status
    type: STRING
    value: "{{ outputs.classify_backup_health.value }}"
  - id: failed_backups
    type: INT
    value: "{{ outputs.probe_backup_telemetry.row.failed_backups_count ?? 0 }}"
  - id: latest_backup_status
    type: STRING
    value: "{{ outputs.probe_backup_telemetry.row.latest_backup_status ?? 'UNKNOWN'
      }}"

ClickHouse analytical data platforms frequently ingest terabytes of critical event data, but backup operations can fail silently due to object storage permission expiration, disk space exhaustion, or cluster mutation locks. This blueprint connects directly to ClickHouse via JDBC on a recurring schedule, queries the official system.backups catalog, audits recent backup executions, verifies that disaster recovery snapshots comply with SLA staleness limits, and dispatches detailed Slack notifications when failures occur.

How it works

probe_backup_telemetry (io.kestra.plugin.jdbc.clickhouse.Query) queries the native ClickHouse system.backups system catalog over the configured lookback window. It aggregates total backup runs, counts failed operations (status != 'BACKUP_CREATED' or non-empty error), extracts the latest backup name and status, and calculates the exact elapsed time in hours since the latest successful snapshot. allowFailure: true ensures network unreachable conditions are caught gracefully.
classify_backup_health (io.kestra.plugin.core.debug.Return) evaluates query outputs using declarative Pebble conditionals:
- UNREACHABLE: The ClickHouse endpoint could not be contacted over JDBC.
- MISSING_BACKUP: Zero successful backups exist within the entire lookback period.
- CRITICAL: One or more backup jobs failed or logged non-empty error strings.
- STALE: All recorded backups succeeded, but the most recent backup age exceeds max_stale_backup_hours.
- HEALTHY: Backups are succeeding within SLA limits with zero errors.
route_by_backup_health (io.kestra.plugin.core.flow.Switch) branches on the evaluated status:
- HEALTHY: Writes an informational confirmation log with backup size and file counts.
- STALE: Sends an SLA alert to Slack warning that backup snapshots have stalled.
- CRITICAL: Sends an incident alert to Slack containing the failed backup name and exact error details.
- MISSING_BACKUP: Triggers an alert warning that disaster recovery coverage is absent.
- UNREACHABLE: Dispatches an unreachable endpoint alert to on-call engineers.
log_audit_summary (io.kestra.plugin.core.log.Log) records an immutable summary log entry across all executions, establishing an auditable compliance trail in Kestra execution history.
The top-level errors handler dispatches a Slack webhook alert if any unexpected task error occurs, preventing silent monitoring blackouts.
daily_backup_audit_schedule triggers the sentinel daily at 04:00 UTC (configured with disabled: true for initial onboarding).

What you get

Proactive detection of failed ClickHouse backups before disaster recovery incidents occur.
Automated verification of backup recency against Recovery Point Objective (RPO) SLAs.
Context-rich Slack notifications containing exact error messages from system.backups.
Agentless telemetry extraction using native ClickHouse SQL and standard JDBC connectivity.
An immutable execution history of backup audits for compliance and disaster recovery reporting.

Who it's for

Database Reliability Engineers (DBREs) and Platform Engineers maintaining ClickHouse clusters.
Data Engineers responsible for warehouse data durability, backup pipelines, and compliance audits.
Site Reliability Engineers (SREs) who require automated verification of disaster recovery readiness.

Why orchestrate this with Kestra

Custom bash cron scripts querying database endpoints often fail silently without leaving execution trails, and hard-coded database passwords in scripts create severe security vulnerabilities. Kestra orchestrates the monitoring lifecycle declaratively with encrypted secret injection, granular task retries, visual execution tracking, and seamless integration into automated escalation workflows.

Prerequisites

A running ClickHouse server or cluster (accessible locally via Docker clickhouse/clickhouse-server:latest on port 8123 or over the network).
A ClickHouse user account with SELECT privilege on the system.backups table.
A Slack Incoming Webhook URL for receiving incident notifications.

Secrets

CLICKHOUSE_PASSWORD: ClickHouse database user password (required).
SLACK_WEBHOOK_URL: Slack Incoming Webhook URL used for dispatching alert messages (required).

Quick start

Configure the CLICKHOUSE_PASSWORD and SLACK_WEBHOOK_URL secrets in your Kestra namespace.
Import this blueprint into your Kestra instance and adjust clickhouse_host and clickhouse_port to point to your ClickHouse instance.
Click Execute to run a manual verification pass.
Verify execution logs and check that status outputs HEALTHY.
Enable the daily_backup_audit_schedule trigger to begin automated daily disaster recovery monitoring.

How to extend

Add automated backup trigger: In the MISSING_BACKUP or STALE branch, trigger an ad-hoc ClickHouse BACKUP DATABASE execution task to automatically initiate a catch-up snapshot.
Add multi-channel escalation: Route CRITICAL alerts to PagerDuty or Opsgenie using Kestra notification plugins for immediate off-hours paging.
Export metrics: Append a DuckDB or Prometheus push task to graph backup size growth trends and backup duration over time.

ClickHouse Automated Backup and Disaster Recovery Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

ClickHouse Automated Backup and Disaster Recovery Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Incremental Docs-to-Chroma RAG Sync with Deletions and a Retrieval Smoke Test

Chroma Collection Distance Metric Validator

Build a Chat-With-Your-Data System Using Elasticsearch and OpenAI (RAG)

Score change requests on risk and build the CAB agenda, pre-approving the low-risk ones

Citus Inactive Worker Reactivate Guard

ChromaDB Vector Collection Snapshot and AWS S3 Disaster Recovery Pipeline

More Infrastructure Blueprints

Kafka Connect Sink Poison Pill Offset Rewind and Skip Gate

Archive ownerless failed Pod diagnostics before cleanup

Kafka Client Quota Guard

Kafka Consumer Lag Sentinel

Kafka ACL Overly Broad Grant Audit

Kafka Consumer Group Dead-Letter Rebalance and Offset Rewind Guard

New to Kestra?