The Semantic Compression Problem: Engineering AI-Ready Views for Complex SQL

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.

1. SQL describes computation. Semantics describe meaning.

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.

1. The problem with a 500-line query

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.

1. Text-to-SQL exposed the deeper problem

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.

1. A semantic view should not simply contain your entire SQL query

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.

1. Start with grain, not columns

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.

1. Think in entities, dimensions, facts and metrics

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)

becomes:

Order Count

Now the model doesn't need to rediscover the meaning every time.

1. Relationships are part of the semantics

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.

For example:

"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.

1. Metrics should be first-class concepts

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:

SUM(revenue) / NULLIF(COUNT(DISTINCT order_id), 0)

we have:

Average Order Value

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.

1. The semantic layer becomes an AI interface

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

1. Why constrained generation matters

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.

1. Semantic modeling is also a query-optimization problem

There is another misconception:

"If the semantic layer is correct, performance is automatically solved."

It isn't.

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.

1. Don't create one giant semantic universe

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.

1. Descriptions are not decoration

Consider:

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.

1. A practical semantic-view pattern

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: country description: Customer billing country. expr: country
  • name: segment description: Customer business segment. expr: segment

• name: orders

  description: >
  One row per customer order.

  primary_key:
  columns:
  - order_id

  dimensions:

  • name: order_date description: Date on which the order was created. expr: order_date

  facts:

  • name: order_amount description: Monetary value of the order. expr: order_amount

  metrics:

  • name: total_revenue description: Total revenue from orders. expr: SUM(order_amount)
  • name: order_count description: Number of distinct customer orders. expr: COUNT(DISTINCT order_id)

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.

1. What should remain outside the semantic layer?

This is just as important.

Don't blindly move everything upward.

Keep implementation-specific transformations where they belong.

For example:

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.

1. A semantic view is a contract

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.

1. Testing the semantic layer

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.

1. The semantic compression pipeline

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.

1. The deeper implication for AI

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.

1. Final perspective

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?"

to:

"Which relationships are valid?"

to:

"Which metric definition should I use?"

to:

"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

1. 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.

2. 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.

3. 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.

4. Snowflake — Semantic Views: Modeling and Best Practices.
   Current guidance covering business-domain modeling, descriptions, relationships, metrics, filters, verified queries, and accuracy iteration.

5. Snowflake — Semantic View YAML Specification.
   Current specification for logical tables, dimensions, facts, metrics, relationships, verified queries, tags, and native semantic-view objects.