Snowflake data pipelines for AI: a practical guide

The pipeline comes before the model

Most AI projects on Snowflake do not fail because of the model. They fail because the data under it is late, duplicated or different from what people think it is.

Snowflake now has AI built in. Cortex AI functions let you call a language model from SQL, next to your data. That makes the last step easy, and it makes the earlier steps matter more. A model that reads bad data gives a confident wrong answer.

This guide describes a pipeline in four layers: ingest, transform, check and use.

1. Ingest: get the data in as it is

Load the data the way it arrives, and keep that raw copy. You will need it when a rule changes and you have to rebuild.

Snowflake gives you several ways in:

• Snowpipe loads files as they land in cloud storage.
• Snowpipe Streaming loads rows as they are produced, for events and other data that cannot wait.
• Connector tools such as Fivetran or Airbyte copy data from business applications and databases on a schedule.

Choose by how fresh the data has to be. Most reporting and most AI work is fine with data that is minutes or hours old. Real streaming costs more to run, so use it only where the delay matters.

2. Transform: describe the result with Dynamic Tables

A Dynamic Table is a table defined by a query. You say what the result should be and how fresh it must stay. Snowflake works out when to refresh it and in which order.

CREATE OR REPLACE DYNAMIC TABLE analytics.support_tickets_clean
  TARGET_LAG = '15 minutes'
  WAREHOUSE = transform_wh
AS
SELECT
  ticket_id,
  customer_id,
  created_at,
  TRIM(body) AS body
FROM raw.support_tickets
WHERE body IS NOT NULL;

Snowflake recommends Dynamic Tables for new pipelines that are written in SQL across several tables. You write no schedule and no dependency list.

Use streams and tasks when a Dynamic Table does not fit. A Dynamic Table is read-only and does not support MERGE. So if you need upserts, step-by-step logic or the ability to delete single rows on request, a stream with a task on a normal table is the right tool.

dbt works well on top of either. It keeps the transformation code in a repository, with tests and documentation.

Build in layers: raw, cleaned, ready for use. Each business number is defined once, in one place.

3. Check: test the data, not only the code

A pipeline that runs without errors can still deliver wrong data. Add checks that look at the data itself:

• Freshness. Did new data arrive when it should?
• Completeness. Are required fields filled in?
• Uniqueness. Is each key there only once?
• Volume. Is today’s load about the size you expect?

dbt tests and Snowflake’s data metric functions both do this. What matters is the result: a failed check stops the load or raises an alert before anyone uses the data.

4. Use: add AI on top of clean data

With clean tables in place, the AI step is a query:

SELECT
  ticket_id,
  AI_CLASSIFY(body, ['billing', 'delivery', 'technical', 'other']):labels[0]::string AS topic,
  AI_COMPLETE('claude-sonnet-5', 'Summarise this support ticket in one sentence: ' || body) AS summary
FROM analytics.support_tickets_clean;

The main building blocks:

• Cortex AI functions such as AI_CLASSIFY, AI_SENTIMENT, AI_EXTRACT and AI_COMPLETE work on text row by row.
• Cortex Search builds a search service over your text, for assistants that answer from your own documents.
• Cortex Analyst answers questions in plain language over structured data, using a description of your tables that you define.

The models that are available depend on your Snowflake region. Check the list before you choose one.

Keep the cost under control

AI functions are billed by the amount of text they process. Three habits keep the bill small:

1. Run the model once, in the pipeline. Store the result in a table. Do not call the model again every time someone opens a dashboard.
2. Process only new rows. Yesterday’s tickets do not need a second summary.
3. Use the smallest model that passes your test. Classification rarely needs the largest one.

Keep control of the data

The AI functions run inside Snowflake, under the same roles and masking policies as the rest of your data. That is the main reason to do the work there and not copy data to another service.

Check two things before you send personal data to a model: which region the model runs in, and who can read the output table. A summary of a private message is still private.

Common mistakes

• AI on raw data. The model repeats every duplicate and every test record.
• No test set. Nobody knows whether a new prompt or a new model made the answers better.
• The model in the dashboard query. It is slow, and you pay for the same answer many times.
• No owner. When a check fails at night, somebody has to be the person who gets the alert.

Where to start

Pick one question the business asks every week. Build the pipeline for the data behind that question only, with checks. Then add one AI step and measure it on real cases.

Our use case for a data platform defined in code shows this kind of project from start to finish. The Data Engineering & Operations page describes how we work.