“Revenue” can mean sales before discounts in one dashboard and revenue after discounts in another. An AI-generated query may return a third value if it includes canceled orders. The issue is not SQL syntax: the business definition is missing from the query path.
A Semantic View defines table relationships, metrics, dimensions, and mandatory filters as a database object. Applications query business concepts, and the engine expands those requests into SQL over the underlying tables.
This tutorial defines a small retail sales model in SQL and YAML, imports and exports its definitions through the REST API, and then validates query results and execution plans.
Version note: The SQL syntax, YAML formats, API endpoints, and session variables in this tutorial are available in the MirrorShip Enterprise build used here. They are not public StarRocks Community Edition interfaces. Check the supported release or build before running the examples.
1. Put business definitions in the query path
Analytics projects often run into the same three problems. A metric has one name but several formulas. Join paths live in years of copied SQL. Rules such as excluding cancelled orders or test data must be remembered in every new query. Table and column names alone do not give a text-to-SQL system enough context. "Revenue" might mean gross or net sales. "Quantity" might mean units, line items, or orders.
A Semantic View, or SV, moves those decisions into the model. The model owner establishes the grain, keys, table relationships, and metric expressions. The database stores them as a queryable object. At query time, an agent selects dimensions and metrics. The engine supplies the required joins, filters, and aggregations. Synonyms and descriptions help the agent interpret a request. FILTER clauses and SQL expressions enforce the actual calculation.
These two layers have different responsibilities. A prompt that says "revenue includes paid orders only" is guidance for SQL generation. A Semantic View definition such as FILTER orders.o_status = 'PAID' becomes part of every expanded query. A reviewable, reusable business definition can therefore serve dashboards, ad hoc analysis, and agents without relying on each caller to reimplement it.
An SV does not materialize business data. It provides semantics on the query path, after which the optimizer and execution engine process ordinary relational operations. Table pruning removes tables that a query does not need. Aggregation elimination can remove redundant grouping when declared uniqueness proves that every group contains at most one row.
.png)
Figure 1. SQL DDL and both YAML formats create Semantic Views in the catalog. Queries are expanded and optimized before they read the base tables. Use EXPLAIN INLINE to inspect expanded SQL and EXPLAIN to inspect the final physical plan.
2. Define a retail sales Semantic View
2.1 Three base tables for the Semantic View
The example starts with orders, order lines, and products from a retail database. They are reduced to a self-contained database named sv_retail_demo. The DDL and sample data below do not depend on a private database. Run them on a MirrorShip Enterprise build that includes Semantic View and the YAML API.
Base table | One row represents | Business key | Role in the SV |
|---|---|---|---|
| One order |
| Payment status and order date |
| One product line in an order |
| Quantity, unit price, and discount |
| One product |
| Product category |
.png)
Figure 2. Base-table relationships for the sales SV. The arrows show many-to-one relationships from order lines to orders and products. The data must satisfy the business keys shown here. Duplicate Key sort keys do not enforce them.
Each order line joins to an order by order ID and to a product by product ID. Both relationships are many-to-one: an order can contain several lines, and a product can appear in many orders. Run the complete table definitions once on a test cluster that supports Semantic View:
CREATE DATABASE sv_retail_demo;
USE sv_retail_demo;
CREATE TABLE fact_order (
o_order_id BIGINT NOT NULL COMMENT 'Order ID',
o_order_time DATETIME COMMENT 'Order creation time',
o_status VARCHAR(16) COMMENT 'PAID for paid orders, CANCELLED for cancelled orders'
) DUPLICATE KEY(o_order_id)
DISTRIBUTED BY HASH(o_order_id) BUCKETS 1
PROPERTIES ("replication_num"="1");
CREATE TABLE fact_order_line (
ol_order_id BIGINT NOT NULL COMMENT 'Parent order ID',
ol_line_no INT NOT NULL COMMENT 'Line number within the order',
ol_product_id BIGINT NOT NULL COMMENT 'Product ID',
ol_quantity INT COMMENT 'Units sold',
ol_unit_price DECIMAL(18,2) COMMENT 'Pre-discount unit price in CNY',
ol_discount_rate DECIMAL(5,4) COMMENT 'Discount rate; 0.1 means 10 percent off'
) DUPLICATE KEY(ol_order_id,ol_line_no)
DISTRIBUTED BY HASH(ol_order_id) BUCKETS 1
PROPERTIES ("replication_num"="1");
CREATE TABLE dim_product (
p_product_id BIGINT NOT NULL COMMENT 'Product ID',
p_category VARCHAR(32) COMMENT 'Product category'
) DUPLICATE KEY(p_product_id)
DISTRIBUTED BY HASH(p_product_id) BUCKETS 1
PROPERTIES ("replication_num"="1");All three tables use the Duplicate Key model. Its sort keys do not enforce business uniqueness. Primary keys declared in an SV also depend on the underlying data being valid, so key uniqueness and join cardinality must be verified during modeling.
2.2 Load sample data to validate the definitions
INSERT INTO fact_order VALUES
(101,'2026-08-01 10:00:00','PAID'),
(102,'2026-08-01 11:00:00','PAID'),
(103,'2026-08-02 10:00:00','CANCELLED'),
(104,'2026-08-02 11:00:00','PAID');
INSERT INTO dim_product VALUES (1,'Electronics'),(2,'Grocery');
INSERT INTO fact_order_line VALUES
(101,1,1,1,100,0.1000),
(101,2,2,2,20,0.0000),
(102,1,1,1,100,0.2000),
(103,1,1,1,100,0.0000),
(104,1,2,3,20,0.1000);Order 101 has two lines, so the five line records belong to four orders. Cancelled order 103 is worth CNY 100 and will be excluded from the sales definition below. Insert the sample data only once. Duplicate Key tables would otherwise accumulate the repeated rows.
2.3 Define the complete SV in StarRocks-native YAML
In the build covered here, Semantic Views support both SQL DDL and YAML as definition interfaces. YAML can declare base tables, keys, relationships, row-level fields, dimensions, and metrics. It is more than a comment layer on top of SQL. The complete StarRocks-native YAML, or SR YAML, below creates sv_sales_sr. Section 03 shows how to submit it through the API and exchange it through OSI. Do not assume that this interface is part of every StarRocks distribution.
name: sv_sales_sr
description: Paid retail sales at order-line grain
filter: orders.o_status = 'PAID'
ai_sql_generation: Use net_revenue for sales. Use a separate order-grain model for order counts.
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: [sales, net paid revenue]
description: Revenue after discounts for paid orders, in CNY
- 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: [product category, merchandise category]
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 limits every query to paid orders. net_revenue fixes the definition of revenue as the post-discount amount. discount_rate reuses the two revenue metrics to compute the overall discount rate. The file serves as a reviewable business contract and can be submitted directly to the database.
2.4 The equivalent SQL DDL makes the semantic structure explicit
The following DDL creates the same sales model. TABLES binds the base tables and declares business keys. RELATIONSHIPS fixes the join paths. FACTS exposes row-level fields. DIMENSIONS defines grouping attributes, and METRICS defines aggregate calculations.
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 = ('product category','merchandise category')
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 = ('sales','net paid revenue')
COMMENT = 'Revenue after discounts for paid orders, in CNY',
order_lines.discount_rate AS
1-order_lines.net_revenue/order_lines.gross_revenue
)
COMMENT = 'Paid retail sales at order-line grain'
AI_SQL_GENERATION = 'Use net_revenue for sales. Use a separate order-grain model for order counts.';Net revenue is quantity * unit price * (1 - discount rate), measured here in CNY. A discount rate of 0.1 means 10 percent off. The overall discount rate is calculated from total gross and net revenue, not by averaging line-level discount rates. For example, one CNY 100 item at 10 percent off plus two CNY 20 items at full price produces 1-130/140, or about 7.14 percent.
The following check validates the business key on the order-line table. With the sample data, this query returns no rows. Run equivalent checks on o_order_id and p_product_id for orders and products.
SELECT ol_order_id,ol_line_no,COUNT(*) AS n
FROM sv_retail_demo.fact_order_line
GROUP BY ol_order_id,ol_line_no HAVING COUNT(*)>1;3. Create, import, and export Semantic Views through the YAML API
3.1 Native definitions and open exchange use one Semantic View API
YAML is useful for more than avoiding one DDL statement. It turns a business definition into a structured asset that can live in Git, pass through review, and be consumed by platform tooling. SR YAML is the StarRocks-native model format supported by this build. OSI, or Open Semantic Interchange, YAML is intended for exchanging semantic definitions across systems. The Semantic View API accepts both formats and creates a catalog object from either. Exported definitions can also be consumed by compatible semantic tools or third-party agents.
.png)
Figure 3. The same API accepts both YAML formats and creates Semantic Views in the catalog. Solid blue lines represent imports, while dotted pink lines represent exports to internal release workflows or compatible external semantic tools.
YAML connects both ends of the workflow. On the authoring side, it fits into existing modeling and release processes. On the consumption side, agents and BI tools query the Semantic View stored in the database. The database still interprets and executes the revenue formula, relationships, and mandatory filter, so every caller uses the same definition. This tutorial uses the OSI 0.1.1 structure. When exchanging a model with another system, verify each field and every StarRocks extension against what the receiving system supports.
Section 2.3 contains the complete SR YAML payload. Send it as the request body to create sv_sales_sr:
Item | Value |
|---|---|
Method |
|
Path |
|
Authentication | HTTP Basic with the account and password for the target FE |
Content-Type |
|
Request body | The complete SR YAML from Section 2.3 |
The API base URL is the HTTP address of the target FE. The default port is 8030. A successful response contains "code":"0" and "result":"created". The caller should validate both the HTTP status and the response fields code and message.
3.2 A complete OSI YAML model for the same sales definition
The following self-contained OSI definition can be submitted as-is. It does not depend on a prior export or require missing fields to be filled in. It uses the same base tables and creates a model named sv_sales_osi, preserving the paid-order filter, product category dimension, and three metrics.
version: '0.1.1'
semantic_model:
- name: sv_sales_osi
description: Paid retail sales at order-line grain
filter: orders.o_status = 'PAID'
ai_context:
ai_sql_generation: Use net_revenue for sales. Use a separate order-grain model for order counts.
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: [product category, merchandise category]}
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: Revenue after discounts for paid orders, in CNY
ai_context: {table: order_lines, synonyms: [sales, net paid revenue]}
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}'Submit it in the same way as SR YAML, changing the format parameter in the path to type=osi:
POST /api/v2/catalogs/default_catalog/databases/sv_retail_demo/semantic_views?type=osi
Content-Type: text/plainThe request body is the complete OSI YAML above, with the same HTTP Basic authentication. datasets.source binds the base tables. fields declares row-level fields and dimensions. relationships provides the join keys, and metrics defines aggregate expressions. expression.dialects identifies the expression dialect. ai_context carries semantic descriptions and synonyms. The filter and STARROCKS extension are interpreted by this implementation. Their presence does not mean every OSI consumer provides identical execution behavior.
3.3 Import and export definitions, then validate the results
Each relative path below is appended to the collection path from Section 3.1, /api/v2/catalogs/default_catalog/databases/sv_retail_demo/semantic_views. All requests continue to use HTTP Basic authentication.
Operation | HTTP method and path |
|---|---|
Export SR YAML |
|
Export OSI YAML |
|
Create from SR YAML |
|
Create from OSI YAML |
|
An export returns the full YAML document. To migrate within the same database, first change the model name to an unused value and then import it in the corresponding format. To replace the existing object, explicitly append &replace=true. A cross-database migration must also update base-table locations. Auxiliary metadata support can differ between formats, so validate both the imported model definition and its query results.
The SQL DDL, SR YAML, and OSI YAML in this tutorial create sv_sales, sv_sales_sr, and sv_sales_osi, respectively. The next section runs the same query against all three objects and checks their outputs. This makes YAML useful in established definition-management workflows while providing an executable test that the business calculation survived the exchange.
4. Query the Semantic View and inspect the expanded SQL
4.1 Paid net revenue by product category
To answer "How much net revenue did each category generate?", select one dimension and one metric. The query does not repeat the join conditions, aggregation formula, or paid-order filter.
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;category | revenue |
|---|---|
Electronics | 170.00 |
Grocery | 94.00 |
The models created from SR YAML and OSI YAML are queried in exactly the same way. There is no need to repeat the revenue formula:
SELECT category,ROUND(net_revenue,2) AS revenue
FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales_sr
DIMENSIONS products.category
METRICS order_lines.net_revenue)
ORDER BY category;
SELECT category,ROUND(net_revenue,2) AS revenue
FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales_osi
DIMENSIONS products.category
METRICS order_lines.net_revenue)
ORDER BY category;Both queries should return 170 for Electronics and 94 for Grocery, matching the table above.
The Electronics total is 90+80=170. Grocery is 40+54=94. The CNY 100 cancelled order is excluded. Total paid net revenue is CNY 264, gross revenue is CNY 300, and the overall discount rate is 12 percent.
To constrain the date, add WHERE orders.order_date = '2026-08-01' inside SEMANTIC_VIEW(...). Electronics remains 170 and Grocery becomes 40. An inner WHERE filters rows before aggregation, while HAVING filters aggregate results. The outer query can still use ordinary SQL ordering, joins, and subqueries.
An SV can also be queried as a table. Logical columns are exposed as logical table alias + double underscore + field name, which makes the model accessible to tools that issue standard SQL:
SELECT products__category,
ROUND(order_lines__net_revenue,2) AS revenue
FROM sv_retail_demo.sv_sales
ORDER BY products__category;In this aggregate model, the table-reference interface groups by every dimension declared in the model, including date and category. The query therefore returns three rows: Electronics/170, Grocery/40, and Grocery/54. To aggregate by category alone, use the previous query with an explicit dimension list. Both interfaces share the metric definition, but their query grains must be checked separately.
4.2 EXPLAIN INLINE shows how the model reaches the base tables
Run EXPLAIN INLINE on the category-revenue query:
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;.png)
Figure 4. The semantic query references category and revenue. The SV supplies the relationships, calculation, and paid-order filter. EXPLAIN INLINE exposes the intermediate SQL that produces the result.
The expanded SQL is shown below. Only indentation has been cleaned up and repeated database qualifiers inside expressions have been omitted for readability. The tables, columns, subqueries, and computation remain unchanged.
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 post-discount revenue aggregation defined in the SV. The query consumes business field names, and the engine resolves the underlying expressions.
Joins and filters. Category comes from the product table, while revenue comes from order lines, so the order-line-to-product relationship is retained. Payment status comes from the order table. Even though the query does not select an order dimension, the global FILTER still requires a join to orders and adds WHERE orders.o_status='PAID'. That predicate also excludes order lines with no matching order.
Grouping and output. GROUP BY 1 aggregates by the first inner projection, the product category. The outer query applies the requested two-decimal rounding and category sort. The user did not write a JOIN, SUM, or paid-order predicate. Those operations came from the semantic definition.
EXPLAIN INLINE returns ordinary SQL that can be executed, which makes it useful for answering "how was this number calculated?" Use regular EXPLAIN to inspect final join implementations, scan nodes, and whether an aggregate operator was eliminated. Semantic expansion and physical optimization are separate stages.
5. Table pruning: Remove unnecessary tables from the query
A Semantic View can describe a complete business model even though most queries use only part of it. Table pruning traces dependencies from projections, filters, ordering, metrics, the view-level FILTER, and required relationship paths. It then retains any intermediate tables required to connect them. A table can therefore remain in the plan even when none of its columns appear in the output.
.png)
Figure 5. A total-revenue query needs order lines and orders. Adding category also brings in products. Orders supplies the mandatory payment filter, so it cannot be pruned with the product table.
SET enable_semantic_view_table_pruning=true;
EXPLAIN INLINE
SELECT * FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
METRICS order_lines.net_revenue);With pruning enabled, the expanded SQL is as follows. As before, only qualifiers and formatting have been simplified:
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;Unlike the category query in Section 4.2, this query does not use a product dimension. dim_product and its join are pruned, and there is no grouping by category. The order-line table provides the revenue expression, while the order table provides the mandatory filter, so both remain. The category query from Section 4.1 uses the product dimension and therefore keeps all three tables. If pruning is temporarily disabled before expanding the same total-revenue query, the product join reappears:
SET enable_semantic_view_table_pruning=false;
EXPLAIN INLINE
SELECT * FROM SEMANTIC_VIEW(sv_retail_demo.sv_sales
METRICS order_lines.net_revenue);
SET enable_semantic_view_table_pruning=true;In this sample, product keys are unique and all references are valid, so the result remains CNY 264 with pruning either on or off. The difference is that semantic expansion goes from three tables to two. Use EXPLAIN to verify changes in physical scans and joins. This toy dataset is useful for validating semantics, not benchmarking performance.
Table pruning is enabled by default in the build used here. Qualified semantic fields help the system resolve dependencies. A bare physical-column reference that cannot be attributed to a specific table can trigger a conservative fallback. If a model uses an INNER relationship and relies on it to remove unmatched rows, also verify enable_semantic_view_table_pruning_on_inner_join against the intended business behavior. This example uses explicit LEFT relationships, with payment behavior expressed separately in the global filter.
6. Aggregation elimination: Replace redundant grouping with projection
Aggregation elimination uses a different kind of model information. If the grouping columns cover a complete unique key on the input, each group can contain at most one row. SUM(quantity) over that row is just quantity, and COUNT(*) is 1. A grouping operator is unnecessary.
The order-line key in this example is (ol_order_id, ol_line_no). Order ID alone is not sufficient because order 101 has two lines. The query below selects row-level fields through FACTS and groups them in the outer query. Do not mix FACTS with METRICS or DIMENSIONS in the same semantic query. Record the current values of both session variables before the comparison. Keep aggregation elimination enabled for both plans, then change only deferred inlining.
SET enable_eliminate_agg=true;
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;Enable deferred inlining, then run the exact same EXPLAIN again:
SET enable_semantic_view_deferred_inline=true;The first plan should contain AGGREGATE; the second should not. Deferred inlining keeps the semantic object visible to the optimizer long enough to read its declared keys. The optimizer can prove that the grouping is unique, rewrite the aggregate as a projection, and then expand the semantic scan into an ordinary plan.
.png)
Figure 6. Aggregation elimination first proves uniqueness and only then applies an equivalent rewrite. If uniqueness cannot be proved, the aggregation remains. The optimizer does not infer uniqueness from the sample rows.
Remove EXPLAIN and append ORDER BY order_id,line_no. Both settings should return the same result:
order_id | line_no | units | n |
|---|---|---|---|
101 | 1 | 1 | 1 |
101 | 2 | 2 | 1 |
102 | 1 | 1 | 1 |
104 | 1 | 3 | 1 |
Now group only by order ID and change FACTS to select only order ID and quantity. Order 101 must merge two lines, so the aggregation must remain. The result is 101/3/2, 102/1/1, and 104/3/1. This negative test distinguishes a proof based on the complete key from a rewrite that drops aggregation without justification.
The rule must also prove that joins preserve uniqueness of the root-table key, and it applies only to supported aggregate functions. COUNT(quantity) is 0 when quantity is NULL, so it cannot be rewritten to the constant 1. Keep the aggregation when the grouping key is incomplete, relationship properties cannot be proved, or the function is unsupported. Incorrect uniqueness declarations also invalidate the proof, which is why key validation belongs in the production modeling workflow.
In the build used here, enable_semantic_view_deferred_inline is off by default and table pruning is on by default. They are separate settings, and every SET in this tutorial is session-scoped. After inspecting the plan, restore both enable_semantic_view_deferred_inline and enable_eliminate_agg to the values you recorded before the test. This example does not change global settings.
7. From a written definition to an executable contract
One semantic definition serves two roles: a query contract and an optimizer input. CREATE fixes the grain, relationships, and calculations. QUERY consumes business fields. The YAML API brings the definition into version control and system-to-system exchange. In the retail example, "paid orders only, using the post-discount amount" is no longer an instruction that every analyst or agent must remember. It is an executed filter and expression. At the same time, query dependencies and key declarations give the database evidence for table pruning and aggregation elimination.
Start with one well-understood business domain. Validate keys and join cardinalities first. Define the metrics people ask for most often, then build regression cases around expected values, expanded SQL, and physical plans. Model order count at order grain and sales revenue at order-line grain. Govern several focused Semantic Views with the same rules instead of forcing every fact table in the company into one object.
Once the first models are stable, focus on reducing authoring and maintenance costs. Historical queries can suggest relationships and metrics. AI can draft a model, but business owners should approve it. Bring data-quality tests, model evolution, and query lineage into the same workflow. The result is a reusable semantic layer whose calculations remain inspectable and executable.
