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

Question 1

You have an Azure Data Lake Storage Gen2 account that contains sensitive data. You need to implement a solution that provides column-level security and dynamic data masking for queries executed by an Azure Synapse Analytics serverless SQL pool. (Choose two.)

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

Accepted Answer

Use CREATE VIEW with the MASKED WITH clause to mask sensitive columns for specific users.. Serverless SQL pools do not support native column-level security or dynamic data masking. To achieve these, you can create views that restrict columns or apply masking logic, and then grant permissions on those views. This provides the required security while allowing users to query the data.

Answer

Implement dynamic data masking by using the MASKED WITH clause in a CREATE TABLE statement.

Answer

Enable Azure Active Directory authentication and assign users to the db_owner role.

Answer

Create a database scoped credential that references a managed identity with access to the data lake.

Question 2

You are optimizing an Azure Synapse Analytics dedicated SQL pool. You observe that a large fact table is distributed using hash distribution on a column that has many NULL values and low cardinality. Queries joining this table to a dimension table on a different column are experiencing excessive data movement. What should you do to reduce data movement?

Accepted Answer

Change the distribution of the fact table to hash on the join column.. In dedicated SQL pools, hash distribution on the join column ensures that rows with the same key are stored on the same distribution. This minimizes data movement during joins, as each distribution can perform the join locally. Choosing a column with high cardinality and even distribution is critical. The current distribution key has many NULLs and low cardinality, leading to skew and excessive data movement.

Answer

Create a materialized view that pre-joins the tables.

Answer

Increase the number of distributions by scaling the dedicated SQL pool.

Answer

Replicate the fact table to all distributions.

Question 3

You are a data engineer managing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table with 60 daily partitions. You need to remove all data for a specific date (e.g., '2023-10-01') without affecting other partitions and ensure the operation completes as quickly as possible. Which T-SQL statement should you execute?

Accepted Answer

TRUNCATE TABLE FactSales WITH (PARTITIONS (30)). Using TRUNCATE TABLE with the PARTITIONS clause is the most efficient way to remove all data from a specific partition in a dedicated SQL pool. It is a metadata operation that deallocates storage quickly and does not affect other partitions. This approach minimizes logging and resource usage compared to row-by-row deletion, making it ideal for large fact tables with partitioned data.

Answer

DELETE FROM FactSales WHERE DateKey = '2023-10-01'

Answer

DROP TABLE FactSales

Answer

ALTER TABLE FactSales SWITCH PARTITION 30 TO FactSales_Staging

Question 4

You manage an Azure Data Lake Storage Gen2 account that stores sensitive financial data. You need to ensure that all data access is logged and that you can audit who accessed which files and when. You also need to detect anomalous access patterns, such as access from unusual locations. Which solution should you implement?

Accepted Answer

Enable Azure Defender for Storage and configure diagnostic settings to send logs to a Log Analytics workspace.. Azure Defender for Storage provides threat detection and anomaly detection for storage accounts, including suspicious access patterns. When combined with diagnostic settings that route logs to Log Analytics, you gain comprehensive auditing of data access, including user identity, IP address, and operation. Log Analytics enables querying and alerting on these logs. Together, they satisfy the need to log access and detect anomalies, whereas other options either lack anomaly detection or do not capture file-level access.

Answer

Configure Azure AD Privileged Identity Management (PIM) for the storage account and enable just-in-time access.

Answer

Enable soft delete for blobs and configure a lifecycle management policy to retain deleted blobs for 90 days.

Answer

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

Question 5

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 occasionally fails due to transient network errors. You need to implement a solution to automatically retry the copy activity on failure and log the retry attempts for auditing. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable Azure Monitor diagnostic settings for the Data Factory and send logs to Log Analytics.. To automatically retry on transient errors, you configure the retry property on the Copy activity. To log retry attempts for auditing, you enable diagnostic settings on the Data Factory and route logs to Log Analytics. These two actions together provide automatic retries and an audit trail of those retries.

Answer

Configure a failure dependency from the Copy activity to a Web activity that logs the error.

Answer

Set the timeout property on the Copy activity to a higher value.

Answer

Use a ForEach activity to loop the Copy activity until it succeeds.

Question 6

You are optimizing an Azure Synapse Analytics dedicated SQL pool that experiences performance degradation during peak hours. You observe that many queries are waiting on memory grants and tempdb usage is high. You need to reduce these waits and improve query performance. Which two actions should you take? (Choose two.)

Accepted Answer

Implement workload management with workload groups to isolate and prioritize critical queries.. Memory grant waits and high tempdb usage often stem from poor query plans and resource contention. Updating statistics gives the optimizer better information, reducing the need for large memory grants and spills. Workload management with workload groups allows you to prioritize critical queries and cap resource usage for others, alleviating contention. Together, these actions address the root causes without reducing concurrency or compute resources.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

Increase the resource class of the users running the queries.

Answer

Reduce the size of the dedicated SQL pool by scaling down to a lower service level.

Question 7

You are designing a monitoring solution for an Azure Synapse Analytics dedicated SQL pool. You need to identify queries that are currently running and consuming significant resources, and also capture historical query performance data for trend analysis. Which two actions should you take? (Choose two.)

Accepted Answer

Enable Azure Monitor diagnostic settings to send query execution logs to a Log Analytics workspace.. To monitor active queries, querying sys.dm_pdw_exec_requests provides real-time details. For historical performance data and trend analysis, enabling Azure Monitor diagnostic settings to send logs to Log Analytics is the appropriate solution. Other options either provide node-level granularity, are unsupported, or do not retain historical data.

Answer

Use the sys.dm_pdw_nodes_exec_requests DMV to view active requests across all compute nodes.

Answer

Create an Extended Events session to capture query execution events in real-time.

Answer

Query the sys.dm_pdw_sql_requests DMV to view historical query performance data.

Question 8

You are optimizing an Azure Synapse Analytics dedicated SQL pool. You observe that a large fact table is distributed using hash distribution on the ProductKey column, but most queries join this table with a dimension table on a different column, causing frequent data movement. You need to reduce data movement and improve query performance. What should you do?

Accepted Answer

Recreate the fact table with hash distribution on the column most frequently used in joins.. In a dedicated SQL pool, hash distribution on the column most frequently used in joins ensures that rows with matching keys are stored together, minimizing data movement during join operations. This directly reduces shuffle and broadcast overhead, leading to faster query execution. Choosing the right distribution column is a key optimization step for large fact tables.

Answer

Change the distribution of the fact table to round-robin.

Answer

Replicate the dimension table and keep the fact table hash-distributed on ProductKey.

Answer

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

Question 9

You are designing a monitoring solution for an Azure Data Factory pipeline that ingests data into Azure Data Lake Storage Gen2. The pipeline occasionally fails due to transient network issues. You need to implement an automated alert that triggers when the pipeline fails more than 3 times within 10 minutes, and you want to minimize false positives from individual transient failures. Which Azure Monitor alert configuration should you use?

Accepted Answer

Create a metric alert on the ADFPipelineFailedRuns metric with a threshold of 3 and an aggregation granularity of 10 minutes, and configure the alert to fire only when the pipeline name is not a transient test pipeline.. The correct configuration uses a metric alert on the ADFPipelineFailedRuns metric with an aggregation granularity of 10 minutes and a threshold of 3, while excluding a known transient test pipeline to avoid false positives. This approach directly monitors the failure count over the specified window and allows filtering out expected transient failures, meeting the requirement to minimize false positives. Metric alerts are near-real-time and cost-effective for this scenario.

Answer

Create a metric alert on the ADFPipelineFailedRuns metric with a threshold of 3 and an aggregation granularity of 10 minutes.

Answer

Create a log alert using a custom log query that counts failed pipeline runs and set the threshold to greater than 3 over a 10-minute window.

Answer

Create a metric alert on the ADFPipelineFailedRuns metric with a dynamic threshold and set the sensitivity to high.

Question 10

You are a data engineer for a logistics company. Your Azure Data Factory pipeline ingests real-time vehicle telemetry data into an Azure Data Lake Storage Gen2 account. The pipeline writes JSON files every 5 minutes and the data volume is growing rapidly. You need to optimize storage costs while ensuring the data remains accessible for analytics queries with minimal latency. What should you do?

Accepted Answer

Convert the JSON files to Parquet format and partition the data by date using Azure Data Factory data flows.. Converting JSON to Parquet and partitioning by date reduces storage footprint and accelerates analytics queries by enabling columnar reads and partition pruning. This directly addresses the need to optimize storage costs while maintaining low-latency access. Other options either do not reduce costs, increase latency, or are irrelevant to the performance and cost goals.

Answer

Enable Azure Storage soft delete for blobs and set the retention period to 30 days.

Answer

Configure Azure Data Lake Storage Gen2 to use hierarchical namespace and set the access tier to Hot for all files.

Answer

Enable Azure Blob Storage lifecycle management to move blobs to the Cool tier after 30 days and to the Archive tier after 90 days.

Question 11

You manage an Azure Data Lake Storage Gen2 account that ingests telemetry data from thousands of IoT devices. The data is stored in the Parquet format and partitioned by date. Analysts run interactive queries using Azure Synapse serverless SQL pools. You need to minimize the amount of data scanned per query and improve query performance. What should you do?

Accepted Answer

Partition the data by date in the folder structure and use a partitioned view or query with a date filter.. Partitioning data by date in the folder structure allows serverless SQL pools to perform partition elimination, scanning only the relevant folders for a given date filter. This reduces I/O and improves query performance. Other options either do not leverage Parquet's columnar benefits or do not provide a mechanism for skipping irrelevant data.

Answer

Enable caching on the storage account to store frequently accessed data in memory.

Answer

Create a view that references the Parquet files using OPENROWSET and filter by date.

Answer

Convert the Parquet files to CSV format to reduce file size.

Question 12

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table with 10 billion rows. Queries frequently filter on a date column and join to dimension tables on a product key. You need to improve query performance by minimizing data movement during joins and enabling effective partition elimination. What should you do?

Accepted Answer

Create a clustered columnstore index on the fact table and partition it by date.. For a large fact table in a dedicated SQL pool, a clustered columnstore index provides superior compression and query performance for analytical workloads. Partitioning by the date column allows the query optimizer to eliminate partitions that do not match the filter, reducing I/O. While distribution choice affects join data movement, the combination of columnstore and partitioning is fundamental for optimizing large fact tables, and hash distribution on join keys can further reduce movement.

Answer

Create a round-robin distributed table and a columnstore index on all columns.

Answer

Create a hash-distributed table on the product key and a clustered index on the date column.

Answer

Create a replicated table for the fact table and a nonclustered index on the date column.

Question 13

You manage an Azure Data Lake Storage Gen2 account that contains a large volume of JSON files. Your data pipeline uses Azure Data Factory to read these files and write to Azure Synapse Analytics. You notice that pipeline runs are slow during peak hours, and you suspect that the file structure is causing performance issues. You need to optimize the storage layout for better read performance. What should you do?

Accepted Answer

Enable hierarchical namespace on the storage account and reorganize files into a folder structure that reflects query patterns.. To optimize read performance for large datasets in Azure Data Lake Storage Gen2, you should enable hierarchical namespace and organize files into a folder structure that aligns with query patterns. This allows the query engine to prune irrelevant files, reducing the amount of data read. Converting to Parquet is beneficial, but it must be combined with appropriate partitioning, not a single large file. Increasing parallelism without a logical layout is less effective.

Answer

Convert all JSON files to Parquet format and store them in a single large file to minimize the number of files.

Answer

Enable Azure Storage analytics logging and use the logs to identify hot partitions, then move those files to a premium storage tier.

Answer

Increase the degree of copy parallelism in the Azure Data Factory copy activity and set the parallelCopies property to a higher value.

Question 14

You are a data engineer at a financial services firm. You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs every 15 minutes and copies only new or changed rows based on a timestamp column. You need to ensure that the pipeline can be monitored for failures and that alerts are sent to the data operations team when a copy activity fails. You also need to minimize administrative overhead. What should you do?

Accepted Answer

Configure diagnostic settings on the Azure Data Factory to send logs to a Log Analytics workspace, and create an alert rule based on the ADFActivityRunLogs table.. The most efficient and low-maintenance solution is to use Azure Monitor diagnostic settings to route Azure Data Factory logs to Log Analytics, then create an alert rule based on the ADFActivityRunLogs table. This provides detailed failure information and integrates with action groups for notifications. It avoids custom code and leverages native monitoring, meeting the requirement to minimize administrative overhead.

Answer

Create a Logic App that periodically queries the Azure Data Factory pipeline runs using the REST API and sends an email on failure.

Answer

Enable Azure Monitor alerts directly on the Azure Data Factory resource for the Copy activity failure metric.

Answer

Use Azure Event Grid to subscribe to Azure Data Factory events and trigger an Azure Function that sends a notification on failure.

Question 15

You are designing a security strategy for an Azure Synapse Analytics workspace that contains sensitive financial data. The data is stored in a dedicated SQL pool and accessed by analysts using Power BI. You need to ensure that analysts can only see aggregated data and cannot view individual customer records, even if they have read access to the database. What should you implement?

Accepted Answer

Create a view that aggregates the data and grant analysts access only to that view.. The requirement is to prevent analysts from viewing individual customer records while allowing aggregated data. Creating an aggregated view and granting access only to that view ensures that analysts can only retrieve summarized data. They cannot query the base tables directly, so they cannot see individual records. This is a robust security measure.

Answer

Column-level security to deny access to sensitive columns.

Answer

Dynamic data masking on the columns containing sensitive data.

Answer

Row-level security (RLS) with a filter predicate that restricts rows based on user identity.