Large SQL queries rarely become difficult because SQL itself is difficult.
They become difficult because meaning gets distributed across the query.
A 20-line query can usually be understood by reading it top to bottom.
A 500-line analytical query is different.
Its meaning may be distributed across:
nested CTEs
multiple joins
aggregation levels
derived metrics
business filters
date logic
slowly changing dimensions
window functions
aliases
implicit assumptions
technical column names
duplicated business rules
At that point, adding another abstraction isn't necessarily the answer.
The real question becomes:
How do we compress the physical complexity of a data system into a semantic representation that humans, BI systems, and AI agents can reliably reason about?
That is where semantic modeling becomes interesting.
Consider a simple calculation:
SUM(unit_price * quantity)
Technically, this is just an aggregation.
But suppose the business calls it:
Gross Revenue
Now consider:
SUM(unit_price * quantity * (1 - discount_rate))
The business might call that:
Net Revenue
The SQL tells us how the value is calculated.
The semantic layer tells us what the value means.
That distinction becomes increasingly important as analytical systems become consumed by AI.
Snowflake describes semantic views as schema-level objects that model business entities, relationships, dimensions, facts, and metrics on top of physical data.
The abstraction therefore becomes:
Physical Data
↓
SQL Computation
↓
Business Semantics
↓
Human / BI / AI Consumption
The semantic layer isn't supposed to hide SQL.
It is supposed to expose the right meaning above SQL.
Imagine an analytical query:
WITH orders AS (
...
),
customers AS (
...
),
products AS (
...
),
daily_orders AS (
...
),
customer_metrics AS (
...
),
regional_metrics AS (
...
),
ranked_products AS (
...
)
SELECT ...
The query may be completely correct.
But correctness isn't the same thing as usability.
A new analyst now has to understand:
Which table represents the customer?
What is the grain of orders?
What does revenue mean?
Which date should be used?
Which joins are one-to-many?
Which filters are mandatory?
Which aggregation is authoritative?
Which calculation is business-defined?
Which CTE exists only as an implementation detail?
This is semantic complexity.
And semantic complexity becomes particularly important when an AI system needs to generate SQL.
Natural-language-to-SQL research has repeatedly shown that generating correct SQL requires more than understanding the user's sentence.
The model also has to understand the database schema and relationships.
The Spider benchmark demonstrated this problem explicitly by evaluating text-to-SQL across 200 databases, 138 domains, and thousands of complex SQL queries.
RAT-SQL later showed how important schema encoding and schema linking are when translating natural language into SQL, particularly when the system encounters previously unseen schemas.
This leads to an important architectural observation:
Natural Language
↓
Intent
↓
Semantic Concepts
↓
Schema Mapping
↓
Relationships
↓
SQL
If the semantic layer is weak, the model has to reconstruct too much meaning from raw database structure.
That is a difficult problem.
This is one of the easiest mistakes to make.
Suppose you have a 700-line SQL transformation.
It is tempting to think:
"I'll put the entire query inside a semantic view."
But that doesn't necessarily create a good semantic model.
Instead, first separate the query into two categories.
Physical implementation
CTEs
Temporary transformations
Technical joins
Deduplication
Intermediate calculations
Staging logic
Optimization logic
Business semantics
Customer
Order
Product
Revenue
Profit
Conversion Rate
Active Customer
Order Date
Region
Product Category
The semantic layer should primarily expose the second category.
A useful mental model is:
700-line SQL
│
┌──────────┴──────────┐
│ │
Implementation Meaning
│ │
▼ ▼
SQL complexity Business concepts
│
▼
Semantic View
This is semantic compression.
We're not necessarily reducing the amount of computation.
We're reducing the amount of meaning a consumer has to reconstruct.
Before creating dimensions or metrics, ask:
What does one row represent?
For example:
orders
→ one row per order
order_items
→ one row per order item
customers
→ one row per customer
daily_sales
→ one row per customer/product/day
This sounds basic.
It isn't.
Grain determines whether a metric is valid.
Consider:
Orders
1 customer
│
├── Order A
├── Order B
└── Order C
Now imagine joining:
Orders
×
Order Items
×
Product Events
If one order contains 4 items and each item has 3 events, careless aggregation can create:
1 × 4 × 3 = 12 rows
A metric such as:
SUM(order_amount)
can now be multiplied unintentionally.
The SQL may execute successfully.
The result can still be semantically wrong.
This is why grain is more fundamental than syntax.
A useful semantic decomposition is:
Entity
├── Dimensions
├── Facts
└── Metrics
For an e-commerce domain:
Customer
├── customer_id
├── country
├── segment
└── signup_date
Order
├── order_id
├── order_date
├── status
└── order_amount
Product
├── product_id
├── category
├── brand
└── price
Then metrics:
Total Revenue
Average Order Value
Order Count
Customer Count
Conversion Rate
The important transformation is:
SUM(amount)
becomes:
Total Revenue
and:
COUNT(DISTINCT order_id)
Order Count
Now the model doesn't need to rediscover the meaning every time.
A semantic model is not merely a dictionary of column names.
It is also a representation of relationships.
Customer
│
│ 1:N
▼
Order
│
│ 1:N
▼
Order Item
│
│ N:1
▼
Product
These relationships constrain how questions can be answered.
"Revenue by product category"
requires a valid path:
Revenue
↓
Order
↓
Order Item
↓
Product
↓
Category
Without explicit relationship information, an AI system may have to infer the join path from schema names.
That is precisely the kind of schema reasoning that text-to-SQL research has identified as difficult.
Snowflake's semantic-view guidance similarly emphasizes explicitly defining relationships required by the questions the model must answer.
Consider this calculation:
SUM(revenue) / NULLIF(COUNT(DISTINCT order_id), 0)
Technically:
SQL expression
Semantically:
Average Order Value
Once defined as a metric, it becomes reusable.
Instead of every analyst writing:
we have:
with a single authoritative definition.
Snowflake's current semantic-view model supports reusable metrics and filters specifically for this purpose.
This matters enormously for AI-generated SQL.
An AI shouldn't have to invent:
"What exactly does this organization mean by active customer?"
The semantic model should already know.
This is where semantic modeling becomes much more interesting.
Without semantic modeling:
User
↓
LLM
↓
Raw database schema
↓
Infer relationships
↓
Infer metric definitions
↓
Generate SQL
↓
Hope it's correct
With semantic modeling:
User
↓
Natural-language intent
↓
Semantic concepts
↓
Known entities
↓
Known relationships
↓
Defined metrics
↓
Verified examples
↓
SQL
This is not merely metadata.
It is structured context for reasoning.
Snowflake's current semantic-view architecture supports verified queries — natural-language questions paired with validated SQL — as examples that can help Cortex Analyst understand how similar questions should be answered.
That creates an interesting bridge:
Semantic Layer
+
LLM
+
Verified Queries
↓
More constrained SQL generation
An LLM has an enormous output space.
SQL doesn't.
A generated query has to satisfy a formal grammar and the target database's schema.
PICARD demonstrated this problem in text-to-SQL: unconstrained language-model generation can produce invalid SQL, while incremental parsing can constrain decoding to valid continuations. The work was published at EMNLP 2021.
This gives us an important architectural principle:
Don't ask an LLM to infer everything that your data architecture already knows.
If relationships are known, expose them.
If metrics are defined, expose them.
If certain filters are mandatory, encode them.
If certain queries are verified, preserve them.
The semantic layer reduces the space of possible interpretations.
There is another misconception:
"If the semantic layer is correct, performance is automatically solved."
Semantic correctness and execution performance are different dimensions.
Semantic Correctness
│
├── correct grain
├── correct joins
├── correct metric
└── correct business definition
Execution Performance
│
├── scan volume
├── join cost
├── aggregation cost
├── pruning
├── materialization
└── warehouse resources
A semantic model can be conceptually excellent and still generate expensive SQL.
Therefore:
Model
↓
Generate SQL
↓
EXPLAIN / PROFILE
↓
Measure
↓
Optimize
↓
Re-test semantics
Snowflake currently supports materialization of selected semantic-view dimensions and metrics as one mechanism for improving performance, while also providing native semantic-view query syntax and management capabilities.
A common architectural temptation is:
EVERYTHING
│
┌─────────────┼─────────────┐
▼ ▼ ▼
Sales Finance Marketing
│ │ │
└─────────────┼─────────────┘
▼
1 giant model
That sounds convenient.
It often isn't.
Snowflake's current modeling guidance recommends organizing semantic views around business domains and use cases, rather than simply mirroring the database. It also recommends starting with a manageable scope and avoiding irrelevant columns.
A better structure might be:
Semantic Layer
│
┌──────────────┼──────────────┐
▼ ▼ ▼
Sales Analytics Customer Product Analytics
Analytics
The objective is not:
"Expose everything."
The objective is:
Expose enough meaning to answer the intended class of questions reliably.
name: csat_score
description: "Score"
versus:
name: csat_score
description: >
Customer Satisfaction Score measured on a 1–5 scale,
where 5 indicates the highest satisfaction.
These are technically similar.
Semantically, they are very different.
The second provides information that an AI system can actually reason about.
Snowflake's current guidance explicitly calls descriptions one of the most important elements for semantic-view accuracy and recommends clear business-oriented descriptions for tables and columns.
So metadata becomes part of the model's reasoning context.
A simplified conceptual definition could look like:
name: sales_analytics
description: >
Business analytics model for understanding customer orders,
revenue, products, and regional sales performance.
tables:
name: customers
description: >
One row per customer.
primary_key:
columns:
- customer_id
dimensions:
name: orders
description: >
One row per customer order.
primary_key:
columns:
- order_id
facts:
metrics:
The exact syntax should always be aligned with the current platform specification; the important architectural idea is the decomposition into business entities, dimensions, facts, metrics and relationships. Snowflake's current semantic-view specification supports these concepts natively.
This is just as important.
Don't blindly move everything upward.
Keep implementation-specific transformations where they belong.
Raw ingestion
↓
Cleaning
↓
Deduplication
↓
Normalization
↓
Business transformations
↓
Curated analytical data
↓
Semantic layer
↓
BI / AI / Applications
A semantic layer should not become a dumping ground for:
ETL
ELT
debugging SQL
temporary transformations
one-off reports
application-specific formatting
Otherwise we simply move the complexity from one location to another.
This is perhaps the most important architectural perspective.
Think about a semantic view as a contract between:
Data Engineering
│
▼
Semantic Model
│
▼
Analytics / BI
│
▼
AI Systems
│
▼
Applications
The contract defines:
What does this entity represent?
What does this metric mean?
What is its grain?
How are entities related?
Which filters are valid?
Which calculations are authoritative?
Which questions have been verified?
This makes semantic modeling closer to interface design than simply creating another database view.
A semantic model should be tested like software.
Test 1 — Grain
Does every logical table have a clearly understood grain?
Test 2 — Join correctness
Can every supported relationship be validated?
Test 3 — Metric correctness
Does Total Revenue match the authoritative calculation?
Test 4 — Aggregation safety
Does Revenue by Region equal total Revenue?
Test 5 — Natural-language coverage
"Revenue by country"
"Average order value by month"
"Top 10 products"
Can the system generate correct SQL for each?
Test 6 — Performance
Generated SQL
↓
Query profile
↓
Bytes scanned
↓
Join behavior
↓
Execution time
Test 7 — Regression
Every semantic change should be tested against existing verified questions.
This is where semantic modeling starts looking like software engineering rather than documentation.
After working through all of this, the architecture becomes:
RAW DATA
│
▼
DATA MODELING
│
▼
COMPLEX SQL LOGIC
│
▼
GRAIN ANALYSIS
│
▼
┌──────────────────────┐
│ SEMANTIC DECOMPOSITION│
└──────────┬───────────┘
│
┌──────────┼──────────┐
▼ ▼ ▼
Entities Dimensions Metrics
│ │ │
└──────────┼──────────┘
▼
Relationships
│
▼
Business Rules
│
▼
Verified Queries
│
▼
SEMANTIC VIEW
│
┌──────────┼──────────┐
▼ ▼ ▼
BI AI Applications
│
▼
Generated SQL
│
▼
Validation
│
▼
Performance
│
▼
Feedback
This is what I mean by semantic compression.
We're taking a complicated physical system and exposing a smaller, more meaningful representation to the consumers that need to reason about it.
There is a broader lesson here.
The future of natural-language analytics isn't simply:
LLM + database
It increasingly looks like:
LLM
Semantic representation
Schema relationships
Metric definitions
Verified examples
Query constraints
Execution feedback
The database contains the data.
The semantic layer contains the meaning needed to reason over that data.
That distinction becomes increasingly important as AI systems move from answering questions to autonomously generating and executing analytical queries.
Large SQL queries are not necessarily a problem.
Unstructured meaning is.
A 1,000-line query can be correct.
A 100-line query can be semantically wrong.
And a beautifully designed semantic view can still produce expensive SQL if its underlying relationships, grain, or execution strategy are poorly understood.
The goal, therefore, isn't to eliminate SQL complexity.
It is to put complexity at the correct architectural boundary.
Physical layer
→ How data is stored
Transformation layer
→ How data is prepared
Semantic layer
→ What data means
AI / BI layer
→ What users want to know
Execution layer
→ How the answer is computed
The most useful semantic layer is not the one containing the most metadata.
It is the one that allows a human or an AI system to move from:
"What does this data mean?"
to:
"Which concepts do I need?"
"Which relationships are valid?"
"Which metric definition should I use?"
"Generate the correct SQL."
And that leads to the principle I keep coming back to:
Don't make the AI understand your entire database. Give it a semantic representation of the part of the database it actually needs to reason about.
That is the real purpose of a semantic layer.
Research & References
Yu et al. — “Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task,” EMNLP 2018.
A foundational benchmark demonstrating the difficulty of generating SQL across complex, previously unseen database schemas.
Wang et al. — “RAT-SQL: Relation-Aware Schema Encoding and Linking for Text-to-SQL Parsers,” ACL 2020.
Important research on schema representation, relationships, and mapping natural-language concepts to database structures.
Scholak, Schucher & Bahdanau — “PICARD: Parsing Incrementally for Constrained Auto-Regressive Decoding from Language Models,” EMNLP 2021.
Demonstrates how constraining language-model generation can improve validity for formal languages such as SQL.
Snowflake — Semantic Views: Modeling and Best Practices.
Current guidance covering business-domain modeling, descriptions, relationships, metrics, filters, verified queries, and accuracy iteration.
Snowflake — Semantic View YAML Specification.
Current specification for logical tables, dimensions, facts, metrics, relationships, verified queries, tags, and native semantic-view objects.