RAG vs SQL: Por que tu agente de IA deberia consultar, no crear embeddings

# The RAG approach to answering "What was revenue in Q3?"

# Step 1: Chunk your tables into text
chunks = []
for row in orders_table:
    chunks.append(f"Order {row.id}: customer {row.customer}, "
                  f"amount ${row.amount}, date {row.date}")

# Step 2: Embed chunks
embeddings = openai.embed(chunks)  # $0.0001 per chunk, 1M rows = $100

# Step 3: Store in vector DB
pinecone_index.upsert(vectors=embeddings)  # $70/month for 1M vectors

# Step 4: At query time, retrieve "relevant" chunks
query_embedding = openai.embed("What was revenue in Q3?")
results = pinecone_index.query(query_embedding, top_k=20)

# Step 5: Ask LLM to synthesize
answer = openai.chat(
    f"Based on these records: {results}\n"
    f"Answer: What was revenue in Q3?"
)
# Returns: "Based on the retrieved records, Q3 revenue appears to be
#           approximately $2.3M" (actual answer: $4.7M)

SELECT SUM(amount) AS revenue
FROM orders
WHERE created_at >= '2024-07-01'
  AND created_at < '2024-10-01'

from dataspoc_lens import LensClient

client = LensClient()

# Agent discovers what data exists
tables = client.list_tables()
# ['raw.postgres.orders', 'raw.postgres.customers', 'raw.postgres.products']

# Agent inspects schema
schema = client.get_schema("raw.postgres.orders")
# [{"name": "id", "type": "INTEGER"},
#  {"name": "customer_id", "type": "INTEGER"},
#  {"name": "amount", "type": "DECIMAL"},
#  {"name": "created_at", "type": "TIMESTAMP"}, ...]

# Agent writes and executes SQL
result = client.query("""
    SELECT SUM(amount) AS revenue
    FROM raw.postgres.orders
    WHERE created_at >= '2024-07-01'
      AND created_at < '2024-10-01'
""")
# [{"revenue": 4712843.00}]

# ingest.py — runs daily, takes 45 minutes for 1M rows
import pandas as pd
from langchain.text_splitter import RecursiveCharacterTextSplitter
from langchain_openai import OpenAIEmbeddings
from langchain_pinecone import PineconeVectorStore

df = pd.read_parquet("s3://company-lake/raw/postgres/orders/")

# Convert rows to text chunks
documents = []
for _, row in df.iterrows():
    text = f"Order {row['id']}: {row['customer_name']} purchased "
    text += f"${row['amount']:.2f} on {row['created_at']}"
    documents.append(text)

# Split and embed
splitter = RecursiveCharacterTextSplitter(chunk_size=500)
chunks = splitter.create_documents(documents)
embeddings = OpenAIEmbeddings()
vectorstore = PineconeVectorStore.from_documents(chunks, embeddings, index_name="orders")

# query.py — at query time
retriever = vectorstore.as_retriever(search_kwargs={"k": 50})
docs = retriever.get_relevant_documents("What was Q3 revenue?")
# Gets 50 random-ish order records out of 250,000 Q3 orders

# query.py — that's it, no ingest step needed
from dataspoc_lens import LensClient

client = LensClient()
result = client.query("SELECT SUM(amount) FROM orders WHERE quarter = 'Q3-2024'")
# Scans all 250,000 rows in 0.3 seconds, returns exact answer

┌─────────────────────────────────────────────────┐
│                  AI Agent                         │
├─────────────────────────────────────────────────┤
│                                                   │
│  Structured data?  ──► DataSpoc Lens (MCP + SQL) │
│                                                   │
│  Unstructured docs? ──► RAG (vector search)      │
│                                                   │
└─────────────────────────────────────────────────┘

from dataspoc_lens import LensClient
from langchain_pinecone import PineconeVectorStore

lens = LensClient()
vectorstore = PineconeVectorStore(index_name="docs")

def route_question(question: str) -> str:
    """Route to SQL for data questions, RAG for document questions."""
    data_keywords = ["revenue", "count", "average", "total", "growth",
                     "last month", "quarterly", "how many", "top 10"]

    if any(kw in question.lower() for kw in data_keywords):
        # SQL path — exact answers from structured data
        result = lens.ask(question)
        return result["answer"]
    else:
        # RAG path — semantic search over documents
        docs = vectorstore.similarity_search(question, k=5)
        return synthesize_from_docs(docs, question)