Malloy Gotchas Modeling

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

Modeling Gotchas

Read this before writing Malloy code. These patterns cause most modeling errors.

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.

When a Model Will Not Compile

Get the error. Use an editor-diagnostics tool if your host has one. Otherwise run any query against the source with execute_query and read the error it returns; every host that can run Malloy can do this. Only ask the user to open the file in an editor when you know they have it open there.

Errors cascade. Fix the FIRST error only, recompile, repeat; later errors are often caused by it. If the message is unclear, call search_malloy_docs with the message text.

Reserved Words: Backtick Them

When in doubt, backtick it. Unquoted reserved words cause cascading errors on unrelated lines.

// WRONG                        // RIGHT
dimension: d is Date::date      dimension: d is `Date`::date

Words 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

NULL Checks: write is not null

// AVOID (compiles with a warning)     // RIGHT
dimension: is_sold is sold_at != null   dimension: is_sold is sold_at is not null

!= null also compiles and returns the right rows (a null row comes back false), but the compiler warns Use 'is not null' to check for NULL instead of '!= null'. Write is not null to keep the file warning-free.

Date Functions vs Properties

// WRONG: day_of_week is a function        // RIGHT
dimension: dow is created_at.day_of_week   dimension: dow is day_of_week(created_at)

Property access: .month, .year, .quarter, .day, ::date Function call required: day_of_week(), week(), hour(), minute(), second()

.date Is a Cast, Not a Truncation

Calendar truncations are .day, .week, .month, .quarter, .year (plus .hour, .minute, .second for timestamps). .date is not among them: it's a cast (::date), not a truncation, so created_at.date does not compile. This bites twice: once at compile time, and again as a latent bad #(doc) comment that only a review pass catches ("truncated to date" is a doc smell; it should say "to day").

// WRONG                          // RIGHT
created_at.date                   created_at.day     // truncate to day
                                   created_at::date   // cast to a date

Interval Functions: unit(start to end), and the unit decides the operand type

An interval is unit(start to end). Two rules, both enforced by the compiler:

• Never subtract. days(a - b) fails with Can not offset time by 'date'. The to form is the only one.
• Mixing a date and a timestamp needs a cast, unless the date side is a literal. A date literal widens to a timestamp on its own, so days(@2020-01-01 to now) compiles. A date column does not: days(signup_date to now) fails with Cannot measure from date to timestamp. Cast the odd one out (::date, ::timestamp). now is a timestamp.

Which units accept what:

// WRONG: subtraction, and a calendar unit applied to timestamp columns
dimension: gap is days(closed_at - opened_at)
dimension: months_open is months(opened_at to closed_at)

// WRONG: approximating a calendar unit that exists
dimension: months_open is days(opened_at to closed_at) / 30.44

// RIGHT
dimension: days_open   is days(opened_at to closed_at)
dimension: months_open is months(opened_at::date to closed_at::date)

The calendar units are real and exact. If one fails, read the message: it is telling you to cast the operands, not to divide by 30.44.

Safe Division: Always nullif

// WRONG              // RIGHT
a / b                 a / nullif(b, 0)

String Columns Need Casts for Aggregates

// WRONG: "Can't use type string"     // RIGHT
measure: avg_score is avg(score)      measure: avg_score is avg(score::number)

Dirty columns: null the sentinel before casting. ::number is a strict cast, so a column that carries non-numeric sentinels ('NA', 'N/A', '', '-', 'null') compiles fine but fails at query time with Could not convert string 'NA' to DOUBLE. Strip the sentinel with nullif first, then cast (aggregates skip nulls):

// WRONG: throws on 'NA' at query time   // RIGHT: nulls 'NA', then casts
measure: s is avg(score::number)         measure: s is avg(nullif(score, 'NA')::number)

Chain nullif for multiple sentinels: nullif(nullif(score, 'NA'), '')::number. Sample the column's values first (run: source -> { group_by: score; limit: 20 }) to see which sentinels it uses.

Boolean Columns: No Quotes

// WRONG                                     // RIGHT
count() { where: complaint = 'true' }        count() { where: complaint = true }

Check schema: if BOOL, use true/false. If STRING, use 'true'/'false'.

greatest() / least() Are Null-Poisoning

Malloy's greatest() / least() return NULL if any argument is null, unlike Postgres GREATEST/LEAST, which ignore nulls. Porting a LookML/SQL expression verbatim is a silent parity bug: the number just goes null for any row with a missing input. Coalesce the result back to a non-null argument:

// WRONG: one null input nulls the whole thing
dimension: last_touch is greatest(email_at, call_at)

// RIGHT: fall back so a null arg can't poison the result
dimension: last_touch is greatest(email_at, call_at) ?? email_at ?? call_at

No Scalar Median; Raw-SQL Aggregates Don't Compile

There is no scalar median, and PERCENTILE_CONT cannot be expressed as a measure in this build. Every documented form for a custom SQL aggregate - percentile_cont!(x, 0.5), sql_number(...), sql_number(...) { is_aggregate: true }, and the # is_aggregate annotation - resolves as a scalar and fails with "Cannot use a scalar field in a measure declaration." The docs' own avg_dist example fails the same way. This is a deployed-runtime limitation, not a syntax error you can fix: do not burn cycles trying !, sql_number, or is_aggregate variations to get a median.

// DOES NOT COMPILE in this build (all forms resolve as scalar):
measure: median_x is percentile_cont!(x, 0.5)
measure: median_x is sql_number("PERCENTILE_CONT(...) ...") { is_aggregate: true }

To see a median or any percentile as evidence (for example before choosing a tier boundary), run the two-stage nearest-rank query in skill:malloy-discover § Example Queries. It is a query result you read, not a reusable measure.

For a measure, ship avg instead, or defer median with a documented gap ("median deferred: no scalar median / runtime rejects raw-SQL aggregates"). Tell the user; don't silently substitute avg for a metric that was specified as median.

stddev does work, so reach for it when the question is about spread. It is a native Malloy aggregate rather than a raw-SQL escape, so unlike everything above it compiles both inline and as a measure:, and it is the sample standard deviation. variance, stddev_samp, and stddev_pop are not Malloy functions, and pushing them through ! fails as a scalar exactly like percentile_cont!.

// WORKS: inline, or as a measure on a source
run: order_items -> { aggregate: sd is stddev(sale_price) }
source: items is order_items extend { measure: price_stddev is stddev(sale_price) }

Field Management: extend {} and include {}, in that order

Malloy has two field-management mechanisms for base sources. include {} is the curated default; extend { except / accept / rename } handles the renames. They do compose, but only in one order: the extend {} that renames must come before the include {}, and include {} must name the field as it is after the rename.

Default: include {} for documented, curated base sources

Use include {} whenever the source doesn't need a rename:. It's the only way to attach #(doc) tags to raw columns, and it's the canonical way to hide empty/garbage/duplicate columns (internal:) and sensitive ones (private:). See skill:malloy-model § Access Modifiers.

##! experimental.access_modifiers
source: orders is conn.table('orders') include {
  public:
    #(doc) Order identifier
    order_id

    #(doc) Customer who placed the order
    user_id

  internal:
    raw_payload_json    // empty after JSON extraction
    legacy_status_code  // superseded by status_code
}

When a rename: is needed: rename first, then include {}

The usual reason is a collision inside include {}: a measure cannot share a name with a raw column, even one tagged internal:, and the compiler says so (Cannot redefine 'revenue'). The fix is to rename the raw column out of the way, which frees the name for the measure. Order is what makes it work:

##! experimental.access_modifiers
// RIGHT: rename frees `revenue`, include curates what is left, measure takes the name
source: orders is conn.table('orders')
  extend { rename: raw_revenue is revenue }
  include {
    #(doc) Revenue as loaded, before adjustments
    internal: raw_revenue
    public: order_id, user_id
  }
  extend { measure: revenue is raw_revenue.sum() }

Two ways to get the order wrong, with the errors they produce:

• include {} before the renaming extend {} fails with Can't find field 'X' to set access modifier, currently surfaced as an internal compiler error. include runs against names that no longer exist by the time the rename is applied.
• Naming the pre-rename column inside include {} fails with `revenue` not found. After a rename only the new name exists; use it.

You do not have to give up include {} to get a rename: the curated surface, #(doc) on raw columns, and the public/internal/private tiers all survive. Renaming the measure instead is still worth considering when the raw column name is the one people know, but it is a modeling preference, not a workaround for a limitation.

extend {} clauses (reference)

• accept:: allow-list, keep only the named columns
• except:: deny-list, drop the named columns; keep everything else (mutually exclusive with accept:)
• rename:: alias a raw column to free up its original name for a measure or dimension

Migrating conn.sql() to conn.table() + Malloy clauses

The biggest reason teams reach for conn.sql() is column gating, aliasing, and per-row derivation in one place. All three have native equivalents:

1. Verify the schema: run: <source> -> { select: *; limit: 1 } to discover all columns. Anything in the table but not in the SQL's SELECT was being intentionally hidden, so preserve that gating.
2. Switch to conn.table('…').
3. Hidden columns: include { internal: ... } (lets you also #(doc) the public columns). A rename: in the same source does not force you off include {} - see item 4 for the order.
4. SQL aliases: an extend { rename: ... } before include {}, naming the field by its new name in include {} (they compose, but only in that order). If the alias was to free up a name for a measure, use rename: raw_X is X, then measure: X is raw_X.sum().
5. SQL derivations: dimension: definitions in extend {}.
6. SQL WHERE: source-level where:.

Cannot Redefine Query-Based Source Columns

Columns from table -> { group_by, aggregate } or conn.sql() already exist. You cannot re-declare them.

// WRONG: "Cannot redefine 'user_id'"
source: facts is conn.table('t') -> { group_by: user_id, aggregate: total is sum(amt) }
  extend { dimension: user_id is user_id }
// RIGHT: add only NEW derived dimensions
source: facts is conn.table('t') -> { group_by: user_id, aggregate: total is sum(amt) }
  extend { dimension: is_high_value is total > 1000 }

To add #(doc) tags to existing query columns, use include {} between the query and extend.

Extending a Source Cannot Reuse a Name It Already Defines

// WRONG: "Cannot redefine 'overview'" when sales already declares view: overview
source: wines is sales extend { view: overview is { aggregate: record_count } }
// RIGHT: give the extension its own name
source: wines is sales extend { view: summary is { aggregate: record_count } }

An extension adds to the parent's namespace, it does not override it. This bites when you extend a source to "replace" one of its views: rename the new definition, or edit the view on the parent source instead of extending it. Malloy reports the same Cannot redefine 'X' for dimensions and measures that collide with an inherited name, per the sections above and below.

Never Use conn.sql() When Malloy Has a Native Pattern

// WRONG: raw SQL for pre-aggregation
source: facts is conn.sql("""SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id""")
// RIGHT: Malloy query-based source
source: facts is conn.table('orders') -> { group_by: user_id, aggregate: total is sum(amount) }

Mandatory: call search_malloy_docs before reaching for conn.sql(). Don't argue from intuition. Most patterns that look SQL-only have a Malloy equivalent, including the ones reviewers historically said couldn't be expressed.

Genuinely valid conn.sql() candidates (rare):

• SQL features Malloy explicitly doesn't model (e.g., DML/DDL, specific MERGE patterns)
• Multi-stage transformations where every CTE has 3+ joins to different tables AND the result is consumed by multiple downstream sources, but in this case an intermediate table in the data warehouse is usually still better than conn.sql()

Never use conn.sql() for: simple column selection or renaming, WHERE filters, two-table joins, column type casts, latest-snapshot patterns, conditional aggregation, or window functions of any kind.

If a project's standards file specifies a stricter policy (e.g., a search_malloy_docs rationale comment requirement above every conn.sql() block), defer to that.

Relative Data-File Paths: Set an Absolute workingDirectory

duckdb.table('data/x.csv') is resolved against the DuckDB connection's workingDirectory, not against the model file. Publisher sets that to the package root, so relative paths work there with no config at all. Every other host reads it from malloy-config.json, and a relative value there is resolved against the process's current directory - not the config file's directory, and not the VS Code workspace root. canonicalizeConfigPath in malloy-db-duckdb/src/duckdb_config.ts calls canonicalizePath with no baseDirectory, which is a bare path.resolve(input).

So the same config works or fails depending on which directory the editor or shell was launched from. That is why this breaks intermittently and appears to be a model bug.

// WRONG: resolved against the process cwd, so it works from the repo root and
// fails from anywhere else - including however your editor happened to launch
{"connections": {"duckdb": {"is": "duckdb", "workingDirectory": "malloy"}}}

// RIGHT: absolute, so the cwd cannot change the answer
{"connections": {"duckdb": {"is": "duckdb", "workingDirectory": "/abs/path/to/pkg"}}}

Point it at the directory the model's table paths are written relative to - the package root, the one holding data/. Keep the model's paths relative (data/x.csv) so Publisher still serves it; only the config carries the absolute path.

The symptom, and the cascade. One IO Error at the source: line, then a "not defined" error for every field of that source:

line 87: IO Error: No files found that match the pattern "data/product_usage.csv"
line 91: 'org_slug' is not defined
line 93: Reference to undefined value active_users

Those field errors are not real. Fix the first error and they all go. Do not start renaming columns.

Check the config before the model when a source that Publisher queries fine fails in the editor or CLI with a missing-file error. The model is the same file; the resolution base is not.

JSON Files: Read Them In Place Like CSV

// RIGHT: .json works like .csv/.parquet
source: reviews is duckdb.table('data/reviews.json')
// RIGHT: newline-delimited JSON is read the same way
source: events is duckdb.table('data/events.ndjson')
// RIGHT: read options need read_json_auto in a SQL source
source: nested is duckdb.sql("""SELECT * FROM read_json_auto('data/reviews.json')""")
// WRONG: shelling out to python, or converting to CSV first

DuckDB reads JSON directly, so never preprocess a .json file before modeling it and never reach for a scripting language to inspect one. Both a top-level array of objects and newline-delimited JSON work through duckdb.table().

Quirk: JSON carries no schema, so a value written as "90" arrives as a string where the same data in CSV would be inferred as a number. Cast it in the source, under a new name (reusing the column's own name is a redefinition error):

source: reviews is duckdb.table('data/reviews.json') extend {
  dimension: points_num is points::number
}