Modeling with zsql

Model files are plain YAML you can write by hand. Two commands save the typing, and they are different jobs:

  • zsql generate drafts 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 table writes 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 / @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 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.

Next steps