version: "1.0.1" name: malloy-modeling description: Build semantic models with Malloy for the Malloy Publisher. Read this skill whenever the user asks about modeling data or specifically mentions Malloy.

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-gotchas-queries, and skill:malloy-gotchas-rendering.

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: malloy_getContext returns that package's sources, views, and fields (with their docs).
• Modelling a database with no package yet: malloy_getContext has nothing to return, so use malloy_searchDatabaseSchema 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.

1. Search docs proactively: call malloy_searchDocs BEFORE writing unfamiliar patterns (window functions, query-based sources, pipelines). Don't guess. Malloy syntax is specific and SQL intuition is often wrong.
2. Use `skill:malloy-patterns` to discover available doc topics (YoY, cohorts, rendering, window functions).
3. Check diagnostics after writing: fix the FIRST error first, errors cascade.
4. Read the gotcha skills: skill:malloy-gotchas-modeling, skill:malloy-gotchas-queries, and skill:malloy-gotchas-rendering 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. Use `count(x)` not `count(distinct x)`: Malloy's count() is always distinct
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. Once approved, you can write a modeling-notes.md during execution to record decisions (scope, sources, key choices, prior art, gaps). This file persists alongside the model. Otherwise, keep the proposal and decisions in the conversation; Publisher has no separate workspace document store to write them to.

8-Step Modeling Workflow

The agent orchestrates all steps. Steps marked (user) pause for input. Each step has a dedicated skill with full instructions; load the relevant skill when needed.

A field is not complete until it has its definition, `#(doc)` tag, and rendering tags. 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)

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: "Explore this data" → skill:malloy-analyze → formalize via skill:malloy-model (reference/analysis-to-model.md)

After analysis completes, always recommend formalizing into a model.

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. A field without documentation is not finished. If you lose track, re-read the model and your notes. Suggest notebooks at the end.

Route by Intent

If the user's first message is a data question (not "build me a model"), route to skill:malloy-analyze. After analysis completes, always recommend formalizing via the analysis-to-model workflow (skill:malloy-model → reference/analysis-to-model.md).

Additional Support Skills

These supplemental skills may also be loaded as needed:

• `skill:malloy`: Index of Malloy skills and routing guide
• `skill:malloy-debug`: Fix compile errors and interpret diagnostics

Publisher MCP Tools

Ensure the Publisher MCP tools are configured before modeling.

Never guess field names. Ground yourself with malloy_getContext to see the sources and fields a package defines.

The edit-and-run loop

Publisher compiles each configured package at boot and serves that cached model, so a source or view you add afterwards is not queryable by name until you reload the package. The loop is:

1. Validate the change with malloy_compile, which reads the model fresh from disk and returns diagnostics without running anything.
2. Save it to the package's model file.
3. Reload with malloy_reloadPackage.
4. Run the new view with malloy_executeQuery.

A reload that fails to compile is safe: your files are left alone and the previously compiled model keeps serving, with the compile errors returned to you. Compile first anyway for faster feedback. Keep the source of truth outside publisher_data/, which is not version-controlled and is wiped by a --init restart. If these two tools are missing, the Publisher you are connected to predates them; fall back to validating with a throwaway malloy_executeQuery.

SQL-to-Malloy Quick Reference

Critical Rules

1. All keywords require colons: source:, dimension:, measure:, view:
2. Use `is` not `as`: dimension: name is expression
3. Arrow operator required: run: source -> { operations }
4. Specify join type: join_one:, join_many:, join_cross:
5. Safe division: revenue / nullif(count, 0)
6. Group definitions under one keyword: measure: then indent fields beneath

Common Anti-Patterns

WRONG: source flights is ...           RIGHT: source: flights is ...
WRONG: dimension: x as y               RIGHT: dimension: y is x
WRONG: count(*)                        RIGHT: count()
WRONG: count(distinct x)               RIGHT: count(x)
WRONG: revenue / order_count           RIGHT: revenue / nullif(order_count, 0)
WRONG: run: src { ... }                RIGHT: run: src -> { ... }

Reserved Words: Scan Schema First

Malloy has many reserved words. When in doubt, backtick it. Most likely to appear as column names:

date, time, day, month, year, quarter, week, hour, minute, second,
number, string, boolean, type, table, source, index, count, sum, avg, min, max,
true, false, null, is, on, with, all, from, by, in, to, for, select, order_by,
top, bottom, desc, asc, row, range, current, window, rank

• number: only the bare word needs backticking; account_number is fine
• source: reserved; use a different alias like traffic_source
• string, boolean, true, false: backtick any column with these exact names

Gotcha Skills: Read Before Writing Code

The following skills contain detailed WRONG/RIGHT patterns that prevent the most common Malloy errors. Read them before writing code:

• `skill:malloy-gotchas-modeling`: Reserved words, NULL checks, date functions, type casts, rename pitfalls, query-based source gotchas, conn.sql() anti-pattern
• `skill:malloy-gotchas-queries`: Chart constraints, aggregate filters, joined field aliasing, time truncation vs extraction
• `skill:malloy-gotchas-rendering`: Tag syntax, scale rules, sparkline setup, big_value patterns