DP-900 Practice Test 47 — 15 Questions

Question 1

A multinational corporation runs a mission-critical relational database on Azure SQL Database. They require automatic failover to a secondary region in case of a regional outage, with no data loss. The secondary region must also be readable for reporting purposes. What should they implement?

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

Accepted Answer

Failover group with a readable secondary in a different region. A failover group with a readable secondary in a different region provides automatic failover (no manual intervention) and the secondary can be used for read-only queries. This meets the requirement of automatic failover, no data loss (synchronous replication when using Premium or Business Critical tiers), and read access for reporting. Option A (Active Geo-Replication) requires manual failover, not automatic. Option B (Read Scale-Out) creates a local readable secondary, not in a different region, and does not provide failover. Option C (Azure Site Recovery) is designed for VM and physical server replication, not for Azure SQL Database.

Answer

Active Geo-Replication with manual failover

Answer

Read Scale-Out with a local secondary replica

Answer

Azure Site Recovery for the database server

Question 2

A retail company receives daily sales data as CSV files in Azure Data Lake Storage Gen2. They need to load this data into an Azure Synapse Analytics dedicated SQL pool every night. The process must be automated, scheduled, and include error handling for failed loads. Which Azure service should they use to orchestrate this 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, can copy CSV files from Azure Data Lake Storage Gen2 into an Azure Synapse dedicated SQL pool, and provides built-in error handling via retry policies, activity-level error outputs, and pipeline failure notifications.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 3

A financial services company stores transaction data in Azure Data Lake Storage Gen2 as Parquet files, partitioned by date. The data volume is 5 TB per day. The analytics team runs ad-hoc SQL queries to detect fraudulent patterns. Queries are highly selective (filtering on AccountID and date range). The team also needs to create external tables and views for use in Power BI. They want to pay only for the data processed by each query and avoid provisioning any compute resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, aligning with the requirement to pay only for data scanned by each query. It also supports creating external tables and views for Power BI, making it ideal for ad-hoc, selective queries on partitioned Parquet files.

Answer

Azure Databricks with interactive clusters

Answer

Azure Stream Analytics

Answer

Azure HDInsight with Spark

Question 4

A gaming company stores player profiles in Azure Cosmos DB using the NoSQL API. Each profile is a JSON document containing fields like playerId, userName, level, inventory (an array of items), and friends (an array of playerIds). The application frequently needs to query all players that have a specific item in their inventory (e.g., 'sword'). Which Cosmos DB feature should they use to support this query efficiently?

Accepted Answer

Indexing policy with a wildcard index. A wildcard index in the indexing policy allows Azure Cosmos DB to automatically index all properties within a JSON document, including nested array elements like those in the 'inventory' array. This enables efficient queries such as 'SELECT * FROM c WHERE ARRAY_CONTAINS(c.inventory, {name: "sword"})' without requiring a custom composite index for each possible item. Without a wildcard index, the query would require a full scan of all documents, which is inefficient at scale.

Answer

Change feed

Answer

Stored procedures

Answer

Composite index

Question 5

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 6

A data analyst needs to visualize sales data from Azure SQL Database in real-time dashboards. Which tool should they use to create interactive reports?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct tool because it is designed specifically for creating interactive, real-time dashboards and reports from various data sources, including Azure SQL Database. It supports live connections and DirectQuery to enable near-real-time visualization without requiring data movement.

Answer

Azure Data Studio

Answer

Azure Synapse Analytics

Answer

Microsoft Excel

Question 7

A business analyst needs to query a large Azure SQL Database table that stores sales transactions. The table contains over 100 million rows. The analyst wants to retrieve aggregated sales per product category for the current month. The current query performs a full table scan and takes several minutes. Which indexing strategy will best improve the performance of this aggregation query?

Accepted Answer

Create a columnstore index on the table. A columnstore index stores data column-wise and uses batch processing, which dramatically accelerates aggregation queries (like SUM, COUNT, GROUP BY) over large tables. For a 100-million-row table, this reduces I/O and CPU by reading only the columns needed for the aggregation, making it the optimal choice for the analyst's current-month sales-per-category query.

Answer

Create a clustered index on the transaction date column

Answer

Create a nonclustered index on the product category column

Answer

Create a filtered index on transactions from the current month

Question 8

A startup is deploying a new application on Azure SQL Database. They expect the database to start at 10 GB but grow to 500 GB over time. They want to be able to scale compute independently of storage and only pay for the compute resources they use. They also want to avoid over-provisioning and automatically pause during idle periods. Which purchasing model and service tier should they choose?

Accepted Answer

vCore-based model with General Purpose serverless tier. The vCore-based General Purpose serverless tier is correct because it allows independent scaling of compute and storage, automatically pauses the database during idle periods to eliminate compute costs, and supports growth from 10 GB to 500 GB without manual intervention. This model aligns with the startup's need to pay only for consumed compute resources and avoid over-provisioning.

Answer

DTU-based model with Basic tier

Answer

vCore-based model with Business Critical provisioned tier

Answer

DTU-based model with Standard tier

Question 9

A data analyst needs to run complex SQL queries against petabytes of historical sales data stored in Azure Data Lake Storage Gen2. The solution must be serverless with pay-per-query pricing. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a serverless, pay-per-query engine that can directly query petabytes of data stored in Azure Data Lake Storage Gen2 using standard T-SQL. It eliminates infrastructure management and charges only for the data processed by each query, making it ideal for ad-hoc, complex SQL workloads on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure HDInsight with Spark

Answer

Azure Analysis Services

Question 10

A social networking application needs to store and query relationships between users, such as 'friends of friends'. The application should be able to traverse these relationships efficiently to recommend new connections. Which Azure NoSQL data store and API should they choose?

Accepted Answer

Azure Cosmos DB with the Gremlin API. Azure Cosmos DB with the Gremlin API is the correct choice because Gremlin is a graph traversal language specifically designed for querying highly connected data, such as social network relationships. It allows efficient traversal of edges (e.g., 'friends of friends') using graph algorithms, which is exactly what the application needs for recommending new connections. Other APIs like Table API or services like Blob Storage lack native graph traversal capabilities.

Answer

Azure Cosmos DB with the Table API

Answer

Azure Cache for Redis

Answer

Azure Blob Storage

Question 11

A company stores user profiles as JSON documents. Each profile includes standard fields (userId, name, email) and optional fields (preferences, history). The application needs fast key lookups by userId and SQL-like queries on optional fields. Which Azure Cosmos DB API should they choose?

Accepted Answer

SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports JSON documents with flexible schemas, enabling fast key-value lookups on the `userId` field (via automatic indexing) and rich SQL-like querying (e.g., `SELECT * FROM c WHERE c.preferences.theme = 'dark'`) on optional fields. It is the only Azure Cosmos DB API that provides a SQL query syntax directly over JSON, making it ideal for mixed workloads of point reads and ad-hoc queries on nested or optional properties.

Answer

MongoDB API

Answer

Cassandra API

Answer

Table API

Question 12

A company uses Azure SQL Database for a financial application. Regulatory compliance requires that database backups be retained for 7 years. The current configuration uses the default point-in-time restore (PITR) retention of 7 days. Which Azure SQL Database feature should the company enable to meet the 7-year retention requirement?

Accepted Answer

Long-term retention (LTR) for backups. Azure SQL Database's default point-in-time restore (PITR) retains backups for only 7 days, which is insufficient for the 7-year regulatory requirement. Long-term retention (LTR) allows you to retain full database backups for up to 10 years by configuring backup policies in the Azure portal or via T-SQL, meeting the compliance need.

Answer

Active geo-replication

Answer

Auto-failover groups

Answer

Geo-redundant backup storage

Question 13

A company uses Azure SQL Database for its order management system. The database has a table named Orders with columns OrderID (INT, PRIMARY KEY), CustomerID (INT), OrderDate (DATE), TotalAmount (DECIMAL). Queries that filter by OrderDate are slow. The database administrator observes that the nonclustered index on OrderDate has high fragmentation and many page splits. Which action will most likely improve query performance for these date-based queries?

Accepted Answer

Rebuild the nonclustered index on OrderDate with a FILLFACTOR of 80.. Rebuilding the nonclustered index on OrderDate with a FILLFACTOR of 80 reduces page splits by leaving free space in each leaf-level page. This accommodates future insertions and updates that modify the OrderDate values, lowering fragmentation and improving query performance for date-based filters.

Answer

Change the data type of TotalAmount from DECIMAL to FLOAT.

Answer

Remove the clustered index on OrderID and create a clustered index on OrderDate.

Answer

Add a columnstore index on the OrderDate column.

Question 14

A company receives customer order data from its online store in a CSV file. Each line contains fields like OrderID, CustomerName, Product, Quantity, and OrderDate. This data is best described as:

Accepted Answer

Structured data. A is correct because the CSV file contains data that conforms to a strict tabular schema with predefined columns (OrderID, CustomerName, Product, Quantity, OrderDate) and consistent data types per column. This rigid, row-and-column format with a fixed schema is the defining characteristic of structured data, which can be directly loaded into a relational database or Azure SQL Database without transformation.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Transactional data

Question 15

A marketing team needs to analyze customer purchase history data stored in Azure SQL Database. They want to create interactive dashboards with drill-down capabilities. 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 with drill-down capabilities using data from Azure SQL Database. It connects directly to Azure SQL Database via built-in connectors, allowing users to build visualizations that support hierarchical navigation and real-time filtering.

Answer

Azure Data Studio

Answer

Microsoft Excel

Answer

Azure Analysis Services