DP-900 Practice Test 35 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 Azure Active Directory 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 7

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 8

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 9

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 10

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 11

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 12

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.

Question 13

You are deploying the above ARM template snippet for a storage account. What is the effect of setting 'isHnsEnabled' to true?

Accepted Answer

Enables Azure Data Lake Storage Gen2.. Setting 'isHnsEnabled' to true enables the Hierarchical Namespace (HNS) feature on the Azure Storage account, which is the core requirement for Azure Data Lake Storage Gen2. This allows the storage account to support a file system-like directory structure with POSIX-compliant access control lists, enabling analytics workloads to use both blob and file system semantics.

Answer

Enables Azure Blob Storage lifecycle management.

Answer

Enables geo-redundant storage (GRS).

Answer

Enables Azure Files share.

Question 14

An organization needs to run complex queries on petabytes of data stored in Azure Data Lake Storage. They want to use serverless compute to avoid managing infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it provides serverless compute that can run complex T-SQL queries directly against data stored in Azure Data Lake Storage without requiring any infrastructure management. It uses a pay-per-query billing model and can scale automatically to handle petabytes of data, making it ideal for ad-hoc analytics on large-scale data lakes.

Answer

Azure Analysis Services

Answer

Azure HDInsight

Answer

Azure SQL Database

Question 15

An organization wants to build a real-time dashboard that visualizes IoT sensor data as it arrives. Which Azure service should they use for processing the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a real-time analytics service designed to process streaming data from sources like IoT devices. It can ingest data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect patterns or anomalies, and output results to a dashboard or storage with sub-second latency, making it ideal for real-time IoT dashboards.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure Databricks