@Rocky - Does starrocks support only dirty partition refresh for MVs built directly on iceberg tables?

For Materialized Views (MVs) built directly on Iceberg tables, StarRocks supports partition-level incremental refresh (often referred to as refreshing "dirty partitions"). Here are the key details: * Partition-Level Detection: Starting from v3.1.4, StarRocks can detect data changes in Iceberg tables at the partition level by analyzing Iceberg snapshot metadata. * Dirty Partition Refresh: When a refresh is triggered (manually or by schedule), StarRocks identifies which partitions in the base Iceberg table have changed ("dirty" partitions) and refreshes only the corresponding partitions in the MV. This avoids a resource-heavy full refresh. * Requirements: * The MV must be a partitioned materialized view. * The partition keys of the MV must be aligned with (included in) the partition keys of the base Iceberg table. * Since v3.2.3, this also supports Iceberg Partition Transforms (like

identity

,

year

,

month

,

day

, or

hour

). * Not "Only" Dirty Refresh: While partition-level refresh is the efficient "incremental" method for Iceberg, it is not the only support. StarRocks also supports: * Full Refresh: If the MV is not partitioned or if you manually trigger a refresh with the

FORCE

keyword, StarRocks will perform a full-size refresh. * Refresh Modes: You can set the

refresh_mode

property to

INCREMENTAL

to ensure it attempts partition-level updates. In summary, for MVs on Iceberg, "incremental" refresh is specifically partition-based. StarRocks does not currently support row-level incremental updates (like CDC/Binlog) for external Iceberg tables; if a single row changes in a partition, the entire "dirty" partition is recalculated. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md

@Rocky Give me an example of CREATE MATERIALIZED VIEW which support incremental partition refresh and can also only dirty partitions

To enable incremental refresh that targets only the "dirty" (changed) partitions of an Iceberg table, you must ensure the Materialized View's partition key is aligned with the base table. Here is an example of creating such an MV: 1. Base Iceberg Table (Example) Assume you have an Iceberg table partitioned by a day-level transform:

sql
-- Existing Iceberg Table in a catalog named 'iceberg_catalog'
CREATE TABLE iceberg_catalog.db.orders (
    order_id BIGINT,
    customer_id INT,
    order_date TIMESTAMP,
    amount DECIMAL(10, 2)
) 
PARTITIONED BY (days(order_date));

2. Materialized View with Incremental Refresh To refresh only changed partitions, use

PARTITION BY

with the matching partition expression and set the

refresh_mode

to

INCREMENTAL

.

sql
CREATE MATERIALIZED VIEW mv_iceberg_daily
-- 1. Align the partition with the base Iceberg table
PARTITION BY date_trunc('day', order_date)
-- 2. Define the refresh strategy (can be ASYNC or MANUAL)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    -- 3. Only refresh partitions that have changed (dirty)
    "refresh_mode" = "INCREMENTAL",
    -- Optional: Limit the number of dirty partitions refreshed in one batch
    "partition_refresh_number" = "10" 
)
AS 
SELECT 
    order_date,
    customer_id,
    SUM(amount) as daily_revenue
FROM iceberg_catalog.db.orders
GROUP BY order_date, customer_id;

Key Requirements for "Dirty" Refresh to Work: * Partition Alignment: The MV's

PARTITION BY

column must be included in the base table's partition columns. Since Iceberg uses transforms, you must map them correctly (e.g., Iceberg

days(col)

maps to StarRocks

date_trunc('day', col)

). *

refresh_mode

: Setting this to

INCREMENTAL

tells StarRocks to only recalculate partitions where the underlying Iceberg snapshot metadata indicates a change. * Metadata Detection: StarRocks (v3.1.4+) automatically tracks Iceberg snapshot IDs. When a refresh occurs, it identifies which partitions have new data and refreshes only those. How to trigger manually for only dirty partitions: If you want to manually trigger the refresh of just the dirty partitions:

sql
REFRESH MATERIALIZED VIEW mv_iceberg_daily;

(StarRocks will check the metadata and only process the changed partitions.) References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md ized_view) ) view) References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md