Same table, different revenue

Ask two tools for revenue from the same orders table and both can return a different, defensible number. Here is what that looks like in the demo.

All orders

115,550

Completed orders

103,200

The difference is 12,350. Both queries read the same orders and do the arithmetic correctly. What changes is which orders qualify as revenue. In this demo, revenue counts only when an order is completed. Apache Ossie and a Snowflake semantic view make that rule explicit.

That definition is context. It is the contract that tells a person, a dashboard, or an AI agent which reading of the table is allowed to be called revenue.

Context is the contract

A table stores values. Context says what those values mean when someone asks a business question. For a metric, the contract has a few parts:

• the expression, such as a sum or a count
• the rows that qualify, such as a status filter
• the entities it can be sliced by, and how those entities join
• the other metrics it is built from
• a description that a human and a model can both read

Leave any of those out and the column name does the rest of the work. total_amount looks like revenue. It also looks like revenue before discounts, and like revenue that includes orders that never completed. An analyst picks one reading in a worksheet. A dashboard picks another. An agent, given the same column list, picks a third and sounds sure about it.

The sample model shows the distinction with three metrics on the same orders table.

total_revenue sums total_amount where order_status = 'completed'. On this data that is 103,200.

gross_revenue uses the same completed orders and adds discount_amount back. Same orders, different question: value before the discount rather than after it.

active_customers counts distinct customers with an order in completed, shipped, or processing. It is a wider rule than revenue. The two metrics can disagree. In this sample, the four open orders belong to customers who also have a completed order, so the customer count does not move. The next customer who has only a shipped order would show up in one metric and not the other.

average_order_value is not a fresh formula. It is total_revenue / order_count. Because it is built from the completed-order metrics, it keeps their rule. A hand-written average over every row in ORDERS would not.

Joins are context too

Metrics say what to aggregate. Relationships say which path is safe.

An order belongs to one customer. A line item belongs to one order and one product. Those are many-to-one links, and they matter as soon as someone asks for revenue by region or by category.

Region lives on the customer, not on the order. The path is order to customer, which is many orders to one customer. That is a safe way to group order-level revenue. Category lives on the product, and reaching it means going through line items. If you sum orders.total_amount after joining line items, every multi-line order gets counted once per line. The relationship tells the query engine, and an agent, to use line-level revenue for a category cut and order-level revenue for a region cut.

The model keeps those as separate metrics on purpose. total_revenue is completed order amounts. line_revenue and items_sold are sums on line items, and they carry no status filter. If category revenue should also exclude open orders, that rule has to be added to line_revenue itself. Putting it only in one dashboard query means the next consumer will not see it.

Apache Ossie writes the contract once

Apache Ossie (Incubating) is an open specification for a semantic layer. It was known as Open Semantic Interchange (OSI) until the project was renamed to avoid confusion with other projects that share the OSI acronym. The project is incubating at the Apache Software Foundation, which keeps the format open and free of a single controlling vendor.

An Ossie model is a YAML file that a person and a tool can both read. The e-commerce model in this demo names its sources (orders, customers, products, order_items), the relationships between them, the dimensions used to slice results, and the metrics above. total_revenue in that file carries a sum, a completed-order filter, a description, and tags. customer_segment lists the only values it may take: Enterprise, Mid-Market, and SMB. order_month is defined once as DATE_TRUNC('month', order_date), so two dashboards do not invent two truncations.

The project mascot is a kangaroo carrying semantic metadata from one system to the next. The part worth keeping from that image is the carrying. A definition should survive the move from a YAML file to a warehouse, a BI tool, and an agent.

Ossie is the interchange format. It is not the engine that runs the query. Something still has to execute the contract. In this demo, that something is a Snowflake semantic view.

Snowflake runs the same contract

A Snowflake semantic view stores the model next to the tables. Logical tables point at the physical ones. Relationships use primary keys. Dimensions and metrics become objects a query can name. Comments keep the business description on each object.

A filter label is not enough to hold a metric rule. A WHERE clause the caller might forget does not protect the definition. The rule belongs inside the expression:

orders.total_revenue AS SUM(
  IFF(orders.order_status = 'completed', orders.total_amount, NULL)
)
COMMENT = 'Sum of total_amount for completed orders. Shipped, processing, and cancelled orders are excluded.'

average_order_value then refers to that metric, and to order_count, which carries the same predicate:

orders.average_order_value AS
  orders.total_revenue / NULLIF(orders.order_count, 0)

revenue_per_customer divides completed revenue by active customers. Each input keeps its own status rule, so the ratio does not quietly widen revenue to include shipped orders. The disagreement between the two definitions stays visible in the model.

Mapping the Ossie file onto the view is fairly direct:

The query names the metric

Once the view exists, a question becomes a request for a metric and a dimension. Revenue by region does not restate the sum, the status test, or the join:

SELECT *
FROM SEMANTIC_VIEW(
    OSSIE_DEMO.ECOMMERCE.ecommerce_semantic_model
    DIMENSIONS customers.region
    METRICS orders.total_revenue,
            orders.order_count
)
ORDER BY total_revenue DESC;

The view applies total_revenue as it was defined. Another worksheet, a Streamlit app, or Cortex Analyst asks for the same name and gets the same contract. The SQL in the demo app is the request. The meaning stays in ecommerce_semantic_model.

Active customers by month is the query that shows two contracts at once. active_customers includes shipped and processing orders, and total_revenue on the same result does not. One shared WHERE clause would have forced both onto a single rule and hidden the difference.

Agents need this more than another copy of the SQL

A person who knows the business can argue about a metric and settle it. An agent will not. Give it table and column names, and it writes a plausible query: sum the amount, join whatever foreign keys it can see, keep every status. Give it a semantic model, and the legal moves are the metrics, dimensions, and relationships in that model.

That is why this gets urgent as soon as natural-language questions reach the warehouse. An agent is only as consistent as the context it is given. Ossie is a way to write that context so it is not trapped in one product. A Snowflake semantic view is a way to make the warehouse enforce it, with comments and synonyms a model can read and expressions the engine will actually run.

The same YAML can be converted toward other consumers. Define the contract once, then let each engine run it. A semantic layer that exists only inside one tool is just another copy of the problem.

Try the demo

The demo is a small Streamlit in Snowflake app over this e-commerce model.

1. 01_setup.sql creates OSSIE_DEMO.ECOMMERCE and the sample tables.
2. ossie_semantic_model.yaml is the Ossie contract.
3. 02_semantic_view.sql creates ecommerce_semantic_model, with the status rules inside the metrics.
4. The app's live-query tab selects from that view.

Code: github.com/daanalytics/apache-ossie-demo

Apache Ossie is incubating at the Apache Software Foundation. The specification, mailing lists, and community are at ossie.apache.org. Snowflake's write-up of the project is on the Snowflake blog.

The warehouse already knows the amounts. Context is what makes one of those amounts revenue, in every tool that asks.