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. Without one, the agent reads the warehouse’s comment on the table
`domain_path`	string	Path of the domain this table lives in. Must resolve to a domain declared in the tree, or to the root, `''`, which Cassis writes for a table at the root. A table without the key is unassigned, and `cassis context check` warns about it. An import places a physical table without the key at the root when it carries curation or the context already holds it, and skips it otherwise
`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	Semantics, encodings, caveats. The values the column stores go in `enum_values`, not here. Without one, the agent reads the warehouse’s comment on the column
`enum_values`	list of `{value, description}`	Every value the column stores, verbatim. See enum values
`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.
  enum_values:
  - value: electronics
  - value: home_garden
  - value: fashion
  - value: health_beauty
  - value: sports_leisure
  - value: media
  - value: food_drinks
  - value: industry_tools
  - value: automotive
  - value: office
  - value: 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

Enum values

enum_values lists every value a categorical column stores: a status, a type, a code. The agent takes its filter values from this list, exactly as written, so WHERE DEPARTMENT = 'home_garden' matches the stored rows. Each item has a value and an optional description giving its meaning when the value alone doesn’t say it.

- data_type: NUMBER(38,0)
  description: Fulfilment priority set by the warehouse team.
  enum_values:
  - value: '1'
    description: Same-day dispatch
  - value: '2'
    description: Next-day dispatch
  - value: '3'
  name: PRIORITY

The complete set, verbatim. Write each value exactly as the warehouse stores it, case included, and list them in a meaningful order (low, medium, high). Leave the field out when you don’t have the full set: the agent reads the list as the only values the column holds. A few notable codes in a large column belong in the description instead.
Every value is text. Quote anything YAML would read as something else: numbers ('07' would otherwise become 7), yes/no/on/off, null and dates. Validation rejects an unquoted one and says which.
Limits. Up to 200 values per column, each value up to 255 characters and unique within the column, each description up to 500 characters. An empty value is not allowed.
cassis context check warns when a value can’t be stored by the column’s type, such as abc on an integer column or 07 where the warehouse stores 7.

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 context 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.