@Rocky I have a fact table that joins via location_id to some tables for tenant-related aggregations while also joins via member_id to some tables for members-related aggregations. What can you recommend in terms of partitioning, sort keys, index, etc?

To optimize a fact table that serves two distinct high-priority join paths—*tenant-related (

location_id

)* and *member-related (

member_id

)*—you need to manage the trade-offs of physical data layout. StarRocks can only physically distribute and sort data in one primary way, so you must choose one path for "maximum" optimization and use secondary indexes or materialized views for the other. Here are Rocky's recommendations: 1. Data Distribution (Bucketing) The distribution key determines which join can be a Colocate Join (the fastest join type, as it eliminates network shuffle). * Recommendation: Identify which join path is more frequent or involves larger dimension tables. * Case A (Tenant-centric): If you often aggregate by

location_id

, use

DISTRIBUTED BY HASH(location_id)

. Ensure your location/tenant dimension tables are also distributed by

location_id

and use the same

colocate_with

property. * Case B (Member-centric): If member aggregations are more intensive, use

DISTRIBUTED BY HASH(member_id)

. * Note: If you cannot decide, pick the column with higher cardinality to prevent data skew. 2. Sort Keys (

ORDER BY

) The sort key determines the storage order within each tablet, enabling Prefix Index pruning and Zone Map filtering. * Composite Sort Key: Use both keys in the

ORDER BY

clause. The order depends on your filter patterns. * If most queries filter by tenant first:

ORDER BY (location_id, member_id, timestamp)

. * If you have many point-lookups for members:

ORDER BY (member_id, location_id, timestamp)

. * Pro Tip: In the Primary Key model (v3.0+), you can decouple the Primary Key from the Sort Key. Use

PRIMARY KEY (event_id)

and

ORDER BY (location_id, member_id)

to optimize for queries without being forced to sort by the unique ID. 3. Indexes (For the "Other" Key) Since the physical layout can only favor one key for bucketing/sorting, use indexes to bridge the gap for the second key. * Bloom Filter Index: For the key not chosen as the primary distribution/sort column, add a Bloom Filter. For example, if you bucket/sort by

location_id

, add:

ALTER TABLE fact_table SET ("bloom_filter_columns" = "member_id");

This significantly speeds up equality joins (

WHERE member_id = 123

) by skipping data blocks that don't contain that member. * Bitmap Index: If

location_id

has low-to-medium cardinality (e.g., thousands of locations), a Bitmap index can help filter data rows quickly without a full scan. 4. Advanced: Secondary Layout via Materialized Views If both join paths are equally critical and the table is large, use an Asynchronous Materialized View (MV) to create a secondary physical layout. * Strategy: Define an MV that selects all columns from the fact table but uses the other key for distribution and sorting.

sql
    CREATE MATERIALIZED VIEW member_optimized_mv
    DISTRIBUTED BY HASH(member_id)
    ORDER BY (member_id)
    AS SELECT * FROM fact_table;

* Benefit: StarRocks' optimizer can automatically rewrite queries to use this MV when it detects a join or filter on

member_id

, effectively giving you the benefits of two different table designs for the price of extra storage. 5. Partitioning Partitioning is primarily for data lifecycle management and coarse-grained pruning. * Recommendation: Partition by time (e.g.,

event_date

). * Avoid partitioning by

location_id

or

member_id

unless one tenant/member has such a massive volume of data that it requires its own lifecycle management (e.g., different retention periods). * Keep partitions around 10GB–100GB each for optimal performance. Summary Checklist Feature | Primary Path (e.g., Tenant) | Secondary Path (e.g., Member) Bucketing |

HASH(location_id)

| N/A Sort Key | 1st in

ORDER BY

| 2nd in

ORDER BY

Indexes | Prefix Index | Bloom Filter Advanced | Base Table | Asynchronous MV References

@Rocky if we partition by month what happens to filters based on start date and end date like '2025-08-03 5PM' and '2025-11-13 3AM'?

If you partition your table by month (e.g., using

PARTITION BY date_trunc('month', dt)

), here is exactly what happens when you run a query with a specific range like

'2025-08-03 17:00:00'

to `'2025-11-13 030000'`: 1. Partition Pruning (The Big Skip) StarRocks' optimizer will immediately identify which monthly partitions overlap with your date range. For your specific filter, it will target only these 4 partitions: * August 2025 * September 2025 * October 2025 * November 2025 It will completely skip (ignore) all other partitions (e.g., July 2025 or December 2025), which significantly reduces the amount of data scanned. 2. Fine-Grained Filtering (The Intra-Partition Scan) Because the partition granularity (Month) is coarser than your filter (Day/Hour), the "August" partition contains data for the whole month. To handle the specific "August 3rd, 5 PM" start time, StarRocks uses two internal mechanisms within those 4 partitions: * Zone Maps: StarRocks stores the Min/Max values for every column in every "Data Page" (usually 64KB–1MB chunks). Even though it's reading the August partition, it will skip the data pages where the Max value is earlier than

2025-08-03 17:00:00

. * Sort Keys (Prefix Index): If your

ORDER BY

clause (Sort Key) starts with your date column, StarRocks can perform a binary search within the partition to jump directly to the first row that matches

2025-08-03 17:00:00

. 3. The Performance Trade-off * Monthly Partitioning (Coarser): This is great for managing metadata. If you have years of data, you won't end up with thousands of tiny partitions. However, for a query that asks for only 1 day of data, it still has to "look into" a whole month's worth of metadata and potentially more data pages. * Daily Partitioning (Finer): If you frequently query very narrow ranges (like "the last 3 hours"), daily partitioning is faster because the "Big Skip" (Pruning) would eliminate 29-30 more days of data before even touching the disk. Rocky’s Recommendation For your specific case where you are also joining by

location_id

or `member_id`: * Use Monthly partitioning if your fact table is massive and you keep data for several years. This keeps the number of "Tablets" (the smallest unit of distribution) under control. * Ensure your date column is in your Sort Key (e.g.,

ORDER BY (location_id, event_date)

). This ensures that even though you are reading a "Monthly" partition, StarRocks can still pinpoint the exact hour/minute within that month extremely efficiently. In short: Your filters will work perfectly. StarRocks will pick the months of Aug, Sep, Oct, and Nov, and then use internal indexes to ignore the specific hours/days you didn't ask for. References • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md • table_design/data_distribution/Data_distribution.md

@Rocky im curious about the sort key. can you explain more for example i set it like this? (member_id, location_id, utc_created)

Setting your sort key to

(member_id, location_id, utc_created)

dictates the physical order of data on disk. StarRocks uses this to build a Prefix Index (also known as a Short Key Index), which is one of the most powerful tools for query acceleration. Here is exactly how that specific order will behave: 1. How the Index is Built StarRocks takes the first 36 bytes of your sort key to create a sparse index entry for every 1,024 rows. *

member_id

(BIGINT: 8 bytes): Takes the first 8 bytes. *

location_id

(INT: 4 bytes): Takes the next 4 bytes. *

utc_created

(DATETIME: 8 bytes): Takes the next 8 bytes. * Total used: 20 bytes (well within the 36-byte limit). 2. Which Queries Benefit? The Prefix Index works like a telephone book; it only works if you search from the "left" of the key. * Scenario A:

WHERE member_id = 500

* Result: Excellent. StarRocks uses the index to binary-search and jump directly to the data blocks for member 500. * Scenario B:

WHERE member_id = 500 AND location_id = 10

* Result: Fastest. It pinpoints the exact intersection of that member at that location. * Scenario C:

WHERE location_id = 10

* Result: No Prefix Index benefit. Because you skipped

member_id

, StarRocks cannot use the prefix index. It will have to scan the data (though it may still use Bloom Filters or Zone Maps if you have them). * Scenario D:

WHERE member_id = 500 AND utc_created > '2024-01-01'

* Result: Great. It finds member 500 first, then uses the sorted nature of

utc_created

to scan only the relevant time range for that member. 3. The "VARCHAR" Trap If any of your columns are

VARCHAR

, be careful. In StarRocks, a VARCHAR column can only appear once in a prefix index, and it must be the last part of the prefix. * If

member_id

were a

VARCHAR

, the index would stop there.

location_id

and

utc_created

would not be part of the 36-byte prefix index, even if they were listed in the

ORDER BY

. 4. Rocky’s Recommendation for your Dual-Join Fact Table Since you have two major join paths (Tenant/Location and Member), putting

member_id

first in the sort key makes this table a "Member-First" table. To balance this for your

location_id

queries: 1. Keep

member_id

first if you have more members than locations (higher cardinality usually goes first). 2. Add a Bloom Filter on

location_id

: This compensates for it being second in the sort key. It allows the engine to skip blocks that don't contain a specific

location_id

even if

member_id

isn't in the filter.

sql
    ALTER TABLE your_table SET ("bloom_filter_columns" = "location_id");

3. Check your Join Type: If you distribute by

location_id

(for Colocate Joins) but sort by

member_id

(for fast member lookups), you get a "best of both worlds" scenario for different query types. Summary: Your proposed order

(member_id, location_id, utc_created)

is perfect if your primary "point lookups" or filters start with a Member ID. If you find yourself filtering by Location ID significantly more often, simply swap the first two. References • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md • table_design/table_types/primary_key_table.md

@Rocky i am using starrocks kafka connector to stream data from kafka to starrocks....however i got problem with utc_created having NULL from kafka but we defined it

utc_created

DATETIME NOT NULL DEFAULT "1970-01-01 000000" like that in starrocks....what is the fix?

The reason you are seeing this error is that in StarRocks (and most SQL databases), a

DEFAULT

value only triggers if the column is missing from the ingestion payload. If your Kafka message explicitly contains

"utc_created": null

, the connector tries to insert that literal

NULL

into your

NOT NULL

column, causing the load to fail. To fix this, you need to use a data transformation during the ingestion process. The Fix: Use

sink.properties.columns

In your StarRocks Kafka Connector configuration (JSON or properties file), you can add a column mapping with the

ifnull()

or

coalesce()

function. This tells StarRocks: "Take the value from Kafka, and if it's null, use this default instead." Add the following to your connector configuration:

properties
# 1. Map the Kafka field to a temporary variable (e.g., tmp_utc)
# 2. Assign the actual column using the ifnull function
sink.properties.columns=member_id, location_id, tmp_utc, utc_created=ifnull(tmp_utc, '1970-01-01 00:00:00')

If you are using the JSON configuration for Kafka Connect:

json
"config": {
    "connector.class": "com.starrocks.connector.kafka.StarRocksSinkConnector",
    "starrocks.database.name": "your_db",
    "starrocks.table.name": "your_table",
    "sink.properties.columns": "member_id, location_id, tmp_utc, utc_created=ifnull(tmp_utc, '1970-01-01 00:00:00')",
    ...
}

Why this works: *

tmp_utc

: This is a session variable that holds the raw value from your Kafka record. *

utc_created=ifnull(...)

: This tells StarRocks to perform a calculation before the data hits the storage engine. It checks if

tmp_utc

is null; if so, it writes your desired default string. Alternative: Handle it at the Source (Kafka Connect SMT) If you prefer not to do this in StarRocks, you can use a Kafka Connect Transformation (SMT) to replace nulls before they even reach the StarRocks Connector. Add this to your connector config:

properties
transforms=replaceNull
transforms.replaceNull.type=org.apache.kafka.connect.transforms.ReplaceField$Value
transforms.replaceNull.renames=utc_created:utc_created
# Note: Basic SMTs are limited. The StarRocks 'sink.properties.columns' approach above is usually much easier.

Pro-Tip: Check "Strict Mode" Ensure

sink.properties.strict_mode

is set to

false

(which is the default). If it's

true

, any row that requires a conversion (like a null being forced into a default) might be filtered out more aggressively depending on the error. Summary: Use

sink.properties.columns

to map the incoming field to a temporary name and then use

ifnull()

to apply your default value. References • loading/Kafka-connector-starrocks.md • loading/Spark-connector-starrocks.md