DP-203 Practice Exam 2 — 20 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.

Question 11

You are designing a stream processing solution using Azure Stream Analytics. The job must reference a static lookup table (product catalog) stored in Azure Blob Storage. The catalog is updated once daily. The job should automatically pick up the latest version without restarting. Which two configurations are required? (Choose two.)

Accepted Answer

Set the reference input's 'Path pattern' to include date and time placeholders. Option B is correct because Azure Stream Analytics reference data inputs support path pattern placeholders like {date} and {time} to dynamically resolve the latest blob file. This allows the job to automatically load a new version of the static lookup table when the blob is updated, without requiring a job restart. The path pattern must be structured to match the naming convention of the uploaded file, such as 'catalog/{date}/{time}/products.csv'.

Answer

Configure the reference input with a static blob path

Answer

Use Azure Event Grid to trigger job restart on blob update

Answer

Store the reference data in Azure SQL Database instead of Blob Storage

Question 12

When designing a data processing solution using Azure Databricks, what is the recommended approach to handle schema evolution when reading data from Delta Lake tables?

Accepted Answer

Set the option 'mergeSchema' to 'true' on write. Option A is correct because in Delta Lake, schema evolution is automatically handled by setting the 'mergeSchema' option to 'true' on write operations. This allows new columns to be added or existing column types to be safely widened without manual intervention, preserving existing data and metadata integrity.

Answer

Set the option 'overwriteSchema' to 'true' on write

Answer

Manually alter the table schema using ALTER TABLE

Answer

Ignore schema changes and use 'failOnDataLoss' flag

Question 13

You are implementing a data pipeline using Azure Data Factory. The source is an on-premises SQL Server database. Which Azure Data Factory component is required to connect to the on-premises data source?

Accepted Answer

Self-hosted Integration Runtime. A self-hosted integration runtime (IR) is required to connect Azure Data Factory to on-premises SQL Server because it provides the compute environment for data movement between on-premises networks and Azure. It must be installed on a machine inside the corporate firewall, enabling secure communication via outbound HTTPS (port 443) to Azure. This is the only IR type that can access private, on-premises data sources directly.

Answer

Azure Integration Runtime

Answer

Managed Virtual Network Integration Runtime

Answer

Azure Data Factory Gateway

Question 14

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 15

You are tuning an Azure Stream Analytics job that reads from an Event Hub and writes to an Azure Synapse Analytics table. The job's SU% utilization is consistently at 90%. Which action would most likely reduce the SU% utilization?

Accepted Answer

Increase the number of streaming units (SU) allocated to the job.. Increasing the number of streaming units (SU) allocated to the job directly adds more compute resources, which reduces the SU% utilization by distributing the workload across more SUs. Since the job is consistently at 90% utilization, adding SUs lowers the per-SU load, preventing throttling and improving throughput. This is the standard scaling approach for Azure Stream Analytics when SU% is high.

Answer

Decrease the Event Hub throughput units.

Answer

Partition the output table in Azure Synapse Analytics.

Answer

Use a reference data join to filter events.

Question 16

You are monitoring an Azure Cosmos DB account using Azure Monitor. The 'Normalized RU Consumption' metric for a container is consistently above 90%. You need to ensure that the container can handle the load without throttling. What should you do?

Accepted Answer

Increase the provisioned throughput (RU/s) for the container.. The 'Normalized RU Consumption' metric indicates the percentage of provisioned throughput (RU/s) being used. Consistently above 90% means the container is operating near its capacity limit, risking throttling (HTTP 429 errors) during traffic spikes. Increasing the provisioned throughput (RU/s) directly raises the capacity, allowing the container to handle the load without throttling.

Answer

Change the partition key to a different property.

Answer

Switch the account to serverless mode.

Answer

Modify the indexing policy to exclude unused paths.

Question 17

Which THREE factors should you consider when designing a monitoring strategy for Azure Synapse Analytics dedicated SQL pool performance?

Accepted Answer

Use dynamic management views (DMVs) to identify long-running queries.. Option A is correct because dynamic management views (DMVs) in Azure Synapse Analytics dedicated SQL pool, such as sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps, provide real-time insight into query execution, allowing you to identify long-running queries, monitor resource consumption, and detect performance bottlenecks. This is a foundational monitoring practice for tuning workload performance.

Answer

Ensure data is evenly distributed across distributions.

Answer

Configure automatic index rebuild for columnstore indexes.

Question 18

You are reviewing an Azure Policy assignment that uses the above JSON to define a role-based access control (RBAC) action. What is the primary purpose of this policy?

Accepted Answer

To authorize generation of a shared access signature (SAS) token for the storage account.. The policy JSON defines a role-based access control (RBAC) action that grants the 'Microsoft.Storage/storageAccounts/listAccountSas/action' permission. This specific action authorizes the generation of a shared access signature (SAS) token at the storage account level, not at the container or blob level. Therefore, the primary purpose is to allow the generation of an account SAS token, which provides delegated access to storage services.

Answer

To assign RBAC roles to users for the storage account.

Answer

To enable delegation of access to a specific blob.

Answer

To allow users to set permissions on storage account containers.

Question 19

A data engineer monitors an Azure Stream Analytics job that processes real-time data. The job is falling behind, and the SU utilization is at 100%. Which action should be taken to improve performance?

Accepted Answer

Increase the number of Streaming Units (SU).. When SU utilization reaches 100%, the job is fully saturated and cannot process incoming data fast enough. Increasing the number of Streaming Units (SU) allocates more compute resources (CPU and memory) to the job, allowing it to handle higher throughput and reduce backlog. This is the direct and recommended action for resolving performance bottlenecks caused by insufficient SU capacity.

Answer

Reduce the number of Streaming Units.

Answer

Change the query compatibility level to 1.0.

Answer

Deploy a second Stream Analytics job and split the input.

Question 20

Which TWO actions should you take to reduce costs associated with an Azure Synapse Dedicated SQL Pool that is used for reporting during business hours only?

Accepted Answer

Pause the pool during non-business hours.. Option A is correct because pausing a Dedicated SQL Pool stops billing for compute resources (DWU) while retaining storage costs. Since the pool is only needed for reporting during business hours, pausing it during non-business hours directly eliminates compute charges for that period, which is the most significant cost driver.

Answer

Enable advanced data compression on all tables.

Answer

Scale down the pool during business hours.

Answer

Change the distribution of large tables to ROUND_ROBIN.