DEV Community: motley.ai

What do DuckDB and SLayer have in common?

Anastasiia Beriukhova — Wed, 16 Sep 2026 14:14:02 +0000

They are both lightweight, and can be run both embedded and via a CLI (as well as MCP and other ways ;) ), no server to run if you don't want to, no warehouse to provision.

DuckDB can read a file straight off a URL over httpfs; SLayer can auto-ingest a schema during datasource setup, and then turns its simple yet powerful query syntax into the correct SQL.

Put them together and a semantic layer over a remote dataset is a handful of lines.

To show just how simple and powerful that pattern is, example notebooks have been put together, one for CLI, one for Python.

Each notebook shows, from scratch, how to define a view over a remote file in DuckDB, then to connect SLayer to that view, and to execute a deceptively simple query, containing:

A dimension computed from an aggregate, group each month warm or cool by its average high temperature, a CASE WHEN temp_max:avg(partition_by=date) … band used as a grouping dimension
A ranking transform in a measure, rank(precipitation:sum) to order the months by rainfall, one of SLayer's query-time transforms.

Both of these are defined at query time, showing how SLayer removes the need to pre-configure every little thing you want to query.

Why use SLayer at all, instead of direct SQL? The final cell of each notebook shows the SQL corresponding to that "simple" query json. Judge for yourself which one is easier to read, and which one an agent is more likely to generate correctly, time after time.

CLI
Python
SLayer repo

Post Apache Ossie, what distinguishes semantic layers?

Anastasiia Beriukhova — Mon, 14 Sep 2026 15:08:49 +0000

Enough has been said recently about why having a semantic layer is a good thing. Unfortunately, each of them has its own data model and corresponding storage format for storing those models, so migrating from one to another was a nontrivial endeavour.

Apache Ossie sets out to change that. It is an open-source standard for describing semantic layer models, with a lot of heavy hitters behind it. It's early days yet, and it only defines the semantic model interchange format, but with the kind of backers it has, it seems very likely it will get broad adoption (the obligatory xkcd comes to mind).

This leads to an interesting question: if porting semantic layer definitions from one format to another becomes free thanks to Apache Ossie, what are reasons to choose one semantic layer over the other?

The easy answer is to use whatever is bundled with a solution already in use for other purposes, such as Snowflake or dbt Cloud - and that is a reasonable option. However, their providers have a vested interest in promoting exactly the kind of lock-in that Apache Ossie is supposed to minimize; also, semantic layers that are offered as features of other products have a way of not being quite as polished as standalone products that live and die on their quality as semantic layers alone.

If the config side is made fungible by Apache Ossie, then the differentiating factor becomes the query-time behavior. The old-school semantic layers, such as Looker, Cube, or Metricflow (the dbt semantic layer) were optimized for a world where they were mostly called with the same query over and over, possibly with a different set of filters and groupbys (because only data people could author unusual queries, and they just wrote SQL). This made it natural to optimize for performance in such scenarios, which meant caching of pre-aggregations, which in turn pushed towards pretty static model configs.

In the world where agents are doing the querying, neither of those scenarios quite fits. Having SQL generated by semantic layers, using governed metrics and deterministic guarantees on join correctness, is a great source of reliability for the agent world. However, it's useless if to calculate a ratio of two metrics, or a time shifted version of one, the agent needs to modify the semantic model configs and wait for (at best) seconds for that to feed through - if it is allowed to do that at all.

The other gap that old-school semantic layers tend to have is discoverability - among hundreds, or thousands, of tables, with over a hundred columns each, how does the agent discover what is pertinent for just the query it wants to make? That extends to discoverability of related business rules that mention a metric, but also mention others so don't neatly fit into a single metric's description field.

These, then, are the two properties that distinguish an agent-ready semantic layer in a post-Apache Ossie world: expressiveness and flexibility at query time, and discoverability of both single dimensions/metrics and related business rules.

Naturally, those are what Motley has invested in, and also made available in its open source SLayer engine. On the expressiveness side, the vision is clear: the agent should be able to specify what it's looking for in as concise and natural way as possible, for example directly specifying in a simple syntax dimensions or filters that are themselves aggregates, or transforms such as time shift, no matter what dataset they all come from - and the semantic layer engine should figure out the necessary CTEs (subqueries) and join patterns, and generate the correct SQL in the right database dialect.

Current SLayer is closer to this vision than any other semantic layer on the market, and the upcoming 0.10 release next week will take it all the way there.

On the discoverability side, SLayer supports arbitrary chunks of context stored as memories, with references to the specific entities mentioned (such as metrics or dimensions), and a 3-channel search (embeddings + full-text + entity overlap, merged via Reciprocal Rank Fusion) over both memories and entities, so the agent can ask a freeform question and get a list of the likely relevant memories and entities. In contrast, the best the open source versions of the old school semantic layers can offer is keyword search.

So when evaluating a semantic layer for agent-friendliness, two things matter: query-time expressiveness, and entity and context discoverability. If they don't come out of the box, they'll need to be built before the semantic layer is much help to an agent - is that hassle really necessary?

Cube vs SLayer-using agent? We ran a benchmark expecting a close fight. It wasn't close.

Anastasiia Beriukhova — Wed, 09 Sep 2026 13:34:46 +0000

A few weeks back we showed that an agent using our semantic layer (SLayer) beat a raw-SQL agent by 30%+ on a random selection of 265 live-sqlbench-large tasks. Fair follow-up question: how does that hold up against Cube, the incumbent open-core semantic layer?

So we put open-source Cube behind an agent. There's no MCP server for it, so we wrapped its REST API for introspection and queries, auto-generated models from the DB schemas, adding dimensions and measures off the JSON columns' leaves, and handed the agent the same knowledge-base tools the raw-SQL agent had.

Then we picked 10 tasks the SLayer-using agent had already solved — to give Cube an easy start. The raw-SQL agent had cleared 5 of them.

The Cube agent solved zero.

The first guess was a bug in the harness. There wasn't one. The logs showed two hard blockers:

Cube's schema metadata is so verbose it often blew straight past Claude Code's 25K tool-output limit. The agent couldn't see the whole schema, so it sat there hallucinating queries.
Nine of the ten tasks needed custom measures or dimensions built at solve time — ratios, derived formulas, CASE groupings that were provided in the knowledge base. Open-source Cube gives you no reasonable way to edit models on the fly.

It would be possible to build around it: an external model store, a validate-on-edit compile loop, smart schema retrieval to dodge the token limit. That's a serious chunk of engineering to turn Cube into something it isn't — and it'd still take seconds to recompile on every single edit.

Those are some of the reasons why we left Cube and built SLayer in the first place.

So if you're planning to run Cube behind an agent, go in with your eyes open: you're writing the scaffolding yourself. Batteries not included.

(Or use an agent-native semantic layer and skip the hassle. But we would say that.)

Full post

Original SLayer vs raw SQL post

An Agent's Field Guide to Semantic Layers

Anastasiia Beriukhova — Wed, 26 Aug 2026 11:14:14 +0000

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!