# How we monitor our data pipelines and warehouse health

The pipeline-health agent we run on Kortix — connected to the warehouse and GitHub. Every hour it checks table freshness, row-count anomalies, and schema drift, alerts Slack, and drafts a GitHub issue with the likely cause. It never touches the data.

Canonical page: https://openchatly.com/use-cases/data-pipeline-monitor

A pipeline can fail quietly. A load job silently stops appending rows, a source
schema changes upstream and half the fields come through null, a table that
should refresh every hour hasn't moved since yesterday. None of that throws an
error anyone sees — the dashboards built on top of it just start being wrong,
and usually the first person to notice is whoever's report looks off in a
stakeholder meeting.

We run a pipeline-monitor agent on Kortix that checks the warehouse itself,
every hour: is every table as fresh as its SLA requires, does today's row count
look like the rest of the trend, has the schema changed under anyone, did a load
job fail. It only reads the warehouse. When something is wrong, it posts to the
data team's Slack channel and drafts a GitHub issue with its best guess at the
cause. This is how we watch our own warehouse.

- **Team:** Kortix
- **Runs on:** Hourly cron
- **Connected systems:** Postgres warehouse · Slack · GitHub
- **Mode:** Read-only · alert + draft issue, nothing else

## The problem

Warehouse health doesn't announce itself. A load job can fail without an
exception if it's built to skip a bad batch and move on. A table can go stale
because an upstream cron got disabled, not because anything crashed. A schema
can drift because a source API added a field, and the load still "succeeds" —
it just drops or nulls what it doesn't recognize. Every one of these looks fine
from the outside until a downstream query returns something wrong.

The common approaches don't hold up. Dashboards built on the data can't tell you
the data itself is broken — they just render whatever's there, wrong or not. A
"did the job run" check confirms the process exited zero, not that the table it
wrote actually looks right. Someone eyeballing row counts in a spreadsheet
catches it eventually, usually after a downstream report has already gone out
wrong. And this is a different failure mode from an application throwing an
error mid-request — the pipeline can succeed and the warehouse can still be
unhealthy.

## What we built

On Kortix, an hourly cron triggers an agent. It spawns a fresh session with
read-only access to the warehouse and checks every monitored table against its
freshness SLA, compares today's row count to the table's own trailing baseline,
diffs the current schema against the last-seen shape, and looks for loads that
failed or silently stalled. When it finds something out of bounds, it posts an
alert to the data team's Slack channel with the affected table, the anomaly, and
the most likely cause, and drafts a GitHub issue with the same diagnosis so the
incident is tracked. It never writes to the warehouse — the alert and the draft
issue are the only outputs.

## How it works

### Copy the setup prompt into a new session

Open your Kortix workspace, start a new session, paste this prompt and send it.
The agent writes the agent file, the health check skill and the hourly
trigger, and opens a change request for you to review. Prefer clicks? Choose **Use this
template** instead.

**Setup prompt**

```
Set up an hourly data pipeline monitor agent in this workspace.

1. Create the agent agents/data-pipeline-monitor.md. Its job is to watch the warehouse for stale tables, row-count anomalies, schema drift and failed loads. It may use the PostgreSQL and Slack connectors and a GH_TOKEN secret for GitHub.

2. Create the skill skills/pipeline-health-check/SKILL.md with these rules:
   - Check every table in the public schema. A table is stale when its newest load is older than its SLA. The default SLA is 24 hours.
   - Compare each table's daily row count with its own 14-day trend. Flag a count far below or far above it.
   - Diff each table's columns and types against the last-seen shape. Flag any added, dropped, renamed or retyped column.
   - Flag any load job from the last 2 hours that failed or never finished, with its error message.
   - Read only. Never run INSERT, UPDATE, DELETE, ALTER, DROP or TRUNCATE, and never restart a pipeline.
   - For each anomaly, post one alert to #data-alerts and draft a GitHub issue in owner/repo with the evidence and the likely cause.

3. Add a cron trigger pipeline-health-sweep to kortix.yaml. It runs the data-pipeline-monitor agent every hour, on the hour, with the prompt "Load the pipeline-health-check skill and run one complete health sweep over the public schema."

4. Tell me which of these connectors are not connected yet.

5. Open a change request with all of it. Do not merge it.
```

### Connect your tools

Open **Connectors** in your workspace and connect your warehouse and Slack.
Credentials are brokered server-side and never enter the agent's machine. Add a GitHub token as the `GH_TOKEN` secret so the agent can draft
issues.

- **PostgreSQL** (Required):
Checks freshness, row counts and schema drift, read-only.
- **Slack** (Required):
Posts each anomaly with its evidence and likely cause.
- **GitHub** (Required):
Drafts an issue with the same diagnosis.

### Run it once with this prompt

Merge the change request, then start a session with the **data-pipeline-monitor** agent
and send this prompt. Check its alerts against what you know
about your tables before you let the schedule take over.

**Prompt**

```
Run one health sweep over the public schema.

1. List every table in the schema.
2. Check each table's newest load against its SLA. The default is 24 hours.
3. Compare each table's row count with its 14-day trend.
4. Diff each table's columns against the last-seen shape.
5. Check the load log for jobs from the last 2 hours that failed or stalled.
6. For each anomaly, post an alert to #data-alerts and draft a GitHub issue in owner/repo. Change nothing in the warehouse.
```

### Let the hourly schedule take over

The change request added this trigger to `kortix.yaml`. From now on the agent
runs every hour, on the hour, each run in a new session on its own cloud
computer.
To change the time, edit the cron line, or open **Triggers** in the workspace.

**kortix.yaml**

```yaml
triggers:
  - slug: pipeline-health-sweep
    name: Hourly pipeline health sweep
    on:
      cron: "0 0 * * * *"
    run:
      agent: data-pipeline-monitor
      prompt: Load the pipeline-health-check skill and run one complete health sweep over the public schema.
```

### Stay read-only on the warehouse

Every query the agent runs is a read. It never changes a row, a table or a
pipeline, and it never restarts a load. Its only outputs are the Slack alert
and the drafted GitHub issue. A person decides the fix.

### Read the alerts

A sweep that finds an anomaly posts one alert per table, and a clean sweep posts
nothing. This is what a run looks like in the session:

> **Hourly pipeline health sweep · 14:00**
>
> Prompt: Load the pipeline-health-check skill and run one complete health sweep over the public schema.
>
> Checked **32** tables. **orders_daily** is **31 hours** old against a
> 24-hour SLA, and **events** has a new nullable column. Posted **2** alerts
> and drafted **2** issues. The other **30** tables are healthy.

> **The pattern**
> An hourly **cron** spawns a session with a read-only **connector** into the
> Postgres warehouse. The SLAs and baselines live as **skills** and **memory**.
> The agent reads everything and writes nothing but the Slack alert and a
> drafted GitHub issue.

## Make it do more

Once it works, paste any of these into the same session.

**Set a tighter SLA**

```
Add to memory that the tables our revenue dashboard reads have a 2-hour freshness SLA, and check them first in every sweep.
```

**Morning health digest**

```
Every weekday at 08:00, post one line per anomaly from the last 24 hours to #data-alerts, with whether its issue is still open.
```

**Show the downstream impact**

```
For each anomaly, list the views and tables that read from the affected table, so the alert shows what else is wrong.
```

## Guardrails

The agent reads across the entire warehouse, so its access is scoped and
strictly one-directional:

- **Isolation.** Every run happens in its own isolated sandbox. The session is granted access only to the warehouse it's scoped to, and only the Slack alert and the
  drafted issue are written back out.
- **Scoped secrets.** The warehouse credential and the GitHub token are
  encrypted in the Secrets Manager and injected into the sandbox at runtime,
  scoped to the agents you grant them to.
- **Read-only, no exceptions.** The connector into the warehouse is read-only.
  The agent cannot modify a row, alter a schema, or touch a pipeline — it can
  only alert and draft.
- **Draft, not decide.** The GitHub issue is a draft with a diagnosis attached,
  never auto-closed or auto-assigned. A human owns the incident from there.
- **Everything is code.** The agent's SLAs, baselines, and per-system
  permissions are files in the repo, versioned and changed through a reviewed
  **change request** rather than a dashboard setting.

## The outcome

- **Every hour:** Freshness, row counts, and schema rechecked against the live warehouse
- **Read-only:** Nothing written to any table, schema, or pipeline
- **2 outputs:** A Slack alert and a drafted GitHub issue, nothing else

Warehouse problems that used to surface as a wrong number in someone's dashboard
now show up as an hourly health check with a specific table, a specific anomaly,
and a likely cause — already in Slack and already drafted as an issue. The agent
only reads and reports; the data team decides what to fix.
