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

Question 1

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

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

Accepted Answer

Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.. ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON is an online operation that compresses all rowgroups, including small ones, into larger, higher-quality columnstore segments. It does not block writes, making it suitable for a busy fact table. This directly addresses poor segment quality and deleted rows, improving query performance.

Answer

Drop and recreate the clustered columnstore index.

Answer

Re-cluster the table using a different distribution key.

Answer

Run ALTER INDEX REBUILD on the clustered columnstore index.

Question 2

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to monitor for unauthorized access attempts and ensure compliance with regulatory requirements. You want to be alerted when someone attempts to access data without proper permissions. What should you configure?

Accepted Answer

Enable diagnostic settings for the storage account, send logs to Log Analytics, and create an alert rule for 'StorageRead' failures.. Diagnostic settings stream the storage account's control-plane and data-plane logs (including StorageRead/StorageWrite/StorageDelete) to Log Analytics, where you can query failed authorization attempts and build alert rules on them. This is the only option that both captures unauthorized access attempts and produces actionable alerts for compliance.

Answer

Assign RBAC roles to limit access and rely on Azure Activity Log.

Answer

Configure Azure Policy to audit storage account access.

Answer

Enable Azure Sentinel and connect storage logs.

Question 3

You are designing a data pipeline in Azure Data Factory that reads data from an Azure SQL Database and writes to Azure Synapse Analytics dedicated SQL pool. The pipeline will run hourly and process incremental changes. You need to ensure that the pipeline handles retries for transient failures and logs all activities for audit purposes. What should you configure?

Accepted Answer

Enable pipeline logging to an Azure Storage account and set retry policy on copy activity.. The requirement is twofold: handle retries for transient failures and log all activities for audit. Setting a retry policy on the Copy activity directly addresses transient failures (e.g., throttling, network blips) by automatically reattempting the activity. Enabling pipeline logging to Azure Storage captures activity run details for audit purposes. Together these satisfy both requirements within Azure Data Factory itself, without relying on external services.

Answer

Enable auditing on the Azure SQL Database and Azure Synapse SQL pool.

Answer

Configure a tumbling window trigger to rerun the pipeline on failure.

Answer

Use an Azure Monitor alert to notify on failures.

Question 4

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

Accepted Answer

Query the sys.dm_exec_requests DMV to view running queries and their resource usage.. The first step to identify resource-consuming queries in Azure Synapse serverless SQL pool is to query the sys.dm_exec_requests DMV, which provides real-time information about currently executing requests, including resource usage metrics like CPU time, elapsed time, and wait types. This allows you to pinpoint long-running or high-resource queries for further optimization.

Answer

Use query hints to optimize execution plans.

Answer

Enable diagnostic settings and send query logs to Log Analytics.

Answer

Create statistics on all columns used in queries.

Question 5

Which TWO actions should you take to secure data at rest in Azure Synapse Analytics dedicated SQL pools?

Accepted Answer

Use Always Encrypted with secure enclaves for specific columns.. Always Encrypted with secure enclaves encrypts sensitive columns at rest and during query processing. Option C is correct: Transparent Data Encryption (TDE) encrypts the entire database at rest. Options B, D, and E are incorrect: column-level security and Dynamic Data Masking control access or mask output but do not encrypt at rest; Azure RBAC manages permissions, not encryption.

Answer

Implement column-level security to filter sensitive columns.

Answer

Configure Dynamic Data Masking on tables containing sensitive data.

Answer

Assign Azure RBAC roles to restrict access to the storage account.

Question 6

Contoso Ltd. runs a real-time analytics solution on Azure Databricks with data streaming from Event Hubs. They need to ensure that all data in transit between Event Hubs and Databricks is encrypted using TLS 1.2 or higher. Currently, the Event Hubs namespace is configured with the default TLS version (1.0). The Databricks cluster uses a public endpoint. Compliance requires that only TLS 1.2 is accepted. You need to configure the environment to enforce TLS 1.2 without disrupting ongoing streaming. What should you do?

Accepted Answer

Update the Event Hubs namespace to require TLS 1.2, then modify the Databricks streaming job's connection string to include 'TransportType=AmqpTls' and restart the streaming job.. To enforce TLS 1.2 without disrupting ongoing streaming, you must first update the Event Hubs namespace to require TLS 1.2 via the 'Minimum TLS version' setting. Then, configure the Databricks streaming job's connection string to include 'TransportType=AmqpTls' to ensure the client uses TLS 1.2. Finally, restart the streaming job to apply the new connection settings. Option A correctly describes these steps. Option B incorrectly suggests rebooting the cluster, which is unnecessary and would disrupt streaming. Option C incorrectly suggests redeploying the cluster with an init script, which is not needed and causes disruption. Option D includes an unnecessary Spark configuration change; the connection string parameter (TransportType=AmqpTls) is sufficient.

Answer

Change the Event Hubs namespace minimum TLS version to 1.2 in the Azure portal, then reboot the Databricks cluster.

Answer

In the Event Hubs namespace, set 'Minimum TLS version' to 1.2 and redeploy the Databricks cluster with a new init script that forces TLS 1.2.

Answer

Use Azure CLI to set the Event Hubs namespace TLS version to 1.2 and update the Databricks cluster's Spark configuration to use TLS 1.2.

Question 7

You are a data engineer at Northwind Traders. You have an Azure Synapse Analytics workspace with dedicated SQL pools. You need to monitor query performance to identify slow-running queries and understand resource consumption. The solution must provide historical data for the last 30 days and allow alerting when queries exceed a certain duration. You also need to export the data to a Log Analytics workspace for correlation with other metrics. What should you use?

Accepted Answer

Enable Diagnostic Settings to stream SQL pool metrics and logs to a Log Analytics workspace, then create alert rules and use KQL queries for historical analysis.. Diagnostic Settings in Azure Synapse Analytics allow you to stream platform metrics and logs (such as SQL requests, SQL wait statistics, and SQL pool resource usage) directly to a Log Analytics workspace. This provides the required 30-day historical data retention and enables KQL-based analysis for identifying slow queries and resource consumption. Additionally, you can create alert rules in Azure Monitor based on these logs to trigger when query duration exceeds a threshold, and the Log Analytics workspace facilitates correlation with other metrics from various Azure resources.

Answer

Enable Diagnostic Settings for the dedicated SQL pool to send logs to Azure Storage, and query DMVs for historical data.

Answer

Use Azure Storage Analytics to analyze logs from the storage account backing the SQL pool.

Answer

Configure SQL Server Query Store and export data to Azure Blob Storage using elastic query.

Question 8

Your company uses Azure Data Factory to orchestrate data pipelines that ingest data from on-premises SQL Server to Azure Data Lake Storage Gen2. The network team has implemented a firewall that only allows outbound traffic on port 443. The on-premises SQL Server is not accessible via public endpoint. You need to configure a secure connection that complies with the firewall rules and uses managed identity for authentication. What should you use?

Accepted Answer

Install a Self-hosted Integration Runtime on an on-premises VM, register it with Azure Data Factory using managed identity, and configure the pipeline to use this IR for the SQL Server connection.. A self-hosted integration runtime (IR) installed on an on-premises VM can connect to SQL Server over the private network, then communicate with Azure Data Factory over port 443. Managed identity can be used for authentication to Azure Data Factory. Option A is incorrect: Azure IR with VNet injection still requires a public endpoint for on-premises SQL Server, which is not accessible. Option B is incorrect: Point-to-site VPN provides connectivity but does not directly support managed identity for ADF authentication, and additional configuration is needed. Option D is incorrect: Using an Azure IR with a public endpoint requires the on-premises SQL Server to have a public endpoint, which it does not, and it does not utilize managed identity for authentication.

Answer

Use Azure ExpressRoute to connect the on-premises network to Azure, then use an Azure Integration Runtime with a VNet injection.

Answer

Set up a point-to-site VPN from Azure to on-premises and use an Azure Integration Runtime with a VNet integration.

Answer

Use an Azure Integration Runtime with a public endpoint and configure a firewall rule to allow the Azure IR IP addresses.

Question 9

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from IoT devices and store it in a dedicated SQL pool for reporting. The data volume is high (millions of events per hour), and you need to optimize for both ingestion speed and query performance. You also need to ensure that the data can be partitioned by date for efficient maintenance. Which architecture should you recommend?

Accepted Answer

Use Event Hubs Capture to store data in Avro files in Blob Storage and then query with external tables.. The correct architecture. Event Hubs Capture automatically captures streaming data into Avro files in Blob Storage, which can be organized by date in folder structures. External tables in Azure Synapse can query these files with partition elimination, providing good query performance. This approach handles high-volume ingestion efficiently without impacting the streaming pipeline. Option A is incorrect because PolyBase in Azure Synapse does not natively support the Delta format (it supports Parquet, ORC, etc.), so loading Delta format using PolyBase is not feasible. Option B is incorrect because direct writes from Stream Analytics to a dedicated SQL pool are not efficient for millions of events per hour due to batch constraints and potential bottlenecks. Option C is incorrect because Azure SQL Database with elastic scaling is intended for online transaction processing (OLTP) workloads, not large-scale analytical processing.

Answer

Ingest data to Azure Data Lake Storage Gen2 in Delta format, then use PolyBase to load into a dedicated SQL pool partitioned by date.

Answer

Use Azure Stream Analytics to write directly to a dedicated SQL pool with a time-based window.

Answer

Store data in Azure SQL Database with elastic scaling and use linked server queries.

Question 10

Your company has a data lake in Azure Data Lake Storage Gen2 that stores sensitive customer information. You need to implement fine-grained access control so that data engineers can read all data, data scientists can read only anonymized data, and auditors can view access logs. The solution must use Azure role-based access control (RBAC) and access control lists (ACLs). You also need to enable auditing of read operations. What should you do?

Accepted Answer

Assign Storage Blob Data Contributor RBAC role to data engineers at the storage account level, use ACLs to deny read access to raw data for data scientists, and enable diagnostic settings for read requests to Log Analytics.. Option B correctly combines Azure RBAC at the storage account level for data engineers (Storage Blob Data Contributor) with POSIX-like ACLs on directories/files to restrict data scientists to anonymized data only. Enabling diagnostic settings to stream read request logs to Log Analytics satisfies the auditing requirement for auditors. This layered approach is the only one that meets all three personas' needs while using both RBAC and ACLs as mandated.

Answer

Use Azure RBAC to assign Storage Blob Data Reader to data scientists, and configure lifecycle management to move raw data to archive tier.

Answer

Assign RBAC roles at the storage account level and enable Storage Analytics logs for read operations.

Answer

Assign RBAC roles at the container level to grant read access to all users, and use Azure Policy to audit access.

Question 11

You are a data engineer managing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. Users report that point-lookup queries filtering on a single date column are slow, scanning the entire table. You need to improve query performance for these lookups without changing the table schema. What should you do?

Accepted Answer

Create statistics on the date column.. In dedicated SQL pools, statistics are critical for the query optimizer to generate efficient plans. Without accurate statistics on the date column, the optimizer may choose a full scan instead of a more selective operation. Creating or updating statistics is a non-schema change that can significantly improve point-lookup performance by enabling better cardinality estimates.

Answer

Partition the fact table by date.

Answer

Add a nonclustered index on the date column.

Answer

Create a columnstore index on the date column.

Question 12

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool that ingests data from Azure Data Lake Storage Gen2. The pool has 20 DW1000c units (40 nodes). During nightly ETL, you observe that CETAS (CREATE EXTERNAL TABLE AS SELECT) operations writing Parquet files are slow. You need to optimize the write throughput of CETAS to the data lake. What should you do?

Accepted Answer

Ensure the CETAS operation writes to a folder with multiple files by using a distributed file layout and increasing the number of distributions.. In a dedicated SQL pool, CETAS writes one file per distribution. With 60 distributions, you get 60 files, which may not saturate the storage bandwidth. Increasing the number of distributions (by scaling up) or partitioning the output creates more files and enables parallel writes, improving throughput. The correct approach is to align the file count with the pool's parallelism.

Answer

Set the MAXDOP option to 1 for the CETAS operation to reduce contention.

Answer

Enable PolyBase on the dedicated SQL pool and use external tables instead of CETAS.

Answer

Use CTAS to write to a staging table, then use the Export Data tier of Azure Data Factory to copy the data to the data lake.

Question 13

You have an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to implement column-level security to ensure that only members of the 'SalesAuditors' role can view the 'CreditCardNumber' column in the 'Sales' table. The solution must minimize administrative effort and support future column additions. What should you do?

Accepted Answer

Implement column-level security by granting SELECT on the specific column to the 'SalesAuditors' role and revoking SELECT on that column from public.. Column-level security in Azure Synapse Analytics dedicated SQL pool is implemented using GRANT and REVOKE statements on specific columns. Granting SELECT on the sensitive column to the SalesAuditors role and revoking it from public ensures that only authorized users can view it. This method is granular and scales well because new columns are not automatically exposed; you control access explicitly.

Answer

Create a view that excludes the 'CreditCardNumber' column and grant SELECT on the view to all users except 'SalesAuditors'.

Answer

Create a database role, grant SELECT on the specific column to that role, and deny SELECT on the column to other roles.

Answer

Use dynamic data masking on the 'CreditCardNumber' column with a mask that only the 'SalesAuditors' role can see unmasked.

Question 14

You manage an Azure Data Lake Storage Gen2 account used by an Azure Synapse Analytics workspace. Analysts in the finance department must be able to read all files under the /finance/ path but must not be able to access any other folders. You need to implement the least-privilege access control that persists regardless of how users connect. What should you do?

Accepted Answer

Assign the Storage Blob Data Reader role to the finance users at the storage account scope, and configure a POSIX ACL on the /finance/ directory granting read and execute to the finance group.. The correct solution uses Azure RBAC to grant read access at the account level and POSIX ACLs to restrict the finance group to the /finance/ directory. ACLs are evaluated for every request, ensuring that users cannot access other folders even if they have broader RBAC permissions. This combination enforces least privilege and works consistently across all access methods.

Answer

Create a shared access signature (SAS) token scoped to the /finance/ directory and distribute it to the finance users.

Answer

Assign the Storage Blob Data Contributor role to the finance users at the storage account scope, and rely on Azure Synapse workspace role-based access control to restrict folder access.

Answer

Enable a firewall on the storage account that allows only the finance users' IP addresses, and grant them the Storage Blob Data Reader role.

Question 15

You manage an Azure Data Lake Storage Gen2 account used by an Azure Synapse Analytics serverless SQL pool. Analysts report that queries over a large parquet dataset are slow, and you observe that the serverless SQL pool scans the entire folder even when queries filter on a single partition column. You need to reduce the amount of data scanned per query without changing the file format. What should you do?

Accepted Answer

Enable hierarchical namespace on the storage account and reorganize the data into a partitioned folder structure using the filter column as a directory path.. Partition pruning in a serverless SQL pool relies on a folder hierarchy that matches the filter predicate. By storing data under directories named for each partition value in a hierarchical namespace, the engine reads only the relevant folders, cutting bytes scanned and cost. This preserves parquet and requires no index or DWU changes, directly addressing the full-folder scan symptom.

Answer

Increase the DWU setting of the serverless SQL pool to improve query throughput.

Answer

Create a clustered columnstore index on the parquet files in the data lake.

Answer

Convert the parquet files to CSV format and enable PolyBase external tables.