API Reference

0sql turns a query spec into one SQL statement for your warehouse. You deploy a project (tables, dimensions, measures, joins and security policies in YAML) to a project and branch, then POST a spec, or a one-line expr in the 0sql expression language, together with a security context, and get back the statement, the datasource it is for, and the adapter to run it with. 0sql never executes SQL: your application runs the statement against its own warehouse. Discovery routes list the fields and tables of a deployed branch so a client can build specs without reading the YAML.

The server has no accounts, users or API keys. Every read route (planning and discovery) answers whoever can reach the server, with no Authorization header, the way a database on a private network does. Who may ask, for which project and how often is decided by the application or proxy you put in front of it; do not expose the port to callers you do not trust. The one credential is the optional deploy token: when the server is started with ZSQL_DEPLOY_TOKEN, the four write routes (deploy, validate, test, remove a branch) need Authorization: Bearer <token>, and without it they are as open as the reads. The base URL is wherever your server listens; http://127.0.0.1:3699 is the default.

JSON bodies need Content-Type: application/json and are limited to 1 MB; deploy and validate take a gzipped tar of the project directory, up to 64 MB. Every error is {"error": {"class": "<class>", "message": "<text>"}} with a matching HTTP status; the Error schema lists every class. There are no rate limits: the server caps how much work runs at once and answers 503 Busy with Retry-After when it is full. Planning writes nothing: the spec, the context and the SQL are not stored.

Base URL http://127.0.0.1:3699. Version 1. This page is generated from the OpenAPI specification, which you can load into any client. The Query API guide covers the concepts behind these endpoints; the query spec page explains projections, filters, calculations, and segments in prose.

Planning

Turn a spec or an expr into SQL. All three routes take the same body: spec or expr (spec wins when both are given), plus an optional context. A branch with security policies refuses to plan without a context (400 ContextRequired). Spec and planner errors come back as 422 with the class that raised them. No credential: the context is whatever the caller sends, so the caller must be your own service.

Plan SQL

post /projects/{uid}/branches/{branch}/sql

Plan a spec, or an expr, against the branch and return one SQL statement with the datasource it runs on. Give spec or expr; with neither the answer is 400 Invalid. Field references that resolved by near match are listed under corrections. When the request used expr, the response also carries the spec it produced and items, one line per item describing how it was read.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Request body

A SqlRequest , required.

PropertyTypeDescription
specSpec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

exprstring

An expression line, parsed on the server into a spec. Comma-separated items: projections, filters, calculations, segments and settings.

contextSecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

emailstring

The user's email, read by policies that resolve permissions from the email.

system_adminboolean

Bypasses policies that let system admins through.

Default false

project_adminboolean

Bypasses policies that let project admins through.

Default false

tagsarray of string

The user's own tags as key:value, read by user-scoped tag policies.

groupsarray of Group

The groups the user belongs to, read by group-scoped policies.

{
  "spec": {
    "name": "Query One",
    "projections": [
      {
        "field": "ws_net_paid",
        "alias": "Web Net Paid"
      },
      {
        "field": "ws_sold_date",
        "alias": "Web Sold Date"
      },
      {
        "field": "net_paid",
        "alias": "Net Paid"
      }
    ],
    "filters": [
      {
        "field": "category",
        "predicate": "in_list",
        "value": "men ,children, \"books,com\""
      }
    ]
  },
  "context": {
    "email": "tank@matrix.com",
    "system_admin": false,
    "project_admin": false,
    "tags": [],
    "groups": [
      {
        "name": "CC-TMNT",
        "tags": [
          "call_center_id:TMNT"
        ]
      }
    ]
  }
}

Responses

200 The statement and its datasource

Returns a SqlResponse.

{
  "sql": "WITH ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (\nSELECT\n\tT0.\"ss_sold_date_sk\" AS \"dimed56b67\",\n\tsum(T0.\"ss_net_paid\") AS \"msr621f67c\"\nFROM\n\tstore_sales T0\n\tJOIN item T1\n\t\tON T0.ss_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") IN ('men', 'children', '\"books,com\"')\nGROUP BY\n\tT0.\"ss_sold_date_sk\"\n), ag29130fae5cf6548d6ddb1bd37298b407 AS (\nSELECT\n\tsum(T0.\"ws_net_paid\") AS \"msr501e4a8\",\n\tT0.\"ws_sold_date_sk\" AS \"dimed56b67\"\nFROM\n\tweb_sales T0\n\tJOIN item T1\n\t\tON T0.ws_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") IN ('men', 'children', '\"books,com\"')\nGROUP BY\n\tT0.\"ws_sold_date_sk\"\n)\nSELECT\n\tA0.msr501e4a8 AS \"Web Net Paid\",\n\tCOALESCE(A1.dimed56b67, A0.dimed56b67) AS \"Web Sold Date\",\n\tA1.msr621f67c AS \"Net Paid\"\nFROM\n\tag29130fae5cf6548d6ddb1bd37298b407 A0\n\tFULL OUTER JOIN ag7098b0d0901f2eb16d14f9356f0bb2a0 A1\n\t\tON A1.dimed56b67 = A0.dimed56b67",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres"
}

400 `Invalid` (neither `spec` nor `expr`), `Shorthand` (the `expr` did not parse) or `ContextRequired` (the branch has security policies and the request has no context)

Returns a Error.

{
  "error": {
    "class": "ContextRequired",
    "message": "this branch has security policies; a context is required to plan"
  }
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

422 The spec could not be planned. `InvalidSpec` for a malformed spec, `InvalidValue` for a validation failure (message prefixed `Validation failed:`), `FieldNotFound` or `FieldAmbiguous` for a field reference, `Unresolvable`, `SecurityPolicy`, `SegmentDatasource` or `Unimplemented` from the planner

Returns a Error.

{
  "error": {
    "class": "FieldNotFound",
    "message": "No field named 'net paidd' in this model. Did you mean: Net Paid, Web Net Paid?"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Explain a plan

post /projects/{uid}/branches/{branch}/explain

Everything /sql returns, plus how long the server took (timings), the share of each planning phase (phases), and the node graph the planner shaped (nodes). Nodes are in post order; the last one is the root. Phase names: resolve, segment_fork, complex_measure, exclusion, inclusion, snapshot, contribution, temporal, top_n, segment, security (with a context), reference, alias, sql, final_query.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Request body

A SqlRequest , required.

PropertyTypeDescription
specSpec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

exprstring

An expression line, parsed on the server into a spec. Comma-separated items: projections, filters, calculations, segments and settings.

contextSecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

emailstring

The user's email, read by policies that resolve permissions from the email.

system_adminboolean

Bypasses policies that let system admins through.

Default false

project_adminboolean

Bypasses policies that let project admins through.

Default false

tagsarray of string

The user's own tags as key:value, read by user-scoped tag policies.

groupsarray of Group

The groups the user belongs to, read by group-scoped policies.

{
  "spec": {
    "name": "Query One",
    "projections": [
      {
        "field": "ws_net_paid",
        "alias": "Web Net Paid"
      },
      {
        "field": "ws_sold_date",
        "alias": "Web Sold Date"
      },
      {
        "field": "net_paid",
        "alias": "Net Paid"
      }
    ],
    "filters": [
      {
        "field": "category",
        "predicate": "in_list",
        "value": "men ,children, \"books,com\""
      }
    ]
  },
  "context": {
    "email": "tank@matrix.com",
    "system_admin": false,
    "project_admin": false,
    "tags": [],
    "groups": [
      {
        "name": "CC-TMNT",
        "tags": [
          "call_center_id:TMNT"
        ]
      }
    ]
  }
}

Responses

200 The statement, timings, phases and nodes

Returns a ExplainResponse.

{
  "sql": "SELECT\n\tsum(T0.\"ws_net_paid\") AS \"Web Net Paid\"\nFROM\n\tweb_sales T0\n\tJOIN item T1\n\t\tON T0.ws_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") LIKE 'super%'",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres",
  "timings": {
    "parser_us": 41,
    "plan_us": 187,
    "total_us": 228
  },
  "nodes": [
    {
      "id": 0,
      "alias": "ag5c9bb8d550197b3a219ae50f0b427905",
      "kind": "aggregation",
      "root": true,
      "table": "web_sales",
      "datasource": "warehouse",
      "paths": [
        "web_sales > item"
      ],
      "purpose": "top_n",
      "transform": "month_over_month",
      "segment": false,
      "projections": [
        "Category",
        "Web Net Paid"
      ],
      "filters": [
        "category in_list men, children"
      ],
      "inputs": [],
      "strategies": [
        {
          "node": 1,
          "kind": "segment"
        }
      ],
      "join_type": "full",
      "group_by": true,
      "security_filters": [],
      "security_masks": [],
      "identity": "web_sales|category|ws_net_paid"
    }
  ]
}

400 `Invalid` (neither `spec` nor `expr`), `Shorthand` (the `expr` did not parse) or `ContextRequired` (the branch has security policies and the request has no context)

Returns a Error.

{
  "error": {
    "class": "ContextRequired",
    "message": "this branch has security policies; a context is required to plan"
  }
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

422 The spec could not be planned. `InvalidSpec` for a malformed spec, `InvalidValue` for a validation failure (message prefixed `Validation failed:`), `FieldNotFound` or `FieldAmbiguous` for a field reference, `Unresolvable`, `SecurityPolicy`, `SegmentDatasource` or `Unimplemented` from the planner

Returns a Error.

{
  "error": {
    "class": "FieldNotFound",
    "message": "No field named 'net paidd' in this model. Did you mean: Net Paid, Web Net Paid?"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Explore what a spec can add

post /projects/{uid}/branches/{branch}/explore

Given a spec or expr, list the dimensions and measures that can join the query from where it stands. With q, keep the fields that contain the term (score 1) or sit near it by trigram similarity (score 0.5 or more), best first. Hidden fields are left out. The security context is accepted and ignored.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Request body

A ExploreRequest , required.

PropertyTypeDescription
specSpec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

exprstring

An expression line in place of spec.

contextSecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

emailstring

The user's email, read by policies that resolve permissions from the email.

system_adminboolean

Bypasses policies that let system admins through.

Default false

project_adminboolean

Bypasses policies that let project admins through.

Default false

tagsarray of string

The user's own tags as key:value, read by user-scoped tag policies.

groupsarray of Group

The groups the user belongs to, read by group-scoped policies.

qstring

Keep fields containing this term or near it by trigram similarity, best first.

{
  "spec": {
    "projections": [
      {
        "field": "category"
      },
      {
        "field": "ws_net_paid"
      }
    ]
  },
  "q": "paid"
}

Responses

200 Dimensions and measures the query can add

Returns a ExploreResponse.

{
  "dimensions": [
    {
      "uid": "net_paid",
      "name": "Net Paid",
      "data_type": "decimal",
      "description": "Net amount paid on store sales",
      "tables": [
        "store_sales"
      ],
      "score": 1
    }
  ],
  "measures": [
    {
      "uid": "net_paid",
      "name": "Net Paid",
      "data_type": "decimal",
      "description": "Net amount paid on store sales",
      "tables": [
        "store_sales"
      ],
      "score": 1
    }
  ],
  "total_us": 312
}

400 `Invalid` (neither `spec` nor `expr`), `Shorthand` (the `expr` did not parse) or `ContextRequired` (the branch has security policies and the request has no context)

Returns a Error.

{
  "error": {
    "class": "ContextRequired",
    "message": "this branch has security policies; a context is required to plan"
  }
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

422 The spec could not be planned. `InvalidSpec` for a malformed spec, `InvalidValue` for a validation failure (message prefixed `Validation failed:`), `FieldNotFound` or `FieldAmbiguous` for a field reference, `Unresolvable`, `SecurityPolicy`, `SegmentDatasource` or `Unimplemented` from the planner

Returns a Error.

{
  "error": {
    "class": "FieldNotFound",
    "message": "No field named 'net paidd' in this model. Did you mean: Net Paid, Web Net Paid?"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Discovery

What a deployed branch holds: its summary, fields, tables and sampled values, and every deployment on the server. No credential.

Branch summary

get /projects/{uid}/branches/{branch}

What is deployed on the branch: when, and how many datasources, tables, fields, joins, paths, policies and tests it holds, with any deploy warnings. zsql status prints this.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 The deployment summary

Returns a DeploymentSummary.

{
  "project": "tpcds",
  "branch": "main",
  "deployed_at": "2026-10-01 15:04",
  "datasources": 1,
  "tables": 6,
  "fields": 42,
  "joins": 5,
  "paths": 9,
  "policies": 1,
  "tests": 3,
  "values": 4,
  "warnings": []
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

List or search fields

get /projects/{uid}/branches/{branch}/fields

Every dimension and measure on the branch. With q, fields whose name contains the term come first with score 1, then fuzzy matches by trigram similarity with their score. Hidden fields are left out unless hidden=true.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

q query string

Search term, matched against names, uids and synonyms.

hidden query boolean

Include hidden fields.

Default false

Responses

200 The fields, best match first when searching

Returns a FieldsResponse.

{
  "fields": [
    {
      "uid": "ws_net_paid",
      "name": "Web Net Paid",
      "kind": "measure",
      "data_type": "decimal",
      "description": "Net amount paid on web orders",
      "synonyms": [
        "web revenue"
      ],
      "tags": [],
      "hidden": false,
      "tables": [
        "web_sales"
      ],
      "score": 1
    }
  ]
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

List tables

get /projects/{uid}/branches/{branch}/tables

Every table on the branch with its physical name, cost, datasource and field uids.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 The tables

Returns a TablesResponse.

{
  "tables": [
    {
      "uid": "web_sales",
      "name": "Web Sales",
      "physical_name": "web_sales",
      "cost": 100,
      "datasource": "warehouse",
      "fields": [
        "ws_net_paid",
        "ws_sold_date",
        "category"
      ]
    }
  ]
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

A dimension's sampled values

get /projects/{uid}/branches/{branch}/values/{dim}

The values a dimension holds, as zsql sample collected them from the warehouse and zsql deploy shipped them.

Sampling is off on every dimension until a model sets enable_sampling, so a branch may have none: the branch summary's values count says how many dimensions have them. A dimension that exists but was not sampled answers 404 no sampled values for <uid>, which is a different thing from a dimension that does not exist.

These are the values a caller writes a filter from. distinct is the column's total and values is what came back, so a list as long as sample_size means the sample is truncated rather than whole.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

dim required path string

The dimension, by uid or name. Resolved with the same near-match rules as the other field lookups.

Responses

200 The sampled values

Returns a DimensionValues.

{
  "uid": "contact-subchannel-id",
  "name": "Contact Subchannel ID",
  "table": "cs_duckdb",
  "sampled_at": "2026-10-24T11:18:33Z",
  "sample_size": 1000,
  "distinct": 6,
  "values": [
    {
      "v": "SC1",
      "n": 1530
    }
  ]
}

400 `Ambiguous`: the term matches several dimensions, which are listed

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

404 `NotFound`: no dimension matches the term, or the dimension has no sampled values

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

List projects and deployments

get /projects

Every deployment on the server, one summary per project and branch. There is no per-project access: a service in front that serves several tenants filters this list itself. zsql deployments prints this.

Responses

200 Every deployment

Returns a ProjectsResponse.

{
  "deployments": [
    {
      "project": "tpcds",
      "branch": "main",
      "deployed_at": "2026-10-01 15:04",
      "datasources": 1,
      "tables": 6,
      "fields": 42,
      "joins": 5,
      "paths": 9,
      "policies": 1,
      "tests": 3,
      "values": 4,
      "warnings": []
    }
  ]
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Deployment

Deploy, validate and test a project on a branch, and remove a branch. These four need the deploy token when the server has one (401 Unauthorized without it), and are open to whoever reaches the server when it has none. The first deploy of a uid creates the project.

Deploy a project

post /projects/{uid}/branches/{branch}/deploy

Deploy a project directory to the branch. The body is a gzipped tar (Content-Type: application/gzip) of the directory: project.yml at the root or inside one top-level folder, datasources.yml, models/**/tbl.*.yml, models/**/rel.*.yml, security.yml and tests/*.yml. The server compiles the model, runs the tests, stores the snapshot, and answers with the summary. Deploy returns 200 even when tests fail; check test_results.failed.

An empty body, a bad archive, a missing project.yml or a loader error is 400 DeployError. The first deploy of a uid creates the project, taking name and production_branch from project.yml; a later deploy of the same uid and branch replaces it, whoever sends it. The uid must be a slug: lower-case letters, digits, - and _.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 The deployment summary with test results

Returns a DeployResponse.

{
  "project": "tpcds",
  "branch": "main",
  "deployed_at": "2026-10-01 15:04",
  "datasources": 1,
  "tables": 6,
  "fields": 42,
  "joins": 5,
  "paths": 9,
  "policies": 1,
  "tests": 3,
  "values": 4,
  "warnings": [],
  "test_results": {
    "passed": 3,
    "failed": 0,
    "results": [
      {
        "name": "Category revenue",
        "status": "passed",
        "file": "category_revenue.yml",
        "message": "generated SQL did not match",
        "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "pattern": "SELECT"
      }
    ]
  },
  "validated_only": false
}

400 `DeployError`: the body is not a tar.gz, holds no `project.yml`, or a file did not load. Loader errors read `Error in <file>: <message>`

Returns a Error.

{
  "error": {
    "class": "DeployError",
    "message": "the archive holds no project.yml"
  }
}

401 `Unauthorized`: the server has a deploy token and the request's is missing or not valid. Only the write routes answer this

Returns a Error.

{
  "error": {
    "class": "Unauthorized",
    "message": "this deploy token is not valid"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Validate a project

post /projects/{uid}/branches/{branch}/validate

Same body and checks as deploy, but nothing is stored: the model is compiled and the tests run against it, then the archive is dropped. The response is the deploy summary with validated_only set to true. zsql deploy --dry-run and zsql check -r call this route.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 The summary the deploy would produce

Returns a DeployResponse.

{
  "project": "tpcds",
  "branch": "main",
  "deployed_at": "2026-10-01 15:04",
  "datasources": 1,
  "tables": 6,
  "fields": 42,
  "joins": 5,
  "paths": 9,
  "policies": 1,
  "tests": 3,
  "values": 4,
  "warnings": [],
  "test_results": {
    "passed": 3,
    "failed": 0,
    "results": [
      {
        "name": "Category revenue",
        "status": "passed",
        "file": "category_revenue.yml",
        "message": "generated SQL did not match",
        "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "pattern": "SELECT"
      }
    ]
  },
  "validated_only": false
}

400 `DeployError`: the body is not a tar.gz, holds no `project.yml`, or a file did not load. Loader errors read `Error in <file>: <message>`

Returns a Error.

{
  "error": {
    "class": "DeployError",
    "message": "the archive holds no project.yml"
  }
}

401 `Unauthorized`: the server has a deploy token and the request's is missing or not valid. Only the write routes answer this

Returns a Error.

{
  "error": {
    "class": "Unauthorized",
    "message": "this deploy token is not valid"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Run the deployed tests

post /projects/{uid}/branches/{branch}/test

Run the tests/*.yml deployed on the branch and report each result. No body. Tests plan their projections with no security context and compare the SQL with assert_sql (lower-cased, whitespace-normalized) or assert_regex (case-insensitive, dotall).

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 Pass and fail counts with one result per test

Returns a TestResults.

{
  "passed": 3,
  "failed": 0,
  "results": [
    {
      "name": "Category revenue",
      "status": "passed",
      "file": "category_revenue.yml",
      "message": "generated SQL did not match",
      "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
      "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
      "pattern": "SELECT"
    }
  ]
}

401 `Unauthorized`: the server has a deploy token and the request's is missing or not valid. Only the write routes answer this

Returns a Error.

{
  "error": {
    "class": "Unauthorized",
    "message": "this deploy token is not valid"
  }
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Remove a branch

delete /projects/{uid}/branches/{branch}

Remove the deployment on the branch: its snapshot and tests. The project and its other branches stay. zsql undeploy --yes calls this route.

Parameters

Name In Type Description
uid required path string

The project uid, a slug of lower-case letters, digits, - and _.

branch required path string

The branch name. zsql deploys to the checked-out git branch; main is the usual production branch.

Responses

200 The branch that was removed

PropertyTypeDescription
removedrequiredstring

The removed deployment as uid/branch.

{
  "removed": "tpcds/main"
}

401 `Unauthorized`: the server has a deploy token and the request's is missing or not valid. Only the write routes answer this

Returns a Error.

{
  "error": {
    "class": "Unauthorized",
    "message": "this deploy token is not valid"
  }
}

404 `NotFound`: no deployment for the project and branch

Returns a Error.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

500 `Dialect`: the base dialect could not load

Returns a Error.

{
  "error": {
    "class": "Dialect",
    "message": "the base dialect could not be loaded"
  }
}

503 `Busy`: the server is running as much of this kind of work as it admits, and `Retry-After` says when to send it again. `Timeout`: the request passed its deadline (30 seconds, 90 for deploy, validate and test), with no `Retry-After`

Returns a Error.

{
  "error": {
    "class": "Busy",
    "message": "the server is full of plan work: retry in 1s"
  }
}

Health

Liveness.

Liveness

get /healthz

Answers ok as plain text. No JSON.

Responses

200 The server is up text/plain

Schemas

The objects the endpoints above accept and return.

Error

Every non-2xx answer. class says what went wrong and maps to the HTTP status: 400 Invalid, Shorthand, ContextRequired, TooLarge, DeployError; 401 Unauthorized (write routes, deploy token); 404 NotFound; 422 InvalidSpec, InvalidValue, FieldNotFound, FieldAmbiguous, Unresolvable, SegmentDatasource, SecurityPolicy, Unimplemented; 500 Dialect; 503 Busy, Timeout. The zsql CLI prints errors as <class>: <message>.

PropertyTypeDescription
errorrequiredobject
classrequiredstring

The error class.

One of Invalid, Shorthand, ContextRequired, TooLarge, DeployError, Unauthorized, NotFound, InvalidSpec, InvalidValue, FieldNotFound, FieldAmbiguous, Unresolvable, SegmentDatasource, SecurityPolicy, Unimplemented, Dialect, Busy, Timeout

messagerequiredstring

What went wrong, for a person to read.

{
  "error": {
    "class": "NotFound",
    "message": "no deployment for project tpcds branch main"
  }
}

Spec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

PropertyTypeDescription
namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

{
  "projections": [
    {
      "field": "category"
    },
    {
      "field": "date",
      "decorators": [
        {
          "type": "truncate",
          "grain": "month"
        }
      ]
    },
    {
      "field": "ws_net_paid",
      "alias": "Web Net Paid",
      "order_by": "desc"
    }
  ],
  "filters": [
    {
      "field": "category",
      "predicate": "in_list",
      "value": "men, children"
    },
    {
      "field": "date",
      "predicate": "greater_than_or_equal_to",
      "value": "1y"
    }
  ]
}

Projection

One output column. A field projection names a dimension or measure in field, by uid, by name (case-insensitive) or by synonym, with an optional @d/@m suffix to pick the kind when a name is both. A calculation sets calculation: true with an alias and a sql formula over [Name]@m, [Name]@d and [Alias] references; a formula that calls an aggregate is a measure calculation, otherwise a dimension calculation. A field reference that resolves to nothing is 422 FieldNotFound, with a near match taken silently and reported under corrections when it is clear enough.

PropertyTypeDescription
fieldstring

The field, as "uid", "Name", "Name@d", "Name@m" or {"uid": "..."}. Required on a field projection; ignored on a calculation.

field_typestring

A kind hint when the reference is ambiguous. Anything starting with d is a dimension; any other value is a measure.

One of dimension, measure

aliasstring

The output column name. Required on a calculation; on a field projection it defaults to the name the decorators lend, else the field's name.

axisstring

Where a client lays the column out; carried, not planned. tip holds measures only.

One of row, x, y, series, tip, pivot

Default row

order_bystring

Sort the result by this column.

One of asc, desc

formatobject

Display format, carried and never planned.

hiddenboolean

Compute the column but leave it out of the SELECT list.

Default false

decoratorsarray of Decorator

Date truncation, extraction, windows, contribution, temporal comparison or custom SQL applied to the field.

calculationboolean

Marks the entry as a calculation; then alias and sql are required.

Default false

sqlstring

The calculation formula. SQL text with [Name]@m, [Name]@d and [Alias] references; bare column names are rejected.

data_typestring

A calculation's result type; decimal when absent.

One of string, integer, decimal, date, date_time, boolean, bigint, binary

Default decimal

{
  "field": "ws_net_paid",
  "alias": "Web Net Paid",
  "order_by": "desc"
}

Decorator

A transform on a projected field: {"type": "<kind>", ...attributes}. truncate, extract and customize apply to dimensions, and truncate and extract need a date or date_time field; window, contribute and temporalize apply to measures. Any attribute outside the vocabulary below is rejected (unknown attribute 'x' on the <type> decorator of <field>). Without an explicit alias a decorator lends one, such as Month(Date), Running Sum(Web Net Paid), % Web Net Paid of Total or LM(Web Net Paid).

PropertyTypeDescription
typerequiredstring

The decorator kind.

One of truncate, extract, window, contribute, temporalize, customize

grainstring

Truncate a date to this grain. Required by truncate.

One of raw, millisecond, second, minute, hour, day, week, month, quarter, year

extractstring

The date part to extract. Required by extract.

One of minute, hour, day_of_month, day_of_week, day_of_year, week_of_year, month, quarter, year, day_name, month_name, year_month

modestring

The window shape. Required by window.

One of moving, running

functionstring

The window function. Required by window.

One of avg, sum, min, max, count, row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value

sizeinteger

Rows in a moving window, at least 1.

Default 7

offsetinteger

Rows back or forward for lag and lead, at least 1.

Default 0

bucketsinteger

Buckets for ntile, at least 1.

Default 4

order_byobject

Window ordering, a map of projected dimension to asc or desc, by uid, name or display alias. The key -1 means the first projected date. A key that names no projected dimension is 422 InvalidValue.

partition_refsarray of string

Projected dimensions to partition a window or contribute by, each a uid, a name or a display alias; -1 means automatic. A reference that names no projected dimension is 422 InvalidValue.

ignore_partition_filtersboolean

For window and contribute, compute the window or total before the dimension filters apply.

Default true

transformstring

The period comparison. Required by temporalize.

One of year_over_year, quarter_over_quarter, month_over_month, week_over_week, day_over_day

percent_changeboolean

For temporalize, return the percentage change instead of the prior period's value.

abbreviateboolean

Carried for display.

Default true

custom_sqlstring

For customize, SQL around @expression, which stands for the field. Must contain @expression and no aggregate call.

{
  "type": "truncate",
  "grain": "month"
}

Filter

The shape of filters on a spec or a segment. Either an array of FilterLeaf objects, a flat AND in which every entry names a field (an and/or object inside the array is rejected), or a FilterNode object whose first key is and or or holding an array of leaves and nested nodes. Filters keep their listed order. A filter on a measure is a measure filter and lands in HAVING; a filter on a dimension lands in WHERE.

[
  {
    "field": "category",
    "predicate": "in_list",
    "value": "men, children"
  },
  {
    "field": "ws_net_paid",
    "predicate": "greater_than",
    "value": "100"
  }
]

FilterLeaf

One condition on a field. Values are scalars; lists are one comma-separated string (commas inside quotes do not split). Strings compare lower-cased on both sides. Dates take YYYY-MM-DD and the usual timestamp forms, or a relative value such as 28d, 3m, 1y. For top_n, value is N and top_n_measure the ranking measure, or value is "N:measure"; without a measure, rows are counted. Allowed predicates depend on the field type: strings take the like predicates, lists, keyword and top_n; numbers and dates take the comparisons and between; equals, does_not_equal, is_null, is_not_null and custom apply to any field. custom is accepted but not planned (422 Unimplemented).

PropertyTypeDescription
fieldrequiredstring

The field, by uid, name or synonym, with an optional @d/@m suffix.

predicaterequiredstring

The comparison.

One of equals, does_not_equal, greater_than, greater_than_or_equal_to, less_than, less_than_or_equal_to, between, in_list, exclude_list, contains, does_not_contain, starts_with, does_not_start_with, ends_with, does_not_end_with, is_null, is_not_null, top_n, keyword, custom

valuestring

The value, a number, a date, or a comma-separated list. Required by every predicate except is_null and is_not_null. filter_value is accepted as an alias.

value_endstring

The upper bound of between. filter_value_end is accepted as an alias.

field_typestring

A kind hint when the field reference is ambiguous.

One of dimension, measure

top_n_measurestring

For top_n, the measure to rank by, as a uid or a name.

{
  "field": "date",
  "predicate": "between",
  "value": "2024-01-01",
  "value_end": "2024-12-31"
}

FilterNode

A logic node: exactly one of and or or, holding an array of FilterLeaf objects and nested nodes. Any other key is rejected (a filter node needs a field or an and/or key).

PropertyTypeDescription
andarray of object

Conditions that must all hold. Each item is a FilterLeaf or a FilterNode.

orarray of object

Conditions of which at least one must hold. Each item is a FilterLeaf or a FilterNode.

{
  "or": [
    {
      "field": "category",
      "predicate": "equals",
      "value": "books"
    },
    {
      "and": [
        {
          "field": "category",
          "predicate": "equals",
          "value": "music"
        },
        {
          "field": "date",
          "predicate": "greater_than_or_equal_to",
          "value": "2024-01-01"
        }
      ]
    }
  ]
}

Segment

A population: the members identified by keys that satisfy filters and, through measures, exist in those measures' fact tables. It joins the query on the keys. Without apply_to it constrains the whole query; with apply_to it constrains the named measure projections, matched by alias first, then by measure uid. keys is required and the segment needs at least one of keys, measures, filters or expanding as a definition. An expanding segment carries a dimension of the other members sharing the key into the query (basket and pair analysis); then mode and apply_to are not allowed.

PropertyTypeDescription
namestring

A name for the segment; Segment N when absent.

modestring

Keep members (include, an inner join) or drop them (exclude, an anti-join). Not for expanding segments.

One of include, exclude

Default include

keysrequiredarray of string

Dimensions identifying the members, the join grain. At least one.

measuresarray of string

Measures whose fact tables define membership. Listing several means membership through any of them.

filtersobject

Conditions the members satisfy, in the same array-or-tree shape as the spec's filters. See Filter. A measure filter here becomes a HAVING on the segment.

apply_toarray of string

Measure projections the segment constrains, by alias or measure uid. Empty means the whole query.

expandingarray of Expanding

Dimensions of the other members sharing the key to carry into the query.

joinstring

For expanding segments, how the carried rows join.

One of inner, left

Default inner

pair_dedupestring

For expanding segments, how self-pairs are handled. neq drops a member paired with itself, lt keeps one ordering of each pair, none keeps everything.

One of neq, lt, none

Default neq

{
  "name": "Book Products",
  "mode": "include",
  "keys": [
    "product_name"
  ],
  "filters": [
    {
      "field": "category",
      "predicate": "equals",
      "value": "books"
    }
  ],
  "apply_to": [
    "ws_net_paid"
  ]
}

Expanding

A dimension an expanding segment carries into the query, taken from the other members that share the segment's key.

PropertyTypeDescription
fieldrequiredstring

The dimension to carry, by uid or name.

asstring

The carried column's name. "<Field> (same <Key>)" when absent.

{
  "field": "product_name",
  "as": "Product Name (same Customer ID)"
}

SecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

PropertyTypeDescription
emailstring

The user's email, read by policies that resolve permissions from the email.

system_adminboolean

Bypasses policies that let system admins through.

Default false

project_adminboolean

Bypasses policies that let project admins through.

Default false

tagsarray of string

The user's own tags as key:value, read by user-scoped tag policies.

groupsarray of Group

The groups the user belongs to, read by group-scoped policies.

{
  "email": "tank@matrix.com",
  "system_admin": false,
  "project_admin": false,
  "tags": [],
  "groups": [
    {
      "name": "CC-TMNT",
      "tags": [
        "call_center_id:TMNT"
      ]
    },
    {
      "name": "CC-AVCL",
      "tags": [
        "call_center_id:AVCL",
        "cat:men"
      ]
    }
  ]
}

Group

A group in a security context. A policy resolving from group names reads name; one resolving from group tags reads the values of tags whose key matches its tag_key.

PropertyTypeDescription
namestring

The group name.

tagsarray of string

Tags as key:value.

{
  "name": "CC-TMNT",
  "tags": [
    "call_center_id:TMNT"
  ]
}

SqlRequest

The body of /sql and /explain. Give a spec, or an expr in the 0sql expression language (category, month(date), ws_net_paid desc, category in (Books, Music)); the spec wins when both are present and neither is 400 Invalid. An expr that does not parse is 400 Shorthand. Add a context whenever the branch has security policies.

PropertyTypeDescription
specSpec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

exprstring

An expression line, parsed on the server into a spec. Comma-separated items: projections, filters, calculations, segments and settings.

contextSecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

{
  "spec": {
    "name": "Query One",
    "projections": [
      {
        "field": "ws_net_paid",
        "alias": "Web Net Paid"
      },
      {
        "field": "ws_sold_date",
        "alias": "Web Sold Date"
      },
      {
        "field": "net_paid",
        "alias": "Net Paid"
      }
    ],
    "filters": [
      {
        "field": "category",
        "predicate": "in_list",
        "value": "men ,children, \"books,com\""
      }
    ]
  },
  "context": {
    "email": "tank@matrix.com",
    "system_admin": false,
    "project_admin": false,
    "tags": [],
    "groups": [
      {
        "name": "CC-TMNT",
        "tags": [
          "call_center_id:TMNT"
        ]
      }
    ]
  }
}

SqlResponse

What a query needs to run, the statement and the datasource it is for. corrections lists references that resolved by near match and is left out when empty. spec and items appear only when the request used expr.

PropertyTypeDescription
sqlrequiredstring

The statement, in the datasource's dialect.

datasourcerequiredstring

The datasource name from datasources.yml.

datasource_uidrequiredstring

The datasource key from datasources.yml.

adapterrequiredstring

The warehouse adapter the statement is written for.

correctionsarray of Correction

Field references that resolved by near match, present only when there are any.

specobject

The spec the expression line produced, only for an expr request.

namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

itemsarray of string

One line per item of the expression line saying how it was read, only for an expr request.

{
  "sql": "SELECT\n\tsum(T0.\"ws_net_paid\") AS \"Web Net Paid\"\nFROM\n\tweb_sales T0\n\tJOIN item T1\n\t\tON T0.ws_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") LIKE 'super%'",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres"
}

Correction

A field reference that did not match exactly and was resolved to the nearest field by trigram similarity. The score is the overlap coefficient, rounded to two places; a correction is taken when it scores at least 0.5 and leads the runner-up by 0.2.

PropertyTypeDescription
termrequiredstring

The reference as written.

field_uidrequiredstring

The field it resolved to.

field_namerequiredstring

That field's name.

scorerequirednumber

The similarity, 0 to 1.

{
  "term": "web net payd",
  "field_uid": "ws_net_paid",
  "field_name": "Web Net Paid",
  "score": 0.83
}

ExplainResponse

Everything SqlResponse carries, plus how long the server took, each planning phase's share, and the node graph the planner shaped. Nodes are in post order; the last is the root.

PropertyTypeDescription
sqlrequiredstring

The statement, in the datasource's dialect.

datasourcerequiredstring

The datasource name from datasources.yml.

datasource_uidrequiredstring

The datasource key from datasources.yml.

adapterrequiredstring

The warehouse adapter the statement is written for.

correctionsarray of Correction

Field references that resolved by near match, present only when there are any.

specobject

The spec the expression line produced, only for an expr request.

namestring

A name for the query, carried through to explain output.

descriptionstring

Free text, carried and never planned.

limitinteger

Row limit. When present the statement ends in LIMIT n; when absent nothing is added and no default is supplied. A top_n filter's ranking CTE carries its own LIMIT either way.

projectionsarray of Projection

The fields, with their decorators, and in-place calculations. Order is kept. At least one is required.

calculationsarray of Projection

Calculations kept apart from the projections; they follow them in the output. Each needs alias and sql.

filtersobject

A flat array of FilterLeaf objects, ANDed, or a FilterNode tree. See Filter.

segmentsarray of Segment

Populations that constrain the whole query or named measures.

hintsarray of string

Table uids the resolver prefers to route through: the first key when it ranks the join routes, ahead of coverage and cost. A preference, not a constraint; a uid naming no table is ignored.

db_settingsobject

Per-query dialect overrides, for example final_pass_measure_join_type or force_group_by.

viewobject

Visualization settings, carried and never planned.

classificationobject

Any JSON, carried and never planned.

itemsarray of string

One line per item of the expression line saying how it was read, only for an expr request.

timingsrequiredTimings

Microseconds the server spent parsing and resolving the spec, planning it, and both together.

phasesrequiredarray of Phase

The planning phases in order, with the microseconds each took. security appears only when a context was given.

nodesrequiredarray of ExplainNode

The plan's nodes in post order; the last is the root.

{
  "sql": "SELECT\n\tsum(T0.\"ws_net_paid\") AS \"Web Net Paid\"\nFROM\n\tweb_sales T0\n\tJOIN item T1\n\t\tON T0.ws_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") LIKE 'super%'",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres",
  "timings": {
    "parser_us": 41,
    "plan_us": 187,
    "total_us": 228
  },
  "nodes": [
    {
      "id": 0,
      "alias": "ag5c9bb8d550197b3a219ae50f0b427905",
      "kind": "aggregation",
      "root": true,
      "table": "web_sales",
      "datasource": "warehouse",
      "paths": [
        "web_sales > item"
      ],
      "purpose": "top_n",
      "transform": "month_over_month",
      "segment": false,
      "projections": [
        "Category",
        "Web Net Paid"
      ],
      "filters": [
        "category in_list men, children"
      ],
      "inputs": [],
      "strategies": [
        {
          "node": 1,
          "kind": "segment"
        }
      ],
      "join_type": "full",
      "group_by": true,
      "security_filters": [],
      "security_masks": [],
      "identity": "web_sales|category|ws_net_paid"
    }
  ]
}

Timings

Microseconds the server spent parsing and resolving the spec, planning it, and both together.

PropertyTypeDescription
parser_usrequiredinteger

Parsing and resolving the spec.

plan_usrequiredinteger

Planning the resolved query.

total_usrequiredinteger

Both together.

{
  "parser_us": 41,
  "plan_us": 187,
  "total_us": 228
}

Phase

One planning phase and the microseconds it took.

PropertyTypeDescription
namerequiredstring

The phase.

One of resolve, segment_fork, complex_measure, exclusion, inclusion, snapshot, contribution, temporal, top_n, segment, security, reference, alias, sql, final_query

usrequiredinteger

Microseconds spent.

{
  "name": "resolve",
  "us": 23
}

ExplainNode

One node of the plan. A query node reads a table, an aggregation node groups one, and a merge node joins the outputs of its inputs. Fields that do not apply to a node are left out.

PropertyTypeDescription
idrequiredinteger

The node's index.

aliasrequiredstring

The CTE name in the SQL.

kindrequiredstring

The node kind.

One of query, aggregation, merge

rootrequiredboolean

Whether this node produces the final SELECT.

tablestring

The table the node reads, for query and aggregation nodes.

datasourcestring

The datasource the table lives in.

pathsarray of string

Join paths the node walks from its table.

purposestring

Why the node exists when it serves another, such as a top-n ranking or a contribution total.

transformstring

The temporal transform a comparison node serves.

segmentrequiredboolean

Whether the node was imported from a segment's plan.

projectionsrequiredarray of string

What the node projects.

filtersrequiredarray of string

The filters the node applies.

inputsrequiredarray of integer

Ids of the nodes read through the FROM clause.

strategiesarray of Strategy

Inputs attached by a strategy, such as a segment, an exclusion or an expansion.

join_typestring

How a merge node joins its inputs.

group_byrequiredboolean

Whether the node groups.

security_filtersarray of string

Row filters a security policy added.

security_masksarray of string

Fields a security policy masks.

identityrequiredstring

The string the alias hashes from.

{
  "id": 0,
  "alias": "ag5c9bb8d550197b3a219ae50f0b427905",
  "kind": "aggregation",
  "root": true,
  "table": "web_sales",
  "datasource": "warehouse",
  "paths": [
    "web_sales > item"
  ],
  "purpose": "top_n",
  "transform": "month_over_month",
  "segment": false,
  "projections": [
    "Category",
    "Web Net Paid"
  ],
  "filters": [
    "category in_list men, children"
  ],
  "inputs": [],
  "strategies": [
    {
      "node": 1,
      "kind": "segment"
    }
  ],
  "join_type": "full",
  "group_by": true,
  "security_filters": [],
  "security_masks": [],
  "identity": "web_sales|category|ws_net_paid"
}

Strategy

A node attached to another by a planning strategy rather than through the FROM clause.

PropertyTypeDescription
noderequiredinteger

The attached node's id.

kindrequiredstring

The strategy, such as a segment join, an exclusion or an expansion.

{
  "node": 1,
  "kind": "segment"
}

ExploreRequest

The body of /explore. The same spec or expr as /sql, with an optional search term q. A context is accepted and ignored.

PropertyTypeDescription
specSpec

A query: the fields to project, the calculations over them, the filters, and the segments that constrain the population. Unknown keys are rejected (422 InvalidSpec, malformed spec: ...). At least one projection is required. filters is either a flat array of FilterLeaf objects (an AND) or a FilterNode tree of and/or arrays; see Filter.

exprstring

An expression line in place of spec.

contextSecurityContext

Who the query is for. Row-level security policies on the branch read it: a policy that fires resolves the allowed values of its context dimension from the group names, the group tags, the email or the user tags, then filters rows to them or masks the field outside them. A fired policy that resolves nothing denies unless it says unresolved: allow. The bypass flags skip policies that allow a bypass. Every key is optional; unknown keys are rejected. A branch with policies refuses to plan without a context (400 ContextRequired). Nothing from the context is stored.

qstring

Keep fields containing this term or near it by trigram similarity, best first.

{
  "spec": {
    "projections": [
      {
        "field": "category"
      },
      {
        "field": "ws_net_paid"
      }
    ]
  },
  "q": "paid"
}

ExploreResponse

The dimensions and measures that can join the query from where it stands, hidden fields excluded. With q, each carries a score and the lists are ordered best first.

PropertyTypeDescription
dimensionsrequiredarray of ExploreField

Dimensions the query can add.

measuresrequiredarray of ExploreField

Measures the query can add.

total_usrequiredinteger

Microseconds the server spent.

{
  "dimensions": [
    {
      "uid": "net_paid",
      "name": "Net Paid",
      "data_type": "decimal",
      "description": "Net amount paid on store sales",
      "tables": [
        "store_sales"
      ],
      "score": 1
    }
  ],
  "measures": [
    {
      "uid": "net_paid",
      "name": "Net Paid",
      "data_type": "decimal",
      "description": "Net amount paid on store sales",
      "tables": [
        "store_sales"
      ],
      "score": 1
    }
  ],
  "total_us": 312
}

ExploreField

A field /explore offers.

PropertyTypeDescription
uidrequiredstring
namerequiredstring
data_typerequiredstring

One of string, integer, bigint, decimal, date, date_time, boolean

descriptionstring
tablesrequiredarray of string

Uids of the tables that hold the field.

scorenumber

With q, 1 for a containing match or the trigram similarity from 0.5 up.

{
  "uid": "net_paid",
  "name": "Net Paid",
  "data_type": "decimal",
  "description": "Net amount paid on store sales",
  "tables": [
    "store_sales"
  ],
  "score": 1
}

FieldsResponse

The fields of a branch, best match first when searching.

PropertyTypeDescription
fieldsrequiredarray of Field
{
  "fields": [
    {
      "uid": "ws_net_paid",
      "name": "Web Net Paid",
      "kind": "measure",
      "data_type": "decimal",
      "description": "Net amount paid on web orders",
      "synonyms": [
        "web revenue"
      ],
      "tags": [],
      "hidden": false,
      "tables": [
        "web_sales"
      ],
      "score": 1
    }
  ]
}

Field

A dimension or measure as deployed.

PropertyTypeDescription
uidrequiredstring
namerequiredstring
kindrequiredstring

One of dimension, measure

data_typerequiredstring

One of string, integer, bigint, decimal, date, date_time, boolean

descriptionstring
synonymsrequiredarray of string

Other names the field answers to in specs.

tagsrequiredarray of string

Tags, which security policies trigger on.

hiddenrequiredboolean

Hidden fields are left out of listings but still resolve in specs.

tablesrequiredarray of string

Uids of the tables that hold the field.

scorenumber

With q, 1 for a containing match or the trigram similarity.

{
  "uid": "ws_net_paid",
  "name": "Web Net Paid",
  "kind": "measure",
  "data_type": "decimal",
  "description": "Net amount paid on web orders",
  "synonyms": [
    "web revenue"
  ],
  "tags": [],
  "hidden": false,
  "tables": [
    "web_sales"
  ],
  "score": 1
}

DimensionValues

One dimension's sampled values, most frequent first.

PropertyTypeDescription
uidrequiredstring
namerequiredstring
tablestring

The datasource the values were read from.

sampled_atstring (ISO 8601)
sample_sizeinteger

The limit the sampling query ran with; a values list this long is truncated.

distinctinteger

The column's own distinct total, which may exceed the number of values returned.

valuesrequiredarray of object
vrequiredstring

The value, as the warehouse holds it.

ninteger

Rows with this value on the dimension's own table. Frequency on a fact; near-meaningless on a lookup.

{
  "uid": "contact-subchannel-id",
  "name": "Contact Subchannel ID",
  "table": "cs_duckdb",
  "sampled_at": "2026-10-24T11:18:33Z",
  "sample_size": 1000,
  "distinct": 6,
  "values": [
    {
      "v": "SC1",
      "n": 1530
    }
  ]
}

TablesResponse

The tables of a branch.

PropertyTypeDescription
tablesrequiredarray of Table
{
  "tables": [
    {
      "uid": "web_sales",
      "name": "Web Sales",
      "physical_name": "web_sales",
      "cost": 100,
      "datasource": "warehouse",
      "fields": [
        "ws_net_paid",
        "ws_sold_date",
        "category"
      ]
    }
  ]
}

Table

A table as deployed. The planner prefers the cheapest table that answers a question.

PropertyTypeDescription
uidrequiredstring
namerequiredstring
physical_namerequiredstring

The name in the warehouse.

costrequiredinteger

The relative cost from the table file.

datasourcerequiredstring

The datasource key.

fieldsrequiredarray of string

Uids of the fields the table holds.

{
  "uid": "web_sales",
  "name": "Web Sales",
  "physical_name": "web_sales",
  "cost": 100,
  "datasource": "warehouse",
  "fields": [
    "ws_net_paid",
    "ws_sold_date",
    "category"
  ]
}

DeploymentSummary

What is deployed on a branch and when. Counts come from the compiled snapshot.

PropertyTypeDescription
projectrequiredstring

The project uid.

branchrequiredstring
deployed_atrequiredstring

UTC to the minute.

datasourcesrequiredinteger
tablesrequiredinteger
fieldsrequiredinteger
joinsrequiredinteger
pathsrequiredinteger

Join paths the universe formed.

policiesrequiredinteger

Security policies.

testsrequiredinteger
valuesrequiredinteger

Dimensions with sampled values on this branch. 0 means the values route answers 404 for every dimension.

warningsrequiredarray of string

Non-fatal findings from the last deploy.

{
  "project": "tpcds",
  "branch": "main",
  "deployed_at": "2026-10-01 15:04",
  "datasources": 1,
  "tables": 6,
  "fields": 42,
  "joins": 5,
  "paths": 9,
  "policies": 1,
  "tests": 3,
  "values": 4,
  "warnings": []
}

DeployResponse

The answer to deploy and validate, the deployment summary plus the test run. Deploy answers 200 even when tests fail; check test_results.failed.

PropertyTypeDescription
projectrequiredstring

The project uid.

branchrequiredstring
deployed_atrequiredstring

UTC to the minute.

datasourcesrequiredinteger
tablesrequiredinteger
fieldsrequiredinteger
joinsrequiredinteger
pathsrequiredinteger

Join paths the universe formed.

policiesrequiredinteger

Security policies.

testsrequiredinteger
valuesrequiredinteger

Dimensions with sampled values on this branch. 0 means the values route answers 404 for every dimension.

warningsrequiredarray of string

Non-fatal findings from the last deploy.

test_resultsrequiredTestResults

The outcome of running the branch's tests/*.yml.

validated_onlyrequiredboolean

True from /validate, where nothing was stored.

{
  "project": "tpcds",
  "branch": "main",
  "deployed_at": "2026-10-01 15:04",
  "datasources": 1,
  "tables": 6,
  "fields": 42,
  "joins": 5,
  "paths": 9,
  "policies": 1,
  "tests": 3,
  "values": 4,
  "warnings": [],
  "test_results": {
    "passed": 3,
    "failed": 0,
    "results": [
      {
        "name": "Category revenue",
        "status": "passed",
        "file": "category_revenue.yml",
        "message": "generated SQL did not match",
        "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
        "pattern": "SELECT"
      }
    ]
  },
  "validated_only": false
}

TestResults

The outcome of running the branch's tests/*.yml.

PropertyTypeDescription
passedrequiredinteger
failedrequiredinteger

Failed and errored tests together.

resultsrequiredarray of TestResult
{
  "passed": 3,
  "failed": 0,
  "results": [
    {
      "name": "Category revenue",
      "status": "passed",
      "file": "category_revenue.yml",
      "message": "generated SQL did not match",
      "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
      "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
      "pattern": "SELECT"
    }
  ]
}

TestResult

One test. On a failed assert_sql the expected and generated statements are included; on a failed assert_regex the pattern and the generated statement. An error is a test that could not plan.

PropertyTypeDescription
namerequiredstring
statusrequiredstring

One of passed, failed, error

filerequiredstring

The test file's basename.

messagestring

Why the test failed or errored, when it did.

expected_sqlstring

The assert_sql the test expected, when it failed.

generated_sqlstring

The statement the planner produced, when the test failed.

patternstring

The assert_regex the test expected, when it failed.

{
  "name": "Category revenue",
  "status": "passed",
  "file": "category_revenue.yml",
  "message": "generated SQL did not match",
  "expected_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
  "generated_sql": "SELECT T1.\"i_category\" AS \"Category\", sum(T0.\"ws_net_paid\") AS \"Web Net Paid\" FROM web_sales T0 JOIN item T1 ON T0.ws_item_sk = T1.i_item_sk GROUP BY T1.\"i_category\"",
  "pattern": "SELECT"
}

ProjectsResponse

Every deployment on the server.

PropertyTypeDescription
deploymentsrequiredarray of DeploymentSummary

One summary per project and branch.

{
  "deployments": [
    {
      "project": "tpcds",
      "branch": "main",
      "deployed_at": "2026-10-01 15:04",
      "datasources": 1,
      "tables": 6,
      "fields": 42,
      "joins": 5,
      "paths": 9,
      "policies": 1,
      "tests": 3,
      "values": 4,
      "warnings": []
    }
  ]
}