TPC-DS Tutorial

Complete walkthrough of the TPC-DS benchmark semantic model.

Overview

The TPC-DS Tutorial is a nearly complete model of the TPC-DS benchmark system. It demonstrates best practices for modeling a complex data warehouse with 0sql.

Project Structure

tpcds-tutorial/
├── project.yml              # Project configuration
├── datasources.yml          # Datasource definitions
├── models/                  # Semantic model files
│   ├── catalog/            # Catalog channel models
│   ├── common/             # Shared dimension tables
│   ├── inventory/          # Inventory models
│   └── store/              # Store channel models
└── tests/                   # Planner tests, run on every deploy

Key Components

Datasource Configuration

tpcds:
  name: TPC-DS (Postgres)
  adapter: postgres
  tier: warm
  host: localhost
  port: 5432
  database: tpcds
  schema: public
  username: tpcds

adapter and tier are what the planner uses, and name is what the response calls the datasource. The connection keys are carried as metadata for your own application; 0sql never uses them, and zsql deploy strips any secret before building the archive.

Table Models

Fact Tables

Store Sales (models/store/tbl.store_sales.yml):

name: Store Sales
physical_name: store_sales
datasource: tpcds
cost: 100  # Fact table: higher cost

fields:
  - type: dimension
    name: Store Ticket number
    data_type: integer
    expression:
      primary_key: true
      sql: ss_ticket_number

  - type: measure
    name: Store Quantity
    data_type: integer
    expression:
      sql: sum(ss_quantity)

  - type: measure
    name: Store Sales Price
    data_type: decimal
    expression:
      sql: sum(ss_sales_price)

Dimension Tables

Store (models/store/tbl.store.yml):

name: Store
physical_name: store
datasource: tpcds
cost: 10  # Dimension table: lower cost

fields:
  - type: dimension
    name: Store Name
    data_type: string
    expression:
      sql: s_store_name

Relationships

Store Relationships (models/store/rel.store.yml):

datasource: tpcds

# Store Sales to Date Dimension
store_sales_sold_date:
  left: Store Sales
  right: Date
  sql: left.ss_sold_date_sk = right.d_date_sk
  cardinality: many_to_one

# Store Sales to Customer Dimension
store_sales_customer:
  left: Store Sales
  right: Customer
  sql: left.ss_customer_sk = right.c_customer_sk
  cardinality: many_to_one

# Store Sales to Store Dimension
store_sales_store:
  left: Store Sales
  right: Store
  sql: left.ss_store_sk = right.s_store_sk
  cardinality: many_to_one

Key Patterns

1. Cost Configuration

  • Dimension tables: cost: 10 (lower cost = preferred)
  • Fact tables: cost: 100 (higher cost = used when needed)

2. Primary Keys

Mark unique identifiers as primary keys:

- type: dimension
  name: Store Ticket number
  expression:
    primary_key: true
    sql: ss_ticket_number

3. Measure Aggregation

All measures include aggregation:

- type: measure
  name: Store Quantity
  expression:
    sql: sum(ss_quantity)

4. Relationship Cardinality

Most relationships are many_to_one:

store_sales_store:
  left: Store Sales
  right: Store
  sql: left.ss_store_sk = right.s_store_sk
  cardinality: many_to_one

Testing

Test files validate SQL generation:

name: Store Sales Positive Values

projections:
  - Store Sales Price
  - Store Quantity

assert_sql: "SELECT sum(T0.\"ss_quantity\") AS \"Store Quantity\", sum(T0.\"ss_sales_price\") AS \"Store Sales Price\" FROM store_sales T0"

Tests run on every zsql deploy and can be re-run with zsql test. A failing test exits non-zero, which is what you want in CI.

Lessons Learned

  1. Organize by domain - Group related tables (catalog, store, common)
  2. Use appropriate costs - Lower for dimensions, higher for facts
  3. Mark primary keys - Helps with query optimization
  4. Define relationships - Enable cross-table queries
  5. Test the planner - Assert the SQL a projection produces in tests/*.yml

Next Steps