Modeling with zsql

Model files are plain YAML you can write by hand. zsql new saves the typing: it writes a table or relation file with every key documented in comments, into the domain folder you name. zsql check then validates the whole project on the service and reports counts, warnings and test results without deploying anything.

zsql new table

zsql new table NAME --datasource DS [--physical-name P] [--domain core]

Writes models/<domain>/tbl.<stem>.yml, where the stem is the slug of NAME with - turned into _. --physical-name is the warehouse table; it defaults to the stem. An existing file is refused.

$ zsql new table "Store Sales" --datasource tpcds --physical-name store_sales --domain store
create models/store/tbl.store_sales.yml

The template, with the name, physical name and datasource filled in:

# A table and the dimensions and measures it holds. Joins between tables
# live in rel.*.yml files.
name: Store Sales
physical_name: store_sales   # defaults to the stem
datasource: tpcds
# The planner prefers the cheapest table that answers a question. Dimension
# tables low, big facts high.
cost: 10

# For a snapshot table (inventory, balances), the date dimension it snapshots on.
# snapshot: Date

# Data the table holds, so the planner prefers it for filters inside the
# range and avoids it for filters outside. Predicates: between,
# greater_than, greater_than_or_equal_to, less_than, less_than_or_equal_to
# (number or date dimensions) and in_list (any dimension).
# partitions:
#   - dimension: Date
#     predicate: between
#     filter_value: 2y
#     filter_value_end: 1d
#   - dimension: Region
#     predicate: in_list
#     filter_value: US, EU

fields:
  # - type: dimension                 # dimension | measure
  #   name: Category
  #   description: Product category
  #   data_type: string               # string | integer | bigint | decimal | date | date_time | boolean
  #   synonyms: [dept]
  #   tags: []                        # policy triggers, e.g. [pii]
  #   expression:
  #     sql: category
  #     primary_key: false
  #
  # - type: measure
  #   name: Revenue
  #   data_type: decimal
  #   format: currency:2
  #   expression:
  #     sql: sum(revenue)

Fill it in: set cost (dimension tables low, big facts high), uncomment and edit the fields. A dimension’s expression.sql is a column or a scalar expression; a measure’s holds an aggregate. The file for the running example becomes:

name: Store Sales
physical_name: store_sales
datasource: tpcds
cost: 100

fields:
  - type: dimension
    name: Store Ticket number
    description: Actual ticket number of an order
    data_type: integer
    expression:
      primary_key: true
      sql: ss_ticket_number

  - type: measure
    name: Store Net Paid
    description: Net amount paid for sales
    data_type: decimal
    expression:
      sql: sum(ss_net_paid)

  - type: measure
    name: Store Net Profit
    tags: [secure-cat]
    description: Net profit from sales
    data_type: decimal
    expression:
      sql: sum(ss_net_profit)

Every field key (hidden, synonyms, format, grains, exclusions, inclusions, extended_blend_group, snapshot, …) is documented in Semantic model.

zsql new relation

zsql new relation --datasource DS [--domain core]

Writes models/<domain>/rel.<domain>.yml. One relation file per datasource per domain is the usual shape; it holds every join between that datasource’s tables that the domain introduces.

$ zsql new relation --datasource tpcds --domain store
create models/store/rel.store.yml

The template, verbatim:

# Joins between this datasource's tables, by the names in their table files.
# Cardinality is required; many_to_one is the common shape (fact to dimension).
datasource: tpcds

# orders_customer:
#   left: Orders
#   right: Customers
#   sql: left.customer_id = right.id
#   cardinality: many_to_one        # many_to_one | one_to_many | one_to_one
#   join: inner                     # inner | left | right
#   # allow_measure_expansion: false

Each join is a key with left and right table names (the name: in their table files), a sql of the form left.column = right.column, and a cardinality. Filled in for the example:

datasource: tpcds

store_sales_sold_date:
  left: Store Sales
  right: Date
  sql: left.ss_sold_date_sk = right.d_date_sk
  cardinality: many_to_one

store_sales_item:
  left: Store Sales
  right: Item
  sql: left.ss_item_sk = right.i_item_sk
  cardinality: many_to_one

Naming

• Title Case names. Store Net Paid, Item Category, Month of Year. Names are what callers write in specs and what appears as column aliases in the SQL.
• One name, one concept. Table names are unique in the project; field names should be unique too, or callers must disambiguate with @d / @m or a uid. Prefix fact-specific fields with the fact (Store Quantity, Catalog Quantity) and leave conformed dimensions plain (Date, Item Category).
• Uids derive from names. Store Net Paid becomes store-net-paid. Renaming a field changes its uid; callers that reference uids notice.
• Synonyms are extra names callers can use: synonyms: [dept] on Item Category lets dept resolve, and fuzzy matching corrects close misspellings.

Domain folders

models/ is read recursively; any depth works. The convention zsql new follows is one folder per business domain: models/common/ for conformed dimensions (Date, Item, Store), models/store/ for store sales and its joins, models/catalog/, models/inventory/. The folder name has no meaning to the loader; it keeps related tables and their relation file together.

models/
├── common/
│   ├── tbl.date_dim.yml
│   └── tbl.item.yml
├── store/
│   ├── tbl.store_sales.yml
│   └── rel.store.yml
└── inventory/
    ├── tbl.inventory.yml
    └── rel.inventory.yml

zsql check

zsql check

Packs the project exactly as zsql deploy would and sends it to the validate route. The service loads it, forms the join routes, runs the tests, and answers with the same counts a deploy returns, but keeps nothing. Use it after every edit.

$ zsql check
validate tpcds (6214 bytes) as tpcds/main (the production branch) to https://app.0sql.io
validated tpcds/main: 6 tables, 42 fields, 5 joins, 9 paths, 1 policies
warning: models/store/tbl.store_returns.yml: table Store Returns has no cost; 0 assumed
PASSED store net paid by category
PASSED catalog quantity by month
PASSED date range
tests: 3 passed, 0 failed

The response holds datasources, tables, fields, joins, paths (join routes formed), policies, tests, a warnings list, and test_results with passed, failed and one entry per test.

A model that does not load fails with one message naming the file:

$ zsql check
validate tpcds (6198 bytes) as tpcds/main (the production branch) to https://app.0sql.io
DeployError: Error in models/store/tbl.store_sales.yml:
	Expression errors for Store Net Paid: Sql measure should have an aggregation function

Every error has the form Error in <file>: <message>. Warnings do not fail the check: a table without cost, an unknown key (table Store Sales: unknown key 'owner' is ignored), an ambiguous join route. Read them; they describe choices the planner made for you. The complete list is in Troubleshooting.

Agents

If an LLM writes your model files, point it at /docs/llms.txt: the whole guide, including every YAML key, as one markdown file. Have it run zsql check after each edit and feed the errors back.

Next steps

• Semantic model reference
• Tests
• Deploying