Dremio Reflection Health and Refresh Failure Sentinel with Kestra

id: dremio-reflection-health-sentinel
namespace: company.team
description: |
  Query Dremio's own sys.reflections system table to classify accelerator
  reflection health HEALTHY, SLOW_REFRESH, FAILED, or NO_REFLECTIONS, and
  alert Slack when a reflection has failed to refresh or is refreshing too
  slowly to keep accelerated queries current.

triggers:
  - id: schedule_every_30_minutes
    type: io.kestra.plugin.core.trigger.Schedule
    description: "Checks every 30 minutes - reflection refresh jobs typically run on
      a slower cadence than streaming ingestion, so a tight schedule is
      unnecessary. Shipped disabled; enable it once dremio_url points at a real
      coordinator and a manual run has been verified."
    cron: "0,30 * * * *"
    disabled: true
    inputs:
      dremio_url: "jdbc:dremio:direct=localhost:31010"
      max_refresh_duration_ms: 60000

inputs:
  - id: dremio_url
    type: STRING
    displayName: "Dremio Coordinator JDBC URL"
    defaults: "jdbc:dremio:direct=localhost:31010"
    description: "JDBC URL of the Dremio coordinator node, in the form
      jdbc:dremio:direct=host:31010, used by the
      io.kestra.plugin.jdbc.dremio.Query task."

  - id: max_refresh_duration_ms
    type: INT
    displayName: "Max Acceptable Reflection Refresh Duration (ms)"
    defaults: 60000
    description: "Above this many milliseconds,
      sys.reflections.last_refresh_duration_millis for a non-failed reflection
      counts as SLOW_REFRESH."

tasks:
  - id: probe_reflection_health
    type: io.kestra.plugin.jdbc.dremio.Query
    description: "Single SQL statement (io.kestra.plugin.jdbc.dremio.Query supports
      only one statement per task) aggregating Dremio's own sys.reflections
      system table (columns status, num_failures, last_failure_message,
      refresh_status, last_refresh_duration_millis verified from the official
      Dremio SYS.REFLECTIONS documentation). coalesce guards against SUM
      returning NULL when no row matches a case."
    url: "{{ inputs.dremio_url }}"
    username: "{{ secret('DREMIO_USERNAME') }}"
    password: "{{ secret('DREMIO_PASSWORD') }}"
    fetchType: FETCH_ONE
    sql: |
      SELECT count(*) AS total_reflections, coalesce(sum(case when status = 'FAILED' then 1 else 0 end), 0) AS failed_count, coalesce(sum(case when last_refresh_duration_millis > {{ inputs.max_refresh_duration_ms }} then 1 else 0 end), 0) AS slow_count FROM sys.reflections

  - id: evaluate_reflections_exist
    type: io.kestra.plugin.core.flow.If
    description: "NO_REFLECTIONS when total_reflections is 0 - no accelerator
      reflections are configured on this Dremio instance at all, distinct from
      reflections existing but unhealthy."
    condition: "{{ outputs.probe_reflection_health.row.total_reflections is defined
      and outputs.probe_reflection_health.row.total_reflections > 0 }}"
    then:
      - id: evaluate_failures
        type: io.kestra.plugin.core.flow.If
        description: "FAILED when at least one reflection's current status is FAILED,
          checked before slow-refresh since a failed reflection is a harder
          failure than a merely slow one."
        condition: "{{ outputs.probe_reflection_health.row.failed_count > 0 }}"
        then:
          - id: list_failed_reflections
            type: io.kestra.plugin.jdbc.dremio.Query
            description: "Detail fetch of only the FAILED reflections, for the Slack alert
              payload."
            url: "{{ inputs.dremio_url }}"
            username: "{{ secret('DREMIO_USERNAME') }}"
            password: "{{ secret('DREMIO_PASSWORD') }}"
            fetchType: FETCH
            sql: |
              SELECT reflection_name, dataset_name, num_failures, last_failure_message FROM sys.reflections WHERE status = 'FAILED'
          - id: alert_failed_reflections
            type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
            description: "Always alert on FAILED so an on-call engineer sees it without
              querying sys.reflections by hand."
            url: "{{ secret('SLACK_WEBHOOK_URL') }}"
            payload: |
              {
                "text": "Dremio reflection health sentinel: FAILED. {{ outputs.probe_reflection_health.row.failed_count }} of {{ outputs.probe_reflection_health.row.total_reflections }} reflections are FAILED: {{ outputs.list_failed_reflections.rows | jq('[.[] | (.reflection_name + \" on \" + .dataset_name + \": \" + (.last_failure_message // \"no message\"))] | join(\"; \")') | first }}. Execution {{ execution.id }}."
              }
        else:
          - id: evaluate_slow_refresh
            type: io.kestra.plugin.core.flow.If
            description: "SLOW_REFRESH when no reflection has failed outright, but at least
              one took longer than max_refresh_duration_ms on its last refresh."
            condition: "{{ outputs.probe_reflection_health.row.slow_count > 0 }}"
            then:
              - id: list_slow_reflections
                type: io.kestra.plugin.jdbc.dremio.Query
                description: "Detail fetch of only the reflections over the refresh-duration
                  threshold, for the Slack alert payload."
                url: "{{ inputs.dremio_url }}"
                username: "{{ secret('DREMIO_USERNAME') }}"
                password: "{{ secret('DREMIO_PASSWORD') }}"
                fetchType: FETCH
                sql: |
                  SELECT reflection_name, dataset_name, last_refresh_duration_millis FROM sys.reflections WHERE last_refresh_duration_millis > {{ inputs.max_refresh_duration_ms }}
              - id: alert_slow_refresh
                type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
                description: "Lower-severity alert than FAILED - the reflection is still
                  refreshing, just too slowly to keep accelerated queries
                  reliably current."
                url: "{{ secret('SLACK_WEBHOOK_URL') }}"
                payload: |
                  {
                    "text": "Dremio reflection health sentinel: SLOW_REFRESH. {{ outputs.probe_reflection_health.row.slow_count }} of {{ outputs.probe_reflection_health.row.total_reflections }} reflections exceeded {{ inputs.max_refresh_duration_ms }}ms: {{ outputs.list_slow_reflections.rows | jq('[.[] | (.reflection_name + \" on \" + .dataset_name + \": \" + (.last_refresh_duration_millis | tostring) + \"ms\")] | join(\"; \")') | first }}. Execution {{ execution.id }}."
                  }
            else:
              - id: log_reflections_healthy
                type: io.kestra.plugin.core.log.Log
                message: "HEALTHY: all {{ outputs.probe_reflection_health.row.total_reflections
                  }} Dremio reflections have status != FAILED and
                  last_refresh_duration_millis <= {{
                  inputs.max_refresh_duration_ms }}ms."
    else:
      - id: log_no_reflections
        type: io.kestra.plugin.core.log.Log
        message: "NO_REFLECTIONS: sys.reflections returned 0 rows. No accelerator
          reflections are configured on this Dremio instance."

  - id: log_audit_complete
    type: io.kestra.plugin.core.log.Log
    description: "Final status line for every run, independent of which branch fired."
    message: "Reflection health audit complete. total_reflections={{
      outputs.probe_reflection_health.row.total_reflections }}. failed_count={{
      outputs.probe_reflection_health.row.failed_count }}. slow_count={{
      outputs.probe_reflection_health.row.slow_count }}."

errors:
  - id: alert_execution_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: "Alert when the flow itself fails outside the handled branches, for
      example an unreachable Dremio coordinator or invalid credentials."
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {
        "text": "Dremio reflection health sentinel FAILED in execution {{ execution.id }}. The sys.reflections audit did not complete; investigate before trusting the last known HEALTHY/SLOW_REFRESH/FAILED/NO_REFLECTIONS status."
      }

A Dremio accelerator reflection that fails to refresh degrades silently - accelerated queries keep running, just against increasingly stale data, with no obvious client-side error. A reflection that refreshes too slowly erodes the same guarantee more gradually. This blueprint reads Dremio's own sys.reflections system table to catch both failure modes, classifies the result into a four-state risk level, and alerts on anything but a clean bill of health.

How it works

schedule_every_30_minutes (io.kestra.plugin.core.trigger.Schedule) runs every 30 minutes, explicitly passing dremio_url and max_refresh_duration_ms through its own inputs: block so the flow is self-contained when the trigger fires. Shipped disabled so a manual run can be verified first.
probe_reflection_health (io.kestra.plugin.jdbc.dremio.Query, fetchType: FETCH_ONE) runs a single aggregate SQL statement against sys.reflections, returning row.total_reflections, row.failed_count (reflections with status = 'FAILED'), and row.slow_count (reflections with last_refresh_duration_millis over the threshold), both guarded with coalesce against SUM returning NULL on an empty match set.
evaluate_reflections_exist (io.kestra.plugin.core.flow.If) branches on whether total_reflections > 0.
- else (NO_REFLECTIONS): log_no_reflections reports that no reflections are configured at all.
- then (reflections exist): nested evaluate_failures (io.kestra.plugin.core.flow.If) branches on failed_count > 0.
  - then (FAILED): list_failed_reflections fetches the failed rows (reflection_name, dataset_name, num_failures, last_failure_message), then alert_failed_reflections reports them to Slack.
  - else: nested evaluate_slow_refresh (io.kestra.plugin.core.flow.If) branches on slow_count > 0.
    - then (SLOW_REFRESH): list_slow_reflections fetches the over-threshold rows, then alert_slow_refresh reports them to Slack at a lower severity than FAILED.
    - else (HEALTHY): log_reflections_healthy records a clean check.
log_audit_complete always runs last, printing total_reflections, failed_count, and slow_count regardless of which branch fired.
The errors block alerts Slack separately if the flow itself fails outright - an unreachable coordinator or bad credentials must not read as NO_REFLECTIONS or HEALTHY.

What you get

A four-state answer (HEALTHY / SLOW_REFRESH / FAILED / NO_REFLECTIONS) sourced directly from Dremio's own sys.reflections table, not inferred from query latency or an external agent.
Outright refresh failure and merely slow refresh treated as two distinct severities, so a reflection that is still working but lagging is never confused with one that has stopped updating entirely.
A separate NO_REFLECTIONS path for an instance with no accelerator reflections configured, so an empty setup is never misreported as healthy acceleration.
Slack alerts that include the exact last_failure_message or last_refresh_duration_millis per affected reflection, instead of requiring a manual SELECT * FROM sys.reflections.

Who it's for

Data platform teams running Dremio accelerator reflections who need an early signal when a refresh job fails or falls behind.
On-call engineers who want one Slack alert with the exact failure message instead of querying sys.reflections by hand during an incident.
Anyone evaluating the Dremio JDBC plugin who wants a worked example of Query driving both an aggregate system-table probe and a detail fetch, not just a plain data extract.

Why orchestrate this with Kestra

sys.reflections queried by hand answers one moment in time; it does not decide a cadence, classify the result into a four-state risk level, or notify anyone. Kestra supplies the schedule, the HEALTHY/SLOW_REFRESH/FAILED/NO_REFLECTIONS classification as a first-class branch, and an execution history that shows exactly when a reflection degraded and what Dremio's own error message said at the time.

Prerequisites

A reachable Dremio coordinator node with at least one dataset and reflection configured (update dremio_url).
A Dremio user with permission to query sys.reflections (available to any user who can run SQL against the coordinator).
A Slack incoming webhook for alerts.

Local testing:

docker pull dremio/dremio-oss:latest
docker run -d --name dremio-oss -p 9047:9047 -p 31010:31010 -p 32010:32010 dremio/dremio-oss:latest
docker network connect YOUR_KESTRA_NETWORK dremio-oss

Run docker network ls first to find the active Kestra network name. Port 9047 serves the Dremio web UI (used once, to create the initial admin user and configure a source/reflection) and 31010 is the JDBC client port used by dremio_url; neither collides with Kestra's own UI on port 8080.

Secrets

DREMIO_USERNAME / DREMIO_PASSWORD: credentials used by every io.kestra.plugin.jdbc.dremio.Query task in this flow.
SLACK_WEBHOOK_URL: Slack incoming webhook used by alert_failed_reflections, alert_slow_refresh, and the errors block.
In Kestra OSS (no Enterprise secrets backend), secrets are supplied as environment variables prefixed SECRET_, base64-encoded - for example SECRET_DREMIO_PASSWORD=$(echo -n '<your-local-password>' | base64) - and read back in flows with {{ secret('NAME') }}.

Inputs

Input	Type	Default	Purpose
`dremio_url`	STRING	`jdbc:dremio:direct=localhost:31010`	JDBC URL of the Dremio coordinator
`max_refresh_duration_ms`	INT	`60000`	Duration threshold before reflection counts as SLOW_REFRESH

Outputs

outputs.probe_reflection_health.row.total_reflections / failed_count / slow_count: the aggregate counts from every run.
outputs.list_failed_reflections.rows / outputs.list_slow_reflections.rows: only populated on the branch that fired, each row carrying the sys.reflections detail columns fetched for that branch.

Quick start

Start Dremio locally with the command above, open http://localhost:9047 once to create the initial admin user, add a source, and create at least one reflection on a dataset.
Add the DREMIO_USERNAME, DREMIO_PASSWORD, and SLACK_WEBHOOK_URL secrets.
Run the flow manually with the defaults and confirm log_reflections_healthy or log_no_reflections fires depending on whether a reflection exists yet.
Force a failure (for example, remove the underlying dataset's source credentials) and re-run to confirm alert_failed_reflections fires with the real last_failure_message.
Enable schedule_every_30_minutes once you trust the check.

How to extend

Add a loop over dataset_name with io.kestra.plugin.core.flow.Loop to post one alert per affected dataset instead of a single combined message.
Track considered_count / matched_count / accelerated_count over time (via a Kestra KV store) to detect a reflection that is healthy but no longer being used by any query planner decision.
Route NO_REFLECTIONS to a different, lower-priority Slack channel than FAILED, since an empty configuration is typically a setup gap, not an active incident.

Pitfalls

sys.reflections is scoped to the Dremio project/cluster you connect to. On Dremio Cloud, dremio_url and credentials differ from the self-hosted software example above - see the plugin's own documented Cloud connection string.
There is no dedicated "reflection status" task in this plugin. Both the aggregate probe and the detail fetches go through the same generic io.kestra.plugin.jdbc.dremio.Query task used for any other Dremio read.
io.kestra.plugin.jdbc.dremio.Query supports only a single SQL statement per task. That is why the aggregate probe and each detail fetch are separate tasks rather than one combined query.
SUM over zero matching rows returns NULL, not 0, in standard SQL. failed_count and slow_count are wrapped in coalesce(..., 0) specifically to avoid a NULL > 0 comparison in the If conditions.
This blueprint is UNTESTED against a live Dremio cluster. The sys.reflections column names are taken verbatim from Dremio's own SYS.REFLECTIONS documentation, and the io.kestra.plugin.jdbc.dremio.Query properties and outputs are taken from the plugin's own source on main - but no Docker container or Kestra engine was run to execute this flow end to end.

Dremio Reflection Health and Refresh Failure Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Inputs

Outputs

Quick start

How to extend

Pitfalls

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Dremio Reflection Health and Refresh Failure Sentinel

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Inputs

Outputs

Quick start

How to extend

Pitfalls

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

AI Data Quality Anomaly Advisor and SQL Remediation Generator

Aerospike Stop-Writes Headroom Sentinel

AES-256 Argon2id Cryptographic Landing and Compliance Gate

Read out an A/B test with a sample ratio check and a guardrail metric

Adanos Asset Sentiment Drift Sentinel

AI Document Data Extraction: Parse PDFs and Web Pages into Structured JSON with OpenAI

More Infrastructure Blueprints

ACM Certificate Expiry Compliance Sentinel

Apache ActiveMQ Artemis Dead Letter Address (DLA) Message Accumulation Sentinel

Environmental Disaster Early Warning System

Automatically Review GitHub Pull Requests with OpenAI GPT-4o and Post Comments

ADLS World-Writable ACL Sentinel

Renew TLS certificates through ACME, verify them, deploy them and roll back on failure

New to Kestra?