Section 2: Data Modeling Fundamentals

Cassandra Static Columns

Master partition-wide shared data! Learn the STATIC keyword, optimization patterns, when to use shared columns with animations, production examples, and expert design patterns.

📖 The Story: The Office Bulletin Board

Imagine an office with a bulletin board for each team...

📌 The Engineering Team Bulletin Board

📌 PINNED AT TOP (Same for everyone):
Team: Engineering
Manager: Sarah Johnson
Budget: $500,000
Location: Building A, Floor 3

→ This info is STATIC - shared by all team members!

Individual Team Member Entries:
├── Alice: Software Engineer, Joined 2023-01-15
├── Bob: DevOps Engineer, Joined 2023-03-20
├── Carol: QA Engineer, Joined 2023-06-10
└── Dave: Data Engineer, Joined 2023-09-01

Key Insight:

Pinned Info: Team name, manager, budget - SAME for all members
Individual Entries: Each person's role, join date - DIFFERENT per person
Efficiency: No need to repeat team info on every person's entry!

📌 This is Static Columns in Cassandra!

CREATE TABLE team_members (

  team_id UUID,

  employee_id UUID,

  -- STATIC columns (shared by all members):

  team_name TEXT STATIC,

  manager_name TEXT STATIC,

  team_budget DECIMAL STATIC,

  -- Regular columns (per employee):

  employee_name TEXT,

  role TEXT,

  joined_date DATE,

  PRIMARY KEY (team_id, employee_id)

);

Partition for team_id='eng-team':

┌──────────────────────────────────────────┐

│ STATIC: team_name="Engineering"  │

│ STATIC: manager="Sarah Johnson" │

│ STATIC: budget=$500,000         │

├──────────────────────────────────────────┤

│ alice: Software Engineer, 2023-01-15    │

│ bob: DevOps Engineer, 2023-03-20        │

│ carol: QA Engineer, 2023-06-10          │

│ dave: Data Engineer, 2023-09-01         │

└──────────────────────────────────────────┘

Benefits:

Team info stored ONCE per partition (not repeated per employee)
Update manager? One write updates for ALL employees
Query any employee, automatically get team info
Massive storage savings with wide partitions

📌 What Are Static Columns?

Complete Definition

Static Column: A column that is shared by ALL rows in a partition. Instead of each row having its own value, there's ONE value for the entire partition.

Key Concepts:

Partition-Wide: One value for entire partition (not per row)
Shared Data: All rows see same static value
Storage Once: Stored one time per partition (not N times)
Keyword: Declared with STATIC keyword

Visual Comparison

💻 Syntax & Declaration

Complete Schema Example

CREATE TABLE sensor_data (

  -- Partition Key:

  sensor_id UUID,

  -- Clustering Column:

  reading_time TIMESTAMP,

  -- STATIC Columns (shared by all readings in partition):

  sensor_location TEXT STATIC,

  sensor_model TEXT STATIC,

  install_date DATE STATIC,

  calibration_factor DECIMAL STATIC,

  -- Regular Columns (per reading):

  temperature DECIMAL,

  humidity DECIMAL,

  pressure DECIMAL,

  PRIMARY KEY (sensor_id, reading_time)

);

Critical Rules

Must Have Clustering Column: Static columns only work with compound primary keys
Cannot Be Partition Key: Only regular columns can be static
Cannot Be Clustering Column: Clustering columns define row uniqueness
One Value Per Partition: All rows share same static value

Query Examples

Reading Static Data

-- Get one reading (includes static):

SELECT * FROM sensor_data

WHERE sensor_id = ?

AND reading_time = ?;

Returns:

• Static: location, model, etc.

• Row: temp, humidity, pressure

Updating Static Data

-- Update static column:

UPDATE sensor_data

SET sensor_location = 'Building B'

WHERE sensor_id = ?;

Effect:

• Updates for ALL readings!

• Only specify partition key

Reading ONLY Static

-- Get just static columns:

SELECT sensor_location,

  sensor_model

FROM sensor_data

WHERE sensor_id = ?

LIMIT 1;

Returns ONE row with static data

🔬 How Static Columns Work Internally

🎯 Common Use Cases

📊

IoT Sensor Metadata

Static: Location, model, install date, calibration

Per Row: Timestamp, readings

Why: Sensor metadata rarely changes, readings constantly stream in

👥

Team/Group Info

Static: Team name, manager, budget, location

Per Row: Employee name, role, join date

Why: Team info shared by all members

📝

Blog Posts & Comments

Static: Post title, author, publish date

Per Row: Each comment with timestamp

Why: Post metadata same for all comments

🏪

Product Reviews

Static: Product name, price, category

Per Row: Each review with rating

Why: Product info shared by all reviews

⚡ Performance Benefits

Storage & Performance Gains

Example: 1 sensor with 100,000 readings

Without Static Columns

4 metadata fields × 100K rows
= 400,000 values stored
= ~8MB wasted storage
= Slower reads (more data)

With Static Columns

4 metadata fields × 1 time
= 4 values stored
= ~80 bytes storage
= 99.99% storage savings!

Additional Benefits:

Faster Queries: Less data to read from disk
Easier Updates: Change metadata once, affects all rows
Data Consistency: No risk of mismatched metadata
Network Efficiency: Less data transferred

🌍 Production Examples

Netflix: Show Episodes

CREATE TABLE show_episodes (

  show_id UUID,

  season INT,

  episode INT,

  -- STATIC (show-level info):

  show_title TEXT STATIC,

  creator TEXT STATIC,

  genre TEXT STATIC,

  -- Per episode:

  episode_title TEXT,

  duration INT,

  PRIMARY KEY (show_id, season, episode)

);

Benefit: Show metadata stored once for hundreds of episodes

Uber: Driver Trips

CREATE TABLE driver_trips (

  driver_id UUID,

  trip_time TIMESTAMP,

  -- STATIC (driver info):

  driver_name TEXT STATIC,

  rating DECIMAL STATIC,

  vehicle_model TEXT STATIC,

  -- Per trip:

  pickup_location TEXT,

  dropoff_location TEXT,

  fare DECIMAL,

  PRIMARY KEY (driver_id, trip_time)

);

Benefit: Driver info shared across thousands of trips

✅ Best Practices

DO This

Use for Metadata: Info about the partition itself
Rarely Changes: Data that's mostly static
Shared Data: Same value for all rows
Wide Partitions: More rows = more savings
Simple Updates: One write updates all

DON'T Do This

Frequently Updated: Causes write amplification
Different Per Row: Use regular columns
Skinny Partitions: Little benefit with few rows
Large Values: Keep static values small

⚠️ Anti-Patterns

❌ Frequently Updated Static Columns

Problem: Every update rewrites data for entire partition

Bad Example: user_current_balance STATIC
If balance updates every transaction, ALL transaction rows are affected

Solution: Use regular column or separate table for frequently changing data

💼 Interview Questions & Expert Answers

1 What are static columns and when should you use them?

Static columns store one value per partition (not per row), perfect for shared metadata in wide partitions.

▼

Answer: Static columns are partition-wide columns where ONE value is shared by ALL rows in a partition. Use for metadata that applies to the entire partition: sensor location for all readings, team info for all employees, product details for all reviews. Massive storage savings in wide partitions (99%+ with thousands of rows).

2 How do static columns save storage?

Store data once per partition instead of repeating it in every row, saving 99%+ storage in wide partitions.

▼

Answer: Without static: metadata repeated N times (once per row). With static: metadata stored ONCE per partition. Example: 1 sensor, 100K readings, 4 metadata fields. Without: 400K values (8MB). With: 4 values (80 bytes). 99.99% savings! More rows = exponentially better savings.

3 Can static columns be used without clustering columns?

No - static columns require compound primary keys with at least one clustering column to define rows.

▼

Answer: No. Static columns only make sense with clustering columns because they provide partition-wide values while clustering columns define individual rows. PRIMARY KEY (partition_key) has no rows to share data with. Must be PRIMARY KEY (partition_key, clustering_col) to use STATIC.

4 What happens when you update a static column?

One UPDATE statement changes the value for ALL rows in the partition automatically.

▼

Answer: UPDATE table SET static_col = value WHERE partition_key = ? updates for ALL rows in partition. Only need to specify partition key, not clustering columns. All rows immediately see new value. Efficient for infrequent updates, but frequent updates cause write amplification.

5 Give a real production example of static columns

Netflix stores show metadata (title, creator) as STATIC, with episodes as regular rows per partition.

▼

Answer: Netflix show_episodes: PRIMARY KEY (show_id, season, episode). STATIC: show_title, creator, genre (shared by all episodes). Regular: episode_title, duration (per episode). Result: Show metadata stored once for 100+ episodes per show. Similar patterns at Uber (driver info across trips), IoT platforms (sensor metadata across readings).

🎓 Chapter Summary: Static Column Mastery

You now understand Cassandra's partition-wide data sharing pattern!

Key Concepts:

Definition: One value per partition, shared by ALL rows
Keyword: Declared with STATIC
Use Case: Metadata about the partition itself
Storage: 99%+ savings in wide partitions
Updates: One write updates for ALL rows

Perfect For:

IoT sensor metadata (location, model, calibration)
Team/group information (manager, budget, location)
Product details across reviews
Show metadata across episodes

🚀 You can now optimize storage with partition-wide columns!

Responsive Ad