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

Question 1

You are designing a data processing solution using Azure Databricks with Delta Live Tables (DLT). The solution must handle late-arriving data and ensure that updates to the delta table are processed exactly once. Which DLT pipeline setting should you configure?

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

Accepted Answer

Configure the DLT pipeline with 'Delta Live Tables' and use 'APPLY CHANGES INTO' with a watermark.. The correct setting is to configure the DLT pipeline with 'APPLY CHANGES INTO' and use a watermark (D). APPLY CHANGES INTO is the DLT SQL API for processing change data capture (CDC) and handling out-of-order or late-arriving data via a watermark, while ensuring exactly-once processing semantics through Delta Lake's transactional guarantees. This combination directly addresses late-arriving data and exactly-once updates.

Answer

Set the pipeline mode to 'Trigger once'.

Answer

Set the pipeline mode to 'Continuous' and enable 'Exactly once' delivery.

Answer

Use Auto Loader with 'file arrival' mode.

Question 2

You are designing an Azure Data Factory pipeline to ingest data from an on-premises SQL Server into Azure Synapse Analytics. The data must be encrypted in transit. Which integration runtime type should you use and what additional configuration is required?

Accepted Answer

Use Self-hosted Integration Runtime with a certificate for HTTPS. A self-hosted integration runtime is required to connect to on-premises networks. To encrypt data in transit, a certificate must be configured for HTTPS encryption. Option A is wrong because Azure Integration Runtime cannot directly access on-premises data sources. Option B is incorrect because Azure VPN Gateway provides network-level connectivity but does not handle application-layer encryption; the self-hosted IR still needs a certificate. Option D is wrong because using a public endpoint does not ensure encryption in transit without additional configuration, and Azure IR still cannot access on-premises directly.

Answer

Use Azure Integration Runtime with ExpressRoute

Answer

Use Self-hosted Integration Runtime with Azure VPN Gateway

Answer

Use Azure Integration Runtime with a public endpoint

Question 3

You need to monitor the performance of Azure Stream Analytics jobs. Which Azure Monitor metric can be used to detect if the job is falling behind in processing input data?

Accepted Answer

InputEventsBacklog. InputEventsBacklog measures the number of input events waiting to be processed by the Stream Analytics job. A growing backlog indicates the job cannot keep up with incoming data, i.e., it is falling behind. This is the correct Azure Monitor metric for detecting processing lag relative to input volume.

Answer

WatermarkDelay

Answer

OutputEvents

Answer

RuntimeErrors

Question 4

Your organization uses Azure Purview for data governance. You need to ensure that only authorized users can register data sources and create classification rules, while other data consumers can only search and browse the data catalog. What should you configure?

Accepted Answer

Assign the Data Curator role to curators and Data Reader role to consumers. Azure Purview uses roles: Data Curator can register sources and manage classifications; Data Reader can only search and browse. Option A is wrong because Data Source Administrator can only manage source registrations. Option C is wrong because Collection Admin manages collections. Option D is wrong because there is no 'Catalog Admin' role; Purview has built-in roles.

Answer

Assign the Catalog Admin role to curators and Data Reader role to consumers

Answer

Assign the Collection Admin role to curators and Data Reader role to consumers

Answer

Assign the Data Source Administrator role to curators and Data Reader role to consumers

Question 5

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The pool has a large fact table distributed by hash on CustomerID. Most queries filter on OrderDate. You need to improve query performance for date-range queries without changing the distribution. What should you do?

Accepted Answer

Partition the table on OrderDate. Partitioning the table on OrderDate enables partition elimination, which significantly reduces the amount of data scanned for date-range queries, improving performance without altering the distribution. Option A is incorrect because changing to round-robin distribution would increase data movement during joins and aggregations. Option B is incorrect because materialized views are pre-aggregated and not optimized for row-level date filtering; they involve storage and maintenance overhead. Option D is incorrect because non-clustered indexes can be used in dedicated SQL pool but are not as effective as partition elimination for large fact tables scanned sequentially, and they require additional storage and maintenance.

Answer

Change the distribution to round-robin on OrderDate

Answer

Create a materialized view on OrderDate

Answer

Create a non-clustered index on OrderDate

Question 6

Which TWO actions should you take to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Enable Azure Storage Service Encryption (SSE) for data at rest. Option A is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting the data on the underlying storage media. Option E is correct because Azure RBAC enforces least-privilege access to storage accounts, ensuring only authorized identities can read or modify the data at rest, which is a core data-at-rest protection control. Option B is incorrect because diagnostic settings audit access activity (data in use/transit monitoring), not data at rest. Option C is incorrect because firewall rules restrict network access, which protects data in transit rather than at rest. Option D is incorrect because soft delete protects against accidental deletion, not unauthorized access to data at rest.

Answer

Enable diagnostic settings to audit access

Answer

Configure firewall rules to restrict network access

Answer

Enable soft delete for blobs

Question 7

Refer to the exhibit. You run the Kusto query in Azure Monitor Logs to analyze Data Factory pipeline runs. What is the purpose of this query?

Accepted Answer

Identify pipelines with the most failed activity runs per hour. The query filters pipeline runs with a status of 'Failed', groups them by pipeline name and hourly time bins, then counts the number of failed activity runs per hour. The results are sorted descending by count, making it easy to identify which pipelines have the most failed activity runs per hour. Therefore, option B is correct. Option A is incorrect because the query only includes failed runs, not all runs. Option C is incorrect because the query counts runs, not calculates duration. Option D is incorrect because the query filters on pipeline runs, not trigger runs.

Answer

List all pipeline runs regardless of status

Answer

Calculate the average duration of failed pipeline runs

Answer

Show the number of failed trigger runs per hour

Question 8

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The pool experiences high concurrency and frequent data skew. Which indexing strategy should you recommend to improve query performance for large fact tables?

Accepted Answer

Use round-robin distributed tables with clustered columnstore indexes.. Round-robin distribution spreads data evenly across distributions, minimizing data skew in high-concurrency scenarios. Clustered columnstore indexes provide high compression and query performance for large fact tables. Option A is incorrect because hash-distributed tables can still suffer from skew if the distribution column is poorly chosen. Option C is incorrect because replicated tables are best for small dimension tables, not large fact tables. Option D is incorrect because heap tables lack indexing and are typically used for staging, not optimized for querying large fact tables.

Answer

Use hash-distributed tables with clustered columnstore indexes.

Answer

Use replicated tables with clustered indexes.

Answer

Use heap tables with non-clustered indexes.

Question 9

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.. When using PolyBase to load from Blob Storage into a dedicated SQL pool, the first optimization is to use a round-robin distribution for the staging table. PolyBase loads into a staging table first; a round-robin distribution balances rows evenly across distributions and avoids data skew during the load, improving throughput and reducing latency. This is the recommended first step before other tuning.

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 10

Which THREE features should you use to optimize query performance in Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

Materialized views.. Materialized views (C) are correct because in a dedicated SQL pool they persist precomputed result sets on disk, letting the optimizer rewrite queries against the base tables to read the smaller materialized result, which dramatically reduces I/O and compute for repetitive aggregate or join-heavy queries. Workload management with workload groups and importance (D) is correct because it lets you classify requests into workload groups and assign importance so critical queries get more resources and concurrency, preventing resource contention from degrading query performance. Result-set caching (E) is correct because it caches the results of repeated identical queries in the dedicated SQL pool, so subsequent executions return results without re-running the full query, cutting latency and resource usage. T-SQL views (A) are not correct because a standard view is only a stored query definition that expands at runtime and provides no persisted data or performance benefit by itself. Geo-redundant storage (B) is not correct because GRS is a storage durability/replication option for availability and disaster recovery, not a query-performance optimization feature.

Answer

T-SQL views.

Answer

Geo-redundant storage (GRS).

Question 11

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You suspect that the workload is generating excessive data movement due to suboptimal distribution. Which dynamic management view (DMV) should you query to identify queries that are causing significant data movement?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests shows query steps including data movement operations. Option A is wrong because sys.dm_pdw_node_status shows node health. Option C is wrong because sys.dm_pdw_errors shows error details. Option D is wrong because sys.dm_pdw_waits shows wait states.

Answer

sys.dm_pdw_node_status

Answer

sys.dm_pdw_errors

Answer

sys.dm_pdw_waits

Question 12

Refer to the exhibit. You have created the custom RBAC role shown and assigned it to a security group. Members of the group report that they can read blobs in the storage account but cannot list the contents of the container. What is the most likely reason for this issue?

Accepted Answer

The role does not include the permission to list blobs in a container.. The custom RBAC role includes the 'read' data action for blobs, which allows reading blob data, but it does not include the 'list' action for blobs. Without the 'list' permission, users cannot list the blobs within a container. In Azure RBAC for storage, the 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read' action permits reading blob content and properties, but listing blobs requires the 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/list' action (or equivalent at the container level). Therefore, the role lacks the necessary permission to list blobs.

Option A is incorrect because custom RBAC roles are fully supported for Azure Data Lake Storage Gen2. Option B is incorrect because the scope of the role at the storage account level does not prevent listing blobs; the issue is missing data actions. Option D is incorrect because the role already includes the 'read' data action for blobs; the missing action is 'list'.

Answer

Custom roles are not supported for Azure Data Lake Storage Gen2.

Answer

The role is scoped to the storage account but not to the container.

Answer

The role lacks the 'read' data action for blobs.

Question 13

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully but you notice that the data transfer is taking longer than expected. You need to improve the transfer performance without increasing the cost significantly. Which configuration change should you make?

Accepted Answer

Enable staged copy with an Azure Blob Storage staging location.. Enabling staged copy with an Azure Blob Storage staging location is the correct choice because it uses Azure Data Factory's staged copy feature, which leverages the high-throughput capabilities of Blob Storage as an intermediary. For on-premises sources, staged copy can improve performance by first copying data to Blob Storage using a highly parallelized process, then loading into the destination. This approach often yields better throughput without significantly increasing cost, as it optimizes data transfer.

Answer

Use PolyBase to load the data into Blob Storage.

Answer

Increase the number of concurrent connections on the SQL Server.

Answer

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

Question 14

You are optimizing a batch processing job in Azure Databricks that reads data from Azure Data Lake Storage Gen2 and writes aggregated results back. The job currently runs slowly due to high shuffle writes. You plan to use Delta Lake and optimize the table layout. Which two actions should you take to reduce shuffle writes? (Select two.)

Accepted Answer

Partition the Delta table by the most frequently used filter column.. The correct actions are B and E. Partitioning the Delta table by the most frequently used filter column (B) enables partition pruning so that queries scan and shuffle far less data, and running OPTIMIZE with Z-ORDERING on join keys (E) co-locates related data in the same files, which lets Databricks skip large amounts of data and reduces shuffle volume during joins. Option A is incorrect because auto-optimize targets the small-file problem, not shuffle writes, and does not reorganize data by join or filter keys. Option C is wrong because broadcast hash joins only help when one side is small enough to fit in memory and do not address shuffle-heavy aggregations. Option D is wrong because increasing shuffle partitions to 400 typically increases the number of shuffle tasks and small files rather than reducing shuffle writes.

Answer

Enable Delta Lake auto-optimize to coalesce small files.

Answer

Use a broadcast hash join hint for all joins.

Answer

Increase the number of shuffle partitions to 400.

Question 15

Your team uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are running slower than expected. You need to improve query performance by reducing the amount of data scanned. Which two features should you implement? (Select two.)

Accepted Answer

Partition the data in the data lake and use partition elimination in queries.. The correct options are A and E. Partitioning the data in Data Lake Storage Gen2 and using partition elimination lets the serverless SQL pool skip entire partitions that do not match the query predicate, directly reducing the bytes scanned, while file pruning by specifying explicit file paths in OPENROWSET restricts the query to only the relevant files instead of scanning the whole folder. Both techniques target the core problem of minimizing data read from storage, which is the main lever for serverless SQL pool performance. Option B is not applicable because result-set caching is a dedicated SQL pool feature, not a serverless SQL pool capability. Option C does not apply because auto-optimize (Auto Compaction and Optimize Write) is a Databricks Delta Lake feature, not an Azure Synapse serverless SQL pool feature. Option D is incorrect because materialized views are not supported in serverless SQL pools; they are supported in dedicated SQL pools.

Answer

Enable result-set caching for the serverless SQL pool.

Answer

Enable auto-optimize on the Delta Lake tables.

Answer

Create materialized views on the serverless SQL pool.