DP-203 Practice Test 30 — 15 Questions

Question 1

You need to monitor the performance of an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a self-hosted integration runtime. Which metric is most important to monitor to ensure the self-hosted IR is not a bottleneck?

Accepted Answer

Queue depth for the self-hosted IR. Option A is correct because the queue depth indicates how many activities are waiting to be processed, directly showing if the IR is overloaded. Option B is wrong because data read/write measures throughput, not queuing. Option C is wrong because pipeline duration includes all activities. Option D is wrong because connection count does not directly indicate bottleneck.

Answer

Pipeline duration metric

Answer

Number of active connections to the IR

Answer

Data read and data written metrics for the pipeline

Question 2

You are designing a data processing solution in Azure Synapse Analytics. The solution must ensure that data at rest in a dedicated SQL pool is encrypted using customer-managed keys (CMK) stored in Azure Key Vault. The encryption should be enabled at the database level. What should you configure?

Accepted Answer

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Option C is correct because Transparent Data Encryption (TDE) with CMK in Azure Key Vault provides database-level encryption with customer-managed keys. Option A is wrong because Always Encrypted is for column-level encryption, not database-level. Option B is wrong because Azure Disk Encryption is for VMs, not SQL pools. Option D is wrong because Azure Purview is for data governance, not encryption.

Answer

Azure Purview data classification and encryption policies.

Answer

Azure Disk Encryption on the nodes hosting the dedicated SQL pool.

Answer

Always Encrypted with keys stored in Azure Key Vault.

Question 3

Which TWO Azure services can be used to monitor and analyze query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Azure Monitor with Log Analytics. Options B and D are correct. DMVs provide detailed query execution statistics, and Azure Monitor with Log Analytics can store and analyze performance data. Option A is wrong because Azure Advisor provides recommendations but not real-time monitoring. Option C is wrong because SQL Data Sync is for synchronization. Option E is wrong because Azure Policy is for governance.

Answer

SQL Data Sync

Answer

Azure Policy

Answer

Azure Advisor

Question 4

Your team is using Azure Synapse Analytics to process sensitive customer data. You need to ensure that column-level security is applied to a specific table so that only users with a certain role can view certain columns. Which feature should you use?

Accepted Answer

Column-level security (CLS). Option A is correct because column-level security in Azure Synapse restricts column access based on user's group membership. Option B is incorrect because row-level security restricts rows, not columns. Option C is incorrect because dynamic data masking obfuscates data but does not restrict access. Option D is incorrect because Azure Purview is a data governance service, not for column-level security.

Answer

Row-level security (RLS)

Answer

Azure Purview data policies

Answer

Dynamic data masking (DDM)

Question 5

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

Accepted Answer

Partition the table on the date column.. Option C is correct because a partitioned table on the date column allows partition elimination. Option A is wrong because a clustered columnstore index is good for scans but partition elimination is more effective for filtering. Option B is wrong because round-robin distribution does not help with filtering. Option D is wrong because a nonclustered index on date may help but partition elimination is more effective for large scans.

Answer

Create a nonclustered index on the date column.

Answer

Create a clustered columnstore index on the table.

Answer

Change the distribution to round-robin.

Question 6

Your team uses Azure Databricks for data processing. You need to implement a cost-control strategy that automatically terminates idle clusters after 30 minutes of inactivity, but allows users to override this policy for specific workloads that require long-running clusters. What is the most efficient approach?

Accepted Answer

Create a cluster policy that enforces auto-termination with a default of 30 minutes, but allows users to override the value for specific clusters.. Option D is correct because cluster policies in Azure Databricks allow admins to define rules (e.g., auto-termination) while allowing users to choose different values within allowed limits. Option A is wrong because manual termination is not automatic. Option B is wrong because cluster policies are not enforced by Azure Policy. Option C is wrong because auto-termination is a cluster-level setting, not a global policy.

Answer

Instruct all users to set auto-termination to 30 minutes on each cluster they create.

Answer

Configure a global auto-termination setting in the Azure Databricks workspace that terminates all clusters after 30 minutes of inactivity.

Answer

Use Azure Policy to enforce a tag that triggers a function to terminate idle clusters.

Question 7

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.. Option B is correct because partitioning can help with partition elimination, reducing data scanned. Option A is wrong because they already created statistics. Option C is wrong because increasing DWU might help but is not the best first step. Option D is wrong because result-set caching is for repeated queries, not for improving scan efficiency.

Answer

Enable result-set caching.

Answer

Increase the DWU setting for the dedicated SQL pool.

Answer

Create additional statistics on all columns.

Question 8

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. Option C is correct because 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 9

You need to implement column-level security in Azure Synapse Analytics to restrict access to salary information. Only users with the 'HRManager' role should see salary columns. Which feature should you use?

Accepted Answer

Column-level security using GRANT on columns. Option A is correct because column-level security (CLS) in Azure Synapse Analytics uses GRANT on specific columns. Option B is wrong because row-level security filters rows, not columns. Option C is wrong because dynamic data masking obfuscates data but does not restrict access. Option D is wrong because Azure Purview is a governance tool, not a security enforcement mechanism.

Answer

Row-level security using security predicates

Answer

Dynamic data masking

Answer

Azure Purview data classification

Question 10

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You notice that some queries are being queued due to resource class conflicts. What should you implement to optimize performance and reduce queuing?

Accepted Answer

Configure workload management with workload groups and classifiers. Option C is correct because workload management in Synapse allows you to assign queries to different workload groups with resource classes, preventing conflicts. Option A is wrong because scaling the pool adds cost and may not resolve the specific resource class conflict. Option B is wrong because result-set caching does not address concurrency issues. Option D is wrong because materialized views improve query performance but do not resolve queuing.

Answer

Scale the dedicated SQL pool to a higher DWU level

Answer

Create materialized views for the most common aggregations

Answer

Enable result-set caching for frequently run queries

Question 11

You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which THREE of the following actions will most likely improve query performance?

Accepted Answer

Create materialized views for frequently used aggregations. Option A is correct because partitioning large tables can reduce data scanned. Option B is correct because materialized views precompute aggregations. Option D is correct because result-set caching speeds up repeated queries. Option C is wrong because smaller distribution columns can cause data skew. Option E is wrong because converting to heap table is for staging data, not performance optimization.

Answer

Use a smaller distribution column to improve data distribution

Answer

Convert all tables to heap tables to avoid index maintenance

Question 12

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.. Option C is correct because 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 13

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.. Option B is correct because in Azure Synapse Pipelines (or Azure Data Factory), you can set the activity's 'Failure path' to go to a 'Fail' activity that terminates the pipeline and can trigger an alert via webhook or email. Option A is wrong because setting retry to 0 does not stop the pipeline; it just doesn't retry. Option C is wrong because a wildcard error path would still allow other activities to run if not explicitly failed. Option D is wrong because the default behavior is to continue if there's no error path defined.

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 14

Which THREE best practices should be followed when designing a data lake in Azure Data Lake Storage Gen2 for optimal performance?

Accepted Answer

Use Parquet file format for analytics workloads.. Options A, C, and E are correct. Using Parquet improves compression and query performance; partitioning by date allows partition pruning; using a naming convention that avoids high cardinality directories prevents throttling. Option B is wrong because hierarchical namespace is required for ADLS Gen2 and is not a best practice to disable. Option D is wrong because a deep directory structure can cause performance issues.

Answer

Disable hierarchical namespace to improve performance.

Answer

Use a deep directory structure with many subfolders.

Question 15

Your Azure Data Factory pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Blob Storage. You notice that the pipeline is running slower than expected. You need to identify the bottleneck. Which Data Factory monitoring metric should you analyze first?

Accepted Answer

Data Integration Unit (DIU) consumption. Option B is correct because Data Integration Unit (DIU) consumption indicates whether the Copy activity is resource-bound. Option A is wrong because pipeline duration is a result, not a bottleneck indicator. Option C is wrong because activity run count is not relevant to performance. Option D is wrong because source queue length is for integration runtime, not directly for Copy activity throughput.

Answer

Source queue length

Answer

Pipeline duration

Answer

Activity run count