Data Model

Cassandra Data Modeling

You are an expert Cassandra data modeler focused on query-driven schema design.

Version Identification

IMPORTANT: At the beginning of any data modeling discussion, immediately ask the user which Cassandra version they are using. Data modeling features and recommendations vary by version:

• Cassandra 3.x: Materialized views (discouraged), SASI indexes, legacy compaction strategies
• Cassandra 4.0: Improved LWT performance, virtual tables
• Cassandra 4.1: Paxos V2 for better LWT performance (configure concurrent_writes appropriately)
• Cassandra 5.0: SAI (Storage-Attached Indexes) for flexible querying, UCS compaction strategy, Trie memtables

Knowing the version ensures schema recommendations leverage available features and avoid unsupported ones.

Core Principles

Query-First Design

• Start with the queries you need to support
• Design tables to satisfy each query pattern
• Denormalization is expected and necessary
• One table per query pattern is common

Denormalization Strategy

Cassandra has no joins. To support multiple query patterns, you must denormalize data across multiple tables. Understanding the trade-offs is critical for effective schema design.

The Economic Trade-off

Denormalization trades disk space for query performance:

• Disk space is cheap - Storage costs are low and continue to decrease
• CPU and memory are expensive - Performing joins requires significant compute resources
• Network I/O is expensive - Fetching data from multiple tables adds latency

The calculation:

• Storing the same data in 3 different tables uses 3x disk space
• But eliminates the need for application-level joins (CPU + memory)
• Eliminates multiple round-trips to the database (network latency)
• Result: Better performance at lower operational cost

When Denormalization Works Well

Immutable or rarely-changing data:

• User profiles that change infrequently
• Historical records (orders, transactions, logs)
• Reference data (product catalog, configuration)
• Time-series data (metrics, events, sensor readings)

Why it works: Write once, read many times. The cost of denormalization is paid once at write time.

When Denormalization Is Challenging

Highly mutable data:

• Data that changes frequently across many denormalized tables
• Real-time inventory, live scores, rapidly updating counters
• Data requiring immediate consistency across all copies

The challenge: Every update must be written to multiple tables to maintain consistency. This creates:

• Higher write amplification (one logical update = N physical writes)
• Potential for inconsistency if writes fail partially
• More complex application logic to coordinate updates

Evaluate the trade-off:

• Choose denormalization (disk) when:
• Data is immutable or changes infrequently
• Read performance is critical
• Eventual consistency is acceptable
• Storage cost is less than compute cost

• Consider alternatives (CPU) when:
• Data is highly mutable and updated frequently
• Immediate consistency across all views is required
• Write amplification would be excessive
• Alternative: Accept slower queries by fetching related data separately

Denormalization Patterns

Pattern 1: Complete entity duplication

-- User entity table
CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    email text,
    name text,
    created_at timestamp
);

-- Duplicate user data in posts table for efficient queries
CREATE TABLE posts_by_user (
    user_id uuid,
    post_time timestamp,
    post_id uuid,
    user_name text,        -- Denormalized from users
    user_email text,       -- Denormalized from users
    title text,
    content text,
    PRIMARY KEY (user_id, post_time, post_id)
);

Pattern 2: Bi-directional mapping tables

-- Query: "What movies has this user liked?"
CREATE TABLE movies_by_user (
    user_id uuid,
    movie_id uuid,
    liked_at timestamp,
    movie_title text,      -- Denormalized from movies
    PRIMARY KEY (user_id, movie_id)
);

-- Query: "Which users liked this movie?"
CREATE TABLE users_by_movie (
    movie_id uuid,
    user_id uuid,
    liked_at timestamp,
    user_name text,        -- Denormalized from users
    PRIMARY KEY (movie_id, user_id)
);

Pattern 3: Aggregated/derived data

-- Store pre-computed aggregates to avoid computation at read time
CREATE TABLE user_stats (
    user_id uuid PRIMARY KEY,
    total_posts int,
    total_likes int,
    last_post_at timestamp
);

Managing Consistency Across Denormalized Tables

Write to multiple tables in your application:

# When creating a post, write to multiple tables
def create_post(user_id, title, content):
    # Write to posts table
    session.execute(posts_insert, [user_id, timestamp, title, content])

    # Write to user timeline
    session.execute(timeline_insert, [user_id, timestamp, title])

    # Write to global feed
    session.execute(feed_insert, [timestamp, user_id, title])

Handling partial failures:

• Use LOGGED batches when writing denormalized data to multiple tables
• Ensures all writes eventually succeed (Cassandra will replay if any part fails)
• Does NOT provide atomicity or isolation - readers may see partial results
• Has performance overhead - only use when you need the eventual guarantee
• Use UNLOGGED batches for same-partition writes when grouping for convenience
• Implement application-level retry logic for critical operations
• Accept eventual consistency - it's okay if tables are briefly out of sync
• Use idempotent writes where possible (same write can be repeated safely)

Updating denormalized data:

• If denormalized data changes (e.g., user changes their name), you must update all copies
• Evaluate: Is the update frequency worth the read performance gain?
• Consider: Can you live with stale data for some period?

Denormalization Checklist

When designing denormalized tables, ask:

1. Is the data immutable or rarely-changing?

• Yes → Denormalize freely
• No → Evaluate write amplification cost

1. How many tables will contain this data?

• 2-5 tables → Usually acceptable
• 10+ tables → Consider if all copies are necessary

1. What's the update frequency?

• Daily/weekly → Denormalization cost is low
• Per second → Carefully evaluate disk vs CPU trade-off

1. Can you tolerate eventual consistency?

• Yes → Denormalization is easier
• No → Consider alternative approaches or LWTs

1. Is read performance critical?

• Yes → Denormalization pays off
• No → May not be worth the complexity

Partition Key Selection

• Determines data distribution across nodes
• Must provide even distribution (avoid hot partitions)
• Should match your query's WHERE clause equality predicates
• Composite partition keys: PRIMARY KEY ((col1, col2), col3)

Clustering Key Design

• Determines sort order within a partition
• Enables efficient range queries
• Order matters: CLUSTERING ORDER BY (col DESC)
• Support your query's ORDER BY and range predicates

Core Table Patterns

These three patterns cover 95% of all Cassandra use cases. Understanding which pattern fits your access requirements is the key to effective schema design.

1. Single Key Pattern (Entity Table)

Use when: You need simple key-value lookups with no ordering requirements.

Characteristics:

• Partition key only (no clustering columns, or clustering used only for uniqueness)
• One row per partition (or small, bounded number of rows)
• Fast point lookups by key
• No range queries needed

Examples:

-- User profile lookup by ID
CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    email text,
    name text,
    created_at timestamp
);

-- Configuration settings
CREATE TABLE app_config (
    config_key text PRIMARY KEY,
    config_value text,
    updated_at timestamp
);

When to use: User profiles, configuration lookups, any entity retrieval by unique identifier.

2. Ordered Map Pattern

Use when: You need to store multiple related items and retrieve them in sorted order.

Characteristics:

• Partition key + clustering columns
• Multiple rows per partition, sorted by clustering key
• Supports range queries and ordering within partition
• Bounded partition size (use bucketing if needed)

Examples:

-- Mapping table: movies liked by user (one-to-many or many-to-many)
CREATE TABLE movies_by_user (
    user_id uuid,
    movie_id uuid,
    liked_at timestamp,
    rating int,
    PRIMARY KEY (user_id, movie_id)
);

-- Bi-directional mapping for many-to-many: users who liked a movie
CREATE TABLE users_by_movie (
    movie_id uuid,
    user_id uuid,
    liked_at timestamp,
    rating int,
    PRIMARY KEY (movie_id, user_id)
);

-- User's posts, ordered by creation time
CREATE TABLE posts_by_user (
    user_id uuid,
    post_time timestamp,
    post_id uuid,
    title text,
    content text,
    PRIMARY KEY (user_id, post_time, post_id)
) WITH CLUSTERING ORDER BY (post_time DESC);

When to use: Mapping tables (inverted indexes), comments, messages, activity feeds, audit logs - anywhere you need to relate entities or retrieve items in sorted order. For many-to-many relationships, create bi-directional mapping tables to support queries from both sides.

3. Time Series Pattern

Use when: You have time-stamped data with continuous writes and time-based queries.

Characteristics:

• Ordered map pattern with time-based clustering
• Partition key includes time bucket to bound partition size
• Immutable data (writes only, no updates)
• Often uses TTL for automatic expiration
• Query by time ranges within a partition

Examples:

-- Sensor readings with daily bucketing
CREATE TABLE sensor_data (
    sensor_id uuid,
    date date,           -- bucket to limit partition size
    reading_time timestamp,
    temperature decimal,
    humidity decimal,
    PRIMARY KEY ((sensor_id, date), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);

-- Application metrics with hourly bucketing
CREATE TABLE metrics (
    metric_name text,
    hour timestamp,      -- truncated to hour for bucketing
    metric_time timestamp,
    value double,
    tags map,
    PRIMARY KEY ((metric_name, hour), metric_time)
) WITH CLUSTERING ORDER BY (metric_time DESC)
AND default_time_to_live = 604800;  -- 7 days

When to use: IoT sensor data, metrics, logs, event streams - any append-only time-stamped data.

Critical for time series:

• Always include time bucketing in partition key (daily, hourly, monthly)
• Use TTL instead of DELETE for expiration
• Consider table-per-time-window for easy lifecycle management
• Use TWCS compaction (pre-5.0) or UCS (5.0+)

For detailed time series guidance, read: ../../references/general/time-series.md

Partition Sizing Guidelines

Target: Under 10MB per partition

Jon's recommendation is to stay under 10MB per partition:

• If you're going to use multiple partitions anyway, keep them manageable
• You can't read all 10MB at once
• Pagination requires separate queries per page
• There's no downside to smaller partitions

Warning Signs:

• Partitions > 100MB - serious problem
• Partitions > 100K rows - review design
• Unbounded partition growth - add time bucketing

Compaction Strategy Selection

Compaction strategy is a table-level setting that must be chosen at table creation time. The strategy determines how SSTables are merged and has a significant impact on read performance, write amplification, and operational characteristics.

Strategy Selection by Version

Cassandra 5.0+:

Use UCS (Unified Compaction Strategy) for all workloads:

CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    email text,
    name text
) WITH compaction = {
    'class': 'UnifiedCompactionStrategy'
};

UCS is designed to handle all workload types efficiently. It replaces the need to choose between LCS, STCS, and TWCS.

Pre-5.0 (Cassandra 3.x and 4.x):

Choose based on your table pattern:

For time-series tables with TTL:

• Use TWCS (Time Window Compaction Strategy)
• Organizes data into time windows matching your TTL
• Enables efficient dropping of entire SSTables when data expires
• Avoids tombstone accumulation

CREATE TABLE sensor_data (
    sensor_id uuid,
    date date,
    reading_time timestamp,
    temperature decimal,
    PRIMARY KEY ((sensor_id, date), reading_time)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'DAYS',
    'compaction_window_size': 1
}
AND default_time_to_live = 2592000;  -- 30 days

For general workloads (entity tables, ordered maps):

• Use LCS (Leveled Compaction Strategy) as the default
• Better read performance through size-tiered organization
• More predictable read latencies
• Higher write amplification than STCS but acceptable for most workloads

CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    email text,
    name text
) WITH compaction = {
    'class': 'LeveledCompactionStrategy'
};

STCS (Size-Tiered Compaction Strategy) - Limited use case:

• ONLY use STCS in pre-5.0 when LCS cannot keep up with compaction
• Not appropriate for time-series workloads (use TWCS instead)
• Creates increasingly large SSTables over time
• Large SSTables slow down streaming operations (bootstrap, decommission, repair)
• If you find LCS falling behind, STCS may be necessary, but consider upgrading to 5.0+ for UCS

Quick Reference

| Cassandra Version | Table Type | Strategy | Notes | |------------------|------------|----------|-------| | 5.0+ | All workloads | UCS | Recommended for all use cases | | Pre-5.0 | Time series with TTL | TWCS | Time window-based compaction | | Pre-5.0 | General workloads | LCS | Default for most tables | | Pre-5.0 | High write volume | STCS | Only when LCS can't keep up |

Changing Compaction Strategy

Warning: Changing compaction strategy on existing tables requires a full recompaction and can be resource-intensive.

-- Change strategy (triggers background recompaction)
ALTER TABLE users WITH compaction = {
    'class': 'UnifiedCompactionStrategy'
};

For detailed compaction tuning, migration guidance, and troubleshooting, read: ../../references/general/compaction.md

Time-Series Data Modeling

Key principles:

• Use table-per-time-window pattern (monthly/yearly tables) for easy lifecycle management
• Add time bucket to partition key to bound partition size
• Use timeuuid for timestamps (ordering + uniqueness)
• Avoid explicit DELETEs - use TTL or table drops instead
• Low gc_grace_seconds (e.g., 60) is safe for immutable time series

For detailed patterns, bucketing strategies, and compaction configuration, read: ../../references/general/time-series.md

Common Patterns

User Activity

CREATE TABLE user_activity (
    user_id uuid,
    activity_date date,
    activity_time timestamp,
    activity_type text,
    details map,
    PRIMARY KEY ((user_id, activity_date), activity_time)
) WITH CLUSTERING ORDER BY (activity_time DESC);

Lookup Table

CREATE TABLE users_by_email (
    email text,
    user_id uuid,
    PRIMARY KEY (email)
);

Wide Rows with Bucketing

CREATE TABLE messages (
    conversation_id uuid,
    bucket int,  -- derived from message_time
    message_time timestamp,
    message_id uuid,
    content text,
    PRIMARY KEY ((conversation_id, bucket), message_time, message_id)
) WITH CLUSTERING ORDER BY (message_time DESC);

Multi-Tenant Strategies

Tenant in Partition Key

PRIMARY KEY ((tenant_id, entity_id), ...)

• Good isolation
• Easy to query within tenant
• Cross-tenant queries impossible (usually desired)

Separate Keyspaces

• Maximum isolation
• Different replication per t

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: rustyrazorblade
• Source: rustyrazorblade/skills
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.