How do you pick partition keys for Azure Cosmos Core SQL?

Question

I'm sort of confused by Core SQL, and would love some insight on how to work with it. For context, I have over 10 years of experience working with SQL Server databases, but the partitionKey concept is new to me, and I don't understand how to use it. It's required, and the default is for the partitionKey to be the same as the row primary key. I just don't see how that can work with multiple result sets, or with joining other tables at all. Unless it's only useful for very basic CRUD apps?

Let's take a simple shop for example. What would the partitionKeys be for an order, that has multiple line items, and those line items fulfilling the order might be located in separate warehouses. But you also want to be able to select all line items in a given warehouse, so the pickers know what they need to pack up and ship out.
If the partitionKey is the same as the primaryKey, you can't do any of those things.
If the partitionKey is something related, like the orderId, then you can't query for all items in a warehouse.
If the partitionKey is the warehouse, then you can't query for everything that's on an order.

I guess... do you guys have like example schemas I could look at? Seeing how a database is setup has always been more useful to me than reading concepts.
Or am I completely off base here, and Core SQL (despite its name) is only meant as a super-powered key-value store? And if THAT'S the case, then... do you guys have an inexpensive sql product that's actually sql?

Answer 1

For people who are new to distributed NoSQL databases I recommend consuming some content before getting too deep. There are some very fundamental concepts that need to be understood to help you model your data and come up with a partitioning strategy that will allow your database to scale and be as efficient as possible.

I realize this is quite a bit of content. But after going through this, I promise you will understand how and why Cosmos DB is different than a relational database and how to design for it.

Articles:

• Understanding the differences between NoSQL and relational databases
• Data modeling in Azure Cosmos DB
• How to model and partition data on Azure Cosmos DB using a real-world example

Videos:

• Modeling and Partitioning - 2 min intro
• How to model and partition data in Cosmos DB - This takes adventure works and remodels it for NoSQL
• Data Modelling in Azure Cosmos DB - This talks about modeling a book store scenario

Microsoft Learn Labs: (Essentially are HOL's for the second video above)

• Model and Partition your data in Azure Cosmos DB
• Advanced modeling patterns for Azure Cosmos DB

GitHub Repo:

• Cosmic Works - this is the code and data set for the adventure works talk above