Looky
Back to site

Docs / Build Workflow

Malloy support

The Malloy version Looky runs

Looky ships a specific, pinned version of Malloy with the BigQuery, Postgres, and MySQL adapters at the same version. Any Malloy syntax beyond what that version supports is not understood; any feature added in later Malloy releases is not available until Looky upgrades.

There are no Looky-specific Malloy extensions. The dialect you write is what Malloy itself documents — no more, no less.

Supported adapters

  • BigQuery — connects with a service-account JSON key.
  • Postgres — connects with a libpq connection string (host / port / database, plus any TLS or pool flags) and a user/password secret.
  • MySQL — connects with a mysql://host:port/database connection string and a user/password secret. Two caveats: connections are not encrypted (no TLS option yet), and MySQL has no real boolean type, so boolean columns read back as numbers — cast explicitly when you need a true/false filter.

No other adapters are bundled. See Sources for the per-adapter source YAML schema.

How models are loaded

Each workspace's content is rooted at workspaces/<billing>/<slug>/content/. Looky resolves .malloy files relative to that root.

import "..." statements inside a model are resolved against sibling .malloy files in the same directory. Use imports to share a base source declaration across multiple domain models — declare the source and its joins once, extend it per topic.

# models/orders_base.malloy — shared foundation
##! experimental.parameters

source: orders_base() is bigquery.table('...order_items') extend {
  join_one: products on product_id = products.id
  measure: revenue is sum(sale_price)
}

# models/revenue.malloy — domain model extends the base
##! experimental.parameters
import "orders_base.malloy"

source: revenue() is orders_base() extend {
  view: by_category is {
    group_by: products.category
    aggregate: revenue
  }
}

Every model needs the ##! experimental.parameters pragma at the top and parentheses on every source declaration (and on every reference to a source) — even when the source takes no parameters. The empty () declares the parameter list.

Source aliases inside models

A Malloy model uses a connection alias when it calls alias.table(...) or alias.sql(...). Looky matches each alias against the workspace's runtime/sources.runtime.yml declarations.

  • If the model uses one alias, Looky picks it automatically.
  • If the model uses several aliases (directly or through imports), the run must say which one to use; otherwise it is rejected.

See Sources for the alias-declaration syntax per adapter.

Named parameters

Filter and cross-filter values reach a query through named parameters declared on the source signature, between the parentheses. Each parameter takes a name, a type, and a default — p_country::string is "all".

There is one naming rule: parameters whose Malloy name starts with p_ have the prefix stripped for the external name. A model parameter declared as p_start_date is set by sending start_date from the dashboard filter.

##! experimental.parameters

source: orders(
  p_country::string  is "all",
  p_start_date::date is @2024-01-01
) is bigquery.table('...') extend {

  view: revenue is {
    where:
      (p_country = "all" or country = p_country)
      and created_at::date >= p_start_date
    aggregate:
      revenue is sum(sale_price)
  }
}

# filter / pill sends
params:
  country: "MX"
  start_date: "2024-12-01"

Parameters can be referenced two ways inside the source body:

  • Malloy expressions — write p_country, p_start_date directly in where:, aggregate:, etc. (the example above).
  • Raw-SQL @param placeholders — when the source body is built from raw SQL (connection.sql("""…""")), use @param placeholders inside the SQL block. Every @param must have a matching declaration on the source signature; otherwise validate fails with a clear error.

Date and timestamp parameters on Postgres and MySQL

Postgres and MySQL share a known limitation when Malloy binds a date or timestamp parameter natively in some query shapes. The reliable path on both is the raw-SQL @param pattern: build the source from connection.sql("""…"""), reference @param placeholders inside the SQL, and declare each placeholder on the source signature.

##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is postgres.sql("""
  select *
  from orders
  where (@date_from::date is null or created_at::date >= @date_from::date)
    and (@date_to::date   is null or created_at::date <= @date_to::date)
""") extend {
  view: revenue is {
    aggregate: revenue is sum(sale_price)
  }
}

Looky substitutes the @date_from / @date_to placeholders with literal values (or typed NULL when the dashboard hasn't supplied a value). On BigQuery and MySQL the same pattern works — the substitution is dialect-aware. See the source adapter comparison for the full divergence list.

Cache and parameters

Each cached entry is scoped to one query in one model with one specific parameter combination. A user filtering by "MX" gets a cached result for that specific combination; a user filtering by "AR" triggers a separate cache entry the first time, then hits cache on subsequent loads.

Editing the .malloy file invalidates every cached entry for queries inside it on the next request. See Cache for the cache sidecar shape.

Worked patterns

String parameter with an "all" sentinel

##! experimental.parameters

source: orders(
  p_status::string is "all"
) is bigquery.table('...') extend {
  view: detail is {
    where: p_status = "all" or status = p_status
    select: *
  }
}

Date range parameter pair (Malloy-expression style)

##! experimental.parameters

source: orders(
  p_date_from::date is @2024-01-01,
  p_date_to::date   is @2024-12-31
) is bigquery.table('...') extend {
  view: revenue is {
    where:
      created_at::date >= p_date_from
      and created_at::date <= p_date_to
    aggregate: revenue is sum(sale_price)
  }
}

Date range parameter pair (raw-SQL @param style)

##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is postgres.sql("""
  select *
  from orders
  where (@date_from::date is null or created_at::date >= @date_from::date)
    and (@date_to::date   is null or created_at::date <= @date_to::date)
""") extend {
  view: revenue is {
    aggregate: revenue is sum(sale_price)
  }
}

The @date_from / @date_to placeholders are substituted at run time with the values from the filter (or with typed NULL when the dashboard hasn't supplied a value).

Month string parameter

##! experimental.parameters

source: orders(
  p_month::string is "2024-12"
) is bigquery.table('...') extend {
  view: monthly is {
    where: format_datetime('%Y-%m', created_at) = p_month
    aggregate: revenue is sum(sale_price)
  }
}

Common pitfalls

  • Missing ##! experimental.parameters or missing () on the source. Validate fails with unsupported_model_shape. Add the pragma at the top of the file and parentheses on every source: declaration (and every reference to it).
  • Raw-SQL @param with no matching declaration on the source signature. Validate fails with unbound_param naming the missing parameter. Add it to the parentheses, e.g. p_date_from::date is null.
  • Parameter name mismatch between filter and model. The filter sends the external name; the model receives the prefixed (p_) Malloy name. Audit both sides.
  • The query fails when the parameter is unset. Declare a default in the model so the query still runs. For "all" semantics, default to a sentinel value the where-clause treats as no-op.
  • The model uses a Malloy feature the engine does not know. Looky pins one Malloy version. Newer features fail at compile time. Stick to features documented in that version.
  • Two models on the same dashboard declare different parameter names for the same dimension. Cross-filter clicks on that dimension only narrow the model that uses the matching name. Standardise the parameter names across models on the same dashboard.
  • The cache returns stale data. Cache is keyed by parameter combination; if the data changed but the parameter combination is the same, the cached entry still wins until its TTL expires (or the model file changes).