@Rocky I have a primary key table that is getting populated by a pipe BUT it seems like duplicate pk values are getting into the target table, how?

In StarRocks, a Primary Key (PK) table is specifically designed to prevent duplicate primary key values by using a "Delete+Insert" strategy. If you are seeing duplicates in your target table while using a Pipe, it is usually due to one of the following reasons: 1. The Table is not actually a Primary Key Table This is the most common cause. In StarRocks, if you do not explicitly define a

PRIMARY KEY

, the table defaults to a Duplicate Key model (which allows duplicates). * Check: Run

SHOW CREATE TABLE <table_name>;

* Fix: Ensure you see

PRIMARY KEY(column_names)

in the schema definition. If it says

DUPLICATE KEY

or

ORDER BY

without a

PRIMARY KEY

clause, the table will allow multiple rows with the same "key" values. 2. Composite Primary Key Misunderstanding If your table has a composite primary key (multiple columns), StarRocks only enforces uniqueness across the combination of all PK columns. * Scenario: If your PK is

PRIMARY KEY(id, event_time)

, then

(1, '2023-01-01')

and

(1, '2023-01-02')

are considered distinct rows. * Check: Verify the columns included in the

PRIMARY KEY()

clause in your

SHOW CREATE TABLE

output. 3. Data Integrity Issues (Whitespace or Case Sensitivity) The primary key index is sensitive to the exact data content. * Whitespace:

'123'

and

'123 '

(with a trailing space) are different keys. * Case Sensitivity:

'abc'

and

'ABC'

are different keys for

VARCHAR

types. * Check: Run a query like

SELECT pk_col, LENGTH(pk_col) FROM table WHERE pk_col = 'your_value';

to see if hidden characters exist. 4. Querying a Materialized View or External Table If you are querying an Asynchronous Materialized View or an External Table (via the

FILES()

function directly), StarRocks does not enforce PK uniqueness there. Uniqueness is only enforced within the internal Primary Key table itself. 5. Partial Updates and Default Values If you are using the Partial Update feature (e.g.,

SET "partial_update" = "true"

) and your

INSERT

statement doesn't provide values for all PK columns, StarRocks might be using default values for the missing PK columns, leading to unexpected "duplicates" if multiple rows fall into those defaults. (However, even then, they should upsert). 6. Pipe Retries and Transactional Behavior StarRocks Pipes use

INSERT INTO ... SELECT FROM FILES()

. This is an idempotent operation for PK tables. Even if a file is processed twice (due to a retry or a pipe restart), the PK table will simply overwrite the existing rows with the same key rather than creating duplicates. * Note: If you are seeing duplicates, it is likely not because the Pipe is "double loading," but because the table itself isn't configured to deduplicate. Recommended Troubleshooting Steps: 1. Verify the Model: Run

SHOW CREATE TABLE <your_table_name>

and confirm it says

PRIMARY KEY

. 2. Count Duplicates: Run this query to confirm the issue:

sql
    SELECT pk_column, COUNT(*)
    FROM your_table
    GROUP BY pk_column
    HAVING COUNT(*) > 1;

3. Check for Hidden Characters: If the query above returns nothing but you still see what looks like duplicates, check for trailing spaces or case differences in the values. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • loading/Flink-connector-starrocks.md