Skip to content
Semantic View Engineering in StarRocks: One YAML Defines Enterprise Business Metrics
Databases 30 min read

Semantic View Engineering in StarRocks: One YAML Defines Enterprise Business Metrics

This article demonstrates how StarRocks Semantic Views codify business metrics, relationships, and filters into version-controlled YAML definitions, enabling consistent SQL generation, table pruning, and aggregation elimination across BI, AI, and ad-hoc queries.

StarRocks
StarRocks
StarRocks

01 Business Metrics Move from Documentation into Execution Path

Enterprises often struggle with inconsistent metric definitions: the same "revenue" may mean pre-discount in reports, post-discount in operations, and include cancelled orders in AI-generated SQL. The root cause is that business definitions never enter the query execution path. StarRocks Semantic View (SV) solves this by storing table relationships, metrics, dimensions, and fixed filters as queryable database objects. Applications query by business names; the engine expands and optimizes the SQL. A retail example threads through CREATE, QUERY, and YAML API to show how business metrics become verifiable results and execution plans.

SV does not materialize data. It supplies semantics at query time; the optimizer and execution engine handle relational operations. Table pruning reduces scanned tables; aggregation elimination uses uniqueness information to remove unnecessary grouping. EXPLAIN INLINE reveals the expanded SQL for review; EXPLAIN shows the final physical plan.

02 CREATE: Defining Business Semantics with YAML

Three Base Tables

The example uses a retail schema in database sv_retail_demo with three tables: fact_order: order_id, order_time, status (PAID/CANCELLED) fact_order_line: order_id, line_no, product_id, quantity, unit_price, discount_rate dim_product: product_id, category

Relationships are many-to-one: order_line → order, order_line → product. Five sample rows (four orders, two products) suffice to verify metric logic. Cancelled order 103 (100 CNY) is excluded by the paid-only filter.

Complete StarRocks Native YAML

The following YAML creates sv_sales_sr:

name: sv_sales_sr
description: 已支付零售销售, 明细行粒度
filter: orders.o_status = 'PAID'
ai_sql_generation: 销售额使用 net_revenue; 订单数另用订单粒度模型。
tables:
  - name: order_lines
    base_table: {database: sv_retail_demo, table: fact_order_line}
    primary_key: {columns: [ol_order_id, ol_line_no]}
    facts:
      - {name: order_id, expr: ol_order_id}
      - {name: line_no, expr: ol_line_no}
      - {name: quantity, expr: ol_quantity}
    metrics:
      - name: gross_revenue
        expr: SUM(ol_quantity * ol_unit_price)
      - name: net_revenue
        expr: SUM(ol_quantity * ol_unit_price * (1-ol_discount_rate))
        synonyms: [销售额, 实收金额]
        description: 已支付订单的折后金额, 单位为元
      - name: discount_rate
        expr: 1-order_lines.net_revenue/order_lines.gross_revenue
  - name: orders
    base_table: {database: sv_retail_demo, table: fact_order}
    primary_key: {columns: [o_order_id]}
    dimensions:
      - {name: order_date, expr: DATE(o_order_time)}
  - name: products
    base_table: {database: sv_retail_demo, table: dim_product}
    primary_key: {columns: [p_product_id]}
    dimensions:
      - name: category
        expr: p_category
        synonyms: [品类, 类目]
        sample_values: [Electronics, Grocery]
relationships:
  - name: line_order
    left_table: order_lines
    right_table: orders
    join_type: LEFT
    relationship_columns:
      - {left_column: ol_order_id, right_column: o_order_id}
  - name: line_product
    left_table: order_lines
    right_table: products
    join_type: LEFT
    relationship_columns:
      - {left_column: ol_product_id, right_column: p_product_id}

The filter hard-codes paid orders only; net_revenue fixes the post-discount formula; discount_rate reuses the two revenue metrics. This file is both a reviewable business definition and a deployable database input.

Equivalent SQL DDL

CREATE SEMANTIC VIEW sv_retail_demo.sv_sales
  TABLES (
    order_lines AS sv_retail_demo.fact_order_line PRIMARY KEY (ol_order_id,ol_line_no),
    orders AS sv_retail_demo.fact_order PRIMARY KEY (o_order_id),
    products AS sv_retail_demo.dim_product PRIMARY KEY (p_product_id)
  )
  RELATIONSHIPS (
    line_order AS order_lines (ol_order_id) LEFT REFERENCES orders (o_order_id),
    line_product AS order_lines (ol_product_id) LEFT REFERENCES products (p_product_id)
  )
  FACTS (
    order_lines.order_id AS ol_order_id,
    order_lines.line_no AS ol_line_no,
    order_lines.quantity AS ol_quantity
  )
  FILTER orders.o_status = 'PAID'
  DIMENSIONS (
    orders.order_date AS DATE(o_order_time),
    products.category AS p_category WITH SYNONYMS = ('品类','类目') SAMPLE_VALUES = ('Electronics','Grocery')
  )
  METRICS (
    order_lines.gross_revenue AS SUM(ol_quantity * ol_unit_price),
    order_lines.net_revenue AS SUM(ol_quantity * ol_unit_price * (1-ol_discount_rate)) WITH SYNONYMS = ('销售额','实收金额') COMMENT = '已支付订单的折后金额, 单位为元',
    order_lines.discount_rate AS 1-order_lines.net_revenue/order_lines.gross_revenue
  )
  COMMENT = '已支付零售销售, 明细行粒度'
  AI_SQL_GENERATION = '销售额使用 net_revenue; 订单数另用订单粒度模型。';

Post-discount amount = quantity × unit_price × (1 − discount_rate); 0.1 means 10% off. Overall discount rate = 1 − total_net / total_gross. Example: one 100 CNY item at 10% off plus two 20 CNY items at full price → 1 − 130/140 ≈ 7.14%. Primary key uniqueness checks return zero rows on clean data.

03 YAML API: Open Interchange for Business Semantics

Native and Open Formats Converge on One Semantic Object

YAML turns business definitions into structured assets that can be stored in Git, reviewed, and consumed by platforms. StarRocks native YAML (SR YAML) targets internal modeling; Open Semantic Interchange (OSI) YAML targets cross-tool exchange. Both can create SVs via API; exported definitions feed external semantic tools or third-party agents. The database still executes formulas, relationships, and fixed filters; callers share the same business definition.

The article uses OSI 0.1.1. When exchanging with other systems, each field and StarRocks extension must be confirmed for support.

Complete OSI YAML for the Same Sales Metric

version: '0.1.1'
semantic_model:
  - name: sv_sales_osi
    description: 已支付零售销售, 明细行粒度
    filter: orders.o_status = 'PAID'
    ai_context:
      ai_sql_generation: 销售额使用 net_revenue; 订单数另用订单粒度模型。
    datasets:
      - name: order_lines
        source: sv_retail_demo.fact_order_line
        primary_key: [ol_order_id, ol_line_no]
        fields:
          - name: order_id
            expression:
              dialects: [{dialect: ANSI_SQL, expression: ol_order_id}]
          - name: line_no
            expression:
              dialects: [{dialect: ANSI_SQL, expression: ol_line_no}]
          - name: quantity
            expression:
              dialects: [{dialect: ANSI_SQL, expression: ol_quantity}]
      - name: orders
        source: sv_retail_demo.fact_order
        primary_key: [o_order_id]
        fields:
          - name: order_date
            dimension: {is_time: false}
            expression:
              dialects: [{dialect: ANSI_SQL, expression: 'DATE(o_order_time)'}]
      - name: products
        source: sv_retail_demo.dim_product
        primary_key: [p_product_id]
        fields:
          - name: category
            dimension: {is_time: false}
            ai_context: {synonyms: [品类, 类目]}
            expression:
              dialects: [{dialect: ANSI_SQL, expression: p_category}]
    relationships:
      - name: line_order
        from: order_lines
        to: orders
        join_type: LEFT
        from_columns: [ol_order_id]
        to_columns: [o_order_id]
      - name: line_product
        from: order_lines
        to: products
        join_type: LEFT
        from_columns: [ol_product_id]
        to_columns: [p_product_id]
    metrics:
      - name: gross_revenue
        ai_context: {table: order_lines}
        expression:
          dialects:
            - {dialect: ANSI_SQL, expression: 'SUM(ol_quantity * ol_unit_price)'}
      - name: net_revenue
        description: 已支付订单的折后金额, 单位为元
        ai_context: {table: order_lines, synonyms: [销售额, 实收金额]}
        expression:
          dialects:
            - {dialect: ANSI_SQL, expression: 'SUM(ol_quantity * ol_unit_price * (1-ol_discount_rate))'}
      - name: discount_rate
        expression:
          dialects:
            - {dialect: ANSI_SQL, expression: '1-order_lines.net_revenue/order_lines.gross_revenue'}
    custom_extensions:
      - vendor_name: STARROCKS
        data: '{"temporary":false}'

Submission uses the same endpoint with type=osi. datasets.source binds base tables; fields declare row-level fields and dimensions; relationships provide join keys; metrics define aggregation expressions; expression.dialects tag SQL dialects; ai_context carries descriptions and synonyms. The filter and STARROCKS extension are interpreted by this implementation; other OSI consumers may not support them.

Import/Export Enables Migration; Query Verification Confirms Metric Parity

Export returns full YAML. To migrate within the same database, rename the model and re-import; to replace, append &replace=true. Cross-database moves require adjusting base table locations. Metadata differences between formats mean both model definition and query results should be validated after import. The article creates three objects — sv_sales (SQL DDL), sv_sales_sr (SR YAML), sv_sales_osi (OSI YAML) — and verifies identical query results in the next chapter.

04 QUERY: From Semantic Query to Expanded SQL

Category-Level Paid Revenue

Business question: "How much net revenue per category?" The query selects category dimension and net_revenue metric; joins, aggregation formula, and paid filter are supplied by the SV.

SELECT category,ROUND(net_revenue,2) AS revenue
FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
  DIMENSIONS products.category
  METRICS order_lines.net_revenue)
ORDER BY category;

Results: Electronics 170 CNY, Grocery 94 CNY. Cancelled order 103 (100 CNY) excluded. Total paid revenue 264 CNY; gross 300 CNY; overall discount 12%.

SR YAML and OSI YAML objects are queried identically and return the same numbers. Date filtering uses WHERE orders.order_date = '2026-08-01' inside the SEMANTIC_VIEW(...) clause (pre-aggregation); HAVING filters post-aggregation. Outer SQL retains standard ordering, joins, subqueries.

SV also supports plain table-name access: columns exposed as logical_table__field. This path groups by all declared dimensions (date + category), yielding three rows. Explicit dimension specification is needed for category-only rollup. Both entry points share metric definitions; query granularity must be confirmed separately.

EXPLAIN INLINE Shows How the Query Reaches Base Tables

EXPLAIN INLINE
SELECT category,ROUND(net_revenue,2) AS revenue
FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
  DIMENSIONS products.category
  METRICS order_lines.net_revenue)
ORDER BY category;

Expanded SQL (indentation cleaned, duplicate qualifiers removed):

SELECT sv_sales.category,
       ROUND(sv_sales.net_revenue,2) AS revenue
FROM (
  SELECT products.p_category AS category,
         SUM(order_lines.ol_quantity * order_lines.ol_unit_price * (1-order_lines.ol_discount_rate)) AS net_revenue
  FROM sv_retail_demo.fact_order_line AS order_lines
  LEFT OUTER JOIN sv_retail_demo.fact_order AS orders
    ON order_lines.ol_order_id = orders.o_order_id
  LEFT OUTER JOIN sv_retail_demo.dim_product AS products
    ON order_lines.ol_product_id = products.p_product_id
  WHERE orders.o_status = 'PAID'
  GROUP BY 1
) AS sv_sales
ORDER BY sv_sales.category ASC;

Field binding: products.category maps to dim_product.p_category; order_lines.net_revenue expands to the SV-defined post-discount sum. The query consumes business field names; the engine resolves underlying expressions.

Joins and filter: Category comes from product table, amount from line table → line→product relationship retained. Paid status lives in order table → global FILTER forces line→order join and adds WHERE orders.o_status='PAID' even if order dimension is not selected. Unmatched lines are excluded by the filter.

Grouping and output: GROUP BY 1 groups by the first inner projection (category). Outer query keeps two-decimal rounding and category ordering. User SQL wrote no JOIN, SUM, or paid filter — all supplied by the semantic definition. EXPLAIN INLINE returns executable SQL for reviewing "how this number is calculated." Physical join implementation, scan nodes, and aggregation operator elimination are checked with regular EXPLAIN; semantic expansion and physical optimization are separate layers.

05 Table Pruning: Query Joins Only Required Tables

One SV describes the full business model; a single query typically uses a subset. Table pruning collects tables referenced in projections, filters, ordering, metric dependencies, and the view-level FILTER, then retains intermediate tables needed to connect them. Tables absent from output columns may still be kept due to metric or join-path requirements.

Enable pruning:

SET enable_semantic_view_table_pruning=true;
EXPLAIN INLINE
SELECT * FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
  METRICS order_lines.net_revenue);

Expanded SQL (pruned):

SELECT sv_sales.net_revenue
FROM (
  SELECT SUM(order_lines.ol_quantity * order_lines.ol_unit_price * (1-order_lines.ol_discount_rate)) AS net_revenue
  FROM sv_retail_demo.fact_order_line AS order_lines
  LEFT OUTER JOIN sv_retail_demo.fact_order AS orders
    ON order_lines.ol_order_id = orders.o_order_id
  WHERE orders.o_status = 'PAID'
) AS sv_sales;

Compared to the category query (which kept all three tables), dim_product and its join are pruned because no product dimension is used. Line table provides the revenue expression; order table provides the fixed filter — both retained. With pruning disabled, the product join reappears. Both plans return 264 CNY; the difference is expansion from three tables to two. Physical scan/join changes are verified via EXPLAIN. Small samples do not prove performance multiples.

Pruning is enabled by default. Using declared semantic fields helps the system locate dependencies; bare physical column references may cause conservative fallback. When using INNER relationships that rely on filtering unmatched rows, check enable_semantic_view_table_pruning_on_inner_join and business intent. This example explicitly uses LEFT relationships with paid condition expressed in the global filter.

06 Aggregation Elimination: Grouping on Unique Keys Becomes Row-Level Calculation

Aggregation elimination exploits another metadata type: when grouping columns cover a complete unique key of the input, each group has at most one row. SUM(quantity) equals reading quantity; COUNT(*) equals 1 — no grouping needed.

In this example the line primary key is (ol_order_id, ol_line_no). Order_id alone is insufficient (order 101 has two lines). The test queries FACTS (row-level fields) with an outer GROUP BY on the full key. FACTS and METRICS / DIMENSIONS are not mixed in the same semantic query.

SET enable_semantic_view_deferred_inline=false;
EXPLAIN
SELECT order_id,line_no,SUM(quantity) AS units,COUNT(*) AS n
FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
  FACTS order_lines.order_id,order_lines.line_no,order_lines.quantity)
GROUP BY order_id,line_no;

Then enable deferred inline and aggregation elimination:

SET enable_semantic_view_deferred_inline=true;
SET enable_eliminate_agg=true;

The first plan contains an AGGREGATE operator; the second should not. Deferred inline keeps the semantic object in the optimizer long enough to read declared keys, prove grouping uniqueness, rewrite aggregation to projection, then expand the semantic scan for normal execution.

Removing EXPLAIN and adding ORDER BY order_id,line_no yields identical results under both settings. Changing the grouping to only order_id (with FACTS selecting order_id and quantity) forces aggregation because order 101 must merge two lines — result: 101/3/2, 102/1/1, 104/3/1. This reverse check distinguishes "eliminate aggregation using full key" from "blindly dropping aggregation."

The rule also requires proving joins preserve the root table's key uniqueness and restricts rewritable aggregation functions. COUNT(quantity) returns 0 on NULL, not constant 1; incomplete grouping keys, unprovable relationships, or unsupported functions retain aggregation. False uniqueness declarations break the premise, so key validation is a pre-production modeling task.

This build defaults enable_semantic_view_deferred_inline off, table pruning on — they are separate switches. After plan inspection, run SET enable_semantic_view_deferred_inline=false; to restore session default. No global settings were changed.

07 Conclusion

A single business semantic definition serves as both query contract and optimization evidence: CREATE fixes granularity, relationships, and metrics; QUERY consumes by business field; YAML API brings definitions into version control and system interchange. For retail sales, "paid only, post-discount" moves from verbal agreement to executable filter and expression; for the database, declared query dependencies and keys enable table pruning and aggregation elimination.

Adoption can start from one verified business domain. First validate base table keys and join cardinalities, then codify high-frequency metrics, building regression cases with expected results, expanded SQL, and physical plans. Order count lives in an order-granularity model; sales amount in a line-granularity model — multiple SVs governed by the same rules, not one giant view.

Future work focuses on reducing modeling and maintenance cost: recommending relationships and metrics from historical queries, AI drafting with business sign-off; integrating data quality checks, definition evolution, and query lineage into one pipeline. On this foundation, business definitions are continuously reused while the database translates them into auditable, executable computation.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

SQLStarRocksYAMLBusiness MetricsOSISemantic ViewAggregation EliminationTable Pruning
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.