Semantic Layer
A shared layer of business-defined metrics and dimensions that sits between raw lakehouse tables and every BI tool or query, so "revenue" means the same thing everywhere.
High-Level Design
The Semantic Layer is what keeps every dashboard's numbers reconcilable.
💼 Business Context
- Ends the recurring problem of two dashboards showing two different numbers for "revenue" because each was computed with a slightly different SQL query
- Lets business users self-serve metrics without needing to know the underlying table joins
- Owned by Analytics Engineering, metric definitions reviewed with the owning business domain
🔌 Technical Overview
Metric and dimension definitions (e.g., net_revenue = sum(order_total) - sum(refund_amount)) are declared once, version-controlled alongside the dbt models in Rollups & Aggregations that they read from, and compiled into SQL by the Query & Analytics Engine's DataFusion runtime — running inside a .NET Core Analytics/AI API packaged as a Docker container on Azure Container Apps. Any consumer (BI tool, ad hoc query, the AI Copilot) requests a metric by name rather than writing its own aggregation logic, guaranteeing every caller gets an identical computation.
Semantic Objects
💾 Metric Definition
metric: net_revenue sql: sum(order_total) - sum(refund_amount) table: marts.order_fact dimensions: [region, channel, product_category] time_grain: day
🔗 Integration Points
- Rollups & Aggregations (Transformation & Processing) — dbt marts the semantic layer reads from
- Data Dictionary (Metadata Layer) — business-readable definitions are cross-linked to semantic metrics
- AI Copilot — translates natural language into semantic-layer metric requests rather than raw SQL
- BI tools (Power BI, Looker-class tools) — connect via the semantic layer's query API, not directly to marts tables
🧰 Services Consumed
- Owning microservice —
Cxos.Intelligence.Api(see the Full Application Service Map) - Database — Azure Database for PostgreSQL (semantic layer) + Azure Cache for Redis (query cache)
⚠️ Non-Functional Considerations
- Scale: metric definitions grow with business complexity, not data volume — a governance/curation concern more than an infrastructure one
- Latency: metric compilation to SQL adds negligible overhead; actual query latency is dominated by the Query Optimizer and Caching layers beneath it
- Reliability: metric definitions are code-reviewed and tested like dbt models, since a wrong formula silently produces a wrong number everywhere it is used
- Security/Privacy: semantic-layer queries still pass through Access Control (RBAC/ABAC) — a shared metric definition does not bypass row/column-level entitlements
🎯 Enterprise Example
Finance and Marketing both build dashboards referencing net_revenue by region. Because both queries compile through the same semantic definition, a quarter-end reconciliation that used to take a week of tracing formula differences now takes minutes.