DP-900 Practice Test 4 — 25 Questions

Question 1

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?

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, 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 2

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 3

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 4

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 5

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

Question 6

A retail company processes customer orders throughout the day. Each order involves inserting a new record into a database table, updating inventory counts, and deleting temporary cart data. At the end of each week, the company runs a query that aggregates all orders by product category and region to generate a sales report. Which of the following best describes these two workloads?

Accepted Answer

Order processing is OLTP; weekly reporting is OLAP. Order processing involves frequent, small transactions (inserts, updates, deletes) that are typical of Online Transaction Processing (OLTP) workloads, which prioritize data integrity and low-latency writes. The weekly sales report aggregates large volumes of historical data by product category and region, which is characteristic of Online Analytical Processing (OLAP) workloads that support complex queries and data summarization. Option C correctly identifies these two distinct workload types.

Answer

Order processing is OLAP; weekly reporting is OLTP

Answer

Order processing is batch processing; weekly reporting is streaming processing

Answer

Both workloads are OLTP

Question 7

A manufacturing company collects temperature and vibration data from thousands of sensors. The data is streamed to Azure Event Hubs. The company wants to store all this raw data in Azure Data Lake Storage Gen2 for future batch analytics. They need a solution that automatically writes the streaming data to the data lake in near real-time, without requiring any custom code for the write operation. Which Azure feature should they use?

Accepted Answer

Azure Event Hubs Capture. Azure Event Hubs Capture is the correct choice because it automatically writes streaming data from Event Hubs to Azure Data Lake Storage Gen2 in near real-time without requiring any custom code. It integrates directly with Event Hubs to buffer and write data in Avro format, meeting the requirement for a no-code, automated solution.

Answer

Azure Stream Analytics job output to Azure Data Lake Storage Gen2

Answer

Azure Data Factory Copy Activity

Answer

Azure Synapse Pipelines

Question 8

A company wants to migrate an on-premises SQL Server database to Azure. The database uses SQL Agent jobs to run nightly ETL processes and relies on Service Broker for asynchronous messaging between applications. They want to minimize changes to the application and database code. 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 on-premises SQL Server, including support for SQL Agent jobs and Service Broker. This allows the company to migrate the database with minimal code changes, as these features are not available in Azure SQL Database (single or elastic pool). SQL Server on Azure VMs would also support these features but requires more management overhead and is not a fully managed PaaS option.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 9

A database system ensures that a transaction either completes fully and all changes are applied, or it is completely rolled back and no partial changes are saved. Which property of ACID transactions does this describe?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails, the entire transaction is rolled back, leaving the database in its original state. This property guarantees that no partial changes are saved, which directly matches the description in the question.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 10

A startup is building a new mobile app that will track user fitness activities. They need a relational database to store user profiles, activity logs, and goals. The database must be easy to set up, require minimal administration, and automatically scale during peak usage. The startup has a limited budget and prefers a consumption-based pricing model. Which Azure service should they choose?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it provides a consumption-based pricing model that automatically pauses during inactivity and scales compute resources based on demand, requiring minimal administration. This aligns perfectly with the startup's need for easy setup, minimal administration, automatic scaling during peak usage, and a limited budget.

Answer

Azure Database for MySQL serverless

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Question 11

A company stores terabytes of web server log data in CSV files in Azure Data Lake Storage Gen2. Data analysts need to run ad-hoc SQL queries on this data to analyze user behavior patterns. The queries are complex, involve joins across multiple files, and the analysts prefer not to move the data into a separate store. 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 analysts to run T-SQL queries directly against CSV files stored in Azure Data Lake Storage Gen2 without moving the data. It uses a distributed query engine to process complex joins across multiple files, making it ideal for ad-hoc analytics on large-scale log data.

Answer

Azure Data Factory

Answer

Azure SQL Database

Answer

Azure HDInsight

Question 12

A company runs a real-time dashboard in Power BI that displays sales data from Azure Synapse Analytics. The dashboard must show data with less than 5 seconds of latency. Which Azure service should be used to ingest streaming sales events into Azure Synapse Analytics?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, capable of ingesting high-velocity streaming sales events and outputting them to Azure Synapse Analytics with sub-second latency. This meets the requirement of less than 5 seconds of latency for the Power BI dashboard.

Answer

Azure SQL Database

Answer

Azure Data Factory

Answer

Azure Databricks

Question 13

A financial services company is evaluating distributed NoSQL databases for a new application that must remain fully available even during network partitions. The application can tolerate stale reads for some types of queries. Which statement accurately describes the trade-off described by the CAP theorem in this context?

Accepted Answer

When a network partition occurs, a distributed system must choose between providing consistency and providing availability.. The CAP theorem states that during a network partition (P), a distributed system must choose between consistency (C) and availability (A). Since the application requires full availability even during partitions, it must sacrifice strong consistency in favor of eventual consistency, which tolerates stale reads. Option B correctly captures this fundamental trade-off.

Answer

During a network partition, the system can maintain both consistency and availability.

Answer

Partition tolerance is an optional property and can be sacrificed to achieve both consistency and availability.

Answer

Availability guarantees that every read returns the most recent write.

Question 14

A banking application processes a funds transfer transaction consisting of two steps: debit $100 from Account A and credit $100 to Account B. If the system crashes after debiting Account A but before crediting Account B, the database automatically reverts the debit, restoring Account A to its original balance. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the debit and credit are part of one transaction; if the system crashes after the debit but before the credit, the database management system (DBMS) automatically rolls back the entire transaction, undoing the debit to restore Account A's original balance. This all-or-nothing behavior is the defining characteristic of atomicity.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 15

A financial services company runs a single SQL Server database that is 6 TB in size and handles a high volume of concurrent transactions. The database needs to support near real-time analytics without impacting OLTP performance. The company wants to migrate to Azure SQL Database and requires fast scale-out for read workloads, as well as the ability to independently scale compute and storage. Which Azure SQL Database service tier should they choose?

Accepted Answer

Hyperscale. Hyperscale is the correct choice because it is designed for databases up to 100 TB, supports high-volume concurrent transactions, and provides near real-time read scale-out via named replicas that offload read workloads without affecting OLTP performance. It also allows independent scaling of compute (vCores) and storage (auto-scaled), meeting the company's requirements for fast scale-out and decoupled resources.

Answer

Serverless

Answer

Business Critical

Answer

General Purpose

Question 16

A global social media platform stores user profile images (JPEG) and activity logs in JSON format. The logs have varying structures based on the type of activity. The application requires low-latency reads of images from any region and the ability to query logs using SQL-like syntax. Which Azure data storage solution should they use for each data type?

Accepted Answer

Azure Blob Storage with a CDN for images and Azure Cosmos DB (SQL API) for logs. Azure Blob Storage is optimized for storing large binary objects like JPEG images, and integrating it with Azure CDN ensures low-latency reads globally by caching content at edge nodes. Azure Cosmos DB with the SQL API provides native support for querying JSON documents with varying schemas using SQL-like syntax, making it ideal for the activity logs.

Answer

Azure Table Storage for images and Azure Cosmos DB (Table API) for logs

Answer

Azure Files for images and Azure SQL Database for logs

Answer

Azure Disk Storage for images and Azure Cosmos DB (MongoDB API) for logs

Question 17

A data engineer is designing a data lake architecture in Azure. They plan to first ingest raw data from various sources into a landing zone in Azure Data Lake Storage Gen2. Then they will clean, validate, and deduplicate that data in a second zone. Finally, they will create aggregated, business-ready datasets in a third zone for analysts. This layered approach is known as which architecture?

Accepted Answer

Medallion architecture. The medallion architecture (bronze, silver, gold) is a layered data lake design pattern where raw data lands in the bronze zone, is cleaned and deduplicated in the silver zone, and aggregated into business-ready datasets in the gold zone. This directly matches the described three-zone ingestion, transformation, and aggregation pipeline in Azure Data Lake Storage Gen2.

Answer

Star schema

Answer

Snowflake schema

Answer

Lambda architecture

Question 18

A bank processes individual customer transactions in real-time to update account balances and also runs a nightly job that aggregates all daily transactions into summary reports for management. Which of the following best describes these two processing workloads?

Accepted Answer

OLTP for real-time transactions, OLAP for nightly reports. Real-time individual transaction processing is the hallmark of Online Transaction Processing (OLTP), which focuses on high-volume, low-latency inserts and updates to maintain current account balances. The nightly aggregation of daily transactions into summary reports is a classic Online Analytical Processing (OLAP) workload, which involves complex queries over large historical datasets for business intelligence. These two workloads have fundamentally different performance and design requirements, making OLTP and OLAP the appropriate classifications.

Answer

Batch processing for transactions, Stream processing for reports

Answer

OLAP for transactions, OLTP for reports

Answer

ETL for transactions, ELT for reports

Question 19

A company has multiple independent databases for different business units, each with low to moderate usage and varying workload patterns. They want to consolidate these databases into a single Azure SQL Database deployment option to share resources and reduce costs, while ensuring that databases do not starve each other of resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed for exactly this scenario: multiple databases with low to moderate usage and varying workload patterns. They allow databases to share a fixed pool of resources (eDTUs or vCores) while using built-in resource governance to prevent any single database from starving others, thus optimizing cost and performance.

Answer

Single database (DTU model)

Answer

Managed Instance

Answer

Database per server (hyperscale)

Question 20

A manufacturing company stores IoT sensor data as JSON documents in Azure Cosmos DB. Each document has fields: deviceId (high cardinality, many unique values), timestamp, temperature, and humidity. The most frequent query is: 'Retrieve all readings for a specific deviceId from the last hour.' To minimize Request Unit (RU) consumption, which combination of partition key and indexing policy should be chosen?

Accepted Answer

Partition key: deviceId, Indexing: automatic on all properties. DeviceId is the most frequently filtered attribute (in the WHERE clause), making it an ideal partition key that ensures queries are scoped to a single physical partition, minimizing cross-partition fan-out. Automatic indexing on all properties allows efficient filtering on timestamp within the partition, while the index on deviceId is not strictly needed since the partition key itself routes the query, but it does not harm RU consumption significantly. This combination balances query performance and RU cost for the described workload.

Answer

Partition key: timestamp, Indexing: automatic on all properties

Answer

Partition key: deviceId, Indexing: none

Answer

Partition key: temperature, Indexing: automatic on all properties