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

Canonical date: when multiple date columns exist, check how often they differ:

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:

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.

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.