Cubes

A cube is a YAML file that models one dataset in the semantic layer: its source table or query, its dimensions, its measures, and how it joins to other cubes.

File structure

# cube name: "orders"

# Data source (exactly one of sql_table / sql)
sql_table: public.orders

# Metadata
title: Orders
description: One row per order line. Source ERP export, refreshed nightly.

dimensions: []
measures: []
joins: []

# Optional
segments: []
sql_alias: ord
extends: base_cube_name
meta: {}

Data source

Exactly one of:

Property	Use
sql_table	Direct reference to a table: sql_table: public.orders
sql	A custom SQL query, for source-level joins, filters, or transformations

sql: |
  SELECT o.*, c.name AS customer_name
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.id
  WHERE o.status != 'deleted'

Metadata

Property	Type	Description
title	string	Human-readable display name.
description	string	Maintainer-facing notes (provenance, grain, refresh cadence). Does not reach the agent — for agent-facing context, describe the individual dimensions and measures, which propagate into every view that includes them.
sql_alias	string	Custom alias for the cube in generated SQL.
meta	object	Free-form metadata (tags, owner, …).

Dimensions

Attributes for grouping and filtering.

dimensions:
  - name: id
    sql: id
    type: number
    primary_key: true

  - name: status
    sql: status
    type: string
    description: "'completed', 'pending', or 'cancelled'. Internal test orders use 'draft'."

  - name: created_at
    sql: created_at
    type: time

  - name: customer_email
    sql: "{customers.email}"   # field from a joined cube
    type: string

Property	Description
name	Field name (snake_case).
sql	Column name, SQL expression, or {joined_cube.field} reference.
type	string, number, time, boolean, or geo.
primary_key	Set true on the cube’s primary key (required for correct joins).
description	Agent-facing — propagates into views. Put per-field conventions and caveats here. See writing good descriptions.

Measures

Aggregated metrics.

measures:
  - name: count
    type: count

  - name: total_revenue
    sql: amount
    type: sum
    description: Gross revenue in CAD, before returns.

  - name: unique_customers
    sql: customer_id
    type: count_distinct

  - name: completed_revenue
    sql: "CASE WHEN {status} = 'completed' THEN {amount} ELSE 0 END"
    type: sum

type	Meaning
count	Row count
sum / avg / min / max	Standard aggregations over sql
count_distinct	Unique value count
number	Custom SQL calculation (e.g. ratios of other measures)

Joins

Relationships to other cubes, enabling cross-cube queries and view join paths.

joins:
  - name: customers
    sql: "{CUBE}.customer_id = {customers}.id"
    relationship: many_to_one

  - name: order_items
    sql: "{CUBE}.id = {order_items}.order_id"
    relationship: one_to_many

Property	Description
name	The joined cube’s name.
sql	Join condition. {CUBE} refers to the current cube; {other_cube} to the joined one.
relationship	many_to_one, one_to_many, one_to_one, or many_to_many (the latter via an intermediate cube).

Segments

Named, reusable filters.

segments:
  - name: completed_orders
    sql: "{CUBE}.status = 'completed'"
  - name: high_value
    sql: "{CUBE}.amount > 1000"

Advanced properties

Property	Description
extends	Inherit everything from another cube and override selectively.
access_policy	Column- and row-level access rules. See access policies.

Conventions

• Cube names and field names in snake_case: customer_orders, total_revenue.
• Always define a primary_key dimension.