MCP Tools for the Modern Data Stack: Coordinator/Worker Split, Silent Background Degradation, and Health Semantics Inversion Across Apache Flink, Apache Spark, dbt, ClickHouse, and Trino

Pattern 1: The Coordinator/Worker Split — coordinator health ≠ execution health

Every tool in the modern data stack separates a coordinator process (the entry point that accepts requests, returns HTTP 200 on health checks, and reports aggregate status) from the worker or execution layer (where actual computation happens). These two layers fail independently. An MCP tool that only probes the coordinator layer will miss every worker-layer failure — and worker-layer failures are the most common class of data pipeline degradation in production.

Apache Flink: The JobManager at port 8081 is the coordinator — it accepts REST API requests, schedules tasks, and runs checkpoint coordination. The TaskManagers are the workers. A job in state: "RUNNING" at the JobManager level can have vertex tasks in DEPLOYING state (waiting for TaskManager slots) or SCHEDULED state (waiting indefinitely because freeSlots = 0). Probe GET /taskmanagers separately from the job list to detect slot exhaustion before jobs fail. The GET /jobs/{jobId}/vertices endpoint reveals task-level health that the job summary obscures — every vertex must have status: "RUNNING" with tasks.RUNNING === parallelism.

Apache Spark: The Driver (hosting the SparkContext and SparkUI at port 4040) is the coordinator. Executors launched on the cluster are the workers. A Spark application returning HTTP 200 on GET /api/v1/applications can have zero active executors, which means all submitted stages are PENDING and the job cannot make progress. GET /api/v1/applications/{appId}/executors is a required second probe — filter for isActive: true and check both executor count and per-executor health metrics (totalGCTime / totalDuration for GC overhead, memoryUsed / maxMemory for memory pressure).

dbt: The dbt runner (whether dbt Core or dbt Cloud) is the coordinator — it orchestrates model execution, writes artifacts, and returns an exit code. But dbt has no live worker health endpoint to probe. Instead, the split is between the runner and its data sources: the dbt runner can execute all models successfully while the raw source data has been stale for 18 hours. sources.json from dbt source freshness is the worker-layer equivalent — it checks whether upstream data pipelines are delivering fresh data on schedule. A dbt pipeline that never runs dbt source freshness has no visibility into its data supply chain.

ClickHouse: Each ClickHouse node is its own coordinator (accepting queries, serving data) and its own worker. But for ReplicatedMergeTree tables, ZooKeeper or ClickHouse Keeper is a required third party for write coordination. The node's HTTP layer (GET /ping → "Ok.") reports liveness but has no knowledge of ZooKeeper session state. SELECT is_readonly FROM system.replicas is the worker-layer check — if any replicated table returns is_readonly = 1, ZooKeeper lost its session and all writes to that table are silently failing even though the HTTP endpoint returns success. This is the data stack's equivalent of a read-only replica silently masquerading as primary.

Trino: The single coordinator at port 8080 (or 8443) is the query planner, scheduler, and REST API. Workers are distributed nodes that execute tasks. The coordinator's GET /v1/info → starting: false confirms coordinator liveness but says nothing about worker health. GET /v1/node/failed surfaces workers the coordinator has lost heartbeat contact with — failed workers cause task retries, which consume memory on surviving workers, which can trigger the cascading memory exhaustion that leads to blockedQueries. Probe the coordinator, the worker list, and the cluster memory metrics as three independent layers.

The structural implication for MCP tool design: every data stack tool requires a minimum of two probe targets — the coordinator endpoint and a worker/execution-layer endpoint. The coordinator probe catches complete outages. The execution-layer probe catches the much more common partial degradation where the coordinator is healthy but nothing is making forward progress.

Pattern 2: Background State Maintenance as the Silent Health Dimension

All five tools in this arc have a class of background processes that run independently of query or job execution, maintain safety nets or data integrity guarantees, and degrade silently over time while every surface health signal stays green. These background processes — Flink's checkpoint mechanism, Spark's shuffle and GC, dbt's source freshness, ClickHouse's merge engine, Trino's memory pool — are the health dimension that naive MCP implementations miss entirely.

Flink checkpointing — the safety net that vanishes quietly: Checkpointing is Flink's mechanism for fault tolerance and exactly-once semantics. At configured intervals, Flink snapshots operator state to a state backend (S3, RocksDB, HDFS). A streaming job with no successful checkpoint in the last 10 minutes cannot recover to a consistent state after any task failure. The checkpoint failure is completely invisible at the GET /jobs/{jobId} endpoint — the job shows state: "RUNNING". Only GET /jobs/{jobId}/checkpoints reveals it: counts.failed increasing monotonically across probes, or latest.completed.trigger_timestamp more than 3× the checkpoint interval in the past. A Flink streaming job can run "healthy" for 24 hours after its last successful checkpoint and then, on a single task failure, be unable to restart from any consistent state. Probe checkpoint health independently of job health.

Spark shuffle spill — the invisible performance collapse: When Spark cannot fit a shuffle (sort, join, aggregation) in executor memory, it spills to disk. Disk spill is 10–100× slower than in-memory shuffle and can turn a 3-minute job into a 2-hour job with identical HTTP responses at every API endpoint. Spark logs the affected tasks as SUCCEEDED. The job status is RUNNING during the spill and SUCCEEDED after. The failureReason field on stages is empty. Only the executor-level diskUsed metric and the stage-level diskBytesSpilled metric reveal what happened — and only if you query GET /api/v1/applications/{appId}/executors and GET /api/v1/applications/{appId}/stages as separate probes. An MCP tool that only checks job status will report "Spark is healthy" while a job that should take 5 minutes has been writing 80 GB to disk for 45 minutes.

dbt source freshness — the leading indicator of everything: dbt models execute SQL against data that was ingested by upstream systems (Fivetran, Airbyte, custom ETL, Kafka consumers). When those upstream systems stop delivering data, dbt models continue to succeed — they just produce stale output from cached or slowly-aging warehouse data. Source freshness checks (dbt source freshness) are the only mechanism that surfaces upstream pipeline breaks before they propagate into stakeholder-facing dashboards. The sources.json artifact records max_loaded_at (the most recent row timestamp in each source table) against configurable freshness thresholds. A source in status: "warn" that has been warn-status for more than 2× its warn threshold means downstream models have been building on stale data for hours. This is the leading indicator for pipeline failures — a source that crosses from warn to error will cascade into model skips and broken dashboards.

ClickHouse merge backlog — the write throttle that appears without warning: ClickHouse's MergeTree family stores data in immutable parts written on each INSERT. Background merge processes combine parts over time. If insert rate outpaces merge completion rate, part count per partition grows toward the 300-part throttle threshold — at which point ClickHouse begins rejecting INSERT statements with Too many parts errors. No HTTP signal precedes this failure. The node serves queries normally. INSERT requests succeed. Only SELECT count() FROM system.parts WHERE active = 1 GROUP BY database, table, partition reveals a partition approaching 300 parts. The merge lag check (system.merges for active background merges, system.replication_queue for pending merge tasks) detects the condition before it reaches the throttle threshold. By the time application code sees Too many parts, the background maintenance has been falling behind for hours.

Trino memory exhaustion — the silent queue stall: Trino allocates memory per-query from each worker's memory pool. When cluster-wide memory reservation (reservedMemory in GET /v1/cluster) approaches total capacity, the next memory-intensive query tips the cluster into blocked state. Blocked queries don't fail — they queue indefinitely. From the application's perspective, query submissions appear to succeed (the query is accepted) but never return results. The coordinator reports healthy. The workers report healthy. Only blockedQueries > 0 in the cluster metrics reveals the stall. Monitor reservedMemory / totalMemory as a continuous leading indicator and alert at 85% utilization rather than waiting for the first blocked query.

The structural implication: for each tool, define a background maintenance probe distinct from the execution health probe. The execution probe (is the job running?) answers whether the tool is doing work. The background maintenance probe (are checkpoints completing? is merge backlog growing? is memory headroom shrinking?) answers whether the tool will be able to continue doing work safely. Only the combination provides genuine health signal.

Pattern 3: Health Semantics Inversion — When "Healthy" Means Opposite Things

The most subtle trap across this arc is that the same status code or HTTP response means opposite things depending on the tool and the job type. An MCP tool that treats "RUNNING" as universally healthy and "FINISHED" as universally terminal will misclassify failures as successes and generate incorrect alerts. Each tool requires a health-semantics model, not just a status-code check.

Flink FINISHED = failure for streaming, success for batch: A Flink streaming job entering state: "FINISHED" is a production incident. Streaming jobs should run indefinitely — FINISHED means the job's source operator returned end-of-stream, which should never happen in a production Kafka consumer or event ingestion pipeline. For batch jobs, FINISHED is the expected terminal state. An MCP tool monitoring a Flink cluster must know which jobs are streaming (expected: perpetually RUNNING) and which are batch (expected: FINISHED after completion) before it can interpret job status correctly. The same applies to RESTARTING — for a streaming job, RESTARTING means the restart strategy is active and the job is recovering from a task failure; for a batch job, RESTARTING is an unexpected intermediate state that may indicate infrastructure instability.

dbt "pass" ≠ data quality healthy: The dbt invocation result in run_results.json has two levels: the invocation-level status (derived from the CLI exit code — 0 = pass, non-zero = error) and individual node statuses. When tests are configured with severity: warn, test failures produce node-level status: "warn" entries but the invocation exits with code 0 and the job-level status is "pass". A dbt pipeline can complete with "pass" while dozens of warn-severity tests detected null primary keys, duplicate records, or referential integrity violations across hundreds of thousands of rows. The correct health model is: invocation status "error" = pipeline broken (models failed); invocation status "pass" with any warn nodes = data quality degraded (investigate but pipeline unblocked); invocation status "pass" with all nodes in success/pass = genuinely healthy. Most MCP implementations check only the first condition.

ClickHouse HTTP 204 on INSERT ≠ data written: ClickHouse returns HTTP 204 for successful INSERT statements. But "successful" here means the node accepted the write — it does not mean the data was replicated. When a ReplicatedMergeTree table's ZooKeeper session is expired (is_readonly = 1 in system.replicas), INSERT statements fail at the application level with a ClickHouse exception — but only if the client inspects the response body. Some ClickHouse client libraries surface this as an HTTP 500; others surface it as an exception in the response JSON while returning HTTP 200. The critical inversion: GET /ping → "Ok." confirms the ClickHouse process is running; is_readonly = 1 means writes are failing. These two signals are completely independent. An application checking HTTP 204 on INSERT and seeing success may be watching writes vanish for hours while the node is in read-only mode.

Trino RUNNING query + zero split progress = stuck, not working: Trino's GET /v1/query/{queryId} returns state: "RUNNING" for active queries. But a query in RUNNING state with completedSplits === 0 and runningSplits === 0 for more than 60 seconds is stuck — either waiting on split generation from a slow connector, blocked on a shuffle that's waiting for worker memory, or starved because all worker task slots are consumed by other queries. The query looks active by status, but no computation is occurring. The correct check for query liveness is state === "RUNNING" AND completedSplits advancing between successive probes. A static completedSplits count with a non-zero totalSplits for more than one probe cycle is the stuck-query signal.

Spark SUCCEEDED with spill = degraded performance, not health: Spark's job status SUCCEEDED is the only terminal healthy status for batch jobs. But SUCCEEDED with diskBytesSpilled > 10% of total shuffle write bytes at the stage level means the job completed with significant performance degradation. A job that normally finishes in 8 minutes and took 2.5 hours because all shuffle data spilled to disk appears identical in the job status API — both return status: "SUCCEEDED". Duration anomaly detection (comparing current job duration against the median of the last 7 successful runs) is required to classify spill-affected completions as degraded rather than healthy.

The structural implication: every MCP tool in this arc requires a semantic health model, not a simple status check. Each status value must be evaluated in context — is this a streaming or batch job? Is this a blocking or non-blocking failure? Is this coordinator liveness or execution completeness? Build these models into the MCP tool's health assessment function rather than treating raw API status codes as ground truth.

Cross-tool comparison: the five health layers

Composite health probe: all five tools in a single data pipeline

In practice, a modern data stack pipeline runs these tools in sequence: Kafka/Flink ingests and transforms streaming data into ClickHouse; Trino queries ClickHouse for analytical workloads; Spark processes historical batch data; dbt transforms both into business-facing models. An MCP tool monitoring this pipeline needs a composite probe that surfaces the right failure layer for each tool without requiring separate tools per system.

async function dataStackPipelineHealth(config) {
  const {
    flinkJobManagerUrl, flinkJobId,
    sparkUiUrl, sparkAppId,
    dbtArtifactBaseUrl, dbtExpectedIntervalMs,
    clickhouseHost, clickhousePort, clickhouseUser, clickhousePassword,
    trinoCoordinatorUrl, trinoUser,
  } = config;

  // All coordinators in parallel — fastest fail detection
  const [flinkOverview, sparkApps, dbtArtifacts, chPing, trinoInfo] = await Promise.allSettled([
    fetch(`${flinkJobManagerUrl}/overview`, { signal: AbortSignal.timeout(5_000) }).then(r => r.json()),
    fetch(`${sparkUiUrl}/api/v1/applications`, { signal: AbortSignal.timeout(5_000) }).then(r => r.json()),
    fetch(`${dbtArtifactBaseUrl}/run_results.json`, { signal: AbortSignal.timeout(10_000) }).then(r => r.json()),
    fetch(`http://${clickhouseHost}:${clickhousePort}/ping`, { signal: AbortSignal.timeout(5_000) }).then(r => r.text()),
    fetch(`${trinoCoordinatorUrl}/v1/info`, { signal: AbortSignal.timeout(5_000) }).then(r => r.json()),
  ]);

  const coordinatorStatus = {
    flink: flinkOverview.status === 'fulfilled',
    spark: sparkApps.status === 'fulfilled',
    dbt: dbtArtifacts.status === 'fulfilled',
    clickhouse: chPing.status === 'fulfilled' && chPing.value?.trim() === 'Ok.',
    trino: trinoInfo.status === 'fulfilled' && trinoInfo.value?.starting === false,
  };

  // Only probe execution layers for reachable coordinators
  const executionProbes = [];

  if (coordinatorStatus.flink) {
    executionProbes.push(
      Promise.allSettled([
        fetch(`${flinkJobManagerUrl}/jobs/${flinkJobId}`, { signal: AbortSignal.timeout(10_000) }).then(r => r.json()),
        fetch(`${flinkJobManagerUrl}/jobs/${flinkJobId}/checkpoints`, { signal: AbortSignal.timeout(10_000) }).then(r => r.json()),
        fetch(`${flinkJobManagerUrl}/taskmanagers`, { signal: AbortSignal.timeout(5_000) }).then(r => r.json()),
      ]).then(([job, ckpts, tms]) => ({ tool: 'flink', job, ckpts, tms }))
    );
  }

  if (coordinatorStatus.clickhouse) {
    const ch = `http://${clickhouseHost}:${clickhousePort}`;
    const headers = { 'X-ClickHouse-User': clickhouseUser, 'X-ClickHouse-Key': clickhousePassword };
    executionProbes.push(
      fetch(ch, {
        method: 'POST',
        headers,
        body: 'SELECT database, table, is_readonly, future_parts FROM system.replicas FORMAT JSON',
        signal: AbortSignal.timeout(15_000),
      }).then(r => r.json()).then(data => ({ tool: 'clickhouse', replicas: data.data }))
    );
  }

  if (coordinatorStatus.trino) {
    executionProbes.push(
      Promise.allSettled([
        fetch(`${trinoCoordinatorUrl}/v1/cluster`, {
          headers: { 'X-Trino-User': trinoUser },
          signal: AbortSignal.timeout(10_000),
        }).then(r => r.json()),
        fetch(`${trinoCoordinatorUrl}/v1/node/failed`, {
          headers: { 'X-Trino-User': trinoUser },
          signal: AbortSignal.timeout(5_000),
        }).then(r => r.json()),
      ]).then(([cluster, failed]) => ({ tool: 'trino', cluster, failed }))
    );
  }

  const executionResults = await Promise.allSettled(executionProbes);

  // Assess: coordinator down is critical; execution layer issues are warning-to-critical depending on severity
  const issues = [];

  for (const [tool, ok] of Object.entries(coordinatorStatus)) {
    if (!ok) issues.push({ tool, severity: 'critical', type: 'coordinator_unreachable' });
  }

  for (const result of executionResults) {
    if (result.status === 'rejected') continue;
    const data = result.value;

    if (data.tool === 'flink') {
      const job = data.job.status === 'fulfilled' ? data.job.value : null;
      const ckpts = data.ckpts.status === 'fulfilled' ? data.ckpts.value : null;
      const tms = data.tms.status === 'fulfilled' ? data.tms.value : null;

      if (job && job.state !== 'RUNNING') {
        issues.push({ tool: 'flink', severity: 'critical', type: 'job_not_running', detail: `Job state: ${job.state}` });
      }
      if (ckpts && ckpts.counts) {
        const lastCompleted = ckpts.latest?.completed;
        const interval = ckpts.configuration?.['execution.checkpointing.interval'] ?? 60_000;
        if (lastCompleted && Date.now() - lastCompleted.trigger_timestamp > interval * 3) {
          issues.push({ tool: 'flink', severity: 'warning', type: 'checkpoint_stale', detail: 'Last checkpoint older than 3× interval' });
        }
      }
      if (tms && tms.taskmanagers) {
        const freeSlots = tms.taskmanagers.reduce((s, tm) => s + tm.freeSlots, 0);
        if (freeSlots === 0) {
          issues.push({ tool: 'flink', severity: 'warning', type: 'slots_exhausted', detail: 'No free TaskManager slots' });
        }
      }
    }

    if (data.tool === 'clickhouse' && data.replicas) {
      const readonlyTables = data.replicas.filter(r => r.is_readonly === 1 || r.is_readonly === '1');
      if (readonlyTables.length > 0) {
        issues.push({
          tool: 'clickhouse', severity: 'critical', type: 'replicas_readonly',
          detail: `${readonlyTables.length} table(s) in read-only mode — writes are being discarded`,
        });
      }
    }

    if (data.tool === 'trino') {
      const cluster = data.cluster.status === 'fulfilled' ? data.cluster.value : null;
      const failed = data.failed.status === 'fulfilled' ? data.failed.value : null;
      if (cluster && cluster.blockedQueries > 0) {
        issues.push({ tool: 'trino', severity: 'critical', type: 'queries_blocked', detail: `${cluster.blockedQueries} queries blocked (cluster out of memory)` });
      }
      if (Array.isArray(failed) && failed.length > 0) {
        issues.push({ tool: 'trino', severity: 'warning', type: 'workers_failed', detail: `${failed.length} worker(s) recently failed` });
      }
    }
  }

  return {
    healthy: issues.filter(i => i.severity === 'critical').length === 0,
    coordinatorStatus,
    issues,
    summary: `${Object.values(coordinatorStatus).filter(Boolean).length}/5 coordinators reachable; ${issues.length} issue(s) detected`,
  };
}

Probe strategy: what to register with AliveMCP

Given the three patterns above, a practical AliveMCP probe configuration for a modern data stack deployment needs three probe tiers per tool:

Tier 1 — Coordinator liveness (60s interval, 5s timeout): Flink GET /overview, Spark GET /api/v1/applications, dbt artifact store URL for run_results.json, ClickHouse GET /ping, Trino GET /v1/info. These are the fastest probes and catch complete outages. Alert as critical on any failure — a down coordinator means all downstream work is stopped.

Tier 2 — Execution layer health (60s interval, 15–30s timeout): Flink GET /jobs/{jobId}/vertices + GET /taskmanagers, Spark GET /api/v1/applications/{appId}/executors + stages, dbt sources.json freshness check, ClickHouse system.replicas query for is_readonly and future_parts, Trino GET /v1/cluster + GET /v1/node/failed. Alert at warning when execution capacity is degraded (slots exhausted, GC pressure, source stale, replication lag, worker failures). Alert at critical when writes are silently failing (ClickHouse read-only) or queries are blocked (Trino).

Tier 3 — Background maintenance health (5–10 minute interval, 20–30s timeout): Flink GET /jobs/{jobId}/checkpoints + backpressure probes, Spark stage-level diskBytesSpilled aggregation, dbt run_results.json warn-severity test count, ClickHouse system.parts part count per partition + system.replication_queue stuck tasks, Trino reservedMemory / totalMemory from GET /v1/cluster. These probes detect slow-burn degradation that will become a production incident in the next 1–4 hours if not addressed. Alert at warning to allow time for investigation before the condition becomes critical.

The probe separation matters for alert routing. A Tier 1 alert wakes up an on-call engineer immediately — the pipeline is down. A Tier 2 alert notifies the data team — capacity is degraded, someone should investigate. A Tier 3 alert goes to a dashboard or a daily digest — a trend that needs attention before the next high-volume batch window. Mixing these tiers into a single probe conflates outages with maintenance tasks and produces alert fatigue.

This three-tier model applies to observability data stores and workflow orchestration tools as well — across all data infrastructure MCP integrations, the coordinator liveness check is the table stakes and the background maintenance tier is what separates a monitoring-aware MCP tool from a naive one. The health check patterns guide covers the composite endpoint structure for exposing all three tiers from a single URL that AliveMCP can probe.

Related guides

• MCP tools for Apache Flink — streaming job health, checkpoint failures, backpressure detection, and TaskManager state
• MCP tools for Apache Spark — SparkUI REST API, stage health, executor state, shuffle spill, and GC pressure
• MCP tools for dbt — run artifacts, model status, test failures, source freshness, and job monitoring
• MCP tools for ClickHouse — ReplicatedMergeTree health, system tables, replication queue lag, and merge backlog
• MCP tools for Trino — coordinator health, worker state, query execution stages, and memory pool management
• MCP tools for observability data stores — Elasticsearch, Loki, Alertmanager, InfluxDB, and Dynatrace
• MCP tools for workflow orchestration — Temporal, Airflow, Step Functions, Prefect, and Dagster
• MCP server health check patterns — composite endpoints and failure classification
• MCP tools for Apache Kafka — consumer group lag, partition health, and broker availability