DP-900 Practice Test 9 — 10 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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