CREATE TABLE

CREATE TABLE defines a local table and its storage contract.

Append table

CREATE TABLE trades (
  id UInt64 NOT NULL,
  market LowCardinality(String) NOT NULL,
  price Decimal(20, 8),
  quantity UInt64 DEFAULT 0,
  observed_at DateTime64(6),
  PRIMARY KEY (id),
  INDEX market_idx market TYPE set(1024) GRANULARITY 1
)
PARTITION BY to_yyyymm(observed_at)
ORDER BY (market, observed_at)
SETTINGS
  mode = 'append',
  storage_policy = 'disk_data',
  index_granularity = 8192;

Append tables retain accepted rows. A primary key may describe row identity but does not make an append table replace prior values.

Keyed table

CREATE TABLE balances (
  address String NOT NULL,
  asset String NOT NULL,
  balance UInt256 NOT NULL,
  updated_at DateTime64(6),
  PRIMARY KEY (address, asset)
)
ORDER BY (address, asset)
SETTINGS
  mode = 'keyed',
  storage_policy = 'sync_disk';

A keyed table exposes the latest committed row for each primary key. It must declare PRIMARY KEY. UPDATE and DELETE apply only to keyed tables.

Attribute table

CREATE TABLE accounts (
  account_id UInt64,
  ATTRIBUTE (
    balance UInt256 CODEC(Delta, ZSTD),
    tier LowCardinality(String),
    last_seen DateTime64(6)
  )
)
SETTINGS
  kind = 'attribute',
  storage_policy = 'disk_data',
  entity = (account_id),
  projection = 'on',
  spillover = 'on',
  max_attributes = 128;

Attribute tables version fields independently and reconstruct the current entity projection. entity is required. Optional valid_by and revision_by settings identify the time and revision dimensions when the schema provides them.

Column declarations

The column form is:

column_name DataType [NOT NULL] [DEFAULT expression] [CODEC(codec, ...)]

• NOT NULL rejects null input.
• DEFAULT is evaluated when an insert omits the column.
• CODEC controls column encoding for persisted parts.
• Nullable(T) is the explicit nullable type.
• Attribute columns belong inside ATTRIBUTE (...).

See Data types for the type catalog.

Keys and physical expressions

PRIMARY KEY defines keyed identity. ORDER BY, PARTITION BY, and SHARD BY expression SHARDS n describe the physical organization accepted by the single-node engine. Shards are local execution/storage partitions; they do not create a distributed deployment.

Inline indexes

Declare skip or value indexes inside CREATE TABLE:

INDEX amount_minmax amount TYPE minmax GRANULARITY 1
INDEX market_set market TYPE set(2048) GRANULARITY 2
INDEX memo_bloom memo TYPE bloom_filter GRANULARITY 1

Index availability and tail coverage vary by kind. See Indexes.

Table settings

Use kind = 'attribute' with entity and the attribute-specific settings for attribute tables. Attribute tables reject ordinary-table mode, shard, and layout settings.

Legacy settings such as storage, durability, residency, and manual flush_* thresholds are not valid table settings in the current grammar.