Semantic Layer Showdown: Snowflake vs Databricks for AI Agents

Background: Semantic Layers Driven by AI Agents

Semantic layers are not new, but the rise of AI agents has made them essential. Previously, inconsistent metrics only caused report discrepancies resolved by humans. Now, LLM-driven agents must autonomously decide which tables, fields, and aggregations to use when answering business questions like "show net revenue by region." Without an authoritative, machine-readable metric definition, LLMs can only guess.

Snowflake's approach combines LLM reasoning with rule-based definitions: Cortex Agents read semantic view definitions to generate SQL against physical tables. Databricks similarly decouples measure definitions from dimensions, allowing metrics to be defined once and reused across any grouping. In the BI era, semantic layers were a nice-to-have; in the AI agent era, they are indispensable grounding for trustworthy AI-generated queries.

Conceptual Comparison: Two Semantic Layer Implementations

Despite shared goals, the abstraction models differ.

Snowflake Semantic View

A schema-level database object storing business concepts as metadata. It defines three core elements on top of logical tables:

Facts : Row-level attributes answering "how many," serving as building blocks for dimensions and metrics.

Metrics : Aggregated business KPIs (SUM, AVG, COUNT) such as "total revenue" or "profit margin."

Dimensions : Categorical attributes answering "who/what/where/when," used for grouping and filtering.

Databricks Metric View

Focuses on the metric itself, with a modeling language around source, fields, measures, and filters. Supports star and snowflake schemas with multi-level joins for entity relationships.

Observation: Snowflake positions semantic views as metadata objects within the database, clarifying governance boundaries. Databricks leverages Unity Catalog to bind the semantic layer with unified governance and lineage. The former adds a business dictionary to the database; the latter grows a metric layer inside the governance hub. Different paths converge toward "semantics as governance."

Core Capabilities and Code Examples

Both platforms provide production-grade capabilities beyond modeling. Databricks supports composability and window metrics for rolling averages, period-over-period comparisons, and cumulative values, plus query-time parameter binding for multiple query variants from a single view. Performance-wise, materialization pre-computes and incrementally refreshes aggregates, with the query engine automatically rewriting queries to hit materialized views when appropriate.

Snowflake allows creating a semantic view directly via SQL. Using a typical star schema, the DDL looks like:

CREATE SEMANTIC VIEW sales_analytics
TABLES (
  orders AS orders_tbl PRIMARY KEY (order_id),
  customers AS customers_tbl PRIMARY KEY (customer_id)
)
RELATIONSHIPS (
  orders (customer_id) REFERENCES customers (customer_id)
)
FACTS (
  orders.amount AS amount
)
DIMENSIONS (
  customers.region AS region,
  orders.order_date AS order_date
)
METRICS (
  -- Net revenue = discounted revenue, single definition
  orders.net_revenue AS SUM(amount * (1 - discount))
);

The key point: net_revenue's calculation is defined once here, ensuring consistent aggregation whether users ask "net revenue by region" or "monthly net revenue."

Databricks Metric View uses YAML (with built-in validation at creation), conceptually equivalent to:

version: 0.1
source: samples.tpch.orders
dimensions:
  - name: region
    expr: region
  - name: order_month
    expr: date_trunc('MONTH', o_orderdate)
measures:
  - name: net_revenue
    expr: SUM(o_totalprice * (1 - o_discount))
  - name: revenue_per_customer
    expr: SUM(o_totalprice) / COUNT(DISTINCT o_custkey)

Side by side, the code reveals a shared philosophy: measure definitions separated from dimensions, defined once and reused across arbitrary dimensions.

Best Practices

Semantic layers are not "build once, done" objects but long-term governed data assets. Combining both vendors' documentation, best practices fall into three layers:

1. Modeling Layer – Start with Simple Star Schemas

Snowflake recommends designing the business data model first (entities, relationships, key metrics and dimensions), then mapping to physical tables, and starting with a simple star schema. High-quality descriptions, clear relationships, and well-defined metrics and filters directly determine agent answer accuracy.

2. Development and Deployment Layer – Data Productization + CI/CD

Treat semantic views as data engineering pipeline products: define ownership, control access via RBAC, data masking, and row-level policies, and deploy via automation (CI/CD), dbt, and BI integration. Encoding definitions as YAML/DDL enables version control and automated deployment.

3. AI Accuracy Layer – Continuous Tuning

This is a new layer for the AI era. Snowflake suggests curating a repository of verified "gold queries," using verified queries to reverse-optimize the model, guiding SQL generation with custom instructions, and improving literal matching with Cortex Search. Databricks enhances agent accuracy and cross-tool display consistency through agent metadata (synonyms, display names, formatting rules).

Best Practices Summary Modeling : Star schema first, business before physical, high-quality descriptions and relationships. Development & Deployment : Data productization, RBAC/masking/row-level policies, CI/CD, dbt integration. AI Tuning : Gold queries, verified query optimization, custom instructions, agent metadata.

Conclusion

Snowflake and Databricks converge on embedding "define business metrics once, ensure consistency everywhere" into their platform cores. Today, with AI agents directly fielding business questions, the semantic layer has shifted from a BI-era bonus to a mandatory foundation for trustworthy AI-driven analytics. For data teams, the earlier core metrics are codified in a governable way, the more control they retain over metric definitions in the AI wave.