DP-900 Practice Test 3 — 10 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