Skip to main content

Production Text Analytics Pipelines

A production pipeline must do more than call an AI function. It must process new records predictably, retain lineage, handle failures, and avoid paying to process the same text repeatedly.

Use four layers:

  1. Raw input: Preserve the original ticket, review, transcript, or document text.
  2. Preparation: Remove empty records, normalize text, redact sensitive values, and calculate a content hash.
  3. AI enrichment: Apply sentiment, classification, summarization, or extraction in controlled batches.
  4. Curated output: Store the result with model, prompt, timestamp, status, and error metadata.

Snowflake AI Functions do not support Dynamic Tables. Use scheduled Tasks, Streams, MERGE, or an external orchestrator for repeatable processing.

2. Create an auditable target​

CREATE OR REPLACE TABLE TICKET_AI_ENRICHMENT (
TICKET_ID NUMBER,
CONTENT_HASH STRING,
SENTIMENT_SCORE FLOAT,
SUMMARY STRING,
MODEL_VERSION STRING,
PROCESSED_AT TIMESTAMP_LTZ,
PROCESSING_STATUS STRING,
PRIMARY KEY (TICKET_ID, CONTENT_HASH)
);

The content hash prevents unchanged records from being processed again when a task is rerun.

3. Process only new or changed text​

MERGE INTO TICKET_AI_ENRICHMENT target
USING (
SELECT
TICKET_ID,
SHA2(TICKET_TEXT) AS CONTENT_HASH,
AI_SENTIMENT(TICKET_TEXT) AS SENTIMENT_SCORE,
SNOWFLAKE.CORTEX.SUMMARIZE(TICKET_TEXT) AS SUMMARY,
'task-functions-v1' AS MODEL_VERSION
FROM CUSTOMER_TICKETS
WHERE TICKET_TEXT IS NOT NULL
AND LENGTH(TICKET_TEXT) > 0
) source
ON target.TICKET_ID = source.TICKET_ID
AND target.CONTENT_HASH = source.CONTENT_HASH
WHEN NOT MATCHED THEN INSERT (
TICKET_ID,
CONTENT_HASH,
SENTIMENT_SCORE,
SUMMARY,
MODEL_VERSION,
PROCESSED_AT,
PROCESSING_STATUS
) VALUES (
source.TICKET_ID,
source.CONTENT_HASH,
source.SENTIMENT_SCORE,
source.SUMMARY,
source.MODEL_VERSION,
CURRENT_TIMESTAMP(),
'SUCCEEDED'
);

For large volumes, divide work into bounded batches and test throughput in the target region. A larger warehouse does not automatically make the managed AI function execute faster.

4. Validate outputs before publishing​

Add deterministic checks before results reach dashboards or downstream applications:

  • required fields are not null;
  • sentiment stays within the documented range;
  • summaries do not exceed the expected length;
  • structured responses pass schema validation;
  • low-confidence or policy-sensitive results enter a review queue;
  • failed rows remain available for retry rather than disappearing.

5. Operate the pipeline​

Track these production metrics:

MetricWhy it matters
Rows attempted and succeededDetect incomplete batches
Error and retry rateIdentify data or service problems
Input and output tokensControl AI Credit consumption
Processing latencyProtect downstream SLAs
Human correction rateMeasure business quality
Duplicate-processing rateDetect avoidable spend

Design exercise​

Design a nightly support-ticket pipeline that:

  1. processes only new or changed tickets;
  2. summarizes and scores sentiment;
  3. sends highly negative tickets to a review queue;
  4. records the prompt or function version;
  5. can be rerun without duplicate charges.

Official references​