malloydata/publisher/malloy-discover

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.

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

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.

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:

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