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

Question 1

You are responsible for securing an Azure Synapse Analytics workspace that contains sensitive data. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

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

Accepted Answer

Transparent Data Encryption (TDE) with a customer-managed key.. Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault provides encryption at rest for Azure Synapse Analytics dedicated SQL pools. This allows you to manage the encryption key, including rotation and revocation, and meets compliance requirements for customer-managed keys.

Answer

Always Encrypted with column encryption keys.

Answer

Transparent Data Encryption (TDE) with a service-managed key.

Answer

Azure Storage Service Encryption with a customer-managed key.

Question 2

You are monitoring an Azure Data Factory pipeline that copies data from Azure SQL Database to Azure Synapse Analytics. The pipeline occasionally fails with transient errors such as 'Cannot connect to SQL Database' during peak hours. You need to make the pipeline more resilient without manual intervention. What should you configure?

Accepted Answer

Set the retry policy on the Copy activity with a count and interval.. The Copy activity in Azure Data Factory supports a retry policy that automatically re-executes the activity after a failure, with configurable count and interval. This handles transient network or service errors without manual intervention. Other options either address security, throughput, or scheduling but do not provide automatic retry for transient failures within a run.

Answer

Enable Azure Defender for Azure SQL Database.

Answer

Increase the data integration unit (DIU) setting for the Copy activity.

Answer

Configure a trigger with a recurrence schedule that runs more frequently.

Question 3

You are a data engineer for a retail company that stores sales data in an Azure Synapse Analytics dedicated SQL pool. You need to optimize query performance for a large fact table that is frequently joined with a much smaller dimension table. The queries often filter on a date column and aggregate sales amounts. Which technique should you implement to improve query performance?

Accepted Answer

Create a clustered columnstore index on the fact table and a replicated distribution for the dimension table.. A clustered columnstore index on the fact table provides high compression and fast aggregation, while a replicated distribution for the smaller dimension table eliminates data movement during joins. Together, these optimizations reduce I/O and network overhead, delivering significant performance gains for queries that filter on date and aggregate sales amounts. This is a best practice for star schema workloads in dedicated SQL pools.

Answer

Create a heap on the fact table and a hash-distributed dimension table on the join key.

Answer

Partition the fact table by date and use a hash distribution on the dimension table's primary key.

Answer

Create a clustered index on the date column of the fact table and a round-robin distribution for the dimension table.

Question 4

You are a data engineer at a financial services company. You have an Azure Data Lake Storage Gen2 account named finlake that stores sensitive transaction data in Parquet files. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault, and that the key is automatically rotated every 90 days. You also need to be able to revoke access to the data immediately if the key is compromised. What should you do?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault, set the key rotation policy to 90 days, and grant the storage account access to the key via a managed identity.. To encrypt data at rest in Azure Data Lake Storage Gen2 with customer-managed keys, you must configure the storage account to use a key stored in Azure Key Vault. A managed identity assigned to the storage account grants access to the key vault. You can set a rotation policy in Key Vault to automatically rotate the key every 90 days. If the key is compromised, disabling or deleting it immediately revokes access to the data.

Answer

Enable infrastructure encryption (double encryption) on the storage account and store the keys in a hardware security module (HSM) without configuring rotation.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a lifecycle management policy to rotate keys every 90 days.

Answer

Use Azure Disk Encryption with BitLocker keys stored in Azure Key Vault and apply the encryption to the storage account's underlying disks.

Question 5

You are a data engineer for a retail company that uses Azure Synapse Analytics. You have a dedicated SQL pool that contains a large fact table named SalesFact. Queries on SalesFact often filter by TransactionDate and join to a dimension table named Product. You notice that these queries perform poorly and sometimes spill to tempdb. You need to optimize the table design to improve query performance and reduce tempdb usage. What should you do?

Accepted Answer

Distribute SalesFact using hash distribution on ProductKey, and replicate Product.. In a dedicated SQL pool, hash distributing the large fact table on the common join key ProductKey collocates related rows and enables efficient joins with the Product dimension. Replicating the smaller Product dimension eliminates data movement entirely for joins. This combination minimizes shuffle operations, reduces tempdb spills, and improves query performance. It is the recommended approach for optimizing star schema queries.

Answer

Distribute both SalesFact and Product using round-robin distribution.

Answer

Distribute SalesFact using hash distribution on TransactionDate, and replicate Product.

Answer

Replicate SalesFact and distribute Product using hash distribution on ProductKey.

Question 6

You have an Azure Data Lake Storage Gen2 account that contains sensitive data. You need to ensure that data is encrypted at rest and that you control the encryption keys. You also need to be able to audit key usage. What should you implement?

Accepted Answer

Customer-managed keys (CMK) in Azure Key Vault for Azure Storage encryption.. Customer-managed keys in Azure Key Vault allow you to encrypt an Azure Storage account with your own keys, providing control over key lifecycle and enabling auditing via Key Vault logs. This satisfies the need for encryption at rest, key control, and auditability. Other options either do not apply to storage accounts or do not grant key control.

Answer

Azure Information Protection with Bring Your Own Key (BYOK).

Answer

Storage Service Encryption with Microsoft-managed keys and Azure Monitor logs.

Answer

Azure Disk Encryption with Azure Key Vault.

Question 7

You are a data engineer at a logistics company. You have an Azure Data Lake Storage Gen2 account that stores JSON logs from IoT devices. The logs are written continuously and are stored in a folder structure of /logs/{year}/{month}/{day}/{hour}/. You need to optimize the storage for cost and performance. The data is accessed frequently for the first 30 days, then occasionally for the next 60 days, and rarely after that. You need to minimize storage costs while ensuring that data remains available. What should you do?

Accepted Answer

Configure a lifecycle management policy to move blobs to the Cool tier after 30 days and to the Archive tier after 90 days.. Lifecycle management policies in Azure Storage automatically transition blobs between Hot, Cool, and Archive tiers based on rules you define. This matches the access pattern: frequent access for 30 days (Hot), occasional for next 60 days (Cool), and rare thereafter (Archive). It minimizes storage costs without manual intervention, and data remains available (Archive requires rehydration). This is the most efficient and cost-effective solution.

Answer

Enable soft delete for blobs and set the retention period to 90 days.

Answer

Create a scheduled Azure Data Factory pipeline that moves data older than 30 days to a separate storage account with the Cool tier, and older than 90 days to the Archive tier.

Answer

Use Azure Data Lake Storage Gen2 hierarchical namespace and set POSIX permissions to restrict access to older data.

Question 8

You have an Azure Data Lake Storage Gen2 account that contains a container named raw with millions of small JSON files, each under 1 MB. A daily Azure Data Factory pipeline reads these files and writes them to a curated container as Parquet files. You notice that the pipeline runs slowly and you want to optimize read performance. What should you do first?

Accepted Answer

Compact the small files into larger files before processing.. The primary performance bottleneck when reading millions of small files is the per-file overhead. Compacting them into larger files reduces the number of read operations and improves throughput. Other options like increasing parallelism or enabling hierarchical namespace do not address the root cause, and binary copy would not solve the small file issue for analytics.

Answer

Use a binary copy and then convert to Parquet in a downstream process.

Answer

Enable hierarchical namespace on the storage account.

Answer

Increase the degree of copy parallelism in the Azure Data Factory Copy activity.

Question 9

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and has recently started taking longer than expected. You need to identify the cause of the performance degradation. Which two actions should you perform? (Choose two.)

Accepted Answer

Check the on-premises SQL Server performance counters for CPU, memory, and disk I/O during the pipeline run.. Reviewing activity run logs helps identify which activity is slow and its duration, while checking on-premises SQL Server performance counters determines if the source is a bottleneck. Together, these diagnostic steps pinpoint the cause of the performance degradation. The other options are either remediation actions or broader monitoring setups that do not directly diagnose the recent slowdown.

Answer

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

Answer

Reconfigure the pipeline to use a self-hosted integration runtime with more nodes.

Answer

Enable Azure Data Factory diagnostic settings to send logs to Log Analytics and analyze with Kusto queries.

Question 10

You are a data engineer for a healthcare company. You have an Azure Data Lake Storage Gen2 account that stores sensitive patient data. You need to ensure that all access to the data is logged and that you can audit who accessed which files and when. You also need to minimize administrative effort. Which solution should you implement?

Accepted Answer

Configure diagnostic settings to send logs to Azure Monitor Logs and use Log Analytics to query access logs.. Configuring diagnostic settings to send Data Lake Storage Gen2 logs to Azure Monitor Logs and using Log Analytics to query them provides a comprehensive auditing solution with minimal administrative effort. This approach captures detailed access logs, including who accessed files and when, and integrates with Azure Monitor for easy analysis and alerting. It is the recommended method for auditing access to sensitive data in Data Lake Storage Gen2.

Answer

Enable Azure Storage analytics logging and store logs in a separate storage account.

Answer

Use Azure Advisor to monitor access and generate audit reports.

Answer

Implement Azure Private Link for the storage account and enable network security groups.

Question 11

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. You notice that the pipeline runs are taking longer than expected, and you suspect performance bottlenecks. You need to identify the cause and optimize the copy performance. What should you do first?

Accepted Answer

Enable logging in Azure Data Factory and analyze the copy activity execution details in Azure Monitor.. To optimize copy performance, you must first diagnose the bottleneck. Azure Data Factory logging provides detailed metrics for copy activities, including duration, throughput, and errors. By analyzing these logs in Azure Monitor or Log Analytics, you can determine whether the issue is due to network, source, sink, or integration runtime configuration. Once the cause is identified, you can apply targeted optimizations such as adjusting parallelism or scaling the integration runtime.

Answer

Scale up the self-hosted integration runtime by adding more nodes to the cluster.

Answer

Change the copy activity to use a staging storage account with PolyBase for faster loading into Synapse Analytics.

Answer

Increase the degree of copy parallelism in the copy activity settings to 32.

Question 12

You are a data engineer at a healthcare company. Your Azure Data Factory pipeline ingests sensitive patient records from an on-premises SQL Server into an Azure Data Lake Storage Gen2 account. The compliance team requires that all data be encrypted at rest with a customer-managed key (CMK) and that key rotation be audited. You need to configure the storage account to meet these requirements. What should you do?

Accepted Answer

Create an Azure Key Vault, generate a customer-managed key, and configure the storage account to use that key for encryption. Enable Key Vault logging and diagnostic settings to audit key rotation.. To encrypt data at rest in Azure Data Lake Storage Gen2 with a customer-managed key, you must use Azure Key Vault to store and manage the key, then associate that key with the storage account. Auditing key rotation requires enabling logging on Key Vault. This ensures both encryption control and compliance auditing.

Answer

Enable Azure Disk Encryption on the virtual machines that run the self-hosted integration runtime, and use BitLocker to encrypt the data before it is uploaded.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure Azure Monitor to log key rotation events.

Answer

Configure Azure SQL Database Transparent Data Encryption (TDE) with a customer-managed key and use Azure SQL Auditing to track key rotations.

Question 13

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a fact table with 10 billion rows. Queries frequently join this fact table to a dimension table on a column that is not the distribution column of either table. You need to reduce data movement during these joins. Which two actions should you take? (Choose two.)

Accepted Answer

Hash-distribute the fact table on the join column.. To reduce data movement during joins in a dedicated SQL pool, you can either replicate small dimension tables so they are available locally on all nodes, or hash-distribute the large fact table on the join column to colocate matching rows. Both actions minimize shuffle. Replicating the fact table is impractical due to its size, and other options do not directly address data movement.

Answer

Create a materialized view that pre-joins the fact and dimension tables.

Answer

Hash-distribute the fact table on a different column.

Answer

Use a replicated table for the fact table.

Question 14

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. You need to minimize data movement during query execution for joins between the fact table and smaller dimension tables. What should you do?

Accepted Answer

Distribute the fact table using hash distribution on the join column and replicate the dimension tables.. Hash distributing the fact table on the join column co-locates matching rows, and replicating small dimension tables places them on all compute nodes. This combination eliminates data movement during joins, which is critical for performance in dedicated SQL pools. The other options either use inappropriate distribution or do not fully minimize movement.

Answer

Distribute the fact table using hash distribution on the primary key and replicate the dimension tables.

Answer

Distribute the fact table using round-robin distribution and replicate the dimension tables.

Answer

Distribute the fact table using hash distribution on the join column and distribute the dimension tables using round-robin distribution.

Question 15

You manage an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and completes successfully. You need to be alerted when the pipeline duration exceeds 60 minutes. You want to minimize administrative effort. What should you do?

Accepted Answer

Configure a diagnostic setting to send activity logs to a Log Analytics workspace, then create a log search alert rule using a query that filters for pipeline runs with duration greater than 60 minutes.. To alert on pipeline duration, you need access to run-level logs that include the Duration property. Diagnostic settings export Azure Data Factory activity logs to Log Analytics, where you can write a query to find runs exceeding 60 minutes and attach an alert rule. This is the least-effort, native Azure solution. Metric-based alerts on pipeline metrics like ADFPipelineRun or Failed Runs do not capture duration, and embedding custom activities adds unnecessary complexity.

Answer

Add a Web activity in the pipeline that calls a Logic App to send an email if the pipeline runs longer than 60 minutes.

Answer

Create an Azure Monitor alert rule on the ADFPipelineRun metric with a threshold of 60 minutes.

Answer

Enable Azure Monitor alerts on the Failed Runs metric of the pipeline and set the threshold to 1.