DP-900 Practice Test 28 — 15 Questions

Question 1

A social media application stores user profiles as JSON documents. Each profile has standard fields like userId, name, and email, but also optional fields such as education and work history. The application needs to query profiles by userId with low latency and also run SQL-like queries to find all profiles with a specific work history value. Which Azure Cosmos DB API should they choose?

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

Accepted Answer

SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports querying JSON documents with SQL-like syntax, enabling both low-latency point reads by userId and complex queries on nested fields like work history. It provides automatic indexing of all JSON properties, which ensures efficient execution of queries across optional fields without requiring schema management.

Answer

MongoDB API

Answer

Gremlin (Graph) API

Answer

Table API

Question 2

A global social media startup stores user profiles as JSON documents in Azure Cosmos DB. Their application frequently reads profiles by user ID and also runs queries to find users based on location or interests. The workload is read-heavy with high throughput requirements. The operations team notices that query performance degrades during peak hours. Which action would most effectively improve query performance?

Accepted Answer

Increase the provisioned throughput (RU/s). Increasing the provisioned throughput (RU/s) directly allocates more processing capacity to the Cosmos DB container, allowing it to handle higher request volumes and reduce throttling during peak hours. Since the workload is read-heavy and query performance degrades under high throughput demands, raising RU/s is the most effective and immediate action to improve performance.

Answer

Increase the number of containers

Answer

Choose a different API (e.g., switch from SQL API to MongoDB API)

Answer

Switch to a different Azure region

Question 3

A data engineer loads raw log files into a storage system. The structure of the data is interpreted at the time of reading, allowing queries to apply schema on the fly without preprocessing. This approach is best described as:

Accepted Answer

Schema-on-read. Schema-on-read means the data is stored in its raw, unstructured form, and the schema is applied dynamically when the data is queried. This is exactly what happens when raw log files are loaded into a storage system like Azure Data Lake Storage and queried with tools like Azure Synapse Serverless SQL or Apache Spark, which infer the schema at query time without requiring preprocessing.

Answer

Schema-on-write

Answer

Data warehouse

Answer

Data virtualization

Question 4

A company needs to store semi-structured data from IoT devices, including temperature readings and device status. The data will be queried by time range and device ID. Which Azure data service is most cost-effective for this use case?

Accepted Answer

Azure Table Storage. Azure Table Storage is a NoSQL key-value store that is optimized for storing large amounts of semi-structured data without requiring a fixed schema. It supports efficient queries by partition key (device ID) and row key (timestamp), making it ideal for time-series IoT data at a lower cost than other Azure data services.

Answer

Azure Blob Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 5

A company runs an e-commerce application on Azure SQL Database. During seasonal promotions, traffic spikes significantly, but at other times traffic is low. They want to automatically adjust compute resources based on demand without manual intervention or provisioning. Which Azure SQL Database feature should they use?

Accepted Answer

Serverless compute. Serverless compute for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during inactive periods, charging only for storage and compute used per second. This matches the requirement for automatic adjustment without manual intervention or provisioning, especially for intermittent, unpredictable traffic spikes like seasonal promotions.

Answer

Geo-replication

Answer

Elastic pools

Answer

Hyperscale

Question 6

A database administrator is explaining to a colleague that a database transaction must ensure that either all operations within it succeed or none of them take effect. Which ACID property is being described?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work: either all operations within it are committed successfully, or none are applied. This is the property that guarantees the 'all-or-nothing' behavior described in the question. In Azure SQL Database or SQL Server, atomicity is enforced through the transaction log and the write-ahead logging (WAL) protocol, which records changes before they are written to disk.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 7

You are designing a data pipeline that ingests sales transactions from an on-premises SQL Server database into Azure Synapse Analytics for reporting. The data must be processed incrementally every hour with minimal latency. Which Azure service should you use to orchestrate the pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data orchestration service designed specifically for building complex, schedule-driven pipelines. It natively supports incremental data loading from on-premises SQL Server via self-hosted integration runtime, and can trigger pipelines on an hourly schedule with minimal latency, making it ideal for this scenario.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions

Question 8

A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows. Queries frequently aggregate data for the current month, but also need to retain historical data for 7 years. The company wants to improve query performance for the monthly aggregations and simplify data archiving. Which design should they implement?

Accepted Answer

Partition the table by month and create aligned indexes.. Partitioning the Transactions table by month allows SQL Server to perform partition elimination during queries that aggregate data for the current month, scanning only the relevant partition(s) instead of the entire table. Aligned indexes ensure that index structures follow the same partition scheme, maintaining efficiency for both queries and maintenance. This design also simplifies data archiving by enabling fast partition switching to move older months out of the table without costly delete operations.

Answer

Create a clustered columnstore index on the entire table.

Answer

Use Azure SQL Database elastic pool for the database.

Answer

Implement transparent data encryption.

Question 9

A company uses Azure Synapse Analytics dedicated SQL pool as its data warehouse. New data is loaded into the warehouse every few minutes. The company wants to visualize the data with near real-time updates in a dashboard that can be refreshed automatically. Which tool and connection mode should they use?

Accepted Answer

Power BI with DirectQuery mode. Power BI with DirectQuery mode is correct because it allows the dashboard to query the Azure Synapse dedicated SQL pool directly for each visual refresh, enabling near real-time updates without importing data. This mode avoids the latency of data import and supports automatic page refresh, which aligns with the requirement for data loaded every few minutes.

Answer

Azure Data Studio with visualizations

Answer

SQL Server Reporting Services (SSRS) with cached reports

Answer

Excel Power Pivot with imported data

Question 10

A company wants to store JSON documents that need to be queried with high throughput and low latency globally. Which Azure data service is most appropriate?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the most appropriate service because it is a globally distributed, multi-model database that natively supports JSON documents and provides guaranteed single-digit-millisecond latency at the 99th percentile, along with high throughput via configurable request units (RUs). Its turnkey global distribution enables low-latency reads and writes across multiple Azure regions, making it ideal for globally queried JSON workloads.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 11

Drag and drop the steps to create an Azure Data Lake Storage Gen2 account in the correct order.

Accepted Answer

Step 1: Create a storage account with Standard performance and Locally-redundant storage. Step 2: On the Advanced tab, enable hierarchical namespace for Data Lake Storage Gen2. Step 3: Configure networking and data protection settings. Step 4: Review the configuration and create the storage account.. Creating ADLS Gen2 requires enabling the hierarchical namespace feature on a standard storage account.

Answer

Step 1: On the Advanced tab, enable hierarchical namespace. Step 2: Create a storage account with Standard performance and Locally-redundant storage. Step 3: Configure networking and data protection settings. Step 4: Review and create.

Answer

Step 1: Create a storage account with Standard performance and Locally-redundant storage. Step 2: Configure networking and data protection settings. Step 3: On the Advanced tab, enable hierarchical namespace. Step 4: Review and create.

Answer

Step 1: Create a storage account with Standard performance and Locally-redundant storage. Step 2: On the Advanced tab, enable hierarchical namespace. Step 3: Review and create. Step 4: Configure networking and data protection settings.

Question 12

A company wants to store JSON documents from IoT devices with low latency and high availability. Which Azure data store should they use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a fully managed NoSQL database designed for low-latency, high-availability workloads, with native support for JSON documents. It offers single-digit millisecond read/write latencies at the 99th percentile, global distribution with multi-region writes, and multiple consistency models, making it ideal for IoT scenarios that require fast, always-on access to semi-structured data.

Answer

Azure Blob Storage

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 13

You are designing a batch processing pipeline that runs nightly to transform CSV files from an FTP server into Parquet files in Azure Data Lake Storage. Which Azure service should you use to orchestrate the pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data integration service designed specifically for orchestrating and automating data pipelines. It supports scheduled triggers (e.g., nightly runs), native connectors for FTP and Azure Data Lake Storage, and built-in data transformation activities like Copy Data and Mapping Data Flows to convert CSV to Parquet. ADF's control flow and dependency management make it ideal for batch processing pipelines.

Answer

Azure Functions

Answer

Azure Logic Apps

Answer

Azure Batch

Question 14

A company stores IoT sensor data in Azure Blob Storage. Data scientists need to query the data using SQL without moving it to another store. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool allows you to query data directly from Azure Blob Storage using T-SQL without moving or copying the data. It uses a distributed query engine that reads files (Parquet, CSV, JSON) in place, making it ideal for ad-hoc analytics over IoT sensor data stored in Blob Storage.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Answer

Azure SQL Database

Question 15

A company stores customer transaction data in Azure Blob Storage. The data is rarely accessed after 30 days, but must be retained for 7 years for compliance. Which access tier minimizes storage cost while meeting the retention requirement?

Accepted Answer

Archive tier. The Archive tier is the correct choice because it offers the lowest storage cost for data that is rarely accessed, which aligns with the scenario where data is accessed infrequently after 30 days but must be retained for 7 years. Azure Blob Storage's Archive tier is designed for long-term retention with a retrieval latency of several hours, making it cost-effective for compliance-driven data that does not require immediate access.

Answer

Hot tier

Answer

Cool tier

Answer

Premium tier