DP-900 Practice Test 28 — 15 Questions

Question 1

A smart building company stores sensor data from thousands of IoT devices as JSON documents in Azure Cosmos DB using the NoSQL API. Each document contains fields: deviceId (string), timestamp (datetime), temperature (float), humidity (float), and additional device-specific fields (e.g., motionDetected, CO2level). The most common query is: SELECT * FROM c WHERE c.deviceId = 'sensor-123' AND c.timestamp >= '2025-01-01' AND c.timestamp < '2025-02-01' ORDER BY c.timestamp DESC. Which indexing strategy will provide the best performance for this query?

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

Accepted Answer

Create a composite index on (deviceId ASC, timestamp DESC). The query filters on `deviceId` (equality) and `timestamp` (range with ORDER BY DESC). A composite index on `(deviceId ASC, timestamp DESC)` allows Cosmos DB to efficiently locate the partition for the device and then scan the timestamp range in descending order without an in-memory sort, minimizing RU consumption and latency.

Answer

Use the default indexing policy that automatically indexes all properties

Answer

Disable indexing for all properties to speed up writes

Answer

Create a spatial index on the deviceId field

Question 2

A manufacturing company collects sensor data from thousands of IoT devices. Each reading contains a device ID, timestamp, value, and device-specific measurement fields. The company needs to analyze the data in real time to detect anomalies and trigger alerts. They also need to store the same data for historical batch analysis to identify long-term trends. Which architecture pattern best describes this combination of data processing approaches?

Accepted Answer

Lambda architecture. The Lambda architecture is the correct pattern because it combines both stream processing for real-time anomaly detection and alerting, and batch processing for historical analysis of long-term trends. This architecture uses a speed layer for low-latency stream processing (e.g., Apache Kafka, Azure Stream Analytics) and a batch layer for comprehensive, accurate historical computations (e.g., Azure Data Lake, Apache Spark). The serving layer then merges results from both paths to provide a unified view.

Answer

Batch processing only

Answer

Stream processing only

Answer

Data lake

Question 3

A data engineer needs to design a solution for a healthcare organization that must store patient records for 7 years to comply with regulatory requirements. The data will be accessed infrequently after the first year. Which Azure storage tier should be used for data older than one year?

Accepted Answer

Cool tier. The Cool tier is designed for data that is accessed infrequently but must be available immediately when needed, with a minimum storage duration of 30 days. Since patient records older than one year are accessed rarely but may still require low-latency retrieval for compliance audits, Cool tier balances cost and accessibility. Hot tier is for frequent access, Archive tier has a retrieval latency of hours (not suitable for occasional access), and Transaction-optimized is not a standard Azure Blob Storage tier.

Answer

Transaction-optimized tier

Answer

Archive tier

Answer

Hot tier

Question 4

A data analyst uses Power BI to create a report that combines data from Azure Synapse Analytics and an on-premises SQL Server database. The on-premises data must be refreshed every hour. Which component is required to connect to the on-premises data source?

Accepted Answer

On-premises data gateway. The on-premises data gateway is required to securely connect Power BI to on-premises SQL Server databases for scheduled refreshes. It acts as a bridge, transmitting data from the on-premises source to the Power BI service without opening inbound firewall ports. This component is specifically designed for self-service analytics scenarios where cloud services need to access on-premises data sources.

Answer

Azure VPN Gateway

Answer

Azure ExpressRoute

Answer

Azure Data Factory

Question 5

A company is migrating a relational database to Azure SQL Database. They anticipate that the amount of stored data will grow significantly over time, but the compute requirements (CPU and memory) will remain relatively stable. Which purchasing model should they choose to allow independent scaling of storage and compute?

Accepted Answer

vCore-based purchasing model. The vCore-based purchasing model separates compute and storage costs, allowing you to scale storage independently without changing compute resources. This matches the scenario where data grows but compute requirements remain stable, as you can increase storage capacity without upgrading CPU or memory.

Answer

DTU-based purchasing model

Answer

Serverless compute tier

Answer

Hyperscale service tier

Question 6

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Question 7

A retail company uses Azure SQL Database to store a large fact table of sales transactions with millions of rows. They run complex aggregate queries (SUM, COUNT, AVG) across many rows for monthly reports. These queries take too long. Which index type should they add to the table to improve performance?

Accepted Answer

Clustered columnstore index. Clustered columnstore indexes are optimized for large fact tables and analytical workloads because they store data in a columnar format, which significantly reduces the amount of data read from disk for aggregate queries like SUM, COUNT, and AVG. This index type also uses batch processing and compression to accelerate query performance on millions of rows, making it ideal for monthly reporting queries.

Answer

Clustered B-tree index

Answer

Nonclustered rowstore index

Answer

Nonclustered columnstore index

Question 8

A company stores customer data in a relational table with fixed columns: CustomerID (integer), FirstName (string), LastName (string), Email (string). They also store product images as JPEG files, and customer feedback as JSON documents that may contain varying fields such as rating, comment, and optional metadata. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

Relational table, JSON documents, JPEG files. The relational table is the most structured because it enforces a fixed schema with predefined columns and data types (e.g., CustomerID integer, FirstName string). JSON documents are semi-structured: they have a flexible schema where fields like rating and comment can vary per document, but they still provide key-value organization. JPEG files are unstructured binary data with no internal schema or queryable structure, making them the least structured.

Answer

JSON documents, relational table, JPEG files

Answer

JPEG files, JSON documents, relational table

Answer

Relational table, JPEG files, JSON documents

Question 9

A healthcare company stores patient records in a relational database with fixed columns (PatientID, Name, DOB, BloodType). Medical images such as X-rays are stored as DICOM files. Clinical notes are stored as free-text documents. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Patient records (structured), DICOM files (semi-structured), Clinical notes (unstructured). Patient records in a fixed-column relational database are structured data because they conform to a rigid schema with defined data types. DICOM files are semi-structured because they contain a structured header with metadata tags (e.g., patient ID, study date) alongside an unstructured binary image payload. Clinical notes as free-text documents are unstructured because they lack a predefined schema or organization, making them difficult to query without natural language processing.

Answer

Patient records (structured), DICOM files (structured), Clinical notes (unstructured)

Answer

Patient records (semi-structured), DICOM files (unstructured), Clinical notes (structured)

Answer

Patient records (unstructured), DICOM files (semi-structured), Clinical notes (structured)

Question 10

A logistics company tracks shipment locations using GPS devices that send JSON data with fields: shipmentId, latitude, longitude, timestamp, speed. The data is stored in Azure Cosmos DB using the Core (SQL) API. The application needs to query all shipments that are currently within a specific geographic bounding box and have a speed greater than 0. Which query approach should they use to efficiently retrieve the data?

Accepted Answer

Use ST_WITHIN to specify the bounding box polygon and add a WHERE clause for speed.. Azure Cosmos DB's Core (SQL) API supports geospatial queries using the ST_WITHIN function, which efficiently checks if a point (latitude/longitude) lies inside a polygon (bounding box). Adding a WHERE clause for speed > 0 further filters the results, and Cosmos DB can leverage a composite index on the geospatial field and speed to optimize query performance.

Answer

Use a BETWEEN clause on latitude and longitude and a WHERE clause for speed.

Answer

Use ST_DISTANCE to measure distance from a center point and also filter on speed.

Answer

Use the IN operator to list all acceptable coordinate pairs and a speed filter.

Question 11

A company stores user profile images in Azure Blob Storage. Each image is accessed via a URL that includes a Shared Access Signature (SAS) token generated using the storage account key. The company needs to immediately revoke access to all images for a specific user. Which action should they take?

Accepted Answer

Change the storage account access keys.. Changing the storage account access keys invalidates all SAS tokens that were generated using those keys, including any existing tokens. This immediately revokes access to all images for all users, including the specific user, without needing to manage individual tokens. SAS tokens are signed with the account key, so rotating the key renders all tokens generated with the old key invalid.

Answer

Delete the individual SAS tokens associated with that user's images.

Answer

Delete the container containing the user's images.

Answer

Regenerate the SAS token for each image.

Question 12

A company stores customer information in a SQL database table with columns: CustomerID, FirstName, LastName, Email, SignupDate. They also store product images as JPEG files in Azure Blob Storage. Which statement correctly describes the types of data involved?

Accepted Answer

Customer data is structured, product images are unstructured.. Customer data stored in a SQL database table with defined columns (CustomerID, FirstName, LastName, Email, SignupDate) is structured because it adheres to a fixed schema with rows and columns. Product images stored as JPEG files in Azure Blob Storage are unstructured because they lack a predefined data model and are stored as binary large objects (BLOBs) without a schema. Option B correctly identifies this distinction.

Answer

Customer data is unstructured, product images are semi-structured.

Answer

Both are structured.

Answer

Customer data is semi-structured, product images are unstructured.

Question 13

A retail company uploads daily sales data from all stores to Azure Blob Storage at midnight. They then run a series of data transformations using Azure Data Factory on a scheduled trigger at 2:00 AM. This processing pattern is best described as:

Accepted Answer

Batch processing. This pattern is batch processing because the sales data is collected in Azure Blob Storage over a period (daily) and then processed as a group at a scheduled time (2:00 AM) using Azure Data Factory. Batch processing is designed for high-volume, periodic data loads where latency is acceptable, and the transformation job runs on a complete dataset rather than individual records.

Answer

Stream processing

Answer

Transactional processing

Answer

Interactive query

Question 14

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 15

A company stores customer names and addresses in a fixed-format file where each record has the same fields in the same order. This type of data is best described as:

Accepted Answer

Structured data. A fixed-format file where each record has the same fields in the same order is a classic example of structured data. Structured data conforms to a rigid schema, such as a table with defined columns and data types, making it easily searchable and processable by relational database systems like Azure SQL Database. The consistent field order and fixed format allow for direct parsing without interpretation.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Streaming data