@Rocky - hoping for some guidance on a design problem we’ve hit with silver→gold promotion using

SUBMIT TASK

. We’re streaming data into a bronze table via Stream Load (transactional: begin → load → commit), then using silver views + scheduled tasks to merge into gold Primary Key tables. The core challenge is building a reliable watermark so the task knows which rows to process on each run. Attempt 1: We watermarked on

ingested_at

(set at load time) with a safety window. This lost data because concurrent Stream Loads can commit out of order — a slow load stages rows with an earlier timestamp, a fast load commits first, the task advances the watermark past the slow load’s rows before they become visible. Attempt 2: We introduced a batch-tracking table with

AUTO_INCREMENT

IDs and a pending/committed status. The task only reads up to

MIN(pending_batch_id) - 1

, so it never advances past in-flight loads. This works perfectly on a single node, but in production (shared-data mode, 2 CN nodes) the AUTO_INCREMENT pre-allocates ID chunks per node, so batch IDs aren’t monotonic. Once the task’s watermark (

MAX(batch_id)

in the gold table) jumps to a high chunk, lower-numbered batches from the other CN are permanently skipped. The fundamental constraint seems to be that

SUBMIT TASK

is a single statement and can’t maintain external state (e.g. mark batches as processed, write to a separate cursor table). Are we missing a pattern here? Is there a recommended approach for exactly-once promotion from a continuously-ingested table to gold tables when Stream Loads arrive concurrently across multiple CNs? Or is an external orchestrator the expected answer at this point?

The challenges you are facing with

SUBMIT TASK

are common when trying to manually implement Change Data Capture (CDC) or "watermarking" logic inside a distributed database. In StarRocks,

SUBMIT TASK

is essentially a wrapper for an

INSERT INTO ... SELECT

or

CTAS

statement and lacks the state-management features (like cursors or sequence-tracking) found in external orchestrators. Here is the guidance on the patterns you've tried and the recommended StarRocks-native approach. 1. Why your previous attempts failed * Attempt 1 (Timestamp + Safety Window): This is the "Time-of-Check to Time-of-Use" (TOCTOU) problem. Since Stream Load commits are atomic but the

ingested_at

timestamp is generated before the commit, a long-running load can "appear" in the past after your task has already moved the watermark forward. * Attempt 2 (

AUTO_INCREMENT

): As you discovered,

AUTO_INCREMENT

in StarRocks (especially in shared-data or multi-CN environments) is designed for uniqueness and performance, not strict monotonicity. It pre-allocates chunks (default 100,000) to each BE/CN to avoid a central bottleneck. This makes it unsuitable for watermark logic. 2. Recommended Pattern: Asynchronous Materialized Views (MVs) The most reliable "StarRocks-native" way to promote data from Silver (views) to Gold (tables) is to use Asynchronous Materialized Views instead of manual tasks. Starting with v3.3, StarRocks introduced Incremental Materialized Views (

"refresh_mode" = "INCREMENTAL"

). This mechanism is designed exactly for your problem: * Version-based Tracking: Unlike your manual timestamp, the MV tracks the internal transaction ID (version) of the base tables. It knows exactly which rows have been committed since the last refresh, regardless of the order in which those transactions started. * View Support: Since v3.1, you can build Asynchronous MVs on top of existing logical Views (your Silver layer). * Exactly-Once: Because it relies on the internal storage engine's versioning, it guarantees that no committed data is skipped or double-processed during the promotion to the Gold layer. Example Design:

sql
CREATE MATERIALIZED VIEW gold_table_mv
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
PROPERTIES (
    "refresh_mode" = "INCREMENTAL"
)
AS 
SELECT * FROM silver_view; -- Your silver view logic

3. Alternative: The "Lookback Window" with PK Tables If you must use a native Primary Key table for your Gold layer (e.g., if you need specific PK features that MVs don't support or if your Silver-to-Gold transformation is too complex for an incremental MV), the standard pattern is a Lookback Window + Upsert. Because your Gold table is a Primary Key table, it natively handles

UPSERT

semantics. You can solve the "invisible load" problem by processing a overlapping window of time. The Logic: Instead of trying to find a perfect "high-watermark," process everything from the last N minutes until now.

sql
SUBMIT TASK gold_promotion AS
INSERT INTO gold_table
SELECT * FROM silver_view
WHERE ingested_at > now() - INTERVAL 30 MINUTE; 
-- Assuming tasks run every 10 mins and max transaction time is < 20 mins

* Reliability: The 30-minute window ensures that any "slow load" that started before your last run but committed after it will be caught in the next run. * Deduplication: Since the Gold table is a Primary Key table, any rows already processed in the previous run will simply be overwritten (upserted) with the same data, resulting in no duplicates. 4. Is an external orchestrator the answer? If your logic requires multi-statement transactions (e.g., "Insert data to Gold AND update a cursor table in one transaction"), an external orchestrator like Airflow or dbt is required. StarRocks

SUBMIT TASK

currently only supports a single ETL statement and does not support stored procedures or multi-statement blocks. Summary

@Rocky Can an async MV with

refresh_mode = 'INCREMENTAL'

support deduplication — e.g., if the same business key appears multiple times in the base table (record updates), can the MV maintain only the latest version? Or would we need window functions / QUALIFY in the MV definition, and does that break incremental refresh?

In StarRocks, the

refresh_mode = 'INCREMENTAL'

setting (introduced in v3.3) has specific constraints that make it unsuitable for the deduplication pattern you're describing. 1. Does it support

QUALIFY

or Window Functions? No. The current implementation of

refresh_mode = 'INCREMENTAL'

(which uses the transaction log for high-performance updates) supports only a limited subset of SQL operators: * Supported:

SELECT

,

FILTER

(WHERE),

INNER JOIN

,

UNION ALL

, and

AGGREGATE

(using

GROUP BY

). * Not Supported: Window functions (e.g.,

ROW_NUMBER()

,

RANK()

), the

QUALIFY

clause, and

OUTER JOINS

are currently not supported for incremental refresh. If you include

QUALIFY

or window functions in an MV with

refresh_mode = 'INCREMENTAL'

, the creation or the refresh will fail. 2. The "Append-Only" Constraint A critical limitation of

refresh_mode = 'INCREMENTAL'

is that it only supports append-only operations on the base tables. * If your Silver layer is a logical view over a Bronze table that is append-only, this is fine. * However, if you ever perform an

UPDATE

or

DELETE

on the base table, the incremental refresh will fail. 3. How to achieve deduplication with Async MVs If you need to maintain "only the latest version" (deduplication) while moving data to the Gold layer, you have two primary options within the StarRocks ecosystem: Option A: Partitioned Async MV (The "PCT" Mode) Instead of the strict

INCREMENTAL

mode, use the default asynchronous refresh mode (often called Partition Refresh or PCT). * Deduplication: You can use

QUALIFY ROW_NUMBER() OVER (PARTITION BY pk ORDER BY version DESC) = 1

. * Efficiency: If you partition both the base table and the MV (e.g., by

event_date

), StarRocks will only refresh the specific partitions that have received new data. It is not as granular as the transaction-log-based incremental mode, but it is the standard way to handle complex logic like deduplication. * Watermark: StarRocks handles the watermark for you by tracking which partitions are "stale" based on the base table's version. Option B: Aggregate with

MAX()

If your deduplication logic is simple enough to be expressed as an aggregation, you can use

INCREMENTAL

mode:

sql
CREATE MATERIALIZED VIEW gold_mv
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
PROPERTIES ("refresh_mode" = "INCREMENTAL")
AS
SELECT 
    user_id, 
    MAX(ingested_at) as latest_ingest,
    MAX(user_name) as user_name -- This only works if columns don't change independently
FROM silver_view
GROUP BY user_id;

Warning: This only works if you are comfortable with aggregate functions for every column. It does not provide the "full row" of the latest record like a window function does. Summary If you need exactly-once deduplication for a Gold layer: 1. Do not use

refresh_mode = 'INCREMENTAL'

with

QUALIFY

. 2. Use a Partitioned Async MV with the default refresh mode. This allows

QUALIFY

and handles all the complex watermarking and "invisible row" issues for you. 3. Alternatively, if your Gold layer must be a table (not an MV), use a Primary Key table as the destination and a Lookback Window (e.g.,

WHERE ingested_at > now() - interval 1 hour

) in your

SUBMIT TASK

. The PK table's native upsert logic will handle the deduplication of the overlapping window automatically. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-functions/Window_function.md References