How to Use Data Types in ClickHouse

What are the core ClickHouse data type categories?

ClickHouse supplies Numeric, String, FixedString(N), Date/Time, UUID, Array, Tuple, Map, Enum, and Nullable(T) wrappers. Choosing the smallest adequate type cuts disk usage, RAM, and CPU.

How do I choose numeric types for IDs and money?

Store surrogate keys such as Customers.id in UInt64.Hold currency in Decimal(12,2) to keep cents exact and avoid floating-point rounding.

Example: Storing order totals precisely

CREATE TABLE Orders (
id UInt64,
customer_id UInt64,
order_date DateTime,
total_amount Decimal(12,2)
) ENGINE = MergeTree
ORDER BY id;

How do I store dates and times accurately?

Use Date for whole days, DateTime for second resolution, or DateTime64(3) for millisecond events like payment callbacks.

When should I enable LowCardinality?

Wrap String or Enum columns with few distinct values—e.g., Customers.country—in LowCardinality to get dictionary compression and faster group-bys.

Best practices for e-commerce schemas

• Keep primary keys as UInt64.
• Use Decimal for monetary fields.
• Add Nullable only when missing values are expected.
• Prefer Enum8/Enum16 for small, fixed value sets like order status.
• Benchmark LowCardinality on high-selectivity columns before production use.

Common pitfalls and how to avoid them

Avoid Float64 for totals; rounding errors accumulate.Define Decimal scale and precision correctly or ClickHouse silently truncates values.

.