Implementation scope: This article describes the MirrorData engineering build used for these examples. Base Semantic View support,SEMANTIC_VIEW(),EXPLAIN INLINE, ASOF and effective-dated interval relationships, window metrics, and query-path row and column policies are implemented in that build. This article demonstrates an effective-dated interval relationship and row-policy enforcement.PRIVATEis persisted as a declaration, but the query layer does not yet hide private objects or reject references to them.NON ADDITIVE BYis persisted as metadata, but the query layer does not yet use it to reject invalid aggregation or select a snapshot automatically. These examples document MirrorData behavior and do not define the public StarRocks Community Edition SQL contract.
A weekly operating report records CNY 80 in revenue. The data agent returns CNY 100 for the same order. The SQL can be perfectly valid in both cases.
Two different failures can produce that result. The agent may have selected gross order value instead of net revenue. Or it may have selected net revenue but applied a definition that failed to subtract a refund of CNY 20. The first is a selection error. The second is a definition error.
SQL parsing and schema validation catch neither one. The SQL is valid, every column exists, and the result is still not the number defined by the report.
When a language model generates SQL directly, selection and calculation are mixed into one block of text. We take a different approach. Query generation becomes a compilation pipeline. The model handles the probabilistic step: choosing registered semantic objects that match the user's intent. Its output is a constrained semantic query. All business references are registered semantic objects, and the query contains no physical table names, join clauses, or model-authored aggregate formulas.
Once that query has been emitted, binding, lowering, authorization, and execution are deterministic for fixed inputs. They are reproducible when the semantic revision, engine build, session state, execution principal, policy state, evaluation time, and data snapshot are fixed. Each stage produces an artifact that can be inspected later.
That evidence matters. If someone challenges a number three months later, the team needs to reproduce what the agent calculated at the time, not merely rerun a similar prompt against today's definitions and data.
The responsibilities are split across three components. MirrorMind maintains the semantic catalog and assembles the Context for each request. MirrorPilot talks to the user and emits a semantic query. MirrorData stores Semantic Views, compiles semantic queries, and executes the resulting SQL.
1. Keep probability at the selection boundary
A user asks, "How has revenue been trending in East China recently?"
Gross order value, net revenue, and recognized revenue could all be reasonable interpretations. MirrorMind gives MirrorPilot the metrics, definitions, and reporting conventions available to the current user. That package is the runtime Context.
Context is not a dump of the full catalog. It is a query-scoped projection containing only the objects needed for the request. Those objects must be relevant, visible to the user, and valid for the requested period. The slice records the candidate set available to the model for this request.

Figure 1. Runtime Context narrows the semantic catalog to the smallest query-scoped slice sufficient for the request.
Several candidates remain plausible, so the agent asks for clarification:
Agent: When you say revenue, do you mean gross order value, net revenue after refunds, or recognized revenue? Should the time period and region follow a particular report?
User: Use net revenue as defined in the weekly operating report. Use each customer's current region. Show the latest 16 completed settlement weeks, including zero for weeks with no orders.
The conversation produces a semantic query. It contains no physical table names, join clauses, or model-authored aggregate expressions. Its business references are metrics, dimensions, and filters registered in the Semantic View.
-- Model output: registered objects only, with no calculation logic
SELECT * FROM SEMANTIC_VIEW(
sales.order_semantics
METRICS orders.net_revenue
DIMENSIONS customers.sales_region, orders.settlement_week
WHERE customers.sales_region = 'East China'
AND orders.settlement_week >= DAYS_SUB(DATE_TRUNC('week', CURRENT_DATE()), 112)
AND orders.settlement_week < DATE_TRUNC('week', CURRENT_DATE())
)The model's role in selecting the metric ends here. It can still choose the wrong metric, dimension, filter, time range, literal, or query shape, but those choices are explicit. A reviewer can see whether the query selected orders.net_revenue or another object. The model no longer writes the metric formula or physical join path.
If the catalog has no applicable definition, the correct response is that the current view cannot answer the question. Inventing a plausible column name is not an acceptable fallback.
2. Compile business rules into executable definitions
Selecting net revenue does not guarantee a correct result. Refund timing and customer attribution still matter.
Should a refund reduce revenue in the week when the refund was processed, or in the original order's settlement week? If a customer changes regions, should an order follow the customer's current region or the region that applied when the order settled?
The weekly operating report in this example assigns cumulative refunds to the original settlement week and groups orders by each customer's current region. A request for the "region at settlement time" uses the historical assignment that was valid when the order settled. MirrorData encodes those rules in a Semantic View:
CREATE OR REPLACE SEMANTIC VIEW sales.order_semantics
TABLES (
orders AS sales.fact_orders PRIMARY KEY (order_id)
WITH SYNONYMS = ('order', 'transaction')
COMMENT = 'One row per settled order; paid_amount is not null',
customers AS sales.dim_customer PRIMARY KEY (customer_id)
WITH SYNONYMS = ('customer', 'buyer')
COMMENT = 'One row per customer; region is the current sales region',
customer_history AS sales.dim_customer_history
PRIMARY KEY (customer_id, valid_from)
CONSTRAINT DISTINCT RANGE BETWEEN valid_from AND valid_to EXCLUSIVE
WITH SYNONYMS = ('customer assignment history')
COMMENT = 'Effective interval [valid_from, valid_to); intervals do not overlap for the same customer'
)
RELATIONSHIPS (
order_to_customer AS orders (customer_id)
INNER REFERENCES customers (customer_id),
order_to_region_at_settlement AS orders (customer_id, settled_at)
LEFT REFERENCES customer_history
(customer_id, BETWEEN valid_from AND valid_to EXCLUSIVE)
)
DIMENSIONS (
orders.settlement_week AS DATE_TRUNC('week', orders.settled_at)
WITH SYNONYMS = ('settlement week', 'week')
COMMENT = 'Grouped by settled_at in Asia/Shanghai; weeks start on Monday',
customers.sales_region AS customers.region
WITH SYNONYMS = ('region', 'current region')
COMMENT = 'The customer''s current sales region, as defined by the weekly operating report',
customer_history.region_at_settlement AS customer_history.region
WITH SYNONYMS = ('region at settlement', 'historical region')
COMMENT = 'The customer sales region that was effective when the order settled'
)
METRICS (
orders.net_revenue AS
SUM(orders.paid_amount - COALESCE(orders.refund_amount, 0))
WITH SYNONYMS = ('net revenue', 'net sales')
COMMENT = 'Paid amount less cumulative refunds in the snapshot, attributed to the original settlement week'
)
COMMENT = 'Semantic View for order revenue analysis'
AI_SQL_GENERATION =
'For net revenue, use orders.net_revenue. For settlement week, use orders.settlement_week. Use customers.sales_region by default. Use customer_history.region_at_settlement only when the user asks for the region at settlement. Ask for clarification when the meaning is ambiguous. Query only declared metrics and dimensions.'
AI_QUESTION_CATEGORIZATION =
'Use this view for order net revenue and settlement-week trends. Refund cash flow, recognized revenue, and general-ledger questions are outside its scope.';The clauses fall into two groups.
COMMENT, WITH SYNONYMS, AI_SQL_GENERATION, and AI_QUESTION_CATEGORIZATION help the model select an object. They do not appear in the lowered SQL. A bad hint can cause a selection error, but it cannot silently change the metric expression after the object has been selected.
Registered expressions and relationships drive lowering. Key and range declarations state cardinality assumptions that still require data-quality checks:
- A
METRICSexpression becomes the aggregate expression in the lowered SQL. It defines how refunds and nulls are handled. - A
RELATIONSHIPSentry defines the join graph and can fix the join type.order_to_customeris always anINNERjoin because every order should have a customer.order_to_region_at_settlementis aLEFTjoin so an order remains in the result if historical assignment data is missing. Once the join type is fixed in the definition, a session variable cannot change it. BETWEEN ... EXCLUSIVEdeclares an interval relationship. It lowers too.settled_at >= h.valid_from AND o.settled_at < h.valid_to. The historical region is now a reusable dimension instead of a hand-written temporal join.PRIMARY KEYandDISTINCT RANGEstate the cardinality assumptions behind those joins. A current-region lookup should match exactly one row, and a historical lookup should match at most one row. These are declarations. The engine does not scan the source tables to prove them, so the data checks in Section 5 remain necessary.
Figure 1A follows one order through the definition. The customer paid CNY 100 and has a cumulative refund of CNY 20 in the selected snapshot. The order contributes CNY 80 to Week 2 and to the customer's current region, East China.

Figure 1A. How one order is attributed under the weekly reporting definition.
Subsequent queries reuse the same definition. A South China query changes only the filter value. A query grouped by the region at settlement changes only the selected dimension.
3. Deterministic execution for fixed inputs
After the model submits the semantic query, the engine takes over. The left side of Figure 3 is probabilistic intent mapping. From SEMANTIC_VIEW() onward, this build applies fixed compilation and execution rules to fixed inputs.

Figure 3. The boundary between probabilistic selection and deterministic execution for fixed inputs.
3.1 Binding
The binder resolves every name to an object declared in the Semantic View. If any name cannot be resolved, the query fails before execution.
Generated SQL can refer to a real column and still calculate the wrong business concept. A semantic query cannot bypass the registered object layer. Binding rejects unregistered names, but it does not prove that the chosen object, filter, time range, or business definition is correct.
3.2 Lowering and inlining
The engine lowers SEMANTIC_VIEW() into SQL over the underlying tables. Metric expressions become aggregate columns, dimension expressions become grouping keys, and relationships become join predicates.
EXPLAIN INLINE exposes the output of this stage. This build can emit executable expanded SQL with quoted identifiers. The following example shows an illustrative expansion reconstructed from the documented behavior, not captured output:
-- Illustrative EXPLAIN INLINE shape, reconstructed from documented behavior
SELECT `order_semantics`.`sales_region`,
`order_semantics`.`settlement_week`,
`order_semantics`.`net_revenue`
FROM (
SELECT `customers`.`region` AS `sales_region`,
DATE_TRUNC('week', `orders`.`settled_at`) AS `settlement_week`,
SUM(`orders`.`paid_amount` - COALESCE(`orders`.`refund_amount`, 0)) AS `net_revenue`
FROM `sales`.`fact_orders` AS `orders`
INNER JOIN `sales`.`dim_customer` AS `customers`
ON `orders`.`customer_id` = `customers`.`customer_id`
WHERE `customers`.`region` = 'East China'
AND DATE_TRUNC('week', `orders`.`settled_at`) >= DAYS_SUB(DATE_TRUNC('week', CURRENT_DATE()), 112)
AND DATE_TRUNC('week', `orders`.`settled_at`) < DATE_TRUNC('week', CURRENT_DATE())
GROUP BY 1, 2
) `order_semantics`;A reviewer can inspect four things in this SQL.
First, the aggregate expression subtracts refunds. This catches the missing CNY 20 in the opening example. Second, the grouping key uses settled_at rather than created_at, which prevents an order from landing in the wrong week. Third, the join type and predicate match the declared relationship, which helps explain duplicate or missing rows. Fourth, customer_history is absent because the query did not request a historical-region dimension.
The enable_semantic_view_table_pruning setting removes unused tables. Under the declared cardinality assumptions, pruning this unused LEFT JOIN preserves the result. If the source data violates those assumptions, the join may still multiply rows. An INNER JOIN can remove rows, so pruning for inner joins has a separate control: enable_semantic_view_table_pruning_on_inner_join.
For a fixed engine build and catalog state, lowering can be treated as a function of the semantic query, the exact Semantic View revision, and relevant session variables. Those variables include enable_semantic_view_inner_join for relationships without an explicit join type, the table-pruning settings, and time_zone. Both relationships in this example declare their join types, so enable_semantic_view_inner_join has no effect here.
The inline REST endpoint can return the same lowered SQL. A production workflow can store it with the delivered result.
3.3 Authorization
Suppose a regional manager may see East China but not South China. Editing the semantic query must not bypass that restriction.
Context limits what the model sees. The PRIVATE declaration records visibility intent, but it is not an enforcement boundary. In the current implementation, the declaration is persisted as metadata; the query path does not yet hide or reject a reference because it is marked PRIVATE. Access control must be attached to the execution path.
Row access policies can be attached to base tables or to columns exposed by the Semantic View. Both apply when a query runs through SEMANTIC_VIEW():
-- Illustrative policy on the region column exposed by the Semantic View
CREATE ROW ACCESS POLICY region_scope AS (r STRING) RETURNS BOOLEAN
-> is_role_in_session('hq_analyst')
OR (is_role_in_session('east_manager') AND r = 'East China');
ALTER TABLE sales.order_semantics
ADD ROW ACCESS POLICY region_scope ON (customers__sales_region);
Figure 2. Constraints enforced in the query path versus declarations stored as metadata.
In the build covered here, the engine partitions result-cache entries by user and role and revalidates privileges on a cache hit. Identity propagation and caches outside the engine remain separate boundaries. Test those paths end to end with different accounts.
3.4 Optimization and execution
The cost-based optimizer (CBO) chooses join order and physical operators. Those choices affect latency, not the business meaning of the query.
Semantic replay does not usually require the original physical plan. Exact reconstruction still needs the lowered SQL, Semantic View revision, engine build, session state, authorization context, policy versions, evaluation time, and data snapshot.
4. Last-mile shaping and replay capture
The engine returns only settlement weeks that have orders. The user asked for 16 consecutive completed weeks and a zero for any week without orders.
MirrorPilot handles that presentation requirement in a delivery-layer wrapper query. It generates a date spine containing 16 Mondays, left joins the semantic-query result to that spine, and replaces missing values with zero.
Zero filling should not be part of the metric definition because every query that reuses the metric would inherit it. The wrapper is also outside the Semantic View compiler. The engine will reject a misspelled metric during binding, but it will not complain if the wrapper returns 15 rows instead of 16. The delivery workflow must check the row count and week continuity.
A displayed zero has two prerequisites. The caller must be authorized to see the region, and all relevant order and refund data must be available. An unauthorized region should return an access error. Incomplete data should be marked as pending. Neither case should be displayed as zero.
The second step is archival. Most replay evidence is available at delivery time and may be difficult to recover later. Session variables change. A CREATE OR REPLACE operation replaces the active view. Snapshots roll forward, and access policies evolve.
For each delivered answer, store:
- the semantic query, the runtime Context slice, and the clarification record;
- the Semantic View revision, since
CREATE OR REPLACEdoes not preserve prior definitions in the engine; - the exact lowered SQL, the engine build, and relevant session variables;
- optionally, a canonicalized SQL or AST fingerprint for comparison across formatting changes;
- the execution timestamp and resolved date bounds, especially when the query contains
CURRENT_DATE(); - an immutable retained snapshot, or a source-version reference with guaranteed retention, together with its snapshot ID;
- the user, active roles, and effective policy versions;
- the delivery-layer wrapper SQL;
- any component queries and results used to support an explanation.
The semantic catalog should retain revision history for view definitions. Reconstructing these artifacts after a dispute only reruns today's system. It does not establish how the original answer was produced.
5. Replaying an answer three months later
Finance asks why the weekly report shows CNY 80 for the week of December 22, 2025, while the agent returned CNY 100.
The review starts with the stored semantic query. It selected orders.net_revenue, so the metric selection was correct. The region, dimensions, and time predicates still need to match the clarification record.
Next, restore the stored Semantic View revision in an isolated replay environment, or select it through a revision-addressable catalog path. Rerun EXPLAIN INLINE with the recorded engine build and session variables. Use the recorded execution time or literalized date bounds so CURRENT_DATE() does not shift the window. Compare the raw expanded SQL and, when available, a canonicalized SQL or AST fingerprint with the retained artifacts. A difference points to the view revision, session state, engine build, or lowering path. The investigation can stop at that layer.
If the SQL matches, run it against the recorded data snapshot. It returns CNY 100. Run the same SQL against the snapshot used to publish the weekly report, and it returns CNY 80. The refund of CNY 20 arrived after the agent delivered its answer. Both results are correct for their respective snapshots. The operational fix is to use the same snapshot for reconciliation and include the snapshot ID with the answer.
Other discrepancies follow the same evidence path. If the total is correct but shifted by a week, inspect whether the grouping expression uses settled_at or created_at and whether time_zone matches. If one week is missing from the trend, inspect the date spine in the delivery wrapper.
There is another failure mode. The definition and lowered SQL are unchanged, but an order worth CNY 80 contributes CNY 160. In this case, the source data violates an assumption declared by the Semantic View. PRIMARY KEY says one customer_id maps to one current customer record, but the table contains two matches. The inner join duplicates the order.
A cardinality check makes the violation visible:
-- COUNT(c.customer_id) counts matched customer rows.
-- COUNT(*) would count an unmatched order as one row.
SELECT o.order_id, o.customer_id, COUNT(c.customer_id) AS customer_matches
FROM sales.fact_orders AS o
LEFT JOIN sales.dim_customer AS c ON o.customer_id = c.customer_id
GROUP BY o.order_id, o.customer_id
HAVING COUNT(c.customer_id) <> 1;Any count above 1 indicates duplicate attribution. A count of 0 means the INNER JOIN would drop the order. After the duplicate customer record is corrected, the result returns to CNY 80.
For customer_history, use the effective-dated interval predicate and flag counts above 1 because they indicate overlapping intervals. Treat a count of 0 separately if complete historical assignment is a business requirement. These checks validate the assumptions behind a Semantic View and should run on a schedule, not only after someone reports a discrepancy.
Metric explanations require the same discipline. If the agent says, "Revenue fell because refunds increased," replay component queries for paid amount and cumulative refunds with the same region, settlement weeks, and snapshot. Within this metric definition, the observed decline is attributable to higher recorded refunds only if paid amount is unchanged and refunds are CNY 20 higher in the later week. Storing the component queries and results makes that attribution reproducible.
6. The artifact produced at each layer
Layer | Inspectable artifact | Failure mode | How to check it | Evidence to retain |
|---|---|---|---|---|
Selection (model) | Semantic query text | Wrong object, dimension, filter, time range, literal, or query shape | Review object names, predicates, and clarification record | Context slice, clarification record, semantic query |
Definition (Semantic View) | Metrics, dimensions, relationships, join types | Incorrect expression or relationship | Compare with lowered SQL | Semantic View revision |
Binding | Success or error | Name cannot be resolved | Engine error | None |
Lowering | Executable base-table SQL | Engine build or session state changes the expansion | Compare | Lowered SQL, engine build, session variables, evaluation time |
Authorization | Filtered row set | Policy is not attached to the execution path | Execute with a different identity | User, roles, policy versions |
Delivery wrapper (MirrorPilot) | 16 consecutive weekly rows | Missing weeks, wrong bounds, or incorrect zero filling | Row-count and continuity checks | Wrapper SQL |
Data | Source tables that satisfy declared assumptions | Late refund, duplicate match, missing match | Replay the same retained snapshot; run match-count checks | Immutable snapshot or guaranteed source-version reference |
A Semantic View localizes failures to inspectable layers. Selection, definition, compilation, authorization, presentation, and data state can be reviewed independently, and each answer retains enough evidence for attribution and replay.
