The Semantic Layer You Already Have

Max Mealing — Thu, 16 Jul 2026 14:26:22 +0000

Semantic layers are having a revival. With agents now querying data across businesses, the thinking goes that they need a specific "language" to query it reliably.

Your company is openly discussing whether adopting one makes sense. Management are still bruised from the last failed warehouse-querying agent implementation and are looking for a better solution. Someone mentions Cube.dev, another suggests building a custom semantic layer.

As the lead data engineer in your company, you're thinking practically about the challenges of implementing a semantic layer: another abstraction to be maintained; a second copy of business logic that drifts as the data models and metrics evolve; another endpoint that needs to be hosted and monitored; a new framework to learn.

Before committing to anything, there's another option you should consider: leveraging your database's native metadata and functions as a lightweight semantic layer.

It's a surprisingly large lever. In our own agent testing, adding authored descriptions to an opaque schema took an agent's first-try table-finding from roughly one in four to near-perfect.

If you're already using dbt, you're likely most of the way there already, and maintaining a single source of truth is trivial.

Here's how you can achieve this in a few days' work.

1. Create your agent gold layer

Instead of revealing all your marts to agents, curate a helpful set of views (or tables) and columns that meet the specific contexts and use cases you want to provide. This is arguably where the most work will go. Aim for 20-30 views to begin with, then later you can scale up to 50-100 (which agents handle well with a good set of tools). Aim to group each view by domain and hide unnecessary internal views.

Since you're building views, you can also bake your metric definitions into them, for example a net_revenue column that already excludes refunds, so the agent uses the correct calculation and inherits your definition instead of re-deriving it.

When using BigQuery/Redshift/Snowflake, declaring primary, foreign and unique keys (even if unenforced) goes a long way later on to help agents build out complex multi-join queries, and spend less time on discovery.

2. Comments on views

Use view comments to help agents discover the right table for their task. A good view comment should help the agent answer:

When should I use this table
What does the table contain
What is the granularity of the data
Business synonyms that real users may actually use
Relationships to other tables in the schema
Any caveats around the scope/lifecycle e.g. "current vs historical, excludes cancelled, soft-deletes and deprecated"

COMMENT ON VIEW finance.orders IS
  'One row per customer order (aka sales, purchases). Current orders only,
   excludes cancelled (see finance.orders_all). Joins to finance.customers
   on customer_id. Filter on status; time grain is created_at.';

Comment the columns that matter, not every column. A blanket, low-value description on all 60 columns adds noise the agent has to wade through. Depth on the filter, join and date columns beats coverage.

3. Column names

Keep these legible, as if one of your business users were using them directly. They should implicitly carry a lot of meaning already:

Full words over abbreviations e.g. shipping_address over shp_adr
Consistent naming conventions: use snake_case, as mixed-case identifiers force quoting and will case-fold when unquoted, which leads to a lot of agent errors down the line.
Suffixes give context e.g. _id, _at, _date, is_* (for bools), _pct, _ratio
Add units and scale in the name when ambiguous e.g. amount_cents, weight_kg, duration_seconds
Consistent key naming for table joins e.g. customer_id, account_id etc.

-- Hard for an agent to read
CREATE TABLE ord (
  oid   int,
  cust  int,
  amt   numeric,
  st    text,
  dt    timestamp
);

-- Self-describing: the name carries the meaning
CREATE TABLE orders (
  id            bigint,
  customer_id   bigint,       -- FK -> customers.id
  total_cents   integer,      -- minor units
  status        text,
  created_at    timestamptz
);

4. Comments on columns

Column comments help agents use the various columns in the table correctly. Depending on the column type, these should typically provide the agent with:

Column meanings
Metric definitions
Additive properties and aggregation caveats for numeric types
Units and formatting
Code → label mapping hints
Join key → targets on other tables

5. Native enums on categorical columns

For categorical columns with a predictable set of values, expose that set so the agent writes the literal verbatim with the right casing. Agents will often guess "Cancelled" vs "cancelled" and return a wrong answer to the user when they get the value wrong. Upfront information about category values also means the agent avoids extra queries to discover the possible values each time.

-- Native enum: the allowed values become metadata the agent can read
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
  id      bigint,
  status  order_status   -- not `text`
);

Native ENUM types exist on Postgres, ClickHouse, DuckDB and MySQL. BigQuery, Redshift and Snowflake have no enum type, so use the fallback ladder there: a CHECK constraint where supported → the allowed values written into the column comment → a lookup/dimension table joined by a foreign key. The lookup table is also the better choice anywhere the value set churns, since a native ENUM is rigid (adding a value is a DDL change) and a lookup row can carry a label and description per value.

CREATE TABLE order_statuses (
  code         text PRIMARY KEY,   -- 'pending', 'paid', ...
  label        text,
  description  text
);

6. Use the analytical warehouse's physical keys as semantic hints

Analytical warehouses like Redshift, ClickHouse and BigQuery are columnar engines usually serving denormalized models, built for big scans rather than row-level integrity. ClickHouse in particular has no foreign keys at all, and even on BigQuery and Redshift any keys you declare are unenforced.

But these engines come with their own set of physical keys, chosen for performance, that double as the semantic hints a PK/FK would have given you in Postgres:

Distribution key (Redshift) → the column data is co-located on, usually the natural join key (only when the table sets DISTSTYLE KEY; the default AUTO may pick none)
Sort key / ORDER BY / partition / clustering key → the columns that filter cheaply, and typically the primary time grain
Primary key (ClickHouse) → the grain (it's a sparse-index/sort hint, not a uniqueness constraint)

-- ClickHouse: the ORDER BY / PARTITION BY are the grain + cheap filters
CREATE TABLE events (
  event_date  Date,
  user_id     UInt64,
  event_type  Enum8('view' = 1, 'click' = 2, 'purchase' = 3),
  amount      Decimal(10, 2)
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)   -- time grain + cheapest filter
ORDER BY (user_id, event_date);     -- grain + the join/filter columns

-- Redshift: distkey = the join column, sortkey = the cheap range filter
CREATE TABLE events (
  event_date  date,
  user_id     bigint,
  amount      numeric(10, 2)
)
DISTSTYLE KEY DISTKEY (user_id)
SORTKEY (event_date);

Read together, these let an agent infer a table's grain, the cheapest columns to filter on, and, from the distribution key specifically, a likely join column. Note the split: the distribution key is the join-shaped hint, while the sort and partition keys are grain and cheap-filter hints, not join paths.

Two caveats: they're heuristics, not guarantees (a distribution key is usually the join key, but not always), so where an engine lets you declare an unenforced PK/FK, do that too; and choose these keys deliberately, since they're now doing double duty as both a performance decision and documentation an agent reads for free.

7. Let the engine's own statistics describe the data

Your warehouse already computes a lot about your data. Surface it instead of making the agent go and find out.

At the column level, Postgres' pg_stats and DuckDB's SUMMARIZE expose helpful statistics e.g. n_distinct, most_common_vals, histogram_bounds, min/max, null %. These let an agent interpret a column before it queries: the real range of a date column before it writes a BETWEEN, or whether a status column is skewed 95% to one value. (Availability varies: Snowflake and BigQuery expose no user-readable column-stats view, so lean on comments and enums there.)

SUMMARIZE orders;
-- column_name | min | max | approx_unique | null_percentage | ...  (per column)

At the table level, row counts and byte size tell the agent what volume it is working with so it can adjust its SQL accordingly. This also serves as fact-vs-dimension routing, so an agent can tell a 50M-row fact table from a 12-row lookup and pick the right driving table in a join.

A word of caution in multi-tenant setups: on a table with row-level security, Postgres hides pg_stats from a non-privileged reader (you get nothing), while the row counts here (reltuples, n_live_tup) are RLS-blind and report the whole-table volume regardless of policy. Treat both carefully when tenants share a table.

What this won't do

This gets you discovery and legibility, not a full metrics platform. The main thing you give up versus a dedicated tool like Cube is caching and pre-aggregation: an agent that fans out into dozens of follow-up questions hits your warehouse, and your bill, each time. If controlling cost at high query volume is a hard requirement, that's where a semantic-layer product still earns its place.

One last thing to keep in mind if your agents are external or per-tenant: metadata itself can leak. Table names, comments and RLS-blind row counts are readable through the catalog, so findable is not the same as safe to expose. Scope what each agent's role can read.

Wrapping up

By applying some or all of the above tips depending on your current warehouse setup, you can create a remarkably effective foundation for agent queries without adopting a completely new tool or framework.

However, this isn't all there is to it. Agents will also need the right tools to explore and query your new semantic layer effectively.

Our next article will cover what a good agent toolset looks like, whether you are handrolling your own agents or creating a data MCP for your users.

DEV Community: Max Mealing