DP-203 Practice Test 15 — 15 Questions

Question 1

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 2

Your team is building a real-time dashboard in Power BI that displays sales data from Azure Stream Analytics. The data must be updated every 5 seconds with low latency. Which output type should you configure in Stream Analytics to achieve this?

Accepted Answer

Power BI. Power BI is the correct output type because Azure Stream Analytics has a native Power BI output connector that supports real-time streaming datasets, enabling sub-second latency for dashboards. This connector pushes data directly to Power BI's streaming API, which refreshes visuals automatically every 5 seconds as required.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Event Hubs

Question 3

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

Accepted Answer

The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.. Option D is correct because the query uses the output of WatermarkQuery (which returns the maximum LastModified) as the filter condition, but it should filter for rows where LastModified is greater than the previous watermark, not the current maximum. This results in no rows being selected because no rows have LastModified > maximum. Option A is wrong because the pipeline succeeded. Option B is wrong because the watermark query executes before the copy activity. Option C is wrong because the dependency condition is correct.

Answer

The dependency condition should be 'Completed' instead of 'Succeeded'.

Answer

The WatermarkQuery activity failed, causing the CopyData activity to be skipped.

Answer

The CopyData activity runs before the WatermarkQuery activity completes.

Question 4

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

Accepted Answer

The TTL setting is too low, causing the cluster to be recreated frequently.. Option B is correct because the TTL (time-to-live) of 10 minutes means the cluster will be spun down after 10 minutes of inactivity, so the first execution after a period of inactivity must wait for the cluster to spin up. Option A is wrong because the location is AutoResolve and works. Option C is wrong because the core count is sufficient. Option D is wrong because the template deploys the IR correctly.

Answer

The core count of 8 is insufficient for the data flow.

Answer

The AutoResolve location cannot be used for Managed IR.

Answer

The integration runtime type should be 'Self-Hosted' for data flows.

Question 5

You are designing a data processing solution in Azure Databricks to transform streaming data from Azure Event Hubs. The data must be aggregated in 1-minute tumbling windows and written to Azure Synapse Analytics. Which Spark API should you use?

Accepted Answer

Structured Streaming. Structured Streaming is the correct choice because it provides native support for event-time-based aggregations, such as 1-minute tumbling windows, and integrates seamlessly with Azure Event Hubs as a streaming source and Azure Synapse Analytics as a streaming sink using the `foreachBatch` or `writeStream` API. It offers exactly-once semantics and automatic state management for windowed operations, which are essential for reliable streaming ETL.

Answer

RDD API

Answer

Spark Streaming (DStreams)

Answer

DataFrame API with batch processing

Question 6

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.. Option D is correct because 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 7

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

Accepted Answer

The storage account firewall does not have a 'Resource instances' exception for the managed identity. Option C is correct because when a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Answer

Parquet files require special permissions that are not granted by RBAC roles

Answer

The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'

Answer

The notebook is using an incorrect connection string with account key

Question 8

Which THREE factors should you consider when choosing between Azure Data Factory Mapping Data Flows and Azure Synapse Spark pools for data transformation?

Accepted Answer

Ease of use: Mapping Data Flows provide a visual designer, while Spark requires code.. Option B is correct because Azure Data Factory Mapping Data Flows offer a visual, no-code designer for building data transformations, which lowers the barrier for users who are not proficient in programming. In contrast, Azure Synapse Spark pools require writing code in languages like PySpark, Scala, or SQL, making them more suitable for developers comfortable with coding. This distinction directly addresses ease of use as a key factor in choosing between the two services.

Answer

Scheduling: Only Data Flows can be scheduled via triggers.

Answer

Data volume: Data Flows are limited to 100 GB, while Spark can handle petabytes.

Question 9

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

Accepted Answer

Increase the number of Streaming Units (SUs) allocated to the job.. The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Answer

Decrease the watermark delay interval.

Answer

Increase the late arrival tolerance window.

Answer

Increase the number of partitions in the output table.

Question 10

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data from multiple sources. You need to ensure that the data is encrypted at rest and in transit, and that access to the data is audited. Which combination of Azure services should you use?

Accepted Answer

Azure Data Lake Storage (encrypted at rest), Azure Synapse Analytics (TDE and SSL), and Microsoft Purview. Option A is correct because Azure Data Lake Storage provides encryption at rest using Azure Storage Service Encryption (SSE) with 256-bit AES, and Azure Synapse Analytics supports Transparent Data Encryption (TDE) for at-rest encryption and SSL/TLS for in-transit encryption. Microsoft Purview enables data governance and auditing by capturing lineage, classification, and access activity logs, meeting the compliance requirements for sensitive financial data.

Answer

Azure Blob Storage (encrypted at rest), Azure HDInsight, and Azure Log Analytics

Answer

Azure SQL Database (TDE and SSL), Azure Analysis Services, and Microsoft Purview

Answer

Azure Data Lake Storage (encrypted at rest), Microsoft Fabric, and Azure Monitor

Question 11

Refer to the exhibit. You have an Azure Synapse pipeline that runs a Spark notebook daily. The notebook uses the inputDate parameter to filter data. The notebook successfully processes data for '2024-01-01' but fails for '2024-01-02' with an error that the 'sales' table does not exist. The 'sales' table is created daily by a preceding job. What is the most likely cause?

Accepted Answer

The notebook expects a table named 'sales_20240102' but the preceding job creates 'sales_20240101'. The error indicates that the notebook is looking for a table named 'sales_20240102' (based on the inputDate parameter for '2024-01-02'), but the preceding job creates a table named 'sales_20240101' (the previous day's table). This mismatch occurs because the notebook dynamically constructs the table name using the inputDate parameter, and the preceding job likely creates the table with a date suffix that does not align with the current inputDate. The correct answer is A because the table naming convention is inconsistent between the two processes.

Answer

The notebook activity should have a dependency on the job that creates the table

Answer

The Spark pool does not have permissions to read the storage account where the table data is stored

Answer

The pipeline parameter 'inputDate' is not being passed to the notebook correctly

Question 12

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

Accepted Answer

Column-level security. Option C is correct because Column-level security in Azure Synapse allows you to restrict access to specific columns for specific users or roles. Option A is wrong because Row-level security restricts rows, not columns. Option B is wrong because Dynamic data masking obfuscates data but does not prevent access entirely. Option D is wrong because Microsoft Defender for Cloud is a security monitoring tool, not for column-level access control.

Answer

Row-level security (RLS)

Answer

Dynamic data masking

Answer

Microsoft Defender for Cloud

Question 13

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.. The error 'Could not create SSL/TLS secure channel' indicates a TLS version mismatch. Azure SQL Database and Azure Blob Storage require at least TLS 1.2 for secure connections. If the self-hosted integration runtime (SHIR) is configured to use TLS 1.0, the handshake fails because the services reject the older protocol. This is the most likely cause given the intermittent nature and the specific error message.

Answer

Azure SQL Database firewall rules blocking the IP address of the integration runtime.

Answer

Transient network connectivity issues between the services.

Answer

The Azure SQL Database DTU limit has been exceeded, causing throttling.

Question 14

You are designing a data processing solution in Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 and you need to perform transformations using Apache Spark. The security requirements mandate that all data in transit must be encrypted and that the storage account must not be accessible from the public internet. What should you configure?

Accepted Answer

Enable the storage account firewall and add a private endpoint for Azure Databricks to use.. Option A is correct because it satisfies both security requirements: encrypting data in transit and preventing public internet access. A private endpoint uses Azure Private Link to connect Azure Databricks to the storage account over the Microsoft backbone network, ensuring all traffic stays within the Azure network and is encrypted via TLS. The storage account firewall is then configured to deny all public traffic, so only the private endpoint can access the storage account, meeting the 'not accessible from the public internet' mandate.

Answer

Disable TLS on the storage account and use a shared access signature (SAS) token for authentication.

Answer

Use Azure Databricks with VNet injection and configure a service endpoint for the storage account.

Answer

Configure the storage account to use HTTPS only and enable firewall rules to allow only Azure services.

Question 15

You are developing a streaming solution with Azure Stream Analytics that outputs results to Azure Synapse Analytics. The output requires exactly-once semantics to avoid duplicates. Which configuration should you use?

Accepted Answer

Configure the output to Azure Synapse Analytics with 'Exactly once' output mode.. Azure Stream Analytics supports an 'Exactly once' output mode for Azure Synapse Analytics, which ensures that each event is written exactly once to the destination, preventing duplicates. This mode uses a combination of checkpointing and transactional writes to guarantee idempotent delivery without requiring custom logic.

Answer

Use an Azure Function output to write data idempotently to Synapse.

Answer

Set the event serialization format to Avro and enable 'Exactly once' event delivery.

Answer

Output to Azure Blob Storage and then use a COPY statement in Synapse.