Tables and joins

The schema directory is the schema name verbatim, in the warehouse’s stored case, which is why a Snowflake tree has tables/STALLORA/FCT_ORDERS.yml while a PostgreSQL one has tables/public/orders.yml. The schema_name and table_name fields inside the file are the authoritative identity and the path is derived from them; if they disagree, validation fails.

Table fields

Field	Type	Notes
`schema_name`	string, required	Physical schema, warehouse stored case
`table_name`	string, required	Physical table name, stored case. The filename derives from it
`columns`	list	Inline column objects, below
`description`	string	What a row is, the grain, the gotchas
`domain_path`	string	Path of the domain this table lives in. Must name a domain that exists in the tree. Absent means the table is tracked but not placed in any domain
`grain`	list of strings	Column names that identify a row, stored case
`synonyms`	list of strings	Alternative names users say. Matching is case-insensitive
`is_virtual`	bool	See virtual tables. Only ever appears as `is_virtual: true`
`sql`	string	The defining `SELECT` of a virtual table. Never valid on a physical table
`lineage_description`	string	Managed. Prose provenance of a built table
`source_tables`	list of strings	Managed. Upstream tables this table is built from
`source_sql`	string	Managed. The upstream SQL that builds this table, not the virtual-table definition
`table_type`	string	Managed. Warehouse-reported type from introspection
`approximate_row_count`	integer	Managed. From introspection

Managed fields are written by Cassis, from warehouse introspection or from a Cassis build. Leave them as you found them.

Column fields

Field	Type	Notes
`name`	string, required	Physical column name, stored case
`data_type`	string	Warehouse type as introspected
`description`	string	Value lists, semantics, caveats
`nullable`	bool	Defaults to true. Only ever appears as `nullable: false`
`ordinal`	integer	Zero-based position from introspection. Drives the canonical column order
`source`	enum	`introspected` or `manual`. `manual` marks a hand-added column that warehouse sync will never overwrite or drop
`synonyms`	list of strings	Alternative names for this column
`unit`	string	Free text: `EUR`, `days`, `count`, `%`

Columns are ordered by ordinal, with columns lacking one going last, alphabetically.

columns:
- data_type: TEXT
  description: Product category label. Join key from PRODUCTS.CATEGORY.
  name: CATEGORY
  ordinal: 0
- data_type: TEXT
  description: 'Broad department the category rolls up to. Values: electronics, home_garden,
    fashion, health_beauty, sports_leisure, media, food_drinks, industry_tools, automotive,
    office, other.'
  name: DEPARTMENT
  ordinal: 1
  synonyms:
  - department
description: Lookup mapping each product category to a broader department. One row
  per category. LEFT JOIN from PRODUCTS on CATEGORY to roll sales up to the department
  level.
domain_path: marketplace
grain:
- CATEGORY
schema_name: STALLORA
table_name: DIM_CATEGORY

Virtual tables

A virtual table is a table the warehouse does not have: is_virtual: true plus a defining SELECT in sql. To the agent it looks like any other table, with its own description, columns, grain, and joins. At query time Cassis expands the reference by inlining the sql body. Declare the output columns inline as usual, marked source: manual since they are hand-declared rather than introspected.

Expansion is single-level. A virtual table’s sql must not reference another virtual table, because such a reference will not expand at query time.
Quote every identifier in the sql body with its explicit stored case, output column aliases included. Bare identifiers get case-folded by the warehouse and stop matching the declared columns.

columns:
- data_type: TEXT
  name: SELLER_ID
  source: manual
- data_type: NUMBER
  description: Delivered GMV for the seller in the month, in EUR.
  name: GMV
  source: manual
  unit: EUR
description: One row per seller per month, with delivered GMV.
domain_path: marketplace
grain:
- SELLER_ID
- MONTH
is_virtual: true
schema_name: STALLORA
sql: |-
  SELECT
    "STALLORA"."ORDER_ITEMS"."SELLER_ID" AS "SELLER_ID",
    DATE_TRUNC('month', "STALLORA"."ORDERS"."ORDER_PURCHASE_TIMESTAMP") AS "MONTH",
    SUM("STALLORA"."ORDER_ITEMS"."PRICE") AS "GMV"
  FROM "STALLORA"."ORDER_ITEMS"
  JOIN "STALLORA"."ORDERS"
    ON "STALLORA"."ORDER_ITEMS"."ORDER_ID" = "STALLORA"."ORDERS"."ORDER_ID"
  GROUP BY 1, 2
table_name: SELLER_MONTHLY_SALES

Joins

Every join in the ontology lives in a single joins.yml, as one YAML list. There are no per-table join files. The list is kept in canonical sort order (by from_schema, from_table, to_schema, to_table, then condition_sql), so keep it sorted when adding one by hand: an out-of-order list fails the round-trip check.

Field	Type	Notes
`from_schema`	string, required	Stored case
`from_table`	string, required	Stored case
`to_schema`	string, required	Stored case
`to_table`	string, required	Stored case
`condition_sql`	string	The ON-clause boolean expression, columns table-qualified, in your warehouse’s dialect. A join without one is useless, so always set it
`column_pairs`	list	`{from_column, to_column}` equi-join pairs. You can omit this when authoring: Cassis derives the pairs from `condition_sql` on import, and they appear in the next export
`cardinality`	enum	`one_to_one`, `one_to_many`, `many_to_one`, `many_to_many`, oriented from to. `many_to_one` means many `from`-rows match one `to`-row
`description`	string	Join-specific caveats: fan-out traps, partial bridges, dedup requirements
`source`	enum	`introspected` or `manual`. Set `source: manual` on joins you author. `introspected` joins are owned by warehouse sync, which derives them from foreign keys and may reconcile them
`parse_ok`	bool	Managed. `false` means Cassis could not parse `condition_sql` into pairs and treats the join as opaque. Never write it

- cardinality: many_to_one
  column_pairs:
  - from_column: CATEGORY
    to_column: CATEGORY
  condition_sql: STALLORA.PRODUCTS.CATEGORY = STALLORA.DIM_CATEGORY.CATEGORY
  description: Maps a product's category to its department.
  from_schema: STALLORA
  from_table: PRODUCTS
  source: manual
  to_schema: STALLORA
  to_table: DIM_CATEGORY

Removing a table takes its joins and metrics with it. If you delete a table file by hand, delete the joins that reference it in the same commit, or the import fails naming the unknown table.