AI-Native Data Warehouses (Snowflake Cortex, BigQuery AI, Databricks Mosaic AI): A 2026 Comparison

ai-native data warehouse is the 2026 category that emerged when Snowflake, BigQuery, and Databricks each folded hosted LLMs, native vector search, RAG plumbing, a model registry, and an agents framework inside the warehouse itself — no separate vector database, no separate model server, no separate orchestration plane. The pivot happened in 2024 and consolidated through 2025; by 2026 every serious data engineering interview probes whether you can name the three stacks, size their per-token cost, latency profile, RBAC surface, and RAG anatomy, and pick the right one for a given workload without reflexively reaching for an external LLM API. This is not a marketing question — it is the load-bearing architecture decision for every analytics + GenAI hybrid workload built on top of a governed data plane.

This guide is the senior-DE walkthrough you wanted the first time an interviewer asked "compare snowflake cortex, bigquery ai, and databricks mosaic ai on cost and latency," or "design a RAG pipeline on Cortex," or "why would you ever pick warehouse-native GenAI over a direct LLM API?" It walks the three canonical ai warehouse stacks — Snowflake Cortex (Cortex Functions, Cortex Search, Cortex Analyst), BigQuery AI (ML.GENERATE_TEXT() + VECTOR_SEARCH() + Gemini remote models + object tables), and Databricks Mosaic AI (Foundation Model APIs, delta-sync Vector Search, Model Serving, Agent Framework, MLflow 3 tracing, Unity Catalog governance) — the four axes interviewers actually probe (cost per token, latency, RBAC/governance, RAG plumbing), the canonical sql llm functions for each stack, and a decision matrix that survives being drawn on a whiteboard under pressure. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the design practice library →, and sharpen the streaming axis with the streaming practice library →.

---

On this page

• Why the AI-native warehouse category exists in 2026
• Snowflake Cortex — Cortex Functions, Cortex Search, Cortex Analyst
• BigQuery AI — BigQuery ML + Vertex AI + Gemini in SQL
• Databricks Mosaic AI — Model Serving, Vector Search, Agents Framework
• Decision matrix + interview signals
• Cheat sheet — AI-native warehouse recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the AI-native warehouse category exists in 2026

Three stacks, one governance plane — the 2024–2025 pivot that made "AI-native" the default architectural noun

The one-sentence invariant: an ai-native data warehouse is a data platform where hosted LLMs, native vector search, embedding functions, RAG pipelines, a model registry, and agent frameworks are first-class SQL/notebook objects governed by the same RBAC and lineage system as the tables themselves — and the pivot from "warehouse + separate vector DB + separate model server + separate orchestration" to "everything inside the warehouse" happened between 2024 and 2025 across Snowflake, BigQuery, and Databricks, forcing every senior data engineer to reason about GenAI stacks in warehouse terms rather than in AI-app terms. The pattern you pick in 2026 becomes the pattern you defend to your CFO and your CISO for the next three years, because every downstream consumer — dashboards, reverse-ETL, agents, semantic-layer clients — hard-codes assumptions about how prompts are billed, how completions are governed, how PII is masked, and how vector search fits into the same SQL surface as your fact tables.

The four axes interviewers actually probe.

• Cost per token. Every hosted-LLM invocation is billed per input token + per output token (Cortex, BigQuery AI, and Foundation Model APIs all follow this shape). But how those tokens get invoiced differs: Snowflake bills through the warehouse credits; BigQuery bills as a slot cost plus a per-invocation remote-model call; Databricks bills pay-per-token on the endpoint plus DBU compute on the cluster that made the call. Interviewers ask this axis first because "what does a million tokens cost me on your stack?" separates people who have shipped from people who have skimmed the marketing page.
• Latency. Warehouse-native LLM calls carry the warehouse's cold-start tax on top of the model's inference latency. Cortex functions in a suspended warehouse cost 3–8 s of resume before the first token. BigQuery slots warm up in ~1 s once a slot pool is reserved. Databricks Model Serving keeps a hot endpoint alive; latency is bounded by the model's inference time (200–800 ms for 100 output tokens on Llama 3.1 70B). Interviewers probe latency because chatbots die at p95 > 3 s.
• RBAC / governance. The whole point of doing GenAI inside the warehouse is that a GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.COMPLETE (or equivalent) is the same RBAC surface as GRANT SELECT ON TABLE. Every prompt, every completion, every embedded document inherits your existing lineage, audit, and row-access policies. If governance doesn't matter to you, don't bother with warehouse-native — call the LLM API directly and save the mark-up.
• RAG plumbing. All three stacks now ship native vector search: Snowflake has VECTOR types + VECTOR_COSINE_SIMILARITY; BigQuery has VECTOR_SEARCH() with IVF and TREE_AH indices; Databricks has Vector Search with delta-sync auto-indexing over Delta tables. Interviewers probe this axis because "do I still need Pinecone?" is the single most-asked GenAI-architecture question in 2026.

The 2026 reality — the three stacks are now feature-comparable, but the pricing shape and ecosystem differ.

• Snowflake Cortex is the "SQL-first" stack. SNOWFLAKE.CORTEX.COMPLETE('llama3.1-70b', prompt) runs inside a normal SELECT; embeddings become a VECTOR(FLOAT, 1024) column; Cortex Search is a hybrid vector+keyword index; Cortex Analyst is text-to-SQL over a semantic model. Best fit: teams whose analysts already write SQL and whose CFO already understands Snowflake credits.
• BigQuery AI is the "Google-Cloud-integrated" stack. ML.GENERATE_TEXT() calls a Gemini remote model; ML.GENERATE_EMBEDDING() produces embeddings for text/image/multimodal; VECTOR_SEARCH() runs against an IVF or TREE_AH index; object tables expose GCS blobs (PDFs, images) as first-class SQL rows. Best fit: teams already invested in Gemini / Vertex AI / GCS, or workloads with multimodal (image/PDF) data.
• Databricks Mosaic AI is the "open-model + agent-framework" stack. Foundation Model APIs expose Llama, DBRX, Mixtral behind pay-per-token endpoints; Model Serving hosts your fine-tuned models; Vector Search delta-syncs from Delta; Agent Framework + evaluation harness + MLflow 3 tracing give you the full production agent lifecycle; Unity Catalog governs model + data + prompt as one asset class. Best fit: teams that want to fine-tune or serve open models with lakehouse-native governance.

What interviewers listen for.

• Do you name all three stacks without prompting? — senior signal.
• Do you say "the whole point of warehouse-native is governance" in the first sentence when Cortex vs API comes up? — required answer.
• Do you push back on "just use OpenAI" with the RBAC + PII question — "who's masking my prompts before they leave the VPC?" — senior signal.
• Do you name cost per 1M tokens in the same breath as latency? — required answer.
• Do you describe warehouse-native GenAI as "LLMs and vectors as first-class SQL objects" rather than as vague "AI-integrated data platform"? — required answer.

Worked example — the three-stack comparison table

Detailed explanation. The single most useful artifact for an AI-native-warehouse interview is a memorised 5×3 comparison table (five axes × three stacks). Every senior discussion converges on this table within the first five minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical "customer-support RAG chatbot" workload that needs to reach 50k queries/day at sub-2-second latency.

• Workload. 50k RAG queries/day; average prompt 1,200 input tokens, 200 output tokens.
• Data. 5M support tickets already in the warehouse; 200k knowledge-base articles.
• Latency target. p95 < 2 s end-to-end (retrieval + generation).
• Governance. PII must not leave the corporate VPC; every prompt/completion logged.

Question. Build the 5-axis × 3-stack comparison for this workload and pick the stack.

Input.

Axis	Snowflake Cortex	BigQuery AI	Databricks Mosaic AI
Cost / 1M input tokens	credits via warehouse	slots + per-call	pay-per-token endpoint
Latency p95 (cold)	3–8 s (warehouse resume)	1–2 s (slot warmup)	~500 ms (hot endpoint)
Vector search	VECTOR type + Cortex Search	VECTOR_SEARCH() + IVF	delta-sync Vector Search
Governance	role grants on SNOWFLAKE.CORTEX.*	IAM + BigQuery ACLs	Unity Catalog on model + data
Open models	proprietary + Llama/Mistral hosted	Gemini + partner models	Llama, DBRX, Mixtral, custom

Code.

-- Same RAG query, three stacks — read the shape, not the dialect

-- Snowflake Cortex
SELECT SNOWFLAKE.CORTEX.COMPLETE(
    'llama3.1-70b',
    CONCAT('You are a support bot. Context:\n',
           LISTAGG(chunk_text, '\n\n') WITHIN GROUP (ORDER BY score DESC),
           '\n\nQuestion: ', :question)
) AS answer
FROM   TABLE(CORTEX_SEARCH_PREVIEW(
           'kb_search_service',
           :question,
           LIMIT => 5
       ));

-- BigQuery AI
WITH retrieved AS (
  SELECT base.chunk_text
  FROM   VECTOR_SEARCH(
           TABLE `prod.kb.chunks`, 'embedding',
           (SELECT ML.GENERATE_EMBEDDING(MODEL `prod.emb.text_emb`,
                                          (SELECT @question AS content))),
           top_k => 5, options => '{"fraction_lists_to_search":0.05}'
         )
)
SELECT ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS answer
FROM   ML.GENERATE_TEXT(
         MODEL `prod.llm.gemini_pro`,
         (SELECT STRUCT(CONCAT('Context:\n',
                                STRING_AGG(chunk_text, '\n\n'),
                                '\n\nQuestion: ', @question) AS prompt)
            FROM retrieved),
         STRUCT(0.2 AS temperature, 200 AS max_output_tokens));

-- Databricks Mosaic AI (SQL editor over a served endpoint)
SELECT ai_query(
    'databricks-meta-llama-3-1-70b-instruct',
    CONCAT('Context:\n',
           array_join(collect_list(chunk_text), '\n\n'),
           '\n\nQuestion: ', :question)
) AS answer
FROM   vector_search(
         index => 'main.kb.chunks_idx',
         query_text => :question,
         num_results => 5
       );

Step-by-step explanation.

1. The three code snippets illustrate the same 5-stage RAG (embed question → vector search → assemble context → LLM call → return answer) expressed in three stack-native dialects. The shapes are near-identical; the dialects diverge on function names, model identifiers, and configuration hooks.
2. Snowflake Cortex leans hardest on being SQL-native — CORTEX_SEARCH_PREVIEW is a table function, SNOWFLAKE.CORTEX.COMPLETE is a scalar function. You compose them like any other UDF; the warehouse handles the model host, the vector index, and the billing in one governed surface.
3. BigQuery AI keeps a more visible split — ML.GENERATE_EMBEDDING(MODEL ...) and ML.GENERATE_TEXT(MODEL ...) invoke remote models registered as first-class BigQuery objects, and VECTOR_SEARCH() is a table function with tunable knobs (fraction_lists_to_search) that experienced practitioners will recognise as IVF-index parameters. The verbosity is real — it's also the reason BigQuery AI is easier to reason about in cost audits.
4. Databricks Mosaic AI ships an ai_query() SQL function that fronts a Model Serving endpoint, and vector_search() fronts a delta-sync index. Behind those two SQL surfaces sit MLflow 3 tracing, the Agent Framework, and Unity Catalog. If you never open a notebook, you never see them; if you do, they are the reason Mosaic AI is disproportionately popular with teams shipping agents.
5. For the 50k-queries/day workload with sub-2s p95, the choice comes down to (a) do you already run on Snowflake / BigQuery / Databricks — pick your incumbent, (b) do you need multimodal (image, PDF) — favour BigQuery AI + object tables, (c) do you need open-model fine-tuning + agent evals — favour Databricks Mosaic AI, (d) do you want the SQL simplest to hand to analysts — favour Snowflake Cortex.

Output.

Workload characteristic	Best fit
Analyst-driven, SQL-heavy	Snowflake Cortex
Multimodal, GCS-resident	BigQuery AI
Open-model + fine-tuning	Databricks Mosaic AI
Agent framework + evals	Databricks Mosaic AI
Simplest RBAC story	Snowflake Cortex
Cheapest at high volume	benchmark per stack; usually Mosaic AI

Rule of thumb. Never pick an ai-native data warehouse stack based on "which one is trendy." Pick it based on (existing warehouse + latency budget + governance surface + open-model requirement). Draw the 5×3 table on a whiteboard first; the pick falls out of the constraints.

Worked example — what interviewers actually probe on AI-native warehouses

Detailed explanation. The 2026 senior data-engineering AI-native interview has a predictable structure: the interviewer opens with an ambiguous question ("how would you build a RAG chatbot over our support tickets?"), then progressively narrows to test whether you know the axes. The candidates who name the warehouse-native stack in sentence one score highest; the candidates who describe "we'd use LangChain and Pinecone" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you build a RAG chatbot over our warehouse data?" — invites you to name a stack.
• Follow-up 1. "Why not just use OpenAI directly?" — probes governance axis.
• Follow-up 2. "What's your latency budget?" — probes latency + warehouse-cold-start.
• Follow-up 3. "How do you handle PII in prompts?" — probes RBAC + masking.
• Follow-up 4. "What does 1M tokens cost you?" — probes cost modelling.

Question. Draft a 5-minute senior AI-native answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Stack named	"we'd use LangChain and OpenAI"	"Snowflake Cortex with a Cortex Search service"
Governance	"we'd add API-key rotation"	"prompts never leave the VPC; every call inherits table RBAC"
Latency	"should be fine"	"p95 800 ms hot; 3–5 s cold — keep the warehouse hot"
Cost	"we'll monitor it"	"$0.90 per 1M input tokens on Llama 3.1 70B; ~$1.6k/mo at 50k queries/day"
RAG anatomy	"chunk, embed, retrieve, generate"	"chunk 800 tok / 100 overlap → embed → VECTOR type → hybrid Cortex Search → COMPLETE"

Code.

Senior AI-native warehouse answer template (5 minutes)
======================================================

Minute 1 — name the stack up front
  "I'd default to Snowflake Cortex — Cortex Search service over the
   support-tickets table with CORTEX.COMPLETE calling Llama 3.1 70B.
   BigQuery AI and Databricks Mosaic AI are the alternatives if the
   data already lives there."

Minute 2 — governance
  "The whole point of a warehouse-native stack is that prompts and
   completions never leave the VPC. Every CORTEX.* call is a normal
   function invocation governed by role grants; every retrieved chunk
   inherits the row-access policy of the underlying table; PII
   masking policies apply to both input columns and the LLM output.
   If governance doesn't matter, call the LLM API directly and save
   the mark-up."

Minute 3 — latency
  "Warehouse cold-start is 3-8 seconds on Cortex; ~1 second on
   BigQuery slots; ~500 ms on Databricks Model Serving. If we need
   sub-2s p95 chatbot latency, we keep the warehouse hot with a
   resource monitor, or move to Databricks where the endpoint is
   always-on."

Minute 4 — cost
  "Cortex bills Llama 3.1 70B at approximately 1.21 credits per 1M
   input tokens. At $2/credit that's ~$2.40/1M input + ~$2.40/1M
   output — call it $5 per million total. 50k queries/day at 1400
   average tokens = 70M tokens/day = $350/day = ~$10k/month on
   compute alone. Cheaper on Mixtral 8x7B; cheapest on-prem
   fine-tuned. Cost is the single biggest reason to benchmark all
   three stacks before committing."

Minute 5 — RAG anatomy + observability
  "Chunk 800 tokens with 100-token overlap; embed with
   CORTEX.EMBED_TEXT_1024; store as VECTOR(FLOAT, 1024) column;
   hybrid vector+keyword search via Cortex Search; assemble
   context in the prompt; call CORTEX.COMPLETE with temperature
   0.2 for factual retrieval. Observability via QUERY_HISTORY on
   the CORTEX functions plus a home-grown eval harness that
   scores retrieval@5 nightly against a golden set."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the stack immediately — "Snowflake Cortex with a Cortex Search service" — signals you're a decision-maker, not a task-runner. Weak candidates dive into libraries ("we'd use LangChain and…") before naming the platform, which telegraphs zero warehouse-side experience.
2. Minute 2 addresses the governance axis before the interviewer asks. This preempts the common trap where you commit to an external LLM API, then admit you have no PII story. Naming "prompts and completions never leave the VPC" up front is senior signal.
3. Minute 3 is the latency probe. The three cold-start numbers (Cortex 3–8 s, BigQuery ~1 s, Databricks ~500 ms hot) are the exact numbers senior interviewers listen for. Naming a mitigation ("keep the warehouse hot with a resource monitor") shows you've operated in production, not just benchmarked in a notebook.
4. Minute 4 is the cost-modelling argument. Naming the per-1M-token price and multiplying by daily volume separates people who ship from people who read blog posts. Senior interviewers do not expect exact numbers — they expect the shape of the arithmetic and a rounded monthly figure.
5. Minute 5 covers RAG anatomy and observability — the two axes that separate a demo from a production system. Naming chunk size, embedding dimension, hybrid search, temperature, and an eval harness makes the answer complete. Rehearse it once; deploy it every time.

Output.

Grading criterion	Weak score	Senior score
Names stack in minute 1	rare	mandatory
Names governance fallback	rare	required
Names latency numbers	rare	senior signal
Names cost per 1M tokens	rare	senior signal
Names RAG anatomy	occasional	mandatory

Rule of thumb. The senior AI-native warehouse answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once against Cortex; port the template to BigQuery AI and Mosaic AI by swapping function names. Every stack fits the same template.

Senior interview question on AI-native warehouse selection

A senior interviewer often opens with: "You inherit a Snowflake warehouse holding 5 million support tickets and 200k knowledge-base articles. The business wants a RAG chatbot answering 50,000 questions per day with sub-2-second p95 latency, PII never leaving the VPC, and a monthly cost ceiling of $12,000 on compute. Walk me through the ai-native data warehouse you'd pick, the stack topology, and the cost + latency defence."

Solution Using Snowflake Cortex with a Cortex Search service, warm warehouse, and role-grant governance

-- Step 1 — Vector column + Cortex Search service (one-time)
CREATE OR REPLACE TABLE kb.chunks (
    chunk_id        NUMBER      PRIMARY KEY,
    doc_id          NUMBER      NOT NULL,
    chunk_text      VARCHAR(4000),
    embedding       VECTOR(FLOAT, 1024),
    tags            ARRAY
);

-- Populate embeddings for every chunk
UPDATE kb.chunks
   SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_1024(
                     'snowflake-arctic-embed-l',
                     chunk_text
                   )
 WHERE embedding IS NULL;

-- Create a Cortex Search service — hybrid vector + keyword
CREATE OR REPLACE CORTEX SEARCH SERVICE kb_search_service
  ON      chunk_text
  ATTRIBUTES tags
  WAREHOUSE = search_wh
  TARGET_LAG = '1 minute'
  AS (
    SELECT chunk_id, doc_id, chunk_text, tags
    FROM   kb.chunks
  );

-- Step 2 — RAG SQL function (one call, one round-trip)
CREATE OR REPLACE FUNCTION kb.rag_answer(question STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
    WITH ctx AS (
        SELECT LISTAGG(value:chunk_text::STRING, '\n\n')
                 WITHIN GROUP (ORDER BY value:score::FLOAT DESC) AS context
        FROM   TABLE(FLATTEN(
                   input => PARSE_JSON(
                     SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
                       'kb_search_service',
                       OBJECT_CONSTRUCT(
                         'query',   question,
                         'limit',   5,
                         'columns', ARRAY_CONSTRUCT('chunk_text')
                       )
                     )
                   ):results
               ))
    )
    SELECT SNOWFLAKE.CORTEX.COMPLETE(
        'llama3.1-70b',
        CONCAT(
          'You are a support assistant. Answer strictly from the context.\n\nContext:\n',
          context,
          '\n\nQuestion: ', question,
          '\n\nAnswer:'
        ),
        OBJECT_CONSTRUCT(
          'temperature',       0.2,
          'max_tokens',        200,
          'guardrails',        TRUE
        )
    )
    FROM   ctx
$$;

-- Step 3 — Governance: role grants + PII masking policy
GRANT USAGE   ON FUNCTION SNOWFLAKE.CORTEX.COMPLETE(STRING, STRING, OBJECT) TO ROLE chatbot_svc;
GRANT USAGE   ON FUNCTION SNOWFLAKE.CORTEX.SEARCH_PREVIEW(STRING, OBJECT)   TO ROLE chatbot_svc;
GRANT USAGE   ON FUNCTION kb.rag_answer(STRING)                             TO ROLE chatbot_svc;

CREATE OR REPLACE MASKING POLICY kb.pii_mask AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('AUDIT_ADMIN') THEN val
      ELSE REGEXP_REPLACE(val, '\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b', '[REDACTED-SSN]')
    END;

ALTER TABLE kb.chunks
  MODIFY COLUMN chunk_text SET MASKING POLICY kb.pii_mask;

-- Step 4 — Keep the warehouse hot to defeat cold start
CREATE OR REPLACE WAREHOUSE search_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND   = 600        -- 10 minutes; longer than the busiest quiet window
  AUTO_RESUME    = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY   = 'STANDARD';

Step-by-step trace.

Layer	Config	Result
Vector column	VECTOR(FLOAT, 1024) on kb.chunks	in-warehouse embedding storage
Embedding model	snowflake-arctic-embed-l	1024-dim; matches Cortex-native dim
Search service	Cortex Search + TARGET_LAG=1 min	hybrid vector+keyword; auto-refresh
Retrieval	SEARCH_PREVIEW LIMIT 5	5 chunks per query
Generation	Llama 3.1 70B via COMPLETE	governed hosted LLM
Governance	role grants + masking policy	prompts inherit table RBAC + PII mask
Warehouse	MEDIUM, AUTO_SUSPEND 10 min	hot during peak; cools after quiet windows

After the rollout, questions flow: request → kb.rag_answer(question) → embedding lookup + hybrid search returns top-5 chunks → prompt assembled → CORTEX.COMPLETE returns an answer → response back to the caller. End-to-end p95 is 800 ms with a warm warehouse; cold-start p99 is 5–6 s. PII in support tickets is transparently redacted before it enters the prompt; every invocation is logged in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY with the calling role.

Output:

Metric	Value
End-to-end p95 latency (hot)	~800 ms
End-to-end p99 latency (cold)	~5 s
Cost per query (avg 1,400 tokens)	~$0.007
Daily cost at 50k queries	~$350
Monthly compute cost	~$10.5k
Governance surface	role grants + masking policy
Vector index refresh	1-minute target lag

Why this works — concept by concept:

• Warehouse-native VECTOR type — VECTOR(FLOAT, 1024) is a first-class Snowflake column type. Embeddings live in the same table as the chunk text, governed by the same masking policy, indexed by the same clustering key. No separate vector DB, no dual-write bug, no ACL gap between "the row" and "the row's embedding."
• Cortex Search hybrid retrieval — a CORTEX SEARCH SERVICE combines vector similarity with keyword BM25 scoring and returns a ranked list. Hybrid retrieval consistently outperforms pure-vector on domain-specific corpora; you get both the recall of embeddings and the precision of keyword matching.
• Role-grant governance — GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.COMPLETE ... TO ROLE chatbot_svc is the same RBAC verb as GRANT SELECT ON TABLE. Auditors see one policy surface; the chatbot service account can only invoke the Cortex functions your role grants allow.
• PII masking policy — CREATE MASKING POLICY applied to kb.chunks.chunk_text runs on every read, including reads that feed prompts. The LLM sees [REDACTED-SSN], not a real SSN. This is the "prompts never leave the VPC with PII" guarantee.
• Cost — Llama 3.1 70B on Cortex is ~1.21 credits per 1M input tokens at time of writing; MEDIUM warehouse holds ~$4/hour warm. 50k queries/day × 1,400 tokens = 70M tokens/day → ~$85/day tokens + $96/day warehouse = ~$180/day = ~$5.4k/mo. Cheaper on Mixtral 8x7B; more expensive with larger warehouses. Compared to routing to OpenAI GPT-4o at ~$5/1M input + $15/1M output, warehouse-native breaks even around ~10M tokens/day.

SQL
Topic — sql
SQL LLM-function and vector-search problems

Practice →

Design Topic — design Design problems on AI-native warehouse architectures

Practice →

---

2. Snowflake Cortex — Cortex Functions, Cortex Search, Cortex Analyst

snowflake cortex treats hosted LLMs and vectors as first-class SQL — one governance surface, one credit model, one dialect

The mental model in one line: snowflake cortex is the Snowflake feature family that exposes hosted LLMs (COMPLETE, SUMMARIZE, TRANSLATE, SENTIMENT), embedding functions (EMBED_TEXT_768, EMBED_TEXT_1024), a native VECTOR column type with VECTOR_COSINE_SIMILARITY and VECTOR_L2_DISTANCE, a hybrid Cortex Search service, a Cortex Analyst text-to-SQL layer over a semantic model, and Document AI for PDF extraction — all callable from ordinary SELECTs, governed by ordinary role grants, and billed as ordinary Snowflake credits. Every senior data engineer who has shipped an internal GenAI app on Snowflake has built at least one of these; the primitives compose exactly the way SQL functions compose, which is the entire pitch.

The four axes for Snowflake Cortex.

• Cost. All Cortex functions bill against your standard warehouse credits, with an additional per-token surcharge on the LLM functions (published in the Cortex consumption table — Llama 3.1 70B is ~1.21 credits per 1M input tokens; Mixtral 8x7B is ~0.22 credits per 1M input tokens; Arctic-embed-l is ~0.02 credits per 1M tokens). Cost lives on your existing Snowflake invoice; no separate billing surface, no separate contract.
• Latency. LLM function latency = warehouse resume (0 s if warm, 3–8 s cold) + LLM inference (100–800 ms for typical 200-token completions). Embedding latency is dominated by warehouse resume; per-row embedding is milliseconds. Cortex Search latency is 100–400 ms for the retrieval step, competitive with dedicated vector databases.
• Governance. GRANT USAGE ON FUNCTION SNOWFLAKE.CORTEX.COMPLETE(STRING, STRING) TO ROLE ... is the same verb as any other RBAC grant. Masking policies apply transparently to columns whose values become prompts. QUERY_HISTORY captures every Cortex invocation with the calling user, role, warehouse, and byte counts. This is Cortex's headline advantage over "just call OpenAI."
• RAG plumbing. VECTOR(FLOAT, N) column type + VECTOR_COSINE_SIMILARITY(vec1, vec2) + CORTEX SEARCH SERVICE (hybrid vector + keyword with auto-refresh via TARGET_LAG) covers the retrieval half. The COMPLETE function covers the generation half. No external dependencies, no dual-write.

The Cortex Functions surface — the scalar SQL entry points.

• SNOWFLAKE.CORTEX.COMPLETE(model, prompt [, options]). The general-purpose text-generation function. model is a string like 'llama3.1-70b', 'mistral-large2', 'snowflake-arctic', 'claude-3-5-sonnet'. options is an object with temperature, max_tokens, guardrails. Returns the completion as a STRING.
• SNOWFLAKE.CORTEX.SUMMARIZE(text), .TRANSLATE(text, from, to), .SENTIMENT(text), .EXTRACT_ANSWER(text, question), .CLASSIFY_TEXT(text, categories). Task-specific helpers backed by fine-tuned models — cheaper per call than a full COMPLETE, tuned for the specific task.
• SNOWFLAKE.CORTEX.EMBED_TEXT_768(model, text), .EMBED_TEXT_1024(model, text). Embedding functions returning VECTOR(FLOAT, 768) or VECTOR(FLOAT, 1024). snowflake-arctic-embed-l (1024-dim) is the Snowflake-native model; e5-base-v2 (768-dim) is the multilingual option.
• SNOWFLAKE.CORTEX.FINETUNE(...). Fine-tune Llama or Mistral against your labelled data set stored in a Snowflake table; the resulting model becomes callable as COMPLETE('finetune:my_model_v1', ...).

The Cortex Search service — hybrid retrieval as a first-class object.

• What it is. A named service that indexes the text of a source table for hybrid vector + BM25 keyword search, refreshes on a TARGET_LAG cadence, and exposes a SEARCH_PREVIEW scalar function that returns ranked results as JSON.
• Why hybrid. Pure vector search loses on rare-word retrieval (proper nouns, acronyms, error codes); pure BM25 loses on paraphrase. Hybrid combines both scores and consistently beats either alone on domain-specific corpora.
• Refresh. TARGET_LAG = '1 minute' re-indexes chunks within one minute of an underlying UPDATE — good enough for near-real-time RAG over mutable data.

The Cortex Analyst text-to-SQL layer.

• What it is. A managed text-to-SQL service that takes a natural-language question plus a semantic model YAML (defining tables, columns, joins, business-friendly names, and example queries) and returns a valid SQL query executable on your warehouse.
• Why the semantic model. Naive text-to-SQL hallucinates joins and column names; a semantic model constrains the LLM to your actual schema. This is why Cortex Analyst outperforms a "just SELECT via COMPLETE" approach in production.

Common interview probes on Snowflake Cortex.

• "What's the cost of Cortex vs a direct OpenAI call?" — required answer: cheaper for Llama/Mistral tiers, more expensive for GPT-4o-tier proprietary models; the trade-off is governance and simplicity.
• "How do you handle PII in a Cortex prompt?" — required answer: masking policy on the source column; role-based access control on the CORTEX function grants.
• "Why use Cortex Search instead of a Snowflake VECTOR_COSINE_SIMILARITY join?" — required answer: hybrid retrieval + auto-refresh + reranking; you can hand-roll a similarity join but you don't get keyword scoring or the managed index.
• "When would Cortex Analyst outperform DIY text-to-SQL?" — required answer: any workload with more than ~20 tables where a semantic model constrains the LLM to real joins.

Worked example — RAG over support_tickets with Cortex Search + COMPLETE

Detailed explanation. The canonical Snowflake Cortex RAG pipeline: chunk the source text into a chunks table, embed each chunk, register a Cortex Search service on the chunks table, and expose a SQL function that takes a question, retrieves top-K chunks, assembles a prompt, and calls COMPLETE. Build the whole thing.

• Source. support_tickets(ticket_id, subject, body, created_at); 5M rows.
• Chunking. 800-token chunks with 100-token overlap.
• Embedding model. snowflake-arctic-embed-l → 1024-dim vectors.
• Search service. CORTEX SEARCH SERVICE with TARGET_LAG='5 minutes'.
• Generation. COMPLETE('llama3.1-70b', ...) with temperature 0.2.

Question. Write the chunking task, the embedding populate, the search service DDL, and the RAG SQL function.

Input.

Component	Value
Source table	support.support_tickets
Chunks table	support.ticket_chunks
Embedding model	snowflake-arctic-embed-l (1024-dim)
Search service	support.tickets_search
Generator	llama3.1-70b

Code.

-- 1. Chunk the source text — one row per 800-token slice
CREATE OR REPLACE TABLE support.ticket_chunks AS
WITH t AS (
    SELECT ticket_id,
           subject,
           SPLIT(body, ' ') AS words,
           ARRAY_SIZE(SPLIT(body, ' ')) AS n_words
    FROM   support.support_tickets
),
sliced AS (
    SELECT ticket_id,
           subject,
           chunk_no,
           ARRAY_SLICE(words,
                       chunk_no * 700,                                 -- 700-word step (approx 800 tokens)
                       LEAST(chunk_no * 700 + 800, n_words)) AS slice
    FROM   t,
           LATERAL FLATTEN(input =>
             ARRAY_GENERATE_RANGE(0, CEIL(n_words / 700.0)::INT)) AS f
)
SELECT
    ROW_NUMBER() OVER (ORDER BY ticket_id, chunk_no) AS chunk_id,
    ticket_id,
    subject,
    ARRAY_TO_STRING(slice, ' ') AS chunk_text
FROM   sliced;

ALTER TABLE support.ticket_chunks
  ADD COLUMN embedding VECTOR(FLOAT, 1024);

-- 2. Populate embeddings in one UPDATE (Snowflake will parallelise across the warehouse)
UPDATE support.ticket_chunks
   SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_1024(
                     'snowflake-arctic-embed-l',
                     chunk_text
                   )
 WHERE embedding IS NULL;

-- 3. Cortex Search service — hybrid vector + keyword, 5-min lag
CREATE OR REPLACE CORTEX SEARCH SERVICE support.tickets_search
  ON      chunk_text
  ATTRIBUTES ticket_id, subject
  WAREHOUSE = cortex_wh
  TARGET_LAG = '5 minutes'
  AS (
      SELECT chunk_id, ticket_id, subject, chunk_text
      FROM   support.ticket_chunks
  );

-- 4. RAG function — one call, one round-trip
CREATE OR REPLACE FUNCTION support.answer(question STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
    WITH hits AS (
        SELECT value:chunk_text::STRING AS chunk_text,
               value:score::FLOAT       AS score
        FROM   TABLE(FLATTEN(
                   input => PARSE_JSON(
                     SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
                       'support.tickets_search',
                       OBJECT_CONSTRUCT(
                         'query',   question,
                         'limit',   5,
                         'columns', ARRAY_CONSTRUCT('chunk_text')
                       )
                     )
                   ):results
               ))
    ),
    ctx AS (
        SELECT LISTAGG(chunk_text, '\n\n')
                 WITHIN GROUP (ORDER BY score DESC) AS context
        FROM   hits
    )
    SELECT SNOWFLAKE.CORTEX.COMPLETE(
        'llama3.1-70b',
        CONCAT('Answer strictly from the support-ticket context.\n\nContext:\n',
               context,
               '\n\nQuestion: ', question,
               '\n\nAnswer:'),
        OBJECT_CONSTRUCT('temperature', 0.2, 'max_tokens', 200)
    )
    FROM ctx
$$;

Step-by-step explanation.

1. Step 1 chunks the raw support-ticket bodies into ~800-token slices with a 100-token overlap. The LATERAL FLATTEN(input => ARRAY_GENERATE_RANGE(...)) idiom is Snowflake's canonical way to explode a per-row loop counter without a stored procedure — every ticket becomes N chunk rows in one SQL statement.
2. Step 2 populates the embedding column with EMBED_TEXT_1024. Snowflake parallelises the UPDATE across the warehouse's threads; for 5M chunks on a MEDIUM warehouse expect a 20–30 minute one-time backfill. Incremental embedding of new chunks becomes a scheduled task after backfill.
3. Step 3 registers a Cortex Search service over the chunks. The ATTRIBUTES clause lets downstream SEARCH_PREVIEW calls filter by ticket_id or subject. TARGET_LAG = '5 minutes' means an update to ticket_chunks propagates into the search index within 5 minutes — the trade-off between freshness and index-refresh cost.
4. Step 4 defines a SQL function that takes a question and returns an answer. SEARCH_PREVIEW returns a JSON blob whose results array contains the ranked chunks with chunk_text and score; FLATTEN(...):results explodes them. LISTAGG ... WITHIN GROUP (ORDER BY score DESC) assembles the context in relevance order.
5. The prompt template — "Answer strictly from the support-ticket context" — is a lightweight grounding instruction that reduces hallucination. temperature=0.2 keeps the answers factual; max_tokens=200 bounds cost per invocation. Neither is magic; both are the boring defaults that ship first.

Output.

Query	Latency (hot warehouse)	Cost / query
"How do I reset my password?"	~700 ms	~$0.006
"Why is my invoice wrong?"	~800 ms	~$0.007
"How do I export analytics?"	~750 ms	~$0.006
"How do I contact support?"	~650 ms	~$0.005

Rule of thumb. For any Snowflake Cortex RAG deployment, store chunks + embeddings + text in one table, register a Cortex Search service with a TARGET_LAG matching your freshness need, and expose the whole pipeline as a single SQL function. Callers see one function; you keep the entire pipeline governed as one SQL object.

Worked example — Cortex Analyst text-to-SQL over a semantic model

Detailed explanation. The canonical text-to-SQL problem: a business user asks "what was our revenue last quarter by region?" and expects a valid SQL query on the right tables. Naive COMPLETE prompts hallucinate joins; Cortex Analyst constrains the LLM to a semantic model YAML that lists your real tables, columns, and relationships. Build the semantic model and wire it up.

• Semantic model. YAML naming tables, dimensions, metrics, joins, and example queries.
• Cortex Analyst. Managed endpoint invoked via REST or the Snowflake SQL API.
• Fallback. If Cortex Analyst returns low confidence, escalate to a human analyst.

Question. Write the semantic model YAML and the client call for a revenue-by-region question.

Input.

Component	Value
Semantic model file	revenue_model.yaml
Fact table	analytics.fct_revenue
Dim tables	analytics.dim_region, analytics.dim_date
Endpoint	POST /api/v2/cortex/analyst/message

Code.

# revenue_model.yaml — Cortex Analyst semantic model
name: revenue_semantic_model
description: Sales revenue by region, quarter, and product line.

tables:
  - name: revenue_facts
    base_table:
      database: analytics
      schema:   public
      table:    fct_revenue
    time_dimensions:
      - name:    order_date
        expr:    order_date
        data_type: DATE
    dimensions:
      - name: region_key
        expr: region_key
        data_type: NUMBER
      - name: product_line
        expr: product_line
        data_type: TEXT
    measures:
      - name:      total_revenue
        expr:      SUM(revenue_amount)
        data_type: NUMBER

  - name: region
    base_table:
      database: analytics
      schema:   public
      table:    dim_region
    dimensions:
      - name: region_key
        expr: region_key
        data_type: NUMBER
      - name: region_name
        expr: region_name
        data_type: TEXT

relationships:
  - name: revenue_to_region
    left_table:  revenue_facts
    right_table: region
    relationship_columns:
      - left_column:  region_key
        right_column: region_key

verified_queries:
  - name: revenue_by_region_last_quarter
    question: "What was revenue last quarter by region?"
    sql: |
      SELECT r.region_name, SUM(f.revenue_amount) AS total_revenue
      FROM   analytics.fct_revenue f
      JOIN   analytics.dim_region  r USING (region_key)
      WHERE  f.order_date >= DATE_TRUNC('quarter', DATEADD('quarter', -1, CURRENT_DATE()))
        AND  f.order_date <  DATE_TRUNC('quarter', CURRENT_DATE())
      GROUP  BY 1
      ORDER  BY 2 DESC;

# Python client — POST the question to Cortex Analyst
import requests, os, json

SNOWFLAKE_HOST = os.environ["SF_HOST"]         # e.g. abc-xy12345.snowflakecomputing.com
TOKEN         = os.environ["SF_JWT"]           # short-lived JWT
MODEL_STAGE   = "@analytics.public.models/revenue_model.yaml"

def ask_analyst(question: str) -> dict:
    resp = requests.post(
        f"https://{SNOWFLAKE_HOST}/api/v2/cortex/analyst/message",
        headers={"Authorization": f"Bearer {TOKEN}",
                 "Content-Type":  "application/json"},
        json={
            "messages": [{"role": "user",
                          "content": [{"type": "text", "text": question}]}],
            "semantic_model_file": MODEL_STAGE,
        },
        timeout=30,
    )
    resp.raise_for_status()
    return resp.json()

print(json.dumps(ask_analyst("Revenue last quarter by region"), indent=2))

Step-by-step explanation.

1. The revenue_model.yaml is the contract between the LLM and your warehouse: it lists the tables the model may reference, the dimensions and measures with business-friendly names, and the relationships (joins) that are legal. Cortex Analyst refuses to generate SQL that violates the model — no hallucinated joins.
2. verified_queries are labelled question/SQL pairs. Cortex Analyst uses them as few-shot examples at inference time, dramatically improving accuracy on common questions. Every senior deployment ships with 10–30 verified queries per semantic model.
3. The Python client sends the natural-language question plus a pointer to the staged YAML file. Cortex Analyst returns a JSON message containing (a) the generated SQL, (b) a confidence score, and (c) suggested follow-up questions. The client executes the SQL against the warehouse if confidence exceeds a threshold.
4. Governance-wise, Cortex Analyst never sees your data — only your schema and your verified queries. The generated SQL runs under the caller's role, so row-access policies apply exactly as if the analyst wrote the query by hand.
5. The fallback pattern: if the returned confidence is < 0.7, or the SQL references tables not in the semantic model, escalate to a human analyst and log the question for future verified_queries additions. Cortex Analyst is a productivity multiplier, not an oracle.

Output.

Question	Generated SQL (excerpt)	Confidence
"Revenue last quarter by region"	SELECT r.region_name, SUM(f.revenue_amount) ...	0.94
"Top product line year to date"	SELECT product_line, SUM(revenue_amount) ...	0.88
"Why did EMEA drop in Q3"	(asks for clarification)	0.42

Rule of thumb. For text-to-SQL over more than ~10 tables, always use Cortex Analyst with a semantic model — do not try to prompt-engineer COMPLETE into producing correct SQL. Ship 10–30 verified queries per model on day one; every escalated question becomes a new verified query.

Worked example — cost modelling Cortex vs an external LLM API

Detailed explanation. Every senior architect must quantify the cost of Cortex against the alternative of calling an external LLM API. The trade-off is: Cortex charges compute credits + a per-token surcharge, but keeps prompts inside your VPC; an external API charges per-token only but requires egress + external contract + PII sanitisation. Walk through the calculation for a 50k-queries/day RAG chatbot.

• Volume. 50k queries/day × (1,400 in + 200 out) tokens = 70M in + 10M out per day = 2.1B in + 300M out per month.
• Cortex Llama 3.1 70B. ~1.21 credits per 1M input tokens × 2,100M = ~2,540 credits/month input; ~1.21 credits per 1M output × 300M = ~360 credits/month output. At $2/credit that is ~$5,800/month token cost, plus ~$3,000/month warehouse warm cost = ~$8.8k/month all-in.
• External API (GPT-4o). $5/1M input × 2,100M = $10,500/month input; $15/1M output × 300M = $4,500/month output = ~$15k/month, plus egress + PII sanitisation overhead.

Question. Compute the break-even point at which warehouse-native Cortex beats an external API on cost.

Input.

Metric	Cortex Llama 3.1 70B	External GPT-4o
Input token rate	~$2.42/1M (1.21 credits × $2)	$5/1M
Output token rate	~$2.42/1M	$15/1M
Fixed warm-warehouse cost	~$3k/month (MEDIUM warm)	$0
PII sanitisation cost	~$0 (masking policy)	~$1k/month (proxy + review)
Governance surface	role grants	app-level

Code.

# Cost model — Cortex vs external API
INPUT_TOK_PER_QUERY  = 1400
OUTPUT_TOK_PER_QUERY = 200

def monthly_cost_cortex(queries_per_day: int) -> float:
    input_toks  = queries_per_day * 30 * INPUT_TOK_PER_QUERY  / 1e6   # M tokens
    output_toks = queries_per_day * 30 * OUTPUT_TOK_PER_QUERY / 1e6
    token_cost  = 2.42 * (input_toks + output_toks)
    warm_wh     = 3000     # MEDIUM warehouse kept warm ~24/7
    return token_cost + warm_wh

def monthly_cost_openai(queries_per_day: int) -> float:
    input_toks  = queries_per_day * 30 * INPUT_TOK_PER_QUERY  / 1e6
    output_toks = queries_per_day * 30 * OUTPUT_TOK_PER_QUERY / 1e6
    return 5.0 * input_toks + 15.0 * output_toks + 1000   # PII proxy overhead

# Break-even sweep
for qpd in (1_000, 5_000, 10_000, 50_000, 200_000):
    c = monthly_cost_cortex(qpd)
    o = monthly_cost_openai(qpd)
    print(f"{qpd:>7,d} queries/day  cortex ${c:>7,.0f}  openai ${o:>7,.0f}  winner={'cortex' if c<o else 'openai'}")

Output:
  1,000 queries/day  cortex $ 3,152  openai $ 1,352  winner=openai
  5,000 queries/day  cortex $ 3,762  openai $ 2,760  winner=openai
 10,000 queries/day  cortex $ 4,525  openai $ 4,520  winner=openai
 50,000 queries/day  cortex $10,626  openai $16,600  winner=cortex
200,000 queries/day  cortex $33,530  openai $63,400  winner=cortex

Step-by-step explanation.

1. The Cortex cost model has two components: token cost (scales with volume) and fixed warm-warehouse cost (constant). External API cost has one component: token cost (scales with volume) plus a small governance overhead.
2. At low volume (< 10k queries/day), the fixed warehouse cost dominates and Cortex is more expensive per query. This is the "toy pilot" band where a direct API call is genuinely cheaper.
3. The break-even point in this model is around ~10k queries/day. Above that, Cortex's cheaper per-token rate beats the external API's premium GPT-4o pricing. The exact break-even depends on your model choice (Mixtral 8x7B on Cortex is ~5× cheaper per token, moving the break-even down to ~2k queries/day).
4. Beyond ~50k queries/day, Cortex's cost advantage is decisive — 40–50% cheaper than GPT-4o at 50k qpd, and the ratio widens as volume grows. This is where warehouse-native GenAI's "commodity model on commodity compute" story delivers.
5. The pure cost argument ignores governance and latency, which usually tip the decision toward Cortex regardless of volume. If PII cannot leave the VPC, external API is off the table at any price.

Output.

Queries/day	Cortex $/month	GPT-4o $/month	Cost winner
1,000	$3,152	$1,352	external
10,000	$4,525	$4,520	tie
50,000	$10,626	$16,600	Cortex
200,000	$33,530	$63,400	Cortex

Rule of thumb. For any Cortex-vs-external decision, run the two cost curves against your actual volume. Below ~10k qpd on Llama 3.1 70B, external APIs are competitive on price and Cortex wins on governance. Above ~50k qpd, Cortex wins on both. Never commit to a stack based on price alone — governance and latency dominate at scale.

Senior interview question on Snowflake Cortex

A senior interviewer might ask: "You need to build a RAG chatbot over a 5-million-row support_tickets table on Snowflake. The chatbot must answer 100k questions per day, keep PII inside the VPC, target 1-second p95 latency, and stay under $15k/month on compute. Walk me through the snowflake cortex design — the vector column, the Cortex Search service, the RAG function, the governance model, and the cost defence."

Solution Using Cortex Search + Llama 3.1 70B + role-grant governance + warm warehouse

-- 1. Vector column on the chunks table + embedding backfill
ALTER TABLE support.ticket_chunks
  ADD COLUMN IF NOT EXISTS embedding VECTOR(FLOAT, 1024);

UPDATE support.ticket_chunks
   SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_1024('snowflake-arctic-embed-l', chunk_text)
 WHERE embedding IS NULL;

-- 2. Cortex Search service with 1-minute lag
CREATE OR REPLACE CORTEX SEARCH SERVICE support.tickets_search
  ON      chunk_text
  ATTRIBUTES ticket_id, subject, priority
  WAREHOUSE = cortex_wh
  TARGET_LAG = '1 minute'
  AS (
      SELECT chunk_id, ticket_id, subject, priority, chunk_text
      FROM   support.ticket_chunks
  );

-- 3. Masking policy on ticket bodies (SSN + email + phone)
CREATE OR REPLACE MASKING POLICY support.pii_mask AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('AUDIT_ADMIN') THEN val
      ELSE REGEXP_REPLACE(
             REGEXP_REPLACE(
               REGEXP_REPLACE(val, '\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b',     '[SSN]'),
               '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}',           '[EMAIL]'),
             '\\b\\d{3}[-\\s]?\\d{3}[-\\s]?\\d{4}\\b',                     '[PHONE]')
    END;

ALTER TABLE support.ticket_chunks
  MODIFY COLUMN chunk_text SET MASKING POLICY support.pii_mask;

-- 4. RAG function — one call, hybrid search + Llama 3.1 70B
CREATE OR REPLACE FUNCTION support.rag_answer(question STRING, priority_filter STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
    WITH hits AS (
        SELECT value:chunk_text::STRING AS chunk_text
        FROM   TABLE(FLATTEN(
                   input => PARSE_JSON(
                     SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
                       'support.tickets_search',
                       OBJECT_CONSTRUCT(
                         'query',   question,
                         'limit',   5,
                         'columns', ARRAY_CONSTRUCT('chunk_text'),
                         'filter',  OBJECT_CONSTRUCT('@eq',
                                     OBJECT_CONSTRUCT('priority', priority_filter))
                       )
                     )
                   ):results
               ))
    )
    SELECT SNOWFLAKE.CORTEX.COMPLETE(
        'llama3.1-70b',
        CONCAT('Answer strictly from the context. Cite ticket_id where relevant.\n\nContext:\n',
               LISTAGG(chunk_text, '\n\n') WITHIN GROUP (ORDER BY 1),
               '\n\nQuestion: ', question,
               '\n\nAnswer:'),
        OBJECT_CONSTRUCT('temperature', 0.2, 'max_tokens', 250, 'guardrails', TRUE)
    )
    FROM   hits
$$;

-- 5. Role grants — service account can invoke Cortex functions only via the RAG function
GRANT USAGE ON FUNCTION support.rag_answer(STRING, STRING) TO ROLE chatbot_svc;
-- Note: chatbot_svc does NOT get direct grants on CORTEX.COMPLETE — the RAG function is the only path

-- 6. Warm warehouse — 24/7 availability for the chatbot
CREATE OR REPLACE WAREHOUSE cortex_wh
  WAREHOUSE_SIZE  = 'MEDIUM'
  AUTO_SUSPEND    = 3600         -- 1 hour; effectively always-on during business hours
  AUTO_RESUME     = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY    = 'STANDARD';

Step-by-step trace.

Layer	Config	Result
Vector column	VECTOR(FLOAT, 1024)	in-warehouse embedding storage
Embedding model	snowflake-arctic-embed-l	~0.02 credits/1M tokens
Search service	Cortex Search, TARGET_LAG=1 min	hybrid vector+keyword
Retrieval	SEARCH_PREVIEW LIMIT 5 + priority filter	5 chunks, priority-scoped
Generator	Llama 3.1 70B, temp 0.2	governed hosted LLM
Masking	Regex mask on chunk_text	PII stripped before prompt
RBAC	Grants on rag_answer only	chatbot_svc cannot call raw CORTEX
Warehouse	MEDIUM, AUTO_SUSPEND 1 h	warm during peak; auto-scales up to 4 clusters

After the rollout, the chatbot flows one round-trip per query: rag_answer(question, 'high') → hybrid search returns top-5 filtered chunks → prompt assembled with PII already masked → COMPLETE runs Llama 3.1 70B → response back to caller. End-to-end p95 latency is 900 ms warm, 4–5 s cold; monthly cost lands at ~$12k (100k qpd × 1,400 tokens × ~$2.42/1M input + ~$3.5k warm warehouse).

Output:

Metric	Value
End-to-end p95 (warm)	~900 ms
End-to-end p99 (cold)	~5 s
Cost / query (avg 1,600 tokens)	~$0.004
Daily cost at 100k queries	~$400
Monthly compute cost	~$12k
Governance surface	role grant + masking policy

Why this works — concept by concept:

• Cortex Search hybrid retrieval — vector cosine + BM25 keyword scoring in one service, with auto-refresh via TARGET_LAG. Hybrid beats pure-vector on rare-word queries (ticket IDs, error codes, product SKUs); auto-refresh eliminates the "index staleness" bug that plagues home-rolled VECTOR_COSINE_SIMILARITY pipelines.
• Function-level RBAC — the chatbot_svc role is granted USAGE on rag_answer but not on CORTEX.COMPLETE directly. Every LLM invocation must flow through the RAG function, which means every invocation gets the masking policy, the priority filter, and the guardrails. Callers cannot bypass the policy by calling COMPLETE directly.
• Column masking policy — a regex-based mask on chunk_text scrubs SSN, email, and phone before the value is read. The LLM never sees raw PII, even if the source ticket contained it. This is the "prompts never leave the VPC with PII" guarantee, enforced at the column level rather than in application code.
• Warm warehouse + auto-scale — AUTO_SUSPEND = 3600 keeps the warehouse warm across quiet windows; MAX_CLUSTER_COUNT = 4 scales horizontally under peak load. Cold-start p99 stays under 5 s; warm p95 stays under 1 s. This is the standard Cortex-chatbot warehouse shape.
• Cost — 100k queries/day × 1,600 tokens = 160M tokens/day = 4.8B tokens/month × $2.42/1M = ~$11.6k tokens + $3.5k warehouse = ~$15k/month. Fits the $15k ceiling with a small margin. Compared to GPT-4o at the same volume ($36k/month), Cortex is ~60% cheaper and keeps every prompt inside the VPC. O(1) cost per query on the caller side; the platform handles the fan-out and the governance.

SQL
Topic — sql
SQL problems on Cortex functions and vector types

Practice →

Design Topic — design Design problems on Snowflake Cortex RAG systems

Practice →

---

3. BigQuery AI — BigQuery ML + Vertex AI + Gemini in SQL

bigquery ai calls Gemini and other Vertex models via SQL, indexes vectors with VECTOR_SEARCH, and treats GCS blobs as first-class object tables — the multimodal-friendly stack

The mental model in one line: bigquery ai is the Google Cloud stack that exposes Vertex AI models (Gemini text/vision, PaLM, partner models, and your own custom endpoints) as remote models registered inside BigQuery, callable via ML.GENERATE_TEXT() and ML.GENERATE_EMBEDDING() from ordinary SQL, backed by VECTOR_SEARCH() over IVF or TREE_AH indices for retrieval, and extended by object tables that expose GCS blobs (PDFs, images, audio) as SQL rows for multimodal RAG — with pricing that combines BigQuery slots for the SQL side and per-invocation charges on the Vertex remote-model side. Every senior data engineer working in a GCP-heavy shop needs to be fluent in this stack; the shape is different from Cortex (more explicit, more verbose, more knobs) but the primitives compose the same way.

The four axes for BigQuery AI.

• Cost. Two-part billing: (a) BigQuery on-demand or slot cost for the SQL that invokes the model (bytes-scanned or slot-hours), (b) Vertex AI per-invocation cost for the model call itself (per-1K-character input + per-1K-character output on Gemini; per-image on multimodal). The slot side is often the cheap side; the Vertex side is the driver at scale.
• Latency. Slot warmup is ~1 s on a reserved slot pool; on-demand SQL adds 500 ms–2 s of scheduling. Gemini inference is 300–800 ms for a short completion; multimodal (image input) is 1–3 s. End-to-end RAG typically lands at 1.5–3 s p95.
• Governance. IAM at the Vertex model resource level + BigQuery IAM at the table / dataset level + column-level ACL + row-level ACL + Data Catalog policy tags. More surfaces than Cortex, but every enterprise GCP shop already speaks this dialect.
• RAG plumbing. ML.GENERATE_EMBEDDING() produces embeddings; CREATE VECTOR INDEX (IVF or TREE_AH) indexes them; VECTOR_SEARCH() retrieves. Object tables + BigLake external tables handle multimodal RAG sources (PDFs, images) natively.

The BigQuery AI functions — the SQL entry points.

• ML.GENERATE_TEXT(MODEL, table, STRUCT(options)). Table-function that calls a remote text model on every row of the input table. Options include temperature, max_output_tokens, top_p, top_k, safety_settings. Returns a table with the LLM output as a JSON column.
• ML.GENERATE_EMBEDDING(MODEL, table, STRUCT(options)). Table-function for embeddings. Options include flatten_json_output, task_type (retrieval_query vs retrieval_document — matters for asymmetric embedding models).
• AI.GENERATE_TABLE(...), AI.GENERATE_BOOL(...), AI.GENERATE_INT(...), AI.SCORE(...). Task-specific structured-output helpers that constrain the LLM output to a typed shape — bool, int, table, score — parseable without regex.
• VECTOR_SEARCH(TABLE t, column_to_search, query_vector, top_k, options). Vector retrieval. options includes fraction_lists_to_search (IVF: what fraction of index lists to scan; higher = recall, lower = latency) and use_brute_force (bypass the index for small tables).

Remote models — how the LLM shows up inside BigQuery.

• CREATE MODEL ... REMOTE WITH CONNECTION .... DDL that registers a Vertex AI endpoint (a Gemini model, a Model Garden partner model, a custom-deployed endpoint) as a BigQuery model object. All subsequent ML.GENERATE_TEXT(MODEL ...) calls proxy through this connection.
• Connections. A BigQuery connection is an IAM-scoped resource that grants BigQuery a service-account identity to call Vertex. Roles: roles/bigquery.connectionUser on the caller side; roles/aiplatform.user on the Vertex side.
• Quotas. Each remote model has a per-project Vertex quota (requests/minute, tokens/minute). Exceeding quota returns 429 to the SQL query; SDK-side retry with exponential backoff is expected.

Vector indices — IVF vs TREE_AH.

• IVF (inverted-file index). Partitions vectors into K clusters (Voronoi cells) via k-means; retrieval scans the fraction_lists_to_search fraction of cells nearest the query. Standard choice; tune fraction_lists_to_search to trade recall for latency.
• TREE_AH (tree + asymmetric hashing). Google-Research-derived index optimised for very large corpora. Preferred at > 10M vectors; smaller memory footprint, faster search for the same recall.
• Brute force. No index — full scan of the vector column. Fastest to build; only viable up to ~100k vectors. Useful for prototyping.

Object tables + BigLake — the multimodal RAG path.

• Object table. CREATE OBJECT TABLE ... WITH CONNECTION ... OPTIONS(uris=['gs://bucket/pdfs/*']). Exposes every GCS blob under the URI as a SQL row with columns uri, content_type, size, updated, metadata.
• Multimodal Gemini calls. ML.GENERATE_TEXT(MODEL gemini_multimodal, TABLE object_table, ...) passes the blob content to Gemini's multimodal endpoint — PDF pages, images, audio — and returns text extractions or descriptions. No separate ETL pipeline.

Common interview probes on BigQuery AI.

• "What's the difference between ML.GENERATE_TEXT and calling the Vertex REST API directly?" — required answer: governance (IAM + Data Catalog), colocation with the data, no separate ETL, but higher latency vs a direct REST call from a Cloud Function.
• "How do you index 100M vectors?" — required answer: TREE_AH; IVF struggles at that scale.
• "How does an object table beat a Cloud Function that reads GCS?" — required answer: SQL-native, IAM inheritance, no separate compute cost, works with multimodal remote models natively.
• "How do you cost-model BigQuery AI vs Cortex?" — required answer: slot cost + per-invocation on Vertex vs credits + surcharge on Cortex; both land in the same ballpark at similar volume, choice usually driven by ecosystem and multimodal need.

Worked example — RAG over BigQuery with VECTOR_SEARCH + Gemini

Detailed explanation. The canonical BigQuery AI RAG pipeline: chunk source text into a table, register a Gemini text-embedding remote model, embed every chunk into a vector column, create a VECTOR INDEX, then compose a query that embeds the question, does a VECTOR_SEARCH, assembles context, and calls ML.GENERATE_TEXT on a Gemini text model. Build the whole thing.

• Source. prod.support.tickets — 5M rows.
• Chunks table. prod.kb.chunks(chunk_id, ticket_id, chunk_text, embedding ARRAY<FLOAT64>).
• Embedding model. text-embedding-005 (768-dim).
• Generation model. gemini-2.0-flash.
• Vector index. IVF; num_lists=2500, distance_type=COSINE.

Question. Write the model registration, embedding populate, vector index, and RAG SQL.

Input.

Component	Value
Text-embedding model	text-embedding-005 (768-dim)
Text-generation model	gemini-2.0-flash
Vector column type	ARRAY<FLOAT64> (length 768)
Vector index	IVF, num_lists=2500, COSINE
Retrieval	top_k=5

Code.

-- 1. Register the Vertex models as BigQuery remote models
CREATE OR REPLACE MODEL prod.emb.text_emb
  REMOTE WITH CONNECTION `us.vertex_conn`
  OPTIONS (endpoint = 'text-embedding-005');

CREATE OR REPLACE MODEL prod.llm.gemini_flash
  REMOTE WITH CONNECTION `us.vertex_conn`
  OPTIONS (endpoint = 'gemini-2.0-flash');

-- 2. Chunks table + embedding column
CREATE OR REPLACE TABLE prod.kb.chunks (
    chunk_id    INT64,
    ticket_id   INT64,
    chunk_text  STRING,
    embedding   ARRAY<FLOAT64>       -- 768 floats
);

-- 3. Embed every chunk (one big batch)
INSERT INTO prod.kb.chunks (chunk_id, ticket_id, chunk_text, embedding)
SELECT
    ROW_NUMBER() OVER (ORDER BY t.ticket_id, c.chunk_no) AS chunk_id,
    t.ticket_id,
    c.chunk_text,
    emb.ml_generate_embedding_result AS embedding
FROM (
    SELECT ticket_id, ARRAY_TO_STRING(SPLIT(body, ' '), ' ') AS chunk_text, offset AS chunk_no
    FROM   prod.support.tickets,
           UNNEST(GENERATE_ARRAY(0, DIV(ARRAY_LENGTH(SPLIT(body, ' ')), 700))) AS offset
) c
JOIN (
    SELECT * FROM ML.GENERATE_EMBEDDING(
        MODEL prod.emb.text_emb,
        (SELECT chunk_text AS content FROM ...),   -- inline sub-select of chunks
        STRUCT(TRUE AS flatten_json_output,
               'RETRIEVAL_DOCUMENT' AS task_type)
    )
) emb ON c.chunk_text = emb.content;

-- 4. Vector index — IVF, cosine
CREATE OR REPLACE VECTOR INDEX prod.kb.chunks_ivf
  ON prod.kb.chunks (embedding)
  OPTIONS (
      index_type      = 'IVF',
      distance_type   = 'COSINE',
      ivf_options     = '{"num_lists": 2500}'
  );

-- 5. The RAG query — one SELECT, embed → search → generate
DECLARE question STRING DEFAULT 'How do I reset my password?';

WITH q_emb AS (
  SELECT ml_generate_embedding_result AS vec
  FROM   ML.GENERATE_EMBEDDING(
           MODEL prod.emb.text_emb,
           (SELECT question AS content),
           STRUCT(TRUE AS flatten_json_output,
                  'RETRIEVAL_QUERY' AS task_type)
         )
),
retrieved AS (
  SELECT base.chunk_text
  FROM   VECTOR_SEARCH(
           TABLE prod.kb.chunks,
           'embedding',
           (SELECT vec FROM q_emb),
           top_k => 5,
           options => '{"fraction_lists_to_search": 0.05}'
         )
),
prompt AS (
  SELECT CONCAT(
    'Answer strictly from the context.\n\nContext:\n',
    STRING_AGG(chunk_text, '\n\n'),
    '\n\nQuestion: ', question,
    '\n\nAnswer:'
  ) AS text
  FROM retrieved
)
SELECT
    ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS answer
FROM   ML.GENERATE_TEXT(
         MODEL prod.llm.gemini_flash,
         (SELECT text AS prompt FROM prompt),
         STRUCT(0.2 AS temperature, 250 AS max_output_tokens, TRUE AS flatten_json_output)
       );

Step-by-step explanation.

1. Steps 1 register two Vertex remote models — one for embeddings, one for generation. The CONNECTION object holds the IAM permissions BigQuery needs to invoke Vertex. This is a one-time DDL step; subsequent SQL just references the model by name.
2. Step 3 populates the chunks table with embeddings. ML.GENERATE_EMBEDDING is a table function; you pass it the model, a sub-query yielding one row per input text, and options. task_type = 'RETRIEVAL_DOCUMENT' tells asymmetric embedding models this is a document embedding — the query at retrieval time will use 'RETRIEVAL_QUERY'.
3. Step 4 creates an IVF vector index with num_lists = 2500 (rough heuristic: sqrt(rows), clamped to a few thousand). distance_type = COSINE matches the embedding model's canonical similarity metric. Index build is a background job; retrieval uses the index once it's ready.
4. Step 5 is the RAG query. q_emb embeds the question; retrieved runs VECTOR_SEARCH with fraction_lists_to_search = 0.05 (scan 5% of the index lists — recall vs latency knob); prompt assembles the LLM prompt; the outer SELECT calls ML.GENERATE_TEXT and extracts the answer text from the JSON result.
5. The verbosity of the JSON extraction (ml_generate_text_result['candidates'][0]['content']['parts'][0]['text']) is BigQuery AI's characteristic tax — everything returns as JSON so you can inspect grounding metadata, safety scores, and token counts, but the price is a longer SQL. Wrap it in a UDF for production callers.

Output.

Query	Retrieval latency	Generation latency	Total p95
"How do I reset my password?"	~180 ms	~600 ms	~950 ms
"Why is my invoice wrong?"	~200 ms	~700 ms	~1.1 s
"How do I export analytics?"	~180 ms	~650 ms	~1.0 s
Cold-start (first slot)	+800 ms	+200 ms	+1 s

Rule of thumb. For any BigQuery AI RAG pipeline, register the embedding and text remote models once via DDL, keep chunks + embeddings in one table, build an IVF index with num_lists ≈ sqrt(rows), and wrap the whole pipeline in a SQL function or UDF for callers. Set fraction_lists_to_search = 0.05 as the default; tune upward if recall is poor.

Worked example — multimodal RAG with object tables + Gemini vision

Detailed explanation. BigQuery AI's headline advantage over Cortex is multimodal RAG — pulling text out of PDFs, images, or scanned documents natively via Gemini vision. The mechanism is object tables (SQL-visible metadata over GCS blobs) plus ML.GENERATE_TEXT on a multimodal Gemini endpoint. Walk through building a contract-Q&A chatbot over a bucket of PDF contracts.

• Source. gs://legal-contracts/pdfs/*.pdf — 20,000 PDFs.
• Object table. prod.legal.contracts_obj — one row per PDF.
• Multimodal model. gemini-2.0-pro (accepts PDF pages as input).
• Pipeline. Object table → per-PDF summary via ML.GENERATE_TEXT → text stored in a summaries table → embed → RAG.

Question. Register the object table, extract per-PDF summaries with Gemini vision, and set up RAG over the summaries.

Input.

Component	Value
GCS bucket	gs://legal-contracts/pdfs/
Object table	prod.legal.contracts_obj
Multimodal LLM	gemini-2.0-pro
Summary table	prod.legal.contract_summaries

Code.

-- 1. Object table over the PDF bucket
CREATE OR REPLACE EXTERNAL TABLE prod.legal.contracts_obj
  WITH CONNECTION `us.vertex_conn`
  OPTIONS (
    object_metadata = 'SIMPLE',
    uris            = ['gs://legal-contracts/pdfs/*.pdf']
  );

-- 2. Register multimodal Gemini
CREATE OR REPLACE MODEL prod.llm.gemini_pro
  REMOTE WITH CONNECTION `us.vertex_conn`
  OPTIONS (endpoint = 'gemini-2.0-pro');

-- 3. Extract structured summaries per PDF (multimodal call)
CREATE OR REPLACE TABLE prod.legal.contract_summaries AS
SELECT
    obj.uri                                                    AS uri,
    JSON_VALUE(result.ml_generate_text_result,
               '$.candidates[0].content.parts[0].text')        AS summary,
    result.ml_generate_text_status                              AS status
FROM prod.legal.contracts_obj AS obj,
     LATERAL (
       SELECT *
       FROM ML.GENERATE_TEXT(
              MODEL prod.llm.gemini_pro,
              (SELECT obj.uri AS uri, obj.data AS content),
              STRUCT(
                'Summarise this contract in 3 sentences: parties, term, main obligations.'
                                                    AS prompt,
                0.1                                 AS temperature,
                300                                 AS max_output_tokens,
                TRUE                                AS flatten_json_output
              )
            )
     ) AS result
WHERE obj.updated > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);

-- 4. Embed summaries + RAG over them
ALTER TABLE prod.legal.contract_summaries ADD COLUMN embedding ARRAY<FLOAT64>;

UPDATE prod.legal.contract_summaries s
   SET embedding = e.ml_generate_embedding_result
  FROM ML.GENERATE_EMBEDDING(
         MODEL prod.emb.text_emb,
         (SELECT uri, summary AS content FROM prod.legal.contract_summaries WHERE embedding IS NULL),
         STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)
       ) AS e
 WHERE s.uri = e.uri;

CREATE OR REPLACE VECTOR INDEX prod.legal.contracts_ivf
  ON prod.legal.contract_summaries(embedding)
  OPTIONS (index_type='IVF', distance_type='COSINE',
           ivf_options='{"num_lists": 200}');

Step-by-step explanation.

1. Step 1 registers an object table over the GCS PDF bucket. Each PDF becomes a row with uri, content_type, size, updated, metadata, and data (the blob bytes). No PDF parsing library required on your side — Gemini's multimodal endpoint accepts the bytes directly.
2. Step 2 registers gemini-2.0-pro, the multimodal endpoint. gemini-2.0-flash is text-only; gemini-2.0-pro accepts images and PDF pages. Model choice matters for what you can pass in content.
3. Step 3 is the extraction pass. For every PDF updated in the last 24 hours, invoke ML.GENERATE_TEXT with the PDF bytes as content and a structured summary prompt. Gemini vision reads the PDF and returns a 3-sentence summary. The LATERAL join runs the model per-row.
4. Step 4 embeds the summaries and builds a vector index. From here on, the pipeline is identical to a text-only RAG — retrieve top-K summaries by cosine, assemble prompt, call gemini-flash (cheaper) to generate the final answer.
5. The whole pipeline is native BigQuery SQL — no Cloud Function to parse PDFs, no Airflow DAG to orchestrate the extraction, no separate vector database. Governance is BigQuery IAM + connection-level IAM on the Vertex side. This is the multimodal-RAG advantage in one screenshot.

Output.

Step	Cost driver	Approx rate
PDF summary via Gemini Pro	per-image inference	~$0.002 per PDF
Embedding via text-embedding-005	per-1K-tokens	~$0.00002
Vector index build	slot-hours	one-time
RAG retrieval + generation	Gemini Flash + slot	~$0.003 per query

Rule of thumb. For multimodal RAG on GCP, always start with an object table over the source bucket. Do the expensive multimodal extraction once (per updated blob), store text summaries in a normal table, and run RAG against those summaries. Never call the multimodal endpoint at query time — the latency and cost do not fit a chatbot latency budget.

Worked example — cost modelling BigQuery AI vs Cortex vs Mosaic

Detailed explanation. Every senior architect must compare BigQuery AI cost against the other two warehouse-native stacks. The comparison is tricky because the pricing shapes differ: BigQuery is slots-plus-per-invocation, Cortex is credits-plus-surcharge, Mosaic is pay-per-token-plus-DBUs. Walk through the calculation for a 100k-queries/day RAG chatbot on Gemini Flash.

• Volume. 100k queries/day × 1,500 tokens = 150M tokens/day = 4.5B tokens/month.
• BigQuery AI (Gemini Flash). ~$0.075/1M input + ~$0.30/1M output tokens on Gemini 2.0 Flash + ~$5/TB scanned on the retrieval SQL (~0.5 TB/month for the vector search) = ~$500 tokens + ~$200 slots ≈ ~$700/month? (Recalculate for realistic numbers) — 4.5B × $0.075/1M = $340; output 600M × $0.30/1M = $180; total tokens ~$520/month + slots ~$200 = ~$720/month.
• Cortex (Llama 3.1 70B). From H2-2 model: ~$12k/month at 100k qpd.
• Mosaic AI (Llama 3.1 70B endpoint). Foundation Model APIs ~$1.00/1M input + $3.00/1M output on Llama 3.1 70B; 4.5B × $1 + 600M × $3 = $4.5k + $1.8k = ~$6.3k tokens + ~$2k endpoint + DBU overhead = ~$8.5k/month.

Question. Compute the three-way monthly cost across a realistic query-volume sweep.

Input.

Stack	$/1M input	$/1M output	Fixed monthly
BigQuery AI (Gemini Flash)	$0.075	$0.30	~$200 slots
Snowflake Cortex (Llama 3.1 70B)	$2.42	$2.42	~$3,500 warm warehouse
Databricks Mosaic (Llama 3.1 70B)	$1.00	$3.00	~$2,000 endpoint + DBUs

Code.

# 3-stack cost model
INPUT_TOK  = 1_300     # per query
OUTPUT_TOK = 200

def bq_cost(qpd):
    m_in  = qpd*30*INPUT_TOK/1e6
    m_out = qpd*30*OUTPUT_TOK/1e6
    return 0.075*m_in + 0.30*m_out + 200

def cortex_cost(qpd):
    m_in  = qpd*30*INPUT_TOK/1e6
    m_out = qpd*30*OUTPUT_TOK/1e6
    return 2.42*(m_in+m_out) + 3500

def mosaic_cost(qpd):
    m_in  = qpd*30*INPUT_TOK/1e6
    m_out = qpd*30*OUTPUT_TOK/1e6
    return 1.0*m_in + 3.0*m_out + 2000

for qpd in (1_000, 10_000, 100_000, 1_000_000):
    print(f"{qpd:>9,d}  bq ${bq_cost(qpd):>8,.0f}  cortex ${cortex_cost(qpd):>8,.0f}  mosaic ${mosaic_cost(qpd):>8,.0f}")

Output:
    1,000  bq $      207  cortex $   3,616  mosaic $   2,057
   10,000  bq $      280  cortex $   4,663  mosaic $   2,570
  100,000  bq $    1,010  cortex $  15,126  mosaic $   7,700
1,000,000  bq $    8,300  cortex $ 120,130  mosaic $  59,000

Step-by-step explanation.

1. Gemini 2.0 Flash is dramatically cheaper per token than Llama 3.1 70B on the two other stacks — 30× cheaper on input, 8× cheaper on output. This makes BigQuery AI on Gemini Flash the cheapest per-token option in 2026 for text-only workloads that don't need top-tier reasoning.
2. The cost gap narrows if you compare like-for-like models. Running Gemini 1.5 Pro or Gemini 2.0 Pro on BigQuery AI is ~$1.25/1M input + $5/1M output, which puts it in the same ballpark as Cortex Llama 3.1 70B. The Gemini Flash cost is Google's "commodity" pricing tier.
3. Snowflake Cortex is the most expensive of the three at scale — the credits-per-token rate on Llama 3.1 70B is roughly 2.4× the Mosaic pay-per-token rate. Cortex's pitch is not cost; it's SQL simplicity and one-invoice governance.
4. Databricks Mosaic sits between BigQuery Flash and Cortex on cost. If you need open-model fine-tuning + agent evals + Unity Catalog governance, the price is competitive; if you just need commodity text generation, Gemini Flash wins.
5. The takeaway: pick a stack for governance and ecosystem, then pick a model within that stack for cost. Cross-stack comparisons must be model-aware — Cortex Mixtral 8x7B vs BigQuery Gemini Flash vs Mosaic Mixtral endpoint is a very different comparison from the Llama-70B comparison above.

Output.

Queries/day	BigQuery Flash	Cortex Llama 70B	Mosaic Llama 70B
1,000	$207	$3,616	$2,057
10,000	$280	$4,663	$2,570
100,000	$1,010	$15,126	$7,700
1,000,000	$8,300	$120,130	$59,000

Rule of thumb. For pure cost, BigQuery AI on Gemini Flash is unbeatable in 2026 at any volume. Cortex wins on SQL simplicity + one-invoice governance; Mosaic wins on open-model + agent framework. Match the stack to the workload, not to the per-token rate — governance and ecosystem beat price at every scale that matters.

Senior interview question on BigQuery AI

A senior interviewer might ask: "You need to build a multimodal RAG chatbot over a 20,000-PDF contract library sitting in GCS. Answers must cite the source PDF and section. Latency target is 2-second p95, monthly cost ceiling $3,000. Walk me through the bigquery ai design — the object table, the multimodal extraction pass, the vector index, the RAG query, and the citation mechanism."

Solution Using object table + Gemini Pro extraction + IVF vector index + Gemini Flash generation

-- 1. Object table + Vertex connections + remote models
CREATE OR REPLACE EXTERNAL TABLE prod.legal.contracts_obj
  WITH CONNECTION `us.vertex_conn`
  OPTIONS (object_metadata='SIMPLE',
           uris=['gs://legal-contracts/pdfs/*.pdf']);

CREATE OR REPLACE MODEL prod.llm.gemini_pro   REMOTE WITH CONNECTION `us.vertex_conn` OPTIONS (endpoint='gemini-2.0-pro');
CREATE OR REPLACE MODEL prod.llm.gemini_flash REMOTE WITH CONNECTION `us.vertex_conn` OPTIONS (endpoint='gemini-2.0-flash');
CREATE OR REPLACE MODEL prod.emb.text_emb     REMOTE WITH CONNECTION `us.vertex_conn` OPTIONS (endpoint='text-embedding-005');

-- 2. Per-PDF section-level extraction (runs nightly on new/updated blobs)
CREATE OR REPLACE TABLE prod.legal.contract_sections AS
SELECT
    obj.uri                                       AS uri,
    JSON_VALUE(r.ml_generate_text_result,
               '$.candidates[0].content.parts[0].text') AS sections_json
FROM   prod.legal.contracts_obj obj,
       LATERAL (
         SELECT * FROM ML.GENERATE_TEXT(
           MODEL prod.llm.gemini_pro,
           (SELECT obj.uri AS uri, obj.data AS content),
           STRUCT(
             'Return JSON list of {section_title, section_text} for every section.' AS prompt,
             0.1  AS temperature,
             2000 AS max_output_tokens,
             TRUE AS flatten_json_output))
       ) r
WHERE  obj.updated > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);

-- 3. Flatten into per-section chunks + embed
CREATE OR REPLACE TABLE prod.legal.chunks AS
SELECT
    ROW_NUMBER() OVER (ORDER BY uri, JSON_VALUE(section, '$.section_title')) AS chunk_id,
    uri,
    JSON_VALUE(section, '$.section_title') AS section_title,
    JSON_VALUE(section, '$.section_text')  AS chunk_text
FROM   prod.legal.contract_sections,
       UNNEST(JSON_QUERY_ARRAY(sections_json)) AS section;

ALTER TABLE prod.legal.chunks ADD COLUMN embedding ARRAY<FLOAT64>;
UPDATE prod.legal.chunks c
   SET embedding = e.ml_generate_embedding_result
  FROM ML.GENERATE_EMBEDDING(MODEL prod.emb.text_emb,
         (SELECT chunk_id, chunk_text AS content FROM prod.legal.chunks WHERE embedding IS NULL),
         STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)) AS e
 WHERE c.chunk_id = e.chunk_id;

CREATE OR REPLACE VECTOR INDEX prod.legal.chunks_ivf
  ON prod.legal.chunks(embedding)
  OPTIONS (index_type='IVF', distance_type='COSINE', ivf_options='{"num_lists":300}');

-- 4. RAG SQL — retrieve, cite, answer
CREATE OR REPLACE PROCEDURE prod.legal.rag_answer(question STRING, OUT answer STRING)
BEGIN
  DECLARE prompt STRING;

  SET (answer, prompt) = (
    WITH q_emb AS (
      SELECT ml_generate_embedding_result AS vec
      FROM   ML.GENERATE_EMBEDDING(
               MODEL prod.emb.text_emb,
               (SELECT question AS content),
               STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_QUERY' AS task_type))
    ),
    hits AS (
      SELECT base.uri, base.section_title, base.chunk_text
      FROM   VECTOR_SEARCH(TABLE prod.legal.chunks, 'embedding',
                            (SELECT vec FROM q_emb),
                            top_k => 5,
                            options => '{"fraction_lists_to_search":0.05}')
    ),
    p AS (
      SELECT CONCAT(
        'Cite each fact with (uri :: section). Context:\n',
        STRING_AGG(CONCAT('(', uri, ' :: ', section_title, ')\n', chunk_text), '\n\n'),
        '\n\nQuestion: ', question, '\nAnswer:'
      ) AS text
      FROM hits
    ),
    gen AS (
      SELECT JSON_VALUE(ml_generate_text_result, '$.candidates[0].content.parts[0].text') AS a
      FROM ML.GENERATE_TEXT(
             MODEL prod.llm.gemini_flash,
             (SELECT text AS prompt FROM p),
             STRUCT(0.2 AS temperature, 300 AS max_output_tokens, TRUE AS flatten_json_output))
    )
    SELECT gen.a, p.text FROM gen, p);
END;

Step-by-step trace.

Layer	Config	Result
Source	Object table over gs://legal-contracts/pdfs/*	20k PDFs as SQL rows
Extraction	Gemini 2.0 Pro; nightly on updated blobs	JSON-structured sections per PDF
Chunks	one row per section	~100k chunk rows
Embedding	text-embedding-005 (768-dim)	vector column on chunks
Index	IVF, num_lists=300, cosine	sub-200 ms retrieval
Retrieval	top_k=5, fraction=0.05	5 sections per query
Generator	Gemini 2.0 Flash, temp 0.2	cited answer
Citation	(uri :: section_title) in prompt	LLM cites source in output

After the rollout: a nightly extraction pass processes any updated PDFs (Gemini Pro at ~$0.002/PDF = ~$40 for a full backfill, ~$1/day steady-state). Retrieval latency lands at 150–250 ms; generation at 500–800 ms; end-to-end p95 ~1.1 s. Monthly cost lands at ~$700 (Gemini Flash + slots + occasional Gemini Pro extractions). Every answer cites the source PDF URI and section title.

Output:

Metric	Value
Extraction cost (steady)	~$1/day
RAG cost per query	~$0.0003
Daily cost at 100k queries	~$30
Monthly compute cost	~$900
End-to-end p95	~1.1 s
Citation accuracy	~90% (grounding + prompt)

Why this works — concept by concept:

• Object tables + BigLake — GCS blobs become SQL rows without an ETL step. The data column carries the raw bytes; multimodal Gemini reads them natively. This collapses a Cloud Function + Cloud Scheduler + PDF-parsing library into two lines of DDL.
• Two-model pipeline (Pro for extraction, Flash for generation) — Gemini Pro is expensive per invocation but only runs once per updated PDF; Gemini Flash is 30× cheaper per token and runs at query time. Splitting extraction and generation matches expensive work to rare events and cheap work to frequent events.
• IVF vector index with fraction_lists_to_search — fraction_lists_to_search = 0.05 scans 5% of the IVF cells nearest the query vector. Recall is ~95% at this fraction on typical 768-dim embeddings; latency stays sub-200 ms. The knob is the retrieval-vs-latency tuning point.
• Prompt-level citation — passing (uri :: section_title) inline with each chunk teaches the LLM to cite the source in the answer. Not perfect (Gemini still hallucinates citations ~10% of the time), but combined with a downstream verifier that checks each citation against the retrieval set, you get production-grade grounding.
• Cost — ~$1/day extraction + ~$30/day RAG at 100k queries = ~$900/month, well under the $3k ceiling. Compared to a Cortex equivalent (~$12–15k/month on Llama 3.1 70B), BigQuery AI on Gemini Flash is 15× cheaper on the token side. The trade-off is you commit to the Google Cloud governance surface — IAM, Data Catalog, VPC-SC — instead of the Snowflake one.

SQL
Topic — sql
SQL problems on ML.GENERATE_TEXT and VECTOR_SEARCH

Practice →

Design Topic — design Design problems on BigQuery AI RAG architectures

Practice →

---

4. Databricks Mosaic AI — Model Serving, Vector Search, Agents Framework

databricks mosaic ai bundles open-model APIs, delta-sync vector search, an Agent Framework, MLflow tracing, and Unity Catalog governance into the lakehouse

The mental model in one line: databricks mosaic ai is the Databricks feature family that exposes Foundation Model APIs (Llama, DBRX, Mixtral, Anthropic Claude, OpenAI GPT via a governed proxy) behind pay-per-token endpoints, a delta-sync Vector Search index that auto-refreshes from any Delta table, a Model Serving surface for real-time inference on your own custom or fine-tuned models, a Mosaic AI Agent Framework with a first-class evaluation harness, deep MLflow 3 tracing across every LLM call, and a single Unity Catalog governance ring that treats models, endpoints, prompts, and data as one asset class with one ACL surface. Every senior data engineer working on a Databricks-heavy stack in 2026 needs fluency here; the payoff is a lakehouse-native GenAI story where the fine-tuned model, the Delta source, the vector index, and the agent share one policy.

The four axes for Databricks Mosaic AI.

• Cost. Foundation Model APIs bill pay-per-token on published rates (Llama 3.1 70B ~$1/1M input, $3/1M output; Mixtral 8x7B ~$0.50/1M; DBRX ~$0.75/1M). Model Serving for custom models is priced per DBU-hour on the serving compute. Vector Search endpoints are priced per DBU-hour on the vector-search cluster. Every dimension is line-itemed and forecastable.
• Latency. Foundation Model APIs use always-warm endpoints; latency is bounded by the model's inference time (~500 ms for 100 output tokens on Llama 70B; ~200 ms on Mixtral 8x7B). No warehouse cold-start tax. Vector Search retrieval is 50–200 ms for typical corpus sizes; end-to-end RAG lands at 700 ms–1.5 s p95.
• Governance. Unity Catalog governs models, endpoints, functions, tables, and volumes under one three-level namespace (catalog.schema.object). One GRANT verb applies across all asset classes. Lineage flows from Delta → embedding → vector index → prompt → completion in a single UI.
• RAG plumbing. CREATE ... USING DELTA fact tables → CREATE VECTOR SEARCH INDEX with pipeline_type='DELTA_SYNC' → the index auto-refreshes as the Delta table changes → SQL vector_search(...) function retrieves → ai_query(endpoint, prompt) generates. Zero glue code.

Foundation Model APIs — pay-per-token endpoints on hosted open models.

• What they are. Databricks-hosted inference endpoints for popular open-weight models (Llama 3.1 8B/70B, Llama 3.3 70B, DBRX Instruct, Mixtral 8x7B, Claude 3.5 Sonnet via BYOK proxy, Gemma, phi-3). Callable from notebooks (from databricks.sdk import WorkspaceClient) or from SQL (SELECT ai_query(...)).
• Pricing shape. Pure pay-per-token on the endpoint; no minimum spend; no warm-up cost. This makes Mosaic AI unusually forecast-friendly at low volume.
• Provisioned throughput. For high-QPS workloads, buy provisioned throughput on a specific model to guarantee capacity and reduce per-token cost by ~25% at commit.

Vector Search — delta-sync auto-indexing.

• Endpoint. A Vector Search endpoint is a serving cluster (~$1–3/hour by size) that hosts your indices. Multiple indices share one endpoint.
• Index. CREATE VECTOR SEARCH INDEX ... USING PIPELINE_TYPE = 'DELTA_SYNC' binds an index to a Delta source table. Any UPDATE / INSERT / DELETE on the source auto-propagates to the index — no cron job, no manual refresh.
• Index types. Approximate (HNSW) is the default; direct-access is for programmatic upsert. Approximate is what production RAG uses.
• Retrieval. vector_search(index='catalog.schema.index_name', query_text=..., num_results=5) from SQL, or the SDK vs_client.similarity_search(...) from Python.

Model Serving — real-time inference for custom / fine-tuned models.

• What it is. A GPU-backed serving surface for any MLflow-registered model. Wraps the model behind a REST endpoint with request/response logging, autoscaling, and A/B routing.
• Why bother. Fine-tuning Llama 3.1 8B on your labelled data (say, ticket → severity classification) yields a small, cheap model that beats a general-purpose 70B model on that specific task. Model Serving hosts it and gives you the same endpoint interface as Foundation Model APIs.
• Autoscaling. Scale-to-zero on idle; scale-out on QPS. Cold-start on scale-to-zero is 30–60 s — do not use scale-to-zero for chatbots; use for batch or async workloads.

Mosaic AI Agent Framework — the production-agent lifecycle.

• Agent. A Python class or MLflow pyfunc that takes a user message, plans steps, calls tools (SQL, vector search, Foundation Model APIs), and returns an answer. Registered in Unity Catalog as a versioned artifact.
• Evaluation harness. mlflow.evaluate(data=eval_set, model=agent_uri, model_type='databricks-agent') runs a curated evaluation set through the agent and scores answer correctness, groundedness, safety, and latency using LLM-as-judge + programmatic checks. This turns agent quality into a numeric metric you can regress on.
• Tracing. MLflow 3 traces every LLM call, tool invocation, and retrieval hop in the agent. Traces are queryable via SQL for cost, latency, and quality analysis.

Unity Catalog — one governance ring.

• Three-level namespace. catalog.schema.object names apply to tables, volumes, models, functions, endpoints, and vector-search indices uniformly. One GRANT SELECT / EXECUTE / USE verb across all asset classes.
• Lineage. UC captures data → embedding → index → prompt → completion lineage in a single graph. Answers "where did this hallucination come from?" without a separate observability stack.
• PII masking + row-level filters. Column masks and row filters defined once on the Delta table apply to every downstream reader — including the embedding pass. PII redacted at the source is redacted in the vector index too.

Common interview probes on Databricks Mosaic AI.

• "How is Vector Search different from doing pyspark.ml.feature.BucketedRandomProjectionLSH on a Delta table?" — required answer: Vector Search is a managed, delta-sync, HNSW-backed serving surface with sub-200 ms retrieval and UC-governed access; LSH on Spark is a one-off compute job with no serving.
• "What does the Agent Framework give you that LangChain doesn't?" — required answer: UC-governed asset registration, MLflow 3 tracing, and the evaluation harness with LLM-as-judge scoring — production lifecycle, not just an SDK.
• "When would you fine-tune vs use Foundation Model APIs?" — required answer: fine-tune when a small model on a narrow task beats the largest general-purpose model, especially for classification and structured extraction; use FM APIs for open-ended generation.
• "How do you cost-model Mosaic AI?" — required answer: pay-per-token on FM APIs + DBU-hour on Vector Search endpoint + DBU-hour on Model Serving (if custom) — three line items, easy to forecast.

Worked example — RAG on Delta with vector_search + Foundation Model APIs

Detailed explanation. The canonical Mosaic AI RAG pipeline: create a Delta chunks table, embed each chunk via the Foundation Model APIs embedding endpoint, register a delta-sync vector search index, then compose a SQL query using vector_search and ai_query. Build the whole thing.

• Source. main.support.tickets — Delta.
• Chunks table. main.kb.chunks — Delta, one row per 800-token slice.
• Embedding model. databricks-gte-large-en (1024-dim), served via Foundation Model APIs.
• Generation model. databricks-meta-llama-3-1-70b-instruct.
• Vector index. main.kb.chunks_idx, delta-sync.

Question. Write the chunks table DDL, the embedding compute, the vector index, and the RAG SQL.

Input.

Component	Value
Embedding endpoint	databricks-gte-large-en (1024-dim)
Generation endpoint	databricks-meta-llama-3-1-70b-instruct
Chunks table	Delta, main.kb.chunks
Vector index	main.kb.chunks_idx, HNSW, DELTA_SYNC
VS endpoint	main-vs-endpoint

Code.

# 1. Chunks table + embedding column
from pyspark.sql import functions as F

tickets = spark.table("main.support.tickets")

chunks = (
    tickets
      .withColumn("chunk_no",
                  F.explode(F.sequence(F.lit(0),
                                       F.floor(F.size(F.split("body", " ")) / 700))))
      .withColumn("start",
                  F.col("chunk_no") * 700)
      .withColumn("chunk_text",
                  F.expr("array_join(slice(split(body, ' '), start + 1, 800), ' ')"))
      .select(F.monotonically_increasing_id().alias("chunk_id"),
              "ticket_id", "chunk_text")
)

# 2. Compute embeddings via the Foundation Model APIs embedding endpoint
from databricks.sdk import WorkspaceClient
w = WorkspaceClient()

def embed(texts: list[str]) -> list[list[float]]:
    resp = w.serving_endpoints.query(
        name="databricks-gte-large-en",
        inputs=texts,
    )
    return [row["embedding"] for row in resp.data]

embed_udf = F.udf(lambda batch: embed(batch),
                  "array<array<float>>")

chunks_with_emb = (
    chunks
      .repartition(64)
      .withColumn("embedding",
                  F.explode(embed_udf(F.collect_list("chunk_text").over(...))))
)

chunks_with_emb.write.mode("overwrite").saveAsTable("main.kb.chunks")

# Enable CDF so the Vector Search index can delta-sync
spark.sql("ALTER TABLE main.kb.chunks SET TBLPROPERTIES (delta.enableChangeDataFeed = true)")

# 3. Vector Search endpoint + delta-sync index
from databricks.vector_search.client import VectorSearchClient
vs = VectorSearchClient()

# One-time: create the serving endpoint
vs.create_endpoint(name="main-vs-endpoint", endpoint_type="STANDARD")

# Index bound to the Delta source; auto-refresh on CDF changes
vs.create_delta_sync_index(
    endpoint_name         = "main-vs-endpoint",
    index_name            = "main.kb.chunks_idx",
    source_table_name     = "main.kb.chunks",
    pipeline_type         = "TRIGGERED",       # or "CONTINUOUS" for streaming
    primary_key           = "chunk_id",
    embedding_dimension   = 1024,
    embedding_vector_column = "embedding",
    columns_to_sync       = ["ticket_id", "chunk_text"],
)

-- 4. RAG SQL — one query, retrieve + generate
CREATE OR REPLACE FUNCTION main.kb.rag_answer(question STRING)
RETURNS STRING
LANGUAGE SQL
RETURN (
  WITH hits AS (
    SELECT search_results.chunk_text
    FROM   vector_search(
             index      => 'main.kb.chunks_idx',
             query_text => question,
             num_results => 5
           ) AS search_results
  ),
  ctx AS (
    SELECT array_join(collect_list(chunk_text), '\n\n') AS context FROM hits
  )
  SELECT ai_query(
    'databricks-meta-llama-3-1-70b-instruct',
    named_struct(
      'messages', array(
        named_struct('role', 'system',
                     'content', 'Answer strictly from the context.'),
        named_struct('role', 'user',
                     'content', concat('Context:\n', context,
                                       '\n\nQuestion: ', question))),
      'temperature', 0.2,
      'max_tokens',  250
    )
  )
  FROM ctx
);

Step-by-step explanation.

1. Step 1 chunks each ticket body into ~800-token slices using PySpark's sequence + slice + array_join pattern. Every chunk becomes a Delta row with chunk_id, ticket_id, chunk_text. Delta is the storage layer; everything downstream binds to it.
2. Step 2 embeds each chunk via the databricks-gte-large-en Foundation Model API endpoint. Batching + repartition tune throughput; the endpoint autoscales on demand. ALTER TABLE ... enableChangeDataFeed = true is mandatory for delta-sync vector indices — the index reads the CDF to detect changes.
3. Step 3 creates the Vector Search endpoint (a serving cluster) and a delta-sync index bound to the chunks table. pipeline_type = 'TRIGGERED' refreshes on demand; 'CONTINUOUS' streams updates as they land. The index inherits the source table's UC ACLs — one governance surface.
4. Step 4 exposes RAG as a SQL function. vector_search(...) is a table function returning the top-K hits; ai_query(endpoint, prompt) invokes a Foundation Model APIs endpoint. Callers see one function; the plumbing is invisible.
5. The ai_query call uses OpenAI-style chat message shape (role, content) inside a named_struct — Mosaic's canonical LLM call format. temperature = 0.2 for factual retrieval; max_tokens = 250 for cost control. Wrap the whole thing in a UDF or SQL function so callers never see the endpoint name.

Output.

Query	Retrieval (VS)	Generation (Llama 70B)	Total p95
"How do I reset my password?"	~110 ms	~550 ms	~750 ms
"Why is my invoice wrong?"	~130 ms	~600 ms	~800 ms
"How do I export analytics?"	~120 ms	~500 ms	~700 ms
Cold-start (endpoint warm)	+0 s	+0 s	+0 s

Rule of thumb. For any Databricks Mosaic AI RAG pipeline, keep chunks + embeddings in one Delta table, enable CDF, bind a delta-sync Vector Search index, and expose retrieval + generation as one SQL function using vector_search + ai_query. The Foundation Model APIs endpoint is always warm — no cold-start defense needed.

Worked example — Agent Framework + evaluation harness

Detailed explanation. The Mosaic AI Agent Framework is the production lifecycle around an LLM agent: register the agent in UC, run the evaluation harness on a curated evaluation set, capture MLflow 3 traces on every call, and iterate. Build a simple ticket-triage agent that classifies severity + routes to a queue, and run the eval harness.

• Agent. Python pyfunc that takes a ticket subject + body, calls Foundation Model APIs for classification, and returns a routing decision.
• Eval set. 200 labelled tickets with ground-truth severity.
• Eval metrics. Correctness (LLM-as-judge), latency, cost, groundedness.

Question. Write the agent, register it in UC, and run the evaluation harness.

Input.

Component	Value
Agent asset	main.agents.triage_v1 (UC-registered)
Model	databricks-mixtral-8x7b-instruct
Eval set	main.evals.triage_gold (200 rows)
Metrics	correctness, latency, cost, groundedness

Code.

# 1. The agent — a simple MLflow pyfunc
import mlflow
from mlflow.pyfunc import PythonModel
from databricks.sdk import WorkspaceClient

class TriageAgent(PythonModel):
    def load_context(self, context):
        self.client = WorkspaceClient()

    def predict(self, context, model_input):
        outputs = []
        for row in model_input.itertuples():
            resp = self.client.serving_endpoints.query(
                name="databricks-mixtral-8x7b-instruct",
                messages=[
                    {"role": "system",
                     "content": "You are a ticket triage agent. Return JSON: "
                                "{severity: low|medium|high, queue: billing|tech|generic}."},
                    {"role": "user",
                     "content": f"Subject: {row.subject}\nBody: {row.body}"},
                ],
                temperature=0.1,
                max_tokens=100,
            )
            outputs.append(resp.choices[0].message.content)
        return outputs

# 2. Log + register in Unity Catalog
mlflow.set_registry_uri("databricks-uc")

with mlflow.start_run():
    mlflow.pyfunc.log_model(
        artifact_path="triage_agent",
        python_model=TriageAgent(),
        registered_model_name="main.agents.triage",
    )

# 3. Evaluation harness — run against the golden set
import mlflow
import pandas as pd

eval_df = spark.table("main.evals.triage_gold").toPandas()
# Columns: subject, body, expected_severity, expected_queue

with mlflow.start_run():
    results = mlflow.evaluate(
        data        = eval_df,
        model       = "models:/main.agents.triage/1",
        targets     = "expected_severity",
        model_type  = "databricks-agent",   # enables LLM-as-judge metrics
        evaluators  = ["default"],
        extra_metrics = [
            mlflow.metrics.latency(),
            mlflow.metrics.token_count(),
            mlflow.metrics.genai.answer_correctness(),
            mlflow.metrics.genai.answer_relevance(),
        ],
    )

    print(results.metrics)
    # → {'answer_correctness/v1/mean': 0.87,
    #    'answer_relevance/v1/mean': 0.92,
    #    'latency/mean_ms': 640,
    #    'token_count/mean': 155, ...}

Step-by-step explanation.

1. Step 1 defines the agent as an MLflow PythonModel. load_context initialises the WorkspaceClient once per replica; predict handles per-row inference. Registering the agent as a pyfunc is the canonical way to make it deployable via Model Serving or runnable in eval jobs.
2. Step 2 logs and registers the agent in Unity Catalog (mlflow.set_registry_uri("databricks-uc")). The main.agents.triage name becomes a UC asset with versioning, ACLs, and lineage. Downstream consumers reference it by models:/main.agents.triage/1.
3. Step 3 runs the evaluation harness. mlflow.evaluate(model_type='databricks-agent', ...) triggers the built-in LLM-as-judge metrics — answer_correctness, answer_relevance, groundedness — plus programmatic metrics like latency and token count.
4. The results dict contains a numeric score per metric. answer_correctness/v1/mean = 0.87 means the judge scored 87% of the agent's answers as correct against the ground truth. This is the quality metric you regress on across agent versions.
5. The harness output flows into MLflow's experiment tracking; you can compare triage_v1 vs triage_v2 on the same eval set and gate promotion on quality delta. This is the "production agent lifecycle" that separates Mosaic AI from raw LangChain — LangChain builds the agent; Mosaic ships the CI/CD for it.

Output.

Metric	Value
answer_correctness/v1/mean	0.87
answer_relevance/v1/mean	0.92
latency/mean_ms	640
token_count/mean	155
cost/mean_usd	$0.0002

Rule of thumb. For any Mosaic AI agent going to production, register it in UC via MLflow pyfunc, run the built-in evaluation harness against a curated ground-truth set, and gate promotion on a correctness threshold (e.g. > 0.85). Version every agent iteration; compare metrics across versions. The Agent Framework is your CI/CD for GenAI.

Worked example — Unity Catalog governance across model + data + prompt

Detailed explanation. The distinctive Mosaic AI advantage is a single governance ring across Delta tables, vector indices, model endpoints, agent artifacts, and Python functions. One GRANT verb applies across them all. Walk through granting the "chatbot service account" the minimum viable permissions to run the RAG pipeline end-to-end.

• Assets. Delta table (main.support.tickets), chunks table (main.kb.chunks), vector index (main.kb.chunks_idx), Foundation Model API endpoint, RAG function (main.kb.rag_answer).
• Roles. chatbot_svc (service account); data_engineer (author); audit_admin (audit).

Question. Write the UC grants that let the chatbot invoke the RAG function without granting direct access to the underlying tables or endpoints.

Input.

Asset	Role	Grant
main.kb.rag_answer	chatbot_svc	EXECUTE
Vector index	chatbot_svc	USE via function only
Foundation Model APIs	chatbot_svc	USE via function only
main.support.tickets (raw PII)	chatbot_svc	denied

Code.

-- 1. UC three-level namespace: catalog.schema.object
USE CATALOG main;

-- 2. Column mask on raw PII (applies before embeddings are computed)
CREATE OR REPLACE FUNCTION main.security.mask_pii(val STRING)
RETURNS STRING
LANGUAGE SQL
RETURN CASE
  WHEN is_account_group_member('audit_admin') THEN val
  ELSE regexp_replace(
         regexp_replace(val, r'\b\d{3}-\d{2}-\d{4}\b', '[SSN]'),
         r'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}', '[EMAIL]')
END;

ALTER TABLE main.support.tickets
  ALTER COLUMN body SET MASK main.security.mask_pii;

-- 3. Row filter — chatbot only sees tickets tagged 'public'
CREATE OR REPLACE FUNCTION main.security.public_only(tags ARRAY<STRING>)
RETURNS BOOLEAN
RETURN array_contains(tags, 'public') OR is_account_group_member('audit_admin');

ALTER TABLE main.support.tickets
  SET ROW FILTER main.security.public_only ON (tags);

-- 4. Grants — chatbot_svc gets EXECUTE on the RAG function, nothing else
GRANT USAGE   ON CATALOG main            TO `chatbot_svc`;
GRANT USAGE   ON SCHEMA  main.kb         TO `chatbot_svc`;
GRANT EXECUTE ON FUNCTION main.kb.rag_answer(STRING) TO `chatbot_svc`;

-- Chatbot cannot SELECT the raw tickets table — the function encapsulates access
-- Chatbot cannot query the vector index directly — vector_search runs inside the function
-- Chatbot cannot call ai_query on the endpoint directly — ai_query runs inside the function

Step-by-step explanation.

1. Step 1 sets the working catalog. UC's three-level namespace (main.security.mask_pii) applies uniformly to tables, volumes, functions, and models — one namespace, no per-asset-class variance.
2. Step 2 defines a column mask function that redacts SSN and email unless the caller belongs to audit_admin. ALTER TABLE ... SET MASK applies the mask to the body column of the source table. Every downstream reader — including the embedding compute — sees the masked value. PII redaction propagates to the vector index automatically.
3. Step 3 adds a row filter: chatbot_svc only sees tickets tagged 'public'. Same mechanism as the column mask; same governance surface. The embedding compute skips non-public tickets; the vector index only contains public content.
4. Step 4 grants the minimum viable ACLs. USAGE on catalog + schema lets chatbot_svc see them exist; EXECUTE on rag_answer lets it call the function. It cannot SELECT the raw tickets table, cannot call vector_search directly, cannot call ai_query directly — the function is the only path.
5. The result: the chatbot service account has one grant. All the security policy — masking, row filtering, endpoint routing, function encapsulation — lives in UC and applies uniformly. This is the "one governance ring" pitch delivered in code.

Output.

Caller	Can call rag_answer	Sees masked PII	Sees non-public tickets
chatbot_svc	yes	yes (regex-masked)	no
data_engineer	yes (via role escalation)	no	yes
audit_admin	yes	no (raw values)	yes
anonymous	no	n/a	n/a

Rule of thumb. For any Mosaic AI deployment, wrap the RAG pipeline in a SQL function and grant EXECUTE on the function only. Never grant direct table SELECT or endpoint USE to a chatbot service account. Column masks and row filters live on the source Delta table and propagate to every downstream consumer including embeddings.

Senior interview question on Databricks Mosaic AI

A senior interviewer might ask: "Design a RAG-plus-agent system on Databricks Mosaic AI that answers customer-support questions over a 5-million-row Delta ticket table. The agent must classify severity, route to a queue, and produce an answer with citations. Latency target 1.5 s p95, monthly cost ceiling $8,000, everything governed by Unity Catalog with a masking policy on PII columns. Walk me through the design — the Delta source, the delta-sync vector index, the Foundation Model APIs choice, the agent artifact, and the evaluation harness."

Solution Using Delta + delta-sync Vector Search + Mixtral 8x7B + Agent Framework + MLflow evals

# 1. Delta chunks + CDF for delta-sync
spark.sql("""
CREATE TABLE IF NOT EXISTS main.kb.chunks (
  chunk_id   BIGINT,
  ticket_id  BIGINT,
  chunk_text STRING,
  embedding  ARRAY<FLOAT>,
  tags       ARRAY<STRING>
) USING DELTA
TBLPROPERTIES (delta.enableChangeDataFeed = true)
""")

# 2. Vector Search endpoint + delta-sync index
from databricks.vector_search.client import VectorSearchClient
vs = VectorSearchClient()

vs.create_endpoint(name="main-vs-endpoint", endpoint_type="STANDARD")

vs.create_delta_sync_index(
    endpoint_name           = "main-vs-endpoint",
    index_name              = "main.kb.chunks_idx",
    source_table_name       = "main.kb.chunks",
    pipeline_type           = "CONTINUOUS",
    primary_key             = "chunk_id",
    embedding_dimension     = 1024,
    embedding_vector_column = "embedding",
    columns_to_sync         = ["ticket_id", "chunk_text", "tags"],
)

-- 3. RAG + triage function; chatbot_svc gets EXECUTE only
CREATE OR REPLACE FUNCTION main.kb.rag_answer(question STRING)
RETURNS STRUCT<answer STRING, severity STRING, queue STRING, citations ARRAY<BIGINT>>
LANGUAGE SQL
RETURN (
  WITH hits AS (
    SELECT search_results.ticket_id, search_results.chunk_text
    FROM   vector_search(
             index       => 'main.kb.chunks_idx',
             query_text  => question,
             num_results => 5
           ) AS search_results
  ),
  triage AS (
    SELECT ai_query(
      'databricks-mixtral-8x7b-instruct',
      named_struct(
        'messages', array(
          named_struct('role','system','content','Classify: JSON {severity, queue}.'),
          named_struct('role','user','content', question)),
        'temperature', 0.0,
        'max_tokens', 60)) AS triage_json
  ),
  answer AS (
    SELECT ai_query(
      'databricks-meta-llama-3-1-70b-instruct',
      named_struct(
        'messages', array(
          named_struct('role','system','content','Answer strictly from context. Cite ticket_id.'),
          named_struct('role','user',
                       'content', concat('Context:\n',
                                          (SELECT array_join(collect_list(chunk_text),'\n\n') FROM hits),
                                          '\n\nQuestion: ', question))),
        'temperature', 0.2, 'max_tokens', 250)) AS a
  )
  SELECT named_struct(
    'answer',    a,
    'severity',  json_extract_string(triage_json, '$.severity'),
    'queue',     json_extract_string(triage_json, '$.queue'),
    'citations', (SELECT collect_list(ticket_id) FROM hits))
  FROM answer, triage
);

GRANT EXECUTE ON FUNCTION main.kb.rag_answer(STRING) TO `chatbot_svc`;

# 4. Wrap as agent + evaluation harness
import mlflow

with mlflow.start_run():
    mlflow.pyfunc.log_model(
        artifact_path="support_agent",
        python_model=SupportAgent(),        # wraps rag_answer + adds tool-calling
        registered_model_name="main.agents.support",
        signature=mlflow.models.infer_signature(...))

    results = mlflow.evaluate(
        data       = spark.table("main.evals.support_gold").toPandas(),
        model      = "models:/main.agents.support/1",
        targets    = "expected_answer",
        model_type = "databricks-agent",
        extra_metrics=[mlflow.metrics.latency(), mlflow.metrics.genai.answer_correctness()])

Step-by-step trace.

Layer	Component	Purpose
Storage	Delta with CDF	source of truth for chunks + embeddings
Governance	UC column mask + row filter	PII redacted before embedding
Retrieval	Vector Search delta-sync index	sub-200 ms retrieval on 5M rows
Triage	Mixtral 8x7B	fast classification (~150 ms)
Generation	Llama 3.1 70B	grounded answer with citations
Agent	MLflow pyfunc + UC-registered	versioned, evaluated, promotable
Eval	mlflow.evaluate + LLM judge	correctness gated on numeric metric

After deployment, the chatbot flows: request → rag_answer(question) → parallel vector_search + Mixtral triage → Llama answer → return struct with answer, severity, queue, citations. End-to-end p95 lands at 1.2 s. Monthly cost at 50k queries/day = 1.5M queries/month × (1,300 in + 200 out) tokens = 1.95B in + 300M out = ~$1.95k input + ~$900 output on Mixtral triage (cheap portion) + ~$5.85k input + ~$2.7k output on Llama 70B = ~$11k tokens + ~$700 VS endpoint = ~$12k — over budget; drop to Mixtral 8x7B only for the answer step (~$4k) and use provisioned throughput for the 25% discount to fit ~$8k ceiling.

Output:

Metric	Value
End-to-end p95	~1.2 s
Cost per query (Mixtral answer)	~$0.005
Daily cost at 50k queries	~$250
Monthly compute cost	~$7.8k
Agent correctness (LLM judge)	0.88
Governance surface	UC one ring

Why this works — concept by concept:

• Delta + CDF + delta-sync Vector Search — the vector index auto-refreshes as the Delta source changes; no cron job, no manual reindex. CDF is mandatory and easily forgotten; check TBLPROPERTIES on day one.
• Two-model pipeline (Mixtral for triage, Llama for answer) — cheap fast model for classification, larger model for generation. Splits cost to match task complexity; Mixtral triage at ~$0.50/1M input is ~5× cheaper than Llama 70B for a task that doesn't need 70B reasoning.
• Function-encapsulated RAG — rag_answer is a UC-registered SQL function. chatbot_svc has one EXECUTE grant; every downstream policy (column mask, row filter, endpoint routing) applies through the function. Callers cannot bypass the policy.
• Agent Framework + evaluation harness — the agent is a versioned UC artifact; mlflow.evaluate(model_type='databricks-agent') scores it with LLM-as-judge + programmatic metrics. Promotion is gated on correctness delta, not on developer judgement.
• Cost — pay-per-token on Foundation Model APIs + DBU-hour on Vector Search endpoint + DBU-hour on any custom Model Serving (none here). Line-itemed; forecast-friendly. At 50k qpd on Mixtral-answer, ~$8k/month fits the ceiling with provisioned throughput. Compared to Cortex Llama 70B (~$12k) or GPT-4o (~$16k), Mosaic on open-model endpoints is the cheapest of the three at governed scale.

Design
Topic — design
Design problems on Databricks Mosaic AI systems

Practice →

Streaming Topic — streaming Streaming problems on Delta + delta-sync patterns

Practice →

---

5. Decision matrix + interview signals

The 3-column decision matrix picks the stack; the interview signals prove you can defend it under pressure

The mental model in one line: the cortex vs bigquery ai vs Mosaic AI decision reduces to a 3-column × 5-row matrix scored on cost per token, latency profile, RBAC / governance surface, RAG plumbing, and ecosystem fit — and the senior interview signal is not "which stack you pick" but "which axes you weigh, in what order, and against what numbers" — because every 2026 interviewer knows all three stacks can do RAG, and what separates candidates is the fluency to defend the trade-off without hand-waving. Every senior discussion of AI-native warehouses converges on this matrix; every candidate who can whiteboard it in 90 seconds passes; every candidate who reaches for LangChain or Pinecone fails.

The five decision axes — the rows of the matrix.

• Cost per 1M tokens. BigQuery AI on Gemini Flash is the cheapest text-only tier in 2026 (~$0.075 in, $0.30 out); Databricks Mosaic on Llama 3.1 70B is mid-tier (~$1 in, $3 out); Snowflake Cortex on Llama 3.1 70B is highest per token (~$2.42 in/out via credits) but bundles governance and warehouse scheduling. Cross-model comparisons must be like-for-like — Gemini Flash is a Mixtral 8x7B tier, not a Llama 70B tier.
• Latency profile. Mosaic AI Foundation Model APIs endpoints are always warm; typical p95 700–1500 ms end-to-end. BigQuery slots warm in ~1 s if reserved; Vertex remote call adds 300–800 ms; typical p95 1.5–3 s. Cortex warehouse cold-start is 3–8 s; warm p95 700–1000 ms. Choose Mosaic or warm Cortex for chatbots; either is fine for batch.
• RBAC / governance surface. Snowflake Cortex has the simplest governance story — one GRANT USAGE ON FUNCTION verb + column masking policies. BigQuery AI has the most granular — IAM at Vertex model level + BigQuery ACLs + Data Catalog policy tags + VPC-SC. Databricks Mosaic has the broadest — Unity Catalog governs models, tables, functions, endpoints, and volumes under one three-level namespace with lineage across all asset classes.
• RAG plumbing sophistication. Cortex Search is a managed hybrid vector+keyword service with TARGET_LAG auto-refresh; BigQuery VECTOR_SEARCH with IVF/TREE_AH is highly tunable but requires manual index refresh; Mosaic Vector Search delta-sync auto-refreshes from any Delta table with HNSW. All three are production-grade; Mosaic wins on lakehouse-native, Cortex wins on SQL simplicity, BigQuery wins on scale-out (TREE_AH beats other indices > 10M vectors).
• Ecosystem fit. Snowflake Cortex fits shops already on Snowflake (analyst-driven, SQL-heavy). BigQuery AI fits GCP-native shops (Gemini + Vertex + GCS multimodal). Databricks Mosaic AI fits lakehouse shops (Delta + open-model + agent-heavy). Never introduce a fourth warehouse just to get its GenAI features; the migration cost dwarfs the AI-native advantage.

The RAG-pipeline anatomy every stack implements.

• Chunk. Split source text into ~800-token slices with 100-token overlap. Preserve source IDs for citation.
• Embed. Convert each chunk to a dense vector (768 or 1024 dims). Store next to the chunk in the same table.
• Store. Native vector column (VECTOR(FLOAT, N) on Cortex, ARRAY<FLOAT64> on BigQuery, ARRAY<FLOAT> on Delta) + a managed index.
• Retrieve. Top-K similarity search + hybrid keyword scoring; filter by attribute if needed.
• Prompt. Assemble the retrieved chunks into a system+user prompt with grounding instructions.
• Respond. Call the LLM; enforce temperature and max_tokens; return the completion, optionally with citations.

When warehouse-native GenAI beats an external LLM API — and when it loses.

• Wins. (a) PII must not leave the VPC. (b) Governance requires one ACL surface across data + model. (c) Volume exceeds ~10k queries/day where token savings on hosted open models beat premium API pricing. (d) The workload sits next to the warehouse data — RAG over your own tables — where colocation removes egress + serialisation cost. (e) Multimodal RAG over blob storage — object tables + multimodal Gemini avoid a separate parsing pipeline.
• Loses. (a) You need top-tier reasoning (GPT-4o, o1, Claude Opus) — external API is the only path to the frontier. (b) You're at pilot scale (~1k qpd) where the warehouse warm cost dominates. (c) The data lives outside a warehouse and moving it in is the biggest cost. (d) Your team already has a working LangChain + Pinecone stack and switching cost exceeds savings.

Common senior interview probes on the decision matrix.

• "Which stack for a GCP-native shop that needs multimodal RAG?" — required answer: BigQuery AI with object tables + Gemini multimodal.
• "Which stack for an open-model fine-tuning + agent workload?" — required answer: Databricks Mosaic AI (Foundation Model APIs + Model Serving + Agent Framework).
• "Which stack for a Snowflake shop that wants the lightest-weight RAG?" — required answer: Cortex Search + COMPLETE — one function, one grant.
• "Where does an external LLM API still win?" — required answer: top-tier reasoning workloads (GPT-4o, o1, Claude Opus) or pilot scale where warehouse warm cost dominates.
• "How do you observe cost + latency of your LLM calls?" — required answer: Cortex → QUERY_HISTORY; BigQuery → INFORMATION_SCHEMA.JOBS; Mosaic → MLflow 3 traces + Serving endpoint metrics. Each surface answers "which query cost how much" natively.

Worked example — the 3-stack × 5-axis decision-matrix drill

Detailed explanation. The interview-ready deliverable is a whiteboard matrix scored per axis with a concrete number or dot-rating. Walk through building it for three canonical scenarios: a Snowflake-shop RAG chatbot, a GCP-native multimodal contract Q&A, and a Databricks-shop agent with fine-tuning. The matrix is the artifact; the reasoning is the answer.

• Scenario A. Snowflake-native shop, 50k qpd RAG over text; PII must stay in VPC.
• Scenario B. GCP-native shop, 20k qpd RAG over 20k PDFs (multimodal); tight $3k/mo budget.
• Scenario C. Databricks-native shop, 30k qpd agent classifying + answering tickets; must fine-tune on labelled data.

Question. Score each scenario against the 5×3 matrix and pick the stack.

Input.

Axis	Cortex	BigQuery AI	Mosaic AI
Cost	3/5	5/5	4/5
Latency (warm)	4/5	3/5	5/5
Governance simplicity	5/5	3/5	4/5
Multimodal RAG	2/5	5/5	3/5
Open-model + fine-tune	3/5	3/5	5/5

Code.

# Decision-tree helper — the same tree, three scenarios
def pick_stack(existing_warehouse: str,
                needs_multimodal: bool,
                needs_finetune_open_model: bool,
                pii_stays_in_vpc: bool,
                latency_budget_ms: int) -> str:

    # Rule 1 — stay with the incumbent unless a specific need forces otherwise
    if existing_warehouse == "snowflake"  and not needs_multimodal and not needs_finetune_open_model:
        return "Snowflake Cortex"
    if existing_warehouse == "bigquery"    and not needs_finetune_open_model:
        return "BigQuery AI"
    if existing_warehouse == "databricks":
        return "Databricks Mosaic AI"

    # Rule 2 — multimodal forces BigQuery AI
    if needs_multimodal:
        return "BigQuery AI (multimodal)"

    # Rule 3 — fine-tune forces Databricks Mosaic
    if needs_finetune_open_model:
        return "Databricks Mosaic AI"

    # Rule 4 — governance-first fallback
    if pii_stays_in_vpc:
        return "Snowflake Cortex"

    return "Databricks Mosaic AI"

# Walk the three scenarios
print(pick_stack("snowflake",  False, False, True,  2000))
# → 'Snowflake Cortex'
print(pick_stack("bigquery",   True,  False, True,  2000))
# → 'BigQuery AI'
print(pick_stack("databricks", False, True,  True,  1500))
# → 'Databricks Mosaic AI'

Step-by-step explanation.

1. Scenario A — Snowflake shop, text-only RAG, PII in VPC. The decision tree short-circuits at Rule 1 → Cortex. Additional evidence: masking policies + role grants meet PII requirement; Cortex Search covers hybrid retrieval; warm warehouse absorbs latency. This is the "boring, correct" answer.
2. Scenario B — GCP shop, multimodal PDFs. Rule 1 keeps you on BigQuery; Rule 2 confirms multimodal → BigQuery AI with object tables. Additional evidence: $3k budget fits Gemini Flash (~$1k/mo at 20k qpd); IAM + Data Catalog + VPC-SC cover governance; TREE_AH not needed at 100k chunk scale.
3. Scenario C — Databricks shop, agent + fine-tune. Rule 1 keeps you on Databricks; Rule 3 confirms fine-tune → Mosaic. Additional evidence: Agent Framework + MLflow eval harness give production lifecycle; Foundation Model APIs handle inference on the fine-tuned model; UC governs the fine-tuned artifact under the same rules as any other model.
4. In all three, the incumbent warehouse wins. Cross-migration is almost never the right answer — the switching cost (data movement, RBAC re-architecture, team retraining) dwarfs the AI-native cost savings unless a specific requirement (multimodal, fine-tune, top-tier reasoning) forces the move.
5. The tree encodes the "boring, correct" answer for 90% of interviews. Senior candidates volunteer the tree; junior candidates get to Cortex, BigQuery, or Databricks only by process of elimination when the interviewer names each.

Output.

Scenario	Pick	Reason
A: Snowflake + text RAG + PII	Cortex	incumbent + governance simplicity
B: BigQuery + multimodal + tight budget	BigQuery AI	Gemini Flash cost + object tables
C: Databricks + agent + fine-tune	Mosaic AI	Agent Framework + Model Serving

Rule of thumb. For any AI-native warehouse interview, use the incumbent-first decision tree: stay where your data is unless multimodal, fine-tuning, or top-tier reasoning forces the move. Score each stack on the 5-axis matrix; the winner emerges from the constraints, not from vendor preference.

Worked example — observability + cost attribution across the three stacks

Detailed explanation. Every senior architect must answer "which prompt cost me how much and how long did it take?" The observability surfaces differ across stacks; naming each one is a senior signal. Walk through the queries for each stack.

• Cortex. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY + CORTEX_FUNCTIONS_USAGE_HISTORY give per-invocation credit cost + token counts.
• BigQuery AI. INFORMATION_SCHEMA.JOBS_BY_PROJECT + region-us.INFORMATION_SCHEMA.JOBS capture per-job slot ms + bytes processed + remote-model tokens.
• Mosaic AI. MLflow 3 traces + system.serving.endpoint_usage_logs (in Unity Catalog) give per-request latency + token counts + endpoint attribution.

Question. Write the cost-attribution query for each stack that shows top-10 most expensive LLM calls in the last 24 hours.

Input.

Stack	Metric source	Cost primitive
Cortex	SNOWFLAKE.CORTEX_FUNCTIONS_USAGE_HISTORY	credits per invocation
BigQuery AI	region-us.INFORMATION_SCHEMA.JOBS	slot-ms + remote-call cost
Mosaic AI	system.serving.endpoint_usage_logs	input/output token counts

Code.

-- Cortex — top-10 most expensive Cortex function calls in last 24h
SELECT
    query_id,
    function_name,
    model_name,
    tokens_input,
    tokens_output,
    credits_used_cortex_functions AS credits,
    query_text
FROM   SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
WHERE  start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER  BY credits_used_cortex_functions DESC
LIMIT  10;

-- BigQuery AI — top-10 most expensive ML.GENERATE_TEXT jobs in last 24h
SELECT
    job_id,
    user_email,
    total_slot_ms,
    total_bytes_processed,
    total_bytes_processed / POW(1024, 4) * 5.0 AS slot_cost_usd,   -- $5/TB scanned
    query
FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND  UPPER(query) LIKE '%ML.GENERATE_TEXT%'
ORDER  BY total_slot_ms DESC
LIMIT  10;

-- Mosaic AI — top-10 most expensive Foundation Model API calls in last 24h
SELECT
    endpoint_name,
    request_id,
    request_time,
    input_token_count,
    output_token_count,
    (input_token_count / 1e6) * 1.0
      + (output_token_count / 1e6) * 3.0 AS cost_usd,       -- Llama 70B rates
    request_duration_ms
FROM   system.serving.endpoint_usage_logs
WHERE  request_time > NOW() - INTERVAL 24 HOURS
  AND  endpoint_name = 'databricks-meta-llama-3-1-70b-instruct'
ORDER  BY cost_usd DESC
LIMIT  10;

Step-by-step explanation.

1. Cortex uses SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY — a dedicated view for Cortex-specific cost attribution. Every function invocation lands a row with input/output tokens and credits consumed. Join to QUERY_HISTORY on query_id to correlate with the SQL that triggered the call.
2. BigQuery AI cost attribution is trickier because Vertex remote-model calls are billed separately from BigQuery slot cost. The INFORMATION_SCHEMA.JOBS view shows slot cost + bytes scanned; the Vertex-side per-call cost lives in system.billing.gcp_billing_export or Cloud Billing exports. Reconciling both requires cross-joining on job_id + timestamp.
3. Mosaic AI ships a system.serving.endpoint_usage_logs view (part of Unity Catalog's system tables) that records every request with input/output token counts and duration. Multiply by the published pay-per-token rate for the model and you have per-request cost. MLflow traces add call-level context (which agent called which model with what prompt).
4. In all three cases, the observability query is SQL — no separate observability tool, no separate agent metrics dashboard. This is a distinctive advantage of warehouse-native GenAI: cost and latency are analytical questions answered by SELECT against system tables.
5. Junior candidates say "we'd add Datadog"; senior candidates name the specific system view for the stack in question. The named view is the interview signal.

Output.

Stack	System view / table	Cost primitive	Latency primitive
Cortex	CORTEX_FUNCTIONS_USAGE_HISTORY	credits	QUERY_HISTORY.total_elapsed_time
BigQuery AI	INFORMATION_SCHEMA.JOBS_BY_PROJECT + billing export	slot-ms + Vertex calls	total_slot_ms
Mosaic AI	system.serving.endpoint_usage_logs + MLflow traces	tokens × rate	request_duration_ms

Rule of thumb. For any AI-native warehouse deployment, know the system view for cost attribution on day one. Ship a daily dashboard that sums cost per-model, per-role, and per-endpoint. Cost drift is the leading indicator of a misconfigured retry loop, a runaway agent, or a prompt-injection abuse pattern.

Senior interview question on the decision matrix

A senior interviewer might ask: "You're the platform lead. Your CFO wants a single AI-native warehouse decision for the next three years across four teams: a Snowflake-native BI team, a GCP-native ML team with multimodal data, a Databricks-native ML team that fine-tunes models, and a new microservices team that wants agents. Walk me through your recommendation — do you consolidate on one stack or run multiple, how do you defend the choice to the CFO, and what observability + cost-attribution do you ship on day one?"

Solution Using a "run all three, one per team, one governance charter" recommendation

# Recommendation — multi-stack, unified governance charter

## Decision
- BI team               → Snowflake Cortex (text RAG, semantic-model Analyst)
- GCP ML team           → BigQuery AI       (multimodal RAG, Gemini)
- Databricks ML team    → Mosaic AI         (fine-tune, agents)
- Microservices agents  → Mosaic AI         (Agent Framework + MLflow evals)

## Rationale
- Data gravity dominates: moving 100 TB out of any warehouse to consolidate
  costs 6 months of platform engineering per source.
- Each team's stack is the incumbent for that team; incumbent-first wins on
  every axis (RBAC, ecosystem, retraining, migration risk).
- Consolidation would deliver ~15% cost savings and impose ~$3M migration
  spend + 6 months delay to at least one team's roadmap. Not defensible.

## Unified governance charter (single-owner: Platform Security)
- PII policy: masking + row filter at each source (Cortex masking policy,
  BigQuery Data Catalog policy tags, UC column mask). One central YAML
  catalog of PII fields; each stack references the same catalog.
- Prompt logging: every stack logs (user, prompt, completion) to a central
  audit table (S3 / GCS / Delta), retained 90 days.
- Model registry: each stack registers models in its native registry
  (Cortex FT models, Vertex Model Registry, UC models). Central inventory
  in Confluence, refreshed weekly.
- Cost dashboards: three per-stack dashboards + one consolidated view via
  a nightly export to a central warehouse.

## Observability (day-one shipped)
- Cortex:   SNOWFLAKE.CORTEX_FUNCTIONS_USAGE_HISTORY → daily $/team dash
- BigQuery: INFORMATION_SCHEMA.JOBS + billing export → daily $/team dash
- Mosaic:   system.serving.endpoint_usage_logs + MLflow → daily $/team dash
- Consolidated:  nightly export to central warehouse; anomaly alerts on
                 > 30% daily cost delta per team.

## CFO defence
- Cost: multi-stack costs ~$450k/year across four teams (baseline).
        Single-stack consolidation costs ~$385k/year (post-migration)
        + $3M migration + 6-month delay. Break-even at 8 years.
- Risk: three-stack posture reduces vendor concentration; each stack has
        first-class governance; audit surface is unified via the charter,
        not via consolidation.
- Talent: retaining team's incumbent stack retains team; forced migration
        costs 20-40% attrition in the affected team.

## Bottom line
- One AI-native warehouse per team; one governance charter across all.
- Revisit consolidation only if a stack loses feature parity or a team
  changes leadership. Do not force consolidation for its own sake.

Step-by-step trace.

Concern	Answer	Reasoning
Consolidation vs multi-stack	multi-stack	data gravity + team incumbent
PII policy	central YAML + per-stack implementation	one policy, three enforcement points
Prompt audit	central S3/GCS/Delta table	90-day retention; queryable
Cost dashboards	per-stack + consolidated	anomaly alerts on delta
CFO defence	break-even 8 yrs	migration + delay dominate savings
Talent retention	keep incumbent	20-40% attrition on forced move

After the presentation, the CFO signs off on the multi-stack + unified-charter approach. Each team ships a per-stack cost dashboard within two weeks; the consolidated view lands within a month; the PII catalog and audit table go live within a quarter. Platform Security owns the charter; each team owns their stack.

Output:

Team	Stack	Monthly cost estimate	Governance
BI (Snowflake)	Cortex	~$12k	masking policy + role grants
GCP ML	BigQuery AI	~$8k	Data Catalog + IAM + VPC-SC
Databricks ML	Mosaic AI	~$15k	Unity Catalog + MLflow
Microservices agents	Mosaic AI	~$10k	Unity Catalog + Agent Framework

Why this works — concept by concept:

• Incumbent-first stack selection — data gravity + RBAC continuity + team retention beat cost savings from consolidation at every realistic scale. The "one warehouse to rule them all" instinct is a CFO-favourite that senior architects push back on with numbers.
• Unified governance charter — one policy owner (Platform Security) with one PII catalog, one prompt audit table, and one cost-anomaly rulebook — implemented three times, once per stack. Governance uniformity does not require platform uniformity.
• Per-stack observability — each stack has a first-class cost + latency system view (Cortex, BigQuery, Mosaic). Ship the dashboards on day one; consolidate via nightly export, not via forced consolidation of the platforms themselves.
• Break-even math against consolidation — quantify the migration + delay cost. If break-even is > 5 years, do not consolidate; if < 2 years, do. This is the only defensible way to answer "why don't we just pick one stack?"
• Cost — ~$45k/month across four teams at the volumes above. Multi-stack overhead is < 5% (unified charter + dashboard export). Compared to a forced consolidation costing $3M + 6-month delay, multi-stack is the cheapest posture that keeps every team productive. O(1) marginal cost per new team using an existing stack; O(migration cost) per team forced to switch. Choose the platform once per team; live with the decision for the platform's useful life.

Design
Topic — design
Design problems on multi-stack AI-native warehouse architectures

Practice →

SQL
Topic — sql
SQL problems on cost-attribution and LLM-call observability

Practice →

---

Cheat sheet — AI-native warehouse recipes

• Which stack when. Snowflake Cortex is the default for teams already on Snowflake whose analysts write SQL and whose CFO understands Snowflake credits — best when text-only RAG plus lightweight governance is the requirement. BigQuery AI is the default for GCP-native shops especially when multimodal (PDF / image / audio) RAG is on the table, when Gemini Flash's ~$0.075/1M input pricing dominates cost, or when object tables over GCS collapse a parsing pipeline into two lines of DDL. Databricks Mosaic AI is the default when you need open-model fine-tuning, an agent framework with an evaluation harness, MLflow 3 tracing across every LLM call, or Unity Catalog governing models + data + prompts under one three-level namespace. Never introduce a fourth warehouse just for its GenAI features; the migration cost dwarfs the AI-native advantage in every realistic 3-year horizon.
• Snowflake Cortex RAG template. ALTER TABLE ... ADD COLUMN embedding VECTOR(FLOAT, 1024); then UPDATE ... SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_1024('snowflake-arctic-embed-l', chunk_text); then CREATE CORTEX SEARCH SERVICE ... ON chunk_text ATTRIBUTES ... WAREHOUSE = cortex_wh TARGET_LAG = '1 minute'; then a single SQL function that calls SEARCH_PREVIEW to retrieve top-K, LISTAGG(chunk_text, '\n\n') WITHIN GROUP (ORDER BY score DESC) to assemble context, and SNOWFLAKE.CORTEX.COMPLETE('llama3.1-70b', prompt, OBJECT_CONSTRUCT('temperature', 0.2, 'max_tokens', 250)) to generate. Grant USAGE on the function only; the chatbot service account never touches CORTEX.* directly.
• BigQuery AI RAG template. CREATE MODEL prod.emb.text_emb REMOTE WITH CONNECTION 'us.vertex_conn' OPTIONS (endpoint='text-embedding-005'); (and similarly for gemini-2.0-flash). Chunks table with embedding ARRAY<FLOAT64> populated via ML.GENERATE_EMBEDDING(MODEL ..., (SELECT chunk_text AS content ...), STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)). Vector index: CREATE VECTOR INDEX ... ON t(embedding) OPTIONS(index_type='IVF', distance_type='COSINE', ivf_options='{"num_lists":2500}');. RAG SQL joins ML.GENERATE_EMBEDDING (query, RETRIEVAL_QUERY) → VECTOR_SEARCH(TABLE t, 'embedding', (query_vec), top_k => 5, options => '{"fraction_lists_to_search":0.05}') → assemble → ML.GENERATE_TEXT(MODEL prod.llm.gemini_flash, (SELECT prompt), STRUCT(0.2 AS temperature, 250 AS max_output_tokens, TRUE AS flatten_json_output)). Unwrap the JSON with ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'].
• Databricks Mosaic AI RAG template. Delta chunks table with delta.enableChangeDataFeed = true; embedding ARRAY<FLOAT> populated via Foundation Model APIs databricks-gte-large-en endpoint. VectorSearchClient().create_delta_sync_index(endpoint_name, index_name, source_table_name, pipeline_type='CONTINUOUS', primary_key='chunk_id', embedding_dimension=1024, embedding_vector_column='embedding', columns_to_sync=[...]). RAG SQL function: vector_search(index => 'main.kb.chunks_idx', query_text => question, num_results => 5) → assemble via array_join(collect_list(chunk_text), '\n\n') → ai_query('databricks-meta-llama-3-1-70b-instruct', named_struct('messages', array(...), 'temperature', 0.2, 'max_tokens', 250)). Grant EXECUTE on the function only; UC column masks apply through the whole chain.
• Cost per 1M tokens comparison (2026, published rates). BigQuery AI on Gemini 2.0 Flash: ~$0.075 in / $0.30 out (cheapest text-only tier). Databricks Mosaic AI on Mixtral 8x7B: ~$0.50 in/out. Databricks Mosaic AI on Llama 3.1 70B: ~$1.00 in / $3.00 out. Snowflake Cortex on Mixtral 8x7B: ~$0.44 in/out (0.22 credits × $2). Snowflake Cortex on Llama 3.1 70B: ~$2.42 in/out (1.21 credits × $2). External GPT-4o: $5 in / $15 out. Warehouse-native always beats external premium APIs at scale; Gemini Flash wins the cheap-text tier; Mosaic pay-per-token beats Cortex on Llama tier because Cortex bills through warehouse credits with a mark-up.
• RBAC + PII mask template (Cortex). CREATE MASKING POLICY pii_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('AUDIT_ADMIN') THEN val ELSE REGEXP_REPLACE(val, '\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b', '[SSN]') END; then ALTER TABLE chunks MODIFY COLUMN chunk_text SET MASKING POLICY pii_mask;. On BigQuery AI, use Data Catalog policy tags on the column plus a data_masking_policy binding. On Mosaic AI, use a Unity Catalog column mask function CREATE FUNCTION ... RETURN CASE WHEN is_account_group_member(...) THEN val ELSE regexp_replace(...) END and ALTER TABLE ... ALTER COLUMN body SET MASK. The mask fires before the embedding compute; PII never enters the vector index.
• RAG anatomy in six boxes. (1) Chunk — split source text into 800-token slices with 100-token overlap; preserve source ID for citation. (2) Embed — 768- or 1024-dim dense vectors via a native embedding function. (3) Store — native vector column next to the chunk text in the same table. (4) Retrieve — top-K similarity + optional keyword hybrid + attribute filter. (5) Prompt — assemble retrieved chunks into system+user prompt with grounding instructions and citation format. (6) Respond — LLM call with temperature=0.2 for factual retrieval, max_tokens bounded for cost, structured-output helpers for typed responses. Every stack implements all six boxes with different function names.
• Decision matrix in 30 seconds. Existing warehouse? If Snowflake and text-only + light governance → Cortex. If BigQuery or multimodal → BigQuery AI. If Databricks or fine-tune or agent-framework → Mosaic AI. Cost check — Gemini Flash for pure cheap text; Mixtral 8x7B on Mosaic for mid-tier; Llama 3.1 70B on any stack for reasoning. Governance check — Cortex simplest, Mosaic broadest (UC), BigQuery most granular. Latency check — Mosaic always-warm, Cortex needs warm warehouse, BigQuery needs reserved slots. Score all four axes; the winner emerges from constraints.
• Observability + cost-attribution queries. Cortex: SELECT function_name, model_name, credits_used_cortex_functions FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY WHERE start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP()) ORDER BY credits_used_cortex_functions DESC;. BigQuery: SELECT job_id, total_slot_ms, total_bytes_processed FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE UPPER(query) LIKE '%ML.GENERATE%';. Mosaic: SELECT endpoint_name, input_token_count, output_token_count, request_duration_ms FROM system.serving.endpoint_usage_logs WHERE request_time > NOW() - INTERVAL 24 HOURS;. Ship a per-stack daily dashboard; anomaly-alert on > 30% day-over-day cost delta per team.
• Embedding-model choice. Cortex: snowflake-arctic-embed-l (1024-dim; Snowflake-native; ~$0.02/1M tokens) for English; e5-base-v2 (768-dim) for multilingual. BigQuery AI: text-embedding-005 (768-dim; Vertex-hosted) is the default; text-multilingual-embedding-002 for cross-lingual. Mosaic AI: databricks-gte-large-en (1024-dim; Databricks-hosted) is the default; databricks-bge-large-en alternative. Always match task_type = RETRIEVAL_DOCUMENT for chunks and RETRIEVAL_QUERY for the question — asymmetric embedding models score noticeably better with the correct task type.
• Latency budget breakdown. Warm-endpoint p95 target 1.5 s for chatbots. Budget: 100 ms client roundtrip + 200 ms retrieval + 700 ms generation (200 output tokens on Llama 70B) + 200 ms buffer. Cortex adds 3–8 s cold-start; mitigate with AUTO_SUSPEND = 3600 on the search warehouse. BigQuery adds 500 ms–2 s slot warmup; mitigate with reserved slots + a hot-warmup query on cron. Mosaic Foundation Model APIs are always-warm; no mitigation needed. Provisioned throughput on Mosaic guarantees capacity at 25% discount for known QPS.
• RAG evaluation harness — the three metrics that matter. (1) Retrieval@K — for each labelled question, does the correct chunk appear in the top-K retrieved set? Numeric metric between 0 and 1. (2) Answer correctness — LLM-as-judge score comparing the generated answer to the ground-truth answer; on Mosaic, mlflow.metrics.genai.answer_correctness(). (3) Groundedness — LLM-as-judge score on whether every claim in the answer is supported by the retrieved context; catches hallucination even when the answer is correct-sounding. Ship a golden set of 100–500 questions; gate promotion on correctness > 0.85 and groundedness > 0.90. All three stacks can host the harness; Mosaic has the most integrated tooling via MLflow.
• When warehouse-native GenAI is the wrong choice. (a) You need frontier-tier reasoning (GPT-4o, o1, Claude Opus) — none of the three stacks host those; call the external API and accept the governance cost. (b) You're at pilot scale < ~5k queries/day where the warehouse warm cost or slot reservation dominates the token cost. (c) The source data lives outside a governed warehouse and moving it in is the biggest project cost. (d) You have a working LangChain + Pinecone + external-LLM stack today and switching would cost > 6 months of engineering. Warehouse-native is the boring, correct answer when data, governance, and volume all favour it — not by default in every scenario.
• Interview-ready one-liner per stack. Cortex: "hosted LLMs and vectors as first-class SQL functions governed by role grants — one credit invoice." BigQuery AI: "Vertex models registered as remote models, called from SQL, backed by VECTOR_SEARCH and object tables for multimodal." Mosaic AI: "open-model Foundation APIs plus delta-sync Vector Search plus Agent Framework plus MLflow evals plus Unity Catalog — one governance ring across model + data + prompt." Rehearse each until it flows without pause; deploy against the interviewer's stack question in the first minute.

Frequently asked questions

What is an AI-native data warehouse in one sentence?

An ai-native data warehouse is a cloud data platform where hosted LLMs, native vector search, embedding functions, RAG pipelines, a model registry, and agent frameworks are first-class SQL or notebook objects governed by the same RBAC and lineage system as the data itself — so a query engineer can call an LLM, embed a document, retrieve top-K by cosine similarity, and register a versioned agent artifact without ever leaving the warehouse's authentication perimeter or opening a ticket for a new vendor. In 2026 the three canonical stacks are snowflake cortex (Cortex Functions, Cortex Search, Cortex Analyst), bigquery ai (ML.GENERATE_TEXT() + VECTOR_SEARCH() + Gemini remote models + object tables), and databricks mosaic ai (Foundation Model APIs, delta-sync Vector Search, Agent Framework, MLflow 3, Unity Catalog). The distinguishing feature is not "the warehouse can call an LLM" — that's been possible via UDFs for years — but that the LLM call, the embedding, the vector index, and the retrieval are all governed by the same ACL surface as the tables, which is what makes the pattern operationally viable at enterprise scale.

Snowflake Cortex vs BigQuery AI vs Databricks Mosaic AI — how do I pick?

Pick based on your incumbent warehouse first, then match specific requirements. If your data already lives in Snowflake and your team is SQL-heavy, pick Snowflake Cortex — one function, one grant, one credit invoice; Cortex Search covers hybrid retrieval; Cortex Analyst covers text-to-SQL; masking policies cover PII. If your data lives in BigQuery and you need multimodal RAG (PDFs, images) or you want the cheapest text-only tier via Gemini Flash, pick BigQuery AI — object tables collapse PDF parsing into DDL; VECTOR_SEARCH with IVF/TREE_AH covers retrieval at any scale; IAM + Data Catalog + VPC-SC cover governance. If your data lives in Databricks or you need to fine-tune open models, build agents with an evaluation harness, or govern model + data + prompt under one namespace, pick Databricks Mosaic AI — Foundation Model APIs cover inference on Llama / DBRX / Mixtral; delta-sync Vector Search auto-refreshes from any Delta table; Agent Framework + MLflow 3 give you production agent lifecycle; Unity Catalog is one ring across every asset class. Never migrate warehouses just to get better AI features; the migration cost dwarfs the AI-native advantage in every 3-year horizon. Multi-stack (one per team, unified governance charter) is the correct answer for most large organisations.

Do I still need a separate vector database like Pinecone or Weaviate in 2026?

Almost never for new work. All three ai-native data warehouse stacks now ship first-class vector search: Snowflake has VECTOR(FLOAT, N) column type + VECTOR_COSINE_SIMILARITY + the managed Cortex Search hybrid service with auto-refresh via TARGET_LAG; BigQuery has VECTOR_SEARCH() with IVF and TREE_AH indices tuned for corpora up to 100M+ vectors; Databricks has Vector Search with delta-sync auto-indexing over any Delta table, HNSW-backed sub-200 ms retrieval, and Unity Catalog governance. A dedicated vector DB (Pinecone, Weaviate, Qdrant, Milvus) is still defensible in three narrow scenarios: (a) your primary workload is a low-latency semantic-search application whose latency budget cannot tolerate any warehouse-side variance and the vector-DB's specialised serving beats warehouse-native by a decisive margin, (b) your source data doesn't naturally live in a warehouse and moving it in for indexing is genuinely more expensive than syncing to a vector DB, or (c) you already have a production Pinecone deployment and switching costs exceed savings. For any new RAG workload where the source is warehouse-resident, warehouse-native vector search is the correct default. The "add Pinecone" reflex is a 2022–2023 pattern that was superseded when the warehouses shipped native vectors.

How do I control the cost of warehouse-native LLM calls?

Cost control is a five-layer problem: model choice, prompt length, retrieval scope, warehouse configuration, and observability. Model choice is the biggest lever — Gemini 2.0 Flash on BigQuery AI is ~30× cheaper per token than Llama 3.1 70B on Cortex or Mosaic; use the smallest model that meets your quality bar, not the largest available. Prompt length — cap context at top-K=5 chunks (roughly 800 tokens each); truncate retrieved chunks to their most relevant sentences via a cheap summarisation model if needed. Retrieval scope — filter the vector search by attribute (priority, region, tag) so you retrieve fewer, more relevant chunks; every extra retrieved chunk becomes tokens in the prompt. Warehouse configuration — Cortex: right-size the warehouse (MEDIUM covers most chatbots), set AUTO_SUSPEND = 3600 to keep it warm during business hours, use resource monitors to alert on runaway cost. BigQuery: reserve slots for predictable load, set project-level quotas on ML.GENERATE_TEXT, use INFORMATION_SCHEMA.JOBS to track the top spenders. Mosaic: buy provisioned throughput for the 25% discount on known QPS, set MAX_CONCURRENT_REQUESTS on the endpoint. Observability — ship a per-stack cost dashboard using the native system views (CORTEX_FUNCTIONS_USAGE_HISTORY, INFORMATION_SCHEMA.JOBS, system.serving.endpoint_usage_logs) and alert on > 30% day-over-day cost delta per team; cost drift is the leading indicator of a misconfigured retry loop, a runaway agent, or a prompt-injection abuse pattern.

How do I mask PII from LLM prompts?

Mask at the source column level so the redaction propagates transparently to embeddings, retrievals, and prompts — never at the application layer, which is easy to bypass. On Snowflake Cortex, define a MASKING POLICY (CREATE MASKING POLICY pii_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('AUDIT_ADMIN') THEN val ELSE REGEXP_REPLACE(val, '\\b[0-9]{3}-[0-9]{2}-[0-9]{4}\\b', '[SSN]') END;) and apply it via ALTER TABLE ... MODIFY COLUMN chunk_text SET MASKING POLICY pii_mask; — every read, including reads that feed prompts, applies the mask. On BigQuery AI, use Data Catalog policy tags on the sensitive columns plus a data-masking-policy binding to redact for roles that lack the appropriate access tag. On Databricks Mosaic AI, define a Unity Catalog column mask function (CREATE FUNCTION main.security.mask_pii(val STRING) RETURNS STRING RETURN CASE WHEN is_account_group_member('audit_admin') THEN val ELSE regexp_replace(...) END) and apply via ALTER TABLE ... ALTER COLUMN body SET MASK main.security.mask_pii; every downstream reader — including the embedding compute — sees the masked value. The critical property in all three cases is propagation: because the mask runs at read time on the source column, the vector index built from that column contains masked chunks, the retrieval returns masked chunks, and the LLM prompt is assembled from masked chunks. PII never enters the prompt, even if the raw source contained it. Encapsulate the RAG pipeline inside a SQL function and grant only EXECUTE on the function to the chatbot service account — this prevents the caller from bypassing the mask by querying the raw table directly.

What are the most-asked interview questions on AI-native data warehouses in 2026?

The senior AI-native warehouse interview probes five families of questions. Stack selection — "How would you build a RAG chatbot on our warehouse?" (expects: name the stack in minute one; defend it on cost + latency + governance + RAG + ecosystem). Cost modelling — "What would 1M tokens cost on Cortex vs BigQuery AI vs Mosaic?" (expects: published per-token rates for a named model on each stack, multiplication to a monthly figure, and the recognition that Gemini Flash is 30× cheaper per token than Llama 70B). RAG design — "Walk me through the RAG pipeline for this data" (expects: chunk → embed → store → retrieve → prompt → respond, with named functions per stack and the choice of embedding model, chunk size, hybrid vs pure-vector retrieval, and prompt template). Governance + PII — "How do you keep prompts inside the VPC?" (expects: warehouse-native reasoning, column-level masking policies, function-encapsulated RAG, role grants on the function only). Observability + cost attribution — "How do you know which prompt cost the most yesterday?" (expects: the named system view per stack — CORTEX_FUNCTIONS_USAGE_HISTORY, INFORMATION_SCHEMA.JOBS, system.serving.endpoint_usage_logs — and a daily dashboard workflow). Bonus probes for very senior roles: text-to-SQL choice (Cortex Analyst semantic model vs raw prompting), agent evaluation harness design (Mosaic MLflow evaluate + LLM-as-judge metrics), latency budgeting for chatbots (100 ms client + 200 ms retrieval + 700 ms generation + 200 ms buffer), and the "when would you not use warehouse-native" question (frontier reasoning, pilot scale, ungoverned source data, existing LangChain investment). Rehearse the 5-minute stack-selection monologue against Cortex, port the template to BigQuery AI and Mosaic by swapping function names, and you'll land any question in this family.

Practice on PipeCode

• Drill the SQL practice library → for the LLM-function, vector-search, and cost-attribution SQL problems that senior AI-native interviewers actually load into their evaluation sets.
• Rehearse on the design practice library → for the AI-native warehouse architecture, RAG-pipeline, and multi-stack governance scenarios that separate architects from implementers.
• Sharpen the streaming axis with the streaming practice library → for delta-sync vector-index, CDF-to-embedding, and event-driven RAG-refresh patterns that ship in production Mosaic AI + Cortex deployments.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the three-stack decision matrix against real graded inputs — cost tables, latency budgets, governance charters, and RAG-anatomy questions per stack.

Lock in ai-native data warehouse muscle memory

Docs explain the SQL. PipeCode drills explain the decision — when Cortex's role-grant simplicity earns its credit-per-token premium, when BigQuery AI's Gemini Flash pricing beats every alternative, when Databricks Mosaic AI's Agent Framework is worth the DBU bill, when the CFO's "just consolidate" reflex is wrong. Pipecode.ai is Leetcode for Data Engineering — stack-first practice tuned for the AI-native warehouse trade-offs that senior data engineers actually defend in 2026 interviews.

Practice SQL problems →
Practice design problems →