@Rocky Hi, we are building an Airflow-based CDC Raw-to-ODS pipeline on StarRocks

4.0.8

shared-nothing. The Airflow DAG reads incremental CDC events from a partitioned

cdc_raw

table, selects the latest event per primary key using a CDC

version

, and applies UPSERTs and hard DELETEs to ODS Primary Key tables. It also manages checkpoints, delete tombstones, retries, and validation. The ODS tables are used as base tables for asynchronous materialized views. One of these MVs must reflect source changes within one minute. Running the full Airflow pipeline every minute does not seem appropriate because each run performs multiple raw scans, validation queries, UPSERTs, DELETEs, and checkpoint updates. What would be the recommended StarRocks-native approach for this requirement? • Run the Airflow ODS pipeline every minute • Refresh the MV directly from

cdc_raw

• Use load-triggered

REFRESH ASYNC

• Schedule the MV refresh at a sub-minute interval • Use another StarRocks-native task or architecture Our target is for a CDC event to become queryable from the MV within 60 seconds.

@Rocky Hi, we are building an Airflow-based CDC Raw-to-ODS pipeline on StarRocks

4.0.8

shared-nothing. The Airflow DAG reads incremental CDC events from a partitioned

cdc_raw

table, selects the latest event per primary key using a CDC

version

, and applies UPSERTs and hard DELETEs to ODS Primary Key tables. It also manages checkpoints, delete tombstones, retries, and validation. The ODS tables are used as base tables for asynchronous materialized views. One of these MVs must reflect source changes within one minute. Running the full Airflow pipeline every minute does not seem appropriate because each run performs multiple raw scans, validation queries, UPSERTs, DELETEs, and checkpoint updates. What would be the recommended StarRocks-native approach for this requirement? • Run the Airflow ODS pipeline every minute • Refresh the MV directly from

cdc_raw

• Use load-triggered

REFRESH ASYNC

• Schedule the MV refresh at a sub-minute interval • Use another StarRocks-native task or architecture Our target is for a CDC event to become queryable from the MV within 60 seconds.

For your requirement of sub-minute latency (under 60 seconds) on StarRocks 4.0.8, the recommended StarRocks-native approach is to move away from Airflow-orchestrated ODS updates and leverage the Primary Key (PK) table engine combined with Incremental Asynchronous Materialized Views. Running the full Airflow pipeline every minute introduces significant overhead due to external task scheduling, connection setup, and multiple redundant scans. Instead, you can achieve your goal by following this architecture: 1. Ingest Directly to ODS using Primary Key Tables Instead of a

cdc_raw

table and an Airflow dedup logic, ingest your CDC events directly into your ODS (Primary Key) tables using Routine Load or the StarRocks Flink Connector. * Deduplication & Hard Deletes: StarRocks PK tables handle UPSERT and DELETE operations natively. By mapping your CDC

__op

column or operation flag, the engine performs the "latest event per PK" logic automatically during the ingestion phase. * Out-of-Order Handling: Use the Conditional Update feature (e.g.,

SET __version = version_column

) during ingestion to ensure that late-arriving events do not overwrite newer data. * Latency: This approach reduces ODS ingestion latency to a few seconds, bypassing the Airflow DAG entirely. 2. Configure the Materialized View for Incremental Refresh On top of your ODS (PK) tables, define your Materialized Views with the following properties: *

REFRESH ASYNC

(Load-Triggered): In StarRocks 4.0.x, setting the refresh strategy to

ASYNC

(without a specific interval) causes the MV to be triggered automatically as soon as a load transaction commits to the base ODS table. * `refresh_mode = "INCREMENTAL"`: Ensure the MV is created with incremental refresh. This allows StarRocks to calculate only the changes (delta) from the ODS table since the last refresh, rather than re-scanning the entire table or partition. * Partition Aligned: If your ODS table is partitioned (e.g., by date), ensure the MV is also partitioned on the same column. This allows StarRocks to refresh only the specific partitions that received updates. Recommended MV Definition

sql
CREATE MATERIALIZED VIEW ods_aggregate_mv
PARTITION BY (event_date)
REFRESH ASYNC  -- Triggered automatically by ODS table updates
PROPERTIES (
    "refresh_mode" = "INCREMENTAL", -- Crucial for sub-minute performance
    "mv_rewrite_staleness_second" = "60" -- Optional: Allows query rewrite to use slightly stale data
)
AS 
SELECT 
    event_date,
    pk_id,
    SUM(value) 
FROM ods_primary_key_table
GROUP BY event_date, pk_id;

Why this beats the Airflow approach: 1. Reduced I/O: Incremental refresh only reads the "new rowsets" (versions) produced by the last ingestion, rather than re-scanning

cdc_raw

. 2. Lower Latency: The trigger happens inside the StarRocks FE/BE immediately after data is committed. There is no "polling" delay from an external orchestrator. 3. Atomic Consistency: Primary Key tables in StarRocks 4.0.x provide optimized delete-vector handling, making the "Upsert + Delete" pattern significantly faster than manual

DELETE FROM ... WHERE ...

statements in a DAG. Conclusion: You should use load-triggered

REFRESH ASYNC

with

refresh_mode = "INCREMENTAL"

. If your ingestion is continuous (e.g., high-frequency small loads), you may alternatively use

REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)

to batch several small ODS commits into a single MV refresh, ensuring you stay within the 60-second target without over-taxing the system. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md