DP-900 Practice Test 23 — 15 Questions

Question 1

A company is migrating a 1.5 TB on-premises SQL Server database to Azure. The database relies on SQL Server Agent jobs for daily ETL processes and uses linked servers to query data from another on-premises SQL Server database. The company wants a fully managed PaaS service that requires minimal application changes. Which Azure SQL service should they choose?

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

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 SQL Server Agent jobs and linked servers, while being a fully managed PaaS service. This minimizes application changes, as the migration can leverage the existing database code and features without significant rework.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 2

A healthcare application stores patient medical records as JSON documents. Each document contains a variable set of fields depending on the patient's conditions. The application needs to query records by any field and support high write throughput. Which Azure data store is most appropriate?

Accepted Answer

Azure Cosmos DB with SQL API. Azure Cosmos DB with SQL API is the most appropriate choice because it natively supports storing and querying JSON documents with variable schemas, enabling efficient queries on any field. Its multi-model architecture and configurable indexing policies allow high write throughput while maintaining low-latency queries, which is critical for healthcare applications with dynamic patient records.

Answer

Azure Blob Storage

Answer

Azure Synapse Analytics

Answer

Azure Table Storage

Question 3

A company is migrating an on-premises SQL Server database to Azure. The database is 800 GB, uses SQL Server Agent jobs for scheduled tasks, and needs to link to another on-premises SQL Server instance via linked servers. The company wants a fully managed service with minimal application changes. Which Azure SQL service 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 SQL Server Agent jobs and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. This allows the company to migrate the 800 GB database with minimal application changes, as it preserves the existing instance-level features without requiring a rearchitecture.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

Azure Synapse Analytics dedicated SQL pool

Question 4

A company is migrating a 500 GB on-premises SQL Server database to Azure. The database uses SQL Server Agent for scheduled maintenance jobs and requires the ability to run cross-database queries within the same logical server. The company wants a PaaS service that minimizes management overhead for patching and backups while preserving these SQL Server features. 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 SQL Server Agent and cross-database queries within the same instance. As a PaaS service, it handles patching and backups automatically, minimizing management overhead while preserving these required features.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure Database for MySQL

Question 5

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 6

A real-time leaderboard for an online game needs to store player scores and quickly retrieve the top 100 players. The data must update frequently as players achieve new scores, and the application requires sub-millisecond read and write latency. Which Azure data store is best suited for this requirement?

Accepted Answer

Azure Cache for Redis. Azure Cache for Redis is an in-memory data store that provides sub-millisecond read and write latency, making it ideal for real-time leaderboards that require frequent updates and fast retrieval of top scores. Its sorted set data structure (ZADD/ZRANGEBYSCORE) allows efficient insertion of player scores and O(log N) retrieval of the top 100 players without disk I/O overhead.

Answer

Azure Cosmos DB Core (SQL) API

Answer

Azure Table Storage

Answer

Azure Blob Storage

Question 7

A company stores customer records in a relational table with columns like CustomerID, Name, and Email. Product reviews are stored as JSON documents, and marketing images are stored as PNG files. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

B. Customer records, Product reviews, Marketing images. Customer records in a relational table have a fixed schema with defined columns (e.g., CustomerID, Name, Email), making them the most structured. Product reviews stored as JSON documents are semi-structured because they have a flexible schema with key-value pairs but no fixed columns. Marketing images as PNG files are unstructured binary data with no inherent schema. Option B correctly orders these from most to least structured.

Answer

A. Product reviews, Customer records, Marketing images

Answer

C. Marketing images, Customer records, Product reviews

Answer

D. Customer records, Marketing images, Product reviews

Question 8

A logistics company ingests GPS coordinates from delivery trucks in real-time to update a live tracking dashboard. They also run a nightly job to aggregate the day's deliveries into a report stored in Azure SQL Database. Which statement correctly describes the data processing types used for these two workloads?

Accepted Answer

GPS ingestion is stream processing; nightly aggregation is batch processing.. The real-time ingestion of GPS coordinates from delivery trucks is a classic stream processing workload, where data is processed continuously as it arrives with low latency. The nightly aggregation of daily deliveries into a report stored in Azure SQL Database is a batch processing workload, where data is processed in bulk at scheduled intervals. Azure Stream Analytics is commonly used for the streaming ingestion, while Azure SQL Database or Azure Synapse Analytics can handle the batch aggregation.

Answer

GPS ingestion is batch processing; nightly aggregation is stream processing.

Answer

Both workloads are examples of stream processing.

Answer

Both workloads are examples of batch processing.

Question 9

A retail company receives a continuous stream of customer orders from their website via Azure Event Hubs. They also receive daily inventory updates from suppliers as CSV files uploaded to Azure Blob Storage. The company needs to calculate real-time order fulfillment availability by joining the streaming orders with the latest inventory snapshot. Additionally, they generate nightly sales reports from historical order data. Which Azure service should they use for the real-time processing component?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing, allowing you to join streaming data from Event Hubs with static or reference data (like the latest inventory snapshot from Blob Storage) using SQL-like queries. This enables the calculation of real-time order fulfillment availability as orders arrive, which is the core requirement.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines

Question 10

A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?

Accepted Answer

A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount). The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.

Answer

A clustered index on OrderID

Answer

A non-clustered index on Status

Answer

A non-clustered index on (OrderDate, TotalAmount)

Question 11

A financial services company runs large-scale analytical queries on a dedicated SQL pool in Azure Synapse Analytics. They notice that during peak hours, complex aggregations consume excessive resources, causing slower queries from other users. They need to ensure that critical management reports always get enough resources and complete within a guaranteed time, while other less important queries do not starve them. Which feature should they implement?

Accepted Answer

Workload management. Workload management in Azure Synapse Analytics allows you to classify, assign resources, and prioritize queries by creating workload groups and classifiers. By configuring a workload group for critical management reports with a higher importance and a guaranteed minimum resource percentage, you ensure those queries always get sufficient resources and complete within a guaranteed time, while less important queries are throttled and cannot starve the critical ones.

Answer

Result-set caching

Answer

Materialized views

Answer

Columnstore index

Question 12

A global e-commerce platform uses Azure Cosmos DB to store product inventory data. Customers add items to their cart, which reduces the available inventory count. The application requires that after a customer adds an item, any subsequent read of that product's inventory from any region in the world must reflect the reduced count immediately. Which Cosmos DB consistency level should be used?

Accepted Answer

Strong consistency. Strong consistency ensures that any read operation returns the most recent write, regardless of the region. Since the application requires that after a customer adds an item, any subsequent read of that product's inventory from any region must reflect the reduced count immediately, Strong consistency is the only level that guarantees linearizability and zero staleness across all replicas.

Answer

Eventual consistency

Answer

Consistent prefix consistency

Answer

Session consistency

Question 13

A development team is designing a relational database for a hospital patient management system. They need to ensure that each patient's medical record number is unique and not null. Which database constraint should they use?

Accepted Answer

PRIMARY KEY. The PRIMARY KEY constraint enforces both uniqueness and non-nullability on the column(s) it is applied to. In a relational database, the medical record number is the natural candidate for the primary key of the Patient table because it uniquely identifies each patient and must always have a value. This directly meets the requirement that each patient's medical record number is unique and not null.

Answer

FOREIGN KEY

Answer

CHECK

Answer

UNIQUE

Question 14

A data engineer needs to process streaming data from IoT devices and store the results in Azure Data Lake Storage for long-term analytics. The data must be processed in near real-time to detect anomalies and trigger alerts. Which Azure service should the engineer use for stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a serverless, real-time stream processing engine designed to handle high-velocity data from sources like IoT devices. It can ingest data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect anomalies in near real-time, and output results directly to Azure Data Lake Storage for long-term analytics. This makes it the correct choice for the described near-real-time anomaly detection and alerting requirement.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Data Lake Analytics

Question 15

A banking system processes a money transfer between two accounts. The system is designed so that after the transaction is committed, the results are permanently saved and survive any subsequent system failure, such as a power outage. Which ACID property ensures this behavior?

Accepted Answer

Durability. Durability ensures that once a transaction is committed, its changes are permanently stored and survive system failures, such as power outages or crashes. In this banking scenario, the money transfer results are written to non-volatile storage (e.g., disk) via a write-ahead log, guaranteeing that the committed state is recoverable even after a restart.

Answer

Atomicity

Answer

Consistency

Answer

Isolation