Why does your agent need a semantic layer?¶
If you want some agentic analytics, why not just give Claude Code readonly access to your database, and ask it to do whatever you need done?
What is a semantic layer anyway?¶
People use it to refer to two quite distinct entities:
Firstly, a way to make business-centric data queries more reliable, consisting of a collection of metric definitions, usually described in a domain-specific language (DSL) that the semantic layer engine then converts into SQL for specific queries. Examples are dbt semantic layer, Cube.js, Looker, etc - basically headless BI.
Secondly, a knowledge graph encoding the entities relevant to the business, such as employees, customers, etc, and their relationships, in a more or less formal manner, using either classic ontologies (RDF), ad-hoc graphs (Neo4j) or typed hypergraphs (typedb)
Why not just ask Claude?¶
Let us start with some quotes from "How Anthropic enables self-service data analytics with Claude"
"To fully understand the challenges with analytics agents, it’s useful to compare them to coding agents. Coding is an open-ended solution space that rewards the models' creativity, while documentation and tests provide natural guardrails against hallucination. In contrast, for analytics use cases, there’s often only a single correct answer using a single correct source in which there’s no deterministic way of proving the correctness."
"One idea we tried that didn’t work: bootstrapping the semantic layer by having an LLM auto-generate metric definitions from raw tables and query logs. It produced plausible-looking definitions that encoded the very ambiguities we were trying to eliminate, and was net-negative on our evals versus a smaller, human-curated layer. Therefore we recommend generating the documentation with Claude, but having a human own the definition."
"It [the skill router] also bundles a dozen reusable analysis patterns (retention curves, rate decomposition, funnel analysis) so that common requests don't get reinvented each time."
So precise, deterministic metric definitions are data that needs to be stored somewhere, not made up on the fly from natural language by agents. That's what a semantic layer of the first kind does.
Why just asking your agent to generate SQL is not optimal¶
- Business-logic complexity — agents must juggle objective rules (currency conversion, time zones) alongside undocumented conventions (which rows count as a "real" order, what defines a funnel stage).
- Undocumented table relationships — joins often exist by convention rather than as foreign keys, so schema introspection misses them.
- Consistency across calls — several reasonable SQL translations exist for the same natural-language question; the agent must land on the same one every time or it looks unreliable.
- Prompt overload — packing every rule into the system prompt means the model quietly ignores some of them once the context gets crowded.
- Hard to validate — writing general checks that catch wrong-but-syntactically-fine SQL (off-by-one time shifts, rows silently dropped by a bad JOIN) is far from trivial.
Each of the next sections shows how SLayer takes one of these off the agent's plate.
The other half: business context¶
"Business context: the layer most teams skip, and the one we underrated the longest. An agent that doesn’t understand your business will answer what the user asked, but not what they meant. It won’t know that “the Q2 launch” refers to a specific product, that two teams define the same term differently, or that a question is being asked because a board meeting is on Thursday. We pipe in a company knowledge graph consisting of indexed docs, roadmaps, decision logs, and our organizational structure so the agent can resolve ambient references and ask better clarifying questions."
That's exactly what a semantic layer of the second kind does.
To enable reliable analytics, an agent needs BOTH kinds in one context provider¶
You see that the two kinds of semantic layer are both necessary, and need to be closely integrated for maximum benefit.
In any case, the difference between the two is one of degree - after all, dbt semantic layer or Cube.js also allow text descriptions of the metrics they store; and some of the nodes of a knowledge graph might well store SQL snippets; but until recently, any given product tended to only do one of the two well.
Recently, as people began to see that the two kinds are both necessary facets of the context an analytics-enabled agent needs, products began to appear that do both - a name I've been hearing for those is "executable context layer".
Allow me to introduce our open-source version of that, SLayer.
What an agent needs to actually analyse data¶
- See what data is available
- Get trusted metric definitions
- Ask using ad-hoc queries — including complex ones
- Know which metric to choose when
- Have business context for specific cases
- Find the relevant bits of all of the above quickly
The rest of the talk walks through each one.
Setup¶
We'll use the Jaffle Shop dataset — synthetic coffee-shop orders across 6 stores, 3 years of data, ~960k orders. Every cell below runs against it.
Prerequisites: pip install 'motley-slayer[advanced_search]' jafgen. OPENAI_API_KEY is optional — without it, search still works via BM25 + tantivy; with it, the dense-embedding channel also activates.
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), '..', '..', '..'))
sys.path.insert(0, os.getcwd())
from setup_talk import ensure_lightning_talk_demo
from slayer.async_utils import run_sync
from slayer.core.errors import MemoryNotFoundError
engine, storage, client, models = ensure_lightning_talk_demo()
See — auto-ingestion gave us models for free¶
Point SLayer at a database, it walks the schema, infers types, and discovers foreign keys. The agent gets a typed graph of your tables without anyone writing YAML. The setup cell above already did this — there's nothing else to configure.
You will probably want to add custom metrics and otherwise customize the models, but auto-ingestion allows you to get started quickly.
See Auto-Ingestion docs.
print(f'{len(models)} models ingested:')
for m in sorted(models, key=lambda m: m.name):
print(f' - {m.name:>10} ({len(m.columns)} columns, {len(m.joins)} joins)')
orders = next(m for m in models if m.name == 'orders')
print(f'orders: {len(orders.columns)} columns, {len(orders.joins)} direct joins')
print()
print('Columns:')
for col in orders.columns:
pk = ' [PK]' if col.primary_key else ''
print(f' {col.name:<14} {str(col.type):<10}{pk}')
print()
print('Joins discovered automatically:')
for j in orders.joins:
src, tgt = j.join_pairs[0]
print(f' orders.{src} -> {j.target_model}.{tgt}')
7 models ingested: - customers (2 columns, 0 joins) - items (3 columns, 2 joins) - orders (7 columns, 2 joins) - products (5 columns, 0 joins) - stores (4 columns, 0 joins) - supplies (5 columns, 1 joins) - tweets (4 columns, 1 joins) orders: 7 columns, 2 direct joins Columns: id TEXT [PK] customer_id TEXT ordered_at DATE store_id TEXT subtotal DOUBLE tax_paid DOUBLE order_total DOUBLE Joins discovered automatically: orders.customer_id -> customers.id orders.store_id -> stores.id
Get — one metric definition, the right rollup per question¶
order_total is a column — defined once in the model. The aggregation (sum / average / median / weighted-average / …) is the agent's choice at query time. The metric never drifts across calls because the definition lives in the model; the agent isn't forced to pre-commit to one aggregation method.
result = engine.execute_sync(query={
'source_model': 'orders',
'measures': [
{'formula': 'order_total:sum', 'name': 'revenue'},
{'formula': 'order_total:avg', 'name': 'avg_order'},
],
'dimensions': ['stores.name'],
'time_dimensions': [{'dimension': 'ordered_at', 'granularity': 'month'}],
'order': [{'column': 'revenue', 'direction': 'desc'}],
})
assert len(result.data) > 0, 'multi-aggregation query returned no rows'
import pandas as pd
pd.DataFrame(result.data)
| orders.stores.name | orders.ordered_at | orders.revenue | orders.avg_order | |
|---|---|---|---|---|
| 0 | Brooklyn | 2025-10-01 | 127902.00 | 11.209641 |
| 1 | Brooklyn | 2025-12-01 | 125207.33 | 11.404256 |
| 2 | Brooklyn | 2025-11-01 | 124262.01 | 11.571097 |
| 3 | Brooklyn | 2026-01-01 | 122842.49 | 11.069883 |
| 4 | Brooklyn | 2025-09-01 | 122738.53 | 11.402688 |
| ... | ... | ... | ... | ... |
| 104 | Brooklyn | 2023-12-01 | 15949.40 | 10.961787 |
| 105 | San Francisco | 2025-02-01 | 14331.85 | 11.347466 |
| 106 | New Orleans | 2026-06-01 | 11825.81 | 11.241264 |
| 107 | New Orleans | 2025-12-01 | 11686.45 | 10.341991 |
| 108 | Philadelphia | 2023-06-01 | 11037.32 | 10.461915 |
109 rows × 4 columns
Ask — complex queries compose like Lego¶
Real questions stack things: group by month, aggregate, compare to the prior month, compare to last year, filter to growth, take the top N. Doing this in raw SQL means CTEs, window functions, self-joins. SLayer lets the agent build the question out of primitives.
hero = {
'source_model': 'orders',
'measures': [
{'formula': 'order_total:sum', 'name': 'revenue'},
{'formula': 'change_pct(order_total:sum)', 'name': 'mom_growth'},
{
'formula': "order_total:sum / time_shift(order_total:sum, -1, 'year') - 1",
'name': 'yoy_growth',
},
],
'dimensions': ['stores.name'],
'time_dimensions': [{'dimension': 'ordered_at', 'granularity': 'month'}],
'filters': ['change_pct(order_total:sum) > 0'],
'order': [{'column': 'mom_growth', 'direction': 'desc'}],
'limit': 10,
'whole_periods_only': True,
}
result = engine.execute_sync(query=hero)
assert len(result.data) > 0, 'hero query returned no rows'
display(pd.DataFrame(result.data))
| orders.stores.name | orders.ordered_at | orders.revenue | orders.mom_growth | orders.yoy_growth | |
|---|---|---|---|---|---|
| 0 | Brooklyn | 2024-01-01 | 40397.72 | 1.532868 | None |
| 1 | Philadelphia | 2023-07-01 | 24685.00 | 1.236503 | None |
| 2 | San Francisco | 2025-03-01 | 31591.94 | 1.204317 | None |
| 3 | New Orleans | 2026-01-01 | 25075.42 | 1.145683 | None |
| 4 | San Francisco | 2025-07-01 | 64800.28 | 0.605787 | None |
| 5 | Brooklyn | 2024-07-01 | 95375.10 | 0.486821 | None |
| 6 | Chicago | 2025-07-01 | 73926.68 | 0.472685 | None |
| 7 | Philadelphia | 2023-09-01 | 40046.75 | 0.402552 | None |
| 8 | Brooklyn | 2024-03-01 | 54936.87 | 0.367116 | None |
| 9 | New Orleans | 2026-03-01 | 32700.66 | 0.357876 | None |
# And this is the SQL generated by SLayer for the above, which neither you, nor the agent had to write.
print(result.sql)
SELECT
"orders.stores.name",
"orders.ordered_at",
"orders.revenue",
"orders.mom_growth",
"orders.yoy_growth"
FROM (
SELECT *
FROM (
WITH base AS (
SELECT
stores.name AS "orders.stores.name",
DATE_TRUNC('MONTH', orders.ordered_at) AS "orders.ordered_at",
SUM(orders.order_total) AS "orders.revenue"
FROM orders AS orders
LEFT JOIN stores AS stores
ON orders.store_id = stores.id
WHERE
orders.ordered_at <= '2026-05-31'
GROUP BY
stores.name,
DATE_TRUNC('MONTH', orders.ordered_at)
),
shifted__ts_mom_growth AS (
SELECT
stores.name AS "orders.stores.name",
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' MONTH AS TIMESTAMP)) AS "orders.ordered_at",
SUM(orders.order_total) AS "orders.revenue"
FROM orders AS orders
LEFT JOIN stores AS stores
ON orders.store_id = stores.id
WHERE
(
orders.ordered_at + INTERVAL '1' MONTH
) <= '2026-05-31'
GROUP BY
stores.name,
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' MONTH AS TIMESTAMP))
),
sjoin__ts_mom_growth AS (
SELECT base."orders.ordered_at", base."orders.revenue", base."orders.stores.name", shifted__ts_mom_growth."orders.revenue" AS "orders._ts_mom_growth"
FROM base
LEFT JOIN shifted__ts_mom_growth
ON base."orders.ordered_at" = shifted__ts_mom_growth."orders.ordered_at" AND base."orders.stores.name" = shifted__ts_mom_growth."orders.stores.name"
),
shifted__t0 AS (
SELECT
stores.name AS "orders.stores.name",
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' YEAR AS TIMESTAMP)) AS "orders.ordered_at",
SUM(orders.order_total) AS "orders.revenue"
FROM orders AS orders
LEFT JOIN stores AS stores
ON orders.store_id = stores.id
WHERE
(
orders.ordered_at + INTERVAL '1' YEAR
) <= '2026-05-31'
GROUP BY
stores.name,
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' YEAR AS TIMESTAMP))
),
sjoin__t0 AS (
SELECT sjoin__ts_mom_growth."orders._ts_mom_growth", sjoin__ts_mom_growth."orders.ordered_at", sjoin__ts_mom_growth."orders.revenue", sjoin__ts_mom_growth."orders.stores.name", shifted__t0."orders.revenue" AS "orders._t0"
FROM sjoin__ts_mom_growth
LEFT JOIN shifted__t0
ON sjoin__ts_mom_growth."orders.ordered_at" = shifted__t0."orders.ordered_at" AND sjoin__ts_mom_growth."orders.stores.name" = shifted__t0."orders.stores.name"
),
shifted__ts__ft0 AS (
SELECT
stores.name AS "orders.stores.name",
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' MONTH AS TIMESTAMP)) AS "orders.ordered_at",
SUM(orders.order_total) AS "orders.revenue"
FROM orders AS orders
LEFT JOIN stores AS stores
ON orders.store_id = stores.id
WHERE
(
orders.ordered_at + INTERVAL '1' MONTH
) <= '2026-05-31'
GROUP BY
stores.name,
DATE_TRUNC('MONTH', CAST(orders.ordered_at + INTERVAL '1' MONTH AS TIMESTAMP))
),
sjoin__ts__ft0 AS (
SELECT sjoin__t0."orders._t0", sjoin__t0."orders._ts_mom_growth", sjoin__t0."orders.ordered_at", sjoin__t0."orders.revenue", sjoin__t0."orders.stores.name", shifted__ts__ft0."orders.revenue" AS "orders._ts__ft0"
FROM sjoin__t0
LEFT JOIN shifted__ts__ft0
ON sjoin__t0."orders.ordered_at" = shifted__ts__ft0."orders.ordered_at" AND sjoin__t0."orders.stores.name" = shifted__ts__ft0."orders.stores.name"
),
step2 AS (
SELECT
"orders._t0",
"orders._ts__ft0",
"orders._ts_mom_growth",
"orders.ordered_at",
"orders.revenue",
"orders.stores.name",
CASE WHEN "orders._ts_mom_growth" != 0 THEN ("orders.revenue" - "orders._ts_mom_growth") * 1.0 / "orders._ts_mom_growth" END AS "orders.mom_growth",
"orders.revenue" / "orders._t0" - 1 AS "orders.yoy_growth",
CASE WHEN "orders._ts__ft0" != 0 THEN ("orders.revenue" - "orders._ts__ft0") * 1.0 / "orders._ts__ft0" END AS "orders._ft0"
FROM sjoin__ts__ft0
)
SELECT
"orders._ft0",
"orders._t0",
"orders._ts__ft0",
"orders._ts_mom_growth",
"orders.mom_growth",
"orders.ordered_at",
"orders.revenue",
"orders.stores.name",
"orders.yoy_growth"
FROM step2
) AS _filtered
WHERE "orders._ft0" > 0
) AS _outer
ORDER BY
"orders.mom_growth" DESC
LIMIT 10
Queries as models — unlimited depth¶
Every SLayer query also defines a model: its result columns become dimensions, its numeric columns become measure sources, and the next query can source_model it by name. No bolt-on multi-stage syntax — this falls out of the design.
Example: average monthly revenue per store. Stage 1 sums revenue by store-month; stage 2 averages those monthly sums per store.
from IPython.display import Markdown
result = engine.execute_sync(query=[
{
'name': 'monthly_store_revenue',
'source_model': 'orders',
'measures': ['order_total:sum'],
'dimensions': ['stores.name'],
'time_dimensions': [{'dimension': 'ordered_at', 'granularity': 'month'}],
},
{
'source_model': 'monthly_store_revenue',
'measures': ['order_total_sum:avg'],
'dimensions': ['stores__name'],
'order': [{'column': 'order_total_sum_avg', 'direction': 'desc'}],
},
])
assert len(result.data) > 0, 'multistage query returned no rows'
# Note the pretty formatting of the numbers if you export them as markdown
Markdown(result.to_markdown())
| monthly_store_revenue.stores__name | monthly_store_revenue.order_total_sum_avg |
|---|---|
| Brooklyn | 92.6K |
| Chicago | 68.6K |
| San Francisco | 61.1K |
| Philadelphia | 60.2K |
| New Orleans | 24.5K |
Have & Find — text context plus a way to find it¶
Most semantic layers stop at a description field per metric and leave the agent to guess which one to read. SLayer ships two things on top:
- Memories — free-form notes the agent writes, each linked to whichever entities it's about (and optionally carrying an example query).
- Search — one tool that retrieves both memories and canonical entities, fusing three retrieval channels.
See Memories docs and Search docs.
# You can save a learning linked to multiple entities
brooklyn = run_sync(client.save_memory(
learning=(
'Brooklyn switched to a new POS system in late 2024. '
'Order totals before 2025-01-01 are from the legacy system; '
'they exclude tax and use a different currency-rounding rule. '
'Exclude pre-2025-01-01 Brooklyn data when comparing to other stores.'
),
linked_entities=[
'jaffle_shop.orders.order_total',
'jaffle_shop.stores.name',
],
id='lightning.brooklyn_pos',
))
print(f'Saved memory id={brooklyn.memory_id}')
print(f'Linked to: {brooklyn.resolved_entities}')
if brooklyn.warnings:
print(f'Warnings: {brooklyn.warnings}')
22:07:01 - LiteLLM:WARNING: get_model_cost_map.py:271 - LiteLLM: Failed to fetch remote model cost map from https://raw.githubusercontent.com/BerriAI/litellm/main/model_prices_and_context_window.json: Using SOCKS proxy, but the 'socksio' package is not installed. Make sure to install httpx using `pip install httpx[socks]`.. Falling back to local backup.
Saved memory id=lightning.brooklyn_pos Linked to: ['jaffle_shop.orders.order_total', 'jaffle_shop.stores.name']
# You can save an example query with annotation, too, and it will be linked to the entities it contains
top_customers = run_sync(client.save_memory(
learning=(
'Top-5 customers by lifetime spend - known-good query pattern '
'used in the weekly CRM-ops dashboard.'
),
linked_entities={
'source_model': 'orders',
'measures': [{'formula': 'order_total:sum', 'name': 'lifetime_spend'}],
'dimensions': ['customers.name'],
'order': [{'column': 'lifetime_spend', 'direction': 'desc'}],
'limit': 5,
},
id='lightning.top_customers',
))
print(f'Saved query-bearing memory id={top_customers.memory_id}')
print(f'Auto-extracted entities: {top_customers.resolved_entities}')
Saved query-bearing memory id=lightning.top_customers Auto-extracted entities: ['jaffle_shop.orders', 'jaffle_shop.customers.name', 'jaffle_shop.orders.order_total']
Find — one tool, three retrieval channels, and Cypher filtering¶
search retrieves both memories and canonical entities, merging three channels via Reciprocal Rank Fusion:
- BM25 over each memory's stored entity tags — strongest when the agent already has an entity reference in hand.
- tantivy full-text over learning text and canonical entities — natural-language questions.
- embeddings (cosine over a sidecar table) — dense similarity. Optional; degrades gracefully if
OPENAI_API_KEYisn't set.
The agent doesn't pick a channel. It asks once.
# Search with a natural-language question. cypher_filter scopes the graph to
# memory nodes, so the *search call* does the narrowing — not Python.
resp = run_sync(client.search(
question='What should I know before comparing Brooklyn revenue to other stores?',
cypher_filter='MATCH (m:Memory) RETURN m.id AS id',
max_results=10,
))
assert any(h.id == 'lightning.brooklyn_pos' for h in resp.results), 'Brooklyn memory must surface'
pd.DataFrame([h.model_dump() for h in resp.results])[['kind', 'id', 'score', 'text']]
| kind | id | score | text | |
|---|---|---|---|---|
| 0 | memory | lightning.brooklyn_pos | 0.032787 | Brooklyn switched to a new POS system in late ... |
| 1 | memory | lightning.top_customers | 0.016129 | Top-5 customers by lifetime spend - known-good... |
# Anchor on a specific column instead of free text; the same graph filter applies.
resp = run_sync(client.search(
entities=['jaffle_shop.orders.order_total'],
cypher_filter='MATCH (m:Memory) RETURN m.id AS id',
max_results=10,
))
assert any(h.id == 'lightning.brooklyn_pos' for h in resp.results), 'Brooklyn memory must surface via order_total'
pd.DataFrame([h.model_dump() for h in resp.results])[['kind', 'id', 'score', 'matched_entities']]
| kind | id | score | matched_entities | |
|---|---|---|---|---|
| 0 | memory | lightning.brooklyn_pos | 0.016393 | [jaffle_shop.orders.order_total] |
| 1 | memory | lightning.top_customers | 0.016129 | [jaffle_shop.orders.order_total] |
# cypher_filter is a graph pre-filter. A multi-label match scopes the search to
# memories AND model entities in one call (columns, measures, … are dropped).
resp = run_sync(client.search(
question='How have analysts queried customer lifetime spend before?',
cypher_filter='MATCH (n:Memory:Model) RETURN n.id AS id',
max_results=20,
))
example_queries = [h for h in resp.results if h.query is not None]
entities = [h for h in resp.results if h.kind != 'memory']
assert any(h.id == 'lightning.top_customers' for h in example_queries), 'top-customers query must surface'
assert entities, 'expected at least one entity hit'
pd.DataFrame([h.model_dump() for h in resp.results])[['kind', 'id', 'score', 'text']]
| kind | id | score | text | |
|---|---|---|---|---|
| 0 | memory | lightning.top_customers | 0.032787 | Top-5 customers by lifetime spend - known-good... |
| 1 | memory | lightning.brooklyn_pos | 0.032258 | Brooklyn switched to a new POS system in late ... |
| 2 | model | jaffle_shop.customers | 0.016129 | Model: jaffle_shop.customers\nsql_table: custo... |
| 3 | model | jaffle_shop.orders | 0.015873 | Model: jaffle_shop.orders\nsql_table: orders\n... |
| 4 | model | jaffle_shop.tweets | 0.015152 | Model: jaffle_shop.tweets\nsql_table: tweets\n... |
Wrap-up¶
Here today:
- Open source (MIT)
- Interfaces: MCP, CLI, REST, Python, Flight SQL
- Auto-ingestion
- Postgres / MySQL/ DuckDB / ClickHouse / SQLite
- Multistage queries, named measures, custom aggregations,
- Memories with embedding + entity + full text search, and Cypher filtering
Try it in Claude Code¶
One command wires SLayer up as an MCP server with the Jaffle Shop demo preloaded:
claude mcp add slayer -- uvx --from motley-slayer slayer mcp --demo
Then ask Claude in any project: "What stores are in jaffle_shop and which one has the highest revenue?" — it will call the same tools used above.
# Idempotent teardown: both ids may already be absent (e.g. partial prior
# run, or this cell rerun standalone). MemoryNotFoundError is the only
# expected failure mode here.
try:
run_sync(client.forget_memory('lightning.brooklyn_pos'))
except MemoryNotFoundError:
pass
try:
run_sync(client.forget_memory('lightning.top_customers'))
except MemoryNotFoundError:
pass
print('Memories removed. Re-running this notebook is idempotent.')
Memories removed. Re-running this notebook is idempotent.