DP-203 Practice Test 10 — 10 Questions

Question 1

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?

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

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 2

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

Accepted Answer

Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).. Partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Answer

Partition by device ID first, then by date.

Answer

Partition by device ID only, with a separate directory for each device.

Answer

Partition by device type only, with a directory for each type.

Question 3

You need to monitor the performance of an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a self-hosted integration runtime. Which metric is most important to monitor to ensure the self-hosted IR is not a bottleneck?

Accepted Answer

Queue depth for the self-hosted IR. The self-hosted integration runtime (IR) queue depth metric indicates the number of activities waiting to be processed by the IR. A consistently high or increasing queue depth signals that the IR cannot keep up with the workload, making it a bottleneck. Monitoring this metric allows proactive scaling or optimization.

Answer

Pipeline duration metric

Answer

Number of active connections to the IR

Answer

Data read and data written metrics for the pipeline

Question 4

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to implement a least-privilege security model. Which authorization mechanism should you use for granular control?

Accepted Answer

Use POSIX-like access control lists (ACLs).. POSIX-like access control lists (ACLs) in Azure Data Lake Storage Gen2 provide file- and directory-level granular permissions, enabling least-privilege access down to individual users or groups. They are the recommended mechanism when you need fine-grained control beyond what Azure RBAC roles at the container or storage account level can provide. ACLs support both access ACLs (for current access) and default ACLs (inherited by new child items).

Answer

Use storage account keys for access.

Answer

Use Azure RBAC roles at the storage account level.

Answer

Use shared access signatures (SAS) with stored access policies.

Question 5

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Accepted Answer

Enable Azure Storage Service Encryption (SSE) for data at rest. Option B is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting stored files and blobs even if the underlying media is compromised. Option D is correct because Data Lake Storage Gen2 supports a hierarchical namespace where Azure RBAC controls management-plane and broad data access while POSIX-style ACLs provide fine-grained file and directory permissions, ensuring only authorized identities can read or modify data. Option E is correct because requiring secure transfer (HTTPS) enforces TLS encryption of data in transit between clients and the storage account, preventing eavesdropping or tampering on the network. Option A is not correct because TLS 1.0 is deprecated and insecure; enabling it weakens transport security rather than strengthening it, and Azure recommends TLS 1.2 or higher. Option C is not correct because firewall IP restrictions are a network-perimeter control, not a mechanism for securing data at rest or in transit, and they do not encrypt or authorize data access by themselves.

Answer

Enable TLS 1.0 for compatibility with legacy clients

Answer

Configure firewall rules to allow only trusted IPs

Question 6

Your organization uses Azure SQL Database with Active Geo-Replication for disaster recovery. You need to ensure that all connections to the database use Microsoft Entra ID authentication and that access is audited. You also want to minimize the attack surface by disabling SQL authentication. What should you do?

Accepted Answer

Enable 'Azure AD-only authentication' in the Azure SQL Database server settings and remove all SQL Server authenticated logins.. Enabling 'Microsoft Entra ID-only authentication' in the Azure SQL Database server settings disables SQL authentication entirely, forcing all connections to use Microsoft Entra ID (formerly Azure AD). Removing all SQL Server authenticated logins further reduces the attack surface by eliminating those credentials. This meets the requirements of using Entra ID authentication and disabling SQL authentication.

Answer

Configure Conditional Access policies to require MFA for database access.

Answer

Create a server-level firewall rule to allow only specific IP addresses and enable SQL authentication.

Answer

Create an Azure RBAC role to restrict access to the database and assign it to users.

Question 7

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

Accepted Answer

Hash distribution on the most joined dimension key with clustered columnstore index. Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Answer

Round-robin distribution with clustered columnstore index

Answer

Replicated distribution with clustered columnstore index

Answer

Hash distribution on a dimension key with heap index

Question 8

Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?

Accepted Answer

Create a staging table, load data, then use CTAS and partition switching to replace the target partition.. CTAS with partition switching is the recommended pattern for dedicated SQL pools because it writes new data into a new distribution/partition and swaps it in via ALTER TABLE ... SWITCH, avoiding row-by-row UPDATE/DELETE operations that create fragmentation and delta-store bloat on clustered columnstore indexes. Because the target partition is replaced atomically, the clustered columnstore index remains well-compressed with minimal deleted-row overhead.

Answer

Use UPDATE for existing rows and INSERT for new rows.

Answer

Use DELETE and INSERT statements in a single transaction.

Answer

Use a MERGE statement to perform upserts.

Question 9

You need to store historical sales data for 10 years with infrequent queries. The storage cost must be minimized while retaining the ability to query using Azure Synapse serverless SQL pool. Which storage tier should you use?

Accepted Answer

Azure Storage Cool tier.. The Cool tier is the correct choice because it provides low-cost storage for data that is infrequently accessed (e.g., historical sales data spanning 10 years) while still supporting immediate read access via Azure Synapse serverless SQL pool. Unlike the Archive tier, Cool tier data is online and can be queried without the need for time-consuming rehydration, making it suitable for infrequent but on-demand analytical queries.

Answer

Azure Storage Archive tier.

Answer

Azure Storage Premium tier.

Answer

Azure Storage Hot tier.

Question 10

You need to process streaming data from Azure Event Hubs and store the results in Azure Cosmos DB for a real-time dashboard. The solution must handle duplicate events and ensure exactly-once processing. Which Azure service should you use?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is correct because it provides exactly-once processing when configured with Event Hubs and Cosmos DB output. Option A (Azure Data Factory) is batch-oriented. Option B (Azure Functions) may have at-least-once guarantees. Option D (Azure Databricks) can achieve exactly-once but requires more configuration.

Answer

Azure Data Factory

Answer

Azure Functions with Event Hubs trigger

Answer

Azure Databricks Structured Streaming