DP-900 Practice Exam 2 — 20 Questions

Question 1

A retail company stores three types of customer data: (1) a table with columns for CustomerID, Name, and Email; (2) product reviews as JSON documents with varying fields such as rating and comment; (3) product demonstration videos stored in MP4 format. Which of the following correctly classifies these data types in order from first to third?

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

Accepted Answer

Structured, semi-structured, unstructured. The customer table with fixed columns (CustomerID, Name, Email) is structured data, product reviews as JSON documents with varying fields are semi-structured data (schema-on-read, flexible fields), and MP4 video files are unstructured data (no schema, binary format). This ordering matches the standard classification in Azure Data Fundamentals: structured → semi-structured → unstructured.

Answer

Structured, unstructured, semi-structured

Answer

Semi-structured, structured, unstructured

Answer

Unstructured, semi-structured, structured

Question 2

A data analyst needs to run ad-hoc SQL queries on terabytes of CSV files stored in Azure Data Lake Storage Gen2. The queries are infrequent and unpredictable. The analyst wants to pay only for the amount of data processed by each query, and does not want to manage any compute or storage infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (C) is the correct choice because it allows querying data in Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, making it ideal for infrequent, unpredictable ad-hoc queries, and it eliminates infrastructure management.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory

Answer

Azure Analysis Services

Question 3

You are designing a relational database for an IoT application that ingests high volumes of time-stamped sensor data. The queries frequently filter by device ID and time range. Which index strategy would optimize query performance?

Accepted Answer

Create a composite index on (DeviceID, Timestamp). A composite index on (DeviceID, Timestamp) directly supports the two most common filter predicates in the query workload: DeviceID (for equality) and Timestamp (for range scans). In SQL Server, a composite index with DeviceID as the leading column allows the query engine to perform an index seek on DeviceID and then a range scan on Timestamp, minimizing I/O and avoiding key lookups. This strategy is optimal for time-series IoT data where queries almost always specify a device and a time window.

Answer

Create a non-clustered index on Timestamp only

Answer

Create a clustered index on DeviceID only

Answer

Create a non-clustered index on SensorType

Question 4

A social networking application stores user profiles as JSON documents in Azure Cosmos DB. Each profile includes fields such as 'userName', 'email', 'followersCount', and optional 'interests'. The application needs to perform fast point reads by 'userName' (under 10 ms) and also run queries to find all users with a 'followersCount' greater than a certain value. The development team prefers to use a query syntax similar to SQL. Which Azure Cosmos DB API should they choose?

Accepted Answer

Azure Cosmos DB for NoSQL (SQL API). Azure Cosmos DB for NoSQL (SQL API) is the correct choice because it natively supports SQL-like query syntax for querying JSON documents, enabling the required queries such as filtering by 'followersCount'. It also provides fast point reads (under 10 ms) by using the 'userName' field as the partition key, ensuring efficient direct access to individual documents.

Answer

Azure Cosmos DB for MongoDB

Answer

Azure Cosmos DB for Table

Answer

Azure Cosmos DB for Apache Cassandra

Question 5

A manufacturing company collects sensor data from equipment on the factory floor. The data is generated continuously and must be processed immediately to detect anomalies and trigger alerts. Which type of data processing workload best describes this scenario?

Accepted Answer

Stream processing. B is correct because the scenario requires continuous data ingestion and immediate processing to detect anomalies and trigger alerts, which is the defining characteristic of stream processing. Technologies like Azure Stream Analytics or Apache Kafka are designed to handle unbounded data streams with low-latency processing, unlike batch processing which operates on static datasets at scheduled intervals.

Answer

Batch processing

Answer

Transactional processing

Answer

Analytical processing

Question 6

A mobile gaming company stores player profiles in Azure Cosmos DB. Each profile document contains many optional fields, and queries frequently filter by the player's locale (a field present in about 30% of documents). Which approach will optimize query performance for these filters?

Accepted Answer

Create a composite index that includes the locale field. Creating a composite index that includes the locale field allows Azure Cosmos DB to efficiently filter queries by locale without scanning every document. Since locale is present in only 30% of documents, a composite index reduces the query RU cost by directly locating matching documents, leveraging the index's sorted structure for faster lookups.

Answer

Embed all fields in a single document and rely on automatic indexing

Answer

Normalize the data by storing locale in a separate container and use cross-container queries

Answer

Define a fixed schema for all documents to ensure every document has the locale field

Question 7

A healthcare provider stores patient admission data in a relational database table with columns for PatientID, Name, and AdmissionDate. Progress notes are stored as free-text documents. Lab results are stored as XML files that contain varying fields depending on the test type. Which of the following correctly categorizes these three data types in order: relational table, progress notes, lab results?

Accepted Answer

Structured, Unstructured, Semi-structured. The relational table with PatientID, Name, and AdmissionDate enforces a fixed schema with defined data types, making it structured data. Progress notes as free-text documents have no predefined structure or schema, classifying them as unstructured data. Lab results in XML files use tags to organize data but allow varying fields per test type, which is the hallmark of semi-structured data. Option A correctly maps these in order: structured, unstructured, semi-structured.

Answer

Structured, Semi-structured, Unstructured

Answer

Semi-structured, Unstructured, Structured

Answer

Unstructured, Structured, Semi-structured

Question 8

Refer to the exhibit. You are reviewing an ARM template that deploys a SQL database in Azure Synapse. The template sets the storageAccountType to GRS. What is a valid concern regarding cost and performance?

Accepted Answer

GRS will increase storage costs and may cause higher latency. Geo-redundant storage (GRS) replicates your data to a secondary region, which increases storage costs because you are paying for both the primary and secondary copies. Additionally, when using GRS with Azure Synapse SQL, read requests may experience higher latency if they are directed to the secondary region, especially during a failover scenario or when using read-access geo-redundant storage (RA-GRS). This makes cost and performance valid concerns when choosing GRS over locally redundant storage (LRS).

Answer

The collation setting is not compatible with Azure Synapse

Answer

The database cannot be part of a failover group

Answer

The database will not support Transparent Data Encryption

Question 9

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent to run scheduled jobs and performs cross-database queries within the same instance. The company wants a fully managed PaaS solution that requires minimal application changes. 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 full SQL Server Agent support for scheduled jobs and enables cross-database queries within the same instance, while being a fully managed PaaS service. It offers near 100% compatibility with on-premises SQL Server, minimizing application changes during migration.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 10

A mobile gaming startup needs to store player profiles that can have varying attributes (e.g., some players have a 'nickname', others have 'avatar URL'). The application must read a player's profile by PlayerID with very low latency (under 10 ms) from any location worldwide. The data does not require complex queries or joins. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that guarantees single-digit-millisecond read latencies (under 10 ms) at any scale from any Azure region. Its schema-agnostic nature allows storing player profiles with varying attributes (e.g., nickname, avatar URL) without requiring a fixed schema, and it supports point reads by PlayerID with a consistency model that can be tuned for performance. This directly matches the requirements of low-latency global reads and flexible, non-relational data.

Answer

Azure SQL Database

Answer

Azure Table Storage

Answer

Azure Blob Storage

Question 11

A retail chain collects daily sales data from hundreds of stores. The data is stored as CSV files in Azure Data Lake Storage Gen2. The analytics team needs to run complex SQL queries that join sales data with product dimensions and aggregate results across petabytes of data. Queries must return results within seconds. Which Azure service is best suited for this analytical workload?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a distributed query engine designed for petabyte-scale data warehousing. It uses Massively Parallel Processing (MPP) to distribute data across compute nodes, enabling complex SQL joins and aggregations on data stored in Azure Data Lake Storage Gen2 to return results in seconds via its SQL pool or serverless SQL endpoint.

Answer

Azure SQL Database

Answer

Azure Analysis Services

Answer

Azure HDInsight

Question 12

A company uses Azure Data Lake Storage Gen2 for a data lake. They implement a folder structure with access control lists (ACLs). A new data scientist needs to read data from a specific folder but not write to it. Which ACL permission should be assigned?

Accepted Answer

Execute. Execute (X) permission on a folder in Azure Data Lake Storage Gen2 is required to traverse the folder and access its contents. Without Execute, a user cannot list or read files inside the folder, even if Read permission is granted. Since the data scientist only needs to read data (not write), assigning Execute on the folder and Read on the files allows traversal and read access without write capability.

Answer

Modify

Answer

Write

Answer

Read

Question 13

A manufacturing company deploys IoT sensors on equipment in a factory. They need to monitor sensor data in real time to detect anomalies and trigger immediate alerts. They also need to store years of historical sensor data for monthly capacity planning reports that involve complex aggregations. The company wants a cost-effective solution that minimizes data movement between storage and compute. Which combination of Azure services should they use for real-time processing and historical batch analytics?

Accepted Answer

A. Azure Stream Analytics for real-time processing, Azure Data Lake Storage Gen2 for historical storage, and Azure Synapse Analytics for batch queries.. Azure Stream Analytics is purpose-built for real-time processing of streaming data from IoT sensors, enabling immediate anomaly detection and alerting. Azure Data Lake Storage Gen2 provides cost-effective, scalable storage for years of historical sensor data, while Azure Synapse Analytics (formerly SQL Data Warehouse) can run complex aggregations directly against that data without moving it, minimizing data movement and cost.

Answer

B. Azure Data Factory for real-time processing, Azure Cosmos DB for historical storage, and Power BI for batch queries.

Answer

C. Azure Functions for real-time processing, Azure Table Storage for historical storage, and Azure Analysis Services for batch queries.

Answer

D. Azure Event Hubs for real-time processing, Azure SQL Database for historical storage, and Azure Machine Learning for batch queries.

Question 14

A mobile gaming company stores player session data as key-value pairs. Each player has a unique PlayerID, and the application needs to read/write the player's current level and score with very low latency. The data does not require complex queries, and the schema (attributes per player) can vary. The company wants a fully managed, globally distributed NoSQL database. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB Table API. Azure Cosmos DB Table API is the correct choice because it provides a fully managed, globally distributed NoSQL database that supports key-value data with schema flexibility. It offers low-latency reads and writes (single-digit milliseconds at the 99th percentile) and automatic global distribution, making it ideal for storing player session data with varying attributes per player.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Cache for Redis

Question 15

A startup develops a mobile application that stores user preferences as simple key-value pairs. The app is only used in North America, and the team needs low-latency reads and writes with minimal cost. They do not require global distribution or complex querying. Which Azure data store should they choose?

Accepted Answer

Azure Table Storage. Azure Table Storage is the correct choice because it provides a cost-effective, low-latency key-value store for simple data like user preferences, with no need for global distribution or complex querying. It offers single-digit millisecond latency for reads and writes within a single region, and its pay-per-request pricing model minimizes cost for a startup. The Table API in Azure Cosmos DB would be overkill and more expensive for this North America-only, non-distributed scenario.

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Cosmos DB (Table API)

Answer

Azure SQL Database

Question 16

A social media company stores user-generated posts as JSON documents. Each post contains fields such as postId, userId, timestamp, and content. The application needs to query posts by userId and timestamp ranges with low latency, and also perform SQL-like queries across all posts. The data volume is growing rapidly and must scale globally. Which Azure data store should the company use?

Accepted Answer

B) Azure Cosmos DB SQL API. Azure Cosmos DB SQL API is the correct choice because it provides native support for querying JSON documents with low-latency, including indexed queries on fields like userId and timestamp. Its global distribution capability ensures data can be replicated across multiple Azure regions for low-latency access worldwide, while its SQL API allows SQL-like queries across all posts, meeting both requirements.

Answer

A) Azure Table Storage

Answer

C) Azure Blob Storage

Answer

D) Azure Cache for Redis

Question 17

A SaaS company hosts a multi-tenant application. Each tenant has a separate Azure SQL Database. The databases are small (1-3 GB) and their workloads vary significantly over time, with some tenants active during business hours and others at night. The company wants to maximize resource utilization and minimize costs by pooling compute resources across tenants while maintaining predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases (tenants) to share a fixed pool of compute and storage resources, enabling cost efficiency through resource pooling while providing predictable performance via per-database resource limits (min/max DTU or vCore). This matches the scenario of small databases with variable, non-overlapping workloads across tenants.

Answer

Azure SQL Database Single Database

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 18

A company runs an e-commerce application on Azure SQL Database. The database experiences high transaction volume during business hours (9 AM to 6 PM) but very low activity at night and on weekends. They want to optimize costs by paying only for the compute resources used, while ensuring the database can automatically scale up during peak periods and scale down (or pause) during idle times. Which Azure SQL Database purchasing model and compute tier should they choose?

Accepted Answer

vCore-based purchasing model with serverless compute tier. The vCore-based purchasing model with serverless compute tier is correct because it automatically scales compute resources based on workload demand and can pause during idle periods, charging only for consumed compute and storage. This matches the requirement of high transaction volume during business hours and low activity at night/weekends, optimizing costs by eliminating charges for unused compute capacity.

Answer

DTU-based purchasing model

Answer

vCore-based purchasing model with provisioned compute tier

Answer

vCore-based purchasing model with Hyperscale service tier

Question 19

A hospital uses Azure SQL Database to store patient records. The database contains tables for patient information, insurance details, and treatment plans. The system must ensure that if a transaction updates a patient's insurance and their treatment plan in two separate rows, either both updates succeed or both fail. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In Azure SQL Database, if a transaction updates both the insurance and treatment plan rows, atomicity guarantees that either both updates are committed or both are rolled back, preventing partial updates that could leave data in an inconsistent state.

Answer

Durability

Answer

Consistency

Answer

Isolation

Question 20

A gaming company stores player scores in Azure Cosmos DB using the NoSQL API. Each document contains fields: PlayerID (unique to the player), GameID, Score, Timestamp. The most common query is: 'Retrieve all scores for a specific GameID, ordered by Score descending.' Which property should be chosen as the partition key to minimize Request Unit (RU) consumption?

Accepted Answer

GameID. GameID is the correct partition key because the most common query filters on GameID, and Cosmos DB routes queries to the exact physical partition(s) containing that GameID's data. This minimizes RU consumption by avoiding cross-partition fan-out, as the query engine can target a single partition. Using any other field would force scanning multiple partitions, increasing RU cost.

Answer

PlayerID

Answer

Score

Answer

Timestamp