DP-900 Exam Simulation — 50 Questions

Question 1

Which THREE of the following are characteristics of a data lake compared to a data warehouse?

Accepted Answer

Data lakes store data in its native or raw format.. A data lake stores data in its native or raw format, meaning it does not require transformation or schema definition at the time of ingestion. This allows organizations to retain the original fidelity of the data, which is a fundamental distinction from a data warehouse that typically transforms and structures data before loading (ETL). In Azure, Azure Data Lake Storage (ADLS) Gen2 supports storing any file format (e.g., Parquet, CSV, JSON, binary) without preprocessing.

Answer

Data lakes guarantee ACID transactions across all data.

Answer

Data lakes store only structured data.

Question 2

Refer to the exhibit. An administrator is configuring aggregations in Power BI Premium to improve performance on a large dataset. The aggregation is defined on the Sales table with SUM(Amount) grouped by ProductCategory, Region, and Date at the monthly level. However, some reports that query daily data are still slow. What is the most likely reason?

Accepted Answer

The aggregation level is monthly, but queries need daily granularity. The aggregation is defined at the monthly level (grouping by month), but the slow reports are querying daily data. Power BI aggregations work by pre-aggregating data at a specified granularity; when a query requests a lower level of detail (daily), the aggregation cannot satisfy the query, so Power BI falls back to scanning the full detailed dataset, causing slow performance. To improve daily queries, an additional aggregation at the daily level would be needed.

Answer

The dataset is too large for aggregations

Answer

The aggregation is not in DirectQuery mode

Answer

The aggregation has too many dimensions

Question 3

A company uses Azure SQL Database for an inventory management system. The Inventory table has millions of rows. Queries frequently filter on WarehouseID and then sort by LastUpdatedDate. The table currently has a clustered index on InventoryID (primary key). Which action will most improve query performance for these frequent filters?

Accepted Answer

Create a non-clustered index on (WarehouseID, LastUpdatedDate) INCLUDE (Quantity). A non-clustered index on (WarehouseID, LastUpdatedDate) allows the database engine to efficiently locate rows matching a specific WarehouseID and return them already sorted by LastUpdatedDate without accessing the clustered index (or with minimal lookup). Including the Quantity column as a non-key included column avoids key lookups for that column, further improving performance. Changing the clustered index to WarehouseID could cause fragmentation and is not ideal for uniqueness. A single-column index on LastUpdatedDate does not support the filter on WarehouseID. Partitioning by InventoryID does not help this query pattern.

Answer

Add a clustered index on WarehouseID

Answer

Create a non-clustered index on LastUpdatedDate only

Answer

Partition the table by InventoryID

Question 4

A company operates an online store where customers place orders and the system immediately updates inventory and records payments. This workload is best described as:

Accepted Answer

OLTP (Online Transaction Processing). This workload is best described as OLTP because it involves real-time, high-frequency transactions that immediately update inventory and record payments. OLTP systems are designed for concurrent, atomic operations that maintain data integrity, which is exactly what an online store's order processing requires.

Answer

OLAP (Online Analytical Processing)

Answer

Batch processing

Answer

Data warehousing

Question 5

A social media application stores user posts in Azure Cosmos DB. Each post has fields: PostID (unique), UserID, Timestamp, Content, LikesCount. The application frequently queries for all posts by a specific UserID ordered by Timestamp descending. To minimize Request Unit (RU) consumption, which partition key and indexing strategy should be used?

Accepted Answer

Partition key: UserID, and create a composite index on (UserID, Timestamp DESC). UserID is the most frequently filtered attribute, making it an ideal partition key to distribute data evenly and avoid cross-partition queries. Adding a composite index on (UserID, Timestamp DESC) allows the query to be served from a single physical partition with an index seek, minimizing RU consumption by avoiding a full scan or sort operation.

Answer

Partition key: Timestamp, and sort by UserID in the query

Answer

Partition key: PostID, and use ORDER BY Timestamp

Answer

Partition key: UserID, and use ORDER BY PostID

Question 6

A library management system uses Azure SQL Database. The Books table has 500,000 rows with columns: BookID (primary key, clustered), Title, Author, ISBN, PublishedYear, CopiesAvailable. Queries frequently filter by Author and then sort results by PublishedYear in descending order. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) INCLUDE (CopiesAvailable, Title, ISBN).. It creates a covering index that matches the exact query pattern: filtering by Author and sorting by PublishedYear in descending order. By defining (Author, PublishedYear DESC) as the index key, SQL Server can perform an index seek on Author and an ordered scan on PublishedYear without a separate sort operation. Including the remaining columns (CopiesAvailable, Title, ISBN) as non-key columns makes the index covering, eliminating the need for key lookups to the clustered index.

Answer

Create a nonclustered index on (Author) INCLUDE (PublishedYear, CopiesAvailable, Title).

Answer

Create a nonclustered index on (PublishedYear) INCLUDE (Author).

Answer

Create a nonclustered columnstore index on (Author, PublishedYear, CopiesAvailable, Title, ISBN).

Question 7

A team is designing a data pipeline to process streaming sensor data from IoT devices. The data must be ingested, transformed in real time, and stored in a time-series database. Which combination of Azure services should they use?

Accepted Answer

Azure IoT Hub, Azure Stream Analytics, and Azure Data Explorer. Azure IoT Hub ingests streaming sensor data from IoT devices, Azure Stream Analytics provides real-time transformation and analysis of the data streams, and Azure Data Explorer (ADX) is a fully managed time-series database optimized for high-velocity telemetry data. This combination directly addresses the requirement for ingestion, real-time transformation, and time-series storage.

Answer

Azure IoT Hub, Azure Data Lake Storage, and Azure Databricks

Answer

Azure Event Hubs, Azure Functions, and Azure SQL Database

Answer

Azure Event Hubs, Azure Synapse Pipelines, and Azure Cosmos DB

Question 8

A company collects data from multiple sources: IoT sensor streams, social media feeds, and CSV files from legacy systems. They want to store all this data in its original format without any transformation, so that data scientists can later apply machine learning models or run ad-hoc queries. Which data storage pattern best describes this approach?

Accepted Answer

Data lake. A data lake is designed to store vast amounts of raw data in its native format (structured, semi-structured, or unstructured) without requiring upfront schema or transformation. This aligns perfectly with the scenario of ingesting IoT streams, social media feeds, and CSV files as-is, enabling data scientists to later apply machine learning or run ad-hoc queries directly against the raw data.

Answer

Data warehouse

Answer

Relational database

Answer

Data mart

Question 9

The exhibit shows a SQL query run against Azure SQL Database. What is the purpose of the HAVING clause in this query?

Accepted Answer

To filter groups based on aggregate conditions. The HAVING clause in SQL is used to filter groups after the GROUP BY clause has been applied, based on aggregate conditions such as SUM, COUNT, or AVG. In this query against Azure SQL Database, HAVING restricts the result to only those groups that satisfy the specified aggregate condition, which cannot be done with a WHERE clause because WHERE filters individual rows before grouping.

Answer

To filter rows before grouping

Answer

To sort the result set

Answer

To join two tables

Question 10

Which TWO of the following are common characteristics of a NoSQL database?

Accepted Answer

Flexible schema. NoSQL databases, such as MongoDB or Cassandra, use a flexible schema that allows documents or records to have varying fields without requiring predefined table structures. This enables developers to iterate quickly and store semi-structured or unstructured data, such as JSON documents, without costly schema migrations.

Answer

Normalized data storage

Answer

Strong ACID transaction support

Answer

Relational data model

Question 11

A financial services company is building a real-time fraud detection system. Transactions are streamed from multiple sources into Azure Event Hubs. The system must run a trained machine learning model (scored in near real-time) to flag suspicious transactions. The model is a Python pickle file that needs to be deployed as a web service with low latency (under 100 ms per prediction). The data engineering team wants to use a serverless compute option to run the scoring logic, and the solution must integrate with Azure Stream Analytics for alerting. Which Azure service should you use to deploy the model?

Accepted Answer

Azure Machine Learning managed online endpoint. Azure Machine Learning managed online endpoints are the correct choice because they are designed for deploying trained models (including Python pickle files) as low-latency web services (under 100 ms per prediction) with serverless compute. They natively integrate with Azure Stream Analytics for alerting, allowing real-time scoring of streaming transactions from Event Hubs without managing infrastructure.

Answer

Azure Functions

Answer

Azure Kubernetes Service (AKS)

Answer

Azure Databricks

Question 12

Your company is migrating an on-premises SQL Server data warehouse to Azure. The solution must support both historical analytics and real-time reporting. Which Azure service should you recommend as the primary data store?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it is a cloud-native analytics service that unifies big data and data warehousing, supporting both historical analytics (via dedicated SQL pools for large-scale relational data warehousing) and real-time reporting (via serverless SQL pools or Apache Spark pools for streaming and interactive queries). It is designed to handle the migration of an on-premises SQL Server data warehouse while providing integrated capabilities for batch and real-time workloads.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Question 13

A globally distributed online auction platform uses a replicated database system across multiple Azure regions. The system must continue accepting bids (writes) even if a network partition occurs between regions, because auctions cannot be interrupted. The business decides that during a partition, some users might see slightly outdated item prices (read inconsistency) but all bids must be recorded. According to the CAP theorem, which two properties is this system prioritizing?

Accepted Answer

Availability (A) and Partition Tolerance (P). The CAP theorem defines three properties: Consistency (C), Availability (A), and Partition Tolerance (P). The system continues accepting bids during network partitions, prioritizing Availability (every request receives a response) and Partition Tolerance (system operates despite network splits). Option A (Availability and Partition Tolerance) directly describes this AP trade-off. Option D (Durability and Availability) is incorrect because Durability is not a CAP property; it belongs to ACID. The question specifically asks for CAP properties, so only A is correct.

Answer

Consistency (C) and Partition Tolerance (P)

Answer

Consistency (C) and Availability (A)

Answer

Durability and Availability

Question 14

A company uses Azure Blob Storage to store video files for a streaming service. The files are accessed frequently for the first 30 days after upload, then rarely after. The company wants to minimize storage costs while maintaining fast access for frequently accessed files. What should they implement?

Accepted Answer

Blob lifecycle management policies. Blob lifecycle management policies allow you to automatically transition blobs to cooler, cheaper access tiers (e.g., from Hot to Cool or Archive) based on age. This directly addresses the requirement: after 30 days of frequent access, the policy moves the video files to a lower-cost tier, reducing storage costs while keeping the Hot tier available for the initial high-access period.

Answer

Azure Content Delivery Network (CDN)

Answer

Azure Files shares

Answer

Geo-redundant storage (GRS)

Question 15

A company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance purposes. The audit logs must be stored for 7 years. Which feature should they enable?

Accepted Answer

SQL Database auditing. SQL Database auditing captures all data modifications (INSERT, UPDATE, DELETE) and can store logs in Azure storage, Log Analytics, or Event Hubs with retention up to 7 years. Option A is wrong because Advanced Threat Protection detects suspicious activities, not audits modifications. Option C is wrong because vulnerability assessment scans for security weaknesses, not logging changes. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, it does not log modifications.

Answer

Advanced Threat Protection

Answer

Vulnerability assessment

Answer

Transparent Data Encryption (TDE)

Question 16

You need to store semi-structured JSON data from a web application and query it using SQL-like syntax. The solution must support high throughput with low latency. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents and offers SQL-like querying via its core (SQL) API. It is designed for high throughput and low latency with guaranteed single-digit millisecond response times at the 99th percentile, making it ideal for web applications with demanding performance requirements.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 17

Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?

Accepted Answer

Azure Blob Storage. The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 18

A global e-commerce company uses Azure Cosmos DB with multiple write regions to handle high traffic from users worldwide. For their order processing system, they must guarantee that once an order is recorded, all subsequent reads from any region see the most up-to-date order status. However, they also need low write latency globally. Which configuration should they choose to meet these requirements?

Accepted Answer

Use single-region writes with strong consistency. Strong consistency with single-region writes ensures that all reads in any region return the most recent write, because Cosmos DB replicates writes synchronously to all regions when strong consistency is configured. This guarantees linearizability: once an order is committed, every subsequent read sees that update. Single-region writes avoid the conflict-resolution overhead of multi-region writes while still providing low write latency within the primary region, and reads from secondary regions are served from locally replicated data that is kept fully consistent.

Answer

Use multi-region writes with strong consistency

Answer

Use multi-region writes with bounded staleness consistency

Answer

Use single-region writes with eventual consistency and implement application-level conflict resolution

Question 19

A company stores three types of data: 1) Customer orders in a SQL table with fixed columns for OrderID, CustomerID, and OrderDate. 2) Product reviews in XML files where each file contains varying tags such as and . 3) Video files of product demonstrations. Which of the following correctly classifies these data types in order from first to third?

Accepted Answer

Structured, Semi-structured, Unstructured. Customer orders in a SQL table with fixed columns (OrderID, CustomerID, OrderDate) are structured data because they conform to a rigid schema. Product reviews in XML files with varying tags like and are semi-structured data because they have tags/metadata but no fixed schema. Video files of product demonstrations are unstructured data because they lack any predefined data model or organization.

Answer

Semi-structured, Unstructured, Structured

Answer

Unstructured, Structured, Semi-structured

Answer

Structured, Unstructured, Semi-structured

Question 20

A gaming company stores player profiles as JSON documents. Each profile includes standard fields like playerId, username, and email, as well as optional fields such as achievements, gamePreferences, and friendsList. The application needs to look up profiles by playerId with low latency (under 10 ms) and also run SQL-like queries to find players who have a specific achievement. Which Azure Cosmos DB API should they choose?

Accepted Answer

D. SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports JSON documents with flexible schemas (including optional fields like achievements) and provides low-latency point reads by playerId (partition key) under 10 ms. It also enables SQL-like queries (e.g., SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specificAchievement')) to find players with a specific achievement, which aligns directly with the requirement.

Answer

A. Table API

Answer

B. Cassandra API

Answer

C. MongoDB API