DP-900: how to use indexes and secondary queries in Azure Table Storage

I will teach how to use partition and row keys (PartitionKey and RowKey) in Azure Table Storage to model efficient data and perform fast queries. This skill is useful on the DP-900 exam because it evaluates your understanding of simple NoSQL solutions on Azure and is practical for real application scenarios with event logging or lightweight catalogs. At the end of this article you will have concrete examples, common patterns and pitfalls to avoid that help you make informed modeling decisions.

What you need to know

Azure Table Storage is a column-oriented key-value NoSQL service optimized for large volumes of simple entities. Each entity has a PartitionKey and a RowKey which together form the unique primary key. The combination determines how data is distributed and read: queries that use PartitionKey (or PartitionKey + RowKey) are much faster and cheaper than full scans.

Conceptually, think of PartitionKey as the physical partition identifier and RowKey as the ordering or unique identifier within that partition. Queries that filter by PartitionKey are typically O(1) relative to the number of partitions queried — that is, the service goes directly to the partition — whereas a query without PartitionKey requires a scan across multiple partitions and can involve reading far more data, increased latency and higher costs.

Conceptual example: imagine a telemetry system that stores readings by device and day. You can use PartitionKey = DeviceId and RowKey = Timestamp (or the opposite, depending on query patterns). Thus, retrieving readings for a device is an efficient operation because you read only that device's partition, instead of scanning millions of records. In scenarios with 1 million to 100 million entities, this difference is critical.

How it works in practice

Main behaviors to know:

• PartitionKey groups entities; all entities in the same partition are physically close and support batch transactions within the same partition.
• RowKey orders entities within the partition and, together with the PartitionKey, uniquely identifies each entity.
• Querying by PartitionKey is very efficient; without PartitionKey, the service performs a scan that is slow and can consume many more RU (request units) and bandwidth.

Entity example (conceptual JSON format):

{
  "PartitionKey": "DeviceA",
  "RowKey": "2026-10-21T09:00:00Z",
  "Temperature": 21.5,
  "Humidity": 60
}

In practice, and for production loads, you should measure: for example, reading 10 records in a dedicated partition can take tens of milliseconds, while a scan over millions of records can take seconds and cost much more. When modeling, always consider latency, cost and write/read throughput per second.

Step by step: model for efficient queries

Follow these steps when planning your schemas in Table Storage.

1. Identify query patterns: what questions does the application ask most often? Eg.: get readings by device, by time range, by location. Make a list of the top three queries and prioritize modeling for those operations.
2. Choose PartitionKey to group entities that will frequently be read together. Eg.: DeviceId or LocationId if you read by location. If you expect reads per day, you can use LocationId|YYYYMMDD as PartitionKey to distribute by day.
3. Choose RowKey to order / uniquely identify within the partition. Eg.: inverted Timestamp (for most recent readings first) using a formula like MaxTicks - ticks, or a composite identifier DeviceId|SequenceNumber.
4. Leverage batch operations when you need to write/update multiple entities in the same partition: batch operations are only supported within the same PartitionKey and each batch supports up to 100 operations, which reduces network overhead and improves transactional consistency.
5. Avoid overly large partitions: if a PartitionKey receives very high write/read volume (hot partition), consider splitting by sub-keys (eg.: DeviceId + hour or adding a salt 0–9) to distribute load. For example, instead of 50k writes/s on one partition, you could have 10 partitions with ~5k writes/s each.

Example: pattern to get the latest readings of a device (RowKey with inverted Timestamp):

// Conceptual pseudocode query
partition = "DeviceA"
// RowKeys were written as (MaxTimestamp - Timestamp) to order descending
query = TableQuery.where(PartitionKey == partition).take(10)
results = table.execute(query)

Common mistakes

Some typical pitfalls when working with Azure Table Storage:

• Not planning query patterns: choosing PartitionKey/RowKey without thinking about queries leads to expensive and slow scans. Re-evaluate the schema when requirements change.
• Hot partitions: using a single PartitionKey for all records (for example, "All") causes write bottlenecks and reduces performance. If you start seeing throttling or elevated latency, it’s a sign to shard.
• Expecting SQL capabilities: Table Storage does not support joins, cross-partition transactions or complex queries. If you need that, consider Cosmos DB, Azure SQL or a pipeline/ETL design for aggregation.

How to practice

To internalize these concepts, do the Azure labs and exercise real scenarios. Check the official Microsoft Practice Assessment (it’s free) to verify your general DP-900 knowledge. Also read the official Microsoft study guide for DP-900 (free) — both present the measured topics without providing real exam questions.

Practice in the Azure portal by creating a Storage Account with Table Storage or use the SDK (C#, Python, JavaScript) to insert and query entities. Test variations: write 1000 entities into a single partition and then write the same 1000 distributed across 10 partitions; measure latency and costs. Try batch operations (up to 100 records) and observe performance gains compared to individual inserts.

In summary

• PartitionKey and RowKey are crucial for performance: they are used to group and identify entities efficiently.
• Define PartitionKey based on your query patterns; avoid "hot" partitions and global scans.
• Batch operations are supported only within the same partition (up to 100 operations) — take advantage of them for efficiency and consistency.
• Azure Table Storage is a simple NoSQL: don’t expect advanced relational capabilities; model data for the queries that matter and validate with real load tests.