DP-203 Practice Test 32 — 15 Questions

Question 1

You are optimizing a Slowly Changing Dimension (SCD) Type 2 load in Azure Synapse Analytics. The dimension table has 10 million rows. Which table distribution strategy minimizes data movement during the merge operation?

Accepted Answer

Hash distribution on the business key. Option D is correct because Hash distribution on the business key (surrogate key source) ensures that matching rows are co-located, reducing data movement during MERGE. Option A is wrong because Round-robin distributes randomly, causing data movement. Option B is wrong because Replicated works for small tables, not 10 million rows. Option C is wrong because Hash on an unrelated column does not help.

Answer

Round-robin distribution

Answer

Hash distribution on the surrogate key

Answer

Replicated distribution

Question 2

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to ensure that only the 'data-scientists' group can read files in the 'processed' container, while denying access to all other users. You have already configured the storage account firewall to allow access only from your corporate network. What should you do next?

Accepted Answer

Configure access control lists (ACLs) on the 'processed' container to grant read and execute permissions to the data-scientists group and set the default ACL to deny all. Option B is correct because ACLs are the mechanism to grant granular permissions to specific users/groups in ADLS Gen2 with hierarchical namespace. Option A is wrong because RBAC roles grant access at the storage account or container level, and a single deny assignment would block everyone. Option C is wrong because private endpoints control network access, not identity-based permissions. Option D is wrong because managed identity is used for service-to-service authentication, not for granting read access to a specific group.

Answer

Create a private endpoint for the storage account and assign the data-scientists group to the private endpoint's access policy

Answer

Assign the Storage Blob Data Reader role to the data-scientists group at the storage account level and add a deny assignment for all other users

Answer

Use a managed identity for the data-scientists group and assign the Storage Blob Data Contributor role to the managed identity

Question 3

You are monitoring an Azure Data Factory pipeline that processes streaming data from Event Hubs to Azure Synapse Analytics. Which TWO Azure Monitor metrics should you set alerts on to detect data loss or processing delays?

Accepted Answer

InputEvents and OutputEvents metrics. Option B is correct because 'InputEvents' and 'OutputEvents' allow you to compare if all events are being processed. Option C is correct because 'Backlogged input events' indicates data is accumulating and not being processed quickly enough. Option A is wrong because 'Data read' and 'Data written' are for copy activities, not streaming. Option D is wrong because 'Duration' is not a streaming metric. Option E is wrong because 'Pipeline run count' is for batch pipelines.

Answer

Duration metric

Answer

Data read and data written metrics

Answer

Pipeline run count metric

Question 4

Your organization uses Azure Data Lake Storage Gen2 and needs to prevent accidental deletion of data by enabling soft delete. You also need to ensure that deleted blobs are recoverable for 30 days. What should you configure?

Accepted Answer

Enable blob soft delete and set retention period to 30 days.. Option A is correct because blob soft delete allows recovery of deleted blobs within a specified retention period. Option B is wrong because container soft delete is for entire containers, not individual blobs. Option C is wrong because Azure Backup is for VM backups, not blob recovery. Option D is wrong because snapshots are manual and not automatic.

Answer

Enable blob snapshots and set them to expire after 30 days.

Answer

Use Azure Backup to create daily backups of the storage account.

Answer

Enable container soft delete with a retention period of 30 days.

Question 5

You are designing a disaster recovery plan for an Azure Synapse Analytics dedicated SQL pool. The primary region becomes unavailable. You need to fail over to a secondary region with minimal data loss. The recovery point objective (RPO) is 1 hour. What should you configure?

Accepted Answer

Enable geo-backup on the dedicated SQL pool.. Option D is correct because the geo-backup feature automatically takes snapshots and replicates them to a paired region with a default RPO of 1 hour. Option A is wrong because the restore point is manual. Option B is wrong because active geo-replication is for Azure SQL Database, not Synapse dedicated SQL pool. Option C is wrong because automatic restore points are local, not in another region.

Answer

Configure active geo-replication to the secondary region.

Answer

Use automatic restore points and copy them to the secondary region using Azure Data Factory.

Answer

Create user-defined restore points every hour and store them in the secondary region.

Question 6

Which TWO actions should you take to secure data in transit between an Azure Synapse Analytics serverless SQL pool and a client application?

Accepted Answer

Configure the serverless SQL pool to enforce TLS 1.2 connections.. Options A and C are correct. Enforcing TLS 1.2 ensures a modern secure protocol; using VNet service endpoints keeps traffic within Azure. Option B (disable SSL) is insecure. Option D (use Azure RBAC) is for auth, not encryption. Option E (use ExpressRoute) provides private connectivity but does not encrypt data by itself; it's more for network security.

Answer

Use Azure RBAC to restrict access to the SQL pool.

Answer

Disable SSL encryption to reduce latency.

Answer

Use Azure ExpressRoute to connect to the SQL pool.

Question 7

An organization is using Azure Data Factory to ingest data from multiple on-premises SQL Server databases into Azure Synapse Analytics. They need to ensure that sensitive data is masked during ingestion before landing in the staging area. What is the best approach?

Accepted Answer

Use a Mapping Data Flow with derived column transformations to mask sensitive columns.. Option D is correct because Data Flow allows transformation steps including column masking. Option A is wrong because Azure SQL Database dynamic data masking is applied at query time, not during copy. Option B is wrong because Azure Purview is for governance, not data masking. Option C is wrong because Azure Policy is for compliance, not data masking at source.

Answer

Apply an Azure Policy that masks sensitive data in Azure Synapse Analytics.

Answer

Use Azure SQL Database dynamic data masking on the source databases.

Answer

Use Azure Purview to classify and mask sensitive data automatically.

Question 8

Refer to the exhibit. You have an Azure Data Factory with two triggers defined as shown. The DailyTrigger runs the CopyPipeline every day at midnight UTC. The BlobTrigger runs the ProcessPipeline when a blob is created in the /input/ folder. You notice that the ProcessPipeline is not executing even though blobs are being created. What is the most likely cause?

Accepted Answer

The storage account does not have an event subscription configured for blob creation.. Option B is correct because BlobEventsTrigger requires a storage event subscription, which must be configured separately. Option A is wrong because the trigger is properly defined with the event type. Option C is wrong because the container is not specified; but the path begins with /input/ which implies a container. Option D is wrong because pipeline parameters are not required.

Answer

The blobPathBeginsWith property is missing the container name.

Answer

The BlobEventsTrigger is configured to listen to the wrong event type.

Answer

The ProcessPipeline expects parameters that are not provided by the trigger.

Question 9

You are reviewing an ARM template for Azure SQL Database security alert policy. Based on the exhibit, which threats will trigger an alert?

Accepted Answer

SQL Injection Vulnerability and Data Exfiltration. Option B is correct because the disabledAlerts list includes 'Sql_Injection' and 'Access_Anomaly', so those are suppressed. The remaining enabled alerts (e.g., SQL Injection Vulnerability, Data Exfiltration, Unsafe Action) will trigger alerts. Option A is wrong because SQL Injection is disabled. Option C is wrong because Access Anomaly is disabled. Option D is wrong because the policy is enabled, so some alerts are active.

Answer

All alerts except SQL Injection and Access Anomaly

Answer

No alerts will be triggered because the policy is disabled

Answer

SQL Injection and Access Anomaly

Question 10

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline has been failing intermittently with an error indicating 'Activity timeout'. Which Azure Monitor metric should you set an alert on to proactively detect such failures?

Accepted Answer

Failed pipeline runs metric. Option A is correct because 'Failed runs' metric directly indicates pipeline failures. Option B is wrong because 'Data read' and 'Data written' measure data throughput, not failures. Option C is wrong because 'Duration' shows runtime, but a timeout is a specific failure type. Option D is wrong because 'Queue depth' is for integration runtimes, not pipeline failures.

Answer

Integration runtime queue depth metric

Answer

Pipeline duration metric

Answer

Data read and data written metrics

Question 11

You are reviewing the ARM template above. The storage account is created with hierarchical namespace enabled (isHnsEnabled: true). After deployment, you need to ensure that the 'data-engineers' group can execute but not read the contents of the root directory. What should you do?

Accepted Answer

Use the Azure portal to set ACLs on the root directory, granting execute permission to the data-engineers group without read permission. Option B is correct because ACLs are the way to set execute permissions at the root directory without granting read. Option A is wrong because RBAC roles at the storage account level grant broad permissions. Option C is wrong because the hierarchical namespace is already enabled. Option D is wrong because firewall rules control network access.

Answer

Modify the ARM template to set the 'isHnsEnabled' property to false and redeploy

Answer

Assign the Storage Blob Data Reader role to the data-engineers group at the storage account level

Answer

Configure a firewall rule to allow only the data-engineers group's IP addresses

Question 12

You are designing a security strategy for Azure Synapse Analytics. The solution must prevent users from accessing sensitive columns in a dedicated SQL pool, such as Social Security numbers, unless they have explicit permission. Which feature should you use?

Accepted Answer

Column-level security.. Option B is correct because column-level security restricts access to specific columns. Option A is wrong because row-level security filters rows, not columns. Option C is wrong because dynamic data masking obfuscates data but does not prevent access. Option D is wrong because Azure Purview is for data discovery and governance, not access control.

Answer

Azure Purview data classification.

Answer

Dynamic data masking.

Answer

Row-level security (RLS).

Question 13

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline occasionally fails due to transient errors. Which monitoring solution should you use to get alerts on failures and analyze trends over time?

Accepted Answer

Azure Monitor with Log Analytics workspace. Option B is correct because Azure Monitor with alerts and Log Analytics provides historical analysis and alerting. Option A (Data Factory Monitor) is for real-time monitoring but lacks long-term trend analysis. Option C (Azure Dashboard) is a visualization tool. Option D (Event Grid) is for event-driven notifications, not analysis.

Answer

Azure Event Grid subscription for pipeline failures

Answer

Azure Dashboard pinned with pipeline metrics

Answer

Azure Data Factory Monitor in the Azure portal

Question 14

You have an Azure Synapse Analytics dedicated SQL pool that handles both high-priority real-time queries and low-priority batch jobs. You need to ensure that high-priority queries always get the resources they need, while batch jobs do not starve. What should you configure?

Accepted Answer

Create workload groups for high-priority and low-priority queries, assigning appropriate importance and resource percentages. Option C is correct because workload management with workload groups allows you to set importance and resource allocation. Option A is wrong because result-set caching does not prioritize queries. Option B is wrong because materialized views improve performance but do not prioritize. Option D is wrong because data compression reduces storage but does not affect prioritization.

Answer

Enable result-set caching for the high-priority queries

Answer

Enable data compression on the tables used by batch jobs

Answer

Create materialized views for the batch job queries

Question 15

Your company uses Azure Databricks to process large datasets stored in Azure Data Lake Storage Gen2. You need to implement a solution that automatically rotates the storage account access keys every 90 days without interrupting running jobs. The solution should use managed identities where possible. What should you do?

Accepted Answer

Configure Azure Databricks to use a user-assigned managed identity for authentication to Azure Data Lake Storage Gen2.. Option A is correct because using a user-assigned managed identity for Databricks to access storage eliminates the need for access keys. Option B is wrong because key rotation would disrupt jobs that rely on keys. Option C is wrong because SAS tokens with expiry still require token management and rotation. Option D is wrong because storing keys in Key Vault does not prevent job interruption during rotation.

Answer

Use Azure Policy to automatically rotate keys and update Databricks cluster configurations.

Answer

Use Azure Key Vault to store the storage account keys and configure a lifecycle policy to rotate them every 90 days. Update Databricks secrets accordingly.

Answer

Generate a SAS token with a 90-day expiry and update the Databricks secret before expiry.