"Last Mile" — Serving Data: Semantic Layers, BI and Data APIs

Skim Card

One thing to remember: if two dashboards can compute “net revenue” differently, you do not have a metric, you have two opinions; define it once and make every tool ask for it.

• Same tables, same rights, four ad-hoc revenue answers; Cube’s definition gave £29,790,674.06 and matched dbt’s report on all 378 country-days.
• Define a ratio as a ratio of counts and never sum a balance across days: Q3 is 88.5% asked whole or rolled up from days.
• Filter rows in the governed engine: each token scope maps to its own Trino identity, so OPA returns GB’s sessions only.
• Give each identity its own query orchestrator: with Cube’s default, a GB token received all three countries’ sessions.
• Pin the query time zone on the server: a New York client turned UK Q3 revenue into £29,795,134.26.
• Cached answers took 5–22 ms against 4–10 s cold; Trino’s default refresh key re-checks a cached result every two minutes.

Use …	When …
Cube Core	You need REST and SQL serving over your own engine, open source
MetricFlow (dbt Semantic Layer)	You live in dbt; serving needs the dbt platform
Platform-native semantics (Snowflake semantic views, Databricks metric views, Power BI, LookML)	One platform owns every consumer
A governed SQL view	One tool, one grain, nobody slices it

Verified against: Cube Core 1.8.15, Apache Superset 6.1.0, Trino 484, Open Policy Agent 1.21.4, Apache Polaris 1.8.0, psycopg2-binary 2.9.9 · vendor docs · 30 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Farah’s question from Part 30 sat on the board: who decides what “availability” means in each tool? Rohan had an answer ready. “A metrics service. Three handlers, three SQL queries, one repository. Everyone calls the endpoint.”

“Then stores want availability by week, marketing wants conversion by platform, Superset wants everything by country,” Ingrid said. “Three handlers become thirty, and someone writes the thirty-first in a notebook. Every number in the incident had a correct query somewhere. Define each number once, so any slice of it follows the same rules.”

The Quick Map

Concepts, with use cases

Of Part 2’s five promises, this part strengthens consistency: one definition per number, whichever tool, slice or person asks. It closes the conversion thread of the incident that Part 6 explained and Part 20 settled by contract.

A metric is a definition, not a query

A metric is a named aggregation together with the rules that make it one number: the grain it counts, what is excluded, which date it belongs to, which currency, and for whom. A query is one use of it. Write the rules into each query and every copy is a chance to forget one.

What breaks without it. The incident. Every team had a correct query for its own slice; the board saw the others. Larkspur: Step 1 runs the incident’s old mistakes on today’s clean gold tables and still gets four revenue figures and three stock figures. Elsewhere: an airline’s “on-time departure” (within 15 minutes of schedule? pushback or wheels-up?) differs between the operations screen and the regulator’s filing.

The semantic layer

A semantic layer (or metrics layer) is a model of your data’s entities, joins, dimensions and measures that compiles each request, “this measure, by these dimensions, with these filters”, into SQL for the engine underneath, and serves the result through APIs. The data stays in the lake; the rules live in one versioned model.

What breaks without it. The definition is copied into each BI tool, notebook and service, and drifts. Larkspur: Cube Core defines the three incident numbers over the dbt gold tables, read through Trino. Elsewhere: a bank defines “net interest margin” once for the regulator and for its branch managers’ app.

The open-source choices differ in what they serve. Cube Core (Apache 2.0) is a server with a REST API and a PostgreSQL-wire SQL API. MetricFlow (Apache 2.0) compiles definitions with its local mf CLI; the dbt Semantic Layer APIs that serve it (JDBC, GraphQL) need the dbt platform, so alone it can’t back a BI tool or an endpoint (not run here). Warehouses ship their own: Snowflake semantic views, Databricks Unity Catalog metric views (no preview label on its docs pages, 30 October 2026), Power BI semantic models, Looker’s LookML. Apache Ossie (incubating), formerly Open Semantic Interchange, is a pre-1.0 spec for moving definitions between them (its converters were not run here).

Measures that survive slicing

A ratio metric must be a ratio of two additive measures, recomputed at every grain; an average of daily rates weights a quiet Sunday like a sale day. A semi-additive measure, such as stock on hand (Part 4), adds across stores but never across days.

What breaks without it. The stores dashboard summed a week of snapshots and called any positive sum “in stock”: 97.2%. Larkspur: availability is in-stock store-SKU-days over all store-SKU-days, two counts, and the model offers no sum of units. Cube has no semi-additive measure type (checked in its 1.8.15 schema validator); MetricFlow has non_additive_dimension. Elsewhere: a fintech’s account balances must be taken at month end, never summed over the month.

Data products: an interface with an owner

A metric that other teams build on is a data product: a published interface (here, a Cube view: the measures and the dimensions you may slice by, nothing else), an owner, a contract (Part 20) and a freshness promise someone monitors (Part 22).

What breaks without it. Someone “fixes” a definition and forty dashboards move overnight. Larkspur: three views, each with an owner and a promise (freshness, or the contract it follows) in its meta, returned by /v1/meta to any client. Elsewhere: a parcel carrier publishes “delivered on time” to retailers under an SLA.

Serving paths: BI, data APIs, embedded analytics, reverse ETL

• BI over SQL: a layer that looks like a database serves an unmodified BI tool.
• Data API: backends call REST or GraphQL with a signed token whose claims decide what the caller sees.
• Embedded analytics: the same API inside your product, for many tenants at once (the Design Drill).
• Reverse ETL: pushing metrics into operational tools with a product or a scheduled job (Part 19).

What breaks without one model behind them. Every path re-implements filters and row security. Larkspur: Superset for head office, REST for the country apps; a nightly reverse-ETL job could push each store’s availability to the supplier portal as idempotent upserts keyed by store, SKU and date. Elsewhere: a SaaS company shows each customer its usage and pushes the same figure into billing.

Caching

The layer caches each query’s result under a refresh key: a value (a timestamp bucket, a table’s last update) whose change marks the result stale. Bigger savings come from pre-aggregations, rollup tables the layer builds and matches queries to; in Cube they need Cube Store as a separate service (not run here).

What breaks without it. The 9 a.m. rush sends the engine the same hundred queries. Larkspur: gold changes once a day, so minutes of staleness cost nothing. Elsewhere: an ad platform serves the same campaign totals to thousands of advertisers.

Under the Hood

From a request to SQL

A request names a view’s members. Cube resolves them to cubes, finds the join path (revenue → stores, revenue → business_days, both many-to-one), inlines each measure’s SQL into an aggregate, turns filters into a WHERE with bound parameters, and adds a limit. part31_serve sql prints the result for UK Q3 revenue (via /v1/sql): the cube’s SQL as a subquery, LEFT JOINs to dim_store and dim_date, the country and date range as ? parameters, LIMIT 10000.

The orchestrator, the cache and the clock

Cube keeps a query orchestrator per orchestrator ID: the database connections (driverFactory runs “once per data_source for every orchestrator id”) and the result cache. The app ID keys only the compiled model (“on its own, it leaves querying and caching shared across app ids”), and by default one orchestrator serves every tenant (quotes from Cube’s configuration reference).

Before serving a cached result, Cube evaluates the refresh key. Trino’s dialect defaults to every: '2 minutes', run as SELECT FLOOR((to_unixtime(NOW())) / 120); the key’s value is itself cached and, as observed in Step 5, renewed at most every 120 s, with the query re-run in the background.

Cube’s Trino driver uses the presto-client package, whose fetchNextWithTimeout waits 800 ms (QUERY_STATE_CHECK_INTERVAL) before every fetch of the next page, so even a statement that reads nothing, the refresh-key check, averaged 2.1 s in Step 5. polls ran the conversion query both ways: 1.7–2.4 s through Cube, 1.5 s from a client that fetches at once. A cold answer pays for two statements, the key and the query.

The SQL API is PostgreSQL on the outside

The SQL API answers the PostgreSQL wire protocol (SELECT version() says PostgreSQL 14.2 on x86_64-cubesql) and shows each public view as a table. A measure is asked for with MEASURE(net_revenue) or with the aggregate its type implies; for a number measure such as availability, SUM(availability) still returns the ratio, so a BI tool’s default aggregate can’t sum a percentage. Behind it, each statement becomes the same query object the REST API builds, where it can.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-31). make up-serve starts core, Trino with OPA (Part 23), Cube (REST :4000, SQL API :15432, 1 GB, no Cube Store) and Superset (:8088, 1.5 GB, metadata in the lab PostgreSQL). It refuses to start while Spark, ClickHouse, Kafka, Connect or Flink run (captured with Kafka up). From a core-only lab it took 39 s; containers peaked at 3.87 GiB over the sequence (Trino 2.42, Cube 0.39, Superset 0.20) and 5.59 GiB during make ci.

make up-serve
uv run python -m scripts.part31_serve cache       # first, while Cube's cache is cold
uv run python -m scripts.part31_serve opinions
uv run python -m scripts.part31_serve metrics
uv run python -m scripts.part31_serve check
uv run python -m scripts.part31_serve scopes
uv run python -m scripts.part31_serve timezone
uv run python -m scripts.part31_serve superset
uv run python -m scripts.part31_serve superset-sql
uv run python -m scripts.part31_serve polls

Step 1: four answers from ad-hoc SQL

sql/part31/two_opinions.sql asks each question the right way and then the way the incident’s teams did, as analyst_gb through the governed catalog: the same tables and rights Cube has.

-- revenue, the definition: baskets once, refunds on their own date, the business date's rate, local dates
    29790674.06
-- revenue, refunds dated by the purchase (Part 4's mistake)
    29832649.44
-- revenue, every euro at today's rate (Part 5's mistake): the latest rate in the table
    29781878.63
-- revenue, the UTC date instead of the store's business date (Part 5's other mistake)
    29790375.96
-- stock, the definition: store-SKU-days in stock at the close of the business day
    88.5
-- stock, the snapshot as reported, some stores counting after the morning delivery (Part 5's mistake)
    91.1
-- stock, units summed over each ISO week, any stock that week counts (the old dashboard, Part 4's mistake)
    97.2
-- conversion, the definition: the sessions contract (Part 20)
    929032  57486  6.19
-- conversion, marketing's old rule: one session per visitor per UTC day, bots in (as lab)
    2525739  134111  5.31

Each revenue mistake alone moves the quarter by £41,975.38, −£8,795.43 or −£298.10 (near, not equal to, Part 21’s waterfall steps, measured one after another). Duplicated till uploads can’t even be written here: an analyst can’t read the raw zone, and gold holds each basket once. And the correct conversion SQL returns 6.19% to this analyst, because OPA keeps GB’s rows only: the scope is part of the answer. Marketing’s 5.31% needs raw events, so it ran as a platform identity.

Step 2: the definitions, once

The model is YAML in cube/model/. Net revenue is one cube over money movements, with every rule the incident taught in its SQL:

cubes:
  - name: revenue
    public: false   # consumers read the net_revenue view, not the cube
    sql: >
      WITH movements AS (
        SELECT 'sale' AS kind, transaction_id AS id, date_key, store_key, profile_key, currency, amount_minor
        FROM gold.fct_basket
        UNION ALL
        SELECT 'refund', refund_id, refund_date_key, store_key, NULL, currency, -refund_minor
        FROM gold.fct_refund
      )
      SELECT m.kind, m.kind || ':' || m.id AS movement_id, m.date_key, m.store_key, m.profile_key,
             m.currency, m.amount_minor,
             CAST(sign(m.amount_minor) * round(abs(m.amount_minor) * fx.rate) AS bigint) AS amount_gbp_minor
      FROM movements m
      JOIN gold.dim_date d ON d.date_key = m.date_key
      JOIN gold.dim_fx_rate fx
        ON fx."date" = d."date" AND fx.base_currency = m.currency AND fx.quote_currency = 'GBP'
    measures:
      - name: net_revenue
        sql: "CAST({CUBE}.amount_gbp_minor AS DECIMAL(18, 0)) * 0.01"
        type: sum
        format: currency

Gold has no row-level table of money movements in GBP, so the lab derives one in the cube’s SQL. In production you would build one dbt model (say fct_money_movement) that both Cube and the report read, and check (Step 3) becomes a guard rather than the glue: row rules belong in tested dbt models; the semantic layer owns aggregation, grain and slicing.

Many-to-one joins (cut from the excerpt) reach the conformed stores and business_days cubes (Part 4), shared with the stock cube. Stock availability is 1e0 * {in_stock_at_close} / NULLIF({store_sku_days}, 0) with type: number, both inputs counts (closing stock above zero, Part 5), and no measure that sums units. Conversion is {converted_sessions} / {sessions} over gold.web_sessions, the sessions the contract defines. The consumer-facing interface is three views:

views:
  - name: net_revenue
    description: UK, India and Singapore net revenue in GBP by local business date. Finance's number.
    meta: {owner: finance data team, built_from: dbt gold (daily), freshness: yesterday by 07:00 London, reconciled: Part 21}
    cubes:
      - join_path: revenue
        includes: [net_revenue, sales, refunds, baskets, kind]
      - join_path: revenue.stores
        includes: [country, store_id, city, format]
      - join_path: revenue.business_days
        includes: [business_date, quarter, month]

Step 3: serve them over REST

Cube connects to Trino as cube, a new analyst-class identity in opa/larkspur/users/data.json (read-only, governed catalog, identifiers hashed, rows of GB, IN and SG); its settings are environment variables in compose.yaml. The app’s backend signs an HS256 token with the API secret, which must never reach a browser, and posts a query:

TOKEN=$(uv run python -m scripts.part31_serve token --scope all)
curl -s -H "Authorization: $TOKEN" -H 'Content-Type: application/json' \
  localhost:4000/cubejs-api/v1/load -d @cube/queries/uk_net_revenue_q3.json | jq '{data, lastRefreshTime, dbType}'

{
  "data": [
    {
      "net_revenue.country": "GB",
      "net_revenue.net_revenue": "29790674.06",
      "net_revenue.refunds": "-651557.84"
    }
  ],
  "lastRefreshTime": "2026-10-30T10:02:39.692Z",
  "dbType": "trino"
}

The money arrives as a string, decimal intact. check compares all three numbers with the board pack Farah signed in Part 25, and every country-day of Cube’s net revenue with dbt’s gold.rpt_daily_net_revenue:

pass  UK net revenue Q3 = board pack                       29,790,674.06
pass  stock availability at close = board pack             88.5%
pass  conversion = board pack (sessions contract)          134,223 / 2,161,515 = 6.21%
pass  every country-day = dbt's gold.rpt_daily_net_revenue 378 country-days; differing: 0 / 0

Parts 17, 21 and 25’s figures, to the penny and the session (Part 25’s count: one session fewer than Part 20’s, after Part 23’s erasure); metrics prints them with their counts.

Step 4: one identity per scope, and two safeguards

A country app’s token carries scope: GB. cube/cube.js maps each scope to a Trino user, cube for all, analyst_gb for GB, and rejects any other:

module.exports = {
  contextToAppId: ({ securityContext }) => `larkspur_${trinoUser(securityContext)}`,
  contextToOrchestratorId: ({ securityContext }) => `trino_${trinoUser(securityContext)}`,
  driverFactory: ({ securityContext }) => ({ type: 'trino', user: trinoUser(securityContext) }),
  queryRewrite: (query) => ({ ...query, timezone: 'UTC' }),
};

scopes asks each scope for conversion by country, then lists the Trino users that ran Cube’s queries:

scope all -> GB 929,032 sessions, 57,486 converted; IN 930,165 sessions, 57,679 converted; SG 302,318 sessions, 19,058 converted
scope GB  -> GB 929,032 sessions, 57,486 converted
scope IN  -> IN 930,165 sessions, 57,679 converted
scope SG  -> SG 302,318 sessions, 19,058 converted
scope FR  -> Cube (FR): Error: unknown scope: FR
Trino users that ran Cube's queries: analyst_gb (3), analyst_in (2), analyst_sg (2), cube (2)

(Two revenue lines trimmed: both scopes get all three countries, because Part 23 scopes gold.web_sessions by country but not aggregate revenue; Cube holds no row rule of its own.) A lab-only switch, LAB_MISTAKES, removes the two safeguards to show what each prevents:

LAB_MISTAKES=shared-orchestrator,client-timezone docker compose --profile core --profile batch --profile serve up -d --wait --no-deps cube
uv run python -m scripts.part31_serve scopes && uv run python -m scripts.part31_serve timezone
docker compose --profile core --profile batch --profile serve up -d --wait --no-deps cube   # safeguards back

scope GB  -> GB 929,032 sessions, 57,486 converted; IN 930,165 sessions, 57,679 converted; SG 302,318 sessions, 19,058 converted
Trino users that ran Cube's queries: cube (3)
timezone UTC (default)     UK net revenue Q3 GBP    29,790,674.06
timezone Europe/London     UK net revenue Q3 GBP    29,790,674.06
timezone America/New_York  UK net revenue Q3 GBP    29,795,134.26

(Trimmed to the lines that changed.) With one orchestrator, the GB token got all three countries: its query compiled to the same SQL as the all query and was served that cached result; a new query would have run on the first identity’s connection. OPA never saw analyst_gb. With the client’s zone, Cube converts the DATE business date into New York time: every date moves back a day, so Q3 loses 1 July and gains 1 October.

Step 5: the cache, cold and warm

Run first after make up-serve, cache asks each metric cold, warm, again after the two-minute refresh key has moved on, 10 s later, and with cache: no-cache:

query                     cold    warm  key expired  10 s later  no-cache  last refresh (UTC)
UK net revenue Q3       10.10s  0.010s       0.022s      0.020s     3.28s  10:00:03, 10:00:03, 10:02:06
stock availability Q3    4.12s  0.006s       0.010s      0.010s     3.27s  10:00:08, 10:00:08, 10:02:06
conversion               4.07s  0.005s       0.005s      0.566s     3.24s  10:00:12, 10:00:12, 10:02:09
Trino statements from Cube, metric queries    : 9, 2.20 s each on average (created to end)
Trino statements from Cube, refresh-key checks: 2, 2.10 s each on average (created to end)
Continue waits: 0

• Cold: about 4 s, two statements (Under the Hood); the very first request after start-up took 10.1 s.
• Key expired: still served from the cache while the queries re-ran in the background; the last-refresh times moved to 10:02 by the next ask (one took 0.57 s). Every response carries lastRefreshTime.
• No Continue wait: Cube answers {"error": "Continue wait"} (HTTP 200) when a query outlasts continueWaitTimeout (10 s by default), and clients must retry; part31_serve does.

Step 6: Superset, through the SQL API

Superset “does not ship bundled with connectivity to databases”. superset/Dockerfile adds psycopg2-binary==2.9.9 (the pin of Superset’s own postgres extra), which serves both its metadata database and Cube’s SQL API. superset creates everything through Superset’s REST API (login, CSRF token, POST /api/v1/database/, /dataset/, /chart/) and reads each chart back with POST /api/v1/chart/data; nothing depends on clicking:

database 1: Larkspur metrics (Cube) -> postgresql://cube:15432 (Cube's SQL API)
chart 1 'UK net revenue, Q3 2026 (GBP)' (dataset 1 = view net_revenue): 29790674.06
chart 2 'Stock availability at close, Q3 2026' (dataset 2 = view stock_availability): 0.8850367837900729
chart 3 'Conversion (sessions contract)' (dataset 3 = view conversion): 0.06209672382565006

Each dataset is a view and each chart’s metric is MEASURE(…), so Superset holds no definition of its own. The SQL it sent (superset-sql):

SELECT MEASURE(net_revenue) AS net_revenue
FROM public.net_revenue
WHERE country = 'GB' AND business_date >= TO_TIMESTAMP('2026-07-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
  AND business_date < TO_TIMESTAMP('2026-10-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
 LIMIT 10;

Superset’s result caches stay at their default, NullCache: the one cache is Cube’s. tests/test_part31.py asserts the headline figures (8 passed in 59 s).

The Landscape

Current product names as of 30 October 2026. A cell that couldn’t be verified says so.

Tips & Gotchas

Gotcha — the SQL API returns money as float8. Through psycopg, net_revenue arrives as a double (29790674.06 survives at this size), where REST sends the decimal as a string. Totals a BI tool computes from doubles can drift in the last digits; let the layer aggregate.

Gotcha — the SQL API has no token. Superset logs in with CUBEJS_SQL_USER, so it gets the default scope, every country. Per-person scoping there needs Cube’s checkSqlAuth to map SQL users to scopes (not run here).

Tip — write descriptions as if a stranger will read them. /v1/meta returns them, with each view’s meta, to every client; in Part 33 an agent reads them.

Troubleshooting

Design Drill

The question: “We run an e-commerce platform for 50,000 merchants. Expose sales, conversion and inventory analytics inside the merchant dashboard: each merchant sees only its own data, numbers must match their payout statements, and the biggest merchants have 100 times the data of the median.”

Clarify first

• Which numbers, at which p95 latency and freshness? Does anything need seconds?
• Must figures reconcile with payouts to the penny, on the merchant’s date and time zone?
• Peak concurrency: how many merchants open the dashboard at once (Monday mornings, sale days)?
• Staff users across merchants (agencies, support)? Self-service exploration or fixed tiles? Exports?

The shape of a strong answer

• One definition per metric, in a semantic layer shared with internal finance and BI, so the merchant’s “net sales” is finance’s and matches the merchant’s order export (refund dating, currency, the merchant’s business date).
• Tenant scope from the token, enforced below the layer: the backend signs a token with merchant_id; the layer turns it into a row filter or an engine identity, with an orchestrator and cache per tenant group so no cached result crosses tenants (Step 4).
• A serving store sized for concurrency: 5,000 dashboards opening at once × 8 tiles (assumptions) is 40,000 tile queries a wave, about 670 a second if spread over a minute. The lake engine is the wrong door; serve per-merchant, per-day rollups, built incrementally, from a real-time OLAP store or the layer’s pre-aggregations, sorted by merchant_id.
• SLOs per tier: for example p95 under 500 ms for cached tiles, 2 s for ad-hoc filters; “today” from the streaming tier (Part 30) within 5 minutes, history by 07:00 merchant time; each tile stamped with its as-of time.
• Isolation and metering: a separate pool or queue for the 100× merchants so they can’t starve the long tail; meter queries and bytes per merchant, and let higher plans pay for dedicated rollups.
• Guardrails and a versioned interface: row limits, timeouts, per-merchant rate limits, asynchronous exports; views as the contract, with deprecation windows and a reconciliation check against payouts.

Trade-offs to say out loud

• Shared tables with row filters are cheap; per-tenant databases isolate noisy neighbours and simplify deletion, at a large operational cost.
• Pre-aggregations make the 9 a.m. rush cheap but add a build pipeline and a staleness window.
• 50,000 orchestrators is a lot of memory: group tenants by the identity that enforces their scope, and keep the merchant filter in the query.

Common weak answers

• Dashboards straight on the warehouse, one service account, the tenant filter in the frontend.
• A separate “merchant metrics” SQL that drifts from payouts.
• A cache keyed by query text alone, so one merchant’s result serves another.

Further Reading

1. Cube documentation, “Configuration options” (context_to_app_id, context_to_orchestrator_id, driver_factory, query_rewrite). https://docs.cube.dev/reference/configuration/config
2. Cube documentation, “SQL API” and “REST API”. https://docs.cube.dev/reference/core-data-apis/sql-api and https://docs.cube.dev/reference/core-data-apis/rest-api
3. dbt documentation, “Measures” (including non_additive_dimension) and the MetricFlow overview. https://docs.getdbt.com/docs/build/measures
4. Apache Superset documentation (6.0), “Connecting to Databases”. https://superset.apache.org/user-docs/6.0.0/configuration/databases/
5. Apache Ossie (incubating). https://ossie.apache.org/

Story Closing

Farah opened Superset and the store app side by side: 6.21% in both, 88.5% in both, £29,790,674.06 in both, and the GB app showing GB’s own 6.19%, labelled as GB. “So when marketing wants 5.31% again?”

“Then it’s a new metric with its own name and owner,” Ingrid said, “not a quiet edit to this one.”

Three numbers, one answer each, wherever they are read: the incident is closed. In Part 32, the governed data starts feeding models: features correct as of the moment of prediction, and a support-ticket pipeline for retrieval.

This is Part 31 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 3: Real Time, Serving and AI.