Dynamic Table Incremental Refresh FAQ

Refresh failure due to excessive incremental data

The volume of data processed during an incremental refresh can fluctuate. A sudden spike in this volume can cause the refresh to fail. Common reasons for data volume to increase include:

• Overwriting one or more source tables can generate a large volume of data changes. If this is the case, consider setting the following GUC at the database level. This ensures that when a source table is overwritten, the associated Dynamic Table is also automatically overwritten.

ALTER DATABASE xxx SET hg_experimental_enable_dynamic_table_consume_change_when_base_overwrite = off;

• The Dynamic Table refresh was paused or remained inactive for a period, leading to an accumulation of incremental data.

• A business workload causes a short-term surge in data ingestion or changes on the source table.

Common error messages for this scenario include:

• Query OOM: ERRCODE_OUT_OF_MEMORY: Total memory used by all existing queries exceeded memory limitation.

• Worker OOM: SERVER_INTERNAL_ERROR message: "ERPC_ERROR_CONNECTION_CLOSED, reason: Ping timeout".

• BinaryArray Exceed 2G: internal error: Capacity error: BinaryArray cannot contain more than 2147483646 bytes.

In this case, use REFRESH OVERWRITE to perform a manual overwrite refresh. After the command completes, verify that subsequent incremental refreshes run normally.

REFRESH OVERWRITE TABLE dynamic_table_xxx WITH (refresh_mode = 'incremental', refresh_guc_hg_computing_resource = 'serverless');

Error: Multiple partitions / No partition is selected

Example error messages: Multiple partitions of table XXX is selected or No partition of table XXX is selected.

Cause: The source table is a physical partition table, but the Dynamic Table definition lacks a partition filter, causing the query to select multiple physical sub-partitions.

Solution:

• Change the source table to use a logical partition. The Dynamic Table can then automatically select the correct partition.

• If the source table is a physical partition table and the Dynamic Table is also physically partitioned, the Dynamic Table definition must include a partition filter that ensures a one-to-one mapping between partitions.

Incremental refresh error: Cannot find index full id: XXX

Cause: You can rename a source table for an incremental Dynamic Table, and the incremental refresh continues to work correctly. However, if the renamed source table is later dropped, the incremental refresh fails. This is because the process can no longer find the table by using its unique internal ID and therefore cannot consume data.

Solution: Confirm that all source tables listed in the error message exist. Then, run a REFRESH OVERWRITE.

REFRESH OVERWRITE TABLE dynamic_table_xxx WITH (refresh_mode = 'incremental');

Incremental refresh error: GetLatestStreamOffset snapshots is empty or stream XXX is not existed

Cause: If a Dynamic Table is not refreshed for an extended period after a successful refresh, the backend automatically clears the stored source table offset.

Solution: Run REFRESH OVERWRITE once.

REFRESH OVERWRITE TABLE dynamic_table_xxx WITH (refresh_mode = 'incremental');