Skip to Content
ArchitectureData Types

Data Types

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

Type Selection Flow

Quick Type Selection

Use this table when deciding a column type:

You store…UseWhy
IDs from SNOWFLAKE_ID()BIGINT64-bit integer ID generation
Small whole numbers (status, counters)INTCompact and fast
Large countersBIGINTHigher range
User-facing textTEXTFlexible string storage
True/false valuesBOOLEANClear semantics
Decimal money-like valuesDECIMAL(p,s)Exact precision
TimestampsTIMESTAMPTime-based queries and ordering
Dates onlyDATECalendar values without time
Structured document payloadJSONNested object/array data
Vector embeddingsEMBEDDING(n)Semantic retrieval and similarity search

Common Types in Practice

Integer Types

sql snippetSQL
CREATE TABLE app.orders (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  user_id BIGINT NOT NULL,  item_count INT NOT NULL DEFAULT 1) WITH (TYPE = 'USER');
  • Prefer INT for bounded counts.
  • Prefer BIGINT for IDs and values that can grow large.

Text and Boolean

sql snippetSQL
CREATE TABLE app.users (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  email TEXT NOT NULL,  display_name TEXT,  is_active BOOLEAN NOT NULL DEFAULT TRUE) WITH (TYPE = 'USER');
  • Use TEXT for variable-length strings.
  • Use BOOLEAN instead of 0/1 integers for feature flags and state.

Decimal Values

sql snippetSQL
CREATE TABLE billing.invoice_items (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  sku TEXT NOT NULL,  amount DECIMAL(12,2) NOT NULL,  tax_rate DECIMAL(5,4) NOT NULL DEFAULT 0.0000) WITH (TYPE = 'SHARED');
  • Use DECIMAL for exact arithmetic (pricing, balances, rates).
  • Pick precision/scale deliberately, e.g. DECIMAL(12,2).

Time Types

sql snippetSQL
CREATE TABLE chat.messages (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  user_id BIGINT NOT NULL,  content TEXT NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT NOW()) WITH (TYPE = 'USER'); SELECT *FROM chat.messagesWHERE created_at >= NOW() - INTERVAL '7 days'ORDER BY created_at DESC;
  • Use TIMESTAMP for event records and ordering.
  • Use DATE when time-of-day is not needed.

JSON Payloads

sql snippetSQL
CREATE TABLE app.events (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  event_name TEXT NOT NULL,  payload JSON,  created_at TIMESTAMP NOT NULL DEFAULT NOW()) WITH (  TYPE = 'STREAM',  TTL_SECONDS = 300);
  • Use JSON for dynamic payloads and schema-flexible metadata.
  • Keep frequently filtered fields as typed top-level columns.
sql snippetSQL
CREATE TABLE rag.documents_vectors (  id BIGINT PRIMARY KEY,  doc_embedding EMBEDDING(384)) WITH (TYPE = 'USER'); ALTER TABLE rag.documents_vectors  CREATE INDEX doc_embedding USING COSINE; SELECT idFROM rag.documents_vectorsORDER BY COSINE_DISTANCE(doc_embedding, '[0.12,0.03,0.98]')LIMIT 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 snippetSQL
SELECT CAST('42' AS INT) AS count_value;SELECT CAST(amount AS DECIMAL(12,2)) AS normalized_amountFROM 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 snippetSQL
CREATE TABLE app.sessions (  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),  user_id BIGINT NOT NULL,  started_at TIMESTAMP NOT NULL DEFAULT NOW(),  ended_at TIMESTAMP,  is_valid BOOLEAN NOT NULL DEFAULT TRUE) WITH (TYPE = 'USER');
  • 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
Last updated on
Clustering & HATable Types

Getting Started

Documentation

Resources

Community

© 2026 KalamDB. All rights reserved.
PrivacyTerms