Looky
Back to site

Docs / Build Workflow

Malloy support

The Malloy version Looky runs

Looky ships a specific, pinned version of Malloy — currently @malloydata/malloy 0.0.346, 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/ec_orders_base.malloy — shared foundation
##! experimental.parameters

source: ec_orders_base() is ecommerce.table('bigquery-public-data.thelook_ecommerce.order_items') extend {
  join_one: products is ecommerce.table('bigquery-public-data.thelook_ecommerce.products')
    on product_id = products.id
  measure: revenue is sum(sale_price)
}

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

source: ec_revenue() is ec_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. (The engine also accepts the full internal name, but the stripped form is the convention every example here follows.)

##! experimental.parameters

source: ec_orders(
  p_country::string  is "all",
  p_start_date::date is @2024-01-01
) is ecommerce.table('bigquery-public-data.thelook_ecommerce.order_items') extend {
  join_one: users is ecommerce.table('bigquery-public-data.thelook_ecommerce.users')
    on user_id = users.id
  dimension: country is users.country

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

A dashboard select filter with param: country then binds a picked value like "MX" to p_country; a cross-filter click on a country bar does exactly the same without any declaration. Use the same parameter name for the same dimension across every model on a dashboard — that is what makes one filter or one click narrow them all together.

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 warehouse.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). The substitution works identically on all three adapters — but the SQL body runs in the source's own dialect, and this example's ::date casts are Postgres syntax. On MySQL write CAST(@date_from AS DATE) instead; on BigQuery COALESCE(CAST(@date_from AS DATE), …). See the source adapter comparison for the full divergence list.

(warehouse here is the workspace's own connection alias from runtime/sources.runtime.yml — alias names are yours, not the adapter names.)

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.

A cached entry lives until its TTL expires or the model file changes. 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: ec_orders(
  p_status::string is "all"
) is ecommerce.table('bigquery-public-data.thelook_ecommerce.order_items') extend {
  view: detail is {
    where: p_status = "all" or status = p_status
    select: *
  }
}

Optional parameter with a null default and coalesce guard

The other working idiom for "no value means no filter" — used throughout production workspaces:

##! experimental.parameters

source: ec_orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is ecommerce.table('bigquery-public-data.thelook_ecommerce.order_items') extend {
  dimension: order_date is created_at::date
  view: revenue is {
    where:
      order_date >= coalesce(p_date_from, order_date),
      order_date <= coalesce(p_date_to, order_date)
    aggregate: revenue is sum(sale_price)
  }
}

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

##! experimental.parameters

source: orders(
  p_date_from::date is null,
  p_date_to::date   is null
) is shop.sql("""
  SELECT o.sale_price, o.created_at
  FROM orders o
  WHERE (CAST(@date_from AS DATE) IS NULL OR DATE(o.created_at) >= CAST(@date_from AS DATE))
    AND (CAST(@date_to   AS DATE) IS NULL OR DATE(o.created_at) <= CAST(@date_to   AS 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). Note the MySQL dialect uses CAST(… AS DATE) — never Postgres's ::date.

Month string parameter

##! experimental.parameters

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