@Rocky Does StarRocks supports array unnest inside async MVs with partition-mapped incremental refresh without silently falling back to a full table scan? My use case is as a DSP, each record of bid request can have multiple inventory package ids associated with it. THey are stored as an array. Now, I want to count impression opportunities per inventory package IDs. This means unnesting the array and performing group by per unnested inventory package ID. Obviously this is not feasible in realtime since it causes row explosion. i have ~5.5 billion rows per 24 hours. What solutions do I have? I am thinking of async MVs creating hourly rollups

StarRocks does support

unnest

(or

LATERAL VIEW explode

) inside asynchronous Materialized Views (MVs) while maintaining partition-level incremental refresh. This setup is commonly used in DSP/AdTech scenarios for handling row explosions (e.g., bid requests to inventory package IDs) at scale. Here is how the behavior works and how to avoid the "silent fallback" to a full table scan: 1. Partitioned Refresh vs. Full Scan To avoid a full table scan during refresh, you must define the MV with a

PARTITION BY

clause that aligns with the base table's partition column (typically a time-based column like

hour

or

day

). * How it works: When data is loaded into a specific partition of your base table (e.g.,

hour=2023-10-01 10:00

), StarRocks identifies this change and triggers a refresh only for the corresponding partition in the MV. * Role of `unnest`: The

unnest

operation occurs during the calculation of that specific partition. Since all unnested rows derived from a base row still belong to the same time partition, the "partition mapping" remains intact. StarRocks will execute an

INSERT OVERWRITE

on just the affected MV partition. 2. Avoiding "Silent Fallback" A "silent fallback" to a full table scan (full refresh) usually occurs if StarRocks cannot determine the mapping between base partitions and MV partitions. To ensure partition-level refresh: * Align Partition Keys: The MV's

PARTITION BY

column must be derived directly from the base table's partitioning column (e.g.,

PARTITION BY bid_hour

). * Internal Tables: If your base table is an internal StarRocks table (Duplicate/Primary Key model), partition tracking is automatic and highly reliable. * External Tables (Hive/Iceberg): Since StarRocks 3.1+, partition-level refresh for external catalogs is supported, but you should ensure the catalog supports partition change tracking (e.g., Iceberg's snapshot mechanism). * Verification: You can verify that StarRocks is performing a partition-level refresh by checking the

information_schema.task_runs

view. The

EXTRA_MESSAGE

column will list the specific

mvPartitionsToRefresh

. 3. Solution for Your DSP Use Case For 5.5 billion rows per day, an hourly rollup is a standard and recommended architecture. Example Implementation:

sql
CREATE MATERIALIZED VIEW mv_inventory_hourly
PARTITION BY bid_hour
REFRESH ASYNC
AS
SELECT 
    date_trunc('hour', bid_time) as bid_hour,
    inventory_pkg_id,
    count(*) as impression_ops
FROM bid_requests, unnest(inventory_package_ids) as t(inventory_pkg_id)
GROUP BY bid_hour, inventory_pkg_id;

4. Key Limitations to Note * Refresh Mode: In StarRocks 3.3+, there is a

refresh_mode = 'INCREMENTAL'

property which uses Incremental View Maintenance (IVM). Do not confuse this with Partitioned Refresh. IVM is for row-level deltas and has stricter operator limitations. For your scale, use the standard Partitioned Refresh (the default

ASYNC

mode with

PARTITION BY

). * Query Rewrite: While the refresh is incremental by partition, Query Rewrite (automatically using the MV when you query the base table) may have limitations with

unnest

in older versions. If transparent rewrite doesn't work, you can still query the MV directly to get your hourly rollups. * Memory Management: Because

unnest

causes row explosion, ensure your

partition_refresh_number

is set reasonably (default is often 1) to avoid OOM by refreshing too many partitions concurrently. Summary StarRocks will not silently fall back to a full scan if you define the MV with a

PARTITION BY

clause that matches your base table's time partitioning. The row explosion from

unnest

is handled locally within each partition's refresh task, making hourly rollups a very efficient solution for your use case. References • sql-reference/sql-functions/array-functions/unnest.md