malloy-discover · git:20260821.e32227f · 2026-08-21 · sha256 5816643285631563

malloy-discover git:20260821.e32227fA

Immutable. This exact content never changes and is served at /api/v1/blob/5816643285631563.

No tool to install first
claude plugin marketplace add https://markdownregistry.com/mp/a/art_6tklhimhfsqzu252/58166432856315636046ed47bfe57559ae2938dec8949e31f19f99bcd82f02c1.json

Then /plugin install malloy-discover@markdownregistry-6tklhimhfsqzu252 in Claude Code. It installs this exact version, the one graded A here, pinned to the commit it was graded at, and leaves it there.

---
name: malloy-discover
description: Silent data discovery for Malloy modeling. Used at Step 1 of the modeling workflow. Scans tables, columns, distributions, and relationships without user interaction. The agent builds an internal picture before presenting anything.
---
<!--
Copyright (c) Credible Data Inc.
SPDX-License-Identifier: MIT
-->

# Data Discovery (Step 1, Silent)

> **CRITICAL**: Read the model before writing ANY Malloy code. The model defines the sources, connection names, and fields. Never guess connection names.

> **Tool names** are written bare here - `get_context`, `execute_query`, `search_malloy_docs`. The exact prefixed name depends on the host surface; match each against the tools you actually have.

> **PREREQUISITE:** Make sure the Malloy MCP tools (`get_context`, `execute_query`, `search_malloy_docs`) are configured and reachable. If they are not, stop and resolve the MCP connection before continuing.

**This step is silent.** The agent does not present findings to the user yet. That happens in the next step (PROPOSE SCOPE). Silent does not mean unrecorded: append findings to your modeling workflow's `modeling-notes.md` as you go (grain proofs, key collisions, coverage cliffs, metadata drift, problems) so the scope proposal argues from a durable record rather than a reconstruction.

**Profiling goes through Malloy.** Even when the underlying engine is available directly (a `duckdb` CLI, `psql`, `bq`), run discovery queries through `execute_query`. The semantic layer under construction is the product, and grounded discovery through it is the point; profiling around it is a category error, not a shortcut: every finding would have to be re-verified through Malloy anyway.

## Tools

- **`get_context`**: Ground yourself in the package's sources, views, and fields (with their docs). Call FIRST. The sources and their join paths are the schema you build on.
- **`execute_query`**: Run ad-hoc queries to preview data, verify values, check NULLs, validate assumptions.
- **`search_malloy_docs`**: Get Malloy syntax help when needed.

## Workflow

```
1. Check for prior art signals                 → BI configs, metadata files, KPI docs, catalog
                                                 exports, READMEs, screenshots. Ask before using.
2. If BI config: read adapter reference        → Follow skill:malloy-lookml-review; read anything
                                                 else directly. Notes into modeling-notes.md
3. get_context                           → Ground yourself: sources, views, fields
4. Inspect source definitions                  → See ALL fields and join paths for key sources
5. Derive candidate joins/dimensions/measures  → Read them off the model and the data, not a suggestion tool
6. Define a minimal source if one is missing   → Just enough to run execute_query for previews
7. execute_query(query)                  → Preview data, verify values, check NULLs, check duplicates
8. search_malloy_docs(query)                    → Get syntax help when needed
9. Proceed to Step 2 (PROPOSE SCOPE)
```

**If the model has no sources defined** and no LookML files are present, do NOT silently retry or proceed without data. Tell the user: "No model sources were found. Please check that the package points at a connected data source, then try again."

**If the model has no sources defined** but LookML files ARE present (LookML-only mode), skip steps 3-7. Use connection name and table paths from the LookML review. Flag all proposals as unvalidated.

**Key principle:** Query data to verify assumptions. Don't ask the user to confirm values you can check yourself.

**Search docs proactively.** If you discover patterns that need derived/pre-aggregated sources, window functions, or unfamiliar features, call `search_malloy_docs` BEFORE writing code, not just when you hit errors.

## Query File for Discovery

**In the schema-first workflow:** Run ad-hoc queries with `execute_query`. If the source you want to preview is not yet defined in the model, define a minimal one against the connection and table so you can run previews. The real model fields are built in later steps.

```malloy
// minimal source for previewing data during discovery
source: explore is my_conn.table('schema.table') extend {}
```

**In analysis-first mode:** There is no temp file. The analysis `.malloy` file IS your working file. It grows throughout the session and becomes the input for formalizing into a model. See `skill:malloy-analyze` for that workflow.

## What to Capture

When reviewing tables and columns, capture:

### Table-Level
- All tables with row counts
- Connection name and schema (CRITICAL, never guess)
- Table roles: fact, dimension, bridge, lookup, staging, operational
- Join relationships (FK → PK mappings)
- **Sibling tables at the same grain**: where two tables share a role AND a grain, diff their columns and state why you chose one, or model both. Verifying keys, grain, and fan-out on the table you picked does not catch the richer sibling you never compared it to; a leaner twin can silently cost a whole class of question (e.g. picking a final-enrolment table over its sibling that also carried first-week and fifth-week counts made the enrolment funnel unaskable).

### Column-Level
- Primary key and foreign key columns
- Data types (watch for string dates, arrays, JSON)
- Reserved word columns that need backticking (`Date`, `Type`, `number`, `source`, etc.)
- Column cardinality and NULL rates (via `execute_query`)
- Data distributions for key numeric and categorical columns

### Data Quality
- **Check for duplicate rows** on primary keys. Run `group_by: pk, aggregate: count(), having: count() > 1` on each key table. Duplicates cause `sum()` to return nonsensical values.
- **Denormalized count columns**: beware pre-aggregated fields (e.g., `order_count` in a customer table) that may conflict with joined counts.
- **Delimited list columns**: flag string columns containing comma-separated values.

### Data-Driven Validation

**Every recommendation must be grounded in queried data, not schema inference.** During discovery, run `execute_query` to validate assumptions before proposing anything in later steps.

| What to validate | Query to run |
|-----------------|-------------|
| **Denormalized vs joined values** | Compare pre-computed columns (e.g., `customers.order_count`) against the actual joined aggregate (`count()` from `orders`). Report discrepancy rate. If >0%, flag for user decision. |
| **Candidate date fields** | When multiple date/timestamp columns exist, query both. What % of rows differ? By how much? This informs which is canonical. |
| **Numeric column distributions** | Query min, max, avg, percentiles (p25, p50, p75, p95). These inform tier boundaries and detect outliers. |
| **Categorical column cardinality** | Query distinct values. A `status` column with 5 values behaves differently from one with 500. |
| **Column usefulness** | Query NULL rates. Columns that are >95% NULL are candidates for `internal`. |
| **Join cardinality** | Query FK uniqueness: `group_by: fk_col, aggregate: row_count is count(), having: row_count > 1`. Determines `join_one` vs `join_many`. |
| **Revenue/amount columns** | When multiple money columns exist (`total`, `subtotal`, `amount`, `price`), query a sample to understand how they relate (does `total = subtotal + tax`?). |
| **Join key value compatibility** | For every proposed join, sample 5-10 actual values from each side. Check for format mismatches: abbreviations ("4th Av" vs "4 Avenue"), ordinals ("23 St" vs "23rd St"), casing, prefixes. Mismatched values mean the join won't work even if column names match. |
| **Mixed-grain rows** | For each key table, run top-N and bottom-N by primary metric. Look for summary/aggregate rows mixed with detail data (e.g., "System Total" rows in a station-level table). These corrupt measures if not filtered out. |

**Never assume from column names.** Always query the data to confirm. A column named `total` could include or exclude tax. A `status` column could have unexpected values. A FK could have orphaned references.

### Example Queries

**Tier boundaries**: query distribution, propose breaks from percentiles:
```malloy
run: orders -> {
  aggregate:
    min_val is min(sale_price), p25 is sale_price.percentile(25)
    median_val is sale_price.percentile(50), p75 is sale_price.percentile(75)
    p95 is sale_price.percentile(95), max_val is max(sale_price)
}
```

**Denormalized vs joined**: compare pre-computed column against real aggregate, report match rate:
```malloy
run: customers -> {
  join_many: orders on customer_id = orders.customer_id
  aggregate:
    total is count()
    match is count() { where: order_count = count(orders.order_id) }
}
```

**Canonical date**: when multiple date columns exist, check how often they differ:
```malloy
run: orders -> {
  aggregate:
    total is count()
    same_date is count() { where: created_at::date = submitted_at::date }
    max_gap_days is max(days(created_at to submitted_at))
}
```

**Revenue columns**: when multiple money columns exist, verify their relationship:
```malloy
run: orders -> {
  aggregate:
    total_eq_parts is count() { where: abs(sale_price - (subtotal + tax)) < 0.01 }
    total is count()
}
```

### Schema Shape
- Is this a star/snowflake schema (use base + joined source layers) or normalized/ER-style (may need 3-stage pattern)?
- Combined vs split tables: prefer filtered/split tables over combined when both exist.

## Computed Source Detection

Flag potential computed sources when:

1. **Grain mismatch**: the analytical scope requires a grain that no physical table provides (e.g., customer-level metrics from an order-grain table)
2. **Repeated aggregation patterns**: the same GROUP BY + aggregate pattern would be needed in multiple analyses
3. **Cross-entity aggregations**: the model or the data implies cross-entity aggregations that require a pre-aggregated entity

## Prior Art Detection

Check for prior art signals at the start of discovery. If a signal is found and the user confirms, **you MUST read** the corresponding reference skill and follow its instructions.

| Signal | Source Type | Reference to Read |
|--------|------------|-------------------|
| `.lkml` files in project or subdirectories | lookml | `skill:malloy-lookml-review` |
| `dbt_project.yml` in project or parent dirs | dbt | dbt review (future) |
| Dataset metadata (`metadata.json` and friends), metrics/KPI docs, catalog exports, data READMEs, existing SQL or report files, dashboard screenshots | direct | none: read it yourself (see below) |

**Prior art is broader than BI tool configs**, and the third row is where most of it lands. A `metadata.json` often carries column-level ground truth worth reconciling against; a metrics doc or a dashboard screenshot tells you which definitions the business already uses. None of it needs a reference skill: read it, treat its claims as hypotheses to verify by query (metadata drifts: trust the data, use the docs for descriptions), cite it in later proposals, and record which sources were used.

The reference handles inventory, classification, and produces prior-art notes. Record those notes in `modeling-notes.md`, then continue with normal discovery below.

**If DB connection available (LookML + DB mode):**
- Read the model and run `execute_query` as normal
- Use prior art as additional context, not a replacement for data validation
- **The LookML connection name is NOT the Malloy connection name.** Always use the connection name from the model.

**If no DB connection (LookML-only mode):**
- Skip the model-read and `execute_query` steps
- Use connection name and table paths extracted from prior art source files
- Flag all proposals in Steps 2-4 as **unvalidated**
- Proceed directly to Step 2 (PROPOSE SCOPE)

**Prior art findings enhance discovery, they don't replace it.** When a DB connection is available, always validate assumptions against the actual data.

## After Discovery

Do NOT present findings to the user yet.

## Done

Step complete. Output: discovery findings (internal: tables, columns, relationships, data quality, prior art). Continue to the next modeling step (see your modeling workflow).

## Verify Source Joins

When reading joins off the model or the data, watch for `join_many` where the actual relationship is many-to-one. Always verify cardinality. Prefer `join_one` when each row in the primary table matches at most one row in the joined table.