cloudparts

Malloy

A modelling and query language from Looker co-founder Lloyd Tabb that compiles to SQL: define joins and measures once in a source, then write short queries, including nested ones, that run on DuckDB, BigQuery, Snowflake, PostgreSQL and other engines.

Linux Foundation project
What it is
Languages that compile to SQL
Used for
Semantic layers
Type
Open source
Made by
Malloy project (LF Projects, started at Google by Lloyd Tabb’s team)
Foundation
The Linux Foundation. A series of LF Projects, LLC
License
MIT
Facts checked
23 October 2026
Website Source code

What it is

Malloy has two layers. A source is a semantic model on top of a table or query: it names joins, dimensions and measures, such as an on-time rate that excludes cancelled and diverted flights. A query starts from a source and lists what to group_by, aggregate, filter with where and sort; results are sorted by the first aggregate unless you say otherwise. Queries can be saved in the source as views and refined later, and each query can serve as the source for the next, so analyses are built from small reusable pieces. The Malloy compiler turns all of it into SQL for the connected database and runs it there.

Two things set it apart from hand-written SQL. Measures stay correct across joins: Malloy tracks which table an aggregate belongs to, so a sum over a joined table doesn’t double-count rows that the join fanned out. And nest: puts a whole sub-query inside each result row, so “carriers, with their top airports inside each carrier” comes back as one result with an array per row; on DuckDB the compiler builds this in a single SQL statement using grouping sets and LIST. The README lists BigQuery, Snowflake, DuckDB, MotherDuck, PostgreSQL, MySQL, Trino, Presto and Databricks. Tooling includes a VS Code extension for writing models and charting results, npm packages, the malloy-cli command line (run, compile, build, plus an MCP server), and Publisher, a server that exposes models through REST and MCP APIs.

Why teams pick it

  • Business logic lives in one model, and every query reuses the same measures and joins instead of copying SQL
  • Aggregates stay correct across one-to-many joins without hand-written subqueries or DISTINCT tricks
  • Nested results in one query, useful for dashboards and for nested data such as arrays of records
  • Runs on the database you already have; malloy-cli compile shows the SQL it will send

Watch out for

  • Another language to learn, with a much smaller community than SQL or dbt; BI tools and SQL clients don’t speak it, so you go through the VS Code extension, the CLI, Publisher or the npm libraries
  • Mostly for reading and analysing data, not for writes or DDL; malloy-cli build can create tables from queries
  • The npm packages are still versioned 0.0.x (0.0.435 in October 2026); the language itself has promised code compatibility since 4.0 in 2023
  • Generated SQL for nested queries is long and engine-specific, which makes it harder to read and tune than hand-written SQL

Alternatives

What all languages that compile to sql have in common →Compare side by side →
IbisOpen sourceA Python dataframe API that compiles to SQL, so the same code runs on DuckDB on a laptop and on BigQuery, Snowflake, PostgreSQL or more than 20 other engines.LegendOpen sourceGoldman Sachs’s data modelling and governance platform, open-sourced through FINOS: describe business concepts once in the Pure language, map them to databases and services, and query or publish them as APIs while the engine generates the SQL.PRQLOpen sourcePipelined Relational Query Language (“Prequel”): a query language where each line transforms the result of the line before, compiled by a Rust compiler into SQL for DuckDB, PostgreSQL, ClickHouse and other databases.

How it fits with other technologies

A source with a reusable measure, and a query that nests airports inside carriers · text

source: flights is duckdb.table('flights.parquet') extend {
  measure:
    flight_count is count()
    on_time_rate is
      count() { where: cancelled = 'N' and diverted = 'N' and arr_delay < 15 }
      / count() { where: cancelled = 'N' and diverted = 'N' }
}

run: flights -> {
  group_by: carrier
  aggregate: flight_count, on_time_rate
  nest: by_origin is {
    group_by: origin
    aggregate: on_time_rate
  }
}

// npx malloy-cli run flights.malloy
// [{ "carrier": "UA", "flight_count": 4, "on_time_rate": 0.667,
//    "by_origin": [{ "origin": "ORD", "on_time_rate": 1 },
//                  { "origin": "SFO", "on_time_rate": 0 }] }, ...]
// npx malloy-cli compile flights.malloy prints the single DuckDB query it runs

See it in a real stack

No architecture uses Malloy yet. Drop it into the playground and wire it up yourself.Open playground →

Worth exploring

WebsiteLanguages that compile to SQLajnsit and contributors, on GitHubA short, MIT-licensed list of query languages that compile to SQL, grouped as strongly typed (Ermine), alternate query syntax (GraphQL via Hasura, LINQ, Malloy, PRQL) and logic programming (Datalog, Yedalog, Logica, Constraint Handling Rules), plus a few alternatives that don’t compile to SQL, such as EdgeDB and Morel. A quick map of the landscape with one line and a link per entry; it is small and some descriptions are dated.
All resources →
Where it came from2021–2026

Lloyd Tabb co-founded Looker and was the lead inventor of LookML, the modelling language from which Looker generates SQL. After Looker became part of Google he kept working on the same problem there: SQL is complete, but verbose and without reusable parts, and real data is often nested rather than rectangular. With a small Google team he started Malloy, whose repository dates from August 2021; early versions compiled to SQL for BigQuery and PostgreSQL and were described as an experimental language.

The team added DuckDB, data notebooks and a command line, and wrote about the language’s design on its blog. Malloy 4.0, in October 2023, cleaned up the syntax (extend, view:, run:, select:) and dropped the word “experimental”, with a promise to keep existing code working. The project now lives under LF Projects, the Linux Foundation’s entity for hosting open-source projects, with committers listed in its contributing guide, and its newer work includes Publisher, MCP access for AI agents and tooling for writing models with LLMs.

Why it’s built this way

Compile to SQL, don’t build an engine
Data already lives in BigQuery, Postgres, Snowflake and DuckDB. Generating SQL lets Malloy run where the data is and lean on each engine’s optimizer.
A semantic model in the language itself
LookML’s lesson: measures and joins should be defined once. Malloy makes the model and the queries one language, so a query can extend the model and leave reusable pieces behind.
Nested results as a first-class feature
Tabb argued that data isn’t rectangular. nest: returns hierarchies in one query, which also matches how dashboards drill down.
Break the syntax once, then freeze it
Malloy 4.0 made all the incompatible changes in one release, with warnings in the VS Code extension first, so later versions could promise compatibility.

Timeline

  1. 2021

    The Malloy repository starts (August)

  2. 2022

    First npm release of @malloydata/malloy (September)

  3. 2023

    Malloy 4.0 ends the “experimental” label (October)

  4. 2026

    Release 0.0.435 (October); the CLI now includes an MCP server

Sources9 · facts checked 23 October 2026

Improve this page on GitHub