Malloy Modeling

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

STOP - READ BEFORE WRITING ANY MALLOY CODE

AI AGENTS: You MUST review this file before writing Malloy code. Cross-skill references below use logical skill: names; load the referenced skill before acting. Before writing code, also read the gotcha skills: skill:malloy-gotchas-modeling, skill:malloy-queries, and skill:malloy-charts.

Pre-Flight Checklist

1. Discover first: ground yourself before writing ANY code, with the tool that matches what you are modelling.
   • Modelling data already in a package: get_context returns that package's sources, views, and fields (with their docs).
   • Modelling a database with no package yet: get_context has nothing to return, so use search_database_schema instead. It walks the connection's schemas and tables, ranks them against a plain-English description, and gives you each table's columns plus the source: line to start from. Take those names verbatim into step 5. Never guess field names either way.
2. Search docs proactively: call search_malloy_docs BEFORE writing unfamiliar patterns (window functions, query-based sources, pipelines). Don't guess. Malloy syntax is specific and SQL intuition is often wrong.
3. Search by topic name. search_malloy_docs takes plain topics such as "window functions", "comparing timeframes", "cohort analysis", "percent of total", "histogram", "nesting", "rendering".
4. Check diagnostics after writing: fix the FIRST error first, errors cascade.
5. Read the gotcha skills: skill:malloy-gotchas-modeling, skill:malloy-queries, and skill:malloy-charts prevent the most common mistakes.

Quick syntax reminders:

1. Backtick reserved words: `Date`, `Hour`, `Timestamp`, `Type`, `number`, `source`
2. Use having: for aggregate filters: not where: on measures
3. Alias joined fields in group_by if using them in order_by
4. count() counts rows; count(x) counts distinct values of x: count(distinct x) is deprecated, write count(x)
5. One tag per line: # label="Revenue" and # currency on separate lines
6. No fixed scale on measures: use # currency not # currency=usd0m
7. Cast strings for aggregates: avg(score::number) not avg(score)
8. Boolean columns: use = true not = 'true' (no quotes!)
9. Read data files in place: .csv, .parquet, .json, .ndjson, and .xlsx all work as-is through duckdb.table('data/file.ext'). Never convert a file to another format first, and never read one with python or jq to "have a look" first: query it. For .xlsx, check the row count before trusting it: a workbook with a title row or a blank spacer reads short and reports no error. (Per-format quirks: skill:malloy-gotchas-modeling)

Planning and modeling-notes.md

If the IDE has a native plan mode, use it for the high-level approach: do data exploration during planning, then present a concrete plan for user approval before writing any files.

modeling-notes.md is an expected output of the workflow, not an optional extra. Start it at step 2 (Propose Scope) and grow it as you work: it persists alongside the model, and its value is as the thing the user argues with at step 3, before source files exist; written after the build it can only document decisions already baked in. Record findings and problems as they are found during discovery (skill:malloy-discover), and every unconfirmed decision as an open item. Only when there is no writable workspace do the notes live in the conversation instead.

Keep it compact, with these sections:

# Modeling notes - <package>
## Scope           what was confirmed, what the model is FOR, skip list with reasons
## Grain and keys  proven by query, not by column name
## Coverage        coverage cliffs; columns excluded for nullity
## Decisions       each with its evidence
## Open decisions  ASSUMPTIONS, NOT CONFIRMED: every threshold or definition the user
                   has not settled, one entry each, mirrored by a hedge in its #(doc)
## Validation      reconciliation checks performed, and their results

8-Step Modeling Workflow

The agent orchestrates all steps. Steps marked (user) pause for input. Each step has a dedicated skill with full instructions. Read each step's skill before starting that step, including the decision skills for steps 1–4 (skill:malloy-discover, skill:malloy-define). They govern what the model says; skipping them to reach the build skills is how unreviewed business logic ships.

A field is not complete until it has its definition, #(doc) tag, and rendering tags, and any threshold or business convention in it is user-confirmed, distribution-derived, or explicitly flagged in its #(doc) (see skill:malloy-document § Mark conventions as conventions). Documentation is part of defining a field, not a separate activity. Read skill:malloy-document for full documentation standards (doc string writing, tag ordering).

DISCOVER → SCOPE → SOURCES → DEFINITIONS → BUILD BASE → BUILD JOINED → REVIEW → CURATE
 (silent)  (user)   (user)      (user)       (agent)      (agent)      (user)   (user)

The pauses are the point

These are governed semantic models: the business decisions in them must be confirmed by a human subject-matter expert, and the (user) steps exist to collect that confirmation. They are real stops, not progress reports. A model can be complete, compiling, and fully documented and still be wrong everywhere it guessed; a capable agent can build the whole thing without pausing once, which is exactly the failure mode this workflow exists to prevent.

When a decision goes unanswered (the user explicitly declines to decide, or nobody is there to ask), do not silently proceed as if it were settled. Take your best-supported position, label it an assumption in the field's own #(doc) (see skill:malloy-document § Mark conventions as conventions), record it under "Open decisions" in modeling-notes.md, and raise it again at Review. An unlabeled assumption is indistinguishable from a confirmed fact, and misleads everyone downstream.

Step 7 Review is a checklist, not a summary

Present these to the user, with answers:

• Which definitions did the user actually confirm? List them; everything else is an assumption.
• Which thresholds and bucket boundaries did you choose? For each: the evidence (distribution query, metadata, prior art) and the #(doc) hedge that marks it.
• Which questions were left unanswered? Each must already carry a labeled assumption and an "Open decisions" entry.
• Does the headline metric have more than one defensible definition? If yes, that is a blocking question: put the candidate definitions to the user with their counts side by side, not in a footnote.

The user confirming this checklist is what makes the model governed. A summary of what you built is not a checkpoint.

Publishing is out of scope for open-source v1. Self-hosters move a finished model into a served package via git and the host's publish path; see skill:malloy-publish for the local-to-served handoff.

Two paths to a model: both produce the same fully documented result:

• Schema-first: "Model my data" → 8-step workflow above using the relevant skills
• Analysis-first: a data question arrives before any model exists → skill:malloy-model-as-you-go. It answers the question with skill:malloy-analysis, then codifies what the answer assumed into the model, one question at a time, confirming binding decisions first. The model exists by the end; there is no separate formalize step.
• Open-ended exploration with no intent to keep anything: skill:malloy-analysis (its "no specific question" branch). If it turns into something worth keeping, formalize via skill:malloy-model (reference/analysis-to-model.md).

Agent Behavior

Research before asking. Present proposals with evidence. Never ask open-ended questions: propose with data and let the user confirm.

Use business language. Say "I simplified the column name" not "reserved word replaced." Don't expose Malloy internals unless the user asks.

Describe what you're doing, not which step you're on. The user doesn't have the skill files open. Say "I'll propose which tables to include and how they relate" not "Steps 3 and 4." Say "Now I'll write the source files" not "Moving to Step 5." Explain the purpose of each phase in plain language before doing it.

Present choices as A/B/C. When asking the user to choose, use lettered options with one-line descriptions. Mark your recommendation.

Complete all workflow steps. Once modeling begins, complete through Review and propose Curate. A field without documentation is not finished. If you lose track, re-read the model and your notes. Every source you write gets a source-level #(doc) description and a stated grain (primary_key: or "one row per ..."), including intermediate steps such as a de-duplication or clean-up source. Or fold the clean-up into the one source it feeds, so there is nothing undescribed left in the package. Build a dashboard or notebook only when the user asks for one.