malloy-discover

Files (1)

• SKILL.md

SKILL.md (first lines)

<!--
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.

| 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 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)

From malloydata/publisher, MIT.

More from malloydata/publisher