Your DP-203 exam objective 3.4 demands that you know how to secure, monitor, and optimise data storage and processing. Before you can secure data, you must understand why your storage is slow in the first place. This chapter will show you how to configure partition keys, choose the right storage tier, and design an indexing strategy that transforms a sluggish data lake into a high-speed engine, so your queries finish in seconds instead of minutes.
Jump to a section
A simple way to picture Optimize Data Storage Performance
Your local supermarket is a vast building. Every single product – from fresh milk to frozen pizzas to cleaning supplies – must be placed somewhere on a shelf. The shelf space is the storage, and how quickly a customer can grab a specific item is the performance. The store manager’s job is to optimise this performance.
Early in the morning, a delivery truck arrives with pallets of new stock. The manager must decide: does she dump every box in a back room, forcing the stock boy to walk miles tearing through boxes every time a customer asks for something? That is like using slow, unorganised storage that causes high latency. Instead, she groups items logically: all dairy in one refrigerated aisle, all pasta in another. This is like using partition keys – data is organised by a common attribute so that a query (the customer) can find everything it needs in one quick trip, rather than scanning the entire store.
But the manager goes further. The most frequently bought items, like milk and bread, get placed at eye level at the front of the aisle. This is the equivalent of a hot tier – frequently accessed data gets stored on high-speed, expensive media. The bulk bags of rice, bought only once a month, get placed on the very bottom shelf, accessible but slow. This is a cool or archive tier – cheaper storage for data that is rarely touched. A customer hunting for a specific obscure spice might also consult the store’s digital map on a terminal – that map is an index, telling them exactly which shelf number to check, bypassing a complete store search. Finally, the manager ensures that the checkout belt is never blocked: data exits storage as quickly as it enters, meeting the throughput demands of a busy Saturday afternoon.
When you store data in Azure, it is not a single, simple location like a folder on your laptop. Azure offers many different storage services, primarily Azure Blob Storage, Azure Data Lake Storage Gen2, and Azure Cosmos DB. Every one of these services has physical limitations. The disk drives inside the data centre can only read and write data so fast. The network cables feeding those drives can only carry so many bits per second. If you dump all your data into one big pile, you hit those limits. The process of 'optimising data storage performance' is the art of organising your data so that the physical hardware does the least amount of work possible to answer a question.
The single most important concept is the partition key. Think of a partition key as a label you glue onto every row or file. Azure uses that label to decide which physical server gets to store that piece of data. All data sharing the same partition key ends up on the same server, called a partition. If you run a query that filters on that key, Azure sends the request to exactly one partition, which reads its own data in isolation. This is called a partition scan or point lookup. If you run a query without the partition key, Azure must send the same request to every single partition, combine all the results, and return them to you. This is called a cross-partition query, and it is dramatically slower because you are forcing every machine to work at the same time.
Choosing the wrong partition key is the number one performance killer. A common beginner mistake is to use a column with very few distinct values, like 'Status' which might be only 'Active' or 'Inactive'. All Active data goes to one partition, and all Inactive goes to another. One partition becomes a 'hot spot' – it is hit with every read and write while the other sits idle. This is called a hot partition. You want a partition key with high cardinality – many distinct values – such as 'CustomerId' or 'OrderId', so that the load spreads evenly across all partitions.
Next is data compression. Azure services like Parquet and Avro file formats are compressed by default. Compression reduces the number of bytes that must be read from the disk and sent across the network. A 10-gigabyte uncompressed CSV file might become a 500-megabyte Parquet file. Your query will read 95% less data, and therefore finish far faster. Always use columnar formats like Parquet for analytical workloads.
Indexing is the third pillar. An index is a separate data structure that lists the location of values within your data. Instead of scanning every row to find 'CustomerId = 123', Azure consults the index, finds the exact file offset, and jumps straight to the data. Azure Blob Storage does not have traditional indexes, but Azure Cosmos DB and Azure SQL Database do. Creating the right index – like a clustered index on the partition key and non-clustered indexes on frequently filtered columns – is essential.
Finally, storage tiers let you match cost to performance. Azure offers three main tiers for Blob Storage:
Hot tier: High-cost, high-speed storage for data accessed frequently.
Cool tier: Lower cost, lower speed for data accessed infrequently (once a month or less).
Archive tier: The cheapest option, but data takes hours to 'rehydrate' (become readable).
You must also consider throughput. Every storage account has a limit on the number of requests per second (Input/Output Operations Per Second, or IOPS) and the amount of data it can transfer per second (bandwidth). If you exceed these limits, Azure throttles your requests – they are delayed or rejected. To avoid throttling, you can split your data across multiple storage accounts, which increases the aggregate IOPS and bandwidth available.
By combining the correct partition key, compression, indexing, storage tier, and throughput planning, you build a storage system that responds to queries quickly without costing you a fortune.
Analyse the Query Pattern
Identify which columns your users filter on the most. Look at the WHERE clauses of your slowest queries. These columns are candidates for the partition key and for indexing. If no one queries on a column, do not design your storage around it.
Select the Partition Key
Choose a column with many unique values (high cardinality) and that is included in most queries. For example, 'OrderId' or 'CustomerId' rather than 'Status'. This ensures even data distribution and allows point lookups that touch only one partition.
Convert File Format to Parquet or ORC
If your data is in CSV or JSON, convert it to a columnar format. This reduces the amount of data read by 80-90% because only needed columns are loaded. Tools like Azure Data Factory or Spark can perform this conversion.
Set Up Data Compression
Enable compression on your data files. Use Snappy for a good balance of speed and compression ratio, or Gzip for maximum compression at the cost of slower decompression. Both are supported by Parquet natively.
Configure Storage Tier Lifecycle
Create a lifecycle management rule that moves data from Hot to Cool after 30 days, and from Cool to Archive after 365 days. This keeps recent queries fast while saving money on older data.
Create Indexes or Materialised Views
For structured data stores like Azure SQL or Cosmos DB, create indexes on the columns used in WHERE and JOIN clauses. For analytical workloads, create materialised views that pre-aggregate common metrics, so users never scan raw data again.
Monitor and Adjust Throughput
Use Azure Monitor to check for throttling errors. If they appear, increase the number of storage accounts or increase the provisioned throughput in Cosmos DB. Distribute the data so that high-traffic partitions are not all in one account.
A retail company, 'ShopFast Ltd', stores every sale from the last five years in Azure Data Lake Storage Gen2. The sales data is stored as a single giant folder of unorganised CSV files. Every Monday morning, the finance team runs a report to show total revenue per product category for the previous month. The report currently takes 45 minutes to run.
An Azure data engineer is assigned to fix this. The engineer starts by analysing the query pattern. The finance team always filters by 'Month' and 'ProductCategory'. The engineer decides to use 'Year-Month' as the partition key and store each month's data in a separate folder within Azure Data Lake Storage Gen2. This is called a directory partition. When the team runs the query, Azure only reads the folder for the relevant month, ignoring the other 47 months of data. This single change cuts the scan size from 5 terabytes to roughly 100 gigabytes.
The engineer then converts the file format. All CSV files are converted to Parquet. Parquet is columnar – it stores each column separately. Since the report only needs the 'Revenue' and 'ProductCategory' columns, the engine reads only those two columns from the Parquet file, skipping the other 15 columns. This reduces the data read from 100 gigabytes to roughly 5 gigabytes.
The engineer then configures Azure Blob Storage lifecycle management. A policy is created that moves data older than 90 days from the Hot tier to the Cool tier, and data older than 365 days to the Archive tier. Queries on recent data are fast because it is on high-speed drives, while historical data costs almost nothing to store.
Finally, the engineer increases the storage account limits by splitting the oldest data across two separate storage accounts. The query engine can now pull data from two accounts in parallel, doubling the effective throughput.
Step by step, the engineer does this:
Identifies the most common query filters and partitions the data on those fields.
Converts all files from CSV to Parquet to reduce scan size.
Creates a lifecycle management policy to move old data to Cool/Archive tiers.
Splits large data sets across multiple storage accounts to avoid throttling.
Creates a materialised view (a pre-computed summary table) for the monthly revenue report.
After these changes, the Monday morning report finishes in under 90 seconds. The company saves 70% on storage costs because most data now sits in the Cool tier. The engineer documented the new partition key schema so that future developers can follow the same pattern.
The DP-203 exam will test you specifically on how to choose the right partition key, how to configure storage tiers, and how to recognise performance bottlenecks. The exam does not ask you to write code to optimise storage. Instead, it presents multiple-choice scenarios where you must select the correct optimisation step.
What the exam expects you to know:
Partition key selection: Always choose a key with high cardinality and that appears in your most frequent WHERE clauses. The exam will give you a scenario such as: 'A table contains OrderDate, CustomerId, Status, and Region. Which column should be the partition key?' The correct answer is the one with the most unique values and the most common query filter. Never choose Status or a Boolean field.
Cross-partition queries: The exam will describe a slow query and ask why. The trap is that the candidate thinks hardware is the problem. The answer is usually that the query is scanning all partitions because it doesn't filter on the partition key.
Storage tiers: You will be asked when to use Hot vs. Cool vs. Archive. The rule is simple: Hot for data accessed within 30 days, Cool for 30 to 365 days, Archive for anything older than a year. A common trap is suggesting Archive for data that is still queried regularly – Archive is offline and takes hours to load.
Throughput limits: The exam will provide a scenario where queries are failing with 'throttling errors'. The fix is to increase the number of storage accounts or to use Azure Data Lake Storage Gen2 which has higher default limits than Blob Storage.
File formats: The exam wants you to know that columnar formats like Parquet and ORC are far more performant than row-based formats like CSV or JSON for analytical queries. A typical question: 'You need to reduce query execution time by 80%. Which file format should you use?'
Compression: The exam tests that using Snappy or Gzip compression on Parquet files reduces size without sacrificing too much decompression speed.
Indexing in Cosmos DB: You must remember that you can set a composite index for queries that filter on multiple fields.
Exam traps:
The exam might ask you to 'optimise storage performance' and offer a choice like 'Add more RAM to the server' – this is wrong because serverless storage services do not let you control hardware.
Another trap is 'Create a new index on every column' – this slows down writes and consumes space, so it is the opposite of optimisation.
The exam loves to offer 'Enable geo-redundant storage' as an option – this is about disaster recovery, not performance. Do not confuse the two.
Memorise this pattern: if a query is slow, first check if it is filtered on the partition key. If not, the answer is to redesign the partition key. If it is filtered on the partition key, check file format (use Parquet) and compression (enable Snappy).
Choose a partition key with high cardinality that appears in your most frequent WHERE clauses to avoid hot partitions and cross-partition queries.
Always use columnar file formats like Parquet for analytical workloads to reduce I/O by skipping unneeded columns.
Enable compression (Snappy or Gzip) on your data files because the I/O savings far outweigh the decompression overhead.
Use Azure Blob Storage lifecycle management to automatically move data from Hot to Cool to Archive tiers based on age, balancing cost and performance.
Avoid creating indexes on every column; instead, create indexes only on columns that appear in WHERE, JOIN, or ORDER BY clauses.
If you encounter storage throttling, distribute your data across multiple storage accounts and ensure your queries target specific partitions.
Materialised views pre-compute aggregated results so that repeated queries do not scan raw data again and again.
The Cool tier is ideal for data accessed monthly, while Archive tier is for data accessed once a year or less and can tolerate hours of latency.
Cross-partition queries are the number one cause of slow performance; always filter on the partition key.
In Azure Cosmos DB, use composite indexes when queries filter on multiple columns to avoid full partition scans.
These come up on the exam all the time. Here's how to tell them apart.
CSV File Format
Row-oriented: reads entire rows even if only a few columns are needed
No built-in compression; file sizes are large
Poor performance for analytical queries that scan many rows but few columns
Parquet File Format
Column-oriented: reads only the columns specified in the query
Built-in compression with Snappy or Gzip; file sizes are typically 80% smaller
Excellent performance for analytical queries; often 10-100 times faster than CSV
Hot Storage Tier
Highest cost per GB of storage
Designed for data accessed frequently (multiple times per day)
Zero latency for read and write operations
Cool Storage Tier
Lower cost per GB of storage
Designed for data accessed infrequently (once a month or less)
Slightly higher latency than Hot, but still online and readable
Point Lookup Query
Filters on the partition key and touches only one partition
Very fast (milliseconds) because only one server is involved
Low consumption of throughput (RU or IOPS)
Cross-Partition Query
Does not filter on the partition key, so it must scan every partition
Very slow (seconds to minutes) because it requires coordination across many servers
High consumption of throughput and can cause throttling
Clustered Index
Determines the physical order of data on disk
Only one per table
Very fast for range queries on the indexed column
Non-Clustered Index
A separate structure that points to the location of data
Many per table allowed
Fast for exact-match lookups but slower for range scans than a clustered index
Mistake
Adding more storage accounts always increases performance automatically.
Correct
Adding more storage accounts increases the aggregate throughput limit, but only if you also redesign your data distribution so that queries actually read from multiple accounts in parallel. If all queries target a single account, adding more accounts does nothing.
Beginners think of storage accounts like adding more lanes to a highway, but if all traffic stays in one lane, the extra lanes are empty and irrelevant.
Mistake
Compression always slows down queries because the data must be decompressed.
Correct
Compression reduces the amount of data that must be read from disk and sent over the network, which is far more impactful than the small overhead of decompression. In practice, compressed data reads 10-100 times faster because network and disk I/O are the true bottlenecks.
It sounds counterintuitive that adding a step (decompression) could speed things up. People confuse computational overhead with I/O overhead.
Mistake
The Hot storage tier is best for all data because it is the fastest.
Correct
The Hot tier is expensive. Using it for old, rarely accessed data wastes money without improving the experience for the queries that actually matter. The Cool and Archive tiers exist to reduce cost without affecting users who query only recent data.
Beginners focus solely on speed and ignore cost. The exam tests your ability to balance cost and performance, not just maximise speed.
Mistake
Once a partition key is chosen, it cannot be changed, so you must get it right the first time.
Correct
In Azure Data Lake Storage Gen2 and Azure Cosmos DB, you can repartition data by writing a new dataset with a different partition key. You can also use Azure Data Factory to copy and repartition data without downtime.
Many people assume that databases are rigid. In Azure, data is stored as files, and you can rewrite those files with a new folder structure or key at any time.
Mistake
All file formats are essentially the same; the format only affects storage size, not query speed.
Correct
File format dramatically affects query speed because row-based formats (CSV, JSON) require reading entire rows even if you only need a few columns. Columnar formats (Parquet, ORC) let the query engine skip columns entirely, reducing I/O by up to 90%.
Beginners rarely understand the difference between row-oriented and column-oriented storage. They think of files as black boxes that are either 'fast' or 'slow' without understanding why.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A partition key tells Azure which physical server stores a piece of data. Choose a column with many distinct values (like CustomerId) that you often filter on. Avoid columns with few values (like Status) because they cause hot partitions.
Yes. Archive is an offline tier – data takes hours to rehydrate before you can read it. Only move data to Archive that you query rarely (once a year or less) and that you are okay waiting for. For monthly reports, use the Cool tier.
CSV stores data row by row, so even if you only need two columns, the engine must read every row completely. Parquet stores data column by column, so the engine can skip reading irrelevant columns entirely, drastically reducing I/O.
Check the query plan – in Azure Synapse or Cosmos DB, the plan shows a 'full scan' or 'cross-partition query' indicator. If your WHERE clause does not include the partition key, the query will scan every partition. You fix this by rewriting the query or repartitioning the data.
Yes. Indexes speed up reads but slow down writes because every write must update the index. They also consume storage. Only create indexes on columns that are frequently used in WHERE, JOIN, or ORDER BY clauses.
Throttling is when Azure refuses your request because you have hit the storage account's IOPS or bandwidth limit. To stop it, either increase the number of storage accounts (so load spreads out) or increase the provisioned throughput in Cosmos DB.
Yes. You cannot modify the key in place, but you can create a new dataset with a different partition key using Azure Data Factory or a Spark job. This essentially repartitions your data without downtime.
You've finished Optimize Data Storage Performance. Continue through the DP-203 study guide to build a complete picture of the exam.
Done with this chapter?