August 5, 2026
Hadia Ahmed
One of the most common questions we get from customers and engineers evaluating PyDough is: 'how is this different from Malloy or PRQL?' It's a fair question. All three let you avoid writing raw SQL, all three compile to SQL, and all three are open source. But each system is built around a different assumption about where complexity in analytics actually lives. Those assumptions drive different designs for the query layer—what it is responsible for, what it abstracts, and what it leaves to the query author.
This post focuses on those differences. We’ll walk through how each system:
Before looking at syntax or performance, it’s useful to understand the mental model each system imposes. This is where most of the real differences originate.
All three tools—PRQL, Malloy, and PyDough—change how queries are written. But more importantly, they change how users are expected to reason about data. That shift determines:
Malloy gives you a source and measure model. You define your data sources, their relationships, and reusable calculations once in a semantic model file. Queries then reference that model. You think in terms of sources, dimensions, and measures, what the data is and how it relates and Malloy handles the SQL from there.
It combines a semantic modeling layer with a query language, and excels at nested, hierarchical result sets that would require complex CTEs in SQL. Its VS Code extension is the primary interface, with DuckDB bundled so you can start immediately. Queries execute eagerly when run in VS Code or via API call.
That semantic model file is written in Malloy syntax by a human. It defines what sources exist, how they join, and what calculations are reusable. Those reusable calculations (dimensions and measures) are expressions that define how to compute something: revenue as price times quantity, for example. They can be referenced across any query, but the model doesn’t constrain where or how they’re combined
PRQL gives you a pipeline model. You think in terms of transformations; start from a table and chain operations in the order you naturally think about them: filter, derive, group, sort, take. Each line transforms the result of the previous one.
SQL's clause ordering, SELECT, FROM, WHERE, GROUP BY, doesn't match how most people reason about data. PRQL fixes that with a clean linear pipeline, a Rust-based compiler with native support in ClickHouse and Databricks, Jupyter integration, and bindings in Python, JavaScript, R, and Rust.
PyDough gives you a collections and relationships model. You think in terms of your data as a hierarchy, collections that contain properties and sub-collections, and traverse those relationships by name. It’s a similar mental model to document databases like MongoDB, you navigate a hierarchy of nested objects rather than joining flat tables. The underlying join logic is encoded once in a knowledge graph and join logic disappears from query code.
PyDough is Python-native. It's not a separate language with Python bindings, it is Python. Queries compose lazily as Python expressions and nothing executes until you explicitly ask for results.
Three different mental models, three different design philosophies, but they all compile down to SQL that your database executes. Malloy and PRQL position themselves as SQL replacements, and for query authoring they largely are. PyDough makes no such claim. It was designed as an intermediate layer between natural language and SQL, not a replacement for it. What changes across all three is how you write queries and what guarantees you build into them along the way.
The differences between these systems become more pronounced when queries are not written by a human, but generated programmatically—particularly by large language models (LLMs).
When an LLM generates analytical queries, the hardest problem isn't syntax. LLMs can produce SQL-looking text just fine. The hard problem is correctness, specifically joins. A schema with many related tables produces countless ways to join them, most of which are wrong.
An LLM generating PRQL still has to write every join condition in every query. It has to know the join keys, get the cardinalities right, and avoid fan traps. PRQL has no schema awareness, it compiles whatever is written. When the model gets a join wrong, the query runs, returns numbers, and looks plausible.
Malloy is better; join relationships are defined in the source model, so if the LLM references them correctly, the joins are handled. But an LLM can still generate syntactically valid but logically incorrect queries, referencing wrong aggregation levels, misusing measures, or combining dimensions in ways that produce misleading results.
PyDough was designed specifically for this problem. Instead of requiring the model to specify joins, PyDough exposes only valid relationship traversals defined in a knowledge graph. Every relationship traversal is validated against the graph at parse time, before any SQL is generated. If a relationship isn't defined in the graph, it cannot be expressed in a query because the language structurally cannot express it. An LLM generating PyDough literally cannot produce a wrong join or inject malicious SQL, because the language itself cannot express those things.
Beyond safety, PyDough is the only one of the three built for agentic AI workflows:
And because PyDough runs 25+ optimization passes automatically, the SQL it produces is not just correct but efficient. Filter pushdown, column pruning, aggregate splitting, all handled automatically by PyDough while the LLM just focuses on writing PyDough code. At scale, when an AI system is generating hundreds of queries against a cost-per-byte engine like Snowflake, that efficiency compounds.
Because PyDough is newer and less represented in LLM training data than SQL or even PRQL, models generate it with less pre-trained bias, producing cleaner output without SQL dialect interference.
Choose PRQL if you are a data analyst or engineer who wants a more ergonomic way to write SQL-style pipelines without the boilerplate of CTEs and window functions. Zero setup, broad ecosystem, immediate results. If your team writes queries by hand and wants cleaner syntax, PRQL may be enough.
Choose Malloy if you are an analytics engineer building a single source of truth for a whole team, tired of writing 500-line SQL files and wanting a more powerful, composable language to build a modern metrics layer, with the flexibility to build the model as you go.
Choose PyDough if you are building an AI-powered analytics tool and need a safe, compact intermediate language that an LLM can write reliably, one where correctness is enforced by the language itself, not by post-generation validation.
The best way to understand the differences is to see the same query written in each language. We used TPC-H Query 3 (Shipping Priority) — a standard benchmark query that joins three tables, applies filters at different stages, and aggregates revenue.
The question: For customers in the BUILDING market segment, find the top 10 unshipped orders placed before March 15, 1995, ranked by revenue from line items shipped after that date.
This query is useful because it:
result = (
orders.CALCULATE(order_date, ship_priority)
.WHERE(
(customer.market_segment == "BUILDING")
& (order_date < date(1995, 3, 15))
)
.lines.WHERE(ship_date > date(1995, 3, 15))
.PARTITION(name="groups", by=(order_key, order_date, ship_priority))
.CALCULATE(
L_ORDERKEY=order_key,
O_ORDERDATE=order_date,
O_SHIPPRIORITY=ship_priority,
REVENUE=SUM(lines.extended_price * (1 - lines.discount)),
)
.TOP_K(10, by=(REVENUE.DESC(), O_ORDERDATE.ASC(), L_ORDERKEY.ASC()))
)No join declarations. customer.market_segment traverses the customer relationship automatically. .lines traverses the lineitem relationship. Both are defined in the knowledge graph and invisible at the query level.
SELECT
lineitem.l_orderkey AS L_ORDERKEY,
COALESCE(SUM(lineitem.l_extendedprice * (
1 - lineitem.l_discount
)), 0) AS REVENUE,
orders.o_orderdate AS O_ORDERDATE,
orders.o_shippriority AS O_SHIPPRIORITY
FROM tpch.orders AS orders
JOIN tpch.customer AS customer
ON customer.c_custkey = orders.o_custkey AND customer.c_mktsegment = 'BUILDING'
JOIN tpch.lineitem AS lineitem
ON lineitem.l_orderkey = orders.o_orderkey AND lineitem.l_shipdate > '1995-03-15'
WHERE
orders.o_orderdate < '1995-03-15'
GROUP BY
1,
3,
4
ORDER BY
2 DESC,
3,
1
LIMIT 10
# Semantic model — defined once, reused across queries
source: customer is duckdb.table('data_sf1/customer.parquet') extend {
primary_key: c_custkey
}
source: lineitem is duckdb.table('data_sf1/lineitem.parquet') extend {
primary_key: l_orderkey
dimension: discounted_price is l_extendedprice * (1 - l_discount)
}
source: orders is duckdb.table('data_sf1/orders.parquet') extend {
primary_key: o_orderkey
join_one: customer is customer with o_custkey
join_many: lineitem on lineitem.l_orderkey = o_orderkey
}
# Query
run: orders -> {
where:
customer.c_mktsegment = 'BUILDING',
o_orderdate < @1995-03-15,
lineitem.l_shipdate > @1995-03-15
group_by:
l_orderkey is lineitem.l_orderkey,
o_orderdate,
o_shippriority
aggregate: revenue is lineitem.discounted_price.sum()
order_by: revenue desc
limit: 10
}Join relationships are declared in source definitions, once, in a shared .malloy file that all queries can import. The query itself is clean and readable. The key difference from PyDough is format: Malloy's source definitions are written in Malloy syntax by a human, while PyDough's knowledge graph is JSON, designed to be generated programmatically from your schema and consumed directly by LLMs as structured context.
SELECT
lineitem_0."l_orderkey" as "l_orderkey",
base."o_orderdate" as "o_orderdate",
base."o_shippriority" as "o_shippriority",
COALESCE(SUM((lineitem_0."l_extendedprice"*((1-lineitem_0."l_discount")))),0) as "revenue"
FROM 'data_sf1/orders.parquet' as base
LEFT JOIN 'data_sf1/customer.parquet' AS customer_0
ON customer_0."c_custkey"=base."o_custkey"
LEFT JOIN 'data_sf1/lineitem.parquet' AS lineitem_0
ON lineitem_0."l_orderkey"=base."o_orderkey"
WHERE (customer_0."c_mktsegment"='BUILDING')
AND (base."o_orderdate"<DATE '1995-03-15')
AND (lineitem_0."l_shipdate">=DATE '1995-03-16')
GROUP BY 1,2,3
ORDER BY 4 desc NULLS LAST
LIMIT 10
from o=orders
join c=customer (o.o_custkey == c.c_custkey)
join l=lineitem (o.o_orderkey == l.l_orderkey)
filter c.c_mktsegment == "BUILDING"
filter o.o_orderdate < @1995-03-15
filter l.l_shipdate > @1995-03-15
group {o.o_orderkey, o.o_orderdate, o.o_shippriority} (
aggregate {
revenue = sum (l.l_extendedprice * (1 - l.l_discount))
}
)
sort {-revenue}
take 10Clean pipeline structure. But all three joins are written explicitly, with their join keys. Every query that touches these tables repeats this.
WITH table_2 AS (
SELECT
*
FROM
orders
),
table_1 AS (
SELECT
*
FROM
customer
),
table_0 AS (
SELECT
*
FROM
lineitem
)
SELECT
o.o_orderkey,
o.o_orderdate,
o.o_shippriority,
COALESCE(SUM(l.l_extendedprice * (1 - l.l_discount)), 0) AS revenue
FROM
table_2 AS o
INNER JOIN table_1 AS c ON o.o_custkey = c.c_custkey
INNER JOIN table_0 AS l ON o.o_orderkey = l.l_orderkey
WHERE
c.c_mktsegment = 'BUILDING'
AND o.o_orderdate < DATE '1995-03-15'
AND l.l_shipdate > DATE '1995-03-15'
GROUP BY
o.o_orderkey,
o.o_orderdate,
o.o_shippriority
ORDER BY
revenue DESC
LIMIT
10
All three produce the same results against the same dataset, confirming the queries are logically equivalent. But the SQL each compiler generates reveals different underlying philosophies:
None of these is universally better. They reflect different design priorities. But the PyDough approach produces more efficient SQL automatically without the query author thinking about it at all.
All three (PyDough, Malloy, and PRQL) return same results against the same TPC-H dataset at scale factor 1, confirming the queries are logically equivalent despite their different syntax and compiler philosophies.
PyDough wasn't built to replace Malloy or PRQL. They solve real problems well, and if your team writes queries by hand and wants cleaner syntax, either one may be exactly what you need.
The difference is the design center. Malloy was designed for human analysts. PRQL was designed for data engineers. PyDough was designed for a world where the query author is an AI model, where wrong joins have production consequences, the language itself needs to be the guardrail, and the compiler should produce not just correct but efficient SQL automatically.
PyDough, Malloy, and PRQL are not competing for the same user. PRQL makes SQL cleaner for engineers who already know what they want to query. Malloy gives analysts a reusable semantic model for exploratory work. PyDough is the only one designed for a system where an AI is writing the queries.
If you're building AI-driven analytics and this resonates, we'd love for you to try PyDough. The code, documentation, and notebook examples are all at PyDough repo. If you want to try the full LLM+PyDough pipeline in action, check out PyDough CE, our community edition: point an LLM at your own database, ask questions in plain English, and get safe executable analytics answers back. Come join our Slack community and tell us what you're building, what's working, and where we can do better. Your feedback shapes where we go next.
