# Tables and joins

One file per table at tables/schema/table.yml with its columns inline, and one joins.yml for every join in the ontology.

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](#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.

```yaml title="cassis/tables/STALLORA/DIM_CATEGORY.yml"
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.

```yaml title="cassis/tables/STALLORA/SELLER_MONTHLY_SALES.yml"
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 |

```yaml title="cassis/joins.yml"
- 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.