📖 The Spotify Story: How CQL Powers Music at Scale
Context: Spotify has over 550 million users streaming billions of songs daily. They needed a query language that was:
- Easy to learn for developers coming from SQL backgrounds
- Powerful enough to handle complex data models
- Fast enough to serve millions of queries per second
The Challenge: Traditional SQL databases couldn't scale horizontally, but NoSQL databases had complex, unfamiliar query languages that made development slow.
The Solution: Cassandra's CQL provided the perfect balance - it looks and feels like SQL but is designed for distributed systems. Spotify engineers could write familiar queries like:
-- Get user's recent plays (simple and intuitive!)
SELECT track_name, played_at, duration_ms
FROM user_listening_history
WHERE user_id = 'user_12345'
ORDER BY played_at DESC
LIMIT 50;
The Result: Spotify now processes over 1.5 billion reads per day using CQL, with average query latencies under 5ms. Developers love it because it's SQL-like, and operations teams love it because it scales effortlessly.
Essential CQL Syntax
1️⃣ Creating a Keyspace (Database)
A keyspace is like a database in traditional RDBMS. It defines replication strategy and other settings:
-- Create keyspace with simple strategy (for single datacenter)
CREATE KEYSPACE IF NOT EXISTS spotify_app
WITH replication = {
'class': 'SimpleStrategy',
'replication_factor': 3
}
AND durable_writes = true;
-- Create keyspace with network topology (for multiple datacenters)
CREATE KEYSPACE IF NOT EXISTS spotify_app_prod
WITH replication = {
'class': 'NetworkTopologyStrategy',
'datacenter1': 3,
'datacenter2': 2
};
Replication Factor Best Practice
Replication Factor = 3 is recommended for production. This provides:
- Fault tolerance - can lose 1 node without data loss
- Better read performance - more replicas to serve reads
- Balance between redundancy and storage cost
2️⃣ Creating Tables
Tables define your data structure. The PRIMARY KEY is crucial - it determines data distribution and query patterns:
-- Simple table with single partition key
CREATE TABLE IF NOT EXISTS users (
user_id uuid PRIMARY KEY,
username text,
email text,
created_at timestamp,
last_login timestamp,
is_premium boolean
);
-- Table with composite partition key
CREATE TABLE IF NOT EXISTS user_playlists (
user_id uuid,
playlist_id uuid,
playlist_name text,
created_date date,
song_count int,
total_duration_ms bigint,
is_public boolean,
PRIMARY KEY (user_id, playlist_id)
);
-- Table with partition key + clustering columns
CREATE TABLE IF NOT EXISTS listening_history (
user_id uuid,
played_at timestamp,
track_id uuid,
track_name text,
artist_name text,
album_name text,
duration_ms int,
play_source text,
PRIMARY KEY (user_id, played_at)
) WITH CLUSTERING ORDER BY (played_at DESC);
-- Real example: Handling time-series data
CREATE TABLE IF NOT EXISTS sensor_readings (
sensor_id text,
reading_date date,
reading_time timestamp,
temperature decimal,
humidity decimal,
pressure decimal,
PRIMARY KEY ((sensor_id, reading_date), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);
Understanding PRIMARY KEY
PRIMARY KEY (partition_key, clustering_columns)
- Partition Key: Determines which nodes store the data (hashed to token)
- Clustering Columns: Define sort order within a partition
- Compound Partition Key: Use ((key1, key2)) to group related data together
Example: In PRIMARY KEY ((sensor_id, reading_date), reading_time):
- All readings for sensor "S001" on "2024-12-29" are stored together
- Within that partition, readings are sorted by time (descending)
- Queries can efficiently fetch all readings for a sensor on a specific day
3️⃣ Inserting Data
INSERT adds new rows. CQL supports both full and partial inserts:
-- Insert complete user record
INSERT INTO users (
user_id,
username,
email,
created_at,
last_login,
is_premium
)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
'taylor_swift_fan',
'taylor@example.com',
'2024-01-15 10:30:00',
'2024-12-29 08:15:00',
true
);
-- Insert with TTL (Time To Live) - auto-delete after 30 days
INSERT INTO session_tokens (user_id, token, created_at)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...',
toTimestamp(now())
)
USING TTL 2592000; -- 30 days in seconds
-- Insert multiple playlist entries
INSERT INTO user_playlists (user_id, playlist_id, playlist_name, created_date, song_count, total_duration_ms, is_public)
VALUES (550e8400-e29b-41d4-a716-446655440000, uuid(), 'Morning Vibes', '2024-12-01', 45, 9234567, true);
INSERT INTO user_playlists (user_id, playlist_id, playlist_name, created_date, song_count, total_duration_ms, is_public)
VALUES (550e8400-e29b-41d4-a716-446655440000, uuid(), 'Workout Mix', '2024-11-15', 32, 7123456, false);
INSERT INTO user_playlists (user_id, playlist_id, playlist_name, created_date, song_count, total_duration_ms, is_public)
VALUES (550e8400-e29b-41d4-a716-446655440000, uuid(), 'Chill Evening', '2024-10-20', 28, 6789123, true);
-- Insert listening history
INSERT INTO listening_history (user_id, played_at, track_id, track_name, artist_name, album_name, duration_ms, play_source)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
'2024-12-29 14:30:00',
uuid(),
'Anti-Hero',
'Taylor Swift',
'Midnights',
200000,
'discover_weekly'
);
INSERT INTO listening_history (user_id, played_at, track_id, track_name, artist_name, album_name, duration_ms, play_source)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
'2024-12-29 14:26:30',
uuid(),
'Lavender Haze',
'Taylor Swift',
'Midnights',
202000,
'playlist'
);
INSERT INTO listening_history (user_id, played_at, track_id, track_name, artist_name, album_name, duration_ms, play_source)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
'2024-12-29 14:23:00',
uuid(),
'Karma',
'Taylor Swift',
'Midnights',
204000,
'search'
);
4️⃣ Querying Data (SELECT)
SELECT retrieves data. Remember: CQL queries must specify the partition key!
-- Get all user info (requires full primary key)
SELECT * FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Result:
-- user_id | username | email | created_at | last_login | is_premium
-- --------------------------------------+------------------+-----------------------+-----------------------------+-----------------------------+------------
-- 550e8400-e29b-41d4-a716-446655440000 | taylor_swift_fan | taylor@example.com | 2024-01-15 10:30:00.000000 | 2024-12-29 08:15:00.000000 | True
-- Get specific columns
SELECT username, email, is_premium
FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Get user's playlists (partition key query)
SELECT playlist_name, song_count, is_public
FROM user_playlists
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Result:
-- playlist_name | song_count | is_public
-- ---------------+------------+-----------
-- Morning Vibes | 45 | True
-- Workout Mix | 32 | False
-- Chill Evening | 28 | True
-- Get recent listening history (with ORDER BY on clustering column)
SELECT played_at, track_name, artist_name, duration_ms
FROM listening_history
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
ORDER BY played_at DESC
LIMIT 10;
-- Result:
-- played_at | track_name | artist_name | duration_ms
-- ----------------------------+-----------------+--------------+-------------
-- 2024-12-29 14:30:00.000000 | Anti-Hero | Taylor Swift | 200000
-- 2024-12-29 14:26:30.000000 | Lavender Haze | Taylor Swift | 202000
-- 2024-12-29 14:23:00.000000 | Karma | Taylor Swift | 204000
-- Query with time range (using clustering column)
SELECT track_name, artist_name, played_at
FROM listening_history
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND played_at >= '2024-12-29 14:20:00'
AND played_at <= '2024-12-29 14:35:00';
-- Using ALLOW FILTERING (not recommended for production!)
SELECT username, email
FROM users
WHERE is_premium = true
ALLOW FILTERING;
-- ⚠️ This scans all partitions - very slow on large datasets!
ALLOW FILTERING - Use with Caution!
Never use ALLOW FILTERING in production! It forces Cassandra to scan all partitions, which:
- Defeats the purpose of Cassandra's distributed architecture
- Creates massive performance bottlenecks
- Can timeout on large datasets
- Instead: Design tables for your query patterns
5️⃣ Updating Data
UPDATE modifies existing rows. If the row doesn't exist, it creates it (upsert behavior):
-- Update user's last login
UPDATE users
SET last_login = toTimestamp(now())
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Update premium status
UPDATE users
SET is_premium = true
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Update playlist details
UPDATE user_playlists
SET song_count = 46, total_duration_ms = 9434567
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND playlist_id = a1b2c3d4-e5f6-7890-abcd-1234567890ab;
-- Conditional update (lightweight transaction - use sparingly!)
UPDATE users
SET is_premium = true
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
IF is_premium = false;
-- Update with TTL (data expires after specified time)
UPDATE session_tokens USING TTL 3600
SET token = 'new_token_value'
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
6️⃣ Deleting Data
DELETE removes rows or specific columns from rows:
-- Delete entire row
DELETE FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Delete specific columns
DELETE email, last_login
FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
-- Delete from composite key table
DELETE FROM user_playlists
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND playlist_id = a1b2c3d4-e5f6-7890-abcd-1234567890ab;
-- Delete with timestamp (removes data written before specific time)
DELETE FROM listening_history
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
AND played_at < '2024-01-01 00:00:00';
-- Conditional delete (lightweight transaction)
DELETE FROM users
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
IF is_premium = false;
Tombstones Warning
Deletes in Cassandra create tombstones (markers for deleted data) rather than immediately removing data. Too many tombstones can degrade performance:
- Why? Distributed systems need tombstones to propagate deletes to all replicas
- Solution: Use TTL for automatic expiration instead of manual deletes
- Compaction: Old tombstones are removed during compaction (after gc_grace_seconds)
Advanced CQL Features
🔢 Collections (List, Set, Map)
-- Create table with collections
CREATE TABLE user_preferences (
user_id uuid PRIMARY KEY,
favorite_genres set, -- SET: unique, unordered
recent_searches list, -- LIST: ordered, allows duplicates
device_settings map -- MAP: key-value pairs
);
-- Insert with collections
INSERT INTO user_preferences (user_id, favorite_genres, recent_searches, device_settings)
VALUES (
550e8400-e29b-41d4-a716-446655440000,
{'pop', 'rock', 'indie'},
['taylor swift', 'the weeknd', 'billie eilish'],
{'theme': 'dark', 'quality': 'high', 'autoplay': 'true'}
);
-- Update collections
UPDATE user_preferences
SET favorite_genres = favorite_genres + {'jazz'} -- Add to set
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
UPDATE user_preferences
SET recent_searches = ['ed sheeran'] + recent_searches -- Prepend to list
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
UPDATE user_preferences
SET device_settings['volume'] = '85' -- Update map value
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;
🔢 Counter Columns
-- Counter table for track plays
CREATE TABLE track_stats (
track_id uuid PRIMARY KEY,
play_count counter,
skip_count counter,
save_count counter
);
-- Increment counters
UPDATE track_stats
SET play_count = play_count + 1
WHERE track_id = a1b2c3d4-e5f6-7890-abcd-1234567890ab;
UPDATE track_stats
SET skip_count = skip_count + 1
WHERE track_id = a1b2c3d4-e5f6-7890-abcd-1234567890ab;
-- Read counter values
SELECT track_id, play_count, skip_count, save_count
FROM track_stats
WHERE track_id = a1b2c3d4-e5f6-7890-abcd-1234567890ab;
-- Result:
-- track_id | play_count | skip_count | save_count
-- -------------------------------------+------------+------------+------------
-- a1b2c3d4-e5f6-7890-abcd-1234567890ab | 1547 | 23 | 412
📦 Batch Operations
-- LOGGED batch (atomic within single partition)
BEGIN BATCH
INSERT INTO users (user_id, username, email, created_at, is_premium)
VALUES (uuid(), 'new_user', 'new@example.com', toTimestamp(now()), false);
INSERT INTO user_activity (user_id, activity_type, activity_time)
VALUES (uuid(), 'signup', toTimestamp(now()));
UPDATE user_stats SET total_users = total_users + 1
WHERE stat_id = 'global';
APPLY BATCH;
-- UNLOGGED batch (better performance, no atomicity guarantee)
BEGIN UNLOGGED BATCH
INSERT INTO listening_history (user_id, played_at, track_id, track_name)
VALUES (uuid(), toTimestamp(now()), uuid(), 'Song 1');
INSERT INTO listening_history (user_id, played_at, track_id, track_name)
VALUES (uuid(), toTimestamp(now()), uuid(), 'Song 2');
INSERT INTO listening_history (user_id, played_at, track_id, track_name)
VALUES (uuid(), toTimestamp(now()), uuid(), 'Song 3');
APPLY BATCH;
Batch Operations Best Practices
- Don't use for bulk loading: Batches are not for performance, they're for atomicity
- Keep batches small: Max 5-10 statements per batch
- Same partition key: Batches work best when all operations are in the same partition
- Use UNLOGGED when possible: Better performance if atomicity isn't critical
🔐 Secondary Indexes
-- Create secondary index (use carefully!)
CREATE INDEX ON users (email);
CREATE INDEX ON users (is_premium);
-- Now you can query by indexed column
SELECT username, email FROM users WHERE email = 'taylor@example.com';
SELECT username, last_login FROM users WHERE is_premium = true;
-- Drop index
DROP INDEX users_email_idx;
Secondary Index Warning
Secondary indexes have significant limitations:
- Poor performance on high-cardinality columns (many unique values)
- Queries still hit all nodes in the cluster
- Best for low-cardinality columns (few unique values)
- Better alternative: Create a dedicated table with the query pattern as primary key