--- name: data-discovery description: Use this skill after the question is framed and BEFORE any analytical planning or querying. Required whenever you're about to write SQL, build a metric, or trust data you haven't personally inspected for this analysis. Discover and document schema, grain, freshness, provenance, and known data hygiene issues for every table the analysis will touch. Trigger on phrases like "let me query", "I'll pull from", or any move toward data access — even when working with a database you've used before, because schemas drift, ETL jobs change, and the table you used last quarter may not be the right one today. --- # Data Discovery You don't query a table you haven't inspected for the analysis you're running today. Familiarity is not freshness. ## What this skill catches - **Grain misunderstandings.** The most common analytical bug is treating a table as if one row meant something other than it does — counting "users" when rows are "user-sessions", or "orders" when rows are "order-line-items". - **Stale data.** Treating a table as up-to-date when there's a 24- or 48-hour ETL lag, or when one upstream feed has been silently failing. - **Definition drift.** A column that meant one thing last quarter (or in last quarter's analysis) means something else now — definitions changed, status codes were repurposed, soft-deletes were introduced. - **Quiet hygiene issues.** NULLs you didn't expect, dupes that double-count, encoding quirks, status flags whose values you've never enumerated, retroactive updates to "settled" rows. ## What this skill enforces For every table the analysis will touch, document in writing: 1. **Schema** — every column the analysis depends on, with type, nullability, and a one-sentence semantic note. (`status` is one of {active, churned, paused}; `paused` was added 2026-02.) 2. **Grain** — what is one row? State this explicitly. *One row = one user-session* is different from *one row = one user-session-event*. The most common analytical bug starts here. **If the table is pre-aggregated** (a cube, snapshot, vendor rollup, or warehouse view that has already collapsed to coarser grain), say so out loud and document the baked-in choices: which baseline period is hard-coded, which window length the metric uses, which cohort definition was applied upstream. Pre-aggregation removes degrees of freedom from the analysis — see `framing-the-question/SKILL.md` §"When the data is (or might be) pre-aggregated" for what to do if the framing asked for a metric the cube can't produce. 3. **Provenance** — which upstream system this table comes from, who owns it, what its update cadence is, when the next refresh is expected. 4. **Freshness** — when was the latest row written? Run a freshness check (e.g., `SELECT max(updated_at)`) and record what you saw. Note whether the data is through end-of-yesterday, or has a 24-hour lag, or is partial for the current period. 5. **Known hygiene issues** — NULL counts on key columns, duplicate detection on natural keys, encoding/timezone quirks, soft-delete flags, retroactive-update behavior, status-code enumerations. 6. **Sample-metric sanity check.** Before handing off to planning, sketch the planned headline metric's formula and apply it to a row whose result *cannot* contaminate prediction — choose a row from **outside the headline's scope** (a different time period, a different segment, a known-discardable cohort). The point is to confirm column semantics combine the way you think they do, not to preview the answer. If the resulting number isn't interpretable on its face, return to schema documentation and resolve the ambiguity now — not in planning. (See "What NOT to look at" below for what makes a row safe to sample.) Save discovery to `.analytics/discovery/.md`. Reuse for future analyses, but **re-verify freshness and grain on every analysis** — a discovery doc older than the latest data refresh is no longer trustworthy. ## What NOT to look at during discovery Discovery inspects *structure*, not the *values* the headline metric will eventually report. LLMs have perfect context recall: any number you see during discovery flows directly into the prediction step, and the keystone discipline of `predict-then-query` degrades from "calibrated forecast" to "transcription of what I just read." Treat headline-metric cells as radioactive. - Sample column **types, NULL patterns, sentinel values, format quirks, and relationships** — not the headline metric's output. - Do **not** read individual rows from the headline window/cohort if those rows expose the metric's value (e.g., `SELECT * FROM weekly_snapshot WHERE week = 'W23' LIMIT 5` when the headline is "W23 cohort revenue change"). - When you must validate a column's behavior (e.g., "is `current_period_count` ever 0 or negative?"), prefer **aggregate** queries (`COUNT`, `MIN`, `MAX`, `COUNT(*) FILTER (WHERE ...)`) over row-level reads, and run them across the **whole table or a non-headline slice** rather than the headline window. - For sample-row reads that are unavoidable, choose rows from outside the headline's scope (different period, different segment) and, where practical, project away the columns that combine into the headline metric. - If you slip and see a headline value, write that down as a path-decision (`decision-type: "discovery-leak"`) and reduce the prediction's stated confidence accordingly — the prediction is no longer blind, and pretending otherwise corrupts the calibration record. ## Logging Log this skill's activation: ```bash python3 .analytics/db.py log-skill \ --skill data-discovery \ --question-slug \ --completed ``` If a hygiene issue or grain quirk is severe enough to shape the analytical plan (e.g., choosing one table over another, dropping a column, picking a deduplication strategy), log it as a path decision in advance: ```bash python3 .analytics/db.py log-path \ --question-slug \ --decision-type "data-handling" \ --chosen "" \ --alternatives "" "" \ --rationale "" \ --pre-specified ``` ## Boundary cases **"I've used this table dozens of times."** Familiarity is not freshness. Schemas drift, ETL jobs get rewritten, semantics change. Re-verify schema, grain, and freshness on every analysis — even briefly. The cost of a five-minute check is much less than the cost of a re-run. **"The table has 200 columns."** Document only the columns the analysis touches, not the whole schema. The point is to be honest about what you depend on, not to recreate a data dictionary. **"There are no formal docs."** Then the discovery doc you write *becomes* the docs for this analysis. That's fine. The point is that someone (you, today) has personally verified what each column means for this run. **"This is exploratory — I'm just looking around."** Pure exploration doesn't trigger this skill. The skill triggers when exploration is about to feed a headline query. If you're inspecting to learn, no doc needed. If you're inspecting to query, document. ## Handoff When discovery is documented and saved, hand off to `analytical-planning/SKILL.md`. ### Back-edge: when discovery invalidates framing Discovery sometimes surfaces a constraint that invalidates a framing choice — the table is pre-aggregated to a coarser grain than the question assumed, the freshness lag rules out the planned time window, or a column doesn't carry the semantics framing assumed. **When this happens, return to framing-the-question before continuing to planning.** Do not silently widen, narrow, substitute, or proxy the metric to fit the data; the audit trail breaks and the question doc no longer matches the work. Indicators that you should hand back, not forward: - The framing's `Metric` formula requires a grain that doesn't exist in any available table. - The framing's `Time window` requires data that hasn't yet landed (freshness lag) or that has been overwritten (retroactive updates). - A pending-flagged assumption from framing was wrong, and the corrected assumption changes the population, comparison, or threshold. - The only available source is a pre-aggregated snapshot/cube whose baked-in baseline differs from the framing's `Comparison`. The re-framing procedure (append a Revisions entry to the question doc, update affected fields in place, log a `re-frame` path-decision, re-run `show-question`) lives in `framing-the-question/SKILL.md` §Re-framing after discovery. Run it, then re-enter discovery for any new tables the revised framing pulls in.