DP-900 Practice Exam 56 — 20 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

A company stores terabytes of customer support chat transcripts in JSON format. The data is rarely modified and needs to be accessed by analysts using SQL queries. The analysts do not want to manage servers or provision throughput. Which Azure service should be used to store and query this data?

Accepted Answer

Azure Blob Storage (with Azure Data Lake Storage Gen2) and query using Azure Synapse Serverless SQL. Azure Blob Storage with Azure Data Lake Storage Gen2 provides a cost-effective, scalable solution for storing large volumes of JSON data in its native format. By using Azure Synapse Serverless SQL, analysts can query this data directly with standard T-SQL without provisioning any infrastructure or managing throughput, meeting the requirement for serverless, on-demand querying of rarely modified data.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 14

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

Question 15

A retail company ingests daily sales data from multiple stores as CSV files stored in Azure Blob Storage. The data must be cleaned and transformed using Spark, then loaded into Azure Synapse Analytics for large-scale reporting. The pipeline must run on a schedule, handle failures with retries, and minimize manual intervention. Which combination of Azure services should they use to orchestrate and execute this pipeline?

Accepted Answer

Azure Data Factory, Azure Databricks, and Azure Synapse Analytics.. Azure Data Factory provides the orchestration and scheduling layer, Azure Databricks executes the Spark-based cleaning and transformation, and Azure Synapse Analytics serves as the target data warehouse for large-scale reporting. This combination supports retry policies for failure handling and minimizes manual intervention through automated pipeline execution.

Answer

Azure Stream Analytics, Azure Data Lake Storage, and Power BI.

Answer

Azure Functions, Azure SQL Database, and Azure Analysis Services.

Answer

Azure Logic Apps, Azure HDInsight, and Azure Cosmos DB.

Question 16

A mobile game stores player achievements in Azure Cosmos DB. Each player has a PlayerID, and achievements are stored as JSON documents with varying fields. The most common query retrieves all achievements for a specific player. To ensure low latency and efficient throughput, which property should be chosen as the partition key?

Accepted Answer

PlayerID. PlayerID is the correct partition key because the most common query retrieves all achievements for a specific player, and partitioning on PlayerID ensures that all documents for a given player are stored in the same physical partition. This allows the query to target a single partition, minimizing cross-partition queries and providing low latency and efficient throughput.

Answer

Timestamp

Answer

AchievementType

Answer

Region

Question 17

A social media application stores user profile data as JSON documents. Each user's document has a different structure, with fields that vary based on user activity. The application needs to query these documents efficiently using SQL-like syntax and support high write throughput. Which Azure data store is most appropriate for this workload?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the most appropriate choice because it natively supports storing and querying JSON documents with varying schemas, offers SQL-like query syntax via its core (SQL) API, and provides guaranteed low-latency reads/writes at any scale with automatic indexing of all fields. Its multi-model nature and configurable consistency levels make it ideal for high-throughput workloads like a social media application.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 18

You need to choose a data storage solution for a global e-commerce platform that requires single-digit millisecond read and write latencies across multiple regions. The data is semi-structured and includes user profiles and product catalogs. Which Azure service should you use?

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 and write latencies at the 99th percentile, regardless of the number of regions. It supports semi-structured data natively through its document (JSON) API, making it ideal for user profiles and product catalogs that require low-latency access across multiple geographic regions.

Answer

Azure Redis Cache

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 19

A ride-sharing application uses Azure Cosmos DB for trip data. Each trip record contains TripID (unique), DriverID, RiderID, TripDate, and other details. The most common query retrieves all trips for a specific driver within a given date range. Which partition key should be chosen to minimize Request Unit (RU) consumption and ensure even data distribution?

Accepted Answer

DriverID. DriverID is the optimal partition key because the most common query filters on DriverID and a date range. Partitioning by DriverID ensures that all trips for a specific driver are stored in the same physical partition, making the query a single-partition operation that consumes minimal Request Units (RUs). It also provides even data distribution across partitions because each driver generates a roughly similar number of trips, avoiding hot spots.

Answer

TripID

Answer

TripDate

Answer

RiderID

Question 20

A data lake stores Parquet files in Azure Data Lake Storage Gen2, organized by date (e.g., /data/2023/01/15/). Analysts frequently run queries that filter on a specific date range. Which feature of Azure Data Lake Storage Gen2 directly enables efficient directory-level operations like renaming or moving entire date partitions without rewriting files?

Accepted Answer

Hierarchical namespace. The hierarchical namespace feature in Azure Data Lake Storage Gen2 enables true directory-level operations, such as renaming or moving entire partitions (e.g., /data/2023/01/15/), by treating directories as first-class objects. This allows atomic metadata operations without rewriting or copying the underlying Parquet files, which is essential for efficient partition management in data lake scenarios.

Answer

Blob soft delete

Answer

Change feed

Answer

Immutable storage