DP-900 Practice Test 3 — 30 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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

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 social media application displays the number of posts each user has created. After a user submits a new post, the count must reflect the update across all servers within a few seconds. Which data consistency model best describes this requirement?

Accepted Answer

Eventual consistency. Eventual consistency is correct because the requirement allows a few seconds for the update to propagate across all servers, meaning the system does not guarantee immediate uniformity but will converge to the same count eventually. This is typical in distributed systems like social media applications where high availability and partition tolerance are prioritized over immediate consistency, often using techniques like asynchronous replication.

Answer

Strong consistency

Answer

Sequential consistency

Answer

Causal consistency

Question 10

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 11

A media company stores large video files and associated metadata (title, duration, tags) as JSON documents. The application requires low-latency streaming of videos to users worldwide and the ability to quickly query metadata by tag. Which combination of Azure services should the company use?

Accepted Answer

Azure Blob Storage for videos and Azure Cosmos DB for metadata. Azure Blob Storage is optimized for storing large binary objects like video files, offering high-throughput streaming via HTTP/HTTPS and integration with CDN for low-latency global delivery. Azure Cosmos DB provides single-digit millisecond read and write latencies with automatic indexing, making it ideal for quickly querying JSON metadata by tag using SQL or MongoDB API. This combination separates storage concerns (blobs for raw video, document DB for structured metadata) to meet both streaming and query performance requirements.

Answer

Azure Blob Storage for both videos and metadata

Answer

Azure Cosmos DB for videos and Azure Table Storage for metadata

Answer

Azure Files for videos and Azure SQL Database for metadata

Question 12

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 13

A data analyst needs to run ad-hoc SQL queries on petabytes of log data stored as Parquet files in Azure Data Lake Storage Gen2. The queries join multiple tables and require high concurrency from multiple analysts. The solution should minimize cost by only paying for queries executed. 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 running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, making it cost-effective for sporadic, high-concurrency workloads where you only want to pay for the compute resources consumed during query execution.

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 14

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

Question 15

A mobile gaming company stores player activity logs as JSON documents. Each document has a unique ActivityID, a PlayerID, a timestamp, and a variable set of attributes depending on the game event (e.g., level started, item purchased). The application requires low-latency point reads by ActivityID and needs to query logs by PlayerID for a given time range. Schema flexibility is critical because new game events are added frequently. Which Azure Cosmos DB API should they choose?

Accepted Answer

NoSQL API (formerly SQL API). The NoSQL API (formerly SQL API) is the correct choice because it natively supports JSON documents with flexible schemas, enabling the variable attributes required for new game events. It provides low-latency point reads by ActivityID via direct partition key lookups and supports efficient queries by PlayerID within a time range using composite indexes or cross-partition queries with filtering. This API is optimized for schema-agnostic, document-based workloads and offers the richest query capabilities for JSON data in Azure Cosmos DB.

Answer

MongoDB API

Answer

Cassandra API

Answer

Gremlin API

Question 16

A bank processes a fund transfer that involves deducting money from one account and crediting it to another. The system ensures that both operations succeed together or, if any part fails, the entire transaction is rolled back, leaving both accounts unchanged. Which ACID property does this scenario primarily guarantee?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this fund transfer scenario, both the debit and credit operations must complete successfully, or the entire transaction is rolled back, leaving the accounts unchanged. This all-or-nothing behavior is the defining characteristic of atomicity in ACID transactions.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 17

A company uses Azure SQL Database for an order processing system. The Orders table has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. The Customers table has CustomerID (PK), Name, Email. The database administrator wants to ensure that when a customer record is deleted, all orders for that customer are also automatically deleted. Which database constraint should be implemented?

Accepted Answer

ON DELETE CASCADE on Orders.CustomerID. ON DELETE CASCADE on the foreign key (Orders.CustomerID) automatically deletes all child rows in the Orders table when the parent row in the Customers table is deleted. This ensures referential integrity without requiring additional code or triggers, and is the standard SQL mechanism for cascading deletes in Azure SQL Database.

Answer

ON DELETE SET NULL on Orders.CustomerID

Answer

ON UPDATE CASCADE on Customers.CustomerID

Answer

A trigger on Customers table

Question 18

A company's application uses Microsoft SQL Server with multiple databases that need to run complex queries joining tables across databases. They are migrating to Azure and need a fully managed relational database service with high availability, automated backups, and minimal management overhead. They do not need a separate SQL Server installation and want to avoid managing VMs. Which Azure 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 on-premises SQL Server, including support for cross-database queries and linked servers, while being a fully managed platform-as-a-service (PaaS) offering. It eliminates the need to manage VMs or a separate SQL Server installation, and it includes built-in high availability (99.99% SLA) and automated backups, meeting all stated requirements.

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database elastic pool

Question 19

A hospital stores patient vital signs data in Azure Cosmos DB. Each document contains PatientID, Timestamp, HeartRate, BloodPressure, and other measurements. The most common query retrieves all vital signs for a specific patient within a time range (e.g., last 24 hours). Which property should be chosen as the partition key to minimize Request Unit (RU) consumption and ensure even data distribution?

Accepted Answer

PatientID. PatientID is the ideal partition key because the most common query filters by PatientID and a time range. With PatientID as the partition key, Cosmos DB can route the query to a single physical partition containing all documents for that patient, minimizing cross-partition queries and reducing RU consumption. It also ensures even data distribution since each patient generates a similar volume of vital signs data, avoiding hot partitions.

Answer

Timestamp

Answer

HeartRate

Answer

BloodPressure

Question 20

A company needs to run complex SQL queries on petabytes of data stored in Azure Data Lake Storage Gen2. They want to pay only for the queries they run and do not want to manage any infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it enables running complex SQL queries directly against data in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, charging only for the amount of data processed, which aligns with the requirement to pay only for queries run and avoid infrastructure management.

Answer

Azure Synapse dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight