Tables

Table models define semantic metadata for physical database tables.

Overview

Table models (tbl.*.yml) are the foundation of your semantic model. They map physical database tables to business-friendly semantic definitions.

Table Structure

name: Store Sales
physical_name: store_sales
datasource: tpcds
cost: 100

fields:
  - type: dimension
    name: Order ID
    data_type: integer
    expression:
      primary_key: true
      sql: order_id

  - type: measure
    name: Total Revenue
    data_type: decimal
    expression:
      sql: sum(amount)

Required Fields

name

Logical name for the table in the semantic model. Must be unique within the datasource.

name: Store Sales

Notes:

  • Used in relationships and in the tables discovery endpoint
  • Never appears in a query spec: specs name fields, and the planner picks the table
  • Can differ from physical table name

physical_name

The actual table name in the database. Can include schema/catalog prefix.

physical_name: store_sales
# Or with schema:
physical_name: sales.orders

datasource

Datasource key or name this table belongs to.

datasource: tpcds

cost

Numeric value influencing table selection. Lower cost = preferred.

cost: 100

Guidelines:

  • Dimension tables: cost: 10 (lower)
  • Fact tables: cost: 100 (higher)
  • Hot tier: Lower cost
  • Cold tier: Higher cost

Optional Fields

snapshot

For snapshot tables (e.g., inventory), name the date dimension the table is snapped by. The dimension must be a date type, on this table or unambiguous in its universe.

snapshot: Date

Enables snapshot measures on the table (snapshot: beginning / snapshot: ending on a measure). The two keys share a name on purpose: the table’s snapshot says which date, the measure’s says which end of the period.

tags

Metadata tags for organization. Stored on the table; nothing in planning reads them.

tags:
  - marketing
  - operations
  - finance

partitions

Data availability constraints that rank the table for routing. Predicates that rank: between, greater_than, greater_than_or_equal_to, less_than, less_than_or_equal_to (number or date dimensions) and in_list (any dimension). partition: is accepted as an alias.

partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 24m  # 24 months ago
    filter_value_end: 1d  # yesterday

Use cases:

  • Rolling window tables
  • Partitioned tables
  • Region-specific data

imports

Import field definitions from other files.

imports:
  - ../common/common_dates.yml
  - ../common/geo_dimensions.yml

Notes:

  • Imports processed first
  • Local fields merged after
  • Supports relative paths
  • Validated for circular dependencies

Fields

Define dimensions and measures for the table.

fields:
  - type: dimension
    name: Customer Name
    data_type: string
    expression:
      sql: customer_name

  - type: measure
    name: Total Revenue
    data_type: decimal
    expression:
      sql: sum(amount)

Learn about fields →

File Organization

Naming Convention

  • File pattern: tbl.{table_name}.yml
  • Examples:
    • tbl.orders.yml
    • tbl.store_sales.yml
    • tbl.dse.call_center_d.yml (with schema)

Directory Structure

Organize tables by domain. zsql new table NAME --datasource DS writes models/<domain>/tbl.<slug>.yml (default domain core); any depth under models/ is found.

models/
├── sales/
│   ├── tbl.orders.yml
│   ├── tbl.customers.yml
│   └── rel.sales.yml
├── inventory/
│   ├── tbl.products.yml
│   └── tbl.stock.yml
└── common/
    ├── tbl.date_dim.yml
    └── tbl.geography.yml

Complete Example

name: Store Sales
physical_name: store_sales
datasource: tpcds
cost: 100

# Optional: Import common fields
imports:
  - ../common/common_dates.yml

# Optional: Define partitions
partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 24m
    filter_value_end: 1d

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 Quantity
    description: Quantity of items sold
    data_type: integer
    expression:
      sql: sum(ss_quantity)

  - type: measure
    name: Store Sales Price
    description: Actual sales price
    data_type: decimal
    format: currency:2
    synonyms:
      - sales price
      - selling price
      - store revenue
    expression:
      sql: sum(ss_sales_price)

Best Practices

  1. Use descriptive names - Business-friendly table names
  2. Set appropriate costs - Lower for dimensions, higher for facts
  3. Organize by domain - Group related tables
  4. Import common fields - Reuse field definitions
  5. Define partitions - Help planner route queries
  6. Add descriptions - Document table purpose
  7. Set a cost - A table without one is assumed to cost 0, with a warning from zsql check

Next Steps