Data-Driven AI in Snowflake: Feeding LLMs Without Losing Control

If you’ve been experimenting with Snowflake Cortex or external LLM calls, you’ve probably hit that moment when your prompts start pulling data dynamically… and your results get a little odd.

One day, it’s spitting out perfect business summaries.
The next day, it’s writing fan fiction about your sales metrics.

Welcome to the messy middle — where data meets AI. In this post, we’re going to help bring that chaos under control.

Goal: Feed the LLM Structured Data, Not Word Salad

Most developers make the same rookie mistake: dumping full query results into a prompt. You don’t need to feed a model the entire table — you curate the experience. Like a good mixtape.

Instead of this:

SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'gpt-4o-mini',
  'Analyze this dataset: ' || TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT(*)))
)
FROM SALES;

Do this:

Aggregate or summarize before sending.
Limit your dataset (no one needs 10k rows in context).
Add schema cues so the model knows what the columns mean.

WITH summary AS (
  SELECT REGION, SUM(SALES_AMOUNT) AS total_sales, COUNT(*) AS transactions
  FROM SALES
  WHERE SALE_DATE >= DATEADD('month', -1, CURRENT_DATE())
  GROUP BY REGION
)
SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'gpt-4o-mini',
  'Given this summary data: ' || TO_JSON(ARRAY_AGG(summary)) ||
  ' — identify the top performing region and any anomalies.'
) AS ai_response
FROM summary;

🎵 “You can’t always get what you want…” — The Rolling Stones
But if you structure your data, you just might get what you need.

Add Context Windows, Not Chaos

Snowflake Cortex doesn’t “remember” prior queries — each call is stateless.
If you need context, you control the history.

Here’s how to simulate conversational context between prompts:

CREATE OR REPLACE TABLE ai_context (
    session_id STRING,
    message_order INT,
    role STRING,
    content STRING
);

Then append as you go:

INSERT INTO ai_context VALUES
('session_1', 1, 'user', 'Summarize last month’s sales trends.'),
('session_1', 2, 'assistant', 'Top regions: East and Midwest.'),
('session_1', 3, 'user', 'Drill into Midwest performance.');

Now, retrieve your “conversation” and pass it back in:

WITH convo AS (
  SELECT LISTAGG(role || ': ' || content, '\n') AS context
  FROM ai_context
  WHERE session_id = 'session_1'
)
SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'gpt-4o-mini',
  convo.context || '\nProvide a detailed breakdown of Midwest sales performance.'
) AS ai_response
FROM convo;

“Don’t go breaking my prompt.” — Elton John (maybe)

Chain Multiple Cortex Calls

Sometimes, one prompt isn’t enough. You might need to:

Summarize data
Extract insights
Generate recommendations

You can chain Cortex calls right inside Snowflake with CTEs or stored procedures.

WITH summary AS (
  SELECT SNOWFLAKE.CORTEX.COMPLETE(
    'gpt-4o-mini',
    'Summarize key sales trends from this JSON: ' ||
    TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT(*)))
  ) AS text_summary
  FROM SALES
  WHERE SALE_DATE >= DATEADD('month', -1, CURRENT_DATE())
),
recommendation AS (
  SELECT SNOWFLAKE.CORTEX.COMPLETE(
    'gpt-4o-mini',
    'Based on this summary, suggest 3 actions for next month: ' || text_summary
  ) AS recs
  FROM summary
)
SELECT * FROM recommendation;

You’ve just built an AI pipeline entirely in SQL. No Python. No REST calls.
Just pure Snowflake orchestration.

Keep It Deterministic (Mostly)

Set your model temperature low (0–0.3) for consistent outputs — unless you want your AI improvising like Eddie Van Halen mid-solo.

SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'gpt-4o-mini',
  'Summarize financial performance.',
  OBJECT_CONSTRUCT('temperature', 0.2)
);

Higher temperature → more creative
Lower temperature → more predictable

Choose wisely depending on whether you’re running an analysis or a jam session.

Governance and Logging

Every AI call needs to be logged. Period.

Snowflake’s Access History + Query History give you built-in traceability, but you can also add your own logging table:

CREATE OR REPLACE TABLE ai_log (
  run_id STRING,
  model_name STRING,
  prompt STRING,
  response STRING,
  timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

Then wrap your AI query in a stored procedure that writes both the input and output — so you can always retrace what the model was told.

But, also because, someday, compliance will ask. And you’ll look like a genius for being prepared.

Closing Verse

Feeding structured data to AI isn’t just a performance boost — it’s how you tame it.

When you control the context, limit the data, and chain your prompts intentionally, you stop getting random “creative” nonsense and start getting repeatable intelligence.

Your warehouse becomes more than a dull, old data store — it becomes an intelligence engine.

So the next time you hit Run, and everything just works, turn up some Zeppelin and whisper:

🎵 “And she’s buying an AI stairway to data heaven…” 🎵