CQL Fundamentals

CQL Data Types

Master Cassandra's rich type system! From simple text and numbers to complex collections and UUIDs.

📖 The Story: Sarah's E-Commerce Disaster

Sarah just launched her first e-commerce app. She stored EVERYTHING as TEXT because "it's simple!" But then Black Friday happened...

❌ The WRONG Way (Everything as TEXT)

                    CREATE TABLE products_wrong (
                        product_id TEXT,
                        price TEXT,        -- "19.99" stored as text!
                        stock TEXT,        -- "150" stored as text!
                        created TEXT       -- "2024-01-15" as text!
                    );
                                    

What Went Wrong:

💸 Price sorting broke: "9.99" came BEFORE "19.99" alphabetically!
📊 Can't calculate totals: Can't SUM text
🗓️ Date filtering disaster: String comparison chaos

Result: $50,000 in lost Black Friday sales! 😱

✅ The RIGHT Way (Proper Types)

                    CREATE TABLE products_correct (
                        product_id UUID PRIMARY KEY,
                        name TEXT,
                        price DECIMAL,
                        stock INT,
                        created TIMESTAMP
                    );
                                    

Next Black Friday: Zero crashes! Revenue up 40%! 🚀

🔤 Simple Data Types

📝

TEXT / VARCHAR

Purpose: Store letters, words, sentences - any UTF-8 text!

Max Size: Up to 2GB (but keep it reasonable - a few KB typical)

📦 How Cassandra Stores It:

Internally: UTF-8 encoded byte array
Example: "Hello" → [48 65 6C 6C 6F] (hex bytes)

Real Examples:

            -- User names
            'Sarah Johnson'
            '李明'  -- UTF-8 Chinese chars!
            'José García 🎉'  -- Emojis OK!

            -- Email addresses
            'sarah.j@example.com'

            -- Product descriptions
            'Wireless Bluetooth Headphones
            with 30hr battery life'

            -- Blog post excerpt
            'Learn Cassandra in 30 days...'
                                

Use Cases:

✅ Names (any language!)
✅ Email addresses, URLs
✅ Product descriptions
✅ Comments, reviews, posts
✅ JSON strings (store small JSON)

✅

BOOLEAN

Purpose: True or False!

Use Cases:

Is email verified?
Is order completed?
Is user active?
Agreed to terms?

            is_verified BOOLEAN
            true / false
                                

📅

DATE

Purpose: Calendar dates (no time)

Format: YYYY-MM-DD

Use Cases:

Birthdays
Publication dates
Event dates

            birthday DATE
            '2023-12-25'
                                

⏰

TIMESTAMP

Purpose: Date AND exact time!

Use Cases:

Order placed time
Last login
Message sent time

            created_at TIMESTAMP
            '2023-12-25 14:30:00'
                                

🔢 Numeric Data Types

🔢

INT

INT (32-bit)

Purpose: Whole numbers (no decimals)

Range: -2,147,483,648 to 2,147,483,647

Storage: 4 bytes (32 bits)

📦 How Cassandra Stores It:

Binary format: 4 bytes
Example: 42 → 0x0000002A (hex)
Example: -100 → 0xFFFFFF9C (2's complement)

Real Examples:

            -- Ages
            age INT
            25, 30, 45

            -- Product quantities
            stock_quantity INT
            150   -- 150 units in stock
            0     -- Out of stock
            -5    -- 5 units backordered

            -- Page numbers
            page_count INT
            336   -- Book has 336 pages

            -- Score/Points
            game_score INT
            9500  -- Player score

            -- Year
            birth_year INT
            1995  -- Year born
                                

Use Cases:

✅ Age, year, count
✅ Quantity, stock levels
✅ Scores, points, ratings
✅ Sequential IDs (small scale)
❌ Money (use DECIMAL!)
❌ Very large numbers (use BIGINT)

💰

DECIMAL

DECIMAL (Variable Precision)

Purpose: Exact decimals - PERFECT for money!

Precision: Arbitrary precision (no rounding errors)

Storage: Variable (depends on precision)

📦 How Cassandra Stores It:

BigDecimal format: [scale][unscaled value]
Example: 19.99 → scale=2, value=1999
Stored as: 0x00 0x02 0x07 0xCF
NO rounding - 100% exact! ✅

Real Money Examples:

            -- Product prices
            price DECIMAL
            19.99    -- Book price
            999.95   -- Laptop price
            0.99     -- App price

            -- Salary/Payments
            salary DECIMAL
            75000.00  -- Annual salary
            3250.50   -- Monthly payment

            -- Tax calculations
            tax_rate DECIMAL
            0.075     -- 7.5% tax rate
            0.0825    -- 8.25% sales tax

            -- Interest rates
            interest_rate DECIMAL
            3.50      -- 3.5% APR

            -- Exact calculations work!
            0.1 + 0.2 = 0.3  -- ✅ EXACT!
                                

Use Cases:

✅ 💵 Money, prices, salaries
✅ Tax calculations
✅ Financial transactions
✅ Accounting, invoices
✅ Exact measurements
⭐ Anytime precision matters!

⚠️ NEVER use FLOAT for money!
FLOAT: 0.1 + 0.2 = 0.30000000000000004 ❌

📊

BIGINT

Purpose: VERY large numbers

Use Cases:

Timestamps (ms)
Global counters
Large IDs

            timestamp_ms BIGINT
            1704110400000
                                

🎯

FLOAT

Purpose: Approximate decimals

Warning: May have rounding!

Use Cases:

GPS coordinates
Temperature
Scientific data

            latitude FLOAT
            37.7749
                                

Money: NEVER use FLOAT!

❌ WRONG:

            price FLOAT  -- BAD! Has rounding errors
            19.99 becomes 19.989999...
                            

✅ CORRECT:

            price DECIMAL  -- GOOD! Exact precision
            19.99 stays exactly 19.99
                            

🆔 UUID Types

🔑

UUID

UUID (Version 4)

Purpose: Globally unique random identifier

Format: xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (36 chars)

Storage: 16 bytes (128 bits)

Uniqueness: 340 undecillion possible values!

📦 How Cassandra Stores It:

Binary: 128-bit (16 bytes) number
Display: 550e8400-e29b-41d4-a716-446655440000
Internal: [55 0E 84 00 E2 9B 41 D4 A7 16 44 66 55 44 00 00]

Real Examples:

            -- Generate new UUID
            INSERT INTO users (user_id, name)
            VALUES (uuid(), 'Sarah');

            -- Example UUIDs generated:
            550e8400-e29b-41d4-a716-446655440000
            7f8a9b1c-2d3e-4f5a-6b7c-8d9e0f1a2b3c
            a1b2c3d4-e5f6-4789-a1b2-c3d4e5f67890

            -- Use in queries
            SELECT * FROM users
            WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

            -- Table with UUID PK
            CREATE TABLE orders (
                order_id UUID PRIMARY KEY,
                user_id UUID,
                product_id UUID,
                total DECIMAL
            );
                                

Use Cases:

✅ Primary keys (distributed systems!)
✅ User IDs
✅ Order IDs, Transaction IDs
✅ Session IDs, API keys
✅ Any globally unique identifier

✨ Why UUID is amazing:
• Generate IDs on any node - no coordination!
• Collision probability: 1 in 340 undecillion 🤯
• Perfect for distributed systems

⏰

TIMEUUID

TIMEUUID (Version 1)

Purpose: UUID with embedded timestamp!

Benefit: Automatically sortable by creation time

Storage: 16 bytes (128 bits) - same as UUID

Magic: Extract timestamp from the ID itself!

📦 How Cassandra Stores It:

Structure: [timestamp][clock_seq][node_id]
Example: d2177dd0-eaa2-11de-a572-001b779c76e3
• First 60 bits: timestamp (100-nanosecond intervals)
• Sorted chronologically automatically! 🎯

Real Examples:

            -- Generate TIMEUUID (now)
            INSERT INTO messages (id, text, sender)
            VALUES (now(), 'Hello!', 'Sarah');

            -- Messages automatically sorted by time!
            SELECT * FROM messages
            ORDER BY id DESC  -- Newest first!

            -- Extract timestamp from TIMEUUID
            SELECT id, dateOf(id), text
            FROM messages;
            -- Returns: 2024-01-15 10:30:25

            -- Query by time range
            SELECT * FROM events
            WHERE event_id > minTimeuuid('2024-01-01')
            AND event_id < maxTimeuuid('2024-01-31');

            -- Chat app example
            CREATE TABLE chat_messages (
                room_id UUID,
                message_id TIMEUUID,
                sender TEXT,
                content TEXT,
                PRIMARY KEY (room_id, message_id)
            ) WITH CLUSTERING ORDER BY (message_id DESC);
            -- Auto-sorted by time! 🎉
                                

Use Cases:

✅ Chat messages (sorted by time)
✅ Event logs, activity streams
✅ Time-series data
✅ Audit trails
✅ IoT sensor readings
⭐ Anything needing time + uniqueness!

🌟 TIMEUUID Benefits:
• Unique ID + Timestamp in one! 2-in-1!
• Automatic chronological sorting
• No separate created_at column needed
• Perfect for time-series data

📦 Collection Types

📋

LIST

LIST<type>

Purpose: Ordered, allows duplicates

Use Cases:

Tags list
Shopping cart items
Recent searches

            tags LIST<TEXT>
            ['fantasy', 'adventure']
                                

🎯

SET

SET<type>

Purpose: Unique values only

Use Cases:

Unique categories
Skills
Interests

            skills SET<TEXT>
            {'java', 'python'}
                                

🗺️

MAP

MAP<key,value>

Purpose: Key-value pairs

Use Cases:

User preferences
Metadata
Attributes

            prefs MAP<TEXT,TEXT>
            {'theme': 'dark'}
                                

🖥️ Interactive Console

CQL Data Types Playground

Ready! Try the examples or write your own CREATE TABLE...

🔄 How to Choose the Right Data Type

Follow this decision tree to pick the perfect type every time!

💼 Interview Questions

1 When should you use DECIMAL vs FLOAT? ▼

Answer:

Use DECIMAL when:

💰 Storing money (prices, salaries, taxes)
Exact precision required
No rounding errors acceptable
Financial calculations

Use FLOAT when:

Scientific calculations (acceptable approximation)
GPS coordinates
Temperature readings
Statistical data where tiny errors OK

Key Insight: FLOAT can have rounding errors like 19.99 becoming 19.989999. DECIMAL is EXACT - perfect for money!

2 UUID vs TIMEUUID - when to use which? ▼

Answer:

Use UUID when:

Just need unique ID
No time-based sorting needed
User IDs, Product IDs
Session tokens

Use TIMEUUID when:

Need automatic time-based sorting
Messages, Events, Logs
Want to extract timestamp from ID
Time-series data

Example: Chat app messages use TIMEUUID so messages automatically sort by time!

3 What's the difference between LIST and SET? ▼

Answer:

LIST:

✅ Ordered (maintains insertion order)
✅ Allows duplicates
Use for: Shopping cart, recent searches, to-do items

SET:

❌ Unordered
❌ NO duplicates (automatically removes)
Use for: Unique tags, skills, categories

            -- LIST allows duplicates
            ['apple', 'apple', 'banana']  -- Valid!

            -- SET removes duplicates
            {'apple', 'apple', 'banana'}
            -- Becomes: {'apple', 'banana'}
                                    

4 Can you change a column's data type after creation? ▼

Answer: NO! (with rare exceptions)

Why not?

Data already stored in old format
Converting could lose data or cause errors
Cassandra prioritizes data safety

Solution if you MUST change:

Add new column with correct type
Migrate data (write script to copy/convert)
Update application to use new column
Drop old column (optional)

            -- Add new column
            ALTER TABLE products
            ADD price_new DECIMAL;

            -- Migrate data
            UPDATE products
            SET price_new = price;
                                    

Lesson: Choose types carefully upfront! 🎯

5 What's a COUNTER and how is it special? ▼

Answer:

COUNTER is a special type that can ONLY be incremented or decremented - you cannot set it directly!

Special Rules:

❌ Cannot use INSERT (must use UPDATE)
✅ Can only do += or -= operations
Perfect for: page views, likes, downloads

            -- Create counter table
            CREATE TABLE page_stats (
                page_id TEXT PRIMARY KEY,
                view_count COUNTER
            );

            -- ❌ WRONG - Cannot INSERT
            INSERT INTO page_stats (page_id, view_count)
            VALUES ('home', 0);  -- ERROR!

            -- ✅ CORRECT - Use UPDATE
            UPDATE page_stats
            SET view_count = view_count + 1
            WHERE page_id = 'home';
                                    

Why? COUNTER is distributed across nodes. Using += ensures consistency!

📊 Data Type Comparison Matrix

Data Type	Storage Size	Range/Precision	Best For	Avoid For
TEXT	Variable (UTF-8)	Up to 2GB	Names, descriptions	Numbers, dates
INT	4 bytes	-2B to +2B	Quantities, ages	Money, huge numbers
DECIMAL	Variable	Arbitrary precision	💰 Money, finance	Approximations OK
UUID	16 bytes	128-bit unique	Primary keys, IDs	Time-sorted data
TIMEUUID	16 bytes	Timestamp + unique	Messages, logs	Random IDs

🎓 Chapter Summary

You now understand CQL data types!

Simple Types: TEXT, INT, BOOLEAN, DATE, TIMESTAMP
Numeric: INT, BIGINT, DECIMAL (money!), FLOAT, DOUBLE
UUID: Random IDs, TIMEUUID for time-sorted IDs
Collections: LIST (ordered), SET (unique), MAP (key-value)

🎯 Golden Rule: Use the RIGHT type for the RIGHT data!

🏢 Real-World Production Examples

🎵 Spotify: Music Duration Storage Crisis

The Problem: Stored song duration as TEXT: "3:45", "4:12"

Issues:

Can't calculate total playlist duration
Can't sort by length
Can't find songs > 4 minutes

The Fix:

                duration_seconds INT  -- Store as 225 seconds instead of "3:45"
                -- Now can do: WHERE duration_seconds > 240
                            

Result: Playlist calculations work! Sorting perfect! Users happy! 🎉

🛒 Shopify: The FLOAT Price Disaster

The Problem: Used FLOAT for product prices

                price FLOAT  -- WRONG!
                19.99 stored as 19.989999771118164
                            

Impact: Customers charged $19.99, database showed $19.98 → Accounting nightmare!

The Fix:

                price DECIMAL  -- CORRECT!
                19.99 stays exactly 19.99
                            

Result: Accounting matched perfectly. CFO stopped panicking! 💰

Responsive Ad