DP-900 Practice Exam 53 — 20 Questions

Question 1

A retail company stores data about their products in different formats. Product ID and price are stored in a relational database table. Product descriptions are stored as plain text files. Product images are stored as JPEG files. Which of the following best categorizes these data types in order?

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

Accepted Answer

Structured, unstructured, unstructured. Product ID and price in a relational database table are structured because they follow a fixed schema with rows and columns. Product descriptions as plain text files have no predefined structure, making them unstructured. Product images as JPEG files are also unstructured because they consist of binary data without a schema. Thus, the order is structured, unstructured, unstructured, which matches option B.

Answer

Structured, semi-structured, unstructured

Answer

Structured, semi-structured, structured

Answer

Semi-structured, structured, unstructured

Question 2

A retail company maintains a database of customer information including CustomerID, Name, Address, and Phone. Each record follows the same fixed schema. This type of data is best described as:

Accepted Answer

Structured data. Structured data conforms to a fixed schema where each record has the same fields (CustomerID, Name, Address, Phone) and data types, making it ideal for relational database storage. This rigid, tabular format allows efficient querying using SQL and enforces consistency across all rows.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Relational data

Question 3

A ride-sharing company processes trip requests from customers. Each trip is recorded as a single transaction that updates the driver's status, calculates the fare, and logs the ride. At the end of each month, the company runs reports that aggregate millions of trips to determine average wait times and revenue per driver. Which pair of terms best describes these two distinct workloads?

Accepted Answer

OLTP and OLAP. The first workload (trip request processing) is a classic OLTP (Online Transaction Processing) system because each trip is a single, atomic transaction that updates driver status, calculates fare, and logs the ride in real time. The second workload (monthly aggregation reports) is OLAP (Online Analytical Processing) because it queries millions of historical trip records to compute averages and revenue summaries. These two patterns have fundamentally different data storage and query optimization requirements, making OLTP and OLAP the correct pair.

Answer

Batch processing and stream processing

Answer

ETL and ELT

Answer

Relational and non-relational

Question 4

A retail chain collects sales data from all its stores at the end of each business day by exporting CSV files from each store's database. The data is then combined and analyzed to generate daily sales reports. Which type of data processing does this describe?

Accepted Answer

Batch processing. This describes batch processing because sales data is collected from each store at the end of the business day, exported as CSV files, and then combined and analyzed in a scheduled, non-continuous manner. Batch processing is ideal for large volumes of data that are processed at periodic intervals, such as daily sales reports, rather than requiring immediate action.

Answer

Real-time processing

Answer

Stream processing

Answer

Interactive query

Question 5

A retail company stores customer data in a relational database table with columns for CustomerID, Name, and Email. Product reviews are stored as JSON documents where each document contains review text and a rating. Product images are stored as binary files in Azure Blob Storage. Which of the following correctly categorizes these data types in order: relational table, JSON documents, binary images?

Accepted Answer

Structured, semi-structured, unstructured. A is correct because relational tables enforce a fixed schema (columns with defined data types), making them structured data. JSON documents have a flexible schema (key-value pairs) but still contain metadata, classifying them as semi-structured. Binary image files in Azure Blob Storage have no inherent structure or schema, making them unstructured data. This matches the order: structured, semi-structured, unstructured.

Answer

Semi-structured, structured, unstructured

Answer

Unstructured, semi-structured, structured

Answer

Structured, unstructured, semi-structured

Question 6

A social media analytics company needs to store large amounts of user activity logs. Each log entry contains a timestamp, user ID, activity type, and a dynamic set of custom attributes (e.g., page viewed, time spent). The application requires low-latency writes and point reads by a composite key (user ID and timestamp). The data is rarely updated after insertion. The company wants a fully managed NoSQL database that supports serverless throughput and automatic expiration of old logs (TTL). Which Azure Cosmos DB API should they choose?

Accepted Answer

Table API. The Table API is the correct choice because it provides a fully managed, serverless NoSQL database with automatic TTL (Time-to-Live) for data expiration, low-latency point reads and writes by a composite key (partition key + row key), and is optimized for storing large volumes of structured log data with dynamic attributes. It supports the exact requirements: high-throughput writes, point queries by user ID and timestamp, and automatic expiration of old logs without manual intervention.

Answer

NoSQL API (Core/SQL API)

Answer

Cassandra API

Answer

Gremlin API

Question 7

A retail company stores product information in a relational database table with fixed columns: ProductID (integer), Name (string), Price (decimal). They also store customer reviews as JSON documents where each review may contain different fields such as rating, comment, and optional images. Additionally, they store product images as JPEG files in Azure Blob Storage. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Structured (product info), Semi-structured (reviews), Unstructured (images). Product info in a relational table with fixed columns (ProductID, Name, Price) is structured data. Customer reviews stored as JSON documents, which may have varying fields like rating, comment, and optional images, are semi-structured because they have a flexible schema. Product images stored as JPEG files in Azure Blob Storage are unstructured binary data. This ordering from most to least structured matches option A.

Answer

Semi-structured (product info), Structured (reviews), Unstructured (images)

Answer

Unstructured (product info), Semi-structured (reviews), Structured (images)

Answer

Structured (product info), Unstructured (reviews), Semi-structured (images)

Question 8

Which classification of data describes information that has a fixed schema and is organized into rows and columns, such as data found in a relational database table?

Accepted Answer

Structured data. Structured data is defined by a fixed schema, where each data element adheres to a predefined data type and relationship, organized into rows and columns. This is the fundamental model of a relational database table, such as those in Azure SQL Database or SQL Server, where constraints like primary keys and foreign keys enforce the schema.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Transformed data

Question 9

A manufacturing company connects thousands of IoT sensors on an assembly line, each sending telemetry data every second. The data volume is terabyte-scale per day. The company needs to analyze the sensor data in near real-time to detect anomalies (e.g., temperature spikes) and also allow data scientists to run interactive ad-hoc queries on the historical data to find patterns. They prefer using a query language similar to SQL. Which Azure service should they choose?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for high-velocity telemetry data, ingesting terabytes per day from IoT sensors with sub-second latency. It supports Kusto Query Language (KQL), which is SQL-like and optimized for time-series analysis, anomaly detection, and interactive ad-hoc queries on both real-time and historical data. This makes it the ideal choice for the described scenario.

Answer

Azure Stream Analytics

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks with Structured Streaming

Question 10

A retail company collects data from online transactions including order ID, customer details, product IDs, quantities, and timestamps. The data is stored in a relational database and used for order processing and inventory management. Which characteristic of this data makes it structured?

Accepted Answer

It is stored in rows and columns with a predefined schema.. Structured data is defined by a fixed schema where each entity (e.g., orders) is stored in rows and columns with predefined data types (e.g., INT for order ID, VARCHAR for customer details). This relational model enforces consistency and enables efficient querying via SQL for order processing and inventory management.

Answer

It is stored as key-value pairs.

Answer

It is stored in JSON format with variable fields.

Answer

It is stored in unstructured text files.

Question 11

A company stores weather sensor data in Azure Data Lake Storage Gen2. Data scientists need to run large-scale transformations and machine learning experiments on this data using Python and Apache Spark. They want to collaborate using shared Jupyter notebooks. Which Azure service should they use for this analytical workload?

Accepted Answer

Azure Synapse Analytics (with Apache Spark pools). Azure Synapse Analytics with Apache Spark pools provides a fully managed Spark environment that supports Python and allows data scientists to run large-scale transformations and machine learning experiments. It integrates directly with Azure Data Lake Storage Gen2 for reading and writing data, and supports collaborative Jupyter notebooks for shared development. This makes it the correct choice for the described analytical workload.

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Answer

Azure SQL Database

Question 12

A global gaming company stores player profiles in Azure Cosmos DB. Each profile document contains PlayerID (unique), PlayerName, Email, and a nested array of Achievements. The most common query is to look up a player by PlayerID and retrieve their achievements. The company needs strong consistency for reads and writes to ensure that when a player earns an achievement, it is immediately visible. Which partition key and consistency level should they choose?

Accepted Answer

B. Partition key: PlayerID; Consistency: Strong. PlayerID is the natural partition key for the most common query (lookup by PlayerID), ensuring efficient single-partition queries. Strong consistency is required to guarantee that when a player earns an achievement, the write is immediately visible to all subsequent reads, which is critical for the gaming scenario.

Answer

A. Partition key: PlayerID; Consistency: Eventual

Answer

C. Partition key: Achievements; Consistency: Strong

Answer

D. Partition key: Email; Consistency: Bounded staleness

Question 13

Which TWO security features are available in Azure SQL Database to help protect data at rest?

Accepted Answer

Transparent data encryption (TDE). Transparent Data Encryption (TDE) is a feature in Azure SQL Database that encrypts data at rest, including backups and transaction log files, using an AES-256 encryption algorithm. It performs real-time I/O encryption and decryption of the data without requiring changes to the application, ensuring that the physical storage media is protected against unauthorized access. Always Encrypted also protects data at rest by encrypting sensitive columns within the database, but it additionally protects data in transit and during query processing by keeping the encryption keys on the client side.

Answer

Firewall rules

Answer

Dynamic data masking

Answer

Azure AD authentication

Question 14

A company runs an e-commerce application on Azure SQL Database. The database has a table named Orders with columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). The application frequently runs the following query: SELECT * FROM Orders WHERE CustomerID = 12345 AND OrderDate BETWEEN '2025-01-01' AND '2025-01-31' ORDER BY OrderDate DESC. The table contains 10 million rows. Which index would best optimize this query?

Accepted Answer

A nonclustered index on (CustomerID, OrderDate DESC) including TotalAmount as included column.. The query filters on both CustomerID and OrderDate, so a composite nonclustered index on (CustomerID, OrderDate DESC) allows SQL Server to perform an index seek on CustomerID and then an ordered range scan on OrderDate, avoiding a sort operation. Including TotalAmount as an included column makes the index covering, so the query can be satisfied entirely from the index without key lookups to the clustered index.

Answer

A nonclustered index on OrderDate only.

Answer

A clustered index on (OrderDate, CustomerID).

Answer

A nonclustered index on (OrderDate DESC) only.

Question 15

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

Accepted Answer

To filter groups after aggregation. The HAVING clause is used in T-SQL to filter groups after the GROUP BY clause has performed aggregation. In this query, it restricts the result set to only those product categories whose total sales (SUM(Amount)) exceed 1000, which is a condition on the aggregated value, not on individual rows.

Answer

To sort the result set by TotalSales descending

Answer

To join two tables

Answer

To filter rows before grouping

Question 16

You are analyzing a SQL script for an Azure Synapse Analytics dedicated SQL pool as shown in the exhibit. The table 'SensorData' will contain billions of rows. Which statement about the table design is correct?

Accepted Answer

The table uses a clustered columnstore index, which is ideal for large data warehousing tables. In Azure Synapse dedicated SQL pools, the default and recommended index type for large fact tables is the clustered columnstore index, which provides high compression and fast analytical scans across billions of rows. The exhibit's CREATE TABLE statement uses CLUSTERED COLUMNSTORE INDEX, making option A the correct description. Columnstore is purpose-built for data warehousing workloads.

Answer

The table is replicated across all compute nodes

Answer

The table uses round-robin distribution

Answer

The table uses a heap structure

Question 17

A global e-commerce company needs to store user session data (key-value pairs) for a web application hosted in multiple Azure regions. The data must support low-latency reads and writes (under 10 ms) and be automatically replicated across regions for high availability. The development team also requires the ability to query sessions by user ID using a simple key lookup and occasionally filter by secondary attributes such as timestamp. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is correct because it provides globally distributed, multi-region writes with automatic replication, guaranteeing low-latency reads and writes under 10 ms at the 99th percentile. Its key-value API (Table API or SQL API) supports simple key lookups by user ID and secondary indexing on attributes like timestamp, meeting all stated requirements.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Cache for Redis

Question 18

A company is migrating on-premises Hadoop HDFS data to Azure. They want to keep the same file system semantics for compatibility with existing analytics jobs. Which Azure storage solution should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Blob Storage but adds a hierarchical namespace, which provides true directory and file semantics compatible with Hadoop HDFS. This means existing analytics jobs that expect HDFS-style paths (for example, abfss://container@account.dfs.core.windows.net/path) can run with minimal changes, and tools like Azure Databricks, HDInsight, and Synapse Spark can access the data natively.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 19

A company stores customer data in Azure Table Storage. They need to query by a combination of partition key (customer region) and row key (customer ID). Which query pattern is most efficient?

Accepted Answer

Query using both PartitionKey and RowKey. In Azure Table Storage, the most efficient query specifies both PartitionKey and RowKey because it targets a single entity directly. The PartitionKey determines the partition, and the RowKey uniquely identifies the entity within that partition. This point query is the fastest and most cost-effective, as it avoids scanning multiple partitions or entities.

Answer

Query using RowKey only

Answer

Scan all entities

Answer

Query using PartitionKey only

Question 20

A company uses Azure SQL Database for an order management system. The 'Orders' table has millions of rows and is queried frequently with filters on OrderDate and CustomerID. The table currently has a clustered index on OrderID. Which action will most improve query performance for these frequent filters?

Accepted Answer

Create a non-clustered index on OrderDate and CustomerID. The frequent filters on OrderDate and CustomerID require a covering index that includes both columns. A non-clustered index on (OrderDate, CustomerID) allows SQL Server to perform an index seek for queries filtering on those columns, avoiding full clustered index scans on the existing clustered index on OrderID. This directly reduces I/O and improves query response times.

Answer

Create a clustered index on OrderDate

Answer

Create a non-clustered index on OrderID

Answer

Partition the table by CustomerID