DP-900 Practice Test 18 — 15 Questions

Question 1

A company needs to store JSON documents that require flexible schema and low-latency access globally. Which Azure data service should they use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that natively supports JSON documents with flexible schema. It offers turnkey global distribution, single-digit-millisecond latency at the 99th percentile, and multiple consistency models, making it ideal for low-latency access worldwide.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 2

A data analyst needs to create interactive dashboards that display real-time data from Azure SQL Database. Which Microsoft tool should they use?

Accepted Answer

Power BI. Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports, and it supports real-time data connectivity to Azure SQL Database through DirectQuery or streaming datasets. This allows the data analyst to visualize live data without manual refreshes, meeting the requirement for real-time dashboards.

Answer

Microsoft Excel

Answer

Microsoft Copilot

Answer

Azure Data Studio

Question 3

Your company wants to use Microsoft Fabric to create a unified analytics platform. Which component in Microsoft Fabric provides a lake-centric, collaborative, and governed data foundation?

Accepted Answer

OneLake. OneLake is the correct answer because it is the single, unified, lake-centric data foundation in Microsoft Fabric. It provides a multi-cloud, SaaS-based data lake that is automatically provisioned for every Fabric tenant, enabling collaborative and governed access to data without data duplication, while supporting open formats like Delta Parquet.

Answer

Power BI

Answer

Data Factory

Answer

Synapse Data Engineering

Question 4

Which THREE are valid use cases for Azure Cosmos DB?

Accepted Answer

Storing IoT telemetry data with low latency. Azure Cosmos DB is a globally distributed, multi-model NoSQL database designed for low-latency, high-throughput workloads, making option A correct: storing IoT telemetry data benefits from its single-digit-millisecond reads/writes and elastic scale across regions. Option C is correct because session state management for web applications fits Cosmos DB's key-value/document model with a TTL (time-to-live) feature that automatically expires session data, plus low-latency access from any region. Option D is correct because personalization and recommendation engines rely on fast lookups of user profiles and interaction data, which Cosmos DB supports through its multi-model APIs and global distribution. Option B is not valid because relational OLTP with complex joins requires a relational engine with strong schema enforcement and JOIN semantics, which Cosmos DB does not provide as a core capability. Option E is not valid because storing large files like images and videos is better suited to Azure Blob Storage, since Cosmos DB is optimized for small, structured JSON documents rather than large binary objects.

Answer

Relational OLTP with complex joins

Answer

Storing large files like images and videos

Question 5

Which TWO Azure services can be used to store semi-structured data like JSON or Parquet files for analytics? (Choose two.)

Accepted Answer

Azure Blob Storage. Azure Blob Storage (B) is correct because it provides massively scalable object storage that can hold semi-structured files such as JSON and Parquet in containers, and it is commonly used as a landing/staging area for analytics workloads. Azure Data Lake Storage Gen2 (D) is correct because it builds on Blob Storage with a hierarchical namespace, POSIX-like ACLs, and optimized performance for big-data analytics, making it the standard store for JSON, Parquet, and other semi-structured files consumed by engines like Synapse Spark and Databricks. Azure Synapse Analytics (A) is an analytics service with SQL and Spark pools rather than a primary storage service for JSON/Parquet files, Azure SQL Database (C) is a relational database management system designed for structured tabular data, and Azure Cosmos DB (E) is a globally distributed NoSQL database for operational document/key-value workloads, not a file-based analytics data store.

Answer

Azure Synapse Analytics

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 6

Which THREE components are part of Microsoft Fabric's end-to-end analytics platform? (Choose three.)

Accepted Answer

Synapse Data Engineering. Synapse Data Engineering is a core component of Microsoft Fabric, providing a unified platform for data ingestion, transformation, and orchestration using Spark and pipelines. It integrates seamlessly with OneLake for storage and Power BI for visualization, forming part of Fabric's end-to-end analytics solution.

Answer

Azure Machine Learning

Answer

Azure DevOps

Question 7

Which TWO Azure data services are classified as NoSQL databases? (Choose two.)

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB (B) is Microsoft's globally distributed, multi-model NoSQL database service, supporting APIs such as Core (SQL), MongoDB, Cassandra, Gremlin, and Table, which makes it a canonical NoSQL offering. Azure Table Storage (C) is a key-attribute (key-value) NoSQL store for semi-structured data, exposing entities via PartitionKey/RowKey without a fixed relational schema. By contrast, Azure SQL Managed Instance (A) and Azure SQL Database (E) are relational PaaS engines built on the SQL Server engine using T-SQL, and Azure Database for PostgreSQL (D) is a relational open-source engine, so all three are relational (SQL) rather than NoSQL databases.

Answer

Azure SQL Managed Instance

Answer

Azure Database for PostgreSQL

Answer

Azure SQL Database

Question 8

Refer to the exhibit. You are storing product data in Azure Cosmos DB using the SQL API. The JSON shows a sample document. You need to query for all products in the 'Electronics' category with a price less than 200. Which query should you use?

Accepted Answer

SELECT * FROM c WHERE c.category = 'Electronics' AND c.price < 200. It uses the correct syntax: SELECT * FROM c WHERE c.category = 'Electronics' AND c.price < 200. It uses single quotes for the string value, appropriate alias 'c' from the FROM clause, and the AND operator to combine both conditions. Option A uses OR, which would return products that are either in Electronics or have price < 200, not both. Option B uses double quotes for the string, which is invalid in Cosmos DB SQL API. Option C uses alias 'p' but the FROM clause uses 'c', causing an error. Thus, D is the correct query.

Answer

SELECT * FROM c WHERE c.category = 'Electronics' OR c.price < 200

Answer

SELECT * FROM c WHERE c.category = "Electronics" AND c.price < 200

Answer

SELECT * FROM p WHERE p.category = 'Electronics' AND p.price < 200

Question 9

A logistics company uses Azure SQL Database to store millions of shipment records. The table has columns: ShipmentID (primary key), CustomerID, ShipDate, and Destination. Queries frequently filter by CustomerID and ShipDate to retrieve shipments for a specific customer over a date range. Which indexing strategy will most improve query performance?

Accepted Answer

Create a nonclustered index on CustomerID and ShipDate. A nonclustered index on CustomerID and ShipDate is the best choice because it directly supports the frequent query pattern filtering by both columns. This composite index allows SQL Database to perform an index seek rather than a full table scan, drastically reducing I/O for selective queries over millions of rows.

Answer

Create a clustered index on ShipmentID

Answer

Partition the table by ShipmentID

Answer

Create a full-text index on Destination

Question 10

You need to store a collection of JSON documents that contain user profile data. The data is frequently queried by user ID and by email address. The solution must support indexing on multiple fields and provide low-latency queries. Which Azure service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a NoSQL database that supports indexing on multiple fields and provides low-latency queries on JSON documents. Option A is wrong because Azure Table Storage is a key-value store with limited indexing (only on partition key and row key). Option B is wrong because Azure Cache for Redis is an in-memory cache, not a durable indexed store. Option D is wrong because Azure Blob Storage stores unstructured blobs and does not support indexing on document fields.

Answer

Azure Table Storage

Answer

Azure Cache for Redis

Answer

Azure Blob Storage

Question 11

Your company has an Azure SQL Database that stores customer orders. You notice that long-running reports are causing blocking on the transactional tables. Which approach would minimize impact on transaction processing while still allowing reporting?

Accepted Answer

Create a read-only replica in Azure SQL Database Hyperscale. Creating a read-only replica offloads reporting queries to a separate copy, avoiding blocking on the primary transactional tables. Option A is wrong because READ UNCOMMITTED reduces blocking but risks dirty reads and inconsistent data. Option B is wrong because increasing the service tier to Business Critical provides more resources but does not separate reporting load. Option C is wrong because sharding distributes data across databases but does not prevent blocking; reporting queries still run on the same shards as transactional queries.

Answer

Use the READ UNCOMMITTED isolation level for reporting queries

Answer

Increase the service tier to Business Critical

Answer

Shard the database by customer region

Question 12

Refer to the exhibit. You have a Power BI measure defined as shown. What does this measure return?

Accepted Answer

The total sales amount for online channel only.. The measure uses CALCULATE to modify the filter context, summing the Amount column only where Channel is 'Online'. Therefore, it returns the total sales amount for the online channel only. Option D is correct. Option A is incorrect because it counts rows, not sums amounts. Option B is incorrect because it sums Amount for each product, not the total. Option C is incorrect because it sums amounts for all channels, not just online.

Answer

The count of online sales transactions.

Answer

The sum of Amount for each product sold online.

Answer

The total sales amount for all channels.

Question 13

Refer to the exhibit. An Azure Policy is defined as shown. A database administrator attempts to create an Azure SQL Database without enabling zone redundancy. What will happen?

Accepted Answer

The database creation will be denied. The policy has an effect of 'deny', so any attempt to create an Azure SQL Database without zone redundancy will be denied. Option B is correct because the policy explicitly denies the creation. Option A is incorrect because the effect is deny, not audit. Option C is incorrect because the policy denies creation rather than enabling zone redundancy automatically. Option D is incorrect because the policy denies the creation entirely, it does not allow creation with zone redundancy disabled.

Answer

The database will be created and an audit event will be logged

Answer

The database will be created and zone redundancy will be automatically enabled

Answer

The database will be created with zone redundancy disabled

Question 14

A company uses Azure Synapse Analytics for its data warehouse. They notice that query performance is degrading over time as data grows. Which action would most likely improve performance without requiring additional compute resources?

Accepted Answer

Partition large tables based on date or other high-cardinality columns. Partitioning large tables on a high-cardinality column like date enables partition elimination, where queries only scan relevant partitions instead of the entire table. This reduces I/O and improves performance without requiring additional compute resources, as it optimizes data access patterns within the existing Synapse SQL pool.

Answer

Migrate to a star schema on a separate Azure SQL Database

Answer

Increase the Synapse SQL pool service level

Answer

Remove columnstore indexes from large tables

Question 15

A company uses Azure SQL Database for an employee management system. The Employees table has 10 million rows and a clustered index on EmployeeID (the primary key). Queries that filter employees by Department and then sort by HireDate are very slow. Which indexing strategy will most improve performance for these queries?

Accepted Answer

Create a nonclustered index on (Department, HireDate) and include the other needed columns as included columns.. A nonclustered index on (Department, HireDate) with included columns is optimal because it supports both the WHERE clause filter on Department and the ORDER BY on HireDate as a covering index. The index key order matches the query's filter and sort requirements, allowing SQL Server to perform a single index seek and avoid key lookups by including all needed columns. This eliminates the need to scan the clustered index or sort rows after filtering.

Answer

Create a nonclustered index on (HireDate, Department) with no included columns.

Answer

Create a clustered index on Department.

Answer

Drop the existing clustered index and recreate a clustered columnstore index.