DP-900 Practice Test 31 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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

Question 13

Your company uses Azure SQL Database and needs to ensure that transactions are durable even if the database instance fails. Which feature should you enable?

Accepted Answer

Zone-redundant storage. Zone-redundant storage (ZRS) replicates your Azure SQL Database transaction logs and data files synchronously across three Azure availability zones within the same region. This ensures that even if an entire zone fails, committed transactions are preserved and the database remains available, providing durability at the storage layer without requiring a separate database replica.

Answer

Active geo-replication

Answer

Transparent Data Encryption

Answer

Auto-failover groups

Question 14

You need to design a data storage solution for a global e-commerce application that must support ACID transactions and require minimal latency for point lookups by a unique key. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides global distribution with multi-region writes, guarantees ACID transactions through its transactional batch API, and offers single-digit millisecond latency for point reads by a unique key (e.g., id and partition key). This makes it ideal for a global e-commerce application requiring both strong consistency and low-latency lookups.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 15

A healthcare organization must store patient health records for 7 years to meet regulatory requirements. After 7 years, data must be deleted immediately. They use Azure Blob Storage. Which policy should they implement?

Accepted Answer

Time-based retention policy. A time-based retention policy (immutability policy) in Azure Blob Storage ensures that blobs are stored in a WORM (Write Once, Read Many) state for a specified period, preventing modification or deletion. After the retention period expires, the data can be deleted immediately, meeting the 7-year regulatory requirement. This policy is designed specifically for compliance scenarios where data must be preserved for a fixed duration and then removed.

Answer

Soft delete policy

Answer

Legal hold policy

Answer

Lifecycle management policy with deletion after 7 years