Nosql Expert

1---
2name: nosql-expert
3description: "Expert guidance for distributed NoSQL databases (Cassandra, DynamoDB). Focuses on mental models, query-first modeling, single-table design, and avoiding hot partitions in high-scale systems."
4---
5 
6# NoSQL Expert Patterns (Cassandra & DynamoDB)
7 
8## Overview
9 
10This skill provides professional mental models and design patterns for **distributed wide-column and key-value stores** (specifically Apache Cassandra and Amazon DynamoDB).
11 
12Unlike SQL (where you model data entities), or document stores (like MongoDB), these distributed systems require you to **model your queries first**.
13 
14## When to Use
15 
16- **Designing for Scale**: Moving beyond simple single-node databases to distributed clusters.
17- **Technology Selection**: Evaluating or using **Cassandra**, **ScyllaDB**, or **DynamoDB**.
18- **Performance Tuning**: Troubleshooting "hot partitions" or high latency in existing NoSQL systems.
19- **Microservices**: Implementing "database-per-service" patterns where highly optimized reads are required.
20 
21## The Mental Shift: SQL vs. Distributed NoSQL
22 
23| Feature | SQL (Relational) | Distributed NoSQL (Cassandra/DynamoDB) |
24| :--- | :--- | :--- |
25| **Data modeling** | Model Entities + Relationships | Model **Queries** (Access Patterns) |
26| **Joins** | CPU-intensive, at read time | **Pre-computed** (Denormalized) at write time |
27| **Storage cost** | Expensive (minimize duplication) | Cheap (duplicate data for read speed) |
28| **Consistency** | ACID (Strong) | **BASE (Eventual)** / Tunable |
29| **Scalability** | Vertical (Bigger machine) | **Horizontal** (More nodes/shards) |
30 
31> **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.
32 
33## Core Design Patterns
34 
35### 1. Query-First Modeling (Access Patterns)
36 
37You typically cannot "add a query later" without migration or creating a new table/index.
38 
39**Process:**
401.  **List all Entities** (User, Order, Product).
412.  **List all Access Patterns** ("Get User by Email", "Get Orders by User sorted by Date").
423.  **Design Table(s)** specifically to serve those patterns with a single lookup.
43 
44### 2. The Partition Key is King
45 
46Data is distributed across physical nodes based on the **Partition Key (PK)**.
47-   **Goal:** Even distribution of data and traffic.
48-   **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.
49-   **Best Practice:** Use high-cardinality keys (User IDs, Device IDs, Composite Keys).
50 
51### 3. Clustering / Sort Keys
52 
53Within a partition, data is sorted on disk by the **Clustering Key (Cassandra)** or **Sort Key (DynamoDB)**.
54-   This allows for efficient **Range Queries** (e.g., `WHERE user_id=X AND date > Y`).
55-   It effectively pre-sorts your data for specific retrieval requirements.
56 
57### 4. Single-Table Design (Adjacency Lists)
58 
59*Primary use: DynamoDB (but concepts apply elsewhere)*
60 
61Storing multiple entity types in one table to enable pre-joined reads.
62 
63| PK (Partition) | SK (Sort) | Data Fields... |
64| :--- | :--- | :--- |
65| `USER#123` | `PROFILE` | `{ name: "Ian", email: "..." }` |
66| `USER#123` | `ORDER#998` | `{ total: 50.00, status: "shipped" }` |
67| `USER#123` | `ORDER#999` | `{ total: 12.00, status: "pending" }` |
68 
69-   **Query:** `PK="USER#123"`
70-   **Result:** Fetches User Profile AND all Orders in **one network request**.
71 
72### 5. Denormalization & Duplication
73 
74Don't be afraid to store the same data in multiple tables to serve different query patterns.
75-   **Table A:** `users_by_id` (PK: uuid)
76-   **Table B:** `users_by_email` (PK: email)
77 
78*Trade-off: You must manage data consistency across tables (often using eventual consistency or batch writes).*
79 
80## Specific Guidance
81 
82### Apache Cassandra / ScyllaDB
83 
84-   **Primary Key Structure:** `((Partition Key), Clustering Columns)`
85-   **No Joins, No Aggregates:** Do not try to `JOIN` or `GROUP BY`. Pre-calculate aggregates in a separate counter table.
86-   **Avoid `ALLOW FILTERING`:** If you see this in production, your data model is wrong. It implies a full cluster scan.
87-   **Writes are Cheap:** Inserts and Updates are just appends to the LSM tree. Don't worry about write volume as much as read efficiency.
88-   **Tombstones:** Deletes are expensive markers. Avoid high-velocity delete patterns (like queues) in standard tables.
89 
90### AWS DynamoDB
91 
92-   **GSI (Global Secondary Index):** Use GSIs to create alternative views of your data (e.g., "Search Orders by Date" instead of by User).
93    -   *Note:* GSIs are eventually consistent.
94-   **LSI (Local Secondary Index):** Sorts data differently *within* the same partition. Must be created at table creation time.
95-   **WCU / RCU:** Understand capacity modes. Single-table design helps optimize consumed capacity units.
96-   **TTL:** Use Time-To-Live attributes to automatically expire old data (free delete) without creating tombstones.
97 
98## Expert Checklist
99 
100Before finalizing your NoSQL schema:
101 
102-   [ ] **Access Pattern Coverage:** Does every query pattern map to a specific table or index?
103-   [ ] **Cardinality Check:** Does the Partition Key have enough unique values to spread traffic evenly?
104-   [ ] **Split Partition Risk:** For any single partition (e.g., a single user's orders), will it grow indefinitely? (If > 10GB, you need to "shard" the partition, e.g., `USER#123#2024-01`).
105-   [ ] **Consistency Requirement:** Can the application tolerate eventual consistency for this read pattern?
106 
107## Common Anti-Patterns
108 
109❌ **Scatter-Gather:** Querying *all* partitions to find one item (Scan).
110❌ **Hot Keys:** Putting all "Monday" data into one partition.
111❌ **Relational Modeling:** Creating `Author` and `Book` tables and trying to join them in code. (Instead, embed Book summaries in Author, or duplicate Author info in Books).
112