Refresh dynamic table

Starting from V3.0, Hologres supports manual or automatic data refresh for dynamic tables through the REFRESH DYNAMIC TABLE statement.

Use REFRESH DYNAMIC TABLE to update a dynamic table with the latest data on demand, without waiting for the next scheduled automatic refresh. This is useful for one-time updates or when the table has a long refresh interval and you need the data immediately.

Hologres supports two refresh modes:

Manual refresh: Run REFRESH DYNAMIC TABLE once.
Automatic refresh: Configured via a refresh policy in the CREATE DYNAMIC TABLE statement. Runs on a schedule.

Syntax

-- Supported in all versions
REFRESH DYNAMIC TABLE [<schema_name>.]<table_name>;

-- Supported from V3.1
REFRESH [OVERWRITE] DYNAMIC TABLE [<schema_name>.]<table_name>
[PARTITION (<partition_key> = '<partition_value>')]
[WITH (
  refresh_mode = '{full|incremental}'
)];

Parameters

Parameter	Description
`schema_name`	The name of the schema that contains the table.
`table_name`	The name of the non-partitioned table or partitioned child table to refresh.
`OVERWRITE`	Clears the current state before running the refresh. For incremental refresh mode, this re-runs incremental refresh from scratch without preserving the previous state. Supported from V3.1.
`PARTITION (<partition_key> = '<partition_value>')`	Specifies the partition to refresh. `partition_key` is the partition key; `partition_value` is the target partition value. Required for partitioned tables. Only one partition can be refreshed per statement.
`refresh_mode = '{full\|incremental}'`	Overrides the refresh mode for this operation. Supported from V3.1.
`computing_resource = '{serverless\|local\|<warehouse_name>}'`	Specifies the compute resource for the refresh. Default: `serverless`. See Set computing resources for dynamic table refresh for details.

`computing_resource` values:

Value	Description
`serverless`	Uses serverless resources. Default.
`local`	Uses instance resources.
`<warehouse_name>`	Uses the specified virtual warehouse. Supported from V4.0.7. For manual refreshes, defaults to the currently connected virtual warehouse if not specified. For automatic refreshes, uses the virtual warehouse connected when the dynamic table was created.

Refresh behavior by statement

When a dynamic table has an established refresh mode (incremental or full), the effect of each manual refresh statement depends on the table's current mode.

Mismatched combinations — such as requesting incremental refresh on a full-refresh table, or full refresh on an incremental-refresh table — return an error because the two modes handle data changes in fundamentally incompatible ways.

Statement	Table in incremental refresh mode	Table in full refresh mode
`REFRESH`	Runs incremental refresh.	Runs full refresh.
`REFRESH OVERWRITE`	Re-runs incremental refresh without preserving the previous state.	Runs full refresh.
`REFRESH WITH (refresh_mode = 'full')`	Returns an error.	Runs full refresh.
`REFRESH WITH (refresh_mode = 'incremental')`	Runs incremental refresh.	Returns an error.
`REFRESH OVERWRITE WITH (refresh_mode = 'incremental')`	Re-runs incremental refresh without preserving the previous state.	Returns an error.
`REFRESH OVERWRITE WITH (refresh_mode = 'full')`	Runs full refresh and stops incremental refresh.	Runs full refresh.

Limitations

Regular partitioned tables

Only the basic REFRESH DYNAMIC TABLE syntax is supported.
Refreshing the parent table is not supported. Refresh each child table individually.

Logical partitioned tables

If refresh_mode is specified in a manual refresh, the system changes that partition's refresh mode to the specified mode. Automatic refresh no longer runs for that partition afterward.
After OVERWRITE runs, the partition state is cleared and the specified partition is fully refreshed.

General

For partitioned tables, you must specify the partition. Only one partition can be refreshed per statement.