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

Question 1

A database designer is creating a relational database for a library system. Each book can have multiple authors, and each author may have written many books. To avoid data redundancy, the designer creates a separate Authors table and a BookAuthors junction table. This process of organizing data to reduce redundancy and improve integrity is called:

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

Accepted Answer

Normalization. Normalization is the process of organizing data in a relational database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables and defining relationships between them. In this scenario, creating separate Authors and BookAuthors junction tables eliminates the redundancy of storing author information multiple times for each book, which is a classic example of normalization (specifically achieving third normal form). This directly supports the relational database goal of minimizing duplicate data and ensuring consistency.

Answer

Denormalization

Answer

Indexing

Answer

Partitioning

Question 2

An e-commerce application uses Azure SQL Database. During flash sales, the database experiences high CPU usage and query timeouts. The team needs a solution that can handle sudden increases in demand without downtime. Which scaling option should they choose?

Accepted Answer

Hyperscale service tier. The Hyperscale service tier is designed for high-performance, rapidly growing workloads that require instant scalability. It separates compute from storage, allowing compute nodes to be added or scaled up in seconds without downtime, making it ideal for handling sudden spikes in demand like flash sales.

Answer

Read scale-out

Answer

Elastic Pool

Answer

Geo-replication

Question 3

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 4

A multinational e-commerce company uses Azure SQL Database active geo-replication to replicate a critical inventory database to a secondary region. During a regional outage, the application automatically fails over to the secondary database. After the primary region recovers, the administrator wants to make the original primary the main database again without losing any data modifications made on the secondary during the outage. What should the administrator do?

Accepted Answer

Initiate a planned failover to switch back to the original primary.. A planned failover (also known as graceful failover) in Azure SQL Database active geo-replication is designed to switch roles between the primary and secondary databases without data loss. After the original primary region recovers, initiating a planned failover synchronizes all data from the current primary (the former secondary) to the original primary, making it the new primary while preserving all modifications made during the outage. This operation ensures zero data loss because it forces a final synchronization before the role swap.

Answer

Drop the geo-replication relationship, then recreate the secondary from the current primary.

Answer

Perform a forced failover to switch back to the original primary.

Answer

Delete the secondary database and restore the original primary from a backup taken before the outage.

Question 5

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 6

An e-commerce company runs a product inventory database on Azure SQL Database. During a flash sale, write transactions are slow because many read queries are running simultaneously and consuming resources. The company wants to isolate read workloads without modifying application code or database schemas. Which Azure SQL Database feature should they implement?

Accepted Answer

Read scale-out. Read scale-out (B) is correct because it allows read-only queries to be offloaded to a read-only replica of the database, freeing up the primary replica for write transactions. This feature is built into Azure SQL Database at the Business Critical and Premium service tiers, and it requires no application code changes—just a connection string modification to use the `ApplicationIntent=ReadOnly` parameter. It directly addresses the performance bottleneck caused by concurrent read queries during the flash sale.

Answer

Active geo-replication

Answer

Auto-failover groups

Answer

Elastic pools

Question 7

A multinational e-commerce company uses Azure SQL Database for its order processing system. They need to ensure that if an entire Azure region becomes unavailable, the database remains available with minimal data loss and automatic failover. Which feature should they implement?

Accepted Answer

Active geo-replication. Active geo-replication (Option A) is correct because it creates readable secondary replicas of an Azure SQL Database in a paired Azure region, enabling automatic failover during a regional outage. This feature provides a recovery point objective (RPO) of as low as 5 seconds and a recovery time objective (RTO) of under 1 hour, ensuring minimal data loss and high availability.

Answer

Automatic tuning

Answer

Elastic pools

Answer

Serverless compute

Question 8

An e-commerce company uses Azure SQL Database for its product catalog. During promotional events, the database experiences unpredictable spikes in traffic. The company wants a solution that automatically adjusts compute resources based on demand without manual intervention. Which Azure SQL Database option should they use?

Accepted Answer

C) Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during idle periods, charging only for storage and used compute. This matches the requirement for handling unpredictable traffic spikes without manual intervention, as it provides instant scaling and cost efficiency for intermittent workloads.

Answer

A) Read replicas

Answer

B) Active geo-replication

Answer

D) Elastic pool

Question 9

A banking application processes fund transfers. When a transfer is executed, the system must either successfully debit one account and credit the other, or if any step fails, the entire operation must be rolled back so no partial changes remain. Which ACID property directly enforces this behavior?

Accepted Answer

A) Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this banking scenario, the debit and credit operations are part of one transaction; if either step fails, the entire transaction is rolled back, leaving no partial changes. This is the core property that enforces the 'all-or-nothing' behavior described.

Answer

B) Consistency

Answer

C) Isolation

Answer

D) Durability

Question 10

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 11

A hospital uses Azure SQL Database to store patient records. The database contains tables for patient information, insurance details, and treatment plans. The system must ensure that if a transaction updates a patient's insurance and their treatment plan in two separate rows, either both updates succeed or both fail. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In Azure SQL Database, if a transaction updates both the insurance and treatment plan rows, atomicity guarantees that either both updates are committed or both are rolled back, preventing partial updates that could leave data in an inconsistent state.

Answer

Durability

Answer

Consistency

Answer

Isolation

Question 12

A company uses Azure SQL Database for an order management system. They have a table 'Orders' with columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. Queries that filter on OrderDate are slow. They create a nonclustered index on OrderDate. However, after many inserts, the index becomes fragmented and page splits occur frequently. Which action should the DBA take to maintain query performance?

Accepted Answer

Rebuild the index online. Rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.

Answer

Drop and recreate the index

Answer

Add a clustered index on OrderDate

Answer

Change the index to a clustered columnstore index

Question 13

A SaaS provider hosts databases for hundreds of clients, each with light and sporadic usage. They notice that using separate single databases with provisioned DTUs is cost-inefficient. They need to consolidate the databases while optimizing cost and ensuring that a busy client does not monopolize resources. Which Azure SQL Database option should they use?

Accepted Answer

Elastic pool. An Azure SQL Database elastic pool is designed to optimize cost for multiple databases with light and sporadic usage by sharing a fixed set of resources (eDTUs) across all databases in the pool. This prevents a busy client from monopolizing resources through per-database resource limits (min and max DTU), ensuring performance isolation while consolidating costs.

Answer

Single database with provisioned DTU

Answer

Managed Instance

Answer

SQL Server on Azure VM

Question 14

A retail company uses Azure SQL Database for its sales transaction table, which contains over 500 million rows. Queries that filter by OrderDate are slow because the database scans the entire table. The database administrator decides to implement table partitioning on the OrderDate column. What is the primary benefit of this partitioning strategy?

Accepted Answer

It improves query performance by enabling partition elimination, where only relevant partitions are scanned.. Table partitioning in Azure SQL Database divides a large table into smaller, manageable segments based on a partition key (here, OrderDate). The primary benefit is partition elimination: queries with filters on OrderDate can scan only the relevant partition(s) instead of the entire 500-million-row table, drastically reducing I/O and improving query performance.

Answer

It reduces the total storage required by compressing older partitions.

Answer

It enforces referential integrity between partitions automatically.

Answer

It eliminates the need for indexes on the partitioned column.

Question 15

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table with billions of rows. The table is distributed using hash distribution on the SaleDate column. Queries that join this fact table with a small dimension table (Product) on ProductID are slow because the join requires shuffling data across distributions. Which design change would most improve the performance of these join queries?

Accepted Answer

Replicate the Product dimension table to all distributions.. Replicating the Product dimension table to all distributions eliminates the need to shuffle data across distributions during the join. In Azure Synapse dedicated SQL pool, hash distribution distributes rows across 60 distributions based on the hash of the distribution column (SaleDate). When joining on ProductID, which is not the distribution column, data must be moved between distributions. Replicating the small dimension table ensures each distribution has a local copy, allowing the join to be performed without data movement, significantly improving performance.

Answer

Change the distribution of the fact table to round-robin.

Answer

Partition the fact table by SaleDate.

Answer

Create a nonclustered index on ProductID in the fact table.