Modeling with zsql
Model files are plain YAML you can write by hand. Two commands save the typing, and they are different jobs:
zsql generatedrafts a table file from the catalog, with a field per column and every guess marked as one. This is how a model usually starts.zsql generate NAME --empty tablewrites the same file empty, from the template, with every key documented in comments and nothing guessed. This is the one to use when there is no catalog behind the table, or when you would rather write it yourself.
zsql check then loads the whole project from the files here, checks it against the catalog, and reports counts, warnings and test results — with nothing running and nothing deployed.
An empty table file
zsql generate NAME --empty table [--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 generate "Store Sales" --empty table --physical-name store_sales --domain store
create models/store/tbl.store_sales.yml
next: name its fields, then zsql check
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.
A relationship file
zsql generate --relationships <domain>/<name> # the template
zsql generate --relationships <domain>/<name> --infer # filled in from the table files there
Writes models/<domain>/rel.<name>.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. --infer reads the table files already in that directory and writes the joins it can place, with everything it cannot placed as a comment — see Generating model files.
$ zsql generate --relationships store/store
created models/store/rel.store.yml
next: zsql check
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/@mor 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 Paidbecomesstore-net-paid. Renaming a field changes its uid; callers that reference uids notice. - Synonyms are extra names callers can use:
synonyms: [dept]onItem Categoryletsdeptresolve, and fuzzy matching corrects close misspellings.
Domain folders
models/ is read recursively; any depth works. The convention zsql generate 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
Loads the project from the files here with the same loader a server uses, forms the join routes, runs the tests, and prints the same counts a deploy returns. Use it after every edit. zsql -r check sends the archive to the server’s validate route instead, which keeps nothing; that is what CI used to need and no longer does.
$ zsql check
validated tpcds/main: 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
validated tpcds/main: 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.