DP-900: how to understand and use indexes in Azure Cosmos DB (Global Secondary Indexes)

I will teach the DP-900 skill on indexes in Azure Cosmos DB — because understanding indexing is essential to get fast queries and controlled costs in the service. This skill appears in the Non-relational Data area in Azure and is important both for the exam and for real production scenarios.

What you need to know

Indexing in Azure Cosmos DB is the mechanism that allows you to search documents quickly without scanning the entire collection. By default, Cosmos DB indexes all fields of all documents (default index policy), which makes queries fast but can increase write cost and latency. Understanding how to control indexing — include/exclude paths, set index types (Hash, Range, Spatial) and precision levels — allows you to optimize performance and cost.

Simple example: you have JSON documents with the structure {"id": "1", "cliente": {"nome": "Ana", "idade": 30}, "pedido": {"valor": 120.5}}. If you run many queries by cliente.nome, you should ensure that that path is indexed. If you have large fields or frequently updated fields that are never queried (e.g., historicoCompleto), you should exclude them from the indexing policy to reduce write costs.

How it works

Key concepts:

• Index policy: defines which paths are indexed and with which index type.
• Index types: Hash (for equality), Range (for ordering and ranges) and Spatial (for geospatial data).
• Included / Excluded paths: you explicitly specify which properties to index or not.
• Consistency with RU costs: indexing more paths increases RU/s for write operations; indexing less reduces write cost but may require scans for some queries.

Example of an index policy (simplified JSON):

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    { "path": "/cliente/nome/?", "indexes": [ {"kind":"Hash","dataType":"String","precision":3} ] },
    { "path": "/pedido/valor/?", "indexes": [ {"kind":"Range","dataType":"Number"} ] }
  ],
  "excludedPaths": [
    { "path": "/historicoCompleto/*" }
  ]
}

The notation "/prop/?" indicates indexing of scalar properties at that route; "/prop/*" includes descendant objects and arrays.

In practice

Step-by-step practical approach to optimize indexing:

1. Map query patterns: list the properties used in filters, orderings and JOINs.
2. Assess write frequency: for highly dynamic fields, consider excluding them.
3. Choose index type: frequent equality → Hash; ordering/ranges → Range; geospatial → Spatial.
4. Define included/excluded paths in the collection/container index policy.
5. Test with representative workloads: measure RU/s for writes and read latency before and after.

Practical example with two queries and adjustments:

// Before: default indexing => everything indexed
SELECT * FROM c WHERE c.cliente.nome = 'Ana'

// If we add ordering by value:
SELECT * FROM c WHERE c.cliente.nome = 'Ana' ORDER BY c.pedido.valor

// Recommended adjustment: index /cliente/nome as Hash and /pedido/valor as Range to support ORDER BY without scans

Operational notes: changing the index policy is immediate for new writes; reindexing existing data can be done automatically depending on the change, but it can impact RU/s while it runs.

Common mistakes

1) Relying on the default without checking: accepting the default indexingPolicy can cause high RUs on writes if you have large fields or many updates. The default is not always the best for production.

2) Excluding overly broad paths: using "/*" in excludedPaths indiscriminately can prevent legitimate queries from using indexes, forcing scans and increasing read RU.

3) Choosing the wrong index type: using Hash when you need ordering (ORDER BY) will prevent the query from being efficiently served by the index.

How to practice

Practice with a container in Azure Cosmos DB: create test documents that reproduce your read/write pattern, change the index policy and observe RU/s and latency. For exam preparation, use the OFFICIAL and free Microsoft Practice Assessment and the official study guide (both free) — these resources help you confirm that you understand the Non-relational Data area. Do not use dumps; practice with labs and official resources.

In summary

• Indexing in Cosmos DB speeds up queries but increases write cost; control it with the index policy.
• Choose Hash for equality, Range for ordering/ranges and Spatial for geospatial.
• Include only necessary paths and exclude bulky/highly updated fields to reduce RUs.
• Test changes with real workloads and use Microsoft's official resources to practice.