DP-203 Practice Test 50 — 15 Questions

Question 1

Which TWO actions should you take when monitoring Azure Data Lake Storage Gen2 to detect security threats?

Accepted Answer

Use Azure Security Center and Azure Defender for Storage.. Azure Security Center (now Microsoft Defender for Cloud) with Azure Defender for Storage provides built-in threat detection for Azure Data Lake Storage Gen2, including anomaly detection, malware scanning, and alerts for suspicious activities like unauthorized access or data exfiltration. This is a primary action for detecting security threats because it continuously monitors storage telemetry and applies machine learning to identify potential security incidents.

Answer

Enable soft delete for blobs to recover from accidental deletions.

Answer

Configure firewall and virtual network service endpoints.

Answer

Set up alerting on the 'Transactions' metric.

Question 2

You are a data engineer for a company that uses Azure Synapse Analytics dedicated SQL pool. You need to implement security best practices to protect sensitive data. Which TWO actions should you take? (Choose two.)

Accepted Answer

Enable Transparent Data Encryption (TDE) on the dedicated SQL pool.. Options C and E are correct. C: Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, protecting against unauthorized access to the physical storage. E: Column-level security restricts access to sensitive columns based on user roles or identities, ensuring that only authorized users can view or modify sensitive data. A is incorrect because firewall rules control network access, not data protection within the database. B is incorrect because Azure Storage encryption is enabled by default for all storage accounts and is not a security measure specific to Synapse SQL pool. D is incorrect because Dynamic Data Masking obfuscates data from all users, but it does not prevent access; users can still see the original data if they have permissions, unlike column-level security which can block access entirely.

Answer

Configure a firewall rule to allow only specific IP addresses.

Answer

Enable Azure Storage encryption for the underlying storage.

Answer

Use Dynamic Data Masking to obfuscate sensitive data from all users.

Question 3

You are processing streaming data from IoT devices using Azure Stream Analytics. The data includes temperature readings and device IDs. You need to calculate the average temperature per device over a 5-minute window, sliding every 1 minute. Which window function should you use?

Accepted Answer

Hop window. A Hop window in Azure Stream Analytics allows you to specify a window size (5 minutes) and a hop size (1 minute), creating overlapping windows that slide forward every minute. This matches the requirement to calculate the average temperature per device over a 5-minute period, recalculated every minute, as the hop window outputs results at each hop interval while retaining data across overlapping windows.

Answer

Session window

Answer

Sliding window

Answer

Tumbling window

Question 4

You need to secure data at rest for an Azure Data Lake Storage Gen2 account that contains sensitive financial data. Which configuration should you enable to ensure that data is encrypted using a customer-managed key stored in Azure Key Vault, and that access to the key is logged?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault and enable Key Vault logging. Azure Storage encryption with customer-managed keys in Key Vault provides control and logging. Option A is wrong because Microsoft-managed keys are the default but do not provide customer control. Option B is wrong because client-side encryption requires managing keys on the client side. Option C is wrong because infrastructure encryption adds a second layer but does not use customer-managed keys.

Answer

Enable Azure Storage encryption with Microsoft-managed keys

Answer

Implement client-side encryption using Azure Key Vault

Answer

Enable infrastructure encryption for double encryption

Question 5

Refer to the exhibit. You have an Azure Data Lake Storage Gen2 account with hierarchical namespace enabled. The exhibit shows an Azure CLI command output that lists access permissions. User user@contoso.com is a member of group-analysts@contoso.com. What level of access does user@contoso.com have to the /data/analytics/ directory?

Accepted Answer

Read and write access. RBAC permissions are additive. The user has Storage Blob Data Contributor at the root scope, which grants read/write access to all directories. The reader role at the directory scope does not override the broader contributor role. Option A is wrong because the user has write access via the root scope. Option B is wrong because the user has read access from the root scope already. Option D is wrong because the user has explicit permissions.

Answer

Access denied because of the directory-level reader role

Answer

Read-only access

Answer

No access

Question 6

You are developing a real-time data processing solution using Azure Stream Analytics. The input is from an Azure Event Hub, and the output is to an Azure Synapse Analytics dedicated SQL pool. You need to ensure exactly-once delivery semantics to the SQL pool. What should you configure?

Accepted Answer

Use the Azure Synapse Analytics output adapter with exactly-once semantics.. The Azure Synapse Analytics output adapter for Stream Analytics supports exactly-once delivery semantics to a dedicated SQL pool. This is achieved through built-in mechanisms that ensure each event is written exactly once, even in failure scenarios. Option A is incorrect because checkpointing is used for job recovery and state management, not for guaranteeing exactly-once output delivery. Option C is incorrect because custom deserializers handle data format conversion, not delivery guarantees. Option D is incorrect because event ordering by timestamp helps with time-based processing but does not provide delivery semantics like exactly-once.

Answer

Enable checkpointing in the query.

Answer

Use a custom deserializer.

Answer

Configure event ordering by timestamp.

Question 7

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 8

A multinational corporation uses Azure Data Lake Storage Gen2 to store petabytes of parquet files partitioned by date and hour. Data scientists report that queries on the last 7 days of data take over 30 minutes, while queries on older data are fast. The storage account uses the default Azure Blob Storage hierarchical namespace. Which action will MOST improve query performance on recent data?

Accepted Answer

Optimize the partition layout by partitioning by date first, then by hour, to reduce the number of partitions scanned for recent data. Partitioning by date first, then by hour, ensures that queries filtering on the last 7 days scan only the relevant date partitions, drastically reducing the amount of data read. In Azure Data Lake Storage Gen2, the hierarchical namespace allows partition pruning at the directory level, so a date-first layout minimizes the number of partitions scanned for recent data, directly addressing the performance bottleneck.

Answer

Convert the parquet files to CSV format to reduce metadata overhead

Answer

Enable soft delete on the storage account to reduce read latency

Answer

Apply Z-order clustering on the parquet files using Azure Databricks

Question 9

Refer to the exhibit. You have created an external table in Azure Synapse serverless SQL pool as shown. You run a query: SELECT ProductID, SUM(Amount) FROM dbo.ExternalSales WHERE SaleDate > '2024-01-01' GROUP BY ProductID. The query is slow and scans all files in the /sales/ folder, which contains data from 2023 and 2024. The files are partitioned by year and month in the folder structure, e.g., /sales/year=2023/month=01/. What should you do to improve query performance?

Accepted Answer

Recreate the external table with a partition definition on SaleDate column using the folder structure. The query performance is slow due to full file scanning. By recreating the external table with a partition definition on the SaleDate column that maps to the folder structure (e.g., /sales/year=2023/month=01/), Azure Synapse serverless SQL pool can perform partition elimination, reading only the relevant partitions for the WHERE clause filter (SaleDate > '2024-01-01'). This drastically reduces the amount of data scanned, improving query speed.

Answer

Recreate the external table with a partition on ProductID

Answer

Create statistics on the SaleDate column

Answer

Change the file format to CSV to improve read performance

Question 10

You need to configure encryption for an Azure SQL Database to protect data at rest. Which Azure service or feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) is the correct choice because it performs real-time I/O encryption and decryption of the data and log files at rest, protecting against unauthorized access to the physical storage media. TDE uses an AES-256 encryption algorithm and is fully transparent to the application, requiring no changes to the database schema or queries.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 11

In Azure Synapse Analytics serverless SQL pool, you query Parquet files stored in Azure Data Lake Storage Gen2. You notice that queries are slow. Which configuration change is most likely to improve performance?

Accepted Answer

Use OPENROWSET with a properly defined schema and file format. OPENROWSET with an explicitly defined schema and file format (e.g., FORMAT='PARQUET') enables the serverless SQL pool to bypass schema inference, which is a costly runtime operation. By providing a proper schema and file format, the query engine can directly read the Parquet metadata and column statistics, significantly reducing I/O and CPU overhead. This is the most direct and effective performance tuning change for querying Parquet files in a serverless SQL pool.

Answer

Partition the data in Azure Data Lake Storage Gen2

Answer

Create a pipeline in Azure Synapse to preprocess the data

Answer

Increase the DWU setting of the serverless SQL pool

Question 12

Refer to the exhibit. You are reviewing the workload classifier configuration for an Azure Synapse Analytics dedicated SQL pool. You notice that the 'HeavyLoader' classifier has a queryExecutionTimeoutSeconds of 0. What is the implication of this setting?

Accepted Answer

Queries classified as 'HeavyLoader' will not have a timeout.. When queryExecutionTimeoutSeconds is set to 0, it means no timeout is enforced. Queries classified as 'HeavyLoader' can run indefinitely without being terminated by the timeout mechanism. Option A is incorrect because a value of 0 does not mean indefinite waiting for resources; it refers to the timeout duration. Option B is incorrect; 0 is a valid configuration that disables the timeout. Option D is incorrect; a timeout of 0 does not cause immediate timeout but rather no timeout.

Answer

Queries classified as 'HeavyLoader' will wait indefinitely for resources.

Answer

The configuration is invalid; queryExecutionTimeoutSeconds must be greater than 0.

Answer

Queries classified as 'HeavyLoader' will timeout immediately.

Question 13

You are monitoring an Azure Data Factory pipeline that runs daily. You notice that some runs are failing due to transient network errors. You want to automatically retry the failed activities with a 5-minute delay, up to 3 times. How should you configure this?

Accepted Answer

On each activity, set 'Retry' to 3 and 'Retry interval' to 00:05:00.. Azure Data Factory activities have a 'Retry' property that can be set to 3, and 'Retry interval' to 00:05:00. Option A is too low (1 retry). Option B is wrong because retry is per activity, not at pipeline level. Option D is wrong because the default retry is 0.

Answer

Set the pipeline's 'Concurrency' to 3 and 'Retry' to 1.

Answer

Leave the default settings as they are because Azure Data Factory automatically retries failed activities 3 times.

Answer

Configure a 'Retry' policy on the pipeline itself, setting maximum retries to 3 and retry interval to 5 minutes.

Question 14

You need to ensure that an Azure Data Factory pipeline can copy data from an Azure SQL Database that is behind a private endpoint. The Data Factory should use a managed virtual network. What should you configure?

Accepted Answer

Enable managed virtual network for the Data Factory and create a managed private endpoint for the SQL Database.. A managed private endpoint in the Data Factory's managed virtual network allows secure access to the SQL Database's private endpoint. Option A is wrong because the integration runtime must be in the same virtual network. Option B is wrong because self-hosted IR is for on-premises data sources. Option D is wrong because Azure Bastion is for VM access, not data factory.

Answer

Install a self-hosted integration runtime on a VM in the same virtual network.

Answer

Use the default Azure integration runtime.

Answer

Use Azure Bastion to connect the Data Factory to the SQL Database.

Question 15

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to support both batch and near-real-time data ingestion. Which TWO of the following methods can you use to ingest data into a dedicated SQL pool? (Select TWO.)

Accepted Answer

CREATE TABLE AS SELECT (CTAS) from external tables.. CREATE TABLE AS SELECT (CTAS) from external tables is correct because it allows you to load data from external storage (e.g., Azure Blob Storage or Azure Data Lake Storage) into a dedicated SQL pool in a single, parallelized operation. This method leverages the MPP (Massively Parallel Processing) architecture of Synapse SQL pools to efficiently ingest large volumes of data for batch processing.

Answer

COPY INTO command.

Answer

Azure Logic Apps with SQL connector.

Answer

Azure Data Factory with a copy activity using native sink.