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

A developer argues that large analytical SQL queries fail not because SQL is hard but because meaning is distributed across CTEs, joins, and derived metrics, and proposes semantic views as a compression layer that exposes business concepts like Gross Revenue and Net Revenue above physical computation. The writeup cites Snowflake's schema-level semantic views and text-to-SQL research including the Spider benchmark and RAT-SQL to show that language models must reconstruct schema meaning when the semantic layer is weak. The recommended approach is to split a large transformation into physical implementation and business semantics before modeling.

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.