signals-scout-data-warehouse

Three cheap reads cold-start a run:

• scout-scratchpad-search (text=warehouse) — durable steering: the watchlist of high-value sources/schemas and their freshness baselines, noise: / addressed: / dedupe: entries gating re-reports, plus report: / reviewer: entries pointing at the open report for a source/schema and who owns it.
• scout-runs-list (last 7d) — what prior warehouse runs found and ruled out.
• scout-project-profile-get — products in use and integrations. Warehouse tables are not events, so the profile won't enumerate them; it only tells you whether the warehouse is in use at all.
• inbox-reports-list (search=source/schema name, ordering=-updated_at) — the reports already in the inbox. A contradiction on a source/schema 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=data_warehouse — you'd miss every report you authored.

Then take the import roster. Sweep with SQL over the metadata system tables, drill down with REST. A large project can have thousands of schemas — paginating external-data-schemas-list (50/page) is hundreds of pages, so do the bulk scan in one query against system.source_schemas instead:

-- Everything not cleanly Completed, plus the silent-staleness candidates, in one pass.
SELECT name, source_id, status, sync_type, last_synced_at,
       dateDiff('hour', last_synced_at, now()) AS hours_since_sync
FROM system.source_schemas
WHERE should_sync AND deleted = 0
  AND (status != 'Completed'
       OR last_synced_at < now() - INTERVAL 48 HOUR)  -- tune the staleness floor per cadence
ORDER BY status, hours_since_sync DESC

system.source_schemas carries should_sync, status, sync_type, last_synced_at, latest_error, source_id — the fields you triage on. Group the Failed rows by source_id to find cascades (one source whose tables all fail at once is one source-level finding, not N). What the system table does not have: sync_frequency (the promised cadence) and the source-level status / latest_error. Get those from REST, but only for the handful of candidates the SQL sweep surfaced:

• external-data-schemas-list (search=<schema name>) — the one candidate's sync_frequency, incremental_field, full latest_error. Footgun: never call it unfiltered to page the whole project, and never use external-data-sources-list for the schema sweep — each source there embeds all its schemas, so the response is many MB on a large project.
• external-data-sources-retrieve {source_id} — the source's connection-level status (Error/Running/…) and latest_error, to confirm a cascade is a broken connection rather than N independent table failures.

If Failed schemas span many sources in the same window, suspect a platform/warehouse incident — one finding naming the shared cause.

Patterns to watch — starting points, not a checklist.

A source at status: Error/Failed breaks every armed schema under it — credentials expired/rotated, host unreachable, SSH gateway down, integration deleted. This is the highest-blast-radius shape: report it once at the source level, name the affected armed schemas as the blast radius, and quote the source latest_error (an auth 401/403, an SSH error, a "matching query does not exist"). external-data-sources-retrieve {id} gives the full per-source picture when you need it.

For each armed Failed schema, the latest_error names the root cause and decides who fixes it: authentication failed/401 (creds), column "X" does not exist / does not have a column named (schema drift), Primary key required / primary keys ... not unique (incremental/PK misconfig), replication slot / publication / wal_level (CDC prerequisites — e.g. a slot invalidated for exceeding max reserved size), timeout / query_wait_timeout / QueryTimeoutException (an incremental field with no index, or an overloaded source), Schema exceeds row limit (billing). Date the onset from advanced-activity-logs-list (scopes for the source/schema) and quantify the gap (intervals missed × sync_frequency). A schema stuck in Running with a last_synced_at hours old is an orphaned job — the same growing-gap finding, not a healthy state.

The active-failure view does not flag this — it's where you earn your keep. The SQL sweep already surfaced armed Completed schemas with a stale last_synced_at (a real DateTime on system.source_schemas, so dateDiff('hour', last_synced_at, now()) works directly — no string parsing). Score each candidate's gap against its promised cadence, which you pull per-candidate from REST sync_frequency:

• A tight cadence gone stale is the real signal — a 1hour / 6hour incremental whose freshness is > ~3× its cadence with no Running run in flight is effectively broken behind a green status (a silently disabled trigger or stuck scheduler). Confirm the source status, quantify the gap, file a report.
• Don't confuse abandoned with broken. An armed schema that hasn't synced in months — a full_refresh one-shot that was never on a recurring cadence, or a table under a source the team quietly stopped using — is most likely abandoned, not an active regression. That's a P3 cleanup/hygiene note (or a noise: entry once confirmed), not a P1/P2 gap. The shape that earns a report is a schema recently healthy that just fell behind its cadence, not one stale since last year.

For sync_type: webhook schemas, the bulk-sync safety net can keep the status Completed while the push channel is silently dead, so real-time data lands hours late. Check the source with external-data-sources-webhook-info-retrieve {source_id}: exists: false (never registered or deleted), external_status.error set (remote revoked/deleted it), or external_status.status ≠ enabled (remote disabled it after delivery failures) each mean the push path is down. This never shows on external-data-schemas-list.

records_completed / table row_count collapsing across consecutive runs while the source stays healthy and event ingestion holds points at a filter/incremental-cursor/config change, not an outage. Cross-check last_updated_at and the activity log before calling it unexplained; an execute-sql count() over the warehouse table (by ingested day) confirms the cliff.

Sweep materialized views the same SQL-first way: SELECT name, status, last_run_at FROM system.data_modeling_views WHERE is_materialized = 1 AND deleted = 0 AND status = 'Failed'. For a failing view, view-run-history {id} is the run trail and view-list carries the latest_error. A materialized view Failed is usually a HogQL/data problem in the view itself (missing table, type mismatch) — surface it and route to view diagnosis rather than deep-diving. A healthy-but-never-queried materialized view is an optimization-lane waste finding (below), not an anomaly.

Run this sweep only when the integrity lane is quiet — never while a P1/P2 import gap is live (filed this run, edited this run, or still open in the inbox). The usage signal is the query_log table (available on every project): one row per executed query with query (the SQL text), query_duration_ms, created_by, endpoint, read_bytes, memory_usage, cpu_microseconds, status. It covers app, API, and named background traffic, and read_bytes is the cost signal duration hides — a query shape can look mild on wall-clock while reading terabytes. Do not use the query completed analytics event as the substrate — that is PostHog-internal app telemetry most projects don't capture.

Four hygiene filters on every probe, all load-bearing: query_duration_ms > 5000 (the slow tail — the full stream is millions of rows and probes over it time out; the matview waste check below is the one deliberate exception), endpoint != '' (rows with no endpoint are unattributable internal machinery — ~10× the scan cost and pure noise; what remains splits cleanly by endpoint class: interactive /api/.../query/, cache warming, cohort calculation, endpoint runs), query != '' (structured insight nodes — Trends/Funnels/Lifecycle, even over warehouse tables — log empty query text; empty rows all collapse into one meaningless hash bucket and can't be attributed to a table), and status = 'QueryFinish' (the log includes exception and timeout rows that satisfy every duration and recurrence gate — a shape that recurs as failures needs error diagnosis, not a materialization suggestion; if you spot one, that's a differently-framed finding, never this lane's). Always pair every event_time bound with a matching event_date bound — the table is partitioned by month on event_date, and an event_time predicate alone prunes no partitions, so a nominally 1-day probe can scan every retained month. Coverage caveat: because structured nodes log empty text, this lane sees the SQL-textual workload only — a warehouse table read exclusively through structured insights is invisible here; hedge accordingly. Start with a 1-day window and widen only if it's fast. You suggest, never conclude — every finding is a hypothesis a human validates.

Two probes:

Hot warehouse tables. Discover burn from the query side — match every sizable table name into the slow tail. Do not rank candidates by row_count and check the top N: the biggest tables are usually batch-fed and query-silent, so size-first ranking misses the hot tables entirely. Two steps, because the multi-pattern search needs constant needles:

1. Fetch the roster: SELECT name, external_data_source_id FROM system.data_warehouse_tables WHERE deleted = 0 AND (row_count > 1000000 OR row_count IS NULL) AND length(name) > 8 (the row_count floor bounds the needle list, but keep the NULL rows — direct-access Postgres/MySQL/Snowflake tables never get a row count and can be the most expensive workloads; the length guard stops short generic names false-matching). Needle forms matter: self-managed tables, direct-access tables, and matview backing tables are referenced in queries by their raw name, but warehouse-synced tables appear in HogQL as a dotted name — storage myprefix_googleanalytics_devices is queried as googleanalytics.myprefix.devices. For rows with external_data_source_id set, join system.data_warehouse_sources for source_type/prefix and add the dotted form (sourcetype.prefix.table, lowercased; prefix segment omitted when empty) as a needle alongside the raw name — a flat-only roster silently misses every synced table. (system.source_schemas.name values are source-side and will not match query text either.) The multiSearch* family accepts at most 2^8 needles: if the roster exceeds ~250, raise the row_count floor, or split the needles into batches and run the sweep once per batch, merging the results.
2. Embed the names literally as <NAMES> in one pass:

SELECT tbl, count() AS runs, uniqIf(cb, cb != 0) AS users,
       round(quantile(0.5)(d)/1000, 1) AS p50_s,
       round(sum(d)/60000, 1) AS total_min,
       round(sum(rb)/1e9, 1) AS read_gb
FROM (
  SELECT arrayJoin(arrayFilter(t -> positionCaseInsensitive(q, t) > 0, <NAMES>)) AS tbl,
         q, d, cb, rb
  FROM (
    SELECT query AS q, query_duration_ms AS d, created_by AS cb, read_bytes AS rb
    FROM query_log
    WHERE event_date >= today() - INTERVAL 1 DAY
      AND event_time >= now() - INTERVAL 1 DAY
      AND query_duration_ms > 5000 AND endpoint != '' AND query != ''
      AND status = 'QueryFinish'
      AND multiSearchAnyCaseInsensitive(query, <NAMES>) = 1
      AND positionCaseInsensitive(query, 'multiSearchAny') = 0
  )
) GROUP BY tbl HAVING users >= 2 OR max(cb) = 0
ORDER BY total_min DESC LIMIT 15

Performance footguns, all hit in practice: a plain arrayJoin over the roster crossed with positionCaseInsensitive times out (it duplicates every KB-sized SQL text per name — multiSearchAny first, then split only the matching rows); dropping the endpoint != '' filter roughly 10×es the scan; and your own sweep query contains every needle, so it would match itself on later runs — the positionCaseInsensitive(query, 'multiSearchAny') = 0 line in the template is that self-exclusion (it also drops the rare legitimate query using multiSearchAny; acceptable). Attribution footgun: matching is substring-based — a roster name that is a substring of another (…_month vs …_month_recalc) double-counts, and a name inside a comment or string literal counts as usage. The cost columns are also query-wide totals: the arrayJoin credits a query's full read_bytes/duration to every table it matches, so a cheap dimension joined to a fat fact table inherits the fact table's burn and multi-table joins count the same cost once per table. The sweep is candidate discovery, never the filing bar: read actual query samples for a candidate before filing, attribute overlapping names from the samples, not the sweep counts, and present filed numbers as query-shape burn from the samples — never as table-attributed cost. Rank by total_min and read_gb — they disagree, and the read_gb monsters (a table reading tens of TB a day behind a moderate wall-clock) are the highest-value findings. Cache the hot list + baselines as pattern:data_warehouse:opt-watchlist. A table with recurring multi-user slow queries (e.g. 165 runs / 17 users / p50 11s / 11 TB read in one day) is a materialization candidate. Before suggesting, check system.data_modeling_views — if a matview already covers the shape, the finding is "queries bypass the existing view", not "build a new one".

Recurring slow query shapes. Group repeated expensive queries by a prefix hash and rank by total burn:

SELECT toString(cityHash64(substring(query, 1, 500))) AS qhash,
       count() AS runs, uniqIf(created_by, created_by != 0) AS users,
       any(endpoint) AS ep, uniq(endpoint) AS n_eps,
       round(quantile(0.5)(query_duration_ms)/1000, 1) AS p50_s,
       round(sum(query_duration_ms)/60000, 1) AS total_min,
       round(sum(read_bytes)/1e9, 1) AS read_gb
FROM query_log
WHERE event_date >= today() - INTERVAL 1 DAY
  AND event_time >= now() - INTERVAL 1 DAY
  AND query_duration_ms > 5000 AND endpoint != '' AND query != ''
  AND status = 'QueryFinish'
GROUP BY qhash
HAVING runs >= 5 AND (users >= 2 OR max(created_by) = 0)
ORDER BY total_min DESC LIMIT 10

Hash the prefix (substring(query, 1, 500)), not the full text — hashing multi-KB SQL times out, and the prefix groups shapes that differ only in tail literals (accept the slight over-grouping). The endpoint column classifies each shape's burn: interactive (/api/.../query/), insight cache warming, cohort calculation, endpoint runs — each implies a different fix (materialize the underlying model, simplify the cohort definition, cache the endpoint). But any(endpoint) is an arbitrary pick — when n_eps > 1 the shape runs from several origins, so fetch the candidate's per-endpoint split (one bounded per-candidate GROUP BY endpoint query) before choosing the remediation framing. The shape that matters is high runs × high users × real burn — a shared query everyone (or the platform, on the team's behalf) pays for repeatedly; a real example: a cohort-calculation shape at 6,639 runs / 352 TB read in six hours. The HAVING clause applies that multi-user bar inside the aggregate, before the LIMIT — otherwise ten single-analyst top-burn rows can crowd every shared shape out of the top 10. users counts real users only (uniqIf(created_by, created_by != 0) — created_by = 0 is the background sentinel, not a person, so one analyst plus background traffic is still single-user); the max(created_by) = 0 arm keeps genuinely all-background named workloads (cohort calculation, cache warming) eligible. Single-user interactive rows are one analyst's exploration — skip them. Recurring means multi-day: before filing any shape or table, re-check the candidate over 7 days with uniq(event_date) AS active_days (one bounded per-candidate query) and require ≥3 active days — a burst inside a single day is an incident or ad-hoc analysis, not a pattern. Read a candidate's text with a second, per-hash query — WHERE toString(cityHash64(substring(query, 1, 500))) = '<qhash>' LIMIT 1 plus the same status/duration/endpoint filters and bounded event_date + event_time windows (the aggregate emitted the hash as a string, and an unbounded lookup full-scans history just to fetch one sample). Use the 7-day recheck window for the lookup, not a re-derived now() - INTERVAL 1 DAY — now() shifts between queries, and a wider bounded window costs little while never missing the candidate's rows; footgun: selecting a substring() sample column inside the aggregate can fail on multi-byte characters ("Type is not JSON serializable: bytes") — always fetch text separately. A shape that touches a warehouse table gets the materialization framing; an events-only shape can still earn a suggestion (a saved view, a narrower date-range default) when the burn is large. But a shape that is a product default — an SQL-editor starter query or docs example run by hundreds of distinct users (e.g. SELECT count(*) from persons) — is a product/engine finding, not a modeling gap: note it in memory, don't file it.

Matview waste. The inverse: is_materialized = 1, healthy, but zero query_log rows match its name over 14+ days — a scheduled rebuild the team pays for with no reader. Three candidacy gates before any reader check:

• A full reader window since materialization — gate on the earliest materialization run (SELECT min(created_at) FROM system.data_modeling_jobs WHERE data_modeling_view_id = '<id>' older than 14 days), not the view's created_at: an old saved query newly materialized has had no reader window either.
• An active rebuild schedule — the system table carries no cadence, so view-list the candidate and drop it if sync_frequency is never (a paused materialization incurs no recurring cost; stale-and-paused is at most a hygiene note, not waste).
• No downstream models — another saved query can consume the view inside its own definition: interactive traffic then mentions only the downstream name and rebuild reads are endpoint-less, so the reader check sees nothing while retiring the view would break the chain. One bounded query catches it — SELECT count() FROM system.data_modeling_views WHERE deleted = 0 AND name != '<view>' AND positionCaseInsensitive(toString(query), '<view>') > 0 — any hit disqualifies the waste framing (the view has readers; they're models).

The reader check itself deliberately drops the query_duration_ms > 5000 filter: the whole point of a useful materialization is that its reads are fast, so the slow-tail filter would make every well-served view look unread. Keep endpoint != '' (excludes its own rebuild machinery), keep bounded event_date + event_time windows, and self-exclude with positionCaseInsensitive(query, 'positionCaseInsensitive') = 0 — the audit query necessarily contains that function name (it's how it searches for the view name) while real readers select from the view directly; without this, the previous run's audit counts as a reader and permanently masks the finding. Exclude on the function name, not on 'query_log' — a legitimate user query that joins the view against query_log is a real reader and must keep counting. That's a P3 cost-hygiene suggestion to confirm-and-retire; still hedge hard — a structured insight reading the view logs empty query text and is invisible to this check, and an external consumer could read it through a path that logs oddly — never assert.

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:data_warehouse:watchlist — "High-value imports: source Stripe (Postgres CDC, 12 armed schemas), schema public.orders (1hour, ~2M rows, the revenue join), webhook schema stripe.charges. Check these first."
• key pattern:data_warehouse:orders-freshness — "public.orders syncs hourly, baseline freshness < 90 min, ~2M rows. Only a multi-hour staleness or a Failed status matters."
• key noise:data_warehouse:onboarding-mirror-sources — "Sources labelled onboarding-*, posthog-<customer>, inc-* are throwaway demo/incident mirrors that fail by design — never findings; confirm by label and skip."
• key dedupe:data_warehouse:stripe-cdc-slot — "Filed CDC replication-slot invalidation on source Stripe 2026-06-30 (12 schemas dead, slot exceeded max reserved size). Skip unless the error class changes or it recovers then breaks again." One stable key per issue — update it in place, don't mint a dated variant.
• key addressed:data_warehouse:hubspot-billing-limit — "Team aware: Hubspot schemas capped at the row quota on purpose. Don't re-file BillingLimitReached."
• key report:data_warehouse:stripe — "Report 019f0a96-… covers the Stripe source-level Error cascade. Edit it (append_evidence with the fresh numbers / blast radius) while it persists and the report is still live; if it was resolved and the source later re-breaks, that's a fresh report."
• key reviewer:data_warehouse:stripe — "Stripe source owned by alice (GitHub login) — route its reports there."
• key pattern:data_warehouse:opt-watchlist — "Hot tables by daily query_log burn: prod_postgres_invoice_with_annual (165 slow runs / 17 users / p50 11s / 11 TB read), iwa_summary_customer_month (73 / 5 / 18.6s / 98 TB read), … Recheck weekly, not every run."
• key report:data_warehouse:opt-invoice-annual — "Report 019f… suggests materializing the recurring prod_postgres_invoice_with_annual join (2026-07-15: 165 slow q/day, 17 users, 11 TB read). Edit with fresh numbers at most every few runs while live; on decline or fix, write addressed: and stop."
• key addressed:data_warehouse:opt-usage-report-view — "Team declined materializing the usage-report query (2026-07-10, acceptable cost). Never re-file unless burn grows ~3×."

By run #5 you should know the project's high-value imports and their freshness baselines, which sources are throwaway mirrors, the optimization watchlist and what's already been suggested — so a real import contradiction or a new burn hotspot 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:data_warehouse:<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 source/schema name (ordering=-updated_at), not a broad word like warehouse. Also cross-check health-issues-list: the active warehouse failures (external_data_failure) may already be surfaced by the health-checks scout — your distinctive lane is the silent gaps the active-failure summary misses (staleness behind a green status, broken webhook channels, row cliffs).
• Edit (scout-edit-report) when a still-live report already covers the same import issue — a source still in Error, a schema still stale, a webhook channel still dead. Add the fresh numbers with append_evidence (widening gap, growing blast radius), 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. When a health-checks external_data_failure report already covers the same source/schema, only author (or edit your own) with a material new angle — a quantified growing gap, a broader blast radius, an onset tied to a deploy.
• Author (scout-emit-report) only when nothing live covers it. A good report names the source/schema and its id, states the contradiction (status vs freshness vs cadence), quantifies the gap (intervals or hours missed, rows behind), names the error class from latest_error, and dates the onset — ideally tied to a config edit or deploy from the activity log. Set priority (P0–P4) + priority_explanation — a source-level Error / all armed schemas under a source failing / a stalled ingestion-critical table is P1, a single Failed schema / confirmed growing gap / broken webhook channel is P2, billing limits / unused materialized views / hygiene 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:data_warehouse:<slug>); left empty the report reaches no one. A warehouse import gap is a config/credential/remote-side investigation a human confirms, not a one-line code change → actionability=requires_human_input and repository=NO_REPO (NO_REPO is what stops priority+reviewers from spawning a pointless repo-selection sandbox). After authoring, write the report:data_warehouse:<slug> pointer with the report_id so the next run edits instead of duplicating.
• Remember if below the bar but worth carrying forward (freshness drifting inside the noise band, a single self-recovered Failed run, records_failed creeping); skip with a one-line note if a noise: / addressed: / dedupe: entry or an existing report already covers it.
• Optimization reports are P3, capped, and evergreen-deduped. At most 1–2 new opportunity reports per run, and lane-2 reports never crowd out an integrity finding. A good one names the table or query shape, gives the window's numbers (runs, users, p50, minutes burned, bytes read — the resource cost is the persuasive half), and attaches one concrete suggestion (materialize this shape as a view, point the dashboard at the existing matview, simplify the expensive cohort, retire the unread matview) — actionability=requires_human_input, repository=NO_REPO. Opportunities are evergreen (the same slow query is slow every run), so the discipline is strict: file once under report:data_warehouse:opt-<slug>, edit with fresh numbers at most every few runs while live, and once addressed: exists never re-file unless the burn changes materially. Derive <slug> from a stable identifier — the table name for hot tables, the view name for matview findings, the prefix qhash for query shapes — never from prose, so the next run recomputes the same key and the dedupe holds.

Summarize the run in one paragraph: which sources/schemas 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. "Every armed import is fresh and Completed on schedule" is a real, useful outcome.