query generation for agents and apps
any agent
search_fields 3 fields profit plan_query sql · 70 µs profit, region = EMEA, date >= 30d generated sql
SELECT sum(T0.profit) AS "Profit"
FROM sales T0 LEFT JOIN region T1 ON T1.id = T0.region_id
WHERE T1.name = 'EMEA' AND T0.date >= current_date - 30Planned against your governed model, never your warehouse. Same spec, same statement, every time.
curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
-H "Authorization: Bearer zqk_…" \
-H "Content-Type: application/json" \
-d '{
"spec": {
"projections": [
{"field": "Web Net Paid"},
{"field": "Web Sold Date"},
{"field": "Net Paid"}
]
}
}'zsql sql --expr "web net paid, web sold date, net paid"const res = await fetch('https://app.0sql.io/projects/tpcds/branches/main/sql', {
method: 'POST',
headers: { Authorization: `Bearer ${key}`, 'Content-Type': 'application/json' },
body: JSON.stringify({
spec: { projections: [{ field: 'Web Net Paid' }, { field: 'Web Sold Date' }, { field: 'Net Paid' }] },
}),
});
const { sql } = await res.json();sql, adapter postgres, planned in 70 µs
WITH ag29130fae5cf6548d6ddb1bd37298b407 AS (
SELECT
sum(T0."ws_net_paid") AS "msr501e4a8",
T0."ws_sold_date_sk" AS "dimed56b67"
FROM
web_sales T0
GROUP BY
T0."ws_sold_date_sk"
), ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (
SELECT
T0."ss_sold_date_sk" AS "dimed56b67",
sum(T0."ss_net_paid") AS "msr621f67c"
FROM
store_sales T0
GROUP BY
T0."ss_sold_date_sk"
)
SELECT
A0.msr501e4a8 AS "Web Net Paid",
COALESCE(A1.dimed56b67, A0.dimed56b67) AS "Web Sold Date",
A1.msr621f67c AS "Net Paid"
FROM
ag29130fae5cf6548d6ddb1bd37298b407 A0
FULL OUTER JOIN ag7098b0d0901f2eb16d14f9356f0bb2a0 A1
ON A1.dimed56b67 = A0.dimed56b67Two measures from two fact tables, one conformed date. Each fact is aggregated at its own grain and stitched with a full outer join. Nothing double counts, and nobody modeled that query.
trusted by data teams at
head to head
One difference of many. A median order value needs two levels of aggregation: sum each order, then take the median. Only one of the three answers it correctly, with your security policy already in the statement, without touching your data.
See every difference in the comparison tableLLM-to-SQL
The model writes the SQL itself.
what it wrote
SELECT r.name AS region,
median(s.net_paid) AS median_order
FROM sales s JOIN region r ON r.id = s.region_id
GROUP BY r.namemedian(s.net_paid) is the median of line items, not of orders. Fluent, plausible, wrong. And no row-level security.
Other semantic layers
Governed, but one aggregation per measure.
what it returned
measure median_order_value
aggregation median(net_paid)
not expressible
A measure aggregates once. There is no grain between the row and the result, so a median of per-order sums cannot be defined.
Safe, because it refuses. Useless, because it refuses the right question.
0sql
Plans the statement from your model. Never runs it.
what it returned
spec region, median order size
SELECT A0.region,
percentile_cont(0.5)
WITHIN GROUP (ORDER BY A0.order_total)
FROM (
SELECT T1.name AS region, T0.ticket,
sum(T0.net_paid) AS order_total
FROM sales T0
JOIN region T1 ON T1.id = T0.region_id
WHERE T1.name IN ('EMEA', 'APAC')
-- the caller's row-level security
GROUP BY T1.name, T0.ticket
) A0
GROUP BY A0.regionThe measure is modeled once as an inclusion: sum per ticket, then the median. The request is two field names. The security context is already in the statement.
A plausible wrong number ships. A refusal ships nothing. 0sql ships the statement, and you can read it before it runs. Reference: inclusion measures.
example · open source
Your dashboard is a chat. A question goes in, the agent writes a 0sql spec, 0sql returns the SQL, your app runs it on your warehouse and draws the chart. Say “pin that” and it is a tile.
one turn, under the hood
{
"projections": [
{ "field": "Call Center" },
{ "field": "Average Contact Duration Secs",
"alias": "Average Handle Time Secs", "order_by": "desc" }
]
}SELECT
T1."call_center_desc" AS "Call Center",
sum(T0."contact_duration_secs") * 1.0 / nullif(count(*), 0) AS "Average Handle Time Secs"
FROM
cs_contact_f T0
LEFT JOIN cs_call_center_d T1
ON T0.call_center_id = T1.call_center_id
GROUP BY
T1."call_center_desc"
ORDER BY
sum(T0."contact_duration_secs") * 1.0 / nullif(count(*), 0) descThe join came from the model and the average is the measure's own formula, because an average of an average is a bug and that gets settled once, in the YAML, not in every question.
Four steps, three of them yours. The semantic model is YAML in git; a branch deploys as a branch.
model
Name a measure once. Declare joins with cardinality. The planner derives every reachable combination; you never write a query.
name: Web Sales
physical_name: web_sales
datasource: warehouse
cost: 100
fields:
- type: measure
name: Web Net Paid
data_type: decimal
expression:
sql: sum(ws_net_paid)datasource: warehouse
web_sales_item:
left: Web Sales
right: Item
sql: left.ws_item_sk = right.i_item_sk
cardinality: many_to_onedeploy
Secrets are stripped before upload. The service validates the model, forms the universes, runs your tests and answers with counts and warnings.
$ zsql deploy
deploy tpcds (6120 bytes) as tpcds/main (the production branch) to https://app.0sql.io
deployed tpcds/main: 6 tables, 41 fields, 7 joins, 11 paths, 1 policies
PASSED category by net paid
tests: 1 passed, 0 failedrequest
Decorators for month grains and period-over-period, filters that land in WHERE or HAVING by themselves.
{
"spec": {
"projections": [
{"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}], "order_by": "asc"},
{"field": "Web Net Paid", "decorators": [{"type": "temporalize", "transform": "month_over_month"}]}
],
"filters": [{"field": "Web Net Paid", "predicate": "greater_than", "value": "100"}]
}
} the same, as shorthand: zsql sql --expr "month(date) asc, mom(web net paid), web net paid > 100"
run
Top five categories by revenue: a ranking CTE joined back, no window-function guesswork. Hand it to your driver.
WITH ag5c9bb8d550197b3a219ae50f0b427905 AS (
SELECT
T1."i_category" AS "dim30d09b7",
sum(T0."ws_net_paid") AS "msr0c0d023"
FROM
web_sales T0
JOIN item T1
ON T0.ws_item_sk = T1.i_item_sk
GROUP BY
T1."i_category"
ORDER BY
sum(T0."ws_net_paid") desc
LIMIT 5
)
SELECT
sum(T0."ws_net_paid") AS "Web Net Paid",
T1."i_category" AS "Category"
FROM
web_sales T0
JOIN item T1
ON T0.ws_item_sk = T1.i_item_sk
INNER JOIN ag5c9bb8d550197b3a219ae50f0b427905 AS A2
ON T1."i_category" = A2.dim30d09b7
GROUP BY
T1."i_category"security
0sql holds the shape of your warehouse, never its contents. A spec goes out, a statement comes back, and it runs inside your own perimeter with the caller's policy already in it.
your network
0sql
Specs and contexts are planned in memory, never stored or logged. What is kept.
row-level security, compiled into the statement
policy call_center_id
fired on the caller's groups
CC-TMNT → call_center_id:TMNT · CC-AVCL → call_center_id:AVCL generated sql
SELECT
T0."cc_country" AS "Country"
FROM
call_center T0
WHERE
LOWER(T0."cc_call_center_id") IN ('tmnt', 'avcl')The WHERE came from the context, not from the prompt. Rows the caller may not see are never read.
The context comes from your auth system with every request. A branch with policies refuses to plan without one.
determinism
On every deploy and every replica. Join routes and merge order are settled by name, never by row id, so planning is not a coin flip.
Pin a statement in a test. A model edit that would change the SQL your application depends on fails the deploy instead of surprising you in production.
The planner is held to byte-for-byte parity with the engine inside Strata, which has run at Netflix scale for years.
name: revenue by category
projections:
- Category
- Net Paid
assert_sql: |
SELECT
T1."i_category" AS "Category",
sum(T0."ss_net_paid") AS "Net Paid"
FROM
store_sales T0
JOIN item T1
ON T0.ss_item_sk = T1.i_item_sk
GROUP BY
T1."i_category"$ zsql deploy
deploy tpcds (6120 bytes) as tpcds/main (the production branch) to https://app.0sql.io
deployed tpcds/main: 6 tables, 41 fields, 7 joins, 11 paths, 1 policies
PASSED category by net paid
tests: 1 passed, 0 failedreliability
A measure asked for by a dimension its fact cannot reach is an error with a class and a message, never a fan-out. A hallucinated field is a 422, not a confident wrong answer.
Misspelled fields resolve by similarity when one clearly leads, and the response says so under corrections.
{
"error": {
"class": "Unresolvable",
"message": "Could not find Universe for measure: Web Net Paid, id: 376"
}
}Every error class is documented, so an agent can read the message and fix the spec on its next turn. Error classes.
compared · semantic layer as a service
0sql is a semantic layer as a service: the model, the planner and the API, without the server, the cache or the warehouse connection. Side by side with Cube and MetricFlow, from their own documentation. Every 0sql cell links to the docs page that shows the SQL.
| Querying and modeling | 0sql | Cube | MetricFlow |
|---|---|---|---|
| Automatic data blending across many domains | Measures from many facts in one statement. No fan-out. | Primary keys and views; many-to-many needs an associative cube. | Full outer join across facts. Two hops at most. |
| Standard aggregates | Any SQL aggregate. | A fixed list of aggregation types. | Simple metrics over one column expression. |
| Compound measures across facts | Formulas over measures from different facts, auto-leveled. | Number measures inside one cube or view. | Derived metrics. |
| Snapshot measures | Beginning or ending balance per period. | — | — |
| Exclusion level of detail | Percent of total and fixed denominators, in the model. | Multi-stage measures. | — |
| Inclusion level of detail | Aggregate per entity first, then re-aggregate. | Multi-stage measures. | — |
| Ratios | A compound measure, or a formula in the request. | Number measures. | Ratio metrics. |
| Running and moving windows | A decorator in the request. | Rolling-window measures, modeled. | Cumulative metrics, modeled. |
| Period over period | A decorator in the request. | Time-shift measures, modeled. | offset_window on a derived metric, modeled. |
| Top-n and ranking | A filter in the request. | Rank, as a multi-stage measure. | — |
| Conversion between events | Cohort conversion through segments. No per-event window. | — | Conversion metrics. |
| Formulas in the request | [Measure]@m over any measures. No deploy. | Modeled first. | Modeled first. |
| Cohort beside baseline | A segment on one measure or the query, with exclude mode. | Segments filter the whole query. | Per-metric filter in a derived metric. |
| Partition-aware routing | Routes to the table whose partition holds the data. | Partitions its own pre-aggregations only. | — |
| Routing across compute engines | A hot engine for the partitions it holds, colder ones for the rest. | — | — |
| Aggregate-aware routing | The cheapest table you already own: tiers, costs, aggregates. | Pre-aggregations copied into Cube's own separate store. | — |
| Row-level security and masks | Policies become WHERE or CASE masks. No context, no plan. | Access policies from a JWT context. | Not in the layer. Warehouse grants. |
| Pinned SQL, tested on deploy | assert_sql fails the deploy. | — | — |
| Built for agents | /explore, /fields, typed errors, llms.txt. | Meta API. Chat APIs in Cube Cloud. | dbt MCP server. |
| Architecture | 0sql | Cube | MetricFlow |
|---|---|---|---|
| What comes back | SQL. Nothing executes. | Rows. SQL from /v1/sql, for debugging. | Rows. --compile shows the SQL. |
| Needs your warehouse credentials | No. It never connects. | Yes. | Yes, through dbt. |
| Holds results or a cache | No. Planned in memory, never stored. | Relational cache and pre-aggregations. | Exports materialize saved queries. |
| Warehouses | 13 dialects. | 35+ data sources. | 6 platforms. |
| How you get it | Hosted API. Free today. | Apache 2.0 core, or Cube Cloud. | Apache 2.0 engine. APIs need dbt Cloud. |
Read from each project's public documentation on 6 October 2026. A dash means no documented equivalent was found. Cube and MetricFlow are trademarks of their owners. Spotted a mistake? Tell us and we will fix it.
who it's for
A spec is JSON. Whatever builds it gets the same joins, the same grain and the same security back, and none of it has to know SQL.
The model picks fields, never SQL.
A combination the model cannot have comes back as an error with a reason, not a confident wrong number.
See the chat dashboardA query builder in your product, with no SQL generator to write.
Your UI sends specs, your backend runs the statement, and the context decides which rows each tenant sees.
How the context worksOne definition of revenue, on every surface.
Notebooks, internal tools and new front ends ask by name and get the same statement, in every dialect.
Request cookbookModel in git. Deploy per branch. Test the SQL in CI.
Hand every team a read-only query key and keep the warehouse credentials to yourself.
Deploy from CIThe adapter on the datasource decides quoting, date functions, join support and CTE strategy. Hot and cold tiers, partitions and aggregate tables route each request to the cheapest table that can answer it.
Need the whole stack?
0sql returns SQL and stops there, because you are building the product around it. If what you want is the product, Strata is the same planner with everything else already attached: query execution across federated engines, aggregate awareness and hot tiers, dashboards that are good by default, self-service for non-technical users, and delivery through exports, subscriptions and Google Sheets.
It is also where the agents already live. Strata ships AI analytics over the same governed model, so a non-technical user or an agent can ask broad, cross-domain questions and get answers that are correct by construction, without you writing the retrieval layer, the chart layer or the guardrails.
The model format is the same one, so this is not a decision you have to get right today. A semantic model you write for 0sql is a semantic model Strata can run.
Get a key, install zsql, model three tables, deploy, send your first spec. The quickstart walks through every step with the SQL that comes back.
Early access, everything included. What that means.
