DP-203 Timed Practice Test 2 — 10 Questions

Question 1

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

10 minute time limit — choose an answer to begin.

Accepted Answer

Scale out the self-hosted IR by adding more nodes to the cluster.. Scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because Azure ExpressRoute provides dedicated bandwidth but is costly and overkill for this scenario. Option B is wrong because increasing the polling interval does not fix network timeouts; it only changes how often the activity checks for data. Option D is wrong because migrating to Azure-SSIS IR is expensive and unnecessary for this pipeline.

Answer

Implement Azure ExpressRoute to provide dedicated bandwidth.

Answer

Increase the 'Polling Interval' setting in the copy activity.

Answer

Migrate the self-hosted IR to Azure-SSIS IR.

Question 2

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 3

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

Accepted Answer

Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data. Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Answer

Use Azure Data Lake Storage Gen2 for both transactional and historical data

Answer

Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data

Answer

Use Azure SQL Database with Hyperscale tier for both transactional and historical data

Question 4

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 5

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

Accepted Answer

The dimension table is not actually replicated due to its size exceeding the replication threshold. The 'ShuffleMove' operation in a Synapse dedicated SQL pool query plan indicates that data is being moved between distributions to complete the join. If the small dimension table is supposed to be replicated but exceeds the replication threshold (default 60 GB compressed), it will not be replicated and instead remains hash-distributed. This forces a shuffle of the large fact table's data to align with the dimension table's distribution, causing significant data movement and slow performance.

Answer

The query is running with a low resource class

Answer

Result set caching is enabled

Answer

Statistics are outdated on the dimension table

Question 6

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

Accepted Answer

Microsoft Entra ID pass-through authentication.. Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Answer

Storage account key.

Answer

Shared access signature (SAS) token.

Answer

Service principal with a secret.

Question 7

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Azure Stream Analytics can output directly to Azure Synapse Analytics dedicated SQL pool using the built-in Azure Synapse Analytics output adapter, which supports high-throughput, low-latency writes. This enables streaming data to be available for queries within seconds, meeting the 5-second latency requirement without intermediate storage or batch processing steps.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds

Question 8

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to implement a security solution that prevents unauthorized access from the public internet while allowing access from specific Azure services and on-premises networks. Which feature should you configure?

Accepted Answer

Configure firewall rules and virtual network service endpoints.. Configuring firewall rules and virtual network service endpoints allows you to restrict access to your Azure Data Lake Storage Gen2 account to only traffic originating from specific virtual networks and trusted Azure services, while blocking all public internet traffic. This provides a network-level security boundary that meets the requirement of preventing unauthorized public access while permitting access from on-premises networks (via VPN/ExpressRoute) and specific Azure services.

Answer

Use private endpoints for all storage accounts.

Answer

Enable Microsoft Entra ID authentication only.

Answer

Use shared access signatures (SAS) with IP restrictions.

Question 9

You need to store log data from multiple Azure services in a single location for long-term retention and cost-effective querying. The data is append-only and rarely modified. Which storage solution should you use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is optimized for append-only, rarely modified data like logs, offering hierarchical namespace, POSIX-like ACLs, and cost-effective tiered storage (hot/cool/archive). It supports high-throughput querying via Azure Synapse, Athena, or Spark, making it ideal for long-term retention and analytics on immutable log data.

Answer

Azure SQL Database

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Question 10

Your company uses Azure Synapse Analytics dedicated SQL pool to store a fact table with 2 billion rows. You need to improve query performance for a workload that frequently aggregates sales by date and product category. Which distribution and index type should you use?

Accepted Answer

Hash-distribute on product_category and use a clustered columnstore index.. Hash-distributing on product_category ensures that rows with the same product category are co-located on the same distribution, enabling local aggregation without data movement. A clustered columnstore index provides high compression and batch-mode processing, which is ideal for large fact tables and analytical workloads that aggregate millions of rows by columns like date and product_category.

Answer

Replicate the table and use a clustered index.

Answer

Round-robin distribution and a clustered columnstore index.

Answer

Hash-distribute on date and use a clustered index.