malloydata/publisher/malloy-gotchas-modeling

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 tidy away empty/garbage/duplicate columns (internal:, which a caller's query can still read through its own extend or include) and to block 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 {}