Skip to main content

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 queries and relationships
  • Displayed to business users
  • 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_date​

For snapshot tables (e.g., inventory), specify the date dimension.

snapshot_date: Date

Enables snapshot measures (beginning/ending snapshots).

tags​

Metadata tags for organization.

tags:
  - marketing
  - operations
  - finance

partition​

Data availability constraints. Only between and in_list predicates supported.

partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 24m  # 24 months ago
    filter_value_end: 1d  # yesterday
    description: "Table only has rolling 2 year data"

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:

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
    description: "Rolling 2 year window"

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

Next Steps​