Data Types
This page is a practical guide for using data types in SQL when building with KalamDB.
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 |
Common Types in Practice
Integer Types
CREATE USER TABLE app.orders (
id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
user_id BIGINT NOT NULL,
item_count INT NOT NULL DEFAULT 1
);- Prefer
INTfor bounded counts. - Prefer
BIGINTfor IDs and values that can grow large.
Text and Boolean
CREATE USER TABLE app.users (
id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
email TEXT NOT NULL,
display_name TEXT,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);- Use
TEXTfor variable-length strings. - Use
BOOLEANinstead of0/1integers for feature flags and state.
Decimal Values
CREATE SHARED 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
);- Use
DECIMALfor exact arithmetic (pricing, balances, rates). - Pick precision/scale deliberately, e.g.
DECIMAL(12,2).
Time Types
CREATE USER 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()
);
SELECT *
FROM chat.messages
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;- Use
TIMESTAMPfor event records and ordering. - Use
DATEwhen time-of-day is not needed.
JSON Payloads
CREATE STREAM 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 (
TTL_SECONDS = 300
);- Use
JSONfor dynamic payloads and schema-flexible metadata. - Keep frequently filtered fields as typed top-level columns.
Type Conversion
Use explicit casts when moving between compatible types.
SELECT CAST('42' AS INT) AS count_value;
SELECT CAST(amount AS DECIMAL(12,2)) AS normalized_amount
FROM 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.
CREATE USER 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
);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
Related Docs
Last updated on