Cosmos DB Partition Key & Data Modeling Prompt
Design or review an Azure Cosmos DB data model — partition key choice, embedding vs referencing, and indexing — to avoid hot partitions and runaway RU cost.
- Target user
- Application and data engineers modeling on Azure Cosmos DB
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior data engineer who has rescued Cosmos DB workloads from hot partitions and RU bills that doubled overnight. You model from access patterns, not from a relational schema, and you treat the partition key as the most important decision in the design. I will provide: - The entities and their relationships — what data, how it relates — [ENTITIES] - The access patterns — the actual reads and writes, their frequency, and which are latency-critical — [ACCESS_PATTERNS] - Current model (if any) — containers, partition keys, document shapes — [CURRENT_MODEL] - Scale — item counts, item sizes, write/read throughput, cross-partition query frequency — [SCALE] - The pain (if reviewing) — hot partition, high RU, cross-partition queries, 429s — [PAIN_POINTS] Your job: 1. **Partition key** — recommend a partition key that (a) spreads writes and storage evenly and (b) lets the highest-frequency reads be single-partition. Flag low-cardinality keys (hot partitions), monotonically increasing keys (write hotspot), and keys that force the common query to fan out cross-partition. Consider synthetic/composite keys where needed. 2. **Embed vs reference** — embed data read together and bounded in size; reference data that's large, unbounded, or updated independently. Flag embedding that risks the 2 MB item limit or rewrites a huge doc on every small change. 3. **Indexing** — recommend an indexing policy that excludes paths never filtered on, to cut write RU; flag the default index-everything policy on write-heavy containers. 4. **RU & cost** — estimate which queries are single-partition (cheap) vs cross-partition (expensive), and whether autoscale or manual throughput fits the pattern. Flag queries that will 429 under load. 5. **Migration** — if changing the partition key (which requires a new container), give the safe re-partition path: new container, dual-write or backfill, cut over, verify, retire old. Output as: (a) the recommended container/partition-key design with rationale per access pattern; (b) example document shapes; (c) the indexing policy JSON; (d) the migration plan if the key changes. Model only from the access patterns I gave you. Don't invent queries or assume scale figures — ask if the read/write patterns are unclear.
Why this prompt works
In Cosmos DB the partition key is the decision everything else hangs on, and it is the one most likely to be made by analogy to a relational primary key — which is exactly how teams end up with hot partitions and runaway RU bills. This prompt forces the model to come from access patterns, not entities: it asks for the actual reads and writes and their frequency, then chooses a key that both spreads load evenly and keeps the highest-frequency reads on a single partition. That dual requirement is the crux of good Cosmos modeling and the thing generic NoSQL advice skips.
The embed-versus-reference and indexing sections target the cost mechanics that surprise teams later. Embedding data that’s read together is right until the document grows unbounded toward the 2 MB limit or a tiny update rewrites a huge item; the default index-everything policy is convenient until it doubles write RU on a write-heavy container. By grounding these calls in real item sizes and write patterns, the prompt produces a model tuned for the workload’s RU profile rather than one that merely works in a demo and falls over under production load.
The guardrails encode the most expensive mistake in Cosmos: treating the partition key as changeable. It isn’t — fixing it means a new container and a full migration — so the prompt always presents key changes with a dual-write, backfill, cutover, and verify path, and insists the key be validated against real cardinality and access patterns before it’s committed. That keeps an irreversible decision from being made casually, which is precisely where Cosmos projects most often go wrong.