CREATE TABLE

Creates a table with named, typed columns and optional partitioning, clustering, deduplication, and retention rules.

Syntax

CREATE TABLE [ IF NOT EXISTS ] <table_name> (
    [ <col_name> <col_type> [ AS (<expr>) ] [ COMMENT '<string_literal>' ] [, ...] ]
)
[ PARTITION BY <expr> [, ...] ]
[ CLUSTER BY <expr> [, ...] ]
[ DISTINCT ON <expr> [, ...] [ BY <order_by_expr> [, ...] ] ]
[ DATA RETENTION <days> DAY ]
[ COMMENT = '<string_literal>' ]

Parameters

Use ScopeQL data types for column types. IF NOT EXISTS leaves an existing table unchanged; it does not update its definition.

AS (<expr>)

Defines a read-only computed column. The expression must use deterministic scalar operations over ordinary columns in the same table. Its result converts to <col_type>; evaluation or conversion errors produce NULL. The current expression also applies to historical rows.

PARTITION BY <expr> [, ...]

Groups rows with the same partition key values. Choose expressions commonly used in filters, such as an hourly event-time bucket, so queries can skip unrelated data.

Partition expressions must be deterministic and non-constant. They can reference columns and functions, such as trunc(event_time, unit => 'hour').

CLUSTER BY <expr> [, ...]

Keeps rows with related cluster key values close together within a partition. Choose fields used in filters or range conditions, such as service or event_time.

Cluster expressions must be deterministic and non-constant. You can use clustering together with partitioning.

DISTINCT ON <expr> [, ...] [ BY ... ]

Defines a deduplication key. BY specifies which row to retain for each key, using the same sort directions and NULL placement as ORDER BY. For example, order by event time descending to retain the latest state of each order.

DATA RETENTION <days> DAY

Sets automatic data retention. <days> must be a whole number from 1 through 1825. Omit this clause for no automatic retention policy. See the data retention guide for when data is removed and how to inspect the policy.

Examples

Store request events

Keep frequently queried fields in typed columns and additional attributes in an object. Partition by UTC hour and cluster by service:

CREATE TABLE request_events (
    event_time timestamp COMMENT 'Time the request completed',
    service string,
    latency_ms int,
    payload object
)
PARTITION BY trunc(event_time, unit => 'hour')
CLUSTER BY service
COMMENT = 'Completed application requests'

Retain the latest order status

Keep the newest event for each order and apply a 30-day retention policy:

CREATE TABLE latest_order_events (
    order_id string,
    event_time timestamp,
    event_id string,
    status string
)
DISTINCT ON order_id BY event_time DESC NULLS LAST, event_id DESC
DATA RETENTION 30 DAY