Malloy: a semantic modeling language that compiles to SQL

The problem Malloy solves: measure definitions that drift

SQL is a fine execution language and a poor place to keep definitions. A metric like on-time arrival is a rule, not a query: the US DOT defines it as an arrival delay under 15 minutes with cancelled and diverted flights excluded. Written as `count() { where: arr_delay < 15 } / count()`, that rule silently counts cancellations as on-time, and nothing in the SQL tells you so. Copy that expression into twelve dashboards and you have twelve slightly different metrics wearing the same name.

Malloy puts the definition in a source and gives it a name. The README's example defines `on_time_rate` once, with `cancelled = 'N' and diverted = 'N' and arr_delay < 15` in the numerator and a matching filter in the denominator, and every query that references the measure gets that rule. The audience is analysts and analytics engineers who write SQL today and want the definitions to live somewhere other than a BI tool's calculated field or a dbt model's comments.

How Malloy compiles a model into SQL and runs it on your engine

Malloy is two things at once: a semantic modeling language and a query language. It does not store data and it does not execute queries itself. The README states that Malloy compiles to SQL and embeds SQL, and that it uses an existing SQL engine to execute. The supported engines are BigQuery, Snowflake, DuckDB, MotherDuck, PostgreSQL, MySQL, Trino, Presto and Databricks.

The flow is visible in the repository layout. A `.malloy` file defines sources, measures and queries. The compiler in `packages/malloy` turns that into SQL for a target dialect, and per-engine packages such as `packages/malloy-db-duckdb`, `packages/malloy-db-bigquery` and `packages/malloy-db-snowflake` carry the dialect-specific work. `packages/malloy-connections` is the aggregator that registers the backends. Results come back as data, and `packages/malloy-render` handles visualization. Publisher, a separate repository, serves the same models over REST and MCP so other applications can query them.

The design choice worth noting: because execution stays in the warehouse, Malloy cannot be faster than the engine underneath it, and a query that is slow in SQL will be slow in Malloy. What the language changes is how the query is expressed, not how it runs. Nested data is the clearest case. Arrays of records and query pipelines are first-class syntax rather than workarounds, which matters if your warehouse returns repeated fields and you have been flattening them in application code.

Installing Malloy and running a first query from Node.js

The README gives four installation paths: the VS Code extension, the npm packages, the standalone CLI, and Publisher. The npm route requires Node.js 20 or newer, per the package engines field and the README badge.

Install the compiler and the connections package. Importing the connections package registers all supported database backends, so you do not wire up engines individually.

npm install @malloydata/malloy @malloydata/malloy-connections

The README's Node.js example builds a runtime with default connections enabled, loads a query against DuckDB, and prints the rows. The query defines a source with a measure, then groups by category and aggregates the measure by name.

require('@malloydata/malloy-connections');

const {MalloyConfig, Runtime} = require('@malloydata/malloy');

async function main() {
  const config = new MalloyConfig({
    includeDefaultConnections: true,
  });
  const runtime = new Runtime({config});

  const result = await runtime.loadQuery(`
    source: sales is duckdb.sql("""
      SELECT * FROM (VALUES
        ('books', 20),
        ('books', 30),
        ('games', 40)
      ) AS sales(category, revenue)
    """) extend {
      measure: total_revenue is revenue.sum()
    }

    run: sales -> {
      group_by: category
      aggregate: total_revenue
    }
  `).run();

  console.table(result.data.toObject());
  await runtime.shutdown();
}

main().catch(console.error);

What you should see is a table with one row per category and a summed revenue column. Other databases are configured through `MalloyConfig` rather than by changing the query.

For scripting and CI, the README points at a separate CLI package. It can run queries, compile them to SQL, and build persistent tables from sources marked `#@ persist`.

npm install -g malloy-cli
malloy-cli run my_query.malloy

Connections for the CLI live in `~/.config/malloy/malloy-config.json`, covering BigQuery, Snowflake, DuckDB, PostgreSQL, MySQL, Trino, Presto and Databricks, with MotherDuck reached through DuckDB. If you want to serve models to other applications rather than run them by hand, Publisher starts a server against a directory of `.malloy` files.

npx @malloy-publisher/server --port 4000 --server_root path/to/your/models

The README says `http://localhost:4000` lets you browse models, run queries, and retrieve MCP endpoints.

Where Malloy is the wrong tool

Malloy adds a language. That is a real cost, and there are teams that should not pay it. If your work is one-off SQL against a table, a `.malloy` file is an extra artifact between you and the answer. If nobody on the team will maintain the models, the definitions rot in a new place instead of the old one, which is worse because the new place claims to be authoritative.

The runtime constraint is concrete. The npm packages require Node.js 20 or newer, so a Python-only or JVM-only shop has to stand up a Node toolchain to use the compiler, the CLI or Publisher. The VS Code extension avoids that for interactive work, but it does not remove the dependency if you want models served to an application.

Engine coverage is broad but it is a list, not a guarantee. If your engine is not among BigQuery, Snowflake, DuckDB, MotherDuck, PostgreSQL, MySQL, Trino, Presto and Databricks, the README does not describe a path for you. And because execution is delegated, Malloy inherits every limitation of the engine it targets: dialect quirks, permission models, query cost. The README says nothing about how much of a given engine's SQL surface is reachable from Malloy, so if you depend on a specific function or hint, that is something to check before committing.

One more thing the README notes: the VS Code extension collects a small amount of anonymous usage data, with an opt-out in the extension settings. That is a policy question, not a technical one, but it belongs in the adoption decision.

Malloy compared with dbt and with raw SQL plus a BI layer

The nearest alternative for a SQL-first team is dbt. Both put definitions in version-controlled files; the difference is where the abstraction sits. dbt models are SQL files that materialize into tables and views, and the warehouse does the work at build time. Malloy sources are compiled at query time and never require materialization, which means a measure is available the moment the model file is saved, but it also means the semantic layer is only as reliable as the runtime that compiles it. If you need scheduled, persisted transformations with dependency graphs, dbt is built for that and Malloy is not: the README's persistence story is limited to the CLI's `build` command for sources marked `#@ persist`.

The other alternative is what most teams already have: SQL in the BI tool, with metric definitions living in calculated fields. That approach has no compiler, no model files and no Node dependency, and it is genuinely faster to start. Its failure mode is the one Malloy was built to address: the same metric defined in several places, diverging quietly. The honest comparison is that Malloy trades a toolchain dependency for a single definition, and that trade is only worth making when the divergence is actually hurting you.

Licence and the cost of keeping up with a 0.0.x language

The repository's `package.json` declares `"license": "MIT"`, and the README carries an MIT badge. The repository metadata reports the licence as NOASSERTION, which is what GitHub shows when it cannot match the LICENSE file to a known template, so read the LICENSE file itself rather than relying on the badge. MIT is permissive, which means embedding the compiler in a commercial product is not the obstacle here. This is a description of the licence text, not legal advice.

The upgrade cost is the part to plan for. The latest release is v0.0.434, published on 2026-09-18, following v0.0.433 on 2026-08-28 and v0.0.432 on 2026-08-20. That is a fast cadence, and the version number says the language has not reached 1.0. A `.malloy` file is source code you own, so a breaking change in the language or the compiler API lands on you. The repository does ship a CHANGELOG.md at the top level, which is where to look before bumping the version. The `package.json` also marks the root package as `"private": true`; the publishable artifacts are the workspace packages under `packages/`, such as `@malloydata/malloy` and `@malloydata/malloy-connections`, not the repository root. Pin your versions and read the changelog between them.

Editorial conclusion

Adopt Malloy if you have measures whose definitions keep drifting between dashboards, or if nested and repeated data is normal in your warehouse and SQL workarounds have become the job. Do not adopt it if you cannot standardize on Node.js 20 or newer, if you need a production query engine you maintain yourself, or if your team will not accept a second language next to SQL. Verify first: that your target engine is on the supported list, that the npm packages and malloy-cli install cleanly in your environment, and that your BI or application layer can consume the Publisher REST and MCP interfaces. The repository is not archived and the last push was on 2026-09-22, so the release cadence is current, but Malloy is still at v0.0.434 and the language is the part that changes.

Frequently asked questions

Official sources

1. Issues
2. malloydata/malloy on GitHub
3. Project website
4. README
5. Releases