Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Duplicate Key Model

The Duplicate Key Model is the default table model in VeloDB Cloud. It stores every original data record without deduplication or aggregation. When creating a table, use the DUPLICATE KEY keyword to specify the sort columns for data storage and optimize common queries. It is generally recommended to choose three or fewer columns as the sort key. For details on choosing sort columns, see Sort Key.

Applicable scenarios​

Use the Duplicate Key model for append-only workloads that need to retain every original record:

  • Logs: Retain access logs and error logs for auditing and troubleshooting.
  • User behavior: Keep click events and access trails for user profiles and behavior-path analysis.
  • Transaction details: Preserve completed transaction records for reconciliation when the workload does not need to update those records.

Core features​

The model preserves source records and lets you choose aggregation dimensions at query time:

FeatureDescription
Retain original dataKeeps every inserted record for queries that need raw details.
No deduplication or aggregationUnlike the Aggregate Key Model and the Unique Key Model, the Duplicate Key Model does not deduplicate or aggregate data. Even if two rows are identical, both are fully retained on each insert.
Flexible data queriesQuery individual records or aggregate by any dimension from the retained dataset.

Table creation syntax​

When creating a table, use the DUPLICATE KEY keyword to specify the Duplicate Key Model and to designate the Key columns. In the Duplicate Key Model, Key columns are used only for sorting and are not required to be unique.

The following example creates a duplicate table for storing log information, sorted by the three columns log_time, log_type, and error_code:

CREATE TABLE IF NOT EXISTS example_tbl_duplicate
(
log_time DATETIME NOT NULL,
log_type INT NOT NULL,
error_code INT,
error_msg VARCHAR(1024),
op_id BIGINT,
op_time DATETIME
)
DUPLICATE KEY(log_time, log_type, error_code)
DISTRIBUTED BY HASH(log_type) BUCKETS 10;

Data writes and storage​

In a duplicate table, data is not deduplicated or aggregated. Inserted data is stored as-is, and Key columns are used only as sort keys.

Diagram showing how inserted rows are retained in a Duplicate Key table

The example inserts 4 original rows and appends 2 more rows. All 6 rows are retained, including the repeated record. The output illustrates the retained rows. Without an ORDER BY clause, the displayed row order is not guaranteed:

-- Write 4 rows of original data
INSERT INTO example_tbl_duplicate VALUES
('2024-11-01 00:00:00', 2, 2, 'timeout', 12, '2024-11-01 01:00:00'),
('2024-11-02 00:00:00', 1, 2, 'success', 13, '2024-11-02 01:00:00'),
('2024-11-03 00:00:00', 2, 2, 'unknown', 13, '2024-11-03 01:00:00'),
('2024-11-04 00:00:00', 2, 2, 'unknown', 12, '2024-11-04 01:00:00');

-- Append 2 more rows
INSERT INTO example_tbl_duplicate VALUES
('2024-11-01 00:00:00', 2, 2, 'timeout', 12, '2024-11-01 01:00:00'),
('2024-11-01 00:00:00', 2, 2, 'unknown', 13, '2024-11-01 01:00:00');

-- The query result shows that all 6 rows are retained
SELECT * FROM example_tbl_duplicate;
+---------------------+----------+------------+-----------+-------+---------------------+
| log_time | log_type | error_code | error_msg | op_id | op_time |
+---------------------+----------+------------+-----------+-------+---------------------+
| 2024-11-02 00:00:00 | 1 | 2 | success | 13 | 2024-11-02 01:00:00 |
| 2024-11-01 00:00:00 | 2 | 2 | timeout | 12 | 2024-11-01 01:00:00 |
| 2024-11-03 00:00:00 | 2 | 2 | unknown | 13 | 2024-11-03 01:00:00 |
| 2024-11-04 00:00:00 | 2 | 2 | unknown | 12 | 2024-11-04 01:00:00 |
| 2024-11-01 00:00:00 | 2 | 2 | unknown | 13 | 2024-11-01 01:00:00 |
| 2024-11-01 00:00:00 | 2 | 2 | timeout | 12 | 2024-11-01 01:00:00 |
+---------------------+----------+------------+-----------+-------+---------------------+