How Does a Semantic Layer Unlock Your Data for Agentic AI

Teams connect an AI to their warehouse expecting instant answers and get confident SQL that counts the wrong things. The model is rarely the problem. A raw schema carries none of the business meaning the question depends on. The fix is a silver layer the Artificial Intelligence can read: predictable names, prescriptive descriptions, business rules defined once in a governed semantic layer.

We tested this end to end, Airbyte into Snowflake, dbt models, an AI agent querying over MCP, and this article covers what held up and what we changed.

A quick anchor: the silver layer comes from the medallion architecture, the layered pattern most modern warehouses follow. Bronze holds raw data exactly as ingested; silver holds cleaned, conformed tables with consistent names and types; gold holds business-level marts and aggregates.

Each layer refines the one below it, and silver is where data first becomes trustworthy enough for an AI to read.

1. How does AI consume data from the silver layer?

Two AI paradigms touch your warehouse: coding agents that help developers write SQL and dbt models, and natural-language analytics tools that answer business questions directly. The silver layer serves both through the same semantic layer.

1.1 Cortex Code (CoCo) vs Claude Code: which agent writes the pipeline?

These are agentic coding tools like peers, not strictly competitors. Cortex Code reached general availability in February 2026; Snowflake rebranded it “CoCo” at Summit in June 2026 and shipped a Claude Code integration in the same release.

	Cortex Code / CoCo	Claude Code
Data awareness	Deep, automatic – reads your schemas, catalog, RBAC, lineage inside Snowflake	Gains awareness through connection (e.g. a Snowflake MCP server) and your repo
Governance	Runs inside Snowflake’s governance boundary	Depends on the connection’s permissions
Scope	dbt + Airflow + Snowflake-native workflows	General-purpose; any codebase, any tool

CoCo is the safer default when everything lives in Snowflake. Claude Code wins when work spans systems beyond the warehouse, in our project, it connected through a Snowflake MCP server and queried the warehouse directly.

Read more: The Data Architecture Decisions That Actually Matter

1.2 How does a natural-language question become a query?

A raw schema alone produces unreliable SQL. The mapping from business language to physical tables happens through a semantic model: logical tables, dimensions, measures, and the relationships that make joins correct.

The AI only picks the right metrics and dimensions; the engine assembles the SQL deterministically from entities and keys, relationships, measures, dimensions, synonyms, and verified queries, in that order.

2. What naming conventions make the silver layer legible to AI?

Predictability is the single biggest win: layer prefixes (stg_, int_, business-domain mart names), consistent suffixes (_id, _at), snake_case, business-meaningful names. fct_orders with a column order_id tells the model far more than t_ord_01.oid.

2.1 What happens when the same column name appears in different tables?

order_id is a primary key in orders and a foreign key in order_items, payments, and shipments. A naive text-to-SQL tool joins, counts order_id, and returns one row per line item — inflated counts.

The fix is structural: declare the entity once and define the metric explicitly (number_of_orders = count_distinct(order_id) on orders). The collision never reaches the AI.

2.2 What about the same business term meaning two different things?

Column collisions have a known fix; term collisions are the trap nobody plans for. In our time-tracking project, “pending hours” meant two unrelated things: hours under the weekly target, and hours awaiting manager approval. Ask an AI about “pending hours” and it silently picks one meaning and answers with confidence.

Fix it in the semantic model, not the prompt: give each concept its own metric (weekly_hours_gap vs. hours_pending_approval), attach synonyms deliberately, and never let one business word map to two measures. The semantic layer is where you disambiguate a term once, for every consumer.

Read more: Your AI Strategy Has a Data Problem

3. Do table and column descriptions actually help AI?

Yes. Cortex Analyst and MCP-connected agents read semantic-model metadata to decide what a field means. But descriptions do two distinct jobs, and most teams only write the first.

3.1 What’s the difference between descriptive metadata and prescriptive rules?

Descriptive metadata says what a field is: “booked_hours: hours logged for the entry’s date.” Prescriptive rules say how to query it correctly: “only count rows with status = ‘approved’”; “a positive gap means under target.”

In our project, agents honored prescriptive comments at query time, they changed the SQL generated. Treat comments as an instruction channel, not just documentation.

3.2 Where should descriptions live?

In increasing order of value: dbt YAML description fields (version-controlled, persisted to Snowflake as object comments via persist_docs); the semantic view (descriptions, synonyms, prescriptive rules); and verified queries, which pin the exact SQL for recurring questions.

4. Where should business rules live: dbt models or the semantic layer?

Keep silver clean, but the practical answer is more specific than “put logic in the semantic layer.” Compute the rules in dbt marts; use the semantic layer as the governed contract that names, describes, and serves the finished numbers.

4.1 Which semantic layer: Snowflake semantic views or dbt MetricFlow?

Pick based on who queries you. If your AI consumers speak SQL directly — Cortex Analyst, Snowflake Intelligence, or an agent on a Snowflake MCP server — use Snowflake semantic views: schema-level objects the agent can discover, inspect, and query through the SEMANTIC_VIEW() table function, inheriting native RBAC.

If your consumers go through dbt Cloud’s APIs, MetricFlow gives the same guarantees portably across warehouses. For a Snowflake-plus-agent stack, semantic views worked without an extra service in between.

4.2 How do simple and complex business rules differ?

A simple rule: “gross_revenue = price × quantity” aggregates one table. Define it as a metric and the AI selects it. A complex rule: net_revenue = (price × quantity) − tax1 − tax2 − tax3, where each tax comes from another table, should not be assembled at query time.

Pre-compute it in dbt: joins and conditionals in int_ models, materialized at the right grain in a fact model, exposed as a finished metric through the semantic view.

In our project, weekly hours gap, holiday gap, and billable classification all lived in dbt rollup models (fct_weekly_hours, fct_quarterly_hours); the semantic view’s job was naming and governance. The heavier the rule, the earlier in dbt you materialize it. The AI should only ever select a finished, named metric, never reconstruct one.

4.3 Have you planned grain coverage?

This gap bit us hardest, and most architecture guides never mention it. Our rollups existed at weekly and quarterly grain; the moment someone asked for a monthly team summary, the question fell through to raw booking tables with hand-written SQL, the past every rule and guardrail we had built.

Before you build, enumerate the grains and entities users will ask about like day, week, month, quarter; per-user, per-client, per-project, and build a fact at every grain you intend to serve. A missing grain is not a smaller semantic layer, but it is a hole the AI falls through.

4.4 What does semantic leakage look like in an AI stack?

Semantic leakage is business logic escaping the governed layer into ungoverned copies. In BI, that means workbook formulas. In an AI stack, the escape route is prompts and agent files, and we watched it happen: when our monthly summary could not be expressed in the semantic view, its SQL ended up in an agent skill document, outside the warehouse, invisible to BI, governed by nobody.

The rule we adopted: every fallback query is backlog for a new model or metric. If the AI needs bespoke SQL to answer a recurring question, the semantic layer has a gap, and the fix is a model, not a longer prompt.

Read more: When to Move Your Data Out of Spreadsheets?

5. How do LGPD and PII rules apply when AI queries your data?

LGPD governs personal data identically whether a human or an AI runs the query — AI creates no exemption, and it raises the bar on purpose limitation, minimization, and explainability. GDPR applies in parallel for EU data subjects, and Snowflake’s tooling treats both similarly.

5.1 Should you mask PII?

Yes, and warehouse-level masking applies to AI queries automatically. Snowflake enforces dynamic and tag-based masking, row-access, and projection policies at the data layer, so Cortex Agents and semantic-view queries inherit them: the AI cannot perceive data its human operator cannot access.

Use tag-based masking so protection propagates as datasets grow, AI_REDACT for PII in free text, and Cortex Guard for LLM input/output controls.

5.2 Warehouse-native model or local model?

A warehouse-native model (Cortex) runs next to your data inside Snowflake’s governance boundary: no data movement, RBAC and masking inherited, no training on your data, the shortest path to LGPD compliance.

A self-hosted local model shifts the whole compliance and infrastructure burden onto you; worth it only under hard data-residency requirements.

6. How do you keep AI queries cheap, small, and safe?

Cortex queries consume credits, and an unguarded agent can scan enormous tables to answer a one-number question. Two controls worked for us. First, the semantic layer keeps results small by construction: metrics return aggregates, so “how many hours last quarter?” returns one row, not a million.

Second, an explicit agent contract: a read-only role; row caps (around 20 detail rows, enough to answer, small enough to stay cheap); and scoped discovery (SHOW … IN DATABASE analytics_db, never account-wide scans). The constraints that keep credit consumption predictable also keep answers trustworthy.

7. What should you build first?

Start with clean silver tables and predictable names. Model business rules in dbt at every grain users will ask about, and expose them through one semantic layer — with prescriptive comments the AI must follow. Mask PII at the data layer so governance travels with every query.

Then treat every question the layer cannot answer as a modeling ticket. The AI is only ever as good as the contract you hand it.

Discover where your organization stands on the path from AI experimentation to true business transformation, and what to do next.