DP-203 Practice Test 29 — 15 Questions

Question 1

You manage an Azure Data Lake Storage Gen2 account containing a large volume of JSON files. Users report that direct read operations from the data lake are slow, and you observe high egress costs. You need to optimize read performance and reduce cost for analytical queries that frequently filter on a specific timestamp column and select a subset of columns. What should you do?

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

Accepted Answer

Convert the files to Parquet format and partition the data by the timestamp column.. Converting JSON to Parquet reduces storage size and enables columnar reads, while partitioning by the frequently filtered timestamp column allows the query engine to skip irrelevant data. Together, these changes minimize the data scanned and transferred, improving read performance and lowering egress costs. Other options either do not address the root cause or introduce unnecessary expense.

Answer

Enable Azure Storage analytics logging and review the logs to identify slow queries.

Answer

Increase the number of partitions in the Azure Synapse Analytics dedicated SQL pool that reads the data.

Answer

Move the data to a premium block blob storage account with a higher throughput tier.

Question 2

You are monitoring an Azure Synapse Analytics dedicated SQL pool. You need to identify queries that are currently running and consuming the most resources. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests. sys.dm_pdw_exec_requests is the correct DMV for monitoring currently running queries and their resource consumption in a dedicated SQL pool. It includes columns like cpu_time, total_elapsed_time, and memory_usage, allowing you to identify resource-heavy queries. The other DMVs either provide node-level detail, session information, or wait statistics, which are less direct for this specific monitoring task.

Answer

sys.dm_pdw_exec_sessions

Answer

sys.dm_pdw_nodes_exec_requests

Answer

sys.dm_pdw_waits

Question 3

You manage an Azure Data Lake Storage Gen2 account containing a large volume of JSON files. Users report that direct read operations from the data lake are slow, and you observe high egress costs. You need to optimize read performance and reduce cost for analytical queries that frequently filter on a specific timestamp column and select a subset of columns. What should you do?

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

Accepted Answer

Azure Monitor diagnostic settings for the storage account. Azure Monitor diagnostic settings capture resource logs for Data Lake Storage Gen2, including operations like GetBlob, PutBlob, and DeleteBlob, along with the caller's identity. These logs can be sent to Log Analytics for querying and retention, enabling comprehensive auditing. This is the correct feature to enable for detailed access logging in a compliance scenario.

Answer

Storage account firewall and virtual network rules

Answer

Azure Defender for Storage

Answer

Azure Storage analytics logging

Question 4

You are configuring security for an Azure Data Lake Storage Gen2 account. You need to ensure that users can only access files and folders for which they have explicit permissions, and that permissions are enforced at the file and folder level. What should you enable?

Accepted Answer

Azure RBAC and access control lists (ACLs). Azure Data Lake Storage Gen2 uses a combination of Azure RBAC and POSIX-like ACLs to secure data. RBAC controls access at the management and container level, while ACLs provide fine-grained permissions at the file and folder level. To ensure users can only access files and folders for which they have explicit permissions, both must be configured. RBAC alone is too coarse, ACLs alone lack the authentication context, and SAS tokens are not identity-based for per-user permissions.

Answer

Access control lists (ACLs) only

Answer

Shared access signatures (SAS) only

Answer

Azure role-based access control (Azure RBAC) only

Question 5

You are a data engineer at a healthcare company. You have an Azure Data Lake Storage Gen2 account named sthealthcare with a container named records. The container holds sensitive patient data in Parquet files. You need to ensure that only users who are members of the Azure AD group named ClinicalResearchers can read the data, while users in the group DataEngineers can read and write. Access must be managed at the directory level and must not affect other containers in the storage account. What should you do?

Accepted Answer

Enable hierarchical namespace on the storage account, then set POSIX-like ACLs on the records directory, granting read to ClinicalResearchers and read-write to DataEngineers.. The requirement is to manage access at the directory level within a container without affecting other containers. Azure Data Lake Storage Gen2 ACLs, enabled by hierarchical namespace, allow setting permissions on directories and files. By applying ACLs on the records directory, you grant the appropriate groups read or read-write access only to that directory and its contents. This satisfies least privilege and directory-level control.

Answer

Assign the Storage Blob Data Reader role to ClinicalResearchers and Storage Blob Data Contributor role to DataEngineers at the storage account scope.

Answer

Configure Azure RBAC roles on the records container: assign Storage Blob Data Reader to ClinicalResearchers and Storage Blob Data Contributor to DataEngineers.

Answer

Create a shared access signature (SAS) token for each user in ClinicalResearchers and DataEngineers with appropriate permissions, and distribute the tokens.

Question 6

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 7

You are optimizing an Azure Synapse Analytics dedicated SQL pool that stores a large fact table. Queries frequently join the fact table to a small dimension table on a non-distributed column, causing data movement. You need to reduce data movement and improve query performance. (Choose two.)

Accepted Answer

Replicate the small dimension table so a full copy exists on every compute node.. To eliminate data movement for a join between a large fact table and a small dimension, align the fact table's distribution with the join key and replicate the dimension. Hash-distributing the fact table on the join column co-locates matching rows, while replication places the small dimension on every node. Together they enable local joins without shuffling large datasets across the fabric.

Answer

Change the distribution of the large fact table to ROUND_ROBIN.

Answer

Partition the large fact table by date and rebuild statistics.

Answer

Create a clustered columnstore index on the small dimension table.

Question 8

You are a data engineer at a healthcare company. Your Azure Synapse Analytics workspace contains a dedicated SQL pool that holds patient records. A new compliance rule requires that all queries against the dedicated SQL pool be audited, and that any attempt to access data from an unauthorized IP address be logged. You need to configure auditing for the dedicated SQL pool. What should you do?

Accepted Answer

Enable auditing on the dedicated SQL pool and configure the audit log destination to Azure Storage, Log Analytics, or Event Hubs.. Auditing for an Azure Synapse Analytics dedicated SQL pool must be enabled directly on the pool, not at the workspace or Azure SQL Database level. The audit logs can be sent to Azure Storage, Log Analytics, or Event Hubs, providing a full record of queries and unauthorized access attempts. This configuration satisfies the compliance requirement to audit all queries and log unauthorized IP access attempts.

Answer

Enable auditing on the Azure Synapse Analytics workspace and set the audit log destination to a Log Analytics workspace.

Answer

Enable Microsoft Defender for SQL on the dedicated SQL pool and set the audit log destination to Azure Storage.

Answer

Configure Azure SQL Database auditing for the dedicated SQL pool by using the master database.

Question 9

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 10

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?

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 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 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 13

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 14

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.

Question 15

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.