DP-900 Practice Test 7 — 10 Questions

Question 1

A hospital uses Azure SQL Database to store patient records. The database contains tables for patient information, insurance details, and treatment plans. The system must ensure that if a transaction updates a patient's insurance and their treatment plan in two separate rows, either both updates succeed or both fail. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In Azure SQL Database, if a transaction updates both the insurance and treatment plan rows, atomicity guarantees that either both updates are committed or both are rolled back, preventing partial updates that could leave data in an inconsistent state.

Answer

Durability

Answer

Consistency

Answer

Isolation

Question 2

A retail company stores three types of customer data: (1) a table with columns for CustomerID, Name, and Email; (2) product reviews as JSON documents with varying fields such as rating and comment; (3) product demonstration videos stored in MP4 format. Which of the following correctly classifies these data types in order from first to third?

Accepted Answer

Structured, semi-structured, unstructured. Option C is correct because the customer table with fixed columns (CustomerID, Name, Email) is structured data, product reviews as JSON documents with varying fields are semi-structured data (schema-on-read, flexible fields), and MP4 video files are unstructured data (no schema, binary format). This ordering matches the standard classification in Azure Data Fundamentals: structured → semi-structured → unstructured.

Answer

Structured, unstructured, semi-structured

Answer

Semi-structured, structured, unstructured

Answer

Unstructured, semi-structured, structured

Question 3

A medical imaging company stores high-resolution MRI scans in Azure Blob Storage. The scans are accessed frequently for the first 6 months after being generated, then rarely after that, but must be available immediately when accessed for comparisons. The company wants to minimize storage costs. Which Azure Blob Storage access tier should they use for scans older than 6 months?

Accepted Answer

Cool access tier. The Cool access tier is ideal for data that is infrequently accessed but must be available immediately when needed. It offers lower storage costs than the Hot tier while maintaining low-latency retrieval, matching the requirement for scans older than 6 months that are rarely accessed but require instant availability.

Answer

Hot access tier

Answer

Archive access tier

Answer

Premium access tier

Question 4

A hospital uses Azure SQL Database to store patient appointment records. The 'Appointments' table has columns: AppointmentID (primary key), PatientID, DoctorID, AppointmentDate, and Status. Queries frequently filter by DoctorID (equality) and AppointmentDate (range) to retrieve a doctor's schedule. Currently, these queries are slow. Which index strategy will most improve performance for these queries?

Accepted Answer

Add a nonclustered index on (DoctorID, AppointmentDate).. Option B is correct because a nonclustered index on (DoctorID, AppointmentDate) supports both equality filtering on DoctorID and range filtering on AppointmentDate. This index structure allows SQL Server to perform a single index seek for the doctor, then a range scan within that doctor's appointments, avoiding a full table scan. The order of columns matters: the leading column (DoctorID) handles the equality predicate, and the second column (AppointmentDate) handles the range predicate efficiently.

Answer

Add a clustered index on AppointmentID.

Answer

Add a columnstore index on the Status column.

Answer

Add a nonclustered index on (AppointmentDate, DoctorID).

Question 5

A mobile gaming company stores player profiles in Azure Cosmos DB. Each profile document contains many optional fields, and queries frequently filter by the player's locale (a field present in about 30% of documents). Which approach will optimize query performance for these filters?

Accepted Answer

Create a composite index that includes the locale field. Option D is correct because creating a composite index that includes the locale field allows Azure Cosmos DB to efficiently filter queries by locale without scanning every document. Since locale is present in only 30% of documents, a composite index reduces the query RU cost by directly locating matching documents, leveraging the index's sorted structure for faster lookups.

Answer

Embed all fields in a single document and rely on automatic indexing

Answer

Normalize the data by storing locale in a separate container and use cross-container queries

Answer

Define a fixed schema for all documents to ensure every document has the locale field

Question 6

A retail company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and OrderDate, and sort results by OrderDate in descending order. The queries also return the TotalAmount. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount).. Option B is correct because it creates a covering nonclustered index that supports both the filter predicates (CustomerID and OrderDate) and the sort order (OrderDate DESC) while including TotalAmount as an included column to avoid key lookups. This index allows SQL Server to satisfy the query entirely from the index pages, minimizing I/O and improving performance.

Answer

Maintain the existing clustered index on OrderID only.

Answer

Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount).

Answer

Create a clustered columnstore index on the entire table.

Question 7

A travel booking application stores booking data in Azure Cosmos DB using the NoSQL API. Each booking document contains: BookingID (unique), UserID, Destination, TravelDate, Price. The most common query is: 'Retrieve all bookings for a specific UserID, sorted by TravelDate descending.' To minimize Request Unit (RU) consumption, which property should be chosen as the partition key?

Accepted Answer

UserID. UserID is the correct partition key because the most common query filters on UserID, and Cosmos DB routes queries to the exact physical partition(s) containing that UserID's data. This minimizes cross-partition fan-out, reducing RU consumption. A partition key should align with the primary query filter to enable efficient point-read or single-partition query execution.

Answer

BookingID

Answer

Destination

Answer

TravelDate

Question 8

A retail company runs analytical reporting queries on a large Sales table in Azure SQL Database. The table contains over 100 million rows and is updated daily with new transactions. The queries aggregate data by product and month, scanning millions of rows per query. The company wants to significantly reduce query execution time without changing the queries. Which indexing strategy should they implement?

Accepted Answer

Create a clustered columnstore index on the table.. A clustered columnstore index is ideal for large data warehousing and analytical workloads because it stores data column-wise, enabling high compression and batch-mode processing. For queries that aggregate millions of rows by product and month, columnstore indexes dramatically reduce I/O and CPU by scanning only the necessary columns and using segment elimination, which directly addresses the requirement to reduce query execution time without changing the queries.

Answer

Create a nonclustered index on the ProductID column.

Answer

Create a filtered index for the most recent month's data.

Answer

Create a clustered rowstore index (default) and rely on database compression.

Question 9

A logistics company collects sensor data from delivery trucks. Each sensor sends a JSON message that includes a fixed set of core fields (truck ID, timestamp) but also includes optional fields such as temperature, humidity, and engine diagnostics depending on the sensor type. The JSON structure varies between messages. How should this data be classified?

Accepted Answer

Semi-structured data. The JSON messages contain a fixed set of core fields (truck ID, timestamp) but also include optional fields that vary per message, meaning the data has a flexible schema. This mixture of structured fields and variable attributes is the defining characteristic of semi-structured data, which does not require a rigid schema like a relational table but still has organizational properties (e.g., key-value pairs). In Azure, this type of data is commonly stored in services like Azure Cosmos DB or Azure Blob Storage with JSON format.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 10

An e-commerce company uses Azure SQL Database for order processing. The Orders table has columns: OrderID (unique, clustered index), CustomerID, OrderDate, Status, TotalAmount. A common query filters on CustomerID and OrderDate, and sorts by OrderDate descending. The query also returns TotalAmount. Which indexing strategy will produce the best query performance?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount). Option A is correct because it creates a covering index that supports both the equality filter on CustomerID and the range/sort on OrderDate DESC. By including TotalAmount as an included column, the query can be satisfied entirely from the nonclustered index without key lookups to the clustered index, minimizing I/O and improving performance.

Answer

Create a nonclustered index on (OrderDate) INCLUDE (CustomerID, TotalAmount)

Answer

Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount)

Answer

Create a nonclustered index on (CustomerID) INCLUDE (OrderDate, TotalAmount)