DP-900 Identify considerations for relational data on Azure Practice Test 9 — 15 Questions

Question 1

A company runs a mission-critical SQL Server database on-premises. They plan to migrate to Azure SQL Database and need to choose the appropriate service tier. The database is currently 500 GB and is expected to grow to 8 TB within two years. The workload is read-heavy with many concurrent users, and they require fast scaling of compute resources without significant downtime. Which Azure SQL Database service tier should they choose?

Accepted Answer

Hyperscale. Hyperscale is the correct choice because it supports databases up to 100 TB, far exceeding the expected 8 TB growth, and provides fast scaling of compute resources without downtime by using a distributed architecture with separate compute and storage nodes. Its read-heavy workload with many concurrent users benefits from Hyperscale's multiple readable replicas and buffer pool extension, ensuring high performance and availability.

Answer

General Purpose

Answer

Business Critical

Answer

Serverless

Question 2

A retail company runs analytical reporting queries on a large Sales table in Azure SQL Database. The table contains over 100 million rows and is updated daily with new transactions. The queries aggregate data by product and month, scanning millions of rows per query. The company wants to significantly reduce query execution time without changing the queries. Which indexing strategy should they implement?

Accepted Answer

Create a clustered columnstore index on the table.. A clustered columnstore index is ideal for large data warehousing and analytical workloads because it stores data column-wise, enabling high compression and batch-mode processing. For queries that aggregate millions of rows by product and month, columnstore indexes dramatically reduce I/O and CPU by scanning only the necessary columns and using segment elimination, which directly addresses the requirement to reduce query execution time without changing the queries.

Answer

Create a nonclustered index on the ProductID column.

Answer

Create a filtered index for the most recent month's data.

Answer

Create a clustered rowstore index (default) and rely on database compression.

Question 3

A company runs an e-commerce application on Azure SQL Database. The database has a table named Orders with columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). The application frequently runs the following query: SELECT * FROM Orders WHERE CustomerID = 12345 AND OrderDate BETWEEN '2025-01-01' AND '2025-01-31' ORDER BY OrderDate DESC. The table contains 10 million rows. Which index would best optimize this query?

Accepted Answer

A nonclustered index on (CustomerID, OrderDate DESC) including TotalAmount as included column.. Option B is correct because the query filters on both CustomerID and OrderDate, so a composite nonclustered index on (CustomerID, OrderDate DESC) allows SQL Server to perform an index seek on CustomerID and then an ordered range scan on OrderDate, avoiding a sort operation. Including TotalAmount as an included column makes the index covering, so the query can be satisfied entirely from the index without key lookups to the clustered index.

Answer

A nonclustered index on OrderDate only.

Answer

A clustered index on (OrderDate, CustomerID).

Answer

A nonclustered index on (OrderDate DESC) only.

Question 4

An e-commerce company uses Azure SQL Database for order processing. The Orders table has columns: OrderID (unique, clustered index), CustomerID, OrderDate, Status, TotalAmount. A common query filters on CustomerID and OrderDate, and sorts by OrderDate descending. The query also returns TotalAmount. Which indexing strategy will produce the best query performance?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount). Option A is correct because it creates a covering index that supports both the equality filter on CustomerID and the range/sort on OrderDate DESC. By including TotalAmount as an included column, the query can be satisfied entirely from the nonclustered index without key lookups to the clustered index, minimizing I/O and improving performance.

Answer

Create a nonclustered index on (OrderDate) INCLUDE (CustomerID, TotalAmount)

Answer

Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount)

Answer

Create a nonclustered index on (CustomerID) INCLUDE (OrderDate, TotalAmount)

Question 5

A company runs a global e-commerce application on Azure SQL Database. The application has a read-intensive workload with millions of users querying product details simultaneously. The database is experiencing high read latency during peak hours due to the volume of concurrent read requests. The company wants to scale read performance without changing the application code and without affecting write operations. Which Azure SQL Database feature should they implement?

Accepted Answer

Active geo-replication. Active geo-replication creates readable secondary replicas of the Azure SQL Database in different Azure regions. By configuring read-only routing to these secondaries, the application can offload read queries from the primary database, scaling read performance without any code changes and without impacting write operations on the primary.

Answer

Elastic pools

Answer

In-memory OLTP

Answer

Columnstore indexes

Question 6

A company plans to migrate an on-premises SQL Server database to Azure. The database currently uses SQL Server Agent jobs for scheduled maintenance tasks, cross-database queries, and query store for performance tuning. The database size is 500 GB and needs to scale to 10 TB eventually. They want a managed service that requires minimal application changes. Which Azure relational database service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, cross-database queries, and Query Store, while being a fully managed PaaS service. It allows scaling up to 10 TB (up to 16 TB with some configurations) with minimal application changes, as it uses the same T-SQL surface area and network configuration (VNet) as on-premises SQL Server.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database Hyperscale

Answer

Azure Database for SQL Server

Question 7

Drag and drop the steps to load data into Azure Synapse Analytics using PolyBase in the correct order.

Question 8

Drag and drop the steps to ingest data into Azure Data Explorer (ADX) in the correct order.

Question 9

Match each data processing term to its definition.

Question 10

Match each Azure data security feature to its description.

Question 11

Match each Azure data consistency model to its description.

Question 12

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. Option B is correct because Azure SQL Database geo-replication allows configuring a secondary replica in a different region, but the question asks to keep data within a specific region; however, the scenario is about ensuring data does not leave the region. Actually, to keep data within a region, you should not use geo-replication. But the correct answer is to use a single-region deployment; but among options, 'Geo-replication' would allow cross-region, so not that. 'Failover groups' also. 'Azure Policy' can enforce resource location, but not data. 'Data masking' is for security. Wait, re-evaluating: The best answer is 'Azure Policy' to restrict resource creation to a region, but data still could be replicated? Actually, to ensure data remains within a region, you can use 'Geo-replication' to replicate to same region? No. Let's think: The requirement is to keep data within a specific region. Geo-replication is used for disaster recovery across regions, so that would violate. The correct approach is to use Azure Policy to enforce that resources are only created in allowed regions, and also to disable geo-replication. Among options, 'Azure Policy' is the most direct. However, the stem implies a feature of SQL Database itself. Actually, Azure SQL Database allows configuring a 'geo-replication' secondary in the same region? No, geo means different region. So 'Failover groups' also cross-region. 'Data masking' is irrelevant. 'Transparent Data Encryption' (TDE) is for encryption at rest, not residency. So 'Azure Policy' is the correct answer.

Answer

Enable geo-replication

Answer

Configure dynamic data masking

Answer

Enable transparent data encryption (TDE)

Question 13

A company uses Azure SQL Database for a mission-critical application. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss. The recovery point objective (RPO) is 5 seconds. Which deployment option should they choose?

Accepted Answer

Azure SQL Database with active geo-replication and a secondary in a paired region. Option C is correct because Azure SQL Database active geo-replication with a secondary in a paired region provides a configurable RPO of 5 seconds using synchronous mode. Option A is wrong because failover groups use asynchronous replication by default, RPO is higher. Option B is wrong because auto-failover groups are similar. Option D is wrong because a single database in the same region cannot protect against a regional outage.

Answer

Azure SQL Database with failover group enabled

Answer

Azure SQL Database single instance with locally redundant storage

Answer

Azure SQL Database with auto-failover group

Question 14

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. Option B is correct because Azure SQL Database is a fully managed relational database that supports complex queries and stored procedures. Option A is wrong because Azure Cosmos DB is NoSQL. Option C is wrong because Azure Table Storage is NoSQL key-value. Option D is wrong because Azure Blob Storage is object storage.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 15

A company has an on-premises SQL Server database that they want to migrate to Azure with minimal changes to the application. They need to use the least amount of administrative effort for patching and backups. Which Azure service should they choose?

Accepted Answer

Azure SQL Database. Option D is correct because Azure SQL Database is a fully managed PaaS service that automates patching and backups. Option A is wrong because SQL Server on Azure VMs requires manual patching and backup management. Option B is wrong because Azure SQL Managed Instance also automates patching but is more complex than SQL Database. Option C is wrong because Azure Database for MySQL is a different database engine.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL