Malloy

A semantic modeling and query language built on top of SQL

Try in Browser · Quickstart · Docs · Slack · YouTube

---

What is Malloy?

Malloy is an open source language for describing data relationships and transformations. It is both a semantic modeling language and a query language that uses an existing SQL engine to execute queries.

Supported SQL engines: BigQuery · Snowflake · DuckDB · MotherDuck · PostgreSQL · MySQL · Trino · Presto · Databricks

Why Malloy?

Malloy is composable: complex analytics built from small, readable pieces. It handles nested data natively — arrays of records, query pipelines — as first-class syntax, not workarounds. And every query you write leaves behind a semantic model, so the work you do to answer one question is already there for the next. Malloy compiles to SQL and embeds SQL, so none of this asks you to leave the database — or the language — you already have.

---

How Malloy Works

---

Quick Start

There are four ways to install Malloy:

Install the VSCode Extension

The easiest way to try Malloy is the VSCode Extension, which facilitates building Malloy data moels, querying and transforming data, and creating simple visualizations and dashboard. You can do this locally, or try it in the browser (no install), which opens a live Malloy notebook in github.dev (GitHub's in-browser VSCode; requires a GitHub sign-in).

Follow the instructions for connecting Malloy to your database.

Note: The Malloy VSCode Extension collects a small amount of anonymous usage data. You can opt out in the extension settings. Learn more.

Use the npm packages

To use Malloy in Node.js, install the compiler and a database connector. For example:

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

Importing @malloydata/malloy-connections registers all supported database backends.

Run a query from Node.js:

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);

This defines total_revenue once in the semantic model, then uses it by name in the query. Other databases can be configured through MalloyConfig.

Run Malloy from the command line

For scripting, pipelines, or CI, install the standalone Malloy CLI:

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

It can run queries, compile to SQL, and build persistent tables from sources marked #@ persist.
Connections are configured in ~/.config/malloy/malloy-config.json (BigQuery, Snowflake, DuckDB, PostgreSQL, MySQL, Trino, Presto, Databricks; MotherDuck via DuckDB).

See the Malloy CLI docs and malloy-cli repo for more details about using the CLI

Serve models with Publisher

Publisher is the open-source semantic model server for Malloy. It serves your .malloy models through REST and MCP APIs so apps, BI tools, and AI agents can query them through one interface:

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

Open http://localhost:4000 to browse models, run queries, and grab MCP endpoints. See the Publisher repo for setup, sample models, and deployment options.

---

The Malloy Language at a Glance

A bare Malloy query reads like an outline of what you want:

Malloy

run: duckdb.table('airports.parquet') -> {
  group_by: state
  aggregate:
    airport_count is count()
    avg_elevation is elevation.avg()
}

Equivalent SQL

SELECT
  state,
  COUNT(*) AS airport_count,
  AVG(elevation) AS avg_elevation
FROM 'airports.parquet'
GROUP BY state
ORDER BY airport_count DESC  -- Malloy orders by first aggregate automatically

Malloy is most useful for pinning down measures whose definitions aren't obvious. Take "on-time arrival" - the US DOT defines it as arr_delay < 15 with cancelled and diverted flights excluded. A naive count() { where: arr_delay < 15 } / count() silently treats cancellations as on-time. Encode the rule once, and every dashboard, report, and ad-hoc query agrees:

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

These are two simple examples. The 10-minute quickstart and Malloy by Example cover additional topics like joins, nested results, symmetric aggregates, and the pipe operator.

---

Documentation

Resource	Description
Language Reference	Full language guide
Quickstart	10-minute tour
Malloy by Example	Advanced modeling patterns and idioms
Malloy CLI	Command-line reference for run, compile, build
YouTube Channel	Video demos and walkthroughs

---

Community

• Slack: ask questions, share models, meet other users
• GitHub Issues: bug reports and feature requests
• YouTube: demos and tutorials

---

Contributing

If you would like to contribute to Malloy, please read CONTRIBUTING.md and developing.md, and feel free to contact us on slack.

---

Security

To report a security vulnerability, please follow our Security Policy rather than opening a public issue.