Are all semantic layers created the same?
Semantic layers are all the rage these days. There are many popular ones, and it can be hard to know which one you want to use, if any. The goal of this post is to give you a mental map of the domain, the things to look at when choosing one, and the strengths and weaknesses of indivudual ones.
Here we will be speaking about the semantic layers in the sense of a governed collection of metric and dimension definitions, to be used for reliably creating database queries. Sometimes the term is also used in a different sense, to do with ontologies and knowledge graphs, which we will not cover here. We will focus on the open source ones, as the commercial ones are harder and more expensive to access and review.
The incumbents in the semantic layer space are MetricFlow (dbt’s semantic layer engine) and Cube.js; worth mentioning is Lightdash, which used to be more of a dashboard solution but is now increasingly beefing up its semantic layer side. A project to watch is Apache Ossie (formerly Open Semantic Interchange), which aims to become an open source semantic layer standard. At the moment it only has a limited spec for specifying datasets and no reference semantics for querying, so definitely worth following but not a full solution at the moment. Finally, we will cover SLayer, a recent entrant aiming to be used by agents as well as humans.
The shared core
All the semantic layers have a fair bit of a shared core. The most basic idea of a semantic layer is to represent a groupby query, with the definitions of what’s being aggregated (called metrics) and what’s being grouped by (called dimensions) sourced from the semantic layer definitions.
The whole semantic model is composed of what OSI calls datasets and Cube.js calls cubes, each basically an overlay over a database table or SQL query, describing the metrics and dimensions associated with it, and potentially some extras like filters. These datasets are then connected by joins, which are also part of the semantic model.
Joins and multi-dataset queries
Very often, a query needs to use information from more than one dataset. This requires knowledge about the right way to join these, especially if several join hops are needed to connect them. This is more than a technical detail - if a table is joined to another via a one-to-many relationship, for example, the number of rows in the result may increase, which will make aggregations incorrect (known as a fan-out).
To alleviate that danger, all semantic layers store the cardinality of a join (one-to-one, one-to-many, etc) as part of the join definition (MetricFlow calls the join key + cardinality combination “entity”, but it’s really the same thing).
Where the semantic layers differ is in whether they require the cardinality information to be specified on every join, and how they try to prevent incorrect aggregations due to fan-outs and chasm traps (another kind of metric distortion due to careless joins).
OSI is the odd-one out as it structurally is only able to represent one-to-many joins; of the others, Metricflow and Cube require the cardinality to be specified as part of a join, while Lightdash and SLayer keep it optional. Of all the semantic layers reviewed here, only SLayer inspects the actual data in the database to make hypotheses about the cardinality - all others rely on config writer to provide it, and don’t validate it against data.
Metricflow is the most limited in terms of fan-out protection, prohibiting certain joins (foreign-foreign) instead of re-expressing them using sub-queries (CTEs). All the others construct CTEs to avoid the row duplication in the different scenarios, though the degree of config needed to make it work may differ.
Query-time flexibility
The Achilles heel of most old-school semantic layers is their query-time clunkiness, only allowing you to supply a combination of predefined measures/dimensions/filters/segments/order_by/limit, with only minor exceptions. Want to calculate a ratio of two already defined measures, or of a measure to the same measure one year ago? You have to modify the configs and wait for the change to propagate.
This means that agents trying to make a query that isn’t contained in the configs in quite the way needed, must often fall back to writing raw SQL, losing all the advantages of governed metrics.
SLayer is the one exception here, allowing a wealth of transforms and expressions at query time, such as time shift, arithmetic expressions, and even allowing you to extend a dataset on the fly then query it, as part of a single query call.
{
"source_model": "orders",
"measures": [
"order_total:sum",
{
"formula": "time_shift(order_total:sum, -1, 'year')",
"name": "revenue_ly"
},
{"formula": "order_total:sum / *:count", "name": "aov"},
{"formula": "change(order_total:sum)", "name": "mom_change"}
],
"dimensions": ["stores.name"],
"time_dimensions": [
{
"dimension": "ordered_at",
"granularity": "month",
"date_range": ["2025-09-01", "2026-08-31"]
}
],
"filters": ["last(change(order_total:sum)) < 0"]
}
One call against the demo Jaffle Shop database: the plain monthly total, the same month a year earlier via time_shift, a ratio of two measures, and the month-on-month delta, keeping only the stores whose latest month fell. None of revenue_ly, aov or mom_change exists in the model, and none of it needed a config change. More on the time transforms in Time is the Hardest Dimension.
Multi-stage aggregation
Quite often, the question one asks doesn’t resolve to a single simple query, but requires using the result of one query as input to another. Examples are derived dimensions (“Average revenue per usage bucket”, where usage bucket is also computed on the fly) and aggregates of aggregates (eg “Average by region of total revenue by store”). The degree to which this is supported differs greatly by semantic layer.
Metricflow allows differing-grain filters only, but not grouping by or re-aggregating of a first-stage query, and Lightdash only allows a limited set of post-processing such as percent-of-total over the already-fetched result set at its existing grain.
Cube does explicitly support multistage aggregation. The first-stage aggregation must be inlined into the final definition, and only a limited number of pre-aggregation types are supported, which results in a limited syntax that is capable of expressing some kinds of multistage queries but not others. As with any other metrics, these definitions must be added to the dataset configs, and can’t be added on the fly.
In all of the above cases, multistage aggregations can be achieved by defining a new dataset based on custom sql that describes the first-stage aggregation, but that is not really a good solution for an ad hoc query.
In contrast, SLayer provides a natural, general support for multistage aggregations by allowing the agent to treat the result of a query as just another dataset. So it can first define the first-stage query, and then aggregate or join it to the other dataset(s) it needs just like it would do with another dataset, and then evaluate the multi-stage aggregation, all part of a single query call.
[
{
"name": "monthly_store_revenue",
"source_model": "orders",
"measures": [{"formula": "order_total:sum", "name": "revenue"}],
"dimensions": ["stores.name"],
"time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}]
},
{
"source_model": "monthly_store_revenue",
"measures": [{"formula": "revenue:avg"}],
"dimensions": ["stores.name"]
}
]
The aggregate of an aggregate, in one call. The first stage names itself monthly_store_revenue and totals revenue per store and month; the second reads that name as its source_model, so revenue:avg averages those monthly sums rather than the raw rows. Longer walkthrough in Queries That Build on Queries.
Search capability
While demo database setups usually fit into a single agent context window, production setups rarely do. This makes a clean and powerful search capability critical for agent-facing semantic layers. Of those reviewed here, only SLayer offers a genuine, general search. The search is three-channel: full-text, embeddings, and entity overlap between query and result, with the channels results then merged using Reciprocal Rank Fusion.
All the others allow to page through the model/dataset definitions, and possibly do keyword search, but don’t offer any compariable retrieval capability.
What kind of context can be stored?
As a natural extension of the search capability, it will come as no surprise that SLayer is the only one that allows to store (and retrieve) pieces of context not directly tied to a single entity (the way e.g. dimension or metric descriptions are). An example could be an instruction to use one definition of revenue for product-domain queries, and another for finance-domain queries.
Such pieces of context (called memories) will contain formal references to the entities in question, and can also have an example query attached.
Does it come with an MCP server?
Here the breakdown is clear: for SLayer, an MCP server is part of the open source package, while Cube, Lightdash, and Metricflow only make it available as part of the commercial offering (and OSI, being at this point only a static dataset description standard, doesn’t have one at all).
Disclaimer: All the statements we made here represent our understanding as of this moment. However, each of the packages mentioned here is complex and fast-moving, so if you find any inaccuracies, please let us know so that we can correct them!
Top comments (0)