Nosql Expert

# NoSQL Expert Patterns (Cassandra & DynamoDB)

Overview

This skill provides professional mental models and design patterns for **distributed wide-column and key-value stores** (specifically Apache Cassandra and Amazon DynamoDB).

Unlike SQL (where you model data entities), or document stores (like MongoDB), these distributed systems require you to **model your queries first**.

When to Use

- **Designing for Scale**: Moving beyond simple single-node databases to distributed clusters.

- **Technology Selection**: Evaluating or using **Cassandra**, **ScyllaDB**, or **DynamoDB**.

- **Performance Tuning**: Troubleshooting "hot partitions" or high latency in existing NoSQL systems.

- **Microservices**: Implementing "database-per-service" patterns where highly optimized reads are required.

The Mental Shift: SQL vs. Distributed NoSQL

| Feature | SQL (Relational) | Distributed NoSQL (Cassandra/DynamoDB) |

| :--- | :--- | :--- |

| **Data modeling** | Model Entities + Relationships | Model **Queries** (Access Patterns) |

| **Joins** | CPU-intensive, at read time | **Pre-computed** (Denormalized) at write time |

| **Storage cost** | Expensive (minimize duplication) | Cheap (duplicate data for read speed) |

| **Consistency** | ACID (Strong) | **BASE (Eventual)** / Tunable |

| **Scalability** | Vertical (Bigger machine) | **Horizontal** (More nodes/shards) |

> **The Golden Rule:** In SQL, you design the data model to answer *any* query. In NoSQL, you design the data model to answer *specific* queries efficiently.

Core Design Patterns

1. Query-First Modeling (Access Patterns)

You typically cannot "add a query later" without migration or creating a new table/index.

**Process:**

1. **List all Entities** (User, Order, Product).

2. **List all Access Patterns** ("Get User by Email", "Get Orders by User sorted by Date").

3. **Design Table(s)** specifically to serve those patterns with a single lookup.

2. The Partition Key is King

Data is distributed across physical nodes based on the **Partition Key (PK)**.

- **Goal:** Even distribution of data and traffic.

- **Anti-Pattern:** Using a low-cardinality PK (e.g., `status="active"` or `gender="m"`) creates **Hot Partitions**, limiting throughput to a single node's capacity.

- **Best Practice:** Use high-cardinality keys (User IDs, Device IDs, Composite Keys).

3. Clustering / Sort Keys

Within a partition, data is sorted on disk by the **Clustering Key (Cassandra)** or **Sort Key (DynamoDB)**.

- This allows for efficient **Range Queries** (e.g., `WHERE user_id=X AND date > Y`).

- It effectively pre-sorts your data for specific retrieval requirements.

4. Single-Table Design (Adjacency Lists)

*Primary use: DynamoDB (but concepts apply elsewhere)*

Storing multiple entity types in one table to enable pre-joined reads.

| PK (Partition) | SK (Sort) | Data Fields... |

| :--- | :--- | :--- |

| `USER#123` | `PROFILE` | `{ name: "Ian", email: "..." }` |

| `USER#123` | `ORDER#998` | `{ total: 50.00, status: "shipped" }` |

| `USER#123` | `ORDER#999` | `{ total: 12.00, status: "pending" }` |

- **Query:** `PK="USER#123"`

- **Result:** Fetches User Profile AND all Orders in **one network request**.

5. Denormalization & Duplication

Don't be afraid to store the same data in multiple tables to serve different query patterns.

- **Table A:** `users_by_id` (PK: uuid)

- **Table B:** `users_by_email` (PK: email)

*Trade-off: You must manage data consistency across tables (often using eventual consistency or batch writes).*

Specific Guidance

Apache Cassandra / ScyllaDB

- **Primary Key Structure:** `((Partition Key), Clustering Columns)`

- **No Joins, No Aggregates:** Do not try to `JOIN` or `GROUP BY`. Pre-calculate aggregates in a separate counter table.

- **Avoid `ALLOW FILTERING`:** If you see this in production, your data model is wrong. It implies a full cluster scan.

- **Writes are Cheap:** Inserts and Updates are just appends to t