DP-900 Practice Test 24 — 15 Questions

Question 1

A retail chain needs to blend two data sources for a near real-time dashboard: daily batch files from store systems (CSV files on Azure Blob Storage updated once per day) and live web clickstream data from Azure Event Hubs. The dashboard must refresh every 5 minutes with combined data. Which combination of Azure services should be used to ingest and process both data types most efficiently?

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

Accepted Answer

C) Azure Synapse Pipelines + Azure Stream Analytics. Azure Synapse Pipelines can orchestrate the daily batch CSV files from Azure Blob Storage, while Azure Stream Analytics processes the live web clickstream data from Azure Event Hubs in near real-time. Together, they enable a combined data pipeline that refreshes every 5 minutes, meeting the dashboard's latency requirement efficiently.

Answer

A) Azure Data Factory + Azure Analysis Services

Answer

B) Azure Stream Analytics + Power BI

Answer

D) Azure Databricks + Azure Data Lake Storage

Question 2

A financial institution runs complex analytical queries on trading data stored in Parquet files in Azure Data Lake Storage Gen2. The data is partitioned by date and contains billions of rows. Analysts frequently query within a specific date range, and the queries must return results in under 5 seconds. The current solution uses Azure Synapse Serverless SQL pool, but queries are slow because the serverless pool scans all partitions even when the WHERE clause filters on the date column. Which optimization should be implemented to improve query performance?

Accepted Answer

Switch to Azure Synapse dedicated SQL pool with proper table partitioning. Azure Synapse Serverless SQL pool does not support partition elimination based on the partitioning of the underlying Parquet files in Azure Data Lake Storage Gen2. By switching to an Azure Synapse dedicated SQL pool with proper table partitioning on the date column, the query engine can perform partition pruning, scanning only the relevant partitions for the specified date range, which drastically reduces I/O and improves query performance to meet the sub-5-second requirement.

Answer

Create a clustered columnstore index on the external table

Answer

Convert the Parquet files to CSV format

Answer

Use Azure Databricks with Delta Lake for querying

Question 3

A retail company needs to run complex SQL queries on petabytes of historical sales data stored in Parquet files in Azure Data Lake Storage Gen2. They want a solution that provides fast query performance without managing infrastructure, and they prefer a pay-per-query pricing model. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows querying petabytes of Parquet files in Azure Data Lake Storage Gen2 using T-SQL without provisioning any infrastructure, and it charges per terabyte of data processed (pay-per-query). This matches the requirements for fast query performance on historical sales data with a serverless, consumption-based pricing model.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight with Hive

Question 4

A company stores customer data in a relational database. The database design includes a rule that every order must be associated with a valid customer ID that exists in the Customers table. This rule is an example of which data concept?

Accepted Answer

Referential integrity. Referential integrity ensures that relationships between tables remain consistent. In a relational database, a foreign key constraint enforces that every order's customer ID must match an existing customer ID in the Customers table, preventing orphaned records. This rule directly implements referential integrity as defined by the SQL standard (e.g., via FOREIGN KEY constraints).

Answer

Data normalization

Answer

Entity integrity

Answer

Data consistency

Question 5

A company is developing a web application that stores user profiles as JSON documents. The application needs to query these documents using SQL-like queries, and must support automatic indexing of all properties. They want a fully managed, globally distributed NoSQL database with low latency. Which Azure Cosmos DB API should they use?

Accepted Answer

SQL API. The SQL API (formerly DocumentDB API) is the correct choice because it natively supports querying JSON documents with SQL-like syntax (SELECT * FROM c WHERE c.property = value). It automatically indexes all properties by default, provides a fully managed, globally distributed NoSQL database with low-latency reads and writes, and is designed specifically for document-based workloads like user profiles.

Answer

Table API

Answer

Cassandra API

Answer

Gremlin API

Question 6

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:

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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