DP-203 Practice Test 22 — 15 Questions

Question 1

Your team uses Azure Databricks for data processing. You need to implement a cost-control strategy that automatically terminates idle clusters after 30 minutes of inactivity, but allows users to override this policy for specific workloads that require long-running clusters. What is the most efficient approach?

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

Accepted Answer

Create a cluster policy that enforces auto-termination with a default of 30 minutes, but allows users to override the value for specific clusters.. Azure Databricks cluster policies let administrators define constraints and defaults for cluster configuration, including auto-termination. A cluster policy can set a default auto-termination of 30 minutes while allowing users to override the value (within policy-permitted bounds) for specific long-running workloads. This centralizes governance without blocking legitimate exceptions.

Answer

Instruct all users to set auto-termination to 30 minutes on each cluster they create.

Answer

Configure a global auto-termination setting in the Azure Databricks workspace that terminates all clusters after 30 minutes of inactivity.

Answer

Use Azure Policy to enforce a tag that triggers a function to terminate idle clusters.

Question 2

You have an Azure Data Factory pipeline that executes a stored procedure in Azure SQL Database. The pipeline fails with an error indicating that the stored procedure ran out of memory. What change should you make to the pipeline to resolve this?

Accepted Answer

Scale up the Azure SQL Database to a higher service tier.. The error indicates that the stored procedure ran out of memory, which is a resource limitation at the database level, not a transient failure or timeout issue. Scaling up the Azure SQL Database to a higher service tier (e.g., from Standard to Premium or increasing DTU/vCore count) provides more memory and compute resources, directly resolving the out-of-memory condition.

Answer

Add a retry policy to the stored procedure activity.

Answer

Increase the pipeline activity timeout.

Answer

Use a Self-Hosted Integration Runtime instead of Azure IR.

Question 3

You are designing a data pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure SQL Database. The data contains personally identifiable information (PII). What should you use to protect the data during transit?

Accepted Answer

Encryption over HTTPS/TLS. Encryption over HTTPS/TLS protects data while it is moving between Azure Blob Storage and Azure SQL Database, which is exactly what 'in transit' means. Azure Data Factory uses the HTTPS endpoint of Blob Storage and TLS-encrypted connections to Azure SQL Database by default, so enforcing TLS 1.2+ ensures PII cannot be intercepted in cleartext. This is the only option that addresses the transport layer rather than data at rest or classification.

Answer

Azure Information Protection

Answer

Azure Disk Encryption

Answer

Azure Storage Service Encryption

Question 4

An organization is using Azure Data Factory to ingest data from multiple on-premises SQL Server databases into Azure Synapse Analytics. They need to ensure that sensitive data is masked during ingestion before landing in the staging area. What is the best approach?

Accepted Answer

Use a Mapping Data Flow with derived column transformations to mask sensitive columns.. A Mapping Data Flow in Azure Data Factory can apply derived column transformations to mask sensitive fields (for example, using SHA2 hashing, substring, or fixed replacement) as data flows from source to sink. Because the masking occurs in the pipeline before data lands in the staging area, this satisfies the requirement to mask during ingestion.

Answer

Apply an Azure Policy that masks sensitive data in Azure Synapse Analytics.

Answer

Use Azure SQL Database dynamic data masking on the source databases.

Answer

Use Azure Purview to classify and mask sensitive data automatically.

Question 5

Your team is running a critical Azure Stream Analytics job that writes results to Azure SQL Database. Recently, the job has been failing with high latency and occasional data loss. You need to monitor the job's performance and set up alerts for when the watermark delay exceeds a threshold. What should you use?

Accepted Answer

Azure Monitor metrics for the Stream Analytics job.. Azure Monitor metrics for the Stream Analytics job provide built-in performance metrics such as Watermark Delay, Input Events, Output Events, and Runtime Errors, and you can create alert rules on these metrics when the watermark delay exceeds a threshold. This is the native, recommended way to monitor Stream Analytics job performance and set up alerts.

Answer

Application Insights SDK integration in the job.

Answer

Azure Log Analytics workspace connected to the job diagnostics logs.

Answer

Azure Data Explorer for querying job performance data.

Question 6

You are developing a real-time data processing solution using Azure Stream Analytics. The input is an Azure Event Hubs stream with JSON data containing a 'timestamp' field. You need to output the average temperature per device every minute using a tumbling window. Which query should you use?

Accepted Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, TumblingWindow(minute, 1). A tumbling window is a fixed, non-overlapping time window that groups events into distinct time segments. Using `TumblingWindow(minute, 1)` with `TIMESTAMP BY Timestamp` ensures that the average temperature per device is computed over each one-minute interval without overlap, which matches the requirement of 'every minute'.

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SlidingWindow(minute, 1)

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SessionWindow(minute, 1, 1)

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, HopWindow(minute, 1, 1)

Question 7

You deploy the Azure Security Center automation shown in the exhibit. What is the purpose of this automation?

Accepted Answer

It sends high-severity security alerts to an Event Hub for further processing.. The automation in Azure Security Center (now Microsoft Defender for Cloud) is configured to export high-severity security alerts to an Azure Event Hub. This is a built-in continuous export feature that streams alerts to an Event Hub for further processing by downstream systems like SIEM, SOAR, or custom applications. The correct option directly matches this purpose: sending high-severity alerts to an Event Hub.

Answer

It configures Azure Monitor to log high-severity alerts.

Answer

It applies an Azure Policy to remediate high-severity alerts.

Answer

It creates incidents in Azure Sentinel for high-severity alerts.

Question 8

You are a data engineer at a financial services company. You are developing a data processing pipeline that uses Azure Data Factory to copy transactional data from an Azure SQL Database to Azure Data Lake Storage Gen2. The pipeline runs daily and processes about 10 GB of data. You need to implement error handling for the pipeline. Specifically, if the copy activity fails due to a transient error, the pipeline should retry automatically. If the retry fails, the pipeline should log the error and send an email alert to the operations team. What should you do?

Accepted Answer

Configure the copy activity with retry policy (retry count = 2, retry interval = 30 seconds). Add a failure path to a web activity that calls an Azure Logic App to send an email.. Configuring the copy activity's retry policy (retry count and interval) handles transient failures automatically at the activity level. Adding a failure dependency path to a Web activity that invokes a Logic App provides the required logging and email alert when retries are exhausted. This directly satisfies both the retry and notification requirements.

Answer

Use Azure Functions to implement custom retry logic and send email.

Answer

Create an Azure Monitor alert for failed pipeline runs and configure an action group to send an email.

Answer

Set the pipeline retry to 2 and add a storage event trigger on the error file.

Question 9

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query returns fewer rows than expected. What should you check first?

Accepted Answer

Verify the file path and pattern in the OPENROWSET query.. When using OPENROWSET in Azure Synapse serverless SQL pool to query Parquet files, the most common reason for fewer rows than expected is an incorrect file path or pattern. If the path or pattern is too restrictive (e.g., missing a wildcard or pointing to a subfolder instead of the root), the query will only read a subset of the files, resulting in fewer rows. This is the first thing to verify before investigating schema or permissions issues.

Answer

Ensure the external table has the correct schema definition.

Answer

Check that the Azure AD identity has read permissions on the storage account.

Answer

Check the compression codec used in the Parquet files.

Question 10

You need to transform data in Azure Databricks using Apache Spark. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. Which method should you use to read the data into a Spark DataFrame?

Accepted Answer

spark.read.format('delta').load('abfss://container@storage.dfs.core.windows.net/path'). The data is stored in Delta Lake format, which requires using the 'delta' format reader in Spark to properly read the transaction log and schema. The `spark.read.format('delta').load()` method is the standard way to read Delta tables, leveraging the Delta Lake protocol for ACID transactions and time travel capabilities.

Answer

spark.read.parquet('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.csv('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.json('abfss://container@storage.dfs.core.windows.net/path')

Question 11

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use replicated tables for small dimension tables. Option C is correct because replicated tables in a dedicated SQL pool copy small dimension tables to every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option D is correct because materialized views precompute and persist the results of common aggregations, so repeated queries against large fact tables can read the smaller pre-aggregated result set instead of rescanning and re-aggregating the base data. Option A is wrong because hash distribution should be on a high-cardinality column that distributes rows evenly; low-cardinality columns cause data skew and uneven work distribution. Option B is wrong because round-robin distribution is best for staging or temporary tables, whereas fact tables benefit from hash distribution on a frequently joined column to minimize data movement. Option E is wrong because increasing DWU is a scaling action, not a query optimization technique, and doing it after every query is wasteful and does not address query design or data distribution issues.

Answer

Use hash distribution on a low-cardinality column

Answer

Use round-robin distribution for fact tables

Answer

Increase the DWU setting after every query

Question 12

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

Accepted Answer

Data is unevenly distributed across distributions, causing some distributions to be overloaded.. D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Answer

Statistics on the table are outdated.

Answer

The table is not properly partitioned.

Answer

Data compression is not working efficiently.

Question 13

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are running slower over time. The table is hash-distributed on a date key and has a clustered columnstore index. Which action should you take to improve query performance?

Accepted Answer

Rebuild the clustered columnstore index.. Over time, columnstore indexes can become fragmented due to insert, update, and delete operations, leading to compressed row groups that are not optimally sized or have deleted records. Rebuilding the clustered columnstore index reorganizes the data into fully compressed row groups, removes deleted rows, and restores the high compression and segment elimination that columnstore indexes rely on for fast query performance.

Answer

Add a non-clustered index on frequently filtered columns.

Answer

Change the distribution column to a column with higher cardinality.

Answer

Change the distribution to round-robin.

Question 14

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

Accepted Answer

Create external tables with statistics on relevant columns.. In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Answer

Create clustered columnstore indexes on the external tables.

Answer

Convert the Parquet files to CSV format for faster reads.

Answer

Partition the data into many small files.

Question 15

You have an Azure Data Lake Storage Gen2 account that stores parquet files. You need to ensure that files containing personally identifiable information (PII) are automatically classified and tagged. Which Azure service should you integrate?

Accepted Answer

Microsoft Purview. Microsoft Purview provides automated data classification and labeling for Azure Storage, including Azure Data Lake Storage Gen2. Option A is wrong because Azure Policy enforces rules but does not classify content. Option B is wrong because Microsoft Sentinel is a SIEM, not for classification. Option C is wrong because Microsoft Defender for Cloud is for security posture, not data classification.

Answer

Azure Policy

Answer

Microsoft Sentinel

Answer

Microsoft Defender for Cloud