A semantic layer an AI agent builds itself, and a query surface, inside DuckDB
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|
| Linux AMD64 | |||
| Linux ARM64 | |||
| macOS AMD64 | |||
| macOS ARM64 | |||
| Windows AMD64 | |||
| Windows ARM64 | |||
| WebAssembly |
Installing and Loading
INSTALL malloy FROM community;
LOAD malloy;
Example
-- Ask Claude:
-- "Load duckdb with the malloy extension. Run 'malloy;' to learn how it
-- works. Read <your context: questions, existing SQL, docs> and then
-- create a Malloy model so I can ask you questions."
-- Here the context is TPC-H. These are the steps Claude takes.
-- 1. Install and load the extension
INSTALL malloy FROM community;
LOAD malloy;
-- 2. Learn the extension: every command, and where the language help is
malloy;
-- 3. The data: TPC-H, generated by DuckDB's tpch extension
INSTALL tpch;
LOAD tpch;
CALL dbgen(sf = 0.01);
-- 4. Write a model and save it as tpch.malloy (here with COPY, so the
-- example runs as pasted; Claude writes the file directly)
COPY (SELECT '
source: customers is duckdb.table(''customer'') extend {
primary_key: c_custkey
}
source: orders is duckdb.table(''orders'') extend {
primary_key: o_orderkey
join_one: customers with o_custkey
measure:
order_count is count()
revenue is o_totalprice.sum()
view: by_segment is {
group_by: customers.c_mktsegment
aggregate: order_count, revenue
order_by: revenue desc
}
}
') TO 'tpch.malloy' (FORMAT csv, HEADER false, QUOTE '');
-- 5. Load it into this connection
malloy import 'tpch.malloy';
-- 6. Check what it built
malloy describe orders;
-- 7. Answer from the model
run: orders -> by_segment;
-- 8. Keep it: install the model in the database, so every connection can
-- query its sources by name (and later sessions too, in a database file)
malloy install 'tpch.malloy';
About malloy
malloy gives an AI agent a semantic layer it builds itself and a query surface, inside DuckDB.
Point an agent at the extension and have it run malloy;. That lists every
command, and malloy help carries Malloy's language reference, so the agent
learns Malloy from the extension. Give it your tables and whatever context you
have (the questions people ask, existing SQL, documentation) and it writes a
Malloy model: sources, joins, dimensions, measures and views. Later questions
are answered from that model, so "revenue" or "an order's customer" means the
same thing in every answer.
In a cold-start test on TPC-H (sf=1), Claude built a model from the 11 odd-numbered queries, then answered the 11 even ones without changing it. All 22 matched the official answers, with no hand-written SQL.
Malloy aggregates stay correct across joins that fan out, and results can be nested.
-
run:statements, and%{ ... }%for a Malloy query anywhere SQL takes a table - a model per connection (
source:,query:,malloy import 'model.malloy'), or installed in the database for every connection (malloy install) -
malloy sources,malloy describe <source>: what a model contains, with each field's description
Documentation: https://github.com/lloydtabb/malloy-duckdb
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| malloy | table | NULL | NULL | |
| malloy_check | table | NULL | NULL | |
| malloy_compile | table | NULL | NULL | |
| malloy_define | table | NULL | NULL | |
| malloy_describe | table | NULL | NULL | |
| malloy_describe_text | table | NULL | NULL | |
| malloy_error | table | NULL | NULL | |
| malloy_export | table | NULL | NULL | |
| malloy_help | table | NULL | NULL | |
| malloy_help_topic | table | NULL | NULL | |
| malloy_help_topics | table | NULL | NULL | |
| malloy_import | table | NULL | NULL | |
| malloy_install | table | NULL | NULL | |
| malloy_reset | table | NULL | NULL | |
| malloy_session | table | NULL | NULL | |
| malloy_sources | table | NULL | NULL | |
| malloy_sql | table | NULL | NULL | |
| malloy_uninstall | table | NULL | NULL | |
| malloy_update | table | NULL | NULL | |
| malloy_version | table | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| allow_parser_override_extension | Allow extensions to override the current parser. | VARCHAR | GLOBAL | [] | fallback |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |