Data types for analytics

Smaller types compress better and scan faster, which is most of ClickHouse performance.

ClickHouse types are strict and sized, and choosing them well directly shrinks disk and speeds scans.

Integers come sized and signed or unsigned: UInt8, Int32, UInt64 and so on. Use the smallest that fits.
Float32/Float64 for measurements, Decimal(P, S) for money.
String holds arbitrary bytes with no length limit; FixedString(N) for fixed codes. UUID is a native type.
Date, Date32, DateTime (seconds) and DateTime64(3) (milliseconds). A timezone can be attached: DateTime('UTC').
LowCardinality(String) dictionary-encodes a column with few distinct values (country, status, event name). It is often the single biggest win.
Enum8('a' = 1, 'b' = 2) for a fixed set of values.
Array(T), Map(K, V), Tuple(...) and the JSON type for semi-structured data.

Columns are not nullable by default: a missing value becomes the type's default (0, empty string, 1970-01-01). Wrapping a type in Nullable(T) adds a separate null-mask column and costs speed, so use it only when "unknown" truly differs from zero or empty.

Use toTypeName(x) to see the type of any expression, and conversion functions such as toUInt32, toDateTime or toDecimal64(x, 2). The OrZero/OrNull variants (toUInt32OrNull('abc')) convert without throwing.

Example

SELECT
    toTypeName(1)                       AS small_int,    -- UInt8
    toTypeName(-300)                    AS signed_int,   -- Int16
    toTypeName(now64(3))                AS ts,           -- DateTime64(3)
    toTypeName(toLowCardinality('EU'))  AS lc,           -- LowCardinality(String)
    toUInt32OrNull('abc')               AS bad_number,   -- NULL
    toDecimal64(19.999, 2)              AS money;        -- 19.99

CREATE TABLE orders
(
    id       UInt64,
    country  LowCardinality(String),
    amount   Decimal(12, 2),
    coupon   Nullable(String),          -- only because "no coupon" matters
    created  DateTime64(3, 'UTC')
)
ENGINE = MergeTree ORDER BY (country, created);

Remember

Use the smallest integer and time type that fits
LowCardinality(String) for columns with up to ~10k distinct values
Columns are non-nullable by default; Nullable has a cost

Watch out for

Storing timestamps or numbers as String, which compresses worse and blocks date and math functions.

Check yourself

A column country has 200 distinct values across 1 billion rows. Best type?
You insert a row without a value for a UInt32 column (not Nullable). What is stored?

Official docs: clickhouse.com/docs/sql-reference/data-types

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks