DP-203 Secure, monitor, and optimize data storage and data processing Practice Test 2 — 15 Questions

Question 1

Which TWO actions can you take to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Accepted Answer

Replicate small dimension tables.. Option B is correct because replicating small dimension tables in a dedicated SQL pool places a full copy of each dimension on every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option E is correct because hash distribution on a column frequently used in joins and aggregations co-locates related rows on the same distribution, minimizing costly data movement across nodes for those operations. Option A is not the intended optimization here since scaling to a higher DWU increases cost and capacity rather than being a targeted performance-tuning action for data distribution and movement. Option C is incorrect because heap indexes on fact tables are generally used for staging or small tables; large fact tables typically benefit from clustered columnstore indexes. Option D is incorrect because round-robin distribution for large fact tables causes data movement during joins, whereas hash distribution is preferred for large fact tables.

Answer

Scale up the SQL pool to a higher DWU.

Answer

Use heap indexes for fact tables.

Answer

Use round-robin distribution for all large fact tables.

Question 2

Which TWO monitoring metrics in Azure Monitor for Azure Synapse Analytics dedicated SQL pool can help identify performance bottlenecks? (Choose two.)

Accepted Answer

Queued queries. A rising count of queued or waiting requests in a dedicated SQL pool directly signals concurrency limits or insufficient resources, indicating a performance bottleneck where queries wait for slots or DWUs. Option D (Data movement (shuffle) metrics) is correct because dedicated SQL pools are MPP systems, and heavy shuffle/data movement during query execution often reveals poorly distributed tables or skewed joins that degrade query performance. Option A (CPU percentage) is a general node-level metric but is not one of the dedicated SQL pool-specific bottleneck indicators emphasized for this scenario. Option C (Storage used) reflects capacity consumption rather than query performance bottlenecks. Option E (DWU used) shows service-level utilization/scale, but it is not the specific diagnostic metric for identifying query-level bottlenecks like queuing and data movement.

Answer

CPU percentage

Answer

Storage used

Answer

DWU used

Question 3

You are monitoring an Azure Data Factory pipeline that runs daily. You notice that some runs are failing due to transient network errors. You want to automatically retry the failed activities with a 5-minute delay, up to 3 times. How should you configure this?

Accepted Answer

On each activity, set 'Retry' to 3 and 'Retry interval' to 00:05:00.. Azure Data Factory activities have a 'Retry' property that can be set to 3, and 'Retry interval' to 00:05:00. Option A is too low (1 retry). Option B is wrong because retry is per activity, not at pipeline level. Option D is wrong because the default retry is 0.

Answer

Set the pipeline's 'Concurrency' to 3 and 'Retry' to 1.

Answer

Leave the default settings as they are because Azure Data Factory automatically retries failed activities 3 times.

Answer

Configure a 'Retry' policy on the pipeline itself, setting maximum retries to 3 and retry interval to 5 minutes.

Question 4

You are a data engineer for a company that uses Azure Synapse Analytics dedicated SQL pool. You need to implement security best practices to protect sensitive data. Which TWO actions should you take? (Choose two.)

Accepted Answer

Enable Transparent Data Encryption (TDE) on the dedicated SQL pool.. Option C is correct because Transparent Data Encryption (TDE) is a built-in Azure Synapse dedicated SQL pool security feature that encrypts data at rest, protecting the underlying storage files and backups from unauthorized access; it is enabled by default but should be explicitly verified and managed as a best practice. Option E is correct because column-level security (CLS) in dedicated SQL pool allows you to control access to specific columns containing sensitive data based on the user's identity or role, using GRANT and DENY statements, which directly restricts who can read sensitive columns. Option A is not a data protection best practice for sensitive data inside the pool; firewall rules control network access, not data-level security, and are already part of the platform's network perimeter. Option B is unnecessary because Azure Storage encryption is enabled by default for all Azure Storage accounts and TDE already encrypts the dedicated SQL pool's data at rest. Option D is incorrect because Dynamic Data Masking does not obfuscate data from all users; it only masks data for non-privileged users, while users with UNMASK permission see the actual data, so it does not fully protect sensitive data from all users.

Answer

Configure a firewall rule to allow only specific IP addresses.

Answer

Enable Azure Storage encryption for the underlying storage.

Answer

Use Dynamic Data Masking to obfuscate sensitive data from all users.

Question 5

Your Azure Synapse Analytics dedicated SQL pool query performance is degrading over time. You suspect that the statistics might be outdated. What is the most efficient way to update statistics for all tables in the pool?

Accepted Answer

Execute `sp_updatestats` in the user database.. `sp_updatestats` updates statistics for all tables in the current user database efficiently. Option A is wrong: while automatic statistics update is enabled by default, it may not immediately address performance degradation if the statistics are outdated; waiting is not proactive. Option B is wrong: `sp_updatestats` in the master database updates only system tables, not user tables. Option D is wrong: running `UPDATE STATISTICS` on each table individually is less efficient than using `sp_updatestats` for all tables.

Answer

Enable automatic statistics update and wait for the system to update them.

Answer

Execute `sp_updatestats` in the master database.

Answer

Run `UPDATE STATISTICS` on each table individually.

Question 6

You are using Azure Stream Analytics to process real-time data from an event hub and output to Azure Synapse Analytics. You need to ensure exactly-once delivery semantics to the output. What should you configure?

Accepted Answer

Set the output to 'Exactly Once' delivery mode.. Azure Stream Analytics supports exactly-once delivery to Azure Synapse Analytics by configuring the output to 'Exactly Once' delivery mode. Option A is incorrect because the input consumption mode does not control output delivery guarantees. Option B is incorrect because event ordering and late arrival policies handle timing, not delivery semantics. Option C is incorrect because checkpointing is for managing internal query state, not for ensuring exactly-once output.

Answer

Set the input to 'Exactly Once' consumption mode.

Answer

Configure event ordering and late arrival policies.

Answer

Enable checkpointing in the query.

Question 7

You are designing a data processing solution using Azure Databricks. You need to optimize costs while maintaining performance. Which TWO strategies should you implement? (Choose two.)

Accepted Answer

Use spot instances for non-critical, fault-tolerant workloads.. Options A and B are correct. A: Using spot instances reduces costs for fault-tolerant workloads. B: Autoscaling adjusts resources based on workload. Option C is wrong because using larger VMs may lead to underutilization and higher costs. Option D is wrong because all-purpose clusters are more expensive and not recommended for production jobs. Option E is wrong because Azure Databricks does not offer a serverless pricing tier for compute; you pay for running VMs.

Answer

Always choose the largest VM sizes to minimize cluster count.

Answer

Use all-purpose clusters for all workloads to avoid startup delays.

Answer

Use the serverless pricing tier to avoid paying for idle resources.

Question 8

You are designing a data pipeline using Azure Synapse Pipelines. The pipeline ingests data from multiple sources, performs transformations using a notebook, and loads the results into a dedicated SQL pool. You need to ensure that if the notebook fails, the entire pipeline stops and sends an alert. What is the most efficient way to configure this?

Accepted Answer

Add a 'Fail' activity after the notebook activity and connect the notebook's failure output to it. Configure the Fail activity to send an alert.. In Azure Synapse Pipelines, to ensure that a notebook failure stops the entire pipeline and sends an alert, you can add a Fail activity and connect the notebook's failure output to it. The Fail activity will cause the pipeline to fail and can be configured to send an alert (e.g., via a webhook or Azure Monitor). This is the most efficient way because it explicitly handles the failure and stops the pipeline.

Answer

Set the notebook activity's error path to a webhook activity that sends an alert, and then set a wildcard error path for the pipeline.

Answer

Set the notebook activity's retry count to 0, and configure an alert on the pipeline run failure.

Answer

No configuration needed; by default, a failed activity stops the entire pipeline.

Question 9

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are slow. They have already created statistics on all columns used in WHERE clauses and JOIN predicates. What should they do next to improve query performance?

Accepted Answer

Partition the table on a frequently filtered column.. Partitioning on a frequently filtered column enables partition elimination, reducing the amount of data scanned and improving query performance. Option A (result-set caching) caches query results for repeated queries, but this question is about slow queries on a large fact table, not repeated queries. Option B (increasing DWU) might improve performance by providing more resources, but it is a more costly approach and not the best first step; partitioning is a better design optimization. Option C (creating additional statistics on all columns) is unnecessary because they already have statistics on columns used in WHERE and JOIN; statistics on all columns would waste resources and not help.

Answer

Enable result-set caching.

Answer

Increase the DWU setting for the dedicated SQL pool.

Answer

Create additional statistics on all columns.

Question 10

A company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. They notice that queries are slow and want to improve performance by reducing the amount of data read. What is the most effective strategy?

Accepted Answer

Partition the data by a frequently filtered column and use file elimination in queries.. Partitioning the data in the lake and using partition elimination reduces data read. Option B is wrong because increasing the number of compute nodes is not possible in serverless; it's auto-scaling. Option C is wrong because OPENROWSET with CSV reads all files; it does not reduce data read. Option D is wrong because CETAS is for creating external tables, not for improving query performance directly.

Answer

Increase the number of compute nodes in the serverless pool.

Answer

Use OPENROWSET with CSV format instead of Parquet.

Answer

Create external tables using CETAS and query them.

Question 11

Which TWO security features should be implemented to protect data in Azure Data Lake Storage Gen2?

Accepted Answer

Role-based access control (RBAC). Option A (Role-based access control (RBAC)) is correct because Azure Data Lake Storage Gen2 supports Azure RBAC roles such as Storage Blob Data Owner, Storage Blob Data Contributor, and Storage Blob Data Reader, which authorize users and service principals at the management and data-plane level to control who can read, write, or manage data. Option D (Firewall and virtual network rules) is correct because ADLS Gen2 storage accounts support network-level restrictions via the storage account firewall, allowing access only from selected public IP ranges, specific virtual networks/subnets through service endpoints, or private endpoints, thereby blocking unauthorized network paths. Option B (Azure Policy) is not the primary data-protection feature here; it governs and audits resource compliance rather than directly securing access to data in the lake. Option C (Microsoft Entra ID integration) is a prerequisite/identity mechanism that works together with RBAC, but the question asks for the security features to implement, and RBAC already encompasses the authorization layer. Option E (Azure Monitor alerts) provides monitoring and notification of events or metrics, not access control or network protection of the data.

Answer

Azure Policy

Answer

Azure Active Directory (Microsoft Entra ID) integration

Answer

Azure Monitor alerts

Question 12

Which THREE actions can improve the performance of a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Partition large fact tables on a date column.. Option B is correct because partitioning large fact tables on a date column enables partition elimination, so queries that filter on the date range scan only the relevant partitions instead of the entire table, reducing I/O and improving performance. Option D is correct because replicated tables place a full copy of small dimension tables on every compute node, eliminating data movement (shuffle) during joins with large distributed fact tables, which is a key optimization in dedicated SQL pools. Option E is correct because result-set caching in Azure Synapse Analytics stores the results of repeated identical queries in the control node, so subsequent executions return cached results without re-running the full query against the distributed data. Option A is not correct because dedicated SQL pools are optimized for clustered columnstore indexes, which provide high compression and fast analytical scans; switching to rowstore indexes generally degrades performance for large analytical workloads. Option C is not correct because round-robin distribution is best for staging or temporary tables, not for all tables; fact tables typically benefit from hash distribution on a join key, and dimension tables from replication, so applying round-robin universally would cause unnecessary data movement.

Answer

Use rowstore indexes instead of columnstore indexes.

Answer

Use round-robin distribution for all tables.

Question 13

Refer to the exhibit. You have an Azure Data Lake Storage Gen2 account with hierarchical namespace enabled. The exhibit shows an Azure CLI command output that lists access permissions. User user@contoso.com is a member of group-analysts@contoso.com. What level of access does user@contoso.com have to the /data/analytics/ directory?

Accepted Answer

Read and write access. RBAC permissions are additive. The user has Storage Blob Data Contributor at the root scope, which grants read/write access to all directories. The reader role at the directory scope does not override the broader contributor role. Option A is wrong because the user has write access via the root scope. Option B is wrong because the user has read access from the root scope already. Option D is wrong because the user has explicit permissions.

Answer

Access denied because of the directory-level reader role

Answer

Read-only access

Answer

No access

Question 14

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.. The most likely cause is that the storage account does not have an event subscription configured for blob creation. The BlobEventsTrigger in Azure Data Factory relies on an event subscription from the storage account to the trigger. Without this subscription, the trigger will not fire even when blobs are created in the specified path. Option D correctly identifies this issue. Option A is incorrect because the blobPathBeginsWith property includes the container name prefix; Option B is incorrect because the trigger is configured to listen to blob creation events; Option C is incorrect because the ProcessPipeline does not require parameters from the trigger.

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 15

You have an Azure Synapse Analytics dedicated SQL pool that stores sensitive financial data. You need to ensure that all queries accessing the data are audited and that any use of unapproved client tools is blocked. What should you implement?

Accepted Answer

Enable auditing and set an IP firewall rule that allows only approved IP ranges.. Azure Synapse Analytics supports IP firewall rules and auditing. By enabling auditing and creating a firewall rule that only allows approved client IP ranges, you can both audit queries and block unapproved tools. Option A is wrong because Microsoft Defender for Cloud provides threat detection but not granular control over client tools. Option B is wrong because Azure Private Link only provides network-level isolation, not auditing or blocking. Option D is wrong because Azure role-based access control (RBAC) alone does not enforce client tool restrictions.

Answer

Enable Microsoft Defender for Cloud on the server.

Answer

Configure Azure Private Link for the dedicated SQL pool.

Answer

Assign Azure RBAC roles to users for the SQL pool.