DP-900 Practice Exam 51 — 20 Questions

Question 1

A company stores customer data in a relational table with fixed columns: CustomerID (integer), FirstName (string), LastName (string), Email (string). They also store product images as JPEG files, and customer feedback as JSON documents that may contain varying fields such as rating, comment, and optional metadata. Which of the following correctly orders these data types from most structured to least structured?

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

Accepted Answer

Relational table, JSON documents, JPEG files. The relational table is the most structured because it enforces a fixed schema with predefined columns and data types (e.g., CustomerID integer, FirstName string). JSON documents are semi-structured: they have a flexible schema where fields like rating and comment can vary per document, but they still provide key-value organization. JPEG files are unstructured binary data with no internal schema or queryable structure, making them the least structured.

Answer

JSON documents, relational table, JPEG files

Answer

JPEG files, JSON documents, relational table

Answer

Relational table, JPEG files, JSON documents

Question 2

A company develops an IoT device registry that stores device metadata as JSON documents. Each device has a unique DeviceID, and the attributes vary per device type (e.g., sensors, actuators). The application requires low-latency reads by DeviceID and needs global distribution to support devices worldwide. Which Azure Cosmos DB API should they choose to natively support JSON documents with flexible schema?

Accepted Answer

Azure Cosmos DB SQL API. The Azure Cosmos DB SQL API (formerly DocumentDB) is the correct choice because it provides native support for storing and querying JSON documents with flexible schema, allowing each device document to have a unique DeviceID and varying attributes per device type. It offers low-latency reads by DeviceID via direct point reads using the partition key, and supports global distribution through multi-region writes and automatic replication, meeting the worldwide deployment requirement.

Answer

Azure Cosmos DB Table API

Answer

Azure Cosmos DB for MongoDB API

Answer

Azure Cosmos DB Gremlin API

Question 3

A retail company stores customer transaction data in a relational database. Each transaction is recorded with a fixed schema including TransactionID, CustomerID, ProductID, Quantity, and TotalAmount. Which type of data does this represent?

Accepted Answer

Structured data. The data conforms to a fixed schema with defined columns (TransactionID, CustomerID, ProductID, Quantity, TotalAmount) and data types, which is the defining characteristic of structured data. In a relational database, this schema enforces consistency and allows for efficient querying using SQL, making it a classic example of structured data.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Binary data

Question 4

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 5

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Question 6

A media company stores user profile images in Azure Blob Storage. Regulators require that the images cannot be deleted or overwritten for a period of 90 days after upload. Which Azure Blob Storage feature should the company enable to meet this requirement?

Accepted Answer

B: Immutable storage with a time-based retention policy. Immutable storage with a time-based retention policy (also known as WORM – Write Once, Read Many) prevents blobs from being deleted or overwritten for a specified retention interval. By setting a 90-day policy, the company ensures that user profile images remain unmodifiable and undeletable during that period, directly satisfying the regulatory requirement.

Answer

A: Soft delete

Answer

C: Access tiers (Hot, Cool, Archive)

Answer

D: Lifecycle management rules

Question 7

A company stores customer data in a relational table with fixed columns: CustomerID (integer), FirstName (string), LastName (string), Email (string). They also store product images as JPEG files, and customer feedback as JSON documents that may contain varying fields such as rating, comment, and optional metadata. Which of the following correctly orders these data types from most structured to least structured?

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

Accepted Answer

Relational table – structured, JPEG – unstructured, JSON – semi-structured. A relational table with fixed columns and data types (CustomerID, FirstName, LastName, Email) stores structured data with a rigid schema. JPEG files in Azure Blob Storage are binary blobs with no internal structure that a database can interpret, making them unstructured. JSON documents with optional fields (like rating, comment, metadata) have a flexible schema that can vary per document, which is the definition of semi-structured data.

Answer

Relational table – structured, JPEG – semi-structured, JSON – unstructured

Answer

Relational table – semi-structured, JPEG – unstructured, JSON – structured

Answer

Relational table – unstructured, JPEG – structured, JSON – semi-structured

Question 8

A data analyst needs to run ad-hoc SQL queries on petabytes of Parquet files stored in Azure Data Lake Storage Gen2. The queries are infrequent and highly selective. The analyst wants to pay only for the data scanned by each query and does not want to provision any compute resources. They also need to create views to simplify future queries for other analysts. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (correct answer) is a pay-per-query service that charges only for the data scanned, requires no provisioning of compute resources, and supports creating views over external data in Azure Data Lake Storage Gen2. It uses T-SQL to query Parquet files directly, making it ideal for infrequent, highly selective ad-hoc queries without managing infrastructure.

Answer

Azure Data Factory

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure Databricks

Question 9

A company ingests streaming data from social media feeds and needs to process and analyze the data in real time. Which Azure service should they use to capture the stream?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a fully managed, real-time data ingestion service designed to capture and process millions of events per second from sources like social media feeds. It provides a scalable, low-latency endpoint for streaming data, making it the correct choice for capturing the stream before further analysis.

Answer

Azure Stream Analytics

Answer

Azure IoT Hub

Answer

Azure Data Lake Storage

Question 10

A company uses Azure SQL Database for its order management system. The database has a table named Orders with columns OrderID (INT, PRIMARY KEY), CustomerID (INT), OrderDate (DATE), TotalAmount (DECIMAL). Queries that filter by OrderDate are slow. The database administrator observes that the nonclustered index on OrderDate has high fragmentation and many page splits. Which action will most likely improve query performance for these date-based queries?

Accepted Answer

Rebuild the nonclustered index on OrderDate with a FILLFACTOR of 80.. Rebuilding the nonclustered index on OrderDate with a FILLFACTOR of 80 reduces page splits by leaving free space in each leaf-level page. This accommodates future insertions and updates that modify the OrderDate values, lowering fragmentation and improving query performance for date-based filters.

Answer

Change the data type of TotalAmount from DECIMAL to FLOAT.

Answer

Remove the clustered index on OrderID and create a clustered index on OrderDate.

Answer

Add a columnstore index on the OrderDate column.

Question 11

A gaming company stores player profiles in Azure Cosmos DB using the NoSQL API. Each profile is a JSON document containing fields like playerId, userName, level, inventory (an array of items), and friends (an array of playerIds). The application frequently needs to query all players that have a specific item in their inventory (e.g., 'sword'). Which Cosmos DB feature should they use to support this query efficiently?

Accepted Answer

Indexing policy with a wildcard index. A wildcard index in the indexing policy allows Azure Cosmos DB to automatically index all properties within a JSON document, including nested array elements like those in the 'inventory' array. This enables efficient queries such as 'SELECT * FROM c WHERE ARRAY_CONTAINS(c.inventory, {name: "sword"})' without requiring a custom composite index for each possible item. Without a wildcard index, the query would require a full scan of all documents, which is inefficient at scale.

Answer

Change feed

Answer

Stored procedures

Answer

Composite index

Question 12

A retail company captures real-time sensor data from IoT devices to detect anomalies and predict equipment failures. The data must be processed immediately as it arrives. Which type of data processing workload best describes this scenario?

Accepted Answer

Streaming processing. B is correct because streaming processing is designed for continuous, real-time data ingestion and immediate analysis, which matches the requirement to process sensor data as it arrives. Technologies like Azure Stream Analytics or Apache Kafka enable low-latency processing of IoT data streams to detect anomalies and predict failures without batching.

Answer

Batch processing

Answer

Online transaction processing (OLTP)

Answer

Data warehousing

Question 13

A data engineering team needs to analyze petabytes of historical sales data stored in Azure Data Lake Storage Gen2. They require the ability to run complex SQL queries that join multiple tables and need high performance. The solution must separate compute from storage to allow independent scaling of resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is designed for petabyte-scale data warehousing, providing massively parallel processing (MPP) to run complex SQL queries across multiple tables with high performance. It separates compute from storage, allowing independent scaling of compute resources without moving data, which aligns with the requirement for decoupled scaling.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Question 14

A retail company has an Azure SQL Database that handles OLTP transactions for its e-commerce platform. The analytics team needs to run complex reporting queries that join multiple tables (e.g., orders, products, customers) and aggregate millions of rows. These queries are long-running and would negatively impact the performance of the OLTP database if run directly. The company wants to use a separate analytics service that supports T-SQL queries, can scale compute independently, and provides a serverless option to avoid provisioning fixed resources. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics (serverless SQL pool). Azure Synapse Analytics includes a serverless SQL pool that supports T-SQL queries, scales compute independently, and does not require provisioning fixed resources, matching the requirement. The dedicated SQL pool is provisioned and does not provide a serverless option, so the correct choice is the serverless SQL pool.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database (creating a secondary replica)

Question 15

A database designer is creating a relational database for a library system. Each book can have multiple authors, and each author may have written many books. To avoid data redundancy, the designer creates a separate Authors table and a BookAuthors junction table. This process of organizing data to reduce redundancy and improve integrity is called:

Accepted Answer

Normalization. Normalization is the process of organizing data in a relational database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables and defining relationships between them. In this scenario, creating separate Authors and BookAuthors junction tables eliminates the redundancy of storing author information multiple times for each book, which is a classic example of normalization (specifically achieving third normal form). This directly supports the relational database goal of minimizing duplicate data and ensuring consistency.

Answer

Denormalization

Answer

Indexing

Answer

Partitioning

Question 16

A data analyst needs to run ad-hoc SQL queries on large datasets stored as Parquet files in Azure Data Lake Storage Gen2. The queries are infrequent and the data volume varies. The analyst wants to pay only for the amount of data processed per query and does not want to manage any infrastructure. They also need to create views in T-SQL to simplify queries for Power BI reports. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query model, charging only for the amount of data processed, and supports creating T-SQL views that can be used directly by Power BI for simplified reporting.

Answer

Azure Data Lake Analytics

Answer

Azure HDInsight

Answer

Azure Databricks

Question 17

A financial services company runs critical end-of-day reports in an Azure Synapse Analytics dedicated SQL pool. These reports require guaranteed resource allocation and must complete within a fixed time window. However, ad-hoc analytical queries from data scientists often consume resources, causing contention and delaying the critical reports. Which feature should the company implement to ensure the critical reports always receive sufficient resources?

Accepted Answer

A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.. Workload groups in Azure Synapse Analytics dedicated SQL pool allow you to assign a minimum percentage of resources (e.g., CPU and memory) to a specific workload, ensuring guaranteed resource allocation. By setting high importance for the critical reports, the system prioritizes them over ad-hoc queries, preventing resource contention and ensuring they complete within the fixed time window.

Answer

B. Enable result set caching on all queries to reduce execution time.

Answer

C. Implement materialized views for the aggregations used in the critical reports.

Answer

D. Use hash distribution for the fact tables to improve query parallelism.

Question 18

An e-commerce application uses Azure SQL Database and stores user session data in a table called Sessions. The table contains millions of rows and queries often filter by UserID and LastActivityTime. The development team wants to improve query performance for these filters. What should they implement?

Accepted Answer

Create a nonclustered index on UserID and LastActivityTime. A nonclustered index on UserID and LastActivityTime allows the database engine to quickly locate rows matching the filter criteria without scanning the entire table. This index covers the two columns most frequently used in WHERE clauses, significantly reducing I/O and improving query performance for the e-commerce application's session data.

Answer

Create a clustered index on the SessionID column

Answer

Create a view that filters the data

Answer

Partition the table by month

Question 19

A manufacturing company ingests a continuous stream of sensor data from factory equipment into Azure Event Hubs. Additionally, historical maintenance data in CSV format is stored in Azure Data Lake Storage Gen2. The analytics team needs to join the streaming sensor data with the historical data in near real-time and enable analysts to query the combined dataset using standard T-SQL without moving the data. Which Azure service should they use as the primary analytics platform?

Accepted Answer

B) Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool. Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool is the correct choice because it can ingest streaming data from Event Hubs via pipelines, query historical CSV data in Data Lake Storage Gen2 directly using T-SQL without moving it, and join both datasets in near real-time using the serverless SQL pool's ability to reference external data sources. This meets the requirement for standard T-SQL queries on combined streaming and historical data without data movement.

Answer

A) Azure Stream Analytics

Answer

C) Azure SQL Database

Answer

D) Azure Databricks

Question 20

Refer to the exhibit. You are reviewing an Azure Resource Manager template for a Blob Storage container named 'sales'. The container has versioning enabled. A developer accidentally overwrites a blob. What is the simplest way to recover the previous version?

Accepted Answer

Access the previous version through the version list and restore it. Azure Blob Storage versioning automatically maintains a history of blob versions. When a blob is overwritten, the previous version is preserved and can be accessed via the version list. The simplest recovery method is to promote the previous version to the current version, which restores the blob to its prior state without needing additional services or configurations.

Answer

Use blob soft delete to recover the blob

Answer

Restore from a backup using Azure Backup

Answer

Perform a point-in-time restore of the container