DP-900 Practice Test 32 — 15 Questions

Question 1

A company stores customer orders in an Azure SQL Database. They need to ensure that the database can automatically scale to handle peak loads without manual intervention. Which Azure feature should they use?

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

Accepted Answer

Enable the serverless compute tier. The serverless compute tier for Azure SQL Database automatically pauses and resumes the database based on compute usage, scaling compute resources on demand without manual intervention. This makes it ideal for handling unpredictable peak loads while minimizing costs during idle periods.

Answer

Purchase reserved capacity

Answer

Add a read replica

Answer

Configure an elastic pool

Question 2

Your company uses Azure Synapse Analytics to run analytical queries on large datasets. You need to ensure that queries against a frequently accessed fact table perform well without impacting other workloads. Which feature should you use?

Accepted Answer

Enable result set caching for the database.. Result set caching stores query results in the Synapse SQL pool's cache, so repeated queries against the fact table return cached results instantly without re-scanning data. This ensures fast performance for frequently accessed queries while isolating resource usage from other workloads, as cached results do not consume concurrency slots or I/O resources.

Answer

Create materialized views on the fact table.

Answer

Partition the fact table by a frequently filtered column.

Answer

Use workload classification to prioritize the queries.

Question 3

A company uses Azure Synapse Analytics to run large-scale data transformations. They need to optimize costs for predictable workloads that run every night. Which Azure feature should they configure?

Accepted Answer

Implement dedicated SQL pool pause and resume. Azure Synapse Analytics dedicated SQL pool supports pause and resume, which stops compute billing while preserving data in storage. For predictable nightly workloads, pausing the pool during idle hours eliminates compute costs, then resuming it before the job runs. This directly optimizes cost for scheduled, non-continuous workloads.

Answer

Enable always-on availability

Answer

Enable data compression on tables

Answer

Configure auto-scale

Question 4

A data warehouse team in Azure Synapse Analytics notices query performance degradation on a large fact table. The table is partitioned by date and has a clustered columnstore index. Which action is most likely to improve performance?

Accepted Answer

Reorganize the clustered columnstore index. Reorganizing the clustered columnstore index (option C) is the most likely action to improve performance because, over time, columnstore indexes can become fragmented due to data modifications (inserts, updates, deletes). Reorganizing the index physically recompresses the data into optimal rowgroups, removing deleted rows and merging small rowgroups, which directly improves query scan efficiency and reduces I/O.

Answer

Update statistics on the fact table

Answer

Drop and recreate the partition boundaries

Answer

Change the distribution to ROUND_ROBIN

Question 5

A data analyst needs to create a real-time dashboard in Power BI that displays sales data from an Azure SQL Database. The dashboard must update every 10 minutes without manual refresh. Which Power BI feature should they use?

Accepted Answer

DirectQuery mode. DirectQuery mode is correct because it allows Power BI to query the Azure SQL Database directly for each visual interaction, ensuring the dashboard reflects the latest data without requiring a manual refresh. Since the requirement is for updates every 10 minutes, DirectQuery can be configured to auto-refresh at that interval via the 'Automatic page refresh' setting, which sends T-SQL queries to the database on a timer. This avoids the latency and storage overhead of importing data, making it ideal for near-real-time monitoring.

Answer

Scheduled refresh with Import mode

Answer

Streaming datasets

Answer

Paginated reports

Question 6

A retail company uses Azure SQL Database to store inventory data. They notice excessive blocking and deadlocks during peak hours. Which design change would best reduce these issues?

Accepted Answer

Add appropriate indexes to reduce lock duration. Adding appropriate indexes reduces the number of rows scanned during queries, which shortens lock duration and lowers the chance of blocking and deadlocks. In Azure SQL Database, indexes help queries become more efficient by using seeks instead of scans, minimizing the time locks are held on resources.

Answer

Implement read replicas for reporting queries

Answer

Use the READ UNCOMMITTED isolation level

Answer

Scale up to a higher service objective

Question 7

A company needs to create a relational database in Azure that is compatible with existing SQL Server applications and provides built-in high availability without requiring configuration. Which service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that is built on the latest stable version of the Microsoft SQL Server engine, ensuring compatibility with existing SQL Server applications. It provides built-in high availability with a 99.99% SLA through automatic failover groups and zone-redundant configurations, requiring no manual setup or configuration from the user.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MariaDB

Answer

Azure Cosmos DB

Question 8

Which THREE are valid reasons to choose Azure SQL Managed Instance over Azure SQL Database?

Accepted Answer

Need for SQL Server Agent and CLR integration. Azure SQL Managed Instance provides near 100% compatibility with on-premises SQL Server, including instance-level features like SQL Server Agent and CLR integration. These features are not available in Azure SQL Database, which is a Platform-as-a-Service offering that abstracts away the instance scope. Therefore, if your application requires SQL Server Agent for job scheduling or CLR for custom .NET code execution, Managed Instance is the correct choice.

Answer

Simpler high-availability configuration

Answer

Desire for automated backups

Question 9

A company uses Azure Data Lake Storage Gen2 as a data lake. They need to enforce row-level security for sensitive data so that sales representatives can only see rows for their assigned region. Which approach should they use?

Accepted Answer

Load data into Azure Synapse Analytics dedicated SQL pool and implement row-level security (RLS). Row-level security (RLS) in Azure Synapse Analytics dedicated SQL pool allows you to restrict data access at the row level based on a user's identity or group membership. By loading the data into a dedicated SQL pool and defining a security policy with a predicate function that filters rows by region, you can ensure sales representatives only see rows for their assigned region. This is the correct approach because RLS is designed specifically for this purpose and integrates with Microsoft Entra ID for user authentication.

Answer

Apply sensitivity labels in Microsoft Purview

Answer

Use Azure RBAC roles on the storage account

Answer

Use Azure Data Lake Storage Gen2 access control lists (ACLs) on folders per region

Question 10

A company uses Azure SQL Database and needs to run complex analytical queries that scan large amounts of data. The queries are experiencing performance issues. Which Azure service should they use to offload the analytical workload?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is designed for large-scale analytical workloads, using a massively parallel processing (MPP) architecture that distributes data across 60 distributions and executes queries in parallel. This offloads complex analytical queries from Azure SQL Database, which uses a single-node SQL Server engine optimized for OLTP, not heavy scanning.

Answer

Azure SQL Database (Hyperscale tier)

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Question 11

A company uses Azure Synapse Analytics for their data warehouse. They notice that queries against the fact table are slow. The fact table is hash-distributed on OrderID. Most queries filter by CustomerID. What should they do to improve performance?

Accepted Answer

Change the distribution column to CustomerID. The fact table is hash-distributed on OrderID, but queries filter by CustomerID. This causes data movement across nodes for each query, as the filter column doesn't align with the distribution key. Changing the distribution column to CustomerID ensures that rows for the same CustomerID are co-located on the same compute node, eliminating unnecessary data shuffling and improving query performance.

Answer

Change to round-robin distribution

Answer

Use rowstore instead of columnstore

Answer

Replicate the fact table to all compute nodes

Question 12

A company stores terabytes of historical log data in Azure Blob Storage. The data is rarely accessed but must be retained for 10 years for compliance. The company wants to minimize storage costs. Which storage tier should you use?

Accepted Answer

Archive tier. The Archive tier is the correct choice because it is designed for data that is rarely accessed and has a flexible retrieval latency (hours), making it ideal for long-term retention of historical logs. It offers the lowest storage cost among Azure Blob Storage tiers, which directly minimizes costs for data that must be kept for 10 years but is seldom read.

Answer

Cool tier

Answer

Hot tier

Answer

Premium tier

Question 13

A company uses Azure Table Storage to store user session data. The data must be encrypted at rest. What should you do?

Accepted Answer

No action is required; Azure Storage encrypts data at rest by default.. Azure Table Storage, as part of Azure Storage, automatically encrypts all data at rest using Azure Storage Service Encryption (SSE) with 256-bit AES encryption. This encryption is enabled by default for all new and existing storage accounts, including Table Storage, and cannot be disabled. Therefore, no additional action is required to meet the encryption-at-rest requirement.

Answer

Enable Azure Storage Service Encryption (SSE).

Answer

Use Azure SQL Database Transparent Data Encryption (TDE).

Answer

Implement client-side encryption before storing data.

Question 14

You are designing a solution to store IoT device telemetry data. Each message is a small JSON payload (1-2 KB). The data is written once and read frequently for real-time dashboards. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that offers single-digit millisecond read and write latencies at any scale, making it ideal for real-time dashboards consuming IoT telemetry. Its support for JSON documents natively aligns with the small JSON payloads, and its ability to handle high-throughput writes (once) and low-latency reads (frequently) without schema management fits the workload perfectly.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 15

A company runs a critical workload in Azure Synapse Analytics. They need to ensure that if a single node fails, the data in the control node and compute nodes is not lost. Which configuration should they use?

Accepted Answer

No additional configuration is needed; Synapse provides built-in fault tolerance.. Azure Synapse Analytics (formerly SQL DW) is a distributed MPP (Massively Parallel Processing) system that automatically replicates data across multiple internal copies within the control node and compute nodes. This built-in fault tolerance ensures that if a single node fails, data is not lost because Synapse maintains at least three synchronous replicas of all data and metadata. No additional configuration is required for node-level failure protection.

Answer

Deploy a SQL Server Always On availability group.

Answer

Configure active geo-replication.

Answer

Use a Windows Server Failover Cluster.