version: "1.0.1" name: malloy-model description: Build Malloy semantic models with base source and joined source files. Use when creating or modifying .malloy files, user asks to "create a malloy model", "add dimensions", "add measures", "create a source", or any Malloy model authoring task.

Building Malloy Models

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.

Getting Started (New Projects)

If no .malloy files exist yet, do discovery and propose a structure first, then return here to build base source and joined source files. Keep proposals and the analysis behind them in the conversation.

File structure convention (a flat layout at the package root is the simplest default):

<package-name>/
  publisher.json              # Required for publishing (name, version, description)
  customers.malloy            # Base source: one per table
  products.malloy
  orders.malloy
  user_order_facts.malloy     # Computed source
  order_analysis.malloy       # Source: one per analytical domain
  customer_health.malloy

Versions for new packages should start at "0.0.1".

Before creating any files, check for an existing publisher.json in the target directory. If one exists for a different package, create a new subdirectory for your package, don't overwrite another package's config.

In Publisher an environment is a project, and Publisher is single-tenant, so there is no org/tenant layer to model around: one environment holds one set of packages.

Prior Art Dispatch

If your discovery turned up existing modeling patterns to mirror (a derived table, UNNEST joins, a review or curation pass), read the relevant reference before building.

Base Source Templates

Base Source (Simple Mode)

source: customers is my_conn.table('sales.customers')
extend {
  primary_key: customer_id
 
  dimension:
    // Use dimension (not rename:) for cleaner column names
    order_type is `Type`
    full_name is concat(first_name, ' ', last_name)
    segment is lifetime_value ?
      pick 'enterprise' when >= 100000
      pick 'mid-market' when >= 10000
      else 'SMB'
 
  measure:
    customer_count is count()
}

Every `dimension:` needs `name is expr`. A bare column name like dimension: species is a parse error (e.g. missing IS at ...). Raw columns are already usable in group_by / select without any declaration, so only add a dimension: when deriving or renaming a field (e.g. revenue is price * quantity).

Base Source (Curated Mode with Access Modifiers)

##! experimental.access_modifiers
 
source: orders is my_conn.table('sales.orders')
include {
  public:
    #(doc) Order identifier
    order_id
 
    #(doc) Customer who placed the order
    user_id
 
    #(doc) Total sale price in USD
    sale_price
 
    #(doc) Order creation timestamp
    created_at
 
  internal:
    raw_payload_json  // Verified empty via index query + user confirmation
}
extend {
  primary_key: order_id
 
  dimension:
    #(doc) Date the order was placed
    order_date is created_at::date
 
  measure:
    #(doc) Total number of orders
    order_count is count()
 
    #(doc) Total revenue in USD
    # currency
    revenue is sum(sale_price)
}

Computed Source (from Query)

import "orders.malloy"
 
source: user_order_facts is from(
  orders -> {
    group_by: customer_id
    aggregate:
      total_orders is count()
      total_revenue is sum(sale_price)
      first_order_date is min(created_at)
      last_order_date is max(created_at)
  }
) extend {
  primary_key: customer_id
 
  dimension:
    days_since_last_order is days(now - last_order_date)
    is_repeat_buyer is total_orders > 1
 
  measure:
    buyer_count is count()
    avg_customer_ltv is avg(total_revenue)
}

For advanced query-based source patterns (window functions, pipelines), see reference/query-sources.md.

Joined Source File Template

import "customers.malloy"
import "orders.malloy"
import "user_order_facts.malloy"
 
#(doc) Customer health analysis. Use for retention, segmentation, and churn risk.
source: customer_health is customers extend {
  join_one: user_order_facts with customer_id
  join_many: orders on customer_id = orders.customer_id
 
  dimension:
    is_at_risk is user_order_facts.days_since_last_order > 90
      and user_order_facts.total_orders > 1
 
  measure:
    revenue_per_customer is orders.sale_price.sum() / nullif(customer_count, 0)
    at_risk_count is count() { where: is_at_risk = true }
}

Base vs Joined Sources

Key Rules

• Every `dimension:` needs `name is expr`: a bare dimension: species is a parse error. Raw columns are queryable directly in group_by / select; only declare a dimension: to derive or rename a field.
• Define joined tables before referencing them, use import statements in multi-file architecture
• Use `nullif(denominator, 0)` for all division
• Alias joined fields before using in `order_by`: group_by: yr is table.year
• Verify join paths exist before referencing a.b.field (each hop needs explicit join)
• Pick syntax: value BEFORE condition, pick 'Small' when size < 10
• `where:` vs `having:`: Use where: for row filters, having: for aggregate filters
• Never use `rename:`, it's incompatible with include {}. Always use internal: + dimension: for cleaner column names (e.g., mark ` Type as internal, add dimension: order_type is \Type\`)
• Mark raw columns `internal` when a derived dimension replaces them
• Check for duplicate rows before building measures
• When both a combined table (all types) and filtered/split tables exist, prefer the split tables
• DRY: define measures/dimensions in base source files, not inline in views

Parameterizing sources with given: (preferred)

Native Malloy `given:` parameters are the going-forward way to expose tunable knobs (date range, region, manufacturer) on a source - prefer them over #(filter) when you author a new model. A given: is a first-class runtime parameter you reference in the model's own logic; callers supply values at query time and the model uses them however it declares. Enable them with ##! experimental.givens at the top of the model.

##! experimental.givens
 
given:
  manufacturer_filter :: filter<string> is f''
  subject_filter :: filter<string> is f''
 
source: recalls is duckdb.table('data/auto_recalls.csv') extend {
  where: Manufacturer ~ $manufacturer_filter, Subject ~ $subject_filter
  measure: recall_count is count()
}

A given is declared bare but referenced with a `$` sigil in expressions ($manufacturer_filter), as above.

• Give every optional filter a neutral, match-all default - a filter<> given defaulting to f'' - so an unsupplied value returns unfiltered rows, matching how #(filter) behaves when a value is omitted. Because the given bakes an always-on where: into the source, a non-neutral default (e.g. a date floor) applies to every read of the source, not just the ones that opt in - so keep defaults neutral. Defaults must be Malloy literals.
• Givens don't auto-inject a `where:`. Unlike #(filter), you write the filter expression that references the given yourself (e.g. where: dimension ~ $given_name).
• Not every filter maps cleanly. A filter with no neutral match-all literal default - e.g. a scalar date/number range like > @2020-01-01 - is not a good given:; keep those on #(filter). Two more cases keep using #(filter): mandatory scoping filters (required) and system-injected row-level filters (implicit), both below.

Givens are also the substrate for access control - see "Access Control: Source Gating with #(authorize)" below.

Legacy: Parameterizable Filters with #(filter)

#(filter) is the older, Publisher-specific mechanism for the same idea. Publisher parses the annotation, exposes filter metadata via the API, renders filter widgets in the notebook UI, and injects `where:` clauses into queries server-side when callers supply parameters. Prefer given: (above) for new models; keep reading and maintaining #(filter) on existing models, and keep using it for the two cases given: can't cover yet - required (mandatory scoping) and implicit (system-injected filters), below.

Filters are a runtime/modeling construct, not just documentation. They shape governance, query latency (forcing filters keeps result sets bounded), and correctness (see required below). They live on the source, never on the consumer: an ad-hoc report or notebook that imports a source inherits and displays that source's filters automatically; it does not (and cannot) declare new ones. If an existing #(filter)-based source needs another knob, add it to the source itself, not to the consumer.

Syntax

#(filter) [name=NAME] dimension=DIMENSION type=TYPE [implicit] [required]

Filter types

Example

#(filter) name=Manufacturer dimension=Manufacturer type=in
#(filter) name=Subject dimension=Subject type=like
#(filter) name=Major_Recall dimension="Major Recall" type=equal
#(filter) name=Recall_After dimension="Report Received Date" type=greater_than
#(filter) name=Recall_Before dimension="Report Received Date" type=less_than
source: recalls is duckdb.table('data/auto_recalls.csv') extend {
  measure:
    recall_count is count()
}

For date-range filters, declare two filters with distinct name values targeting the same dimension (one greater_than, one less_than).

When to use required

required filters are a correctness, latency, and governance mechanism, not just UX. Mark a filter required when:

1. Modeling correctness, the source's `primary_key:` is only unique under a filter. If a high-cardinality key is not unique across the whole table but is unique within a scoping dimension, then that scoping dimension MUST be supplied for symmetric aggregation to produce correct numbers. For example, if events.id repeats across days but is unique within a single event_date, queries that don't pin the date can fan out and return hash-collision-sized garbage (~10²¹). Declare #(filter) name=Event_Date dimension=event_date type=equal required so the server refuses queries that don't provide it.
2. Query latency, the source spans more data than any single query should scan. A multi-year, multi-region table where every reasonable analysis is scoped to a date range or region: making the date filter required prevents accidental full-table scans.
3. Partial views that are only meaningful inside a date range, region, or business segment.
4. Governance, an analyst should never query the raw source without a scoping filter applied.

For (1), pair the required filter with a comment explaining the cardinality dependency, and consider also declaring #(doc) on the source noting the constraint.

When to use implicit

Use implicit for filters the system must inject but users should not see. The filter applies; it just doesn't appear in the UI or API filter list.

Type-aware literals

Publisher formats values based on the dimension's data type, string → 'value', boolean → bare true/false, date → @YYYY-MM-DD. You don't quote values yourself in the API call; Publisher handles formatting.

Bypass

Pass bypass_filters=true (REST) or bypassFilters: true (POST body) to skip filter injection entirely. Use sparingly, required-filter governance only works if bypass is restricted to trusted callers.

Access Control: Source Gating with #(authorize)

Gate query access to a source with #(authorize) over declared given: values (given: is Malloy's native runtime-parameter mechanism, the going-forward replacement for #(filter)). Publisher evaluates a source's in-scope #(authorize) expressions against the request's supplied givens before running the query; if any expression returns true the request proceeds, otherwise it is denied with 403. A source with no in-scope #(authorize) annotations is unrestricted.

##! experimental.givens
 
given:
  ROLE :: string
 
#(authorize) "$ROLE = 'analyst'"
source: orders is duckdb.table('orders.parquet') extend {
  measure: order_count is count()
}

• Source-level #(authorize) "<expr>" gates that one source. File-level ##(authorize) "<expr>" applies to every source in the file. Multiple gates combine as an OR, access is granted if any one is true, so a permissive file-level gate is a model-wide override, not an added restriction.
• Entry point only: not joined, but inherited through `extend`. The gate applies to the source a query enters through. A gate on a source reached only via join_* never fires, at any depth, so anything ungated that joins a locked base hands the base's rows to every caller. A source that extends a locked base and declares no gate of its own does carry the base's gate; declaring its own replaces it. Pair a locked base (#(authorize) "false") with curated extension sources, using access modifiers (include { public: …, private: * }), so an extension re-exposes only a curated column surface, and keep sensitive sources out of ungated joins.
• The expression may reference only givens and literals, never a column of the gated source; the check runs against a synthetic probe row, not your data.

Trust caveat. Givens are caller-asserted, anyone who can reach the query API can claim a favorable given, e.g. {"ROLE":"admin"}. #(authorize) is only a real boundary when it sits behind a trusted tier that sets givens from its own verified context, never directly from an untrusted caller. It is not, on its own, end-user authentication.

Forward direction. Givens are how access control is built here, and the planned next step is identity-bound ("secure") givens - reserved values a trusted tier populates from a verified token or proxy header, which the caller cannot override - turning #(authorize) into a standalone boundary. Model access on given: + #(authorize) now; it is the surface that carries forward.

Full syntax, OR/override semantics, validation, and the error contract are covered in your deployment's #(authorize) reference documentation.

Join Syntax

• Simple join: join_one: users with user_id
• Expression join: join_one: origin is airports on origin_code = origin.code
• Composite key: join_one: items on order_id = items.order_id and product_id = items.product_id
• Multiple joins to same table: join_one: origin_airport is airports with origin

Join Types: join_one: (many-to-one, efficient) | join_many: (one-to-many, always safe) | join_cross: (many-to-many)

Verify cardinality before writing joins: run: target -> { group_by: fk_col, aggregate: n is count(), having: n > 1, limit: 5 }. 0 results → join_one. Any results → join_many.

After Writing: Check & Review

Check diagnostics after writing. Errors cascade, fix the FIRST error only, then re-check. If errors persist, use the debugging strategy: look at first error, search docs if unsure, fix, repeat.

Validate with `execute_query`: Run queries, check distributions, verify measures, confirm joins (no fan-out).

To inspect the sources and fields a model already defines, ground yourself with get_context. It returns the package's sources, views, and fields, so there is no separate schema-search step. When you're unsure of Malloy syntax, call search_malloy_docs rather than guessing.

Advanced Patterns

Load the relevant reference file when you encounter these scenarios:

Done

Step complete. Output: base source files (.malloy, one per table) and joined source files (.malloy, one per analytical domain).

Suggest next steps to the user:

• Open the model in the browser to see it live: http://localhost:4000/<environmentName>/<packageName> for the package, or http://localhost:4000/<environmentName>/<packageName>/<modelPath> for a single model file. First confirm the running server actually serves this package (it is in the loaded publisher.config.json, or mounted live with --server_root . --watch-env <env>); a package the server has not loaded returns a 404, so do not hand over a link to a package that was just authored but never loaded.
• Build a notebook with interactive filters over the model (see skill:malloy-notebooks).
• Run analysis questions against the model (see skill:malloy-analysis).
• When you're ready to serve the model, publishing is out of scope for open-source Publisher v1: self-hosters commit the package to git and use their host's publish path.