DP-203 Practice Test 37 — 15 Questions

Question 1

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

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

Accepted Answer

Use OPENROWSET with explicit file path filtering.. Using OPENROWSET with explicit file path filtering allows partition pruning by limiting the scan to the specified folders, thus improving query performance for frequently accessed partitions. Option A is wrong because workload management is a feature for dedicated SQL pool, not serverless. Option C is wrong because result-set caching is not supported in serverless SQL pool; it is a feature of dedicated SQL pool. Option D is wrong because materialized views are not supported in serverless SQL pool.

Answer

Implement workload management to prioritize queries.

Answer

Enable result-set caching on the serverless SQL pool.

Answer

Create materialized views on the Parquet files.

Question 2

You need to ensure that an Azure Data Factory pipeline retries a failed activity up to three times with a 5-minute delay between retries. How should you configure the activity?

Accepted Answer

Set retry to 3 and retryIntervalInSeconds to 300 in the activity policy. The correct configuration is to set the retry property to 3 and retryIntervalInSeconds to 300 in the activity policy. This ensures up to three retries with a 5-minute (300-second) delay between each attempt. Option A is incorrect because Azure Data Factory supports a fixed retry interval, not exponential backoff via the Retry policy. Option C is incorrect because timeout is separate from retry configuration; retry is configured at the activity level. Option D is incorrect because the correct property name is 'retry' not 'maxRetries', and the delay is specified in seconds as 'retryIntervalInSeconds'.

Answer

Configure the Retry policy on the pipeline activity as 'Exponential' with count 3

Answer

Set the activity timeout to 15 minutes and enable retry

Answer

Set maxRetries to 3 and delay to 5 minutes in the pipeline JSON

Question 3

Which TWO actions should you take to secure access to an Azure Data Lake Storage Gen2 account using Microsoft Entra ID?

Accepted Answer

Assign Azure RBAC roles such as Storage Blob Data Contributor to users or groups.. Correct options are B and E. Option B: Assigning Azure RBAC roles such as Storage Blob Data Contributor uses Microsoft Entra ID to grant coarse-grained access to the storage account. Option E: Enabling hierarchical namespace is necessary to use ACLs (access control lists) for fine-grained permissions, which can be integrated with Entra ID. Option A is wrong because SAS tokens bypass Entra ID and provide access via shared key. Option C is wrong because firewall rules restrict network access, not identity. Option D is wrong because storage account access keys bypass Entra ID entirely.

Answer

Generate a shared access signature (SAS) token with limited permissions.

Answer

Configure a storage firewall to allow only specific IP addresses.

Answer

Use storage account access keys for authentication.

Question 4

Your Azure Synapse Analytics pipeline uses PolyBase to load data from Azure Blob Storage into a dedicated SQL pool. The load is slow and suffers from high latency. Which optimization should you apply first?

Accepted Answer

Use a round-robin distribution for the staging table.. Using a round-robin distribution for staging tables minimizes data movement during PolyBase loads, reducing latency. Option A is not the first optimization because splitting files can help parallelism but may not directly address high latency. Option C is not the first optimization because increasing DWU can improve performance but incurs additional cost and is not specific to the load process. Option D is incorrect because clustered columnstore indexes are beneficial for query performance, not for import speed.

Answer

Split the source files into smaller chunks.

Answer

Increase the DWU (Data Warehouse Units) of the SQL pool.

Answer

Create clustered columnstore indexes on the staging table.

Question 5

Refer to the exhibit. You are creating an Azure Storage account using an ARM template with the above snippet. After deployment, a security auditor reviews the configuration and notes that the storage account is not using a customer-managed key for encryption. What is the most likely reason?

Accepted Answer

The 'keyVersion' is missing a specific version, so Azure Storage defaults to Microsoft-managed key.. In the ARM template snippet, the 'keyVersion' property is empty. Azure Storage requires a specific key version to use a customer-managed key; if omitted, Azure defaults to Microsoft-managed keys if the key is not found or the vault is inaccessible. Option B is incorrect because 'keySource' should be 'Microsoft.Keyvault' for customer-managed keys, not 'Microsoft.Storage'. Option C is incorrect because infrastructure encryption (double encryption) is independent of the key source. Option D is incorrect because enabling 'infrastructureEncryption' does not override the key source; it can be used with customer-managed keys.

Answer

The 'keySource' should be 'Microsoft.Storage' for customer-managed key.

Answer

The storage account requires double encryption to use customer-managed key.

Answer

The 'infrastructureEncryption' setting is enabled, which overrides customer-managed key.

Question 6

Which TWO of the following are built-in components of Azure Data Factory that can be used to perform data transformation without writing custom code?

Accepted Answer

Mapping Data Flow. Mapping Data Flow (A) is a built-in Azure Data Factory component that allows you to design data transformations visually using a graphical interface, without writing any code. It executes at scale on Azure Databricks clusters and supports operations like joins, aggregations, and pivots, making it a code-free transformation tool.

Answer

Copy Activity

Answer

Azure Function Activity

Answer

Stored Procedure Activity

Question 7

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Avoid data skew by choosing a good distribution key.. A good distribution key minimizes data skew, ensuring that data is evenly distributed across all distributions. This prevents performance bottlenecks where some distributions handle a disproportionate amount of data or queries, which is critical for parallel processing in a dedicated SQL pool.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.

Question 8

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

Accepted Answer

Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.. ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Answer

Use Azure SQL Database with clustered columnstore index on date and device ID.

Answer

Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.

Answer

Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.

Question 9

You are designing a streaming solution in Azure Synapse Analytics using the serverless SQL pool to query streaming data in real-time. The data is ingested via Azure Event Hubs and processed using Azure Stream Analytics. The output of Stream Analytics is written to Azure Data Lake Storage Gen2 in Delta Lake format. You need to ensure that the serverless SQL pool can query the latest data with minimal latency. Which approach should you use?

Accepted Answer

Create an external table in serverless SQL pool that points to the Delta Lake folder and query it directly.. Serverless SQL pool can directly query Delta Lake format files stored in Azure Data Lake Storage Gen2 by creating an external table with LOCATION pointing to the Delta folder. This allows real-time querying of the latest streaming data without any data movement or transformation, achieving minimal latency since Stream Analytics writes continuously to Delta Lake.

Answer

Ingest data directly from Event Hubs into serverless SQL pool using CETAS (CREATE EXTERNAL TABLE AS SELECT).

Answer

Use a materialized view in serverless SQL pool that refreshes every minute.

Answer

Load the streaming data into a dedicated SQL pool using a scheduled pipeline and then query it from serverless SQL pool.

Question 10

Match each Azure service tier to its description.

Accepted Answer

Hot: Optimized for data that is accessed frequently and has low latency requirements.. In Azure Blob Storage and Data Lake Storage, access tiers are optimized for different access patterns. Hot is for frequent access, Cool for infrequent access (30 days), Cold for less frequent access (90 days), and Archive for rarely accessed data (180 days). Common confusions include swapping Cool and Cold descriptions or misattributing Archive characteristics.

Answer

Cool: Optimized for rarely accessed data stored for at least 180 days.

Answer

Hot: Optimized for infrequently accessed data stored for at least 30 days.

Question 11

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with timeout errors. You need to improve reliability. What should you do?

Accepted Answer

Enable fault tolerance and use staging. Enabling fault tolerance with staging in Azure Data Factory allows the copy activity to automatically retry transient failures (such as timeout errors) by staging intermediate data in Azure Blob Storage. This mechanism uses a two-phase commit approach: data is first written to a staging location, and then committed to the final sink only after successful validation, which isolates the pipeline from intermittent source or sink failures.

Answer

Use a self-hosted integration runtime with high availability

Answer

Change the source to Azure SQL Database

Answer

Increase the degree of copy parallelism

Question 12

You are responsible for managing an Azure Data Lake Storage Gen2 account that stores parquet files for analytics. You need to implement a data retention policy that automatically deletes files older than 90 days in the 'logs' container. Additionally, you need to ensure that no data is lost due to accidental deletion; you want to be able to recover deleted files within 30 days. You also need to monitor the storage account for unusual access patterns. The solution must minimize administrative effort. What should you do?

Accepted Answer

Enable soft delete with a retention period of 30 days and configure a lifecycle management rule to delete blobs older than 90 days. Enabling soft delete with a 30-day retention period allows recovery of accidentally deleted files within that window. Configuring a lifecycle management rule to delete blobs older than 90 days automatically enforces the retention policy. This combination minimizes administrative effort. Option B is incorrect because Azure Policy cannot enforce retention or recovery at the blob level. Option C is incorrect because versioning is not equivalent to soft delete for ADLS Gen2 and Azure Policy does not manage retention. Option D is incorrect because manual deletion does not provide automatic enforcement and Azure Backup is not designed for storage-level lifecycle management.

Answer

Create an Azure Policy to enforce tag-based retention and use Azure Monitor to alert on access

Answer

Enable versioning and configure a retention policy in Azure Policy

Answer

Use Azure Backup for the storage account and manually delete old files

Question 13

You are troubleshooting a slow-running Azure Data Factory pipeline that copies data from an Azure SQL Database to ADLS Gen2. The pipeline uses a copy activity with the default settings. The source table has 10 million rows. Which optimization should you apply first?

Accepted Answer

Enable staged copy using an Azure Blob Storage staging location.. Enabling staged copy via Azure Blob Storage improves performance for large datasets by allowing the copy activity to use blob storage as an intermediate staging location, reducing load on source and destination. Option A (parallel copies) may help but default auto parallelism already handles it. Option B (mapping data flow) is heavier and not needed for simple copy. Option C (increase DIU) is a more expensive option and should not be the first optimization.

Answer

Set the 'parallel copies' property to 10.

Answer

Replace the copy activity with a mapping data flow.

Answer

Increase the data integration unit (DIU) to maximum.

Question 14

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

Accepted Answer

Hash distribution on the most joined dimension key with clustered columnstore index. Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Answer

Round-robin distribution with clustered columnstore index

Answer

Replicated distribution with clustered columnstore index

Answer

Hash distribution on a dimension key with heap index

Question 15

Your team is developing a data processing solution that uses Azure Databricks to transform streaming data from Azure Event Hubs. The transformation includes joining the stream with a static reference table stored in Azure Data Lake Storage Gen2. You need to implement the join efficiently. Which approach should you use?

Accepted Answer

Use a broadcast join with the static DataFrame loaded from Delta Lake. When joining a streaming DataFrame with a static reference table, Spark Structured Streaming can optimize the join by broadcasting the static DataFrame to all nodes, avoiding shuffles. This is efficient since the reference data is small and static. Option A is wrong because watermarks are used for stream-stream joins, not streaming-static joins. Option C is wrong because foreachBatch with a batch join adds unnecessary complexity and does not leverage built-in optimizations. Option D is wrong because converting a static table to a stream is not appropriate; the reference table is static and should be read as a batch DataFrame.

Answer

Use a watermark on both sides and perform a stream-stream join

Answer

Use foreachBatch to micro-batch the stream and perform a batch join

Answer

Use a stream-stream join by converting the static table to a stream