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

Question 1

A small online retailer wants to migrate its single on-premises SQL Server database to Azure. They require a fully managed relational database service with built-in high availability, automated backups, and no need to manage virtual machines. They do not need features like multiple databases with cross-database queries or SQL Agent. Which Azure service should they choose?

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

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that provides built-in high availability (99.99% SLA with zone-redundant configuration), automated backups with point-in-time restore, and eliminates the need to manage virtual machines or operating system patches. It is the ideal choice for a single database migration when features like cross-database queries and SQL Agent are not required.

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Question 2

A company uses Azure SQL Database for an e-commerce platform. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and a range of OrderDate, and then sort the results by OrderDate in descending order. The queries also return the TotalAmount column. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) with included column TotalAmount. It creates a covering index that matches the query's filter predicates (CustomerID equality, OrderDate range) and sort order (OrderDate DESC). By including TotalAmount as an included column, the index fully satisfies the query without needing to access the clustered index (key lookup), minimizing I/O and improving performance.

Answer

Create a nonclustered index on (OrderDate DESC, CustomerID) with included column TotalAmount

Answer

Change the clustered index to be on (CustomerID, OrderDate DESC)

Answer

Create a nonclustered index on (OrderDate DESC) without including TotalAmount

Question 3

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 4

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 5

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.. 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 6

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). 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 7

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 8

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 9

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

Accepted Answer

Create external data source, then external file format, then external table, then use CTAS.. PolyBase loading involves defining external data source, file format, external table, then using CTAS to move data into the warehouse.

Answer

Create external file format, then external data source, then external table, then use CTAS.

Answer

Create external table, then external data source, then external file format, then use CTAS.

Answer

Use CTAS, then create external data source, then external file format, then external table.

Question 10

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

Accepted Answer

Create the target table, then define mapping, then execute the ingestion, then verify.. Ingestion involves creating the target table, defining mapping, executing the ingestion, and verifying.

Answer

Define mapping, then create the target table, then execute the ingestion, then verify.

Answer

Execute the ingestion, then verify, then create the target table, then define mapping.

Answer

Create the target table, then execute the ingestion, then verify, then define mapping.

Question 11

Match each data processing term to its definition.

Accepted Answer

Batch processing: Processes large volumes of data at scheduled intervals.. Batch processing handles large volumes at scheduled times, while stream processing handles real-time data. ETL transforms before loading; ELT transforms after loading. Common confusion is swapping ETL and ELT definitions or misapplying batch/stream terms.

Answer

Stream processing: Processes data in batches at scheduled intervals.

Answer

ETL: Extract, Load, Transform - loads raw data before transformation.

Question 12

Match each Azure data consistency model to its description.

Accepted Answer

Strong: Reads always return the most recent write.. Azure Cosmos DB offers five consistency levels: strong, bounded staleness, session, consistent prefix, and eventual. Strong returns the most recent write; bounded staleness allows a bounded lag; session ensures per-session guarantees.

Answer

Strong: Reads may lag behind writes by up to 5 seconds.

Answer

Session: Guarantees that all replicas have the same data at all times.

Question 13

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. Azure Policy can enforce that resources are only created in approved Azure regions, which directly satisfies data residency requirements by preventing deployment of the Azure SQL Database (or any resource) outside the permitted geography. This is the governance control designed for exactly this compliance scenario.

Answer

Enable geo-replication

Answer

Configure dynamic data masking

Answer

Enable transparent data encryption (TDE)

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. Azure SQL Database is a fully managed relational database platform (PaaS) built on the SQL Server engine, supporting T-SQL, complex joins, views, stored procedures, and transactions. It is the correct choice for applications requiring relational modeling with advanced query capabilities.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 15

A startup wants to build a new web application with a relational database. They expect variable traffic and want to minimize costs by paying only for the compute resources they use. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically pauses the database during periods of inactivity and resumes it when traffic returns, charging only for the compute resources consumed. This makes it ideal for a startup with variable traffic that wants to minimize costs by paying only for what they use.

Answer

Provisioned compute tier

Answer

Elastic pool

Answer

SQL Server on Azure Virtual Machines