DP-203 Practice Test 20 — 15 Questions

Question 1

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The sales table is partitioned by month and has a clustered columnstore index. Over time, the performance of queries filtering on a specific month has degraded. The data engineer suspects high rowgroup elimination. Which action should be taken to improve performance?

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

Accepted Answer

Reorganize or rebuild the columnstore index on the table.. Reorganizing or rebuilding the columnstore index compresses fragmented rowgroups and merges small rowgroups into optimal sizes (typically 102,400 rows per rowgroup). This directly addresses the degraded rowgroup elimination: when rowgroups are too small or fragmented, the engine cannot efficiently skip entire rowgroups during partition-level scans, causing more I/O and slower performance.

Answer

Change the table distribution to hash-distributed on the partition key.

Answer

Drop and recreate the affected partitions.

Answer

Update statistics on the partitioned column.

Question 2

Which TWO of the following are recommended practices for designing a data storage solution using Azure Data Lake Storage Gen2?

Accepted Answer

Enable soft delete to protect against accidental deletion. Enabling soft delete on Azure Data Lake Storage Gen2 protects against accidental deletion by retaining deleted data for a specified retention period. This allows recovery of blobs or snapshots that were deleted, overwritten, or modified, which is a critical data protection practice for enterprise storage solutions.

Answer

Use Kerberos authentication for the storage account

Answer

Store all files in a single directory for simplicity

Answer

Enable anonymous public access for ease of use

Question 3

Your organization uses Azure Synapse Analytics to run large-scale ETL jobs. A pipeline that loads data from Azure Data Lake Storage Gen2 into a dedicated SQL pool is failing with 'Out of memory' errors during the staging step. The source data is 500 GB in size, and the SQL pool is currently set to DW500c. What should you do to resolve the issue without changing the pipeline logic?

Accepted Answer

Increase the DWU of the dedicated SQL pool to a higher tier (e.g., DW1000c).. The 'Out of memory' error during the staging step indicates that the dedicated SQL pool lacks sufficient memory resources to handle the 500 GB data load within the current DWU setting. Increasing the DWU (Data Warehouse Units) from DW500c to a higher tier, such as DW1000c, scales up the memory and compute resources available to the pool, resolving the memory pressure without altering the pipeline logic or source data.

Answer

Use PolyBase instead of the Copy activity.

Answer

Switch the source to Azure Data Lake Storage Gen1.

Answer

Partition the source data into smaller files.

Question 4

You are designing a data storage solution for a retail company that needs to store semi-structured JSON data from IoT sensors. The data is ingested continuously and must support both real-time analytics and batch processing. Which Azure data store should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical file system with the scalability and low cost of Azure Blob Storage, making it ideal for storing semi-structured JSON data from IoT sensors. It supports both real-time analytics (via services like Azure Stream Analytics or Apache Spark) and batch processing (via tools like Azure Data Factory or PolyBase) without data movement, and it natively handles JSON files with schema-on-read capabilities.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 5

You are configuring Microsoft Purview to scan an Azure Data Explorer (ADX) cluster. You define the JSON shown in the exhibit. However, the scan fails with an authentication error. What is the most likely cause?

Accepted Answer

The ADX cluster does not have a managed identity enabled for Purview.. Purview requires a system-assigned managed identity (or user-assigned) to be enabled on the ADX cluster and granted permissions. Option A (firewall) could be a cause, but the error is authentication specifically. Option B (region mismatch) is not a common issue. Option D (incorrect database) would cause a different error.

Answer

The ADX cluster is behind a firewall and Purview cannot connect.

Answer

The database name in the scan configuration is incorrect.

Answer

The region of the ADX cluster does not match the Purview account region.

Question 6

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed, serverless relational database service designed for transactional workloads. It provides built-in high availability, automatic scaling, and pay-per-use billing, making it ideal for OLTP scenarios without the need to manage underlying infrastructure.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Data Lake Storage Gen2

Question 7

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

Accepted Answer

Firewall and virtual network service endpoints. Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Answer

Azure Private Endpoint

Answer

Shared access signature (SAS)

Answer

Azure Defender for Storage

Question 8

Which TWO are valid methods to load data into Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

PolyBase. PolyBase is a valid method for loading data into Azure Synapse Analytics dedicated SQL pool because it uses the T-SQL language to access and combine data from external sources like Azure Blob Storage or Azure Data Lake Store without needing to move the data first. The COPY statement is also valid as it provides a high-throughput, flexible ingestion mechanism that supports various file formats and error handling options directly via T-SQL.

Answer

BCp utility

Answer

Azure Data Factory Copy Activity

Answer

BULK INSERT

Question 9

You are monitoring an Azure Data Factory pipeline that runs hourly. The pipeline executes a stored procedure in an Azure SQL Database. Recently, you have observed that the pipeline occasionally fails with a 'Deadlock' error when the stored procedure runs. The Azure SQL Database is configured with the 'Read Committed Snapshot' isolation level enabled. You need to resolve the deadlock issue with minimal impact on performance. The stored procedure updates multiple tables in a single transaction and is critical for reporting. What should you do?

Accepted Answer

Add retry logic in the Data Factory pipeline for the stored procedure activity. Adding retry logic in the Data Factory pipeline allows the pipeline to automatically retry the stored procedure activity when a deadlock error occurs. Deadlocks are transient and often resolve on retry, minimizing impact on performance without changing the database isolation level or transaction integrity. Option A is wrong because using NOLOCK hints can lead to dirty reads and data inconsistency, which is unacceptable for a critical reporting procedure. Option B is wrong because removing the transaction would break the atomicity of the multiple table updates, potentially leaving data in an inconsistent state. Option D is wrong because disabling Read Committed Snapshot (RCSI) would likely increase blocking and contention, potentially making deadlocks worse or causing other performance issues.

Answer

Change the stored procedure to use NOLOCK hints

Answer

Remove the transaction from the stored procedure

Answer

Disable the 'Read Committed Snapshot' isolation level

Question 10

You are a data engineer at a financial services company. Your Azure Synapse Analytics dedicated SQL pool contains a fact table named 'Transactions' with 10 billion rows. The table is hash-distributed on 'AccountID' and partitioned by month. You notice that queries filtering on 'TransactionDate' (a date column) are performing slowly despite partition elimination. You also observe that the 'Transactions' table is frequently joined with a 'DimAccount' dimension table on 'AccountID'. You need to optimize query performance for the most common workload: monthly reports that aggregate transaction amounts by account for the last 12 months. Additionally, you need to ensure that the solution minimizes maintenance overhead. What should you do?

Accepted Answer

Create a clustered columnstore index on the table. Creating a clustered columnstore index on the 'Transactions' fact table provides column-level compression and significantly improves scan performance for aggregation queries, which is ideal for monthly reports aggregating transaction amounts. Option B is wrong because redistributing on TransactionDate using hash distribution would not improve join performance with DimAccount (which joins on AccountID) and may cause data skew if many rows share the same date. Option C is wrong because changing distribution to round-robin would eliminate collocation benefits for joins on AccountID, hurting query performance. Option D is wrong because table replication is designed for small dimension tables, not large fact tables like 'Transactions' with billions of rows.

Answer

Redistribute the table on TransactionDate using hash distribution

Answer

Change distribution to round-robin to evenly distribute data

Answer

Use table replication for the Transactions table

Question 11

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a security strategy that meets the following requirements: 1) All data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. 2) Access to the storage account must be restricted to specific virtual networks. 3) Users must authenticate using Microsoft Entra ID and be granted read-only access to the 'landing' container. You have configured the storage account with Azure Storage Service Encryption (SSE) using customer-managed keys. You have also configured firewall rules to allow access only from the required virtual network. However, users cannot access the 'landing' container even though they have the Storage Blob Data Reader role. What is the most likely issue?

Accepted Answer

The Key Vault firewall is blocking access from the storage account. The most likely issue is that the Key Vault firewall is blocking access from the storage account. When using Azure Storage Service Encryption (SSE) with customer-managed keys, the storage account must be able to access the Key Vault to wrap and unwrap the encryption key. Even though the users have the Storage Blob Data Reader role and the storage account firewall allows the virtual network, if the Key Vault firewall does not permit access from the storage account (e.g., via 'Allow trusted Microsoft services' or a specific network rule), the storage service cannot decrypt the data, resulting in access failures. Option D correctly identifies this. Options A and B are incorrect because the users have the correct RBAC role and the role is effective at the storage account level; the issue is not with user permissions but with the storage account's ability to reach the Key Vault. Option C is incorrect because the firewall rules are correctly configured for the virtual network.

Answer

The users have not been granted access to the Key Vault

Answer

The users do not have the Storage Blob Data Reader role assigned at the container scope

Answer

The firewall is blocking the users' IP addresses even though they are in the virtual network

Question 12

Drag and drop the steps to set up Azure Data Factory pipeline with parameterization and dynamic expressions into the correct order.

Accepted Answer

Step 1: Create the pipeline. Step 2: Define parameters. Step 3: Use parameters in activities and linked services. Step 4: Trigger pipeline with parameter values.. Create the pipeline, define parameters, use them in activities and linked services, then trigger with values.

Answer

Step 1: Define parameters. Step 2: Create the pipeline. Step 3: Use parameters in activities and linked services. Step 4: Trigger pipeline with parameter values.

Answer

Step 1: Create the pipeline. Step 2: Use parameters in activities and linked services. Step 3: Define parameters. Step 4: Trigger pipeline with parameter values.

Answer

Step 1: Trigger pipeline with parameter values. Step 2: Create the pipeline. Step 3: Define parameters. Step 4: Use parameters in activities and linked services.

Question 13

Refer to the exhibit. You have created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. When you query the external table, you get an error that the external table is not accessible. What should you check first?

Accepted Answer

Verify that the serverless SQL pool has been granted the 'Storage Blob Data Reader' role on the storage account.. The error 'external table is not accessible' in Azure Synapse serverless SQL pool typically indicates an authorization failure when the SQL pool attempts to read the underlying Parquet files in Azure Data Lake Storage Gen2. Serverless SQL pool uses its own service identity to access storage, and it must be granted the 'Storage Blob Data Reader' role on the storage account at the storage account scope to have read permissions. Without this role assignment, the SQL pool cannot authenticate to the storage, resulting in the access error.

Answer

Check that the external table's LOCATION path is relative to the container and does not start with a slash.

Answer

Ensure that the external file format is correctly referencing the Parquet format.

Answer

Confirm that the Snappy compression codec is supported by the serverless SQL pool.

Question 14

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

Accepted Answer

Enable staging using Azure Blob Storage and use PolyBase to load into Synapse. When network bandwidth is limited, staging data in Azure Blob Storage allows the copy activity to use PolyBase, which leverages Azure's internal high-speed network for the final load into Synapse. This bypasses the constrained on-premises-to-cloud link for the bulk of the data transfer, significantly improving throughput.

Answer

Increase the Data Integration Units (DIU) for the copy activity

Answer

Change the copy method to 'Bulk insert'

Answer

Set the Fault Tolerance option to skip incompatible rows

Question 15

A multinational corporation is designing a data lake on Azure Data Lake Storage Gen2. The data must be accessible from multiple regions with low latency, but only one region needs writable access. The solution must also comply with data residency requirements. Which two features or configurations should be implemented? (Choose two.)

Accepted Answer

Use read-access geo-redundant storage (RA-GRS). B is correct because read-access geo-redundant storage (RA-GRS) provides a secondary read-only endpoint in a paired region, enabling low-latency reads from multiple regions while maintaining a single writable primary region. This meets the data residency requirement because the secondary region is within the same geography, and RA-GRS ensures data is replicated asynchronously without allowing writes to the secondary.

Answer

Enable hierarchical namespace on the storage account

Answer

Configure geo-replication with manual failover

Answer

Use Azure Data Lake Storage Gen2 with locally redundant storage (LRS)