Data Model & CQL

Step 1: List Access Patterns
═══════════════════════════════════════════════════════════════
Q1: Get user by user_id
Q2: Get all posts by a user, newest first
Q3: Get all posts in a category, newest first
Q4: Get a specific post by post_id
Q5: Get all comments on a post, oldest first

Step 2: Design One Table Per Query
═══════════════════════════════════════════════════════════════
Q1 → users_by_id         (PK: user_id)
Q2 → posts_by_user       (PK: user_id, CK: created_at DESC)
Q3 → posts_by_category   (PK: category, CK: created_at DESC)
Q4 → posts_by_id         (PK: post_id)
Q5 → comments_by_post    (PK: post_id, CK: created_at ASC)

Step 3: Accept Denormalization
═══════════════════════════════════════════════════════════════
Post data exists in posts_by_user AND posts_by_category AND posts_by_id.
This is correct. Disk is cheap. Joins are impossible.
Write to all tables on insert (or use materialized views/CDC).

-- Anatomy of a Cassandra Primary Key:
-- PRIMARY KEY ((partition_key_cols), clustering_cols)

-- Simple partition key, one clustering column
CREATE TABLE posts_by_user (
  user_id    UUID,
  created_at TIMESTAMP,
  post_id    UUID,
  title      TEXT,
  body       TEXT,
  PRIMARY KEY (user_id, created_at)
  --          ^^^^^^^^  ^^^^^^^^^^
  --          partition  clustering
  --          key        column
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Composite partition key (multiple columns)
CREATE TABLE sensor_data (
  sensor_id  TEXT,
  day        DATE,
  event_time TIMESTAMP,
  reading    DOUBLE,
  PRIMARY KEY ((sensor_id, day), event_time)
  --          ^^^^^^^^^^^^^^^^^^  ^^^^^^^^^^
  --          composite partition  clustering
  --          key (both required)  column
) WITH CLUSTERING ORDER BY (event_time DESC);

-- Multiple clustering columns
CREATE TABLE messages (
  chat_id    UUID,
  bucket     INT,
  sent_at    TIMESTAMP,
  message_id UUID,
  sender_id  UUID,
  body       TEXT,
  PRIMARY KEY ((chat_id, bucket), sent_at, message_id)
  --          ^^^^^^^^^^^^^^^^^^^  ^^^^^^  ^^^^^^^^^^
  --          composite partition   CK1     CK2
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id ASC);

-- Default: clustering columns sort ASC
CREATE TABLE events (
  device_id  TEXT,
  event_time TIMESTAMP,
  event_type TEXT,
  payload    TEXT,
  PRIMARY KEY (device_id, event_time)
);
-- Stored on disk: oldest first
-- Query: SELECT * FROM events WHERE device_id = 'sensor1'
--        ORDER BY event_time ASC  ← free (matches storage)

-- Explicit DESC for "newest first" queries
CREATE TABLE events_newest_first (
  device_id  TEXT,
  event_time TIMESTAMP,
  event_type TEXT,
  payload    TEXT,
  PRIMARY KEY (device_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
-- Stored on disk: newest first
-- Query: SELECT * FROM events_newest_first 
--        WHERE device_id = 'sensor1' LIMIT 10
--        → returns 10 most recent events (single sequential read)

-- Multiple clustering columns
CREATE TABLE leaderboard (
  game_id TEXT,
  score   INT,
  user_id UUID,
  PRIMARY KEY (game_id, score, user_id)
) WITH CLUSTERING ORDER BY (score DESC, user_id ASC);
-- Sorted by score descending, then user_id ascending within same score

-- Table: PRIMARY KEY (user_id, year, month, day)

-- ✅ Valid queries (left-to-right filtering):
SELECT * FROM t WHERE user_id = 'alice' AND year = 2024;
SELECT * FROM t WHERE user_id = 'alice' AND year = 2024 AND month = 1;
SELECT * FROM t WHERE user_id = 'alice' AND year = 2024 AND month >= 6;

-- ❌ Invalid queries (skipping clustering columns):
SELECT * FROM t WHERE user_id = 'alice' AND month = 6;  -- skipped year!
SELECT * FROM t WHERE user_id = 'alice' AND day = 15;   -- skipped year, month!

-- ❌ Invalid: range on non-last filtered column
SELECT * FROM t WHERE user_id = 'alice' AND year > 2020 AND month = 6;
-- year uses range, so month cannot be filtered

-- CQL looks like SQL but behaves very differently:

-- ❌ No JOINs
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;  -- ERROR

-- ❌ WHERE must include full partition key
SELECT * FROM posts_by_user WHERE title = 'Hello';  -- ERROR
-- Must be: WHERE user_id = ? (partition key required)

-- ❌ No arbitrary ORDER BY
SELECT * FROM posts_by_user WHERE user_id = ?
  ORDER BY title;  -- ERROR (title is not a clustering column)

-- ✅ UPDATE is actually an UPSERT (no read-before-write)
UPDATE users SET name = 'Alice' WHERE user_id = 123;
-- If user 123 doesn't exist, this CREATES it!
-- There is no "row not found" error for UPDATE

-- ✅ INSERT is also an UPSERT
INSERT INTO users (user_id, name) VALUES (123, 'Alice');
-- If user 123 exists, this OVERWRITES it!
-- There is no "duplicate key" error for INSERT

-- ✅ TTL — automatic expiration
INSERT INTO sessions (session_id, user_id, data)
  VALUES (uuid(), 123, 'session_data')
  USING TTL 3600;  -- expires in 1 hour

-- ✅ Lightweight Transaction (conditional write)
INSERT INTO users (user_id, name)
  VALUES (123, 'Alice')
  IF NOT EXISTS;  -- only inserts if truly new (uses Paxos)

-- Problem: All events for a device in one partition → unbounded growth
-- Solution: Bucket by day (or hour for high-volume)

CREATE TABLE sensor_readings (
  sensor_id  TEXT,
  day        DATE,          -- bucketing column in partition key
  event_time TIMESTAMP,
  value      DOUBLE,
  PRIMARY KEY ((sensor_id, day), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

-- Query today's readings:
SELECT * FROM sensor_readings
  WHERE sensor_id = 'temp-001' AND day = '2024-01-15'
  LIMIT 100;

-- Query a range within a day:
SELECT * FROM sensor_readings
  WHERE sensor_id = 'temp-001' AND day = '2024-01-15'
  AND event_time >= '2024-01-15 10:00:00'
  AND event_time <= '2024-01-15 12:00:00';

-- Same data, different access patterns, different tables:

-- Lookup by email (login flow)
CREATE TABLE users_by_email (
  email    TEXT PRIMARY KEY,
  user_id  UUID,
  name     TEXT,
  password_hash TEXT
);

-- Lookup by user_id (profile page)
CREATE TABLE users_by_id (
  user_id  UUID PRIMARY KEY,
  email    TEXT,
  name     TEXT,
  bio      TEXT,
  avatar   TEXT
);

-- Application writes to BOTH tables on user creation:
-- BEGIN BATCH
--   INSERT INTO users_by_email ...
--   INSERT INTO users_by_id ...
-- APPLY BATCH;

-- Followers/following relationship
CREATE TABLE followers (
  user_id     UUID,
  follower_id UUID,
  followed_at TIMESTAMP,
  PRIMARY KEY (user_id, follower_id)
);

-- Get all followers of a user:
SELECT * FROM followers WHERE user_id = ?;

-- Check if specific user follows:
SELECT * FROM followers WHERE user_id = ? AND follower_id = ?;

-- Count followers (expensive — counts all rows in partition):
SELECT COUNT(*) FROM followers WHERE user_id = ?;
-- Better: maintain a counter table separately

-- Base table
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email   TEXT,
  country TEXT,
  name    TEXT
);

-- Materialized view: automatic denormalization
CREATE MATERIALIZED VIEW users_by_country AS
  SELECT * FROM users
  WHERE country IS NOT NULL AND user_id IS NOT NULL
  PRIMARY KEY (country, user_id);

-- Cassandra automatically keeps the view in sync
-- ⚠️ BUT: MVs have known bugs and performance issues
-- Many teams prefer manual denormalization with BATCH writes

-- ❌ ALLOW FILTERING — full cluster scan
SELECT * FROM users WHERE name = 'Alice' ALLOW FILTERING;
-- Reads EVERY partition, filters in memory. O(n) on cluster size.
-- Never use in production. Create a table with name as partition key.

-- ❌ Secondary index on high-cardinality column
CREATE INDEX ON users (email);
SELECT * FROM users WHERE email = 'alice@example.com';
-- Queries ALL nodes (scatter-gather). Extremely slow at scale.
-- Solution: Create users_by_email table.

-- ❌ Large IN clause
SELECT * FROM posts WHERE user_id IN (uuid1, uuid2, ..., uuid100);
-- Coordinator must query 100 different partitions and merge.
-- Solution: Query individually and merge in application.

-- ❌ Unbounded partition
CREATE TABLE events (
  device_id TEXT,
  event_time TIMESTAMP,
  data TEXT,
  PRIMARY KEY (device_id, event_time)
);
-- A device running for years → partition grows to GB+
-- Solution: Add bucketing: PRIMARY KEY ((device_id, month), event_time)

-- Conditional INSERT (create if not exists)
INSERT INTO users (user_id, email, name)
  VALUES (uuid(), 'alice@example.com', 'Alice')
  IF NOT EXISTS;
-- Returns [applied]=true if inserted, false if email already taken

-- Conditional UPDATE (compare-and-swap)
UPDATE accounts SET balance = 900
  WHERE account_id = 123
  IF balance = 1000;
-- Only updates if current balance is exactly 1000
-- Returns [applied]=true/false and current values if false

-- Conditional DELETE
DELETE FROM locks WHERE lock_id = 'resource-1'
  IF owner = 'worker-5';
-- Only deletes if current owner matches