The Missing AI Layer in Your Iceberg Lakehouse

AI Functions in dynamic Iceberg tables: pay the model only for changed rows

On Monday, someone improves the summary prompt. It’s a good change: the output is shorter, with no chatty preamble.

On Tuesday, the backfill starts. It re-summarizes every ticket since spring, at full token price.

On Wednesday, it’s still running. Last quarter’s report no longer matches the dashboard. The backfill reworded some old summaries, even at temperature 0.

Nobody touched those tickets. The pipeline rewrote them anyway.

On Thursday, two questions arrive from outside the data team:

“Can we improve a prompt without paying for every ticket again?” No. With this pipeline, every backfill pays for your whole history again, and history only grows.

“Can we improve a prompt without changing last quarter’s numbers?” No. Every prompt change reopens a report that you already shipped.

This post builds a pipeline where both answers are yes.

TL;DR: Put AI_CLASSIFY, AI_FILTER and AI_COMPLETE in the SELECT of a Snowflake dynamic Iceberg table. The AI step then becomes a layer of your lakehouse that any Iceberg engine can read, not a job beside it. Snowflake calls the model only for new and changed rows. A frozen region keeps the old outputs when the prompt changes. You'll build a ticket-enrichment pipeline from two CREATE statements, with no scheduler, history table or backfill script.

Customers already use Dynamic Iceberg Tables to turn changing source data into fresh, business-ready datasets in open formats, like current-state network inventories, clinical data products, and regional delivery feeds, without building custom merge pipelines. With externally managed dynamic Iceberg tables, now in Private Preview, Snowflake runs those same declarative, target-lag refreshes and publishes the results straight into your existing external Iceberg catalog, so you keep your catalog and still get Snowflake-managed pipelines

You’ll need:

• An account where Snowflake storage for Iceberg is available (I used AWS), or an external volume of your own
• A role that can create a database and call AI Functions (by default, every role can call them, through PUBLIC)
• An XS warehouse (in this post, its name is transform_wh)
• Python with pyiceberg and pyarrow, for Step 6

How to read this post: 6 short steps come first, each with tested SQL. The known problems and the two answers follow. For safe prompt changes, go to Step 4.

From a scheduled job to a lakehouse layer

Usually, an Airflow job or a dbt job runs each hour. Each run has these steps:

1. It finds the new rows.
2. It calls the model.
3. It upserts the results into a history table, so it never pays twice.
4. It retries the NULL results.
5. It checks the load.

When the prompt changes, someone writes a backfill. The job also sits outside the lakehouse. Its logic lives in a scheduler: which rows are new, which rows to retry and when to backfill.

In this post, an Iceberg table holds the raw tickets. One dynamic Iceberg table runs the AI Functions in its SELECT. A second one filters the safety cases.

Snowflake finds the rows that changed and refreshes the tables to meet a TARGET_LAG. A TARGET_LAG is a freshness goal, not a schedule (best practices). Both outputs are standard Iceberg tables, so the AI step becomes one more layer of the lakehouse.

Step 1: Load the tickets into an Iceberg table

EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED' keeps the files in Snowflake storage, so you need no bucket and no IAM role. CHANGE_TRACKING = TRUE lets dynamic tables read the table incrementally.

CREATE DATABASE demo_db;
CREATE SCHEMA demo_db.demo;
USE SCHEMA demo_db.demo;

CREATE ICEBERG TABLE support_tickets (
  ticket_id   INT,
  created_at  TIMESTAMP_NTZ(6),
  channel     STRING,
  body        STRING
)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED'
  CHANGE_TRACKING = TRUE;

INSERT INTO support_tickets VALUES
  (1,  '2026-09-15 08:12:00', 'email',
    'I was charged twice for order 4471. '
    || 'Please refund the duplicate charge.'),
  (12, '2026-10-03 14:45:00', 'chat',
    'My order is three days late and tracking has not updated.'),
  (18, '2026-10-11 13:00:00', 'phone',
    'The charger gets very hot and smells like burning plastic.'),
  -- ...16 more synthetic tickets, mid-September to mid-October
  (20, '2026-10-14 20:50:00', 'chat',
    'I love the new app update. The order history page is much faster.');

I loaded 20 synthetic tickets. Half of them are from before October 1, and this split is important in Step 4.

Step 2: Put the model in the SELECT

The statement below is the full enrichment job. It calls three AI Functions:

• AI_CLASSIFY chooses a topic.
• AI_FILTER answers a yes-or-no question about safety.
• AI_COMPLETE writes the summary.

CREATE DYNAMIC ICEBERG TABLE ticket_enriched
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED'
  REFRESH_MODE = INCREMENTAL
AS
SELECT
  ticket_id,
  created_at,
  channel,
  body,
  AI_CLASSIFY(body, ['billing', 'delivery', 'account access',
                     'product defect', 'outage', 'other']
  ):labels[0]::STRING AS topic,
  AI_FILTER(PROMPT(
    'Does this support ticket describe a safety hazard to a person? {0}',
    body
  )) AS is_safety_issue,
  AI_COMPLETE(
    model => 'llama3.1-8b',
    prompt => CONCAT(
      'Summarize this support ticket in one sentence: ', body),
    model_parameters => {'temperature': 0, 'max_tokens': 60}
  )::STRING AS summary
FROM support_tickets;

Two details are important:

• Keep the AI Functions in the SELECT. An incremental refresh supports them only there (supported queries). With REFRESH_MODE = INCREMENTAL, any other place makes the CREATE statement fail. Without that mode, Snowflake uses a full refresh without a warning. Step 5 shows why the error is better.
• Cast the AI_COMPLETE output to STRING if needed. The function returned VARIANT. Iceberg v3 tables support this type, but the Iceberg v2 spec does not.

What you should see: SHOW DYNAMIC TABLES shows refresh_mode as INCREMENTAL. The first refresh enriched all 20 tickets in about 5 seconds and flagged exactly the three hazards. For a question about an invoice fee, it wrote the summary "There is no text to summarize." Outputs like this are the reason for the prompt change in Step 4.

Step 3: Pay only for what changed

The SQL below adds five tickets and refreshes the table. Then it reads the refresh history:

INSERT INTO support_tickets VALUES
  (21, '2026-10-15 08:05:00', 'email',
    'I returned the vacuum two weeks ago and still have no refund.'),
  -- ...three more
  (25, '2026-10-16 14:00:00', 'chat',
    'Can you combine my two orders into one shipment?');

ALTER DYNAMIC TABLE ticket_enriched REFRESH;  -- or wait for TARGET_LAG

SELECT refresh_action,
       statistics:numInsertedRows::INT AS inserted,
       statistics:numDeletedRows::INT  AS deleted
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
       NAME => 'DEMO_DB.DEMO.TICKET_ENRICHED'))
ORDER BY refresh_start_time DESC
LIMIT 1;

What you should see: an INCREMENTAL refresh that inserted 5 rows and deleted 0 rows. The query profile agrees: the model read 5 tickets, not 25. The first 20 rows kept exactly the same outputs.

Updates work the same way. I changed ticket 1 from “charged twice” to “charged three times.” Iceberg wrote a new version of the data file that holds ticket 1 and 19 other tickets, so 40 change records arrived. Snowflake combines these records into one net change for each row before the SELECT runs. 38 records cancel each other, and only the old and new versions of ticket 1 reach the AI Functions.

Step 4: Change the prompt and keep the old outputs

This is the Monday prompt change from the start of this post. First, freeze all rows from before October 1:

ALTER DYNAMIC TABLE ticket_enriched
  SET FROZEN WHERE (created_at < '2026-10-01');

SELECT COUNT_IF(METADATA$IS_FROZEN)     AS frozen_rows,
       COUNT_IF(NOT METADATA$IS_FROZEN) AS active_rows
FROM ticket_enriched;

My table now had 10 frozen rows and 16 active rows. The SET FROZEN statement did not start a refresh.

Next, change the prompt with CREATE OR ALTER. In the same statement, state the frozen region again:

CREATE OR ALTER DYNAMIC ICEBERG TABLE ticket_enriched
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED'
  REFRESH_MODE = INCREMENTAL
  FROZEN WHERE (created_at < '2026-10-01')  -- keep this line every time
AS
SELECT
  ticket_id,
  created_at,
  channel,
  body,
  AI_CLASSIFY(body, ['billing', 'delivery', 'account access',
                     'product defect', 'outage', 'other']
  ):labels[0]::STRING AS topic,
  AI_FILTER(PROMPT(
    'Does this support ticket describe a safety hazard to a person? {0}',
    body
  )) AS is_safety_issue,
  AI_COMPLETE(
    model => 'llama3.1-8b',
    prompt => CONCAT(
      'In 12 words or fewer, say what the customer needs: ', body),
    model_parameters => {'temperature': 0, 'max_tokens': 60}
  )::STRING AS summary
FROM support_tickets;

What you should see: the next refresh is a REINITIALIZE. A REINITIALIZE recomputes only the active region (frozen regions). In my run, 15 of the 16 active summaries changed, but 0 of the 10 frozen summaries changed. For example, ticket 12 changed:

• Before: “The customer’s order is three days past the expected delivery date and the tracking information has not been updated.”
• After: “I need an update on the status of my delayed order.”

Ticket 2 is frozen, so its summary still starts with “Here is a summary of the support ticket in one sentence:”.

Include FROZEN WHERE in every CREATE OR ALTER. CREATE OR ALTER uses your statement as the complete target state (CREATE DYNAMIC ICEBERG TABLE). I ran the same change without the FROZEN line. The refresh reinitialized with the reason Frozen region changed or removed, and the model summarized all 26 rows again.

That run also showed the problem from the start of this post. With the same prompt, input and temperature 0, the model wrote different summaries for 2 of the 16 active rows. So do not run the model again on rows that are complete.

Step 5: Filter in a downstream table, not in WHERE

The obvious design for a safety queue puts AI_FILTER in the WHERE clause. With REFRESH_MODE = INCREMENTAL, the CREATE statement fails:

091912 (0A000): Query contains the function 'EXTERNAL_FUNC', but change
tracking is not supported on queries with non-deterministic functions.

With REFRESH_MODE = AUTO, the same statement succeeds. But Snowflake chooses FULL and does not tell you (supported queries). A full refresh sends every row to the model again, so each refresh repeats the backfill from the start of this post.

To fix this, filter on the flag that the upstream SELECT already computes:

CREATE DYNAMIC ICEBERG TABLE safety_queue
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED'
  REFRESH_MODE = INCREMENTAL
AS
SELECT ticket_id, created_at, channel, topic, summary
FROM ticket_enriched
WHERE is_safety_issue;

What you should see: the refresh mode is INCREMENTAL. A new ticket about a kettle lid that burned a person's hand added 1 row to each table.

Step 6: Read the tables from outside Snowflake

Both tables are in the Snowflake catalog. Any engine that supports the Iceberg REST protocol can read them through Horizon Catalog. I used PyIceberg with a programmatic access token that I restricted to one role:

import os
from pyiceberg.catalog import load_catalog

uri = "https://<org>-<account>.snowflakecomputing.com/polaris/api/catalog"
props = {
    "type": "rest",
    "uri": uri,
    "oauth2-server-uri": f"{uri}/v1/oauth/tokens",
    "warehouse": "DEMO_DB",                   # database name, upper case
    "credential": os.environ["HORIZON_PAT"],  # never hard-code the token
    "scope": "session:role:<ROLE>",
    "header.X-Iceberg-Access-Delegation": "vended-credentials",
}
catalog = load_catalog("horizon", **props)
enriched = catalog.load_table("DEMO.TICKET_ENRICHED")
rows = enriched.scan(
    selected_fields=("TICKET_ID", "TOPIC", "IS_SAFETY_ISSUE", "SUMMARY")
).to_arrow()
print(rows.num_rows)

What you should see: PyIceberg returns the same 27 rows and summaries as Snowflake. SAFETY_QUEUE holds the five safety tickets.

The role needs USAGE on the database and the schema, and SELECT on the tables. Outside engines can read dynamic tables, but they cannot write to them.

Known problems

• A failed row does not cause an error, and it stays failed. I added a ticket with 672,036 characters, which is much larger than the 128K-token context window of llama3.1-8b. The refresh succeeded, but AI_COMPLETE and AI_FILTER returned NULL. So add a check for NULL outputs, as shown after this list.
• Fix the input to retry a failed row. return_error_details => TRUE shows the cause, here max tokens of 131072 exceeded (error details). A no-op UPDATE ... SET body = body does not retry the row. For an active row, an input fix does retry it.
• CREATE OR REPLACE reinitializes every dynamic table downstream. CREATE OR ALTER does not (best practices).

For the NULL check, a data metric function counts the NULL summaries each hour. The expectation reports a violation when the count is not 0. This needs Enterprise Edition.

ALTER DYNAMIC TABLE ticket_enriched
  SET DATA_METRIC_SCHEDULE = '60 MINUTE';

ALTER DYNAMIC TABLE ticket_enriched
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (summary)
  EXPECTATION no_null_summaries (VALUE = 0);

What you should see: in my test, one NULL summary made SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS return the value 1 and EXPECTATION_VIOLATED as TRUE.

Wrap-up: both answers are yes

“Can we improve a prompt without paying for every ticket again?” Yes. My prompt change re-read the 16 active rows and none of the 10 frozen ones, with no backfill script. Between prompt changes, the model reads only new and edited tickets. So a prompt change costs your active region, not your history.

“Can we improve a prompt without changing last quarter’s numbers?” Yes. 0 of the 10 frozen summaries changed. In the active region, 15 of the 16 summaries changed.

These numbers come from one demo run with 27 synthetic tickets. Yours will differ. The shape won’t.

Pick one scheduled enrichment job, ideally the one with a history table and a backfill script. Then do three things:

1. Move its SELECT into a dynamic Iceberg table with REFRESH_MODE = INCREMENTAL.
2. Freeze the rows that are complete.
3. Add the NULL check.

If you have no such job yet, the same layer fits anywhere a current-state Iceberg table sits next to free text:

• Network operations: classify technician notes by fault type (AI_CLASSIFY).
• Clinical data products: summarize site-monitoring notes in one line (AI_COMPLETE).
• Perception engineering: flag annotator comments that report a labeling problem (AI_FILTER).
• Enterprise reporting: classify service and invoice descriptions into your report categories (AI_CLASSIFY).

Next Monday, improve the prompt. On Tuesday, the refresh reads your active rows, not your history. On Wednesday, last quarter’s report still matches the dashboard. On Thursday, both answers are yes. On Friday, another team reads the same table from its own engine, with the same grants and no export.

A backfill’s bill grows with your history. This table’s bill grows with your changes.

The code in this post is sample code, provided as-is without official support from Snowflake.

Big thanks to PD Dutta and his Engineering team for their controbutions to this post.

The Missing AI Layer in Your Iceberg Lakehouse was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.