Semantic layer (cubes)

Semantic Models is where business metrics are defined once and reused by the portal, Voir AI agents and API clients.

Concepts

• A cube is a YAML definition (stored in the workspace repository under semantic-models/cubes/) with:
  • a table (the source),
  • measures (aggregates),
  • dimensions (group-by fields),
  • optional joins, a filter and time grains.
• Cubes compile to DN Warehouse (StarRocks) SQL.
• They're served over REST (/semantic/query) and MCP (used by Voir). The page has a Warehouse Browser, a query playground and Agent connector endpoints (API keys are under Settings → API Keys).
• Reading cubes needs Semantic Models: Use or higher.

Writing a cube

name: sales_daily
table: acme_ab12cd_iceberg.gold.sales_daily      # StarRocks-visible name
filter: "is_current = true"                        # ANDed into every query (e.g. SCD2 current rows)
measures:
  - name: revenue
    sql: SUM(net_amount)
  - name: orders
    sql: COUNT(DISTINCT order_id)
  - name: flagged_orders
    sql: SUM(CASE WHEN is_flagged THEN 1 ELSE 0 END)
dimensions:
  - name: channel
    sql: channel
  - name: order_date
    sql: order_date
    type: time
time_grains: [day, week, month]

Rules:

• table must be a name the warehouse can see:
  • <safe>_iceberg.<schema>.<table> for lakehouse tables, or
  • default_catalog.<safe>.<table> for native warehouse tables.
  • Not lakehouse.…. That name only exists in Trino.
• A measure's sql is the full aggregate (SUM(x), COUNT(DISTINCT y), CASE WHEN inside is fine).
• A dimension's sql is a bare column, or {alias.col} from a join.
• Query filter operators: eq, neq, gt, gte, lt, lte, in.
• Build cubes on gold tables, which are stable, de-duplicated and typed. Never on bronze.

Joined cubes

joins:
  - name: customer
    relationship: many_to_one
    sql: "{CUBE}.customer_id = {customer.customer_id} AND {customer.is_current} = true"

• Relationships are many_to_one, one_to_many and one_to_one. Declare them honestly. A one_to_many join multiplies rows, which inflates sums and counts (fan-out). The editor warns you, but live queries don't block it.
• Put SCD2 guards (current-row conditions) in the ON clause.
• If a join is really one-to-many, pre-join or pre-aggregate in a dbt gold model instead.

Materialized views (MVs)

Tick Materialize as Warehouse MV to create mv_<cube> in the DN Warehouse. Matching queries are rewritten to it automatically.

Rule	Detail
Can't materialize	Cubes with a referenced one_to_many join (joins_not_materializable), and cubes with only measures (no dimension)
Joined cubes	Can be materialized, if their joins are many-to-one or one-to-one
⚠️ Freshness	MVs over lakehouse (Iceberg) tables don't notice new data by themselves. Refresh with the cube card's Refresh button, or a Refresh cubes node in a Job after the dbt step. Otherwise they serve stale numbers.
Rewrite defeated by	LIMIT, ORDER BY or window functions in the query
Renaming a cube	Drops and recreates the MV. External references to the old name break (404).
Cube list cache	About 60 s. New cubes may take a minute to appear.
Rows per query	Default 100, maximum 1,000 (set limit). Cubes are for aggregated answers. For detail rows, query the gold table in the Workbench.
Direct SQL on mv_*	Denied by design. Go through the semantic layer.

The recommended production pattern is an Ingest + Transform job: pipeline → dbt silver → dbt gold → Refresh cubes.

Row-level security on cubes

Data & AI Security → Data Governance → Cube Security sets a WHERE predicate per role (e.g. branch_code = 'KTM' for a role). It's injected into every cube query. Admins see unfiltered data. See Access & Policies.

Troubleshooting

Symptom	Cause	Do
Unknown database / table not found	table uses lakehouse.…, or points at an empty native database	Use <safe>_iceberg.<schema>.<table>
Select database (0) in the wizard	Warehouse grants incomplete	Contact your admin
joins_not_materializable (400)	One-to-many join, or measure-only cube	Pre-join in gold, fix the relationship, or add a dimension
Numbers look inflated	Fan-out from a one-to-many join	Pre-aggregate in gold
Materialized cube numbers are stale	MV not refreshed after new data	Refresh, or add Refresh cubes to the job
403 on cube list	Your page level is View	Ask for Semantic Models: Use
The default example_cube errors	Its sample table doesn't exist on your install	Delete it or point it at a real gold table
First query times out	Cold catalog metadata	Retry