Structured Data & LLMs Lab — Text-to-SQL, Semantic Layers & Metadata RAG

The Core Dilemma

Tabular and transactional data at scale should NEVER be converted into text chunks or vector embeddings like PDFs. The database engine must execute heavy aggregations.

The fundamental challenge is decoupling the LLM's reasoning from compute execution: the LLM understands user intent, while SQL/Analytical engines process the data.

⚡ Key Architecture Goal

Never pass millions of raw rows to the LLM. Pass metadata/DDL ➔ Generate Query ➔ Execute in Database ➔ Return aggregated summary.

The 4 Enterprise Patterns

Pattern 1

Text-to-SQL / Text-to-DSL

LLM receives relevant DDL & schema metadata, generates SQL, and database engine executes query. High speed & low cost.

Pattern 2

Semantic Layer & Tool Calling

LLM interacts with predefined metrics via Cube/dbt/Looker APIs. Zero SQL written by LLM; 100% deterministic KPIs.

Pattern 3

Metadata RAG + Schema Routing

Vector search over data catalogs to select only the top 2-3 relevant tables out of 500+ before prompting the LLM.

Pattern 4

Code Generation (DataFrame Sandbox)

LLM generates Python/PySpark code to execute complex multi-step transformations in a sandbox REPL environment.

🔍 Interactive Query Simulator

Select an executive question to see how Text-to-SQL vs Semantic Layer handles it.

USER INTENT

Select a query above...

Approach 1: Raw Text-to-SQL

LLM invents SQL query from raw DDL schemas.

GENERATED SQL

-- Select a prompt to inspect

⚠️ Risk: Hallucinating metric calculation, incorrect JOINs or missing WHERE filters.

Approach 2: Semantic Layer (Tool Calling)

LLM emits Tool Call payload. Semantic layer (Cube/dbt) generates certified SQL.

LLM TOOL CALL PAYLOAD

// Select a prompt to inspect

✅ Guaranteed Accuracy: LLM never writes SQL; metric logic is certified in code.

🌐 Metadata Vector RAG & Schema Router

When an enterprise database has 500+ tables, DDL schemas cannot fit in the prompt. Metadata RAG performs vector search over table descriptions to extract ONLY relevant DDL tables.

ENTERPRISE CATALOG (500+ TABLES INDEXED)

dim_employeesSelected (0.94 similarity)

fact_salary_historySelected (0.89 similarity)

fact_inventory_movementsIgnored

dim_vendor_contractsIgnored

FILTERED DDL CONTEXT FOR LLM PROMPT

CREATE TABLE dim_employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  hire_date DATE,
  status VARCHAR(20)
);

CREATE TABLE fact_salary_history (
  salary_id INT PRIMARY KEY,
  employee_id INT REFERENCES dim_employees(employee_id),
  monthly_salary NUMERIC(10,2),
  effective_date DATE
);

🛡️ AST Security Linter & Guardrails Sandbox

Test SQL queries generated by LLMs against an Abstract Syntax Tree (AST) validator to block destructive DDL/DML, enforce read-only SELECTs, and append LIMIT clauses.

AST VALIDATION RESULT

Status: PASSED
Action: SELECT allowed
Safe Query: SELECT name, department FROM dim_employees WHERE status = 'active' LIMIT 100
Permissions: Read-Only Role Verified
        

📊 Enterprise Decision Matrix

Which approach should you use for your specific architecture?