DP-900 Hard Questions — 25 Difficult Questions

Question 1

A global e-commerce company uses Azure SQL Database for its product catalog. The application experiences high read traffic for product detail pages, often running the same queries for popular items. The database’s write workload is moderate. The company wants to improve read performance without increasing the cost of the primary database tier and without changing the application code. Which Azure SQL Database feature should they implement?

Accepted Answer

Read scale-out. Read scale-out (A) is correct because it offloads read-only queries to a secondary replica of the Azure SQL Database without changing the application code. By setting `ApplicationIntent=ReadOnly` in the connection string, the database routes read queries to a read-only replica, improving performance for high-read workloads like product detail pages while keeping the primary tier unchanged and avoiding additional cost for a higher tier.

Answer

Active geo-replication

Answer

Azure SQL Database elastic pool

Answer

Query Performance Insight

Question 2

A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?

Accepted Answer

Implement table partitioning by month on TransactionDate.. Table partitioning by month on TransactionDate allows Azure SQL Database to efficiently manage and query large tables by splitting data into manageable segments. Queries that filter on TransactionDate for the current month will only scan the relevant partition(s), eliminating full table scans. Additionally, partitioning simplifies archiving by enabling swift partition switching to move old data to archive tables without complex ETL processes.

Answer

Create a non-clustered index on the TransactionDate column.

Answer

Create a materialized view for the current month's data.

Answer

Convert the table to use a clustered columnstore index.

Question 3

A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?

Accepted Answer

Table partitioning with partition functions and schemes. Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.

Answer

Sharding across multiple Azure SQL Managed Instances

Answer

Index partitioning only

Answer

Federated tables

Question 4

A company operates a high-volume order processing system on Azure SQL Database. During peak hours, many concurrent transactions try to insert and update rows in the same table, causing contention on page latches. Indexing and query optimization are already tuned. Which feature should the company implement to reduce write contention while preserving ACID properties?

Accepted Answer

In-Memory OLTP. In-Memory OLTP is correct because it uses memory-optimized tables and natively compiled stored procedures to reduce latch contention by eliminating the need for page latches entirely. Transactions operate directly on in-memory data structures, using optimistic multi-version concurrency control (MVCC) to detect conflicts without blocking, which preserves ACID properties while allowing high concurrency.

Answer

Read Scale-out

Answer

Elastic Database Query

Answer

Transparent Data Encryption (TDE)

Question 5

A database administrator manages an Azure SQL Database with a table that has a clustered index on OrderID. Frequent queries filter on OrderDate and then sort the results by CustomerID. These queries perform poorly. Which indexing strategy will most improve performance for these specific queries?

Accepted Answer

Create a nonclustered index on (OrderDate, CustomerID). The query filters on OrderDate and then sorts by CustomerID. A nonclustered index on (OrderDate, CustomerID) supports both the WHERE clause (by OrderDate) and the ORDER BY clause (by CustomerID) as a covering index, allowing the database engine to perform a single index seek or scan without needing a separate sort operation. This directly addresses the performance bottleneck by eliminating the need to sort the results after filtering.

Answer

Create a nonclustered index on (CustomerID, OrderDate)

Answer

Change the clustered index to (OrderDate)

Answer

Create a filtered index on OrderDate WHERE CustomerID IS NOT NULL

Question 6

A company uses Azure Synapse Analytics to run complex queries against large datasets stored in Parquet files in Azure Data Lake Storage Gen2. They notice that queries scanning entire partitions are slow due to high I/O overhead on the compute nodes. Investigation shows each daily partition contains thousands of small files (under 1 MB each). Which optimization should be implemented first to improve query performance?

Accepted Answer

Compact small files into larger ones before querying. Option C is correct because the high I/O overhead is caused by the thousands of small files per partition. When Synapse compute nodes read many small files, the overhead of opening, reading metadata, and closing each file dominates, even though the total data volume is small. Compacting these small files into fewer, larger files (e.g., 128 MB or more) reduces the number of file operations, improves read throughput, and allows more efficient predicate pushdown and parallelism.

Answer

Increase the number of compute nodes

Answer

Use columnstore indexes on external tables

Answer

Change the partition column to a different date granularity

Question 7

A financial services company stores account balances in Azure SQL Database (strong consistency) and transaction audit logs in Azure Cosmos DB (eventual consistency by default). A compliance requirement demands that when a transaction is rolled back in the SQL database, the corresponding audit log entries in Cosmos DB must also be removed within a short time frame. Which term best describes the difficulty of maintaining this constraint?

Accepted Answer

Distributed transaction coordination. Option C is correct because the scenario requires coordinating a rollback across two distinct data stores—Azure SQL Database (ACID-compliant, strong consistency) and Azure Cosmos DB (eventual consistency by default). This cross-system transactional consistency is a classic distributed transaction coordination problem, often addressed via patterns like the two-phase commit (2PC) or the saga pattern, but not natively supported between these two services without custom orchestration.

Answer

ACID compliance

Answer

Idempotency

Answer

Schema flexibility

Question 8

A financial services company stores transaction data in Azure Data Lake Storage Gen2 as Parquet files, partitioned by date. The data volume is 5 TB per day. The analytics team runs ad-hoc SQL queries to detect fraudulent patterns. Queries are highly selective (filtering on AccountID and date range). The team also needs to create external tables and views for use in Power BI. They want to pay only for the data processed by each query and avoid provisioning any compute resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, aligning with the requirement to pay only for data scanned by each query. It also supports creating external tables and views for Power BI, making it ideal for ad-hoc, selective queries on partitioned Parquet files.

Answer

Azure Databricks with interactive clusters

Answer

Azure Stream Analytics

Answer

Azure HDInsight with Spark

Question 9

A financial services company uses Azure Synapse Analytics to process large volumes of transaction data. They have a dedicated SQL pool (formerly SQL DW) that ingests curated, aggregated data nightly from a data lake. Data analysts need to run ad-hoc, exploratory T-SQL queries on raw transaction data stored as Parquet files in Azure Data Lake Storage Gen2. These queries vary widely in complexity and frequency. The company wants to minimize costs for these ad-hoc queries while still using full T-SQL capabilities. Which approach should they recommend?

Accepted Answer

Create a serverless SQL pool endpoint to query the data lake directly.. Serverless SQL pool in Azure Synapse Analytics is designed for ad-hoc, on-demand querying of data lake files (like Parquet) without provisioning or paying for dedicated compute resources. It supports full T-SQL syntax and charges only for the data processed per query, making it cost-effective for exploratory workloads with variable complexity and frequency.

Answer

Use external tables in the dedicated SQL pool to query the data lake directly.

Answer

Load the raw data into the dedicated SQL pool before querying.

Answer

Use Azure Data Explorer to query the data lake.

Question 10

A global gaming company stores player profiles in Azure Cosmos DB. Each profile document contains PlayerID (unique), PlayerName, Email, and a nested array of Achievements. The most common query is to look up a player by PlayerID and retrieve their achievements. The company needs strong consistency for reads and writes to ensure that when a player earns an achievement, it is immediately visible. Which partition key and consistency level should they choose?

Accepted Answer

B. Partition key: PlayerID; Consistency: Strong. Option B is correct because PlayerID is the natural partition key for the most common query (lookup by PlayerID), ensuring efficient single-partition queries. Strong consistency is required to guarantee that when a player earns an achievement, the write is immediately visible to all subsequent reads, which is critical for the gaming scenario.

Answer

A. Partition key: PlayerID; Consistency: Eventual

Answer

C. Partition key: Achievements; Consistency: Strong

Answer

D. Partition key: Email; Consistency: Bounded staleness

Question 11

A global gaming company develops a multiplayer game. Player profile data (username, email, preferences) is stored as simple key-value pairs and must be accessible with single-digit millisecond latency from any region. Game session logs are stored as JSON documents with varying fields (session ID, player actions, timestamps) and must be queryable by player ID and timestamp range using SQL-like syntax. The company wants to use a single Azure database service for both workloads. Which combination of Azure Cosmos DB APIs should they choose?

Accepted Answer

Table API for profiles and SQL API for logs. Option A is correct because the Table API provides a simple key-value store ideal for low-latency profile lookups, while the SQL API supports querying JSON documents with SQL-like syntax, enabling efficient queries on game session logs by player ID and timestamp range. This combination meets both workloads within a single Azure Cosmos DB account.

Answer

SQL API for both profiles and logs

Answer

MongoDB API for profiles and Cassandra API for logs

Answer

Table API for both profiles and logs

Question 12

A retail chain captures real-time sales data from point-of-sale (POS) systems as a stream of events. The data is ingested into Azure Event Hubs. Additionally, the company receives daily inventory files in CSV format uploaded to Azure Data Lake Storage Gen2. The analytics team needs to combine the streaming sales data with the batch inventory data to generate near real-time dashboards and run historical reports. They want a single analytics platform that can handle both streaming and batch workloads, and allow querying data directly in the data lake using SQL. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that natively integrates with Azure Event Hubs for real-time streaming ingestion and Azure Data Lake Storage Gen2 for batch data. Its Synapse SQL engine supports querying data directly in the data lake using T-SQL, enabling near real-time dashboards and historical reports without data movement. This service is designed to handle both streaming and batch workloads in a single workspace, meeting all the stated requirements.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Analytics

Answer

Azure HDInsight

Question 13

A social media application stores user posts in Azure Cosmos DB. Each document contains fields: PostID (unique), UserID, Timestamp, Content, LikesCount. The most common query retrieves all posts by a specific UserID ordered by Timestamp descending. Which partition key and indexing strategy minimizes Request Unit (RU) consumption?

Accepted Answer

Partition key: UserID; Index: range on Timestamp. Option B is correct because the query filters on UserID, so setting UserID as the partition key ensures all posts for a user are in the same physical partition, avoiding cross-partition queries. Adding a range index on Timestamp allows efficient sorting without additional RU overhead, as Cosmos DB can use the index to return results in descending order directly.

Answer

Partition key: PostID; Index: range on Timestamp

Answer

Partition key: Timestamp; Index: range on UserID

Answer

Partition key: UserID; Index: composite on (UserID, PostID)

Question 14

An e-commerce application uses Azure SQL Database. The Orders table stores millions of rows with columns: OrderID (primary key, clustered index), CustomerID, OrderDate, OrderStatus, TotalAmount. Queries frequently filter on OrderDate and OrderStatus, and sort results by OrderDate DESC. Which indexing strategy will most improve query performance for these filters and sort?

Accepted Answer

Create a nonclustered index on OrderDate, OrderStatus and include other columns needed by the query.. Option B is correct because a nonclustered index on (OrderDate, OrderStatus) supports both the filter and the sort in a single index seek/scan. SQL Server can use the index to locate rows matching both predicates and return them already sorted by OrderDate DESC without a separate sort operation, which is critical for performance on millions of rows.

Answer

Create a nonclustered index on OrderDate only.

Answer

Create a clustered columnstore index on the table.

Answer

Create a nonclustered index on OrderStatus only.

Question 15

A software-as-a-service (SaaS) provider hosts a multi-tenant application with a separate database for each tenant. They anticipate scaling to thousands of tenants and want to minimize cost while allowing tenants to share resources flexibly. Which Azure SQL offering is most suitable?

Accepted Answer

Azure SQL Database elastic pool. Azure SQL Database elastic pool is the most suitable choice because it allows multiple single databases (one per tenant) to share a fixed set of resources (DTUs or vCores) within a pool, optimizing cost by averaging resource usage across tenants. This model supports scaling to thousands of tenants while providing resource elasticity and predictable pricing, as tenants with low activity can use unused capacity from busy ones without over-provisioning.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 16

An e-commerce application uses Azure SQL Database. During flash sales, the database experiences high CPU usage and query timeouts. The team needs a solution that can handle sudden increases in demand without downtime. Which scaling option should they choose?

Accepted Answer

Hyperscale service tier. The Hyperscale service tier is designed for high-performance, rapidly growing workloads that require instant scalability. It separates compute from storage, allowing compute nodes to be added or scaled up in seconds without downtime, making it ideal for handling sudden spikes in demand like flash sales.

Answer

Read scale-out

Answer

Elastic Pool

Answer

Geo-replication

Question 17

A social media startup stores user profile data, posts, and comments in Azure Cosmos DB. They notice that the logical partition size for a popular user's profile is growing beyond 20 GB, causing performance issues. The current partition key is 'userId'. Which action should they take to solve this?

Accepted Answer

Change the partition key to a synthetic key combining userId and postId. A is correct because the logical partition size limit in Azure Cosmos DB is 20 GB. By using a synthetic partition key that combines 'userId' and 'postId', you distribute the data for the popular user across multiple logical partitions, preventing any single partition from exceeding the 20 GB limit and resolving the performance bottleneck.

Answer

Increase the RU/s

Answer

Split the container into multiple containers by userId range

Answer

Use a different API like MongoDB

Question 18

A company is migrating a 3-TB on-premises SQL Server database to Azure. The database heavily uses cross-database queries with three-part names (e.g., db.schema.table) and relies on SQL Server Agent for scheduled maintenance jobs. They want a fully managed PaaS service with automatic backups and patching, while minimizing application code changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names (db.schema.table) and SQL Server Agent for scheduled maintenance jobs. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks cross-database query support and SQL Agent.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database (elastic pool)

Answer

Azure Synapse Analytics dedicated SQL pool

Question 19

A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?

Accepted Answer

Create a non-clustered index on (CustomerID, OrderDate).. A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.

Answer

Rebuild the clustered index on (OrderDate, CustomerID).

Answer

Create a non-clustered index on OrderDate.

Answer

Create a filtered index on OrderDate for recent dates.

Question 20

A company is migrating a SQL Server database to Azure SQL Database. The database uses CLR (Common Language Runtime) integration for business logic and has database mail configured. The company needs full instance-level functionality while still benefiting from the platform-as-a-service model. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with SQL Server on-premises, including support for CLR integration and Database Mail, while still offering a platform-as-a-service (PaaS) model. Single databases and elastic pools lack these instance-scoped features, and Hyperscale is a scaling option for single databases, not a separate deployment type that adds instance-level functionality.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale