Advanced CQL Features

User-Defined Types (UDT)

Create custom complex data structures! Bundle related fields together like building LEGO blocks for your data model.

📖 The Story: Mike's Address Nightmare

Mike is building a user management system. Every user has an address with street, city, state, and zip code. He tried storing it THREE different ways...

❌ Attempt 1: Separate Columns (Messy!)

                CREATE TABLE users_v1 (
                    user_id UUID PRIMARY KEY,
                    name TEXT,
                    street TEXT,         -- Home address street
                    city TEXT,           -- Home address city
                    state TEXT,          -- Home address state
                    zip TEXT,            -- Home address zip
                    work_street TEXT,    -- Work address street
                    work_city TEXT,      -- Work address city
                    work_state TEXT,     -- Work address state
                    work_zip TEXT        -- Work address zip
                );
                                    

Problems:

🤯 16 columns for just 2 addresses! What if we add shipping address? 24 columns!
📝 Updating address requires 4 UPDATE statements
🐛 Easy to miss a field (forgot to update zip code!)
❌ Can't validate complete address (partial data corruption)

⚠️ Attempt 2: JSON String (Hacky!)

                CREATE TABLE users_v2 (
                    user_id UUID PRIMARY KEY,
                    name TEXT,
                    address_json TEXT  -- '{"street":"123 Main","city":"NYC"...}'
                );
                                    

Problems:

🔍 Can't query by city (it's inside JSON string!)
❌ No type validation (stored "12345" for city name!)
🐌 Must parse JSON every read (slow!)
💥 JSON parsing errors at runtime

✅ The RIGHT Way: User-Defined Types!

                -- Step 1: Define the custom type ONCE
                CREATE TYPE address (
                    street TEXT,
                    city TEXT,
                    state TEXT,
                    zip TEXT
                );

                -- Step 2: Use it everywhere!
                CREATE TABLE users (
                    user_id UUID PRIMARY KEY,
                    name TEXT,
                    home_address FROZEN<address>,
                    work_address FROZEN<address>,
                    shipping_address FROZEN<address>
                );

                -- Clean! Reusable! Type-safe!
                INSERT INTO users (user_id, name, home_address)
                VALUES (
                    uuid(),
                    'Mike Johnson',
                    {street: '123 Main St', city: 'NYC', state: 'NY', zip: '10001'}
                );
                                    

Benefits:

✅ Clean schema: 3 columns instead of 12!
✅ Reusable: Define address ONCE, use everywhere
✅ Type-safe: Cassandra validates each field
✅ Atomic updates: Update entire address in one operation
✅ Easy to add: Need billing address? Just add one column!

Mike's code is now clean, maintainable, and production-ready! 🎉

🎭 What are User-Defined Types?

UDTs let you create custom complex data structures by combining multiple primitive types into a single reusable unit.

Simple Definition

User-Defined Type (UDT): A custom data structure that groups related fields together, like a mini-table or struct.

Think of UDT as:

📦 LEGO Blocks: Build complex structures from simple pieces
🧩 Puzzle Pieces: Snap related fields together
🏗️ Building Blocks: Create reusable components
📚 Templates: Define structure once, use many times

When to Use UDT

✅

Perfect For

Grouped Data: Address, phone number, geolocation
Repeating Structures: Multiple addresses per user
Complex Objects: Payment info, ratings, coordinates
Atomic Updates: Update all fields together
Cleaner Schema: Reduce column count

❌

Avoid For

Frequently Queried Fields: Can't index UDT fields directly
Partial Updates: Must replace entire UDT
Large Data: Keep UDTs small (< 1KB typical)
High Mutation Rate: Whole UDT must be rewritten
Query Filters: Can't WHERE on UDT subfields

📝 UDT Syntax & Usage

Master the complete UDT lifecycle: CREATE, USE, ALTER, DROP.

Creating a UDT

-- Basic Syntax
CREATE TYPE [keyspace_name.]type_name (
    field1 data_type,
    field2 data_type,
    ...
);

-- Real Example: Address Type
CREATE TYPE address (
    street TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    country TEXT
);

-- Example: Phone Number Type
CREATE TYPE phone_number (
    country_code TEXT,
    area_code TEXT,
    number TEXT,
    extension TEXT
);
        

Using UDT in Tables

-- Single UDT Column
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT,
    address FROZEN<address>  -- FROZEN is required!
);

-- Multiple UDT Columns
CREATE TABLE contacts (
    contact_id UUID PRIMARY KEY,
    name TEXT,
    home_address FROZEN<address>,
    work_address FROZEN<address>,
    mobile FROZEN<phone_number>
);
        

Why FROZEN?

FROZEN means the UDT is treated as a single BLOB - you cannot update individual fields.

What FROZEN Does:

Entire UDT stored as one unit (blob)
Cannot update single field (must replace whole UDT)
Better performance (single read/write)
Simpler consistency model

-- ❌ WRONG: Can't update just city
UPDATE users
SET address.city = 'Boston'  -- ERROR!
WHERE user_id = ...;

-- ✅ CORRECT: Replace entire address
UPDATE users
SET address = {
    street: '456 Oak Ave',
    city: 'Boston',
    state: 'MA',
    zip: '02101'
}
WHERE user_id = ...;
            

Inserting Data with UDT

-- Insert with UDT using curly braces {}
INSERT INTO users (user_id, name, address)
VALUES (
    uuid(),
    'Sarah Johnson',
    {
        street: '123 Main Street',
        city: 'New York',
        state: 'NY',
        zip: '10001',
        country: 'USA'
    }
);

-- NULL UDT field
INSERT INTO users (user_id, name, address)
VALUES (uuid(), 'Jane Smith', null);

-- Partial UDT fields (missing fields become null)
INSERT INTO users (user_id, name, address)
VALUES (
    uuid(),
    'Bob Wilson',
    {city: 'LA', state: 'CA'}  -- street, zip, country are null
);
        

Querying UDT Data

-- Select entire UDT
SELECT user_id, name, address
FROM users;

-- Access specific UDT field
SELECT name, address.city, address.state
FROM users;

-- CANNOT filter by UDT subfield (limitation!)
SELECT * FROM users
WHERE address.city = 'NYC';  -- ERROR!
        

🏗️ Real-World UDT Examples

Production-ready UDT patterns from real applications!

Example 1: E-Commerce Product Catalog

-- Define dimensions and weight
CREATE TYPE dimensions (
    length DECIMAL,
    width DECIMAL,
    height DECIMAL,
    unit TEXT  -- 'cm' or 'inch'
);

CREATE TYPE weight (
    value DECIMAL,
    unit TEXT  -- 'kg' or 'lb'
);

-- Use in products table
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    price DECIMAL,
    size FROZEN<dimensions>,
    weight FROZEN<weight>
);

-- Insert product
INSERT INTO products (product_id, name, price, size, weight)
VALUES (
    uuid(),
    'MacBook Pro 16"',
    2499.99,
    {length: 35.79, width: 24.59, height: 1.62, unit: 'cm'},
    {value: 2.0, unit: 'kg'}
);
                

Example 2: Social Media User Profile

-- Profile picture metadata
CREATE TYPE image_metadata (
    url TEXT,
    width INT,
    height INT,
    format TEXT,  -- 'jpeg', 'png'
    size_bytes BIGINT
);

-- Social links
CREATE TYPE social_links (
    twitter TEXT,
    linkedin TEXT,
    github TEXT,
    website TEXT
);

CREATE TABLE user_profiles (
    user_id UUID PRIMARY KEY,
    username TEXT,
    bio TEXT,
    profile_pic FROZEN<image_metadata>,
    cover_photo FROZEN<image_metadata>,
    social FROZEN<social_links>
);
                

Example 3: IoT Sensor Data

-- GPS coordinates
CREATE TYPE gps_location (
    latitude DECIMAL,
    longitude DECIMAL,
    altitude DECIMAL,
    accuracy DECIMAL  -- meters
);

-- Sensor reading
CREATE TYPE sensor_reading (
    temperature DECIMAL,
    humidity DECIMAL,
    pressure DECIMAL,
    battery_level INT  -- percentage
);

CREATE TABLE sensor_data (
    sensor_id UUID,
    timestamp TIMESTAMP,
    location FROZEN<gps_location>,
    readings FROZEN<sensor_reading>,
    PRIMARY KEY (sensor_id, timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);
                

Example 4: Payment System

-- Credit card info (encrypted in real production!)
CREATE TYPE payment_card (
    card_type TEXT,       -- 'visa', 'mastercard'
    last_four TEXT,       -- Last 4 digits
    expiry_month INT,
    expiry_year INT,
    cardholder_name TEXT
);

-- Billing address
CREATE TYPE billing_info (
    address FROZEN<address>,  -- Nested UDT!
    phone FROZEN<phone_number>
);

CREATE TABLE payment_methods (
    user_id UUID,
    method_id UUID,
    card FROZEN<payment_card>,
    billing FROZEN<billing_info>,
    is_default BOOLEAN,
    PRIMARY KEY (user_id, method_id)
);
                

🔗 Nested User-Defined Types

UDTs can contain other UDTs! Build complex hierarchical structures.

                    -- Step 1: Create base types
                    CREATE TYPE phone_number (
                        country_code TEXT,
                        area_code TEXT,
                        number TEXT
                    );

                    CREATE TYPE address (
                        street TEXT,
                        city TEXT,
                        state TEXT,
                        zip TEXT
                    );

                    -- Step 2: Create composite type using base types
                    CREATE TYPE contact_info (
                        email TEXT,
                        phone FROZEN<phone_number>,  -- Nested UDT!
                        address FROZEN<address>        -- Nested UDT!
                    );

                    -- Step 3: Create top-level type
                    CREATE TYPE person (
                        name TEXT,
                        contact FROZEN<contact_info>  -- Contains nested UDTs!
                    );

                    -- Step 4: Use in table
                    CREATE TABLE employees (
                        emp_id UUID PRIMARY KEY,
                        employee FROZEN<person>,
                        department TEXT
                    );

                    -- Insert with nested UDT
                    INSERT INTO employees (emp_id, employee, department)
                    VALUES (
                        uuid(),
                        {
                            name: 'Sarah Johnson',
                            contact: {
                                email: 'sarah@company.com',
                                phone: {country_code: '+1', area_code: '415', number: '555-1234'},
                                address: {street: '123 Tech Blvd', city: 'San Francisco', state: 'CA', zip: '94105'}
                            }
                        },
                        'Engineering'
                    );

                    -- Query nested UDT fields
                    SELECT 
                        employee.name,
                        employee.contact.email,
                        employee.contact.phone.area_code,
                        employee.contact.address.city
                    FROM employees;
                        

Nested UDT Best Practices

Limit Nesting Depth: Max 2-3 levels deep (readability!)
Create Base Types First: Build from bottom-up
Always FROZEN: All nested UDTs must be FROZEN
Document Structure: Complex hierarchies need good docs
Consider Performance: Deep nesting = larger blobs

🖥️ Interactive UDT Console

Practice UDT commands in our safe simulator!

CQL User-Defined Types Playground

Enter CQL Command:

🚀 UDT Simulator Ready!
Try the examples or create your own UDT...

Available Examples:
• Example 1: Create address type
• Example 2: Use UDT in table
• Example 3: Nested UDT

⭐ UDT Best Practices & Common Mistakes

Production-proven strategies and pitfalls to avoid!

✅

DO's

Keep UDTs Small: < 1KB typical, max 1MB
Group Related Data: Address, contact info, coordinates
Always Use FROZEN: Required for UDT columns
Reuse Types: Define once, use everywhere
Name Clearly: address_type vs addr (be descriptive!)
Document Schema: Especially for nested UDTs
Version Types: Consider address_v2 for breaking changes

❌

DON'Ts

Don't Query by Subfields: Can't WHERE on UDT.field
Don't Partial Update: Must replace entire UDT
Don't Overuse: Not every column needs UDT
Don't Make Huge: Large UDTs = slow reads/writes
Don't Nest Too Deep: Max 2-3 levels
Don't Skip FROZEN: Non-frozen UDTs fail
Don't Index UDTs: Can't create secondary index

💡

Pro Tips

Denormalize for Queries: If you need to filter by city, add city column
Use Collections: LIST<FROZEN<udt>> for multiple
Null Individual Fields: Missing fields become null
Monitor Size: Track UDT size in production
Plan for Evolution: How will you handle schema changes?
Test Performance: Large UDTs impact throughput
Consider Alternatives: Sometimes separate tables better

⚠️ Common Mistake: Trying to Query by UDT Subfield

The Problem: Developer tries to find all users in NYC...

-- ❌ This does NOT work!
SELECT * FROM users
WHERE address.city = 'NYC';

-- ERROR: Cannot filter by UDT subfield!
            

The Fix:

-- ✅ Option 1: Denormalize - add city column
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    city TEXT,  -- Denormalized for querying
    address FROZEN<address>
);

SELECT * FROM users
WHERE city = 'NYC';  -- Now it works!

-- ✅ Option 2: Create separate table for querying
CREATE TABLE users_by_city (
    city TEXT,
    user_id UUID,
    name TEXT,
    address FROZEN<address>,
    PRIMARY KEY (city, user_id)
);
            

💼 Interview Questions & Expert Answers

Ace your Cassandra interview with these UDT questions!

1 What is the difference between a UDT and a Collection? ▼

Answer:

UDTs and Collections serve different purposes:

User-Defined Type (UDT):

Purpose: Group different data types together
Structure: Named fields with specific types
Example: address {street: TEXT, city: TEXT, zip: TEXT}
Use Case: Composite data that belongs together

Collection (LIST/SET/MAP):

Purpose: Store multiple values of same type
Structure: Homogeneous elements
Example: LIST<TEXT> → ['tag1', 'tag2', 'tag3']
Use Case: Multiple similar items

Combined: You can have LIST<FROZEN<address>> - multiple addresses!

2 Why is FROZEN required for UDTs? ▼

Answer:

FROZEN treats the entire UDT as a single immutable blob, which simplifies Cassandra's internals.

Why FROZEN is Required:

Consistency: Entire UDT is atomic - all fields update together
Performance: Single read/write operation (not field-by-field)
Indexing: UDT stored as one blob, easier to handle internally
Comparison: Can compare entire UDT for equality

Trade-off:

Cannot update individual fields - must replace entire UDT:

-- ❌ Can't do this:
UPDATE users SET address.city = 'Boston'...

-- ✅ Must do this:
UPDATE users SET address = {street:..., city: 'Boston', ...}...
                    

3 Can you query/filter by UDT subfields? ▼

Answer: NO - This is a major UDT limitation!

You CANNOT do this:

SELECT * FROM users
WHERE address.city = 'NYC';  -- ERROR!
                    

Why Not:

UDT is stored as single blob (FROZEN)
Cassandra doesn't index individual UDT fields
Would require deserializing every row (super slow!)

Workarounds:

Denormalize: Add city as separate column
Separate Table: Create users_by_city table
Application Filter: Fetch all, filter in app (small datasets only)

4 How do you ALTER a UDT? Can you add/remove fields? ▼

Answer: You can ADD fields, but cannot REMOVE or RENAME!

Adding a Field (Allowed):

-- Add new field to existing UDT
ALTER TYPE address
ADD country TEXT;

-- Old data: new field is null
-- New data: can include country
                    

Cannot Remove Fields:

-- ❌ This does NOT work:
ALTER TYPE address DROP zip;  -- ERROR!
                    

Cannot Rename Fields:

-- ❌ Cannot rename zip to postal_code
ALTER TYPE address RENAME zip TO postal_code;  -- ERROR!
                    

Workaround for Breaking Changes:

Create new UDT (address_v2)
Create new table using address_v2
Migrate data from old table to new
Drop old table and UDT

5 What happens to existing data when you add a field to a UDT? ▼

Answer: Existing data remains valid - new field is NULL for old rows!

Scenario:

-- Original UDT
CREATE TYPE address (
    street TEXT,
    city TEXT,
    zip TEXT
);

-- Insert old data
INSERT INTO users (...) VALUES (
    ...,
    {street: '123 Main', city: 'NYC', zip: '10001'}
);

-- Later: Add country field
ALTER TYPE address ADD country TEXT;
                    

What Happens:

✅ Old data still works perfectly
✅ Queries on old data return NULL for country
✅ New inserts can include country field
✅ You can UPDATE old rows to add country

-- Query old data
SELECT address FROM users;
-- Returns: {street:'123 Main', city:'NYC', zip:'10001', country:null}

-- Update old data to add country
UPDATE users
SET address = {
    street: '123 Main',
    city: 'NYC',
    zip: '10001',
    country: 'USA'  -- Now included!
}
WHERE user_id = ...;
                    

Best Practice: Plan for evolution - add optional fields, never remove!

🎓 Chapter Summary: UDT Mastery

Congratulations! You now understand User-Defined Types at a production level!

Key Concepts Mastered:

UDT Definition: Custom data structures grouping related fields
FROZEN Requirement: UDTs must be FROZEN (atomic updates)
Nested UDTs: UDTs can contain other UDTs (max 2-3 levels)
Collections: LIST/SET/MAP can contain UDTs
Limitations: Cannot query by subfields, cannot partial update

The Golden Rule:

UDT = Grouping Related Data Together
Keep UDTs small, reusable, and always FROZEN!

When to Use UDT:

✅ Address: street, city, state, zip together
✅ Contact Info: phone, email, social links
✅ Coordinates: latitude, longitude, altitude
✅ Metadata: width, height, format, size
❌ Don't use for: Fields you need to query/filter by

🚀 You're now equipped to design clean, maintainable schemas with UDT!

Responsive Ad