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.
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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
