Generating model files

zsql generate turns catalog entries into model files: one tbl.<name>.yml per table, with a field per column, and a rel.<name>.yml of joins. It is a first draft and says so on every line it guessed.

It never rewrites a file. A table that already has a model is skipped, and --new-columns adds only the fields it lacks. The draft is yours from the moment it is written.

zsql generate orders customers            # named tables
zsql generate --schema public             # every table of a schema
zsql generate shopping/ --all             # every table, into models/shopping/
zsql generate orders --new-columns        # the columns a model lacks
zsql generate returns --empty table       # an empty file from the template, no catalog
zsql generate --relationships main/sales --infer

With no table and a terminal you get a picker. Without a terminal it refuses and names the ways to say what to draft, because --all on a large warehouse is never what someone meant by leaving it out:

error: say which tables: `zsql generate orders customers`, `--schema NAME`, or `--all`;
`zsql catalog search PATTERN --json` lists what the catalog holds

What a draft looks like

$ zsql generate cs_contact_subchannel_d cs_contact_skill_d
created models/main/tbl.cs_contact_skill_d.yml (6 fields, 0 measures)
created models/main/tbl.cs_contact_subchannel_d.yml (3 fields, 0 measures)
next: zsql check, then zsql sample
# Generated from catalog/cs.json on 2026-10-24. Every line ending in "inferred"
# is a guess from a column's name or type: check it. This file is yours
# from here; `zsql generate` never rewrites it, and lists new columns.
name: Cs Contact Subchannel D
physical_name: main.cs_contact_subchannel_d
datasource: cs
cost: 10                        # inferred: no row estimate from the warehouse

fields:
  - type: dimension
    name: Contact Subchannel Id
    data_type: string           # VARCHAR
    expression:
      sql: contact_subchannel_id

Every guess carries a comment saying so, and the warehouse’s own type sits beside the one 0sql chose. A column that looks additive becomes a measure, and says why:

  - type: measure               # inferred: "cust_msg_cnt" looks additive; make it a dimension if not
    name: Cust Msg Cnt
    data_type: bigint           # BIGINT
    expression:
      sql: sum(cust_msg_cnt)

That is the thing to read first in any draft. A count column is a measure; an id that happens to be an integer is not.

What it gets right on its own

  • Columns keep the warehouse’s order, from the catalog’s ordinal positions.
  • A reserved column is quoted, against the dialect’s own keyword list rather than a shared list, so order on Postgres and order on BigQuery are each handled as that warehouse needs.
  • One type per field name across the whole run. If order_date is a DATE on one table and a TIMESTAMP on another, the run picks one and uses it everywhere, because a field named on several tables is one field.
  • A description is written once, on one table, not repeated on every table the field appears on — which is what the model wants, and what zsql check warns about when two files disagree.
  • A model name is qualified with its schema when two schemas hold a table of the same name, so the two drafts do not collide.
  • cost comes from the warehouse’s row estimate where there is one, and says inferred where there is not.

What you still have to do

The three keys that decide whether a request resolves are the ones a column name cannot supply: name, description and synonyms. A draft gives you a name derived from the column and no description at all. Writing those is the modelling work; see Fields.

Domains are not schemas

The models/<domain>/ directories are the model’s subject areas and need not mirror the warehouse. A trailing slash on an argument says which one to draft into:

zsql generate shopping/ cs_contact_f cs_call_center_d

--dir does the same; without either, each table goes in a directory named for its own schema.

Adding to a model you have

zsql generate orders --new-columns              # every column the model lacks
zsql generate orders --columns tax,shipping     # just these

Both append fields to the existing file and leave everything in it alone:

$ zsql generate cs_contact_f --new-columns
added   3 fields to models/tbl.contact.yml
next: zsql check, then zsql sample

With nothing new to add it says so rather than touching the file:

nothing to do: every table named has a model, with no new columns

A table the catalog does not have can be read and drafted in one step with --live, which reads it from the warehouse, adds it to the catalog and then drafts it.

An empty file

$ zsql generate returns --empty table --domain core
create models/core/tbl.returns.yml
next: name its fields, then zsql check

The template with no catalog behind it: every key commented, every option listed, nothing guessed. This is what zsql new table used to do. --physical-name sets the physical name when it differs from the table name, and --domain picks the directory (core when unsaid).

Relationships

zsql generate --relationships main/sales          # the template, to write by hand
zsql generate --relationships main/sales --infer  # filled in from the table files

--relationships takes {domain}/{name}, {domain}, or nothing for every domain that has tables, and writes models/{domain}/rel.{name}.yml.

--infer runs no query. It reads the table files in that directory and matches key column names against what each table is about:

$ zsql generate --relationships joins/sales --infer
created models/joins/rel.sales.yml (1 join inferred)
        Cs Contact F -> Cs Call Center D on call_center_id
next: zsql check
cs_contact_cs_call_center:
  left: Cs Contact F
  right: Cs Call Center D
  sql: "left.call_center_id = right.call_center_id"
  cardinality: many_to_one      # inferred from the name, and the first column there: check the direction
  join: left                    # inferred: a guess, so no row is dropped by it; inner once you trust it

join: left is deliberate: a guessed join should not silently drop rows, so it starts permissive and you tighten it to inner once you trust it. cardinality is the guess to check hardest, because getting it wrong is how a fan-out happens; see Cardinality.

What it cannot place, it writes down rather than guessing. Columns that look like keys pointing at nothing in the directory become comments at the top of the file:

#   Cs Contact F.contact_subchannel_id points at a contact subchannel, and no table here is about one
#   Cs Contact F.account_id points at an account, and no table here is about one

That list is a to-do: either the table belongs in this domain and is not drafted yet, or the join crosses domains and you write it by hand. Either way it is visible instead of missing.

After generating

zsql check          # does it load, and does it match the catalog
zsql sample         # collect values for the dimensions you turned sampling on for
zsql test --new NAME

--dry-run on any form says what would be written and writes nothing. --json or --agent reports the same as a document.

Next steps