malloy-discover · git:20261104.74cd8c4 · 2026-11-04 · sha256 0c9ab80ca2e7a3cf

malloy-discover git:20261104.74cd8c4B

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

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

Then /plugin install malloy-discover@markdownregistry-6tklhimhfsqzu252 in Claude Code. It installs this exact version, the one graded B 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 tools (`get_context`, `execute_query`, `search_malloy_docs`) are reachable. If they are missing and a user is present, stop and have them fix the connection (section 0 of `malloy-getting-started`, where your host has it). If you are running unattended against a local Publisher, with nobody to reconnect you, use its REST API instead: discovery, query, compile and reload all have REST equivalents, listed in the same section.

**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, start from the database instead. If you have `search_database_schema`:

1. Call it with no arguments to list environments and their connections, then with a `connectionName` to list its schemas.
2. Pass a schema name exactly as it was returned, plus a `searchQuery` describing the data you need, to rank that schema's tables. DuckDB schema names are qualified (`memory.main`), so a bare `main` is rejected.
3. Each table comes back with a `source:` line. Paste it into the model verbatim as your minimal source (step 6). Validate the whole file, not just the new line: a check that appends text to the existing model rejects a `connection.table(...)` line. Then save it and make it queryable the way your modeling workflow does before previewing it.

If the environment lists no connection, do NOT silently retry or proceed without data. Tell the user: "No model sources were found, and no database connection is configured to start one from. Add a connection to the package, then try again."

If you do not have `search_database_schema`, a connection may still exist; you just cannot list its tables. Do NOT tell the user to add one. Ask which connection and table to start from, then define a minimal source from them (step 6).

**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-model-as-you-go` 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.

**Sample before you scan.** A full-table `group_by` or `count()` over a large or warehouse-backed table is slow and can be costly. Shrink the input, not the output: a `where:` on a recent date range, or a first stage `select: *; limit: N` piped into the aggregate (the second stage sees only the selected columns, so use `where:` when you need the source's measures or joins). A `limit` on the aggregate itself still reads every row. On BigQuery a `limit` cuts time but not bytes billed; only a `where:` on the partition column cuts the bill. Run the full scan only when the sample leaves a question it cannot answer, and the user has accepted the cost. **Uniqueness is always that case:** a sample can find a duplicate key but never rule one out, so the duplicate-key and join-cardinality checks need the full table before you declare `primary_key` or `join_one`. If the user declines the scan, use `join_many`, do not declare the key as `primary_key`, and record it as unverified.

| 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 and percentiles (p25, p50, p75, p95; Malloy has no percentile function, so use the two-stage query under Example Queries). 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 (not the whole table) 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 the distribution, propose breaks from percentiles. Malloy has no `percentile` function and no scalar median, so this is a two-stage query: count the rows at each distinct value, keep a running count, and take the smallest value whose running count reaches the share you want (the nearest-rank percentile). The `order_by` in the first stage is required, and a null group would shift every percentile, so filter nulls out. On a column with very many distinct values, round it first (`round(x, 0)`) so the first stage stays small. For a measure you will keep, see `skill:malloy-gotchas-modeling` § No Scalar Median.
```malloy
run: orders -> {
  where: sale_price is not null
  group_by: sale_price
  aggregate: c is count()
  calculate: cum is sum_cumulative(c), tot is sum_window(c)
  order_by: sale_price
} -> {
  aggregate:
    min_val is min(sale_price)
    p25 is min(sale_price) { where: cum >= 0.25 * tot }
    median_val is min(sale_price) { where: cum >= 0.5 * tot }
    p75 is min(sale_price) { where: cum >= 0.75 * tot }
    p95 is min(sale_price) { where: cum >= 0.95 * tot }
    max_val is max(sale_price)
}
```

**Denormalized vs joined**: compare a pre-computed column against the real aggregate and report the match rate. Aggregates cannot go inside `where:`, so count the joined rows per key in a first stage and compare in a second:
```malloy
run: customers -> {
  join_many: orders on customer_id = orders.customer_id
  group_by: customer_id, order_count
  aggregate: actual is count(orders.order_id)
} -> {
  aggregate:
    total is count()
    match is count() { where: order_count = actual }
}
```

**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` |
| `.pbix`, `.pbip`, or a `definition/` folder of `.tmdl` files | power bi | `malloy-powerbi-review` (read it where your host has it) |
| `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.