SNOWFLAKE + CORTEX — CRASH COURSE FOR PFIZER BA AI TESTING INTERVIEW · 2026-04-09 · 4:00 PM ET
John Holstein · Architecture mental model · Cortex AI modules (know cold) · Validation pattern · Failure modes · Interview language
Context: Jay Shetty owns MedConnect (148+ sources) + eLAAD · Elnaz consumes outputs for care gap / HCP prioritization
The chatbot/agent under test is almost certainly Snowflake Cortex sitting on top of these data lakes
Snowflake architecture — the mental model (2 sentences)
What Snowflake is: Cloud data platform that separates storage from compute. Data lives in tables; compute (virtual warehouses) runs queries on demand. Pfizer context: enterprise data lake — 148+ sources ingested into structured tables across 15+ global markets.
Key concepts — hierarchy and containers
Database → Schema → Table. Hierarchical organization. MedConnect is probably a database. Medical Affairs data is schemas inside it. HCP records, claims data, care gap data are tables inside those schemas.
Stage. The loading zone. Raw data from 148 sources lands in stages before being loaded into tables. This is where data quality issues enter the system. Schema change in the source = broken pipeline = missing fields downstream.
Time Travel. Snowflake keeps data history. You can query what a table looked like at a past timestamp. Testing relevance: "Did the AI see the same data I'm checking against?" If data was refreshed between AI call and SQL check, timestamps matter.
Data Sharing. External vendors (IQVIA, Symphony Health, MMIT) can share data directly into Snowflake without copying files. Pfizer almost certainly uses this for commercial prescribing data and claims feeds.
Streams & Tasks. Automated pipelines that keep tables current. If a stream breaks, data goes stale. AI outputs become wrong — not because of the AI, but because the upstream feed stopped. This is a data quality failure, not a prompt failure.
MedConnect data flow — how data gets in
148 sources
↓
ingestion pipeline
↓
Snowflake stages (raw landing zone)
↓
structured tables (HCP · claims · care gaps · market data)
↓
AI-ready for Cortex
Each step is a failure point. Stale ingestion = AI answers from old data. Source schema change = broken pipeline = missing fields = hallucination risk. Data Sharing vendor update = new column names = query failure. These are Jay Shetty's problems to fix — flag them, don't own them.
Virtual warehouse — one concept only
What it is: The compute engine that runs queries. Separate from storage. You don't need to know sizing, pricing, or configuration. The one thing that matters for testing: if a warehouse is paused or undersized, query validation can time out — note it, move on.
★ CORTEX AI MODULES — KNOW THESE COLD (most critical section)
What Cortex is: Snowflake's native AI/ML layer. Runs LLMs and ML functions directly inside the warehouse — data never leaves Snowflake. Why Pfizer uses it: compliance, data sovereignty, no external API calls with patient/HCP data.
1Cortex Analyst — Natural Language to SQL
User types a question in plain English → Cortex writes SQL → runs it → returns an answer
Example: "Who are the top 10 HCPs for oncology in the Northeast?" → Cortex generates SELECT → returns ranked list.
Testing question: Did it query the RIGHT table? RIGHT column? RIGHT filter? A valid SQL query can return wrong results if it queries hcp_tier_2024 instead of hcp_tier_2026. This is the #1 failure mode.
How to test: Ask the AI → inspect the generated SQL (Cortex shows it) → verify the SQL matches what a data analyst would write. Wrong answer from correct SQL = data quality. Wrong answer from wrong SQL = query generation failure. They look identical to the user. Different fixes.
2Cortex Search — Vector / Semantic Search
Converts documents and records to embeddings (vectors). Returns semantically similar records even if exact words don't match.
Example: Search "care gaps in cardiovascular patients in Southeast" → returns relevant records without exact keyword match.
How to test: Compare retrieved records against a known-correct SQL query on the same data. If AI retrieves 5 records but SQL returns 12 → retrieval gap = failure. The gap is the finding.
3Cortex Complete — LLM Inference / Generation
Runs a prompt + context through an LLM (LLaMA, Mistral, or others) inside Snowflake. Summarizes, classifies, or generates text from structured data.
Example: Summarize all care gap notes for a given HCP into a rep briefing.
How to test: Compare LLM summary against the source records directly. Any claim not traceable to a source record = hallucination. Check for fabricated NPI numbers, HCP names, dates — numerics and proper nouns are highest-risk.
4Document AI — Unstructured Extraction
Extracts structured data from PDFs, images, unstructured text.
Example: Extract HCP specialty, address, NPI number from a scanned enrollment form.
How to test: Compare extracted fields against the source document visually, or against an existing reference record. Fabricated or missing NPI = critical failure.
Cortex ML Functions (orientation only): Built-in forecasting, anomaly detection, classification — no model building needed. Example: forecast which care gaps will widen next quarter. Testing angle: are the predictions being used correctly downstream? Are thresholds set appropriately for business decisions?
The validation pattern — say this verbatim if asked
AI produces output
↓
Write / request SQL against the same source table
↓
Compare: does AI answer match SQL result?
↓
If NO → root cause:
  (A) Retrieval failure — AI didn't get the right records
  (B) Prompt failure — got the right records, misread them
  (C) Data quality — source records are wrong, stale, or missing
Key diagnostic question: "What can the AI actually SEE in the warehouse?" The gap between what the AI can access and what the business user expects it to know is where most failures live.
Three failure modes — know cold
(A) Retrieval failure. AI queried the wrong table, wrong time range, wrong filter. Looks correct to the user — returns data, no error, just wrong data. Fix: correct the Cortex Analyst query logic or improve metadata tagging for Cortex Search. This is the most insidious failure — no error message, confident wrong answer.
(B) Prompt failure. Retrieved the right data, but the LLM misinterpreted it — hallucinated a field, conflated two records, generated plausible-sounding but fabricated text. Fix: tighten system instructions, add verbatim constraints for numerics/names, test with edge-case records.
(C) Data quality. Source data in Snowflake is wrong, stale, or incomplete. AI output is technically correct given what it sees — the data is the problem. Fix: work with Jay Shetty's data engineering team to fix the pipeline. This is NOT a prompt fix. Fixing the prompt to compensate = hiding a data problem.
Interview language — say these exactly
"My first question is always: what does the AI have access to vs. what does the analyst expect it to see?"
"I validate by writing the ground truth query and comparing — if they diverge, I root-cause whether it's retrieval, prompt, or source data."
"For Cortex Analyst I always inspect the generated SQL — the AI can produce a valid query that answers the wrong question."
"Data quality issues in the ingestion pipeline are upstream of the AI — I'd flag those to your data engineering team, not fix them in the prompt."
Weak spot defense: "I've used Snowflake for AI output validation at OptumInsight — SQL checks against Medicaid claims data. I understand the Cortex modules conceptually and the validation pattern. The specific schema of your data lake I'd learn in week one. The testing methodology doesn't change with the schema."
What you own vs. what you don't
You ownYou don't own
Defining what correct looks likeWriting production SQL
Comparing AI output to expectedPipeline engineering
Root-causing which layer failedSnowflake administration
Documenting findings for engineeringFixing the data lake
Recommending prompt or data fixDeploying the fix
Quick orientation chips — Pfizer context
MedConnect = Jay Shetty's DB 148+ ingested sources 15+ global markets eLAAD = open claims Elnaz = care gap consumer Cortex on top of data lake NL→SQL = Cortex Analyst Vector = Cortex Search LLM gen = Cortex Complete PDF/forms = Document AI Wrong table = #1 silent failure Stale pipeline = not a prompt fix