DP-900 Practice Test 6 — 10 Questions

Question 1

A retail application uses Azure SQL Database. The Products table contains 200,000 rows with columns: ProductID (primary key, clustered), CategoryID, ProductName, Price, StockQuantity. Queries frequently filter on CategoryID and then sort results by Price in descending order. Which indexing strategy will most improve query performance for these operations?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a nonclustered index on (CategoryID, Price) with Price in descending order.. Creates a composite nonclustered index on (CategoryID, Price DESC) that directly supports both the filter (CategoryID equality) and the sort (Price descending) in a single index seek and ordered scan, eliminating the need for a separate sort operation. This is the most efficient strategy because the index is ordered exactly as the query requires, allowing SQL Server to retrieve matching rows in the correct order without additional processing.

Answer

Create a clustered index on CategoryID.

Answer

Create a nonclustered index on CategoryID that includes Price as an included column.

Answer

Create a clustered columnstore index on the table.

Question 2

A bank processes online fund transfers. Each transaction must ensure that either both the debit from the sender's account and the credit to the receiver's account occur, or if any part fails, the entire transaction is rolled back. Which ACID property does this guarantee?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this fund transfer scenario, atomicity guarantees that both the debit and credit operations either complete successfully together or are fully rolled back if any part fails, preventing partial updates that could leave the system in an inconsistent state.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 3

A mobile game developer needs to store player session data. Each session has a unique SessionID, a UserID, a start timestamp, an end timestamp, and a collection of game events (each event is a JSON object). The application requires low-latency point reads by SessionID and the ability to query all sessions for a given UserID within a time range. The schema of game events can vary between sessions (e.g., new event types added frequently). The developer wants a fully managed NoSQL database that supports flexible schemas and secondary indexing. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB with the NoSQL API. Azure Cosmos DB with the NoSQL API is the correct choice because it provides a fully managed, globally distributed NoSQL database with native support for flexible schemas (schemaless JSON documents), low-latency point reads by partition key (SessionID), and automatic secondary indexing for querying by UserID within a time range. Its ability to handle varying game event schemas without schema migrations makes it ideal for this use case.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure Cache for Redis

Question 4

A company uses Azure SQL Database for a reporting application. The database is mostly idle during weekdays but experiences heavy load on weekends when reports are generated. They want to minimize costs by only paying for compute resources when the database is active. Which Azure SQL Database pricing model should they choose?

Accepted Answer

Serverless. The Serverless pricing model for Azure SQL Database automatically pauses the database during periods of inactivity (e.g., weekdays) and resumes it when load increases (e.g., weekends), charging only for compute resources consumed during active periods. This aligns perfectly with the described workload pattern of mostly idle weekdays and heavy weekend usage, minimizing costs by eliminating charges for idle compute.

Answer

Provisioned DTU

Answer

Provisioned vCore

Answer

Hyperscale

Question 5

A company stores large archives of legal documents in Azure Blob Storage. The documents must remain immutable; they cannot be modified or deleted for 7 years due to regulatory requirements. The data is accessed only for compliance audits, which occur less than once a year. The company wants to minimize storage costs while ensuring immutability and data durability. Which combination of features should they configure?

Accepted Answer

Archive access tier with a time-based retention policy. The Archive access tier provides the lowest storage cost for data that is rarely accessed, such as legal documents accessed less than once a year. A time-based retention policy enforces immutability for a fixed 7-year period, preventing modifications or deletions. This combination meets regulatory requirements while minimizing storage costs.

Answer

Cool access tier with a time-based retention policy

Answer

Hot access tier with versioning enabled

Answer

Archive access tier with legal hold

Question 6

A company runs a global e-commerce application on Azure SQL Database. The application has a read-intensive workload with millions of users querying product details simultaneously. The database is experiencing high read latency during peak hours due to the volume of concurrent read requests. The company wants to scale read performance without changing the application code and without affecting write operations. Which Azure SQL Database feature should they implement?

Accepted Answer

Active geo-replication. Active geo-replication creates readable secondary replicas of the Azure SQL Database in different Azure regions. By configuring read-only routing to these secondaries, the application can offload read queries from the primary database, scaling read performance without any code changes and without impacting write operations on the primary.

Answer

Elastic pools

Answer

In-memory OLTP

Answer

Columnstore indexes

Question 7

A logistics company stores sensor data from delivery trucks in Azure Table Storage. Each sensor reading includes a TruckID, Timestamp, Location, and EngineTemperature. The most common query retrieves all readings for all trucks within a specific one-hour time window (e.g., between 10:00 and 11:00 on a given day). Currently, the table uses PartitionKey = TruckID and RowKey = Timestamp (ISO format). However, queries filtering by time range are slow and consume many transactions. Which design change will most improve the performance of these time-range queries?

Accepted Answer

Change PartitionKey to a date-based value (e.g., YYYY-MM-DD) and RowKey to a composite of TruckID and Timestamp.. Azure Table Storage queries are most efficient when they target a specific PartitionKey and a range of RowKey values. By setting PartitionKey to a date-based value (e.g., YYYY-MM-DD), all readings for a given day are co-located in the same partition. Then, using a composite RowKey of TruckID and Timestamp allows the query to filter by time range within that partition using a single partition scan, drastically reducing the number of transactions and improving performance.

Answer

Change RowKey to be a composite of TruckID and Timestamp while keeping PartitionKey as TruckID.

Answer

Use Azure Cosmos DB with a partition key on Timestamp instead of Azure Table Storage.

Answer

Enable indexing on the Timestamp column in Azure Table Storage.

Question 8

A manufacturer collects sensor data from thousands of IoT devices every second. The data is ingested into Azure Event Hubs and then needs to be stored for historical analysis. The analytics team will run complex aggregations and time-series queries over petabytes of data, expecting fast results even with large scans. Which Azure service should be used as the analytical data store?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is the correct choice because it is a massively parallel processing (MPP) engine designed for petabyte-scale data warehousing. It can run complex aggregations and time-series queries with fast results by distributing data across 60 distributions and using columnstore indexes for high compression and scan efficiency.

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 9

A manufacturing company collects sensor data from factory equipment as a continuous stream of events ingested into Azure Event Hubs. Additionally, the company receives daily inventory CSV files uploaded to Azure Data Lake Storage Gen2. The analytics team needs to build near real-time dashboards that combine streaming sensor data with batch inventory data, and also support historical reporting by querying data directly in the data lake using SQL without moving it. Which Azure service should they choose as the primary analytics platform?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest both real-time streaming data from Azure Event Hubs and batch data from Azure Data Lake Storage Gen2. Its SQL Serverless feature allows querying data directly in the data lake using T-SQL without moving it, enabling near real-time dashboards and historical reporting in a single service.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure HDInsight with Spark

Question 10

A company uses Azure SQL Database to store customer order data. They need to automatically track changes to the 'OrderStatus' column in the 'Orders' table. They want to be able to query the current status and also easily retrieve historical status changes for a given order without writing custom triggers or history tables. Which feature should they enable?

Accepted Answer

Temporal tables. Temporal tables (system-versioned) automatically track full row history, including changes to the 'OrderStatus' column, by maintaining a paired history table. This allows querying both current and historical states with simple T-SQL clauses like FOR SYSTEM_TIME, without custom triggers or manual history tables.

Answer

Change Data Capture (CDC)

Answer

Automatic tuning

Answer

Geo-replication