signals-scout-data-pipelines

Three cheap reads cold-start a run:

• scout-scratchpad-search (text=pipeline) — durable steering: the watchlist of high-value pipelines and their baselines, noise: / addressed: / dedupe: entries gating re-reports, plus report: / reviewer: entries pointing at the open report for a pipeline and who owns it.
• scout-runs-list (last 7d) — what prior pipeline runs found and ruled out.
• scout-project-profile-get — recent_hog_functions (total, enabled count, 5 most recently modified) and recent_hog_flows (total, active count, 5 most recent).
• inbox-reports-list (search=pipeline name, ordering=-updated_at) — the reports already in the inbox. A contradiction on a pipeline you've reported before is an edit, not a fresh report; pull the closest matches with inbox-reports-retrieve before authoring. Your own report-channel reports persist their backing signals under source_product=signals_scout, so don't filter source_product=cdp — you'd miss every report you authored.

Then orient on each leg with one fleet-wide read apiece:

1. Functions state scan — cdp-functions-list {"enabled": true, "limit": 100}, following next pages. Every entry carries status: {state, tokens} from the hog watcher, so one paginated scan gives fleet health without per-function calls. States: 1 healthy, 2 degraded (overflowed), 3 auto-disabled, 11 forcefully degraded, 12 forcefully disabled (11/12 are admin actions). Footgun: the type filter must be a comma-separated string ("type": "destination,transformation") — a JSON array silently returns zero results. Footgun: status exists only on the REST tools; system.hog_functions has no state column.
2. Flows fleet stats — workflows-global-stats {"after": "-7d"}: per-flow succeeded/failed counts, sorted most-failing first, one call. It returns bare workflow_ids — cross-reference names and lifecycle status via system.hog_flows (id, name, status), and only judge active flows.
3. Batch exports roster — rosters are small, so check every live one:

SELECT id, name, model, interval, created_at, last_updated_at
FROM system.batch_exports
WHERE paused = 0 AND deleted = 0
LIMIT 100

then batch-export-get {id} per export for the 10 most recent runs (status, records_completed, records_failed, latest_error, interval bounds).

SQL footguns (all three system pipeline tables): boolean-ish columns are integers — countIf(enabled) errors, write countIf(enabled = 1). system.hog_functions and system.hog_flows carry huge JSON columns (inputs_schema, filters, edges, actions) — never SELECT *, name the columns you need. HogQL string timestamp literals parse in the project timezone — use now() - INTERVAL N DAY for recency windows, never hand-written timestamp strings.

Before any per-pipeline deep dive, normalize against the whole fleet: if every destination's failures spiked at once, that's one platform/network finding (or known ingestion trouble), not N per-destination findings.

Patterns to watch — starting points, not a checklist.

From the state scan, every enabled function at state 2 or 3 is a candidate. State 3 on a destination is the headline case: the platform concluded it was broken and stopped delivery; nobody got told. Confirm the story before filing a report:

• cdp-functions-metrics-retrieve {id, after: "-7d", breakdown_by: "name", interval: "day"} — series come back by name: triggered (passed the filter), succeeded, failed, filtered (rejected by the filter), plus fetch-style sub-metrics. Date when failures took over.
• cdp-functions-logs-retrieve {id, level: "WARN,ERROR", limit: 50} — the actual error: an upstream 4xx/5xx, a Hog runtime error, a timeout. Name the error class in the finding; it decides who can fix it (their endpoint vs their function code).

Transformations outrank destinations. A transformation sits in the ingestion hot path — degraded or disabled means every event in the project is processed differently (e.g. GeoIP enrichment silently missing from all events), not one integration down. Treat any non-healthy enabled transformation as P1 material.

The watcher tracks execution health, not delivery semantics — a destination erroring fast on every event can sit at state 1 indefinitely. There is no fleet-wide metrics endpoint and no app_metrics HogQL table, so don't brute-force: maintain a watchlist in memory (the project's high-value destinations — by traffic, by name, by template) and check those with cdp-functions-metrics-retrieve each run, plus a small rotating sample of the rest so coverage accumulates across runs.

Failure share = failed / triggered within the same window — never compare either against filtered, which is usually orders of magnitude larger and healthy by construction (the filter doing its job). A candidate needs sustained contradiction: share ≥ ~10% over 24h with ≥ ~50 triggered, against a flat-or-quiet history. Two special shapes worth catching:

• Born broken — a destination created in the last days failing ~100% since creation (≥ ~20 attempts): a botched setup the team believes is working. created_at is in the list response; the activity log (scopes: ["HogFunction"]) dates config edits.
• Filter starvation — triggered collapsing to ~zero while filtered keeps flowing: the filter stopped matching, usually because an upstream event was renamed or stopped firing. The destination isn't failing — it's starving. Confirm the filtered events still exist before calling it (one execute-sql count on the filter's event).

For each live export, read the 10 latest_runs off batch-export-get:

• Failed runs are terminal — retries exhausted; that interval's data did not land and won't until someone backfills. latest_error carries the reason (auth expiry, schema mismatch, destination quota). One Failed run is already a data gap; file a report with the interval bounds. FailedRetryable / Running / Starting are in-flight states — not findings.
• Stalls — compare the newest run's data_interval_end against now: a gap over ~2× the export interval with no running run means the schedule itself stopped.
• Record-level failures — records_failed > 0 on Completed runs: partial delivery, worth a memory entry and a report only if it grows or persists.
• Volume cliffs — records_completed collapsing across consecutive runs while event ingestion held steady points at a filter/config change; check last_updated_at and the activity log (scopes: ["BatchExport"]) before calling it unexplained.

From workflows-global-stats, candidates are active flows with failure share ≥ ~10% and ≥ ~20 failures over the window, or any active flow failing ~100%. Then:

• workflows-stats {id, after: "-7d", breakdown_by: "kind", interval: "day"} — the time series; date the onset. Series names here are success / failure / other — and other is the huge filtered-out bucket, not a problem; share = failure / (success + failure).
• workflows-list-invocations {id, after: "-24h", status: "failed", limit: 50} — the per-recipient view: error_kind (e.g. http_4xx) and error_message. Failures concentrated in one error_kind mean one broken step — a dead webhook URL, a revoked integration, a bad template. Spread across kinds points at the flow's inputs.
• workflows-logs {id, level: "WARN,ERROR", limit: 50} — step-by-step trace when the invocation view isn't enough.

Messaging flows deserve weight: a failing flow that sends email/messages means real people silently not hearing from the team — reach (distinct failing person_ids) is the impact number.

Write a scratchpad entry whenever you observe something a future run should know. Encode the category in the key prefix — pattern:, noise:, addressed:, dedupe:, report:, reviewer::

• key pattern:pipelines:watchlist — "High-value pipelines: destination Stripe sync (id …, ~5k triggered/day, share <1%), transformation GeoIP (state 1, hot path), export BigQuery events (hourly, ~2M rows/run), flow Order confirmation (~1k/day). Check these first."
• key pattern:pipelines:bigquery-export — "Hourly events export, baseline ~2M records/run, occasional single FailedRetryable that self-recovers. Only the terminal Failed status matters here."
• key noise:pipelines:example-fixtures — "Flow ExampleRepoFailures and functions named *tester* are deliberate test fixtures that fail by design — never findings."
• key dedupe:pipelines:stripe-sync-failures — "Filed delivery-failure shift on destination Stripe sync 2026-06-09 (share 0.4% → 38%, http_401 since 06-08). Skip unless the error class changes or it recovers and breaks again." One stable key per issue — update it in place, don't mint a dated variant.
• key addressed:pipelines:webhook-404-flow — "Team replied: legacy endpoint, flow being retired this sprint. Don't re-file the 404 concentration."
• key report:pipelines:stripe-sync — "Report 019f0a96-… covers the Stripe sync delivery-failure shift. Edit it (append_evidence with the fresh numbers) while it persists and the report is still live; if it was resolved and the destination later re-breaks, that's a fresh report."
• key reviewer:pipelines:stripe-sync — "Stripe sync owned by alice (GitHub login) — route its reports there."

By run #5 you should know the project's high-value pipelines and their failure baselines, which fixtures are noise, and what's already been surfaced — so a real delivery contradiction stands out immediately and cheaply.

For a candidate that clears the bar, the call is edit an existing report, author a new one, remember, or skip — use judgment, these are the rails:

• Search the inbox first. The report:pipelines:<slug> scratchpad pointer is the reliable path (it holds the report_id — inbox-reports-retrieve it directly); with no pointer, inbox-reports-list by the specific pipeline name (ordering=-updated_at), not a broad word like pipeline.

• Edit (scout-edit-report) when a still-live report already covers the same pipeline issue — a destination still watcher-disabled, a failure share still elevated, an export still failing. Add the fresh numbers with append_evidence, or rewrite the title/summary on a report you authored. This is the default when a match exists. edit-report can't change status, so if the matched report is resolved / suppressed / failed, don't append (it won't resurface) — author a fresh report for the relapse and repoint the report: key.

• Author (scout-emit-report) only when nothing live covers it. A good report names the pipeline and its id, quantifies the contradiction (failure share vs baseline, failed/stalled intervals, watcher state), names the error class from logs/invocations, and dates the onset — ideally tied to a config edit or deploy. Keep the failure-share evidence numeric rather than reaching for charts — the daily series lives only behind cdp-functions-metrics-retrieve (no app_metrics HogQL table), so there is no attachable query node that reproduces it. Set priority (P0–P4) + priority_explanation — a non-healthy ingestion-path transformation, a stalled/all-failing batch export, or a 100%-failing production flow is P1, a watcher-disabled destination / sustained failure-share shift / Failed export run is P2, debt and fixture cleanup bundles P3; it's the report's importance in the inbox, your call to make. Set suggested_reviewers via scout-members-list (objects — a {github_login} or {user_uuid}, not bare strings; cache under reviewer:pipelines:<slug>); left empty the report reaches no one. Then choose the actionability + repo together:

  • Most pipeline findings are an investigation a human confirms (a broken remote endpoint, an expired credential, a watcher intervention) → actionability=requires_human_input and repository=NO_REPO (NO_REPO is what stops priority+reviewers from spawning a pointless repo-selection sandbox).
  • When the fix is an obvious code change (a dead webhook URL or bad template in a team-owned function/flow) → actionability=immediately_actionable with repository="owner/repo" (or omit repository to let the selector pick) to open a draft PR.

  After authoring, write the report:pipelines:<slug> pointer with the report_id so the next run edits instead of duplicating.

• Remember if below the bar but worth carrying forward (a share drifting inside the noise band, records_failed creeping, a degraded function that recovered); skip with a one-line note if a noise: / addressed: / dedupe: entry or an existing report already covers it.

Sibling scouts share memory — data warehouse / external-data syncs (data coming in) belong to the data-warehouse scout, and active external_data_failure health issues to health-checks; honor their dedupe: entries. When a prior run already covered a topic, default to edit-or-skip: the same fact twice in the inbox costs more than missing one finding for one tick.

Summarize the run in one paragraph: which pipelines you checked, which reports you authored or edited, what you remembered, and what you ruled out. The harness saves it as the run summary; future runs read it via scout-runs-list. Don't write a separate "run metadata" scratchpad entry. "Everything enabled is delivering" is a real, useful outcome.