@Rocky I’m designing a medallion pipeline (bronze → silver) in StarRocks 3.5 and would love advice on the best architecture. Context StarRocks: 3.5.13 Bronze table: append-only CDC-style, DUPLICATE KEY(id), ~54.6M rows, ~44.28M distinct ids Rows have several very wide columns (multiple varchar(65533) JSON-ish fields) Silver goal: 1 latest row per id (dedup by event_ts_ms DESC) and filter soft-deletes (is_deleted_psql=false) Query pattern: mostly WHERE conversation_id = ... (rare late arrivals, but possible) • I’d prefer to avoid external orchestration for now, so I’m leaning on async MV refresh What I tried • A single async MV using ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_ts_ms DESC) + rn=1 • A single async MV using GROUP BY id + max_by(...) • When selecting the wide text columns, both approaches can hit BE memory limits (“read and decompress page”). When selecting only minimal columns, both run; window allocated ~10GB and group-by allocated ~15GB in my test. Question What is the recommended StarRocks-native architecture for this use case? 1. One partitioned async MV (partitioned by month) with auto_refresh_partitions_limit so it refreshes only the most recent N partitions (accepting older partitions might not refresh automatically)? 2. Two-step inside StarRocks: a thin MV (id + max_event_ts_ms) and then a scheduled internal task (or another MV) to populate a PRIMARY KEY silver table via join-back? 3. Something else (e.g., change distribution/bloom filters, different partition key, recommended properties to reduce decompression memory pressure)? If you have a suggested pattern and any key properties/limits for large-cardinality dedup + wide columns, I’d really appreciate it. Thanks!

Hi! I’m Rocky. Dealing with 44M+ distinct IDs and wide JSON columns in a deduplication pipeline is a classic scenario where standard Async Materialized Views (MVs) hit memory limits. The reason your

max_by

and

ROW_NUMBER()

attempts are failing is that they both require an Aggregation Node in the refresh plan. This node must keep the "current best" wide string in memory for every distinct ID it processes in a batch. With 44M IDs and wide JSONs, this will almost always trigger a "read and decompress" memory overflow or OOM. For StarRocks 3.5.x, here is the recommended native architecture for a Medallion Silver layer: Recommended: Primary Key Table + Scheduled Task Instead of an Async MV, use a Primary Key (PK) table as your Silver layer and a StarRocks Task (native orchestration) to handle the upserts. Why this works: 1. Memory Efficiency: PK tables use a "Delete+Insert" strategy. During an

INSERT INTO ... SELECT

, StarRocks only needs to keep the Primary Key Index in memory (roughly 44M _ 20 bytes ≈ 800MB–1GB total, spread across BEs). It does _not* need to hold the wide JSON strings in memory to compare them—it simply overwrites the old row based on the ID. 2. Deduplication: The PK table handles the "1 latest row per id" logic automatically. 3. Native Orchestration: The

CREATE TASK

feature (introduced in 3.x) allows you to run this without Airflow or external tools. ────────── Step-by-Step Implementation 1. Create the Silver (Primary Key) Table Bucketing by

conversation_id

will optimize your primary query pattern.

sql
CREATE TABLE silver_table (
    id BIGINT NOT NULL,
    event_ts_ms BIGINT NOT NULL,
    conversation_id VARCHAR(255),
    wide_json_col1 VARCHAR(65533),
    wide_json_col2 VARCHAR(65533),
    is_deleted_psql BOOLEAN,
    -- ... other columns
) 
PRIMARY KEY (id)
PARTITION BY (event_ts_ms) -- Match Bronze partitioning if possible
DISTRIBUTED BY HASH(conversation_id) BUCKETS 64 
PROPERTIES (
    "enable_persistent_index" = "true" -- Critical: keeps PK index on disk/cache to save memory
);

2. Create the Refresh Task Use a "Delta Load" logic. The Task will pick up only new records from the Bronze table since the last refresh.

sql
CREATE TASK silver_refresh_task
WAREHOUSE = 'default_warehouse' -- Adjust if using a specific warehouse
SCHEDULE = 'EVERY 10 MINUTE'
AS 
INSERT INTO silver_table
SELECT * 
FROM bronze_table
WHERE event_ts_ms > (SELECT COALESCE(MAX(event_ts_ms), 0) FROM silver_table)
  AND is_deleted_psql = false;

_Note: If a row is deleted in PSQL after it was already in Silver, you would need to adjust the logic to also handle

is_deleted_psql = true

by inserting it as a delete marker or using a different sync strategy._ ────────── Why not the other options? * Option 1 (Partitioned Async MV): Even with

auto_refresh_partitions_limit

, the initial refresh or any partition containing many wide strings will still use the memory-heavy Aggregation node. It doesn't solve the fundamental "strings in memory" problem. * Option 2 (Thin MV + Join-back): This is a valid "manual" way to do what a PK table does automatically. Since StarRocks 3.x has highly optimized PK tables and native Tasks, Option 2 is now unnecessarily complex. Additional Pro-Tips for Wide Columns: * Persistent Index: Always set

"enable_persistent_index" = "true"

for PK tables with high cardinality. This prevents the primary key index from consuming all your RAM. * Compression: Since your wide columns are JSON-ish, ensure you are using the default LZ4 or ZSTD compression to reduce the "decompress page" pressure. * Page Cache: If you hit memory limits during

SELECT

, consider increasing

page_cache_limit

in the

be.conf

, but the PK table approach should already significantly reduce the pressure during the write (Silver) phase. Does this architecture fit your current workflow, or should we look closer at the soft-delete/late-arrival logic?

@Rocky The soft-delete would be a problem, Silver layer should not contain any deleted id

I completely agree. In a Medallion architecture, the Silver layer is intended to be the "source of truth" for cleaned and integrated data. Keeping soft-deleted records in this layer adds unnecessary complexity to downstream queries and can degrade performance. To ensure your Silver layer in StarRocks contains only active records, the best approach is to use the Primary Key (PK) table model and handle the deletions during the ingestion process from the Bronze layer. 1. Use the Primary Key Table Model The Primary Key table is specifically designed for real-time updates and deletes. Unlike other models, it uses a Delete+Insert strategy that physically marks records for deletion (via a

DelVector

), meaning they are filtered out at the storage level before the query engine even sees them. 2. Converting Soft-Deletes to Hard Deletes If your Bronze layer (or source system) provides a soft-delete flag (e.g.,

is_deleted = true

or

op_type = 'D'

), you can map this to StarRocks' internal

__op

column during the load into your Silver layer. * `__op = 0`: Upsert (Insert or Update) * `__op = 1`: Delete Example using Stream Load If you are loading data from Bronze to Silver via a script or tool, you can specify the mapping:

bash
curl --location-trusted -u <user>:<passwd> \
    -H "columns: id, name, is_deleted, __op = case when is_deleted = 1 then 'delete' else 'upsert' end" \
    -T data.csv -XPUT \
    http://<fe_host>:<fe_http_port>/api/db/silver_table/_stream_load

Example using Routine Load (Kafka) If you are streaming from Kafka, you can define the transformation in the

COLUMNS

clause:

sql
CREATE ROUTINE LOAD db.load_silver ON silver_table
COLUMNS (id, name, status, __op = IF(status = 'DELETED', 1, 0))
FROM KAFKA (...)

Why this is better for the Silver Layer: 1. Cleaner Queries: Your analysts don't need to remember to add

WHERE is_deleted = false

to every single query. 2. Performance: Since the records are marked as deleted in the primary key index, StarRocks avoids scanning that data entirely. 3. Storage Efficiency: Once compaction runs, those "deleted" IDs are physically removed from the disk, saving space. 4. Downstream Simplicity: When you build your Gold layer (Aggregates/Data Marts), you are guaranteed to be working with only valid, current records. By handling the "hard delete" at the point of entry into the Silver layer, you maintain a high-performance, high-quality data warehouse. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md