VALUES clause supported with dynamic table incremental refresh

Use cases

Imagine you have a dynamic table that needs to join transactional data with a small, fixed reference list, such as status codes or categories, without creating a physical lookup table.

• Join dynamic tables with static lists of values.
• Maintain incremental refresh (INCREMENTAL, AUTO, and ADAPTIVE modes) without breaking the data flow.

How to use

You can declare the VALUES clause directly within the FROM clause of your dynamic table query.

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
TARGET_LAG = '1 hour'
WAREHOUSE = my_warehouse
AS
SELECT t.id, v.column2
FROM my_base_table AS t
INNER JOIN (VALUES (1, 'active'), (2, 'inactive')) AS v (id, status_desc)
  ON t.status_id = v.id;

What happens in this code:

1. The dynamic table is created with a one-hour target lag.
2. The query performs a JOIN between the base table and a static list generated via VALUES.
3. Snowflake processes the VALUES clause while keeping incremental refresh support enabled.

Limitations and requirements

• Generally available (GA) for INCREMENTAL, AUTO, and ADAPTIVE refresh modes.
• The VALUES clause is limited to a maximum of 200,000 rows.
• The DEFAULT keyword is not supported inside a VALUES clause within a FROM clause.