Every useful AI application runs on well-prepared data. The engineers who can clean and chunk text, store and search embeddings, feed the right context to a model and keep it all governed, mostly with SQL, are some of the most in-demand people in data right now.
This path covers the data side of LLM applications: getting documents and JSON into shape, chunking text, storing embeddings and running vector similarity search in SQL, building the retrieval step of a RAG pipeline, calling LLM functions directly from the warehouse, letting users ask questions in plain English safely, and logging and evaluating what the model does. Examples use PostgreSQL with pgvector and Snowflake, two of the most common setups. Vendor AI features change quickly, so check current docs for exact function names and models before building.
LLM applications are full of JSON: API payloads, chat transcripts, model responses, document metadata. You need to extract fields, unnest arrays and type values reliably in SQL before any of it can be searched, filtered or fed to a model.
-- PostgreSQL: pull fields out of a JSONB chat log
SELECT id,
payload->>'user_id' AS user_id,
payload->'messages'->0->>'content' AS first_message,
jsonb_array_length(payload->'messages') AS message_count
FROM chat_sessions;Models and embeddings work best on clean, reasonably sized pieces of text. Strip boilerplate, normalize whitespace, remove duplicates, then split long documents into chunks with a small overlap so an answer that spans a boundary isn't lost. Keep each chunk's source document, position and metadata so results can be cited.
CREATE TABLE doc_chunks (
chunk_id BIGSERIAL PRIMARY KEY,
doc_id BIGINT REFERENCES documents(doc_id),
chunk_no INT,
chunk_text TEXT,
section TEXT, -- metadata used later for filtering and citations
updated_at TIMESTAMP
);An embedding is a list of numbers representing the meaning of a piece of text; similar meanings produce nearby vectors. Databases now store vectors natively (pgvector in PostgreSQL, the VECTOR type in Snowflake) and rank rows by distance, so semantic search becomes an ORDER BY.
-- PostgreSQL + pgvector: 5 chunks closest in meaning to the question
SELECT chunk_id, chunk_text
FROM doc_chunks
ORDER BY embedding <=> :question_embedding -- <=> is cosine distance
LIMIT 5;
-- Snowflake equivalent (higher similarity = closer)
SELECT chunk_id, chunk_text
FROM doc_chunks
ORDER BY VECTOR_COSINE_SIMILARITY(embedding, :question_embedding) DESC
LIMIT 5;Retrieval-augmented generation (RAG) finds the most relevant chunks and passes them to the model as context. The retrieval step is SQL: apply hard filters first (tenant, permissions, document type, freshness), then rank by similarity. Filtering by who is allowed to see a document must happen here, before anything reaches the model.
SELECT c.chunk_id, c.chunk_text, d.title
FROM doc_chunks c
JOIN documents d ON d.doc_id = c.doc_id
WHERE d.tenant_id = :tenant_id
AND d.access_group IN (SELECT access_group FROM user_access WHERE user_id = :user_id)
AND d.status = 'published'
ORDER BY c.embedding <=> :question_embedding
LIMIT 8;Warehouses now expose LLMs as SQL functions, such as Snowflake Cortex's AI_COMPLETE, AI_CLASSIFY and AI_SENTIMENT, or Databricks' ai_query. You can classify support tickets, summarize reviews or extract fields from free text across millions of rows without moving data out of the platform.
-- Snowflake: classify support tickets in bulk
SELECT ticket_id,
AI_CLASSIFY(ticket_text, ['billing', 'bug', 'feature request', 'other']) AS category,
AI_SENTIMENT(ticket_text) AS sentiment
FROM support_tickets
WHERE created_at >= '2026-09-01';LIMIT sample, store the results in a table, and never put them in a view that recalculates on every dashboard refresh.Letting users ask questions in plain English means an LLM writes SQL against your data. It works far better with a curated semantic layer (clear table and column descriptions, agreed metric definitions, example questions) than with raw tables. It must also be fenced in: a read-only role, approved schemas only, row limits and timeouts.
-- A dedicated, read-only role for generated SQL
CREATE ROLE text2sql_reader;
GRANT USAGE ON SCHEMA analytics_gold TO ROLE text2sql_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_gold TO ROLE text2sql_reader;
-- No INSERT/UPDATE/DELETE, no raw or PII schemasYou can't improve what you don't measure. Log every LLM call (prompt version, retrieved chunk IDs, model, tokens, latency, cost, user feedback) to a table. Then SQL answers the questions that matter: which prompt version gets better ratings, which documents are retrieved but never helpful, and what each feature costs per day.
SELECT prompt_version,
COUNT(*) AS calls,
ROUND(AVG(latency_ms)) AS avg_latency_ms,
ROUND(SUM(cost_usd), 2) AS total_cost_usd,
ROUND(100.0 * AVG(CASE WHEN feedback = 'thumbs_up' THEN 1 ELSE 0 END), 1) AS positive_pct
FROM llm_call_log
WHERE called_at >= CURRENT_DATE - 7
GROUP BY prompt_version;AI pipelines copy data into new places: chunks, embeddings, prompts and logs. Each copy is a new place for personal data to leak. Redact or mask PII before text is chunked or sent to a model, apply the same access rules to the vector index as to the source, and set retention on prompt logs.
-- Mask before indexing: never embed raw personal data
INSERT INTO doc_chunks (doc_id, chunk_no, chunk_text)
SELECT doc_id, chunk_no,
REGEXP_REPLACE(chunk_text, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}', '[EMAIL]')
FROM staged_chunks;Build the data layer of a small AI assistant end to end.
Use warehouse LLM functions to classify and summarize a table of reviews or tickets.
Store structured JSON responses, validate them in SQL and route failures to a rejects table.
Ingest documents, chunk, de-duplicate and index them with metadata for filtered retrieval.
AI data engineering interviews combine classic SQL and pipeline questions with system design: how you'd chunk and index documents, enforce access control in retrieval, and evaluate quality and cost.
25 questions covering everything above. Score 80% or higher to earn your AI / LLM Data Engineer badge on your dashboard.
25 questions · pass at 80%+ to earn your AI / LLM Data Engineer badge on your dashboard. One-time payment, lifetime access.