NoSQL Expert Patterns (Cassandra, ScyllaDB & DynamoDB)
Overview
This skill provides professional mental models and design patterns for distributed wide-column and key-value stores (Apache Cassandra, ScyllaDB, 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. You cannot "add a query later" without migration or creating a new table/index.
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.
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) |
When to Use
- Designing for Scale: Moving beyond simple single-node databases to distributed clusters.
- Technology Selection: Evaluating or using Cassandra, ScyllaDB, or DynamoDB.
- Schema Modeling: Designing tables, partition keys, sort keys, or single-table layouts.
- Performance Tuning: Troubleshooting "hot partitions", high latency, or uneven traffic in existing NoSQL systems.
- Microservices: Implementing "database-per-service" patterns where highly optimized reads are required.
Prerequisites
- Target database identified (Cassandra/ScyllaDB or DynamoDB).
- A complete list of required access patterns (queries) before table design.
- Understanding of expected read/write volume and cardinality of candidate partition keys.
- If running locally on Windows (PowerShell), ensure
cqlsh or AWS CLI v2 is installed and configured with placeholder credentials (YOUR_KEY).
Procedure
1. Query-First Modeling (Access Patterns)
You typically cannot "add a query later" without migration or creating a new table/index.
- List all Entities (User, Order, Product).
- List all Access Patterns ("Get User by Email", "Get Orders by User sorted by Date").
- Design Table(s) specifically to serve those patterns with a single lookup.
- Validate that every access pattern maps to exactly one table or index.
2. Choose the Partition Key (PK)
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).
- Split Partition Risk: For any single partition (e.g., a single user's orders), will it grow indefinitely? If a partition may exceed 10GB, shard it (e.g.,
USER#123#2024-01).
3. Choose Clustering / Sort Keys
Within a partition, data is sorted on disk by the Clustering Key (Cassandra) or Sort Key (DynamoDB).
- Enables efficient Range Queries (e.g.,
WHERE user_id=X AND date > Y).
- Pre-sorts 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
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).
6. Apache Cassandra / ScyllaDB Specifics
- 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 the LSM tree. Don't worry about write volume as much as read efficiency.
- Tombstones: Deletes are expensive markers. Avoid high-velocity delete patterns (like queues) in standard tables.
7. AWS DynamoDB Specifics
- GSI (Global Secondary Index): Use GSIs to create alternative views of your data (e.g., "Search Orders by Date" instead of by User). GSIs are eventually consistent.
- LSI (Local Secondary Index): Sorts data differently within the same partition. Must be created at table creation time.
- WCU / RCU: Understand capacity modes. Single-table design helps optimize consumed capacity units.
- TTL: Use Time-To-Live attributes to automatically expire old data (free delete) without creating tombstones.
Examples
Cassandra: Users by Email Lookup
CREATE TABLE users_by_email (
email text,
user_id uuid,
name text,
created_at timestamp,
PRIMARY KEY (email)
);
Query: SELECT * FROM users_by_email WHERE email = 'ian@example.com';
DynamoDB: Single-Table User + Orders
PK: USER#123 SK: PROFILE -> { name, email }
PK: USER#123 SK: ORDER#2024-001 -> { total, status }
PK: USER#123 SK: ORDER#2024-002 -> { total, status }
Query: Query PK=USER#123 returns the profile and all orders in one request.
Pitfalls
- ❌ Scatter-Gather: Querying all partitions to find one item (Scan).
- ❌ Hot Keys: Putting all "Monday" data into one partition.
- ❌ 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.
- ❌ Low-Cardinality PK:
status, gender, or country as a partition key creates hot partitions.
- ❌ Unbounded Partitions: A single user's orders growing forever without sharding.
- ❌
ALLOW FILTERING in Cassandra: Indicates a broken data model requiring a full cluster scan.
- ❌ High-Velocity Deletes: Creates tombstones that degrade read performance in Cassandra.
- ❌ Forgetting GSI Consistency: GSIs are eventually consistent; do not use them for strong-consistency requirements.
Verification
Before finalizing your NoSQL schema, run through this checklist:
Checkable Commands
Cassandra/ScyllaDB (Windows PowerShell, cqlsh on PATH):
cqlsh localhost 9042 -e "DESCRIBE TABLE keyspace.users_by_email;"
cqlsh localhost 9042 -e "SELECT COUNT(*) FROM keyspace.users_by_email WHERE email='ian@example.com';"
DynamoDB (AWS CLI v2, placeholder credentials):
aws dynamodb describe-table --table-name UsersOrders --endpoint-url http://localhost:8000
aws dynamodb query --table-name UsersOrders --key-condition-expression "PK = :pk" --expression-attribute-values '{":pk":{"S":"USER#123"}}' --endpoint-url http://localhost:8000
Limitations
- Use this skill only when the task clearly matches the scope described above.
- Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
- Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.
1---
2name: nosql-expert
3description: "Models Cassandra, ScyllaDB, and DynamoDB access patterns as tables: high-cardinality partition keys, clustering or sort keys, single-table adjacency lists, GSIs, and duplicated lookup tables. Trigger on hot partitions, ALLOW FILTERING, or DynamoDB single-table design. Never apply MongoDB document schemas or SQL join thinking to these stores."
4version: 1.0.1
5risk: unknown
6source: community
7date_added: "2026-02-27"
8metadata:
9 source: https://skillmd.com/skills/kayforkind/nosql-expert
10---
11
12# NoSQL Expert Patterns (Cassandra, ScyllaDB & DynamoDB)
13
14## Overview
15
16This skill provides professional mental models and design patterns for **distributed wide-column and key-value stores** (Apache Cassandra, ScyllaDB, and Amazon DynamoDB).
17
18Unlike SQL (where you model data entities), or document stores (like MongoDB), these distributed systems require you to **model your queries first**. You cannot "add a query later" without migration or creating a new table/index.
19
20> **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.
21
22### The Mental Shift: SQL vs. Distributed NoSQL
23
24| Feature | SQL (Relational) | Distributed NoSQL (Cassandra/DynamoDB) |
25| :--- | :--- | :--- |
26| **Data modeling** | Model Entities + Relationships | Model **Queries** (Access Patterns) |
27| **Joins** | CPU-intensive, at read time | **Pre-computed** (Denormalized) at write time |
28| **Storage cost** | Expensive (minimize duplication) | Cheap (duplicate data for read speed) |
29| **Consistency** | ACID (Strong) | **BASE (Eventual)** / Tunable |
30| **Scalability** | Vertical (Bigger machine) | **Horizontal** (More nodes/shards) |
31
32## When to Use
33
34- **Designing for Scale**: Moving beyond simple single-node databases to distributed clusters.
35- **Technology Selection**: Evaluating or using **Cassandra**, **ScyllaDB**, or **DynamoDB**.
36- **Schema Modeling**: Designing tables, partition keys, sort keys, or single-table layouts.
37- **Performance Tuning**: Troubleshooting "hot partitions", high latency, or uneven traffic in existing NoSQL systems.
38- **Microservices**: Implementing "database-per-service" patterns where highly optimized reads are required.
39
40## Prerequisites
41
42- Target database identified (Cassandra/ScyllaDB or DynamoDB).
43- A complete list of required **access patterns** (queries) before table design.
44- Understanding of expected read/write volume and cardinality of candidate partition keys.
45- If running locally on Windows (PowerShell), ensure `cqlsh` or AWS CLI v2 is installed and configured with placeholder credentials (`YOUR_KEY`).
46
47## Procedure
48
49### 1. Query-First Modeling (Access Patterns)
50
51You typically cannot "add a query later" without migration or creating a new table/index.
52
531. **List all Entities** (User, Order, Product).
542. **List all Access Patterns** ("Get User by Email", "Get Orders by User sorted by Date").
553. **Design Table(s)** specifically to serve those patterns with a single lookup.
564. **Validate** that every access pattern maps to exactly one table or index.
57
58### 2. Choose the Partition Key (PK)
59
60Data is distributed across physical nodes based on the **Partition Key (PK)**.
61
62- **Goal:** Even distribution of data and traffic.
63- **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.
64- **Best Practice:** Use high-cardinality keys (User IDs, Device IDs, Composite Keys).
65- **Split Partition Risk:** For any single partition (e.g., a single user's orders), will it grow indefinitely? If a partition may exceed **10GB**, shard it (e.g., `USER#123#2024-01`).
66
67### 3. Choose Clustering / Sort Keys
68
69Within a partition, data is sorted on disk by the **Clustering Key (Cassandra)** or **Sort Key (DynamoDB)**.
70
71- Enables efficient **Range Queries** (e.g., `WHERE user_id=X AND date > Y`).
72- Pre-sorts data for specific retrieval requirements.
73
74### 4. Single-Table Design (Adjacency Lists)
75
76*Primary use: DynamoDB (but concepts apply elsewhere)*
77
78Storing multiple entity types in one table to enable pre-joined reads.
79
80| PK (Partition) | SK (Sort) | Data Fields... |
81| :--- | :--- | :--- |
82| `USER#123` | `PROFILE` | `{ name: "Ian", email: "..." }` |
83| `USER#123` | `ORDER#998` | `{ total: 50.00, status: "shipped" }` |
84| `USER#123` | `ORDER#999` | `{ total: 12.00, status: "pending" }` |
85
86- **Query:** `PK="USER#123"`
87- **Result:** Fetches User Profile AND all Orders in **one network request**.
88
89### 5. Denormalization & Duplication
90
91Store the same data in multiple tables to serve different query patterns.
92
93- **Table A:** `users_by_id` (PK: uuid)
94- **Table B:** `users_by_email` (PK: email)
95
96*Trade-off: You must manage data consistency across tables (often using eventual consistency or batch writes).*
97
98### 6. Apache Cassandra / ScyllaDB Specifics
99
100- **Primary Key Structure:** `((Partition Key), Clustering Columns)`
101- **No Joins, No Aggregates:** Do not try to `JOIN` or `GROUP BY`. Pre-calculate aggregates in a separate counter table.
102- **Avoid `ALLOW FILTERING`:** If you see this in production, your data model is wrong. It implies a full cluster scan.
103- **Writes are Cheap:** Inserts and Updates are just appends to the LSM tree. Don't worry about write volume as much as read efficiency.
104- **Tombstones:** Deletes are expensive markers. Avoid high-velocity delete patterns (like queues) in standard tables.
105
106### 7. AWS DynamoDB Specifics
107
108- **GSI (Global Secondary Index):** Use GSIs to create alternative views of your data (e.g., "Search Orders by Date" instead of by User). GSIs are eventually consistent.
109- **LSI (Local Secondary Index):** Sorts data differently *within* the same partition. Must be created at table creation time.
110- **WCU / RCU:** Understand capacity modes. Single-table design helps optimize consumed capacity units.
111- **TTL:** Use Time-To-Live attributes to automatically expire old data (free delete) without creating tombstones.
112
113## Examples
114
115### Cassandra: Users by Email Lookup
116
117```sql
118CREATE TABLE users_by_email (
119 email text,
120 user_id uuid,
121 name text,
122 created_at timestamp,
123 PRIMARY KEY (email)
124);
125```
126
127Query: `SELECT * FROM users_by_email WHERE email = 'ian@example.com';`
128
129### DynamoDB: Single-Table User + Orders
130
131```text
132PK: USER#123 SK: PROFILE -> { name, email }
133PK: USER#123 SK: ORDER#2024-001 -> { total, status }
134PK: USER#123 SK: ORDER#2024-002 -> { total, status }
135```
136
137Query: `Query PK=USER#123` returns the profile and all orders in one request.
138
139## Pitfalls
140
141- ❌ **Scatter-Gather:** Querying *all* partitions to find one item (Scan).
142- ❌ **Hot Keys:** Putting all "Monday" data into one partition.
143- ❌ **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.
144- ❌ **Low-Cardinality PK:** `status`, `gender`, or `country` as a partition key creates hot partitions.
145- ❌ **Unbounded Partitions:** A single user's orders growing forever without sharding.
146- ❌ **`ALLOW FILTERING` in Cassandra:** Indicates a broken data model requiring a full cluster scan.
147- ❌ **High-Velocity Deletes:** Creates tombstones that degrade read performance in Cassandra.
148- ❌ **Forgetting GSI Consistency:** GSIs are eventually consistent; do not use them for strong-consistency requirements.
149
150## Verification
151
152Before finalizing your NoSQL schema, run through this checklist:
153
154- [ ] **Access Pattern Coverage:** Does every query pattern map to a specific table or index?
155- [ ] **Cardinality Check:** Does the Partition Key have enough unique values to spread traffic evenly?
156- [ ] **Split Partition Risk:** For any single partition (e.g., a single user's orders), will it grow indefinitely? (If > 10GB, shard the partition, e.g., `USER#123#2024-01`).
157- [ ] **Consistency Requirement:** Can the application tolerate eventual consistency for this read pattern?
158- [ ] **No Scans:** Are all production queries `SELECT`/`Query` by key, not `Scan`?
159- [ ] **No `ALLOW FILTERING`:** Confirm no Cassandra queries use `ALLOW FILTERING`.
160
161### Checkable Commands
162
163Cassandra/ScyllaDB (Windows PowerShell, `cqlsh` on PATH):
164
165```powershell
166cqlsh localhost 9042 -e "DESCRIBE TABLE keyspace.users_by_email;"
167cqlsh localhost 9042 -e "SELECT COUNT(*) FROM keyspace.users_by_email WHERE email='ian@example.com';"
168```
169
170DynamoDB (AWS CLI v2, placeholder credentials):
171
172```powershell
173aws dynamodb describe-table --table-name UsersOrders --endpoint-url http://localhost:8000
174aws dynamodb query --table-name UsersOrders --key-condition-expression "PK = :pk" --expression-attribute-values '{":pk":{"S":"USER#123"}}' --endpoint-url http://localhost:8000
175```
176
177## Limitations
178
179- Use this skill only when the task clearly matches the scope described above.
180- Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
181- Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.