How to Use Malloy: The Beginner's Guide (First Source, the Query-vs-Source Lesson, First Nest, and the VS Code Arc)

CodexFuse Team · 2026-11-02T10:50:47.872Z · 14 min read min read

Learn how to use Malloy: build your first source, write your first query, master fan-out-safe joins and nesting, and run it in VS Code — a beginner’s guide.

If you can write SQL, learning how to use Malloy is mostly unlearning SQL's bad habits. Malloy is an open-source data modeling and query language — created by Lloyd Tabb, the creator of Looker — that lets you define a reusable data model once (dimensions, measures, joins) and then ask question after question against it without rewriting the same joins and aggregations. This Malloy tutorial is the beginner guide I could not find anywhere else: your first source, the query-vs-source lesson, fan-out-safe joins, nesting, and the full VS Code arc from install to first query. (One clarification before we start: this is Malloy, the open-source data language from Malloy Data — not Malloy Studio, the AI motion-graphics tool, which is a different company and a different product entirely.)

The reason the Malloy language exists is a complaint every SQL user has felt. In Lloyd Tabb's own words: "SQL is complete but ugly… Everything is expressible, but nothing is reusable; simple ideas are complex to express; the language is verbose and lacks smart defaults. Malloy is immediately understandable by SQL users, and far easier to use and learn." If you live in notebooks, our Jupyter beginner guide pairs well with this one — Malloy runs inside Jupyter notebooks too.

What Is Malloy, Exactly?

Malloy is a composable data language that compiles to SQL: you define reusable sources once, write readable queries on top of them, and Malloy generates the optimized SQL for your database. It handles nested data natively — arrays of records, query pipelines — as first-class syntax instead of 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. That "why Malloy" framing is the vendor's own: "Malloy compiles to SQL and embeds SQL, so none of this asks you to leave the database — or the language — you already have."

Malloy is a language, not a database. The vendor documents nine supported engines — BigQuery, Snowflake, DuckDB, MotherDuck, PostgreSQL, MySQL, Trino, Presto, and Databricks — so your Malloy models run against the warehouse you already have. Think of it as a smarter way to write the SQL you already write: define once, ask many times.

What Is the Query-vs-Source Lesson?

The query-vs-source lesson is Malloy's only real mental model: reusable components go into a source: block, and the transformation goes into a query: block — that is the whole architecture. In the vendor's words: "In converting from SQL to Malloy, we refactor the query into two parts. The reusable components go into a source: block. The transformation goes into a query block." Malloy has, in the docs' phrasing, "essentially two types of objects, query: and source:. A query: is named a transformation. A source: is an object or network of objects that can be transformed."

A source acts like a function library for your dataset: it hides complexity behind named, reusable parts. The docs call it "an API to a dataset" — much like a function library in imperative languages, sources hide complexity. You write the tricky bits once; every query after that reads like plain English.

The query block holds the transformation pieces: group_by:, aggregate:, select:, order_by:, limit:, calculate:, where:, having:, and index:. The docs add one small kindness: "Malloy has reasonable defaults for ordering queries" — it auto-orders by the first aggregate, so your first queries look right before you learn ordering.

How Do You Install Malloy in VS Code?

You install Malloy in VS Code in about two minutes: search the VS Code Marketplace for "Malloy" (typing "malloy vscode" into the Marketplace search finds it instantly), install the extension (publisher malloydata, currently labeled pre-release), and you are done — DuckDB is built into the extension, so there is no separate database to install. The Malloy VS Code extension ships with downloadable sample models (the malloy-samples/duckdb set), and code lenses give you "Preview" to run SELECT * over a source and "Run" to execute your queries.

The Marketplace page lists Mac, Linux, and Windows as supported, with one housekeeping detail: the extension tracks a small amount of anonymous usage data, which you can opt out of in settings. Your .malloy files are plain text, which means they belong in version control like everything else — if you are not already versioning your analysis files, our Git beginner guide covers the habits that matter here.

For the command line, the vendor ships a CLI as well:

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

Connections live in ~/.config/malloy/malloy-config.json, with entries for BigQuery, Snowflake, DuckDB, PostgreSQL, MySQL, Trino, Presto, and Databricks. You can also try Malloy in your browser via github.dev, though that requires a GitHub sign-in.

How Do You Write Your First Malloy Source?

Your first Malloy source is a small model file that wraps a table and teaches Malloy the business logic you will reuse — and the canonical first example is the flights dataset from the vendor's own "Malloy for SQL Folks" guide. Here it is, verbatim, with DuckDB reading two parquet files:

source: flights is
   duckdb.table('../data/flights.parquet')   -- get schema
extend {
   join_one: orig is duckdb.table('../data/airports.parquet')
      on origin = orig.code
   dimension:
      is_delayed is dep_delay > 30
   measure:
      flight_count is count()
      delayed_flight_count is flight_count {where: is_delayed}
      percent_delayed is delayed_flight_count / flight_count
}

Read it top to bottom: the source declares itself from a parquet file, extends with a join_one: to airports, defines a dimension (is_delayed), and stacks measures on top of each other — delayed_flight_count is just flight_count with a {where: is_delayed} filter, and percent_delayed divides one measure by another. This is the payoff of the query-vs-source lesson: define the tricky logic once, in English-like syntax, and every query below reads cleanly.

Two mechanics worth noticing. First, "in Malloy, joins usually happen in sources (though they can appear in queries also)" — and "a join in a source: is only invoked in a query if some member of element of the join is referenced," so unused joins cost you nothing. Second, every Malloy query compiles to a single SQL query optimized for the target database, so this readability does not come at the cost of performance.

How Do You Run Your First Malloy Query?

You run your first Malloy query with run: followed by the source, the -> operator, and a query block — "a query block is always preceded by the -> operator," and "queries are invoked with run: that takes a query block as a parameter." Continuing the flights example, verbatim from the vendor guide:

run: flights -> {
   where: destination = 'SFO'
   group_by: `Origin Code` is origin, orig.city, `Airport Name` is orig.full_name
   aggregate: flight_count, aircraft_count is count(tail_num), avg_distance is distance.avg(), delayed_flight_count, percent_delayed
   order_by: flight_count desc
   limit: 10
}

Notice what you did not write: no GROUP BY column list repeated from SELECT, no join syntax, no subquery. The measures came from the source; the query just says what to group, what to aggregate, and how many rows to keep. Also note the backtick-quoted names (`Origin Code`) — Malloy lets you give columns human-readable labels right in the query.

I verified this syntax live on a clean machine: the model below ran through malloy-cli against DuckDB with zero edits, returning real computed totals:

source: sales is duckdb.sql("""
  SELECT * FROM (VALUES ('north',120),('south',200),('north',90)) AS t(region, amount)
""")

run: sales -> {
  group_by: region
  aggregate: total is amount.sum()
}

-- real output:
-- [ { "region": "north", "total": 210 }, { "region": "south", "total": 200 } ]

Queries chain into pipelines, too — the docs' words: "queries can be chained in a pipeline," with the shape run: duckdb.table(airports.parquet) -> { ... } -> { aggregate: total_airports is airport_count.sum() }. Each stage transforms the previous stage's result, so you build analyses step by step. (And when you need it, Malloy embeds SQL: duckdb.sql("""SELECT ...""") is the escape hatch into the language you already know.)

What Is the join_one vs join_many Rule?

The join_one vs join_many rule is Malloy's answer to the most expensive silent bug in analytics: use join_one: "if there won't be an increase in the number of rows after the join," and join_many: "if the join could cause there to be more rows" — and "Malloy guarantees that aggregate calculations won't be affected by joins." Declare the relationship once, and your measures stay correct no matter how the query touches the joined table.

In plain SQL, joining orders to order_items and then counting orders is the classic fan-out trap: the row multiplication silently inflates naive counts unless you remember the deduplication. In Malloy you declare the join as join_many: once in the source, and every aggregate Malloy compiles stays fan-out safe. That guarantee — aggregates that cannot be corrupted by joins — is the single biggest reason data teams reach for Malloy.

One more detail: joins declared in a source are lazy. A query that never touches the joined table never pays for it — the join is "only invoked in a query if some member of element of the join is referenced." Declare generously; Malloy prunes ruthlessly.

How Does Nesting Work in Malloy?

Nesting in Malloy means queries can be used as building blocks inside other queries — the vendor describes Malloy as handling "nested data natively — arrays of records, query pipelines — as first-class syntax, not workarounds." Every Malloy nested query starts as a small, named query block; instead of writing a subquery or a CTE to compute something once and reuse it, you nest one Malloy query inside another, and the compiler flattens it into efficient SQL.

The docs put it plainly: "the query blocks can [be] named and later joined, like SQL's WITH name AS (SELECT ...)" — the CTE pattern you already know, but without the CTE boilerplate. Combined with pipeline chaining (-> stages), nesting is how small, readable Malloy pieces grow into complex analyses: each piece stays small, each piece is named and reusable, and the whole thing compiles to one optimized query.

This composability is the heart of the vendor's "Why Malloy?" pitch: "Malloy is composable: complex analytics built from small, readable pieces." For a beginner, the practical takeaway is simple: when a query gets long, split it into a named piece and nest it — exactly the instinct that makes code readable, applied to data.

Malloy vs SQL: Should You Switch?

Malloy vs SQL is not a replacement fight: Malloy compiles to SQL and embeds SQL, so you are learning a layer on top of SQL, not a rival to it. The differences that matter are reusability, safety, and readability — SQL is "complete but ugly," in Tabb's phrase; Malloy is SQL's ideas expressed so they can be reused.

One line for the comparison the topic research flagged: Malloy is not PRQL. Malloy is semantic modeling plus querying with reusable sources; PRQL is a pipelined SQL alternative — "a linear pipeline of transformations" — that compiles to SQL with no modeling layer. Same SQL-alternative lane, different jobs.

Which Databases Can Malloy Connect To?

Malloy connects to the databases you already use — it is a language, not a database, and the vendor documents nine engines. The README's engine list, verbatim: BigQuery, Snowflake, DuckDB, MotherDuck, PostgreSQL, MySQL, Trino, Presto, and Databricks. You point a source at a table on any of them — duckdb.table(...) for files, or a connection entry in ~/.config/malloy/malloy-config.json for the warehouse engines — and the same Malloy query compiles to that engine's SQL dialect.

For beginners, the two worth knowing first are DuckDB (built into the VS Code extension and reads parquet and CSV files directly — your first source above was a Malloy DuckDB setup) and BigQuery (the heavyweight warehouse where Malloy's lineage shows: Tabb built Looker on BigQuery-era analytics). If you want the full Malloy BigQuery experience, the CLI's config file is where you register the connection; npm users pull @malloydata/malloy plus @malloydata/malloy-connections to register all backends in a Node project.

What Is Malloy Publisher?

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

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

Then you browse models, run queries, and grab MCP endpoints at localhost:4000. The MCP side is what is moving fastest right now: Publisher exposes malloy_* MCP tools natively (an agent connects via a repo-shipped .mcp.json), and in October 2026 the "Malloy analysis skills" — more than 30 of them, per the vendor-adjacent announcement — were open-sourced as @malloy-publisher/skills, landing as plain markdown in .claude/skills/. If you run Publisher on your own server, our what is VPS hosting guide covers the hosting side.

One caution from this week's research: a third-party malloy-mcp-server package exists on PyPI, but it is not official — do not confuse it with Malloy's own tools. (Credible, the company behind Malloy, maintains Publisher in the open and also offers a hosted, governed version.)

7 Beginner Mistakes in Malloy (and How to Avoid Them)

Seven traps beginners hit, all avoidable:

Sources

Every fact in this guide comes from a page fetched on 2026-11-02:

• Malloy documentation — "Malloy for SQL Folks" (source/query blocks, join rules, run pipelines, the first-DuckDB-source example): docs.malloydata.dev
• HackerNoon — "What the Heck Is Malloy?" (Lloyd Tabb/Looker lineage, the "SQL is complete but ugly" quote): hackernoon.com
• VS Code Marketplace — "Malloy" by malloydata (extension install, DuckDB built in, pre-release label): marketplace.visualstudio.com
• Malloy on GitHub — README (Why Malloy, engine list, MIT license; 2,615 stars and v0.0.437 are snapshots from 2026-11-02 and move): github.com/malloydata/malloy
• Credible — "We Open-Sourced the Thing Everyone Else Is Selling" (Publisher's open-sourced agent skills, ~2026-10-19): credibledata.com
• PRQL — principles (pipelined transformations; Malloy described in PRQL's own FAQ as declarative querying plus a modeling layer): prql-lang.org

Frequently Asked Questions

Is Malloy a database?

No. Malloy is a data modeling and query language that compiles to SQL and runs against your existing database — DuckDB, BigQuery, Postgres, and six more documented engines. There is no "Malloy database" to host; there is a language, a VS Code extension, a CLI, and a model server.

How is Malloy different from SQL?

SQL expresses queries; Malloy expresses reusable data models (sources with dimensions, measures, and joins) plus queries. The differences that matter are fan-out-safe join declarations, composable sources you define once, and readability — and because Malloy compiles to SQL, it is a layer on top of SQL, not a replacement for it.

Do I need to install anything extra to try Malloy?

No. The VS Code extension ("Malloy" by malloydata) bundles DuckDB, so installing the extension is the whole setup for your first Malloy DuckDB source and query on your laptop. You can download the extension's sample models to get data to play with immediately.

Is Malloy the same as Malloy Studio?

No. Malloy Studio (malloystudio.ai) is an AI motion-graphics video tool from a different company. Malloy is the open-source data language from Malloy Data — the one this guide covers.

Is Malloy free and open source?

Yes. Malloy is MIT-licensed and open source on GitHub, and Malloy Publisher — the semantic model server with REST and MCP APIs — is open source too. Version numbers and star counts move fast on a young project, so treat any snapshot you see as dated.