DP-900 Practice Test 20 — 15 Questions

Question 1

You are designing a multi-tenant SaaS application using Azure SQL Database. Each tenant has its own database. You need to perform maintenance across all databases efficiently. Which feature should you use?

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

Accepted Answer

Elastic Jobs. (Elastic Jobs) is correct because Elastic Jobs allows executing T-SQL scripts across multiple databases in Azure SQL Database. Option A (Elastic pools) is for resource pooling and sharing, not for running scripts across databases. Option B (Failover groups) is for high availability and disaster recovery. Option C (SQL Server Agent) is not available in Azure SQL Database; it is available in on-premises SQL Server or SQL Server on Azure VMs.

Answer

Elastic pools

Answer

Failover groups

Answer

SQL Server Agent

Question 2

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 3

A company stores customer data in an Azure SQL Database. To comply with data residency requirements, they need to ensure that all customer data remains within a specific Azure region. Which feature should they use?

Accepted Answer

Use Azure Policy to restrict resource creation to allowed regions. To comply with data residency requirements, the company must ensure that all customer data remains within a specific Azure region. Azure Policy allows administrators to define and enforce rules that restrict resource creation to allowed regions, thereby preventing the deployment of Azure SQL Database or any other resources outside that region. This directly supports data residency by controlling where data can be stored. In contrast, geo-replication (option B) replicates data to a secondary region, which would move data out of the specified region, violating the requirement. Dynamic data masking (option C) and transparent data encryption (option D) are security features that protect data at rest or in use but do not control geographic placement. Therefore, Azure Policy is the correct feature to meet the data residency requirement.

Answer

Enable geo-replication

Answer

Configure dynamic data masking

Answer

Enable transparent data encryption (TDE)

Question 4

A company runs a web application on Azure SQL Database that experiences unpredictable spikes in traffic. They want to automatically adjust compute resources based on demand without manual intervention and without over-provisioning. Which Azure SQL Database feature should they use?

Accepted Answer

Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand, pausing databases during idle periods and resuming them when traffic spikes occur. This eliminates the need for manual intervention and prevents over-provisioning by charging only for the compute used per second, making it ideal for unpredictable traffic patterns.

Answer

Active geo-replication

Answer

Hyperscale service tier

Answer

Read scale-out

Question 5

A developer is designing a new application that requires a relational database. The database must support complex queries and stored procedures. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database, is the correct choice because it is a fully managed relational database service that supports complex queries (e.g., joins, subqueries) and stored procedures, which are essential for the described application. Option B (Azure Table Storage) is a NoSQL key-value store and does not support relational queries or stored procedures. Option C (Azure Cosmos DB) is a NoSQL database that, while it can support some query capabilities, is not designed for complex relational queries and stored procedures. Option D (Azure Blob Storage) is object storage for unstructured data and lacks relational features.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 6

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 7

A global e-commerce company uses Azure SQL Database for its order management system. They need to ensure high availability with the ability to fail over to an Azure region in a different continent in case of a regional outage. They also want to use the secondary database for read-intensive reporting without affecting the primary's performance. Which Azure SQL Database feature should they enable?

Accepted Answer

Active geo-replication. Active geo-replication is the correct choice because it creates readable secondary replicas of an Azure SQL Database in a different Azure region (including a different continent). It supports manual failover to the secondary region during an outage, and the secondary can be used for read-only query workloads like reporting without impacting the primary database's performance.

Answer

Long-term backup retention

Answer

Automatic tuning

Answer

Connection pooling

Question 8

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 9

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 10

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 11

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 12

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 13

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.

Question 14

A manufacturing company uses IoT sensors to collect temperature and vibration data from machinery. They need to analyze the streaming data in real time to detect anomalies and trigger alerts. Additionally, they need to run complex historical queries on months of sensor data to identify equipment failure patterns. They want a single Azure service that can handle both real-time stream processing and large-scale batch analytics using a unified query language, minimizing the need for separate technologies. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified platform that combines both real-time stream processing (via Synapse Pipelines and Spark Streaming) and large-scale batch analytics (via Synapse SQL and Spark) using a single query language (T-SQL or Spark SQL). This eliminates the need for separate technologies, directly addressing the requirement for a single service to handle both streaming and historical batch analysis.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage Gen2

Answer

Azure HDInsight

Question 15

A social networking application uses Azure Cosmos DB to store user posts. When a user publishes a new post, they immediately refresh their feed and expect to see their own post. However, the application can tolerate temporary staleness for posts from other users (e.g., a few seconds delay). Which Azure Cosmos DB consistency level should the application use for read operations that display the feed?

Accepted Answer

Session. Session consistency is the correct choice because it guarantees that the user who writes a post will read their own write within the same session, while allowing other users to see slightly stale data. This matches the requirement: the author immediately sees their new post, but the application can tolerate a few seconds of staleness for other users' posts. Session consistency uses a session token to ensure monotonic reads and writes for the same client, making it ideal for per-user feed scenarios.

Answer

Strong

Answer

Bounded staleness

Answer

Eventual