DP-203 Practice Test 3 — 10 Questions

Question 1

A company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. They notice that some queries are using more memory than expected, causing resource contention. Which TWO actions should they take to diagnose and optimize memory usage?

Accepted Answer

Increase the resource class for the users running the heavy queries.. Option B is correct because increasing the resource class for users running heavy queries allocates more memory to those queries, reducing resource contention by ensuring they have sufficient memory to execute efficiently. Option D is correct because querying sys.dm_pdw_exec_requests DMV allows you to identify queries with high memory grants, which is the first step in diagnosing which queries are consuming excessive memory and need optimization.

Answer

Enable result-set caching.

Answer

Scale up the DWU setting.

Answer

Rebuild clustered columnstore indexes.

Question 2

A company is using Azure Data Factory to copy data from an on-premises SQL Server to Azure Blob Storage. The data must be encrypted in transit using TLS 1.2. The on-premises SQL Server is configured to support TLS 1.2. Which Data Factory property should be configured?

Accepted Answer

The typeProperties property in the linked service to include 'Encrypt=True' in the connection string. Option B is correct because TLS 1.2 encryption for data in transit between Azure Data Factory and an on-premises SQL Server is enforced by adding 'Encrypt=True' and 'TrustServerCertificate=False' to the connection string within the typeProperties of the linked service. This ensures the SQL Server Native Client uses TLS 1.2 for the connection, aligning with the requirement.

Answer

The encryptedCredential property in the linked service

Answer

The connectVia property in the linked service

Answer

The integrationRuntime property in the dataset

Question 3

A data engineer is monitoring Azure Data Lake Storage Gen2 costs and notices high transaction costs for a specific container. The container stores Parquet files used by Azure Databricks for read-heavy analytics. The files are accessed frequently by multiple jobs. What is the most cost-effective way to reduce transaction costs?

Accepted Answer

Enable Azure CDN to cache the files.. Option D is correct because enabling Azure CDN caches the frequently accessed Parquet files at edge locations, reducing the number of direct read requests to Azure Data Lake Storage Gen2. This lowers transaction costs (both read and list operations) while maintaining low-latency access for read-heavy analytics workloads. The CDN serves cached content, so the storage account incurs fewer billable transactions.

Answer

Move the data to Azure Blob Storage cool tier.

Answer

Increase the Parquet file size to maximize block size.

Answer

Convert the container to Azure Files.

Question 4

You are designing a data solution in Azure that requires all data in transit between Azure Databricks and Azure Storage to be encrypted using a customer-managed key. Which configuration meets this requirement?

Accepted Answer

Enable 'Secure transfer required' on the storage account. Option A is correct because enabling 'Secure transfer required' on the storage account enforces HTTPS for all requests, which encrypts data in transit using TLS. This ensures that all traffic between Azure Databricks and Azure Storage is encrypted, and the encryption keys used for TLS are customer-managed when combined with a customer-managed key for the TLS session (though the question focuses on in-transit encryption, the key management for TLS itself is handled by the platform; the customer-managed key requirement is satisfied by using Azure Key Vault for the storage account's encryption key, but the in-transit encryption is enforced by HTTPS).

Answer

Configure a service endpoint and a firewall rule to restrict access to Azure Databricks

Answer

Create a customer-managed key in Azure Key Vault and assign it to the storage account for encryption

Answer

Set the minimum TLS version to 1.2 on the storage account

Question 5

Which TWO actions should you take to ensure that only authorized users can access sensitive data in an Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Implement row-level security. Row-level security (RLS) and column-level security are the two correct actions because they directly restrict data access at the row and column granularity within a dedicated SQL pool. RLS uses security predicates to filter which rows a user can query, while column-level security denies access to specific columns for unauthorized principals. Both are native features of Azure Synapse dedicated SQL pools that enforce authorization on the data plane.

Answer

Configure Azure Active Directory authentication

Answer

Enable dynamic data masking on all columns

Answer

Enable transparent data encryption

Question 6

A company runs a streaming pipeline using Azure Stream Analytics to ingest IoT data and output to Azure SQL Database. They notice that the output latency increases over time and eventually the job fails with a timeout error. What is the most likely cause?

Accepted Answer

The Azure SQL Database target table lacks proper indexes.. The most likely cause is that the Azure SQL Database target table lacks proper indexes. Without indexes, each batch of output from Stream Analytics triggers full table scans for inserts or updates, causing cumulative latency. Over time, the backlog exceeds the job's timeout threshold (default 5 minutes for output), leading to failure.

Answer

The Stream Analytics job has a high late arrival tolerance.

Answer

The event hub is not partitioned correctly.

Answer

The event hub consumer group is misconfigured.

Question 7

A data engineer is optimizing an Azure Data Lake Storage Gen2 account used for big data analytics. The account contains billions of small files (under 1 MB). The analytics jobs are slow and cost more than expected. Which THREE actions should the engineer take to improve performance and reduce costs?

Accepted Answer

Convert data to columnar file formats such as Parquet.. Option A is correct because converting data to columnar formats like Parquet reduces the amount of data read during analytics queries, as only the necessary columns are scanned. This significantly improves query performance and lowers I/O costs, especially for big data workloads on Azure Data Lake Storage Gen2.

Answer

Move data to the cool tier to reduce storage costs.

Answer

Enable soft delete to protect against accidental deletion.

Question 8

You are monitoring an Azure Data Lake Storage Gen2 account that stores streaming data from IoT devices. You notice that query performance on the data in Parquet format is degrading over time. You need to improve query performance for both current and future data. Which TWO actions should you take?

Accepted Answer

Partition the data by a column commonly used in filter conditions.. Partitioning the data by a column commonly used in filter conditions (e.g., date, device ID) enables predicate pushdown in query engines like Azure Synapse or Spark, allowing them to skip irrelevant partitions and scan only the necessary files. This directly addresses the performance degradation by reducing the amount of data read during queries, and it benefits both current and future data when applied consistently.

Answer

Move frequently accessed data to Azure SQL Database.

Answer

Enable soft delete on the storage account to optimize read performance.

Answer

Migrate the data to Azure NetApp Files for lower latency.

Question 9

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Accepted Answer

Delta Lake provides time travel capabilities for accessing historical data versions.. Option A is correct because Delta Lake's time travel feature allows querying previous versions of data using a timestamp or version number, which is essential for auditing, rollback, and reproducing historical reports. This capability is built on the transaction log that tracks every change, making it a key differentiator from plain Parquet files.

Answer

Parquet is easier to implement for schema evolution than Delta Lake.

Answer

Delta Lake reduces storage costs by automatically compressing data.

Answer

Parquet files are not natively supported by Azure Databricks.

Question 10

You are analyzing the exhibit from an Azure Monitor metric query for a storage account. What is the primary purpose of this query?

Accepted Answer

To retrieve the average count of block blobs per hour.. The query uses the 'avg' aggregation on the 'BlobCount' metric, which calculates the average number of blobs over the specified time granularity (e.g., per hour). The result shows the average count of block blobs per hour, not the total count or the count in a specific tier. This aligns with option D, as the query is designed to retrieve the average count of block blobs per hour.

Answer

To calculate the average number of block blobs in the hot tier.

Answer

To identify the time period with the highest blob count.

Answer

To measure the total size of all block blobs in the account.