DP-900 Practice Test 26 — 15 Questions

Question 1

A real-time leaderboard for an online game needs to store player scores and quickly retrieve the top 100 players. The data must update frequently as players achieve new scores, and the application requires sub-millisecond read and write latency. Which Azure data store is best suited for this requirement?

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

Accepted Answer

Azure Cache for Redis. Azure Cache for Redis is an in-memory data store that provides sub-millisecond read and write latency, making it ideal for real-time leaderboards that require frequent updates and fast retrieval of top scores. Its sorted set data structure (ZADD/ZRANGEBYSCORE) allows efficient insertion of player scores and O(log N) retrieval of the top 100 players without disk I/O overhead.

Answer

Azure Cosmos DB Core (SQL) API

Answer

Azure Table Storage

Answer

Azure Blob Storage

Question 2

A company stores customer records in a relational table with columns like CustomerID, Name, and Email. Product reviews are stored as JSON documents, and marketing images are stored as PNG files. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

B. Customer records, Product reviews, Marketing images. Customer records in a relational table have a fixed schema with defined columns (e.g., CustomerID, Name, Email), making them the most structured. Product reviews stored as JSON documents are semi-structured because they have a flexible schema with key-value pairs but no fixed columns. Marketing images as PNG files are unstructured binary data with no inherent schema. Option B correctly orders these from most to least structured.

Answer

A. Product reviews, Customer records, Marketing images

Answer

C. Marketing images, Customer records, Product reviews

Answer

D. Customer records, Marketing images, Product reviews

Question 3

A logistics company ingests GPS coordinates from delivery trucks in real-time to update a live tracking dashboard. They also run a nightly job to aggregate the day's deliveries into a report stored in Azure SQL Database. Which statement correctly describes the data processing types used for these two workloads?

Accepted Answer

GPS ingestion is stream processing; nightly aggregation is batch processing.. The real-time ingestion of GPS coordinates from delivery trucks is a classic stream processing workload, where data is processed continuously as it arrives with low latency. The nightly aggregation of daily deliveries into a report stored in Azure SQL Database is a batch processing workload, where data is processed in bulk at scheduled intervals. Azure Stream Analytics is commonly used for the streaming ingestion, while Azure SQL Database or Azure Synapse Analytics can handle the batch aggregation.

Answer

GPS ingestion is batch processing; nightly aggregation is stream processing.

Answer

Both workloads are examples of stream processing.

Answer

Both workloads are examples of batch processing.

Question 4

A retail company receives a continuous stream of customer orders from their website via Azure Event Hubs. They also receive daily inventory updates from suppliers as CSV files uploaded to Azure Blob Storage. The company needs to calculate real-time order fulfillment availability by joining the streaming orders with the latest inventory snapshot. Additionally, they generate nightly sales reports from historical order data. Which Azure service should they use for the real-time processing component?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing, allowing you to join streaming data from Event Hubs with static or reference data (like the latest inventory snapshot from Blob Storage) using SQL-like queries. This enables the calculation of real-time order fulfillment availability as orders arrive, which is the core requirement.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines

Question 5

A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?

Accepted Answer

A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount). The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.

Answer

A clustered index on OrderID

Answer

A non-clustered index on Status

Answer

A non-clustered index on (OrderDate, TotalAmount)

Question 6

A financial services company runs large-scale analytical queries on a dedicated SQL pool in Azure Synapse Analytics. They notice that during peak hours, complex aggregations consume excessive resources, causing slower queries from other users. They need to ensure that critical management reports always get enough resources and complete within a guaranteed time, while other less important queries do not starve them. Which feature should they implement?

Accepted Answer

Workload management. Workload management in Azure Synapse Analytics allows you to classify, assign resources, and prioritize queries by creating workload groups and classifiers. By configuring a workload group for critical management reports with a higher importance and a guaranteed minimum resource percentage, you ensure those queries always get sufficient resources and complete within a guaranteed time, while less important queries are throttled and cannot starve the critical ones.

Answer

Result-set caching

Answer

Materialized views

Answer

Columnstore index

Question 7

A global e-commerce platform uses Azure Cosmos DB to store product inventory data. Customers add items to their cart, which reduces the available inventory count. The application requires that after a customer adds an item, any subsequent read of that product's inventory from any region in the world must reflect the reduced count immediately. Which Cosmos DB consistency level should be used?

Accepted Answer

Strong consistency. Strong consistency ensures that any read operation returns the most recent write, regardless of the region. Since the application requires that after a customer adds an item, any subsequent read of that product's inventory from any region must reflect the reduced count immediately, Strong consistency is the only level that guarantees linearizability and zero staleness across all replicas.

Answer

Eventual consistency

Answer

Consistent prefix consistency

Answer

Session consistency

Question 8

A development team is designing a relational database for a hospital patient management system. They need to ensure that each patient's medical record number is unique and not null. Which database constraint should they use?

Accepted Answer

PRIMARY KEY. The PRIMARY KEY constraint enforces both uniqueness and non-nullability on the column(s) it is applied to. In a relational database, the medical record number is the natural candidate for the primary key of the Patient table because it uniquely identifies each patient and must always have a value. This directly meets the requirement that each patient's medical record number is unique and not null.

Answer

FOREIGN KEY

Answer

CHECK

Answer

UNIQUE

Question 9

A data engineer needs to process streaming data from IoT devices and store the results in Azure Data Lake Storage for long-term analytics. The data must be processed in near real-time to detect anomalies and trigger alerts. Which Azure service should the engineer use for stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a serverless, real-time stream processing engine designed to handle high-velocity data from sources like IoT devices. It can ingest data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect anomalies in near real-time, and output results directly to Azure Data Lake Storage for long-term analytics. This makes it the correct choice for the described near-real-time anomaly detection and alerting requirement.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Data Lake Analytics

Question 10

A banking system processes a money transfer between two accounts. The system is designed so that after the transaction is committed, the results are permanently saved and survive any subsequent system failure, such as a power outage. Which ACID property ensures this behavior?

Accepted Answer

Durability. Durability ensures that once a transaction is committed, its changes are permanently stored and survive system failures, such as power outages or crashes. In this banking scenario, the money transfer results are written to non-volatile storage (e.g., disk) via a write-ahead log, guaranteeing that the committed state is recoverable even after a restart.

Answer

Atomicity

Answer

Consistency

Answer

Isolation

Question 11

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?

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 12

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 13

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 14

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 15

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