DP-900 Practice Test 27 — 15 Questions

Question 1

A database system must ensure that when a transfer of funds between two accounts is processed, if the system crashes after debiting the first account but before crediting the second, the database automatically undoes the debit. This property is best described as:

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. If the system crashes after debiting one account but before crediting the other, the database's transaction log records the partial changes, and during recovery, the database engine (e.g., SQL Server's ARIES recovery model) performs an automatic rollback of the uncommitted transaction, undoing the debit to maintain atomicity.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 2

A social media application stores user profiles in Azure Cosmos DB using the NoSQL API. Each profile includes UserID, Name, Email, and an array of Posts. The most common query retrieves a user's profile by UserID. The application requires strong consistency for writes so that once a profile is updated, all subsequent reads see the latest data. To minimize Request Unit (RU) consumption, which partition key should be chosen?

Accepted Answer

UserID. UserID is the correct partition key because it is the primary filter in the most common query (retrieving a profile by UserID), ensuring each query targets a single logical partition. This minimizes cross-partition queries and RU consumption. Additionally, UserID provides high cardinality and even distribution, which prevents hot partitions and supports the required strong consistency for writes.

Answer

Email

Answer

Name

Answer

A synthetic partition key combining UserID and Region

Question 3

A retail company needs to analyze streaming clickstream data from their website to detect shopping cart abandonment in real-time. They want to use Azure Stream Analytics to output results that can be visualized on a live dashboard. Which output sink allows the fastest data visualization for a real-time dashboard in Power BI?

Accepted Answer

Power BI dataset. Power BI dataset is the correct output sink because Azure Stream Analytics can directly stream data into a Power BI dataset via the Power BI output adapter, enabling real-time dashboard updates with sub-second latency. This integration uses the Power BI REST API to push streaming data events, which Power BI then visualizes immediately without requiring intermediate storage or batch processing.

Answer

Azure Blob Storage

Answer

Azure Event Hubs

Answer

Azure SQL Database

Question 4

A company collects customer feedback forms. Each form contains always-present fields like CustomerID and SubmissionDate, but also a free-text Comments field and optional fields like Rating or ProductCategory that vary between forms. How should this data be classified?

Accepted Answer

Semi-structured data. The customer feedback forms contain a mix of structured fields (CustomerID, SubmissionDate) that follow a fixed schema and unstructured fields (free-text Comments) plus optional fields (Rating, ProductCategory) that may or may not be present. This combination of schema-optional and schema-fixed data within the same record is the hallmark of semi-structured data, which does not require a rigid schema like a relational table but still has some organizational properties (e.g., tags or key-value pairs). In Azure, this data is well-suited for storage in Azure Cosmos DB (using JSON documents) or Azure Blob Storage with metadata, rather than a strictly relational database.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 5

A social media platform stores user posts as JSON documents. Each document contains text content, image URLs, timestamps, and user tags. The structure is consistent for most fields, but users can add custom key-value pairs. How should this data be classified?

Accepted Answer

Semi-structured data. The data is semi-structured because it has a consistent schema for most fields (text, image URLs, timestamps, user tags) but allows custom key-value pairs, which introduces schema flexibility. JSON documents inherently support this mix of fixed and variable attributes, fitting the semi-structured data classification. This aligns with Azure Cosmos DB's handling of JSON items, where each document can have a different set of properties.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 6

A data engineering team needs to build a pipeline that ingests streaming data from IoT devices into Azure Data Lake Storage Gen2. The data arrives as JSON messages. They want to use a service that can capture the streaming data in near real-time and store it as files in the data lake without writing custom code for the ingestion. Which Azure service should they use?

Accepted Answer

Azure Event Hubs with Capture. Azure Event Hubs with Capture is the correct choice because it natively ingests streaming JSON data from IoT devices in near real-time and automatically writes the data to Azure Data Lake Storage Gen2 as files without requiring any custom code. The Capture feature automatically persists the event stream to the specified storage destination at defined time or size intervals, making it ideal for serverless, code-free ingestion.

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Answer

Azure Synapse Pipelines

Question 7

A data warehouse team uses Azure Synapse Analytics dedicated SQL pool to serve both business executives running weekly reports and data scientists running complex ad-hoc queries on large fact tables. The ad-hoc queries often consume excessive resources and degrade performance for the weekly reports. The team needs to ensure that the weekly reports always get guaranteed resources regardless of other concurrent queries. Which Synapse feature should they use?

Accepted Answer

Workload classification. Workload classification in Azure Synapse Analytics dedicated SQL pool allows the team to assign incoming queries to specific workload groups with predefined resource allocations. By classifying the weekly report queries into a group with guaranteed minimum resources (e.g., using `CREATE WORKLOAD CLASSIFIER` with `IMPORTANCE` and `REQUEST_MIN_RESOURCE_PERCENT`), the team ensures those queries always receive the necessary resources, even when ad-hoc data scientist queries are running concurrently. This directly addresses the need for predictable performance for critical reports.

Answer

Result set caching

Answer

Materialized views

Answer

Columnstore indexes

Question 8

A travel booking application stores user itineraries in Azure Cosmos DB using the NoSQL API. Each itinerary document contains: UserID (unique to user), ItineraryID, Destination, BookingDate, and a nested array of Activities. The most common query is: 'Retrieve all itineraries for a specific UserID sorted by BookingDate descending.' To minimize Request Unit (RU) consumption, which partition key should be chosen?

Accepted Answer

UserID. UserID is the correct partition key because the most common query filters on UserID, ensuring that all itineraries for a specific user are stored in the same physical partition. This allows the query to target a single partition, minimizing cross-partition queries and reducing Request Unit (RU) consumption. A partition key that aligns with the primary query filter is essential for optimal performance and cost efficiency in Azure Cosmos DB.

Answer

ItineraryID

Answer

Destination

Answer

BookingDate

Question 9

A social media company stores user session data. Each session record must be quickly looked up by user ID and must have strong consistency so that once a session is written, subsequent reads always return the latest data. The company expects billions of session records globally and needs low-latency reads/writes. Which Azure data store best meets these requirements?

Accepted Answer

Azure Cosmos DB (SQL API). Azure Cosmos DB with SQL API is the correct choice because it offers single-digit millisecond read/write latencies at any scale, global distribution, and tunable consistency levels including strong consistency. Strong consistency ensures that once a write is acknowledged, all subsequent reads return the latest data, which is critical for session state where stale reads could cause authentication or authorization failures. Cosmos DB also supports automatic indexing and partitioning by user ID, enabling fast lookups across billions of records.

Answer

Azure Blob Storage

Answer

Azure Table Storage

Answer

Azure Cache for Redis

Question 10

A company runs an e-commerce platform on Azure SQL Database. The database handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the transactional workload. The company wants to offload the reporting queries to a separate read-only copy of the database to avoid performance impact. Which Azure SQL Database feature should they enable?

Accepted Answer

Read scale-out. Read scale-out (C) is the correct feature because it allows Azure SQL Database to offload read-only workloads, such as complex reporting queries, to a separate read-only replica. This is achieved by using the `ApplicationIntent=ReadOnly` connection string parameter, which routes queries to a secondary replica, thereby preventing performance impact on the primary transactional (OLTP) workload. This feature is specifically designed for scenarios where you need to isolate reporting from high-concurrency OLTP operations without requiring a separate database copy.

Answer

Hyperscale service tier

Answer

Geo-replication

Answer

Elastic query

Question 11

A financial company needs to store transactional records where each record has a fixed set of attributes (TransactionID, Amount, Date, AccountID). The data must support complex queries and enforce referential integrity. Which type of data store is most appropriate?

Accepted Answer

Relational database. A relational database (option C) is the most appropriate choice because transactional records with a fixed schema and the need for referential integrity (e.g., ensuring AccountID references a valid account) are best handled by a structured, ACID-compliant system like Azure SQL Database or SQL Server. Relational databases enforce constraints such as foreign keys and support complex queries using JOINs and aggregations, which are essential for financial reporting and auditing.

Answer

Key-value store

Answer

Document database

Answer

Graph database

Question 12

A company stores customer order data in a relational database table with columns like OrderID, CustomerID, and OrderDate. They also store product images as JPEG files, and customer feedback as JSON documents with varying fields. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

B: Relational table, JSON documents, JPEG files. Relational tables enforce a fixed schema with rows and columns, making them the most structured. JSON documents have a flexible schema with varying fields, placing them in the middle. JPEG files are binary blobs with no inherent structure for querying, making them the least structured. Option B correctly orders these from most structured (relational table) to least structured (JPEG files).

Answer

A: JSON documents, Relational table, JPEG files

Answer

C: JPEG files, Relational table, JSON documents

Answer

D: Relational table, JPEG files, JSON documents

Question 13

A hospital stores patient records. Each record includes a PatientID (integer), Name (text), DateOfBirth (date), and MRI scan images (binary files). Which classification best describes the MRI scan images?

Accepted Answer

Unstructured data. MRI scan images are binary files that lack a predefined data model or schema, making them unstructured data. Unlike structured data (e.g., rows in a SQL table) or semi-structured data (e.g., JSON with tags), binary image files cannot be easily queried or organized using traditional relational database tools without additional processing.

Answer

Structured data

Answer

Semi-structured data

Answer

Streaming data

Question 14

Refer to the exhibit. You are designing a fact table for a data warehouse. The table will store sales transactions with daily granularity. Which column would be most appropriate as the distribution column in a hash-distributed table in Azure Synapse Analytics?

Accepted Answer

CustomerKey. CustomerKey (B) is the most appropriate distribution column because it has high cardinality and is frequently used in joins with dimension tables, ensuring data is evenly distributed across distributions in Azure Synapse Analytics. A hash-distributed table requires a column with many unique values to avoid data skew, and CustomerKey is a natural key for sales transactions that meets this requirement.

Answer

SalesAmount

Answer

ProductKey

Answer

OrderDate

Question 15

A company stores an employee database in a relational database. The Employees table includes columns: EmployeeID (integer), FirstName (text), LastName (text), HireDate (date), and a column called Photo which stores the employee's photo as a binary large object (BLOB). Which statement best describes the data types in this table?

Accepted Answer

The Photo column stores unstructured data, while the other columns store structured data.. The Photo column stores a binary large object (BLOB), which is unstructured data because it does not have a predefined schema or format that can be easily queried or indexed by relational operations. In contrast, EmployeeID, FirstName, LastName, and HireDate are all structured data types (integer, text, date) that conform to a fixed schema and support direct querying, sorting, and indexing. This distinction is fundamental in Azure data services, where structured data is typically stored in Azure SQL Database or Azure Synapse, while unstructured BLOBs are better suited for Azure Blob Storage.

Answer

All columns store structured data.

Answer

All columns store unstructured data.

Answer

The HireDate column stores semi-structured data.