name: malloy-define description: Propose scope, a source plan and field definitions for a Malloy model. Which tables and questions, which sources at what grain, then renames, dimensions and measures, each backed by querying data. metadata: version: "1.0.2"

<!-- Copyright (c) Credible Data Inc. SPDX-License-Identifier: MIT -->

Propose sources and definitions

This skill covers three consecutive activities when building or extending a Malloy semantic model:

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.

• Propose scope: which tables and which questions the model is for.
• Propose sources: the architectural blueprint (which sources, what grain).
• Propose definitions: the specific fields per base source (renames, dimensions, measures).

Both happen in conversation. Propose, let the user confirm or adjust, then carry the confirmed plan forward into the actual .malloy model. There is no separate plan-file store: keep the source plan and field proposals in the conversation, and write the model itself when the user has confirmed. See your modeling workflow for the broader picture.

Read the existing model first so you propose against what is really there. Use get_context with a plain-English description to inspect the current sources and fields and find the most relevant existing sources. Confirm the scope (below) before proposing the source plan.

Propose the analytical scope

When: after you have read the package's sources and fields and looked at the data distributions. Goal: present what you found, recommend one analytical focus, and let the user pick. Query the data with execute_query for row counts and data-quality problems, and record the proposal and the user's decision in your modeling workflow's modeling-notes.md.

Scope is "which questions", not just "which tables". A model aimed at recommendation looks different from one aimed at catalog analysis over the same tables. A single A/B/C question about table inclusion is the source-plan question asked too early.

Present four things:

1. A table summary with rows, columns, role and key relationships. Roles: Fact (events you measure: orders, sessions), Dimension (entities you slice by: customers, products), Bridge (many-to-many links: order_items, tags), Operational (ETL, staging, audit; not analytical).
2. 2-3 analytical focuses, each with the tables it covers (for example "Order Analysis: revenue, trends, product performance"), and one recommendation with reasons. Say what kind of model this is and what that rules out. A dataset of events supports trends over time. A dataset of entities with cumulative counters cannot do period-over-period analysis however well it is built ("release dates are 98% null, so this model cannot do calendar analysis"). The user should learn what the model will never answer before choosing.
3. Tables to skip, with reasons: operational tables, staging copies of a table you model, and pre-aggregated summaries (compute fresh in Malloy; a pre-aggregated source ignores your filters, see skill:malloy-gotchas-modeling).
4. Options the user can answer in one word. If your host has an ask_user tool, put the options in that card and nowhere else, with the evidence and your recommendation as prose. Otherwise give lettered one-line options (label, tables, key questions), mark the recommended one, and invite a mix ("A plus suppliers").

Be opinionated: recommend one option clearly. With 20+ tables, group them by domain and focus on the most relevant cluster. Show evidence (row counts, relationship density) and flag data-quality problems you saw ("orders has about 3 percent duplicate rows on order_id").

After the user confirms, restate the confirmed scope (connection and schema, tables in scope with row count and role, analytical focus in one line, deferred tables with reasons) and record it in modeling-notes.md. Then continue with the source plan.

Propose a source plan

Goal: Propose the full source architecture for the tables in scope.

Base sources

One base source per table in scope. For each, specify:

Computed sources

Computed sources are created from queries, not physical tables. Propose them 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 plus aggregate pattern would be used in multiple places.
3. Cross-entity aggregations: inspecting the model and querying the data shows that an aggregate rolled up to a different entity would be reused.

For each computed source, explain:

Dependencies

Show which sources depend on which:

customers (physical) ← user_order_facts (derived, sources from orders)
orders (physical) → user_order_facts (derived)
products (physical): independent

Deferred sources

List sources considered but not included, with reasoning:

• order_items: bridge table, defer until line-item analysis is needed.
• monthly_product_facts: derived, defer until product trend analysis is requested.

User interaction

The user will:

• Confirm the source plan as-is.
• Add missing sources (physical or derived).
• Remove unnecessary sources.
• Validate grain assignments.
• Defer sources to later iterations.

Once the source plan is confirmed, carry it forward into the definitions step below. Keep the confirmed map in the conversation rather than persisting it to a separate file.

Propose definitions

Goal: Propose specific fields per base source with data evidence, working from the confirmed source plan.

For each base source

Present a table of proposed fields.

Renames (schema cleanup):

Dimensions:

Data-driven tiers: For bucketed dimensions like order_size, always derive boundaries from the actual data distribution (percentiles, natural breaks, clustering). Query min, max, p25, p50, p75, p95 and propose boundaries based on the distribution. Malloy has no percentile function; use the two-stage nearest-rank query in skill:malloy-discover § Example Queries. Show the evidence so the user can confirm or adjust. Never use arbitrary hardcoded thresholds unless the user explicitly provides them.

Measures:

For each computed source

Show the source query and additional fields.

`user_order_facts`, derived from orders grouped by customer_id:

Additional dimensions on top:

Business logic questions

Flag decisions the agent can't make from data alone. Be specific and data-grounded:

Q1: Your orders table has both created_at and submitted_at. 87% of rows have them within 1 minute, but 13% differ by 1-3 days. Which should be the canonical order date?

Q2: I'm proposing order_size tiers based on the data distribution: small (<$35, below p25), medium ($35-$150, p25-p75), large (>$150, above p75). Do these data-driven breaks work for you, or do you have specific business thresholds?

Q3: The status column has 5 values. Should "cancelled" orders be excluded from revenue calculations, or included with a separate measure?

Priority ranking

Group proposals into:

• Must-have: core metrics that every analyst needs (counts, sums, primary dimensions).
• Nice-to-have: useful but not critical (bucketed dimensions, rates).
• Value-add: new insights the data supports but may not be asked for yet (computed sources, complex measures).

User interaction

The user will:

• Confirm business logic decisions.
• Adjust thresholds and bucket boundaries.
• Add missing fields.
• Remove fields they don't need.
• Change priorities.

Once the definitions are confirmed, write them into the .malloy model (see your modeling workflow). Use #(doc) annotations to document sources and fields, and a given: only where the data's meaning or access depends on a runtime value (a gate, a value every query must supply). A filter a reader picks on a dashboard belongs to the report, not the model. #(filter) is deprecated. Never add a #(filter) annotation: every use, including required, implicit, and date/number ranges, has a given: form. See skill:malloy-model § Parameterizing sources with given:. Keep the confirmed definitions in the conversation; there is no separate plan-file store.

Data-driven proposals

Every recommendation must be backed by a query result. Do not propose based on column names or schema structure alone. Always run execute_query to check the actual data before presenting. To learn what sources and fields exist, ground yourself with get_context: it returns the model's sources, views, and fields, so there is no separate schema-search step.

Example, denormalized vs joined:

"Your customers table has an order_count column. I compared it against count() from the orders table:

- 94% of customers match exactly

- 6% have stale counts (the denormalized value is lower than the actual count)

- The max discrepancy is 12 orders

I'd recommend using the joined count from orders rather than the denormalized order_count. Want to keep the denormalized column as internal, or drop it?"

Tips

• Show data, not assumptions: every proposed dimension or measure should have evidence (distinct values, distributions, ranges).
• Use `execute_query` to verify any data questions before presenting to the user.
• Don't over-propose: 5-8 dimensions and 4-6 measures per base source is usually enough to start.
• Rank everything: users appreciate knowing what's essential vs. optional.
• Business logic questions must be specific. "What date should I use?" is bad. "Your table has created_at and submitted_at that differ by 1-3 days in 13% of rows, which is canonical?" is good.

Output

A confirmed source architecture and a confirmed set of field definitions (renames, dimensions, measures, business decisions), held in the conversation and ready to write into the .malloy model via your modeling workflow.