Data Types

This page is a practical guide for using data types in SQL when building with KalamDB.

Data types are independent from table types, but some types have table-type-specific behavior. FILE columns are supported on USER and SHARED tables, and EMBEDDING(n) columns become most useful with vector search. See /docs/server/architecture/table-types for the table boundary.

Type Selection Flow

Quick Type Selection

Use this table when deciding a column type:

You store…	Use	Why
IDs from `SNOWFLAKE_ID()`	`BIGINT`	64-bit integer ID generation
Small whole numbers (status, counters)	`INT`	Compact and fast
Large counters	`BIGINT`	Higher range
User-facing text	`TEXT`	Flexible string storage
True/false values	`BOOLEAN`	Clear semantics
Decimal money-like values	`DECIMAL(p,s)`	Exact precision
Timestamps	`TIMESTAMP`	Time-based queries and ordering
Dates only	`DATE`	Calendar values without time
Structured document payload	`JSON`	Nested object/array data
Vector embeddings	`EMBEDDING(n)`	Semantic retrieval and similarity search

Common Types in Practice

Integer Types

SQL

1CREATE TABLE app.orders (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  user_id BIGINT NOT NULL,4  item_count INT NOT NULL DEFAULT 15) WITH (TYPE = 'USER');

Prefer INT for bounded counts.
Prefer BIGINT for IDs and values that can grow large.

Text and Boolean

SQL

1CREATE TABLE app.users (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  email TEXT NOT NULL,4  display_name TEXT,5  is_active BOOLEAN NOT NULL DEFAULT TRUE6) WITH (TYPE = 'USER');

Use TEXT for variable-length strings.
Use BOOLEAN instead of 0/1 integers for feature flags and state.

Decimal Values

SQL

1CREATE TABLE billing.invoice_items (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  sku TEXT NOT NULL,4  amount DECIMAL(12,2) NOT NULL,5  tax_rate DECIMAL(5,4) NOT NULL DEFAULT 0.00006) WITH (TYPE = 'SHARED');

Use DECIMAL for exact arithmetic (pricing, balances, rates).
Pick precision/scale deliberately, e.g. DECIMAL(12,2).

Time Types

SQL

1CREATE TABLE chat.messages (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  user_id BIGINT NOT NULL,4  content TEXT NOT NULL,5  created_at TIMESTAMP NOT NULL DEFAULT NOW()6) WITH (TYPE = 'USER');7 8SELECT *9FROM chat.messages10WHERE created_at >= NOW() - INTERVAL '7 days'11ORDER BY created_at DESC;

Use TIMESTAMP for event records and ordering.
Use DATE when time-of-day is not needed.

JSON Payloads

SQL

1CREATE TABLE app.events (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  event_name TEXT NOT NULL,4  payload JSON,5  created_at TIMESTAMP NOT NULL DEFAULT NOW()6) WITH (7  TYPE = 'STREAM',8  TTL_SECONDS = 3009);

Use JSON for dynamic payloads and schema-flexible metadata.
Keep frequently filtered fields as typed top-level columns.

Embeddings for Vector Search

SQL

1CREATE TABLE rag.documents_vectors (2  id BIGINT PRIMARY KEY,3  doc_embedding EMBEDDING(384)4) WITH (TYPE = 'USER');5 6ALTER TABLE rag.documents_vectors7  CREATE INDEX doc_embedding USING COSINE;8 9SELECT id10FROM rag.documents_vectors11ORDER BY COSINE_DISTANCE(doc_embedding, '[0.12,0.03,0.98]')12LIMIT 5;

Use EMBEDDING(n) for fixed-size vector columns.
Pick n to match the embedding model you generate in your app or worker.
Index the embedding column before using it for nearest-neighbor retrieval.

Type Conversion

Use explicit casts when moving between compatible types.

SQL

1SELECT CAST('42' AS INT) AS count_value;2SELECT CAST(amount AS DECIMAL(12,2)) AS normalized_amount3FROM billing.invoice_items;

Prefer explicit conversion over relying on implicit casting in critical queries.

Nullability and Defaults

Use NOT NULL for required fields and DEFAULT for stable inserts.

SQL

1CREATE TABLE app.sessions (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  user_id BIGINT NOT NULL,4  started_at TIMESTAMP NOT NULL DEFAULT NOW(),5  ended_at TIMESTAMP,6  is_valid BOOLEAN NOT NULL DEFAULT TRUE7) WITH (TYPE = 'USER');

Recommended Patterns

IDs: BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID()
Created timestamps: TIMESTAMP NOT NULL DEFAULT NOW()
Money/rates: DECIMAL (not floating point)
Optional user text: nullable TEXT
Real-time transient events: STREAM + TTL_SECONDS