DP-203 Practice Test 45 — 15 Questions

Question 1

A company uses Azure Data Factory to orchestrate an ETL pipeline that copies data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline runs hourly and uses a self-hosted integration runtime. Recently, the pipeline started failing with timeout errors. The on-premises SQL Server is healthy and the network is stable. What is the most likely cause and solution?

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

Accepted Answer

The self-hosted integration runtime is under-provisioned; scale up the VM or add more nodes. Timeouts in Azure Data Factory copy activities using a self-hosted integration runtime (IR) often occur when the IR is under-provisioned. Overloaded VMs or insufficient nodes lead to resource contention and slower data processing, causing timeouts. Scaling up the VM or adding more nodes resolves the issue. Option A is incorrect because outdated IR versions are not the typical cause; updates are managed automatically. Option B is incorrect because staging is used for large data transfers or cross-region copies, but not directly linked to timeouts from resource exhaustion. Option D is incorrect because increasing the source query timeout does not address the underlying resource constraint; it may only mask the problem.

Answer

The self-hosted integration runtime version is outdated; update it to the latest version

Answer

The copy activity is not using staging; enable staging through Azure Blob Storage

Answer

The source query timeout in the copy activity is too low; increase it to 3600 seconds

Question 2

Match the Azure service to its primary data processing use case. Drag each service on the left to the correct use case on the right.

Services: Azure Databricks, Azure Stream Analytics, Azure Data Factory, Azure Synapse Analytics
Use Cases: 
- Real-time event processing
- Orchestration of ETL pipelines
- Big data analytics with Spark
- Enterprise data warehousing

Accepted Answer

Azure Databricks - Big data analytics with Spark. All four matches are correct. Azure Databricks is used for big data analytics with Apache Spark. Azure Stream Analytics processes real-time event streams. Azure Data Factory orchestrates and automates ETL pipelines. Azure Synapse Analytics serves as an enterprise data warehouse with integrated analytics.

Question 3

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

Accepted Answer

A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.. The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Answer

The Azure Blob Storage account has been moved to a different subscription.

Answer

The self-hosted integration runtime node has been de-registered from Azure Data Factory.

Answer

The blob container has reached its maximum capacity.

Question 4

You are troubleshooting a pipeline in Azure Data Factory that copies data from an Azure Blob Storage to an Azure Synapse Analytics dedicated SQL pool. The pipeline fails with the error: 'PolyBase requires a varchar(max) column to be less than 1 MB.' Which action should you take to resolve this issue?

Accepted Answer

Configure the copy activity to use staging via Azure Blob Storage.. The error indicates that PolyBase is being used for the copy operation, and it has a limitation that varchar(max) columns must be less than 1 MB. Configuring staging via Azure Blob Storage (option B) allows the copy activity to use PolyBase with staging, which automatically splits large varchar(max) values into manageable chunks, bypassing the 1 MB limit. This is the recommended approach in Azure Data Factory for loading large string data into Synapse dedicated SQL pools.

Answer

Modify the source data to truncate varchar(max) columns to 8000 characters.

Answer

Increase the 'batchSize' property in the copy activity to 10000.

Answer

Disable PolyBase in the sink settings and use bulk insert instead.

Question 5

Your company runs an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage daily. Recently, the pipeline has been failing with the error: 'Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation, Error details: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).' The FTP server administrator confirms that the file exists and the credentials are correct. You need to resolve the issue with minimal administrative effort. What should you do?

Accepted Answer

Check the file path and correct the case sensitivity in the dataset. The error code 550 indicates that the file is not found or access is denied on the FTP server. Since the file exists and credentials are correct, the most likely cause is a case-sensitive file path mismatch. Option C is correct because checking and correcting the case sensitivity in the dataset resolves this. Option A is incorrect because switching to SFTP does not address the case sensitivity issue. Option B is incorrect because resetting credentials is unnecessary when they are already correct. Option D is incorrect because re-uploading the file does not fix the path reference problem.

Answer

Use an SFTP connector instead of FTP

Answer

Reset the FTP server credentials in the linked service

Answer

Ask the FTP administrator to re-upload the file

Question 6

Which TWO actions should be taken to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Configure firewall and virtual network rules. Configuring firewall and virtual network rules restricts network-level access to the storage account, preventing unauthorized traffic from reaching the data at rest. Option C is correct because enabling customer-managed keys (CMK) in Azure Key Vault provides an additional encryption layer for data at rest, allowing you to control and rotate encryption keys independently of Azure-managed keys.

Answer

Enable soft delete for blobs

Answer

Assign storage blob data contributor roles to users

Answer

Use client-side encryption with Azure Key Vault

Question 7

Which THREE factors should you consider when choosing between rowstore and columnstore indexes in Azure Synapse Analytics?

Accepted Answer

The table size is expected to be over 1 TB.. Columnstore indexes in Azure Synapse Analytics are optimized for large-scale data warehousing workloads, where table sizes exceeding 1 TB benefit from high compression and columnar storage, significantly improving scan and aggregation performance. Rowstore indexes, in contrast, are less efficient for such large datasets due to higher I/O and storage overhead.

Answer

The table contains many NULL values in indexed columns.

Answer

The table will be partitioned frequently.

Question 8

A data engineer is designing a batch processing pipeline that reads data from Azure Blob Storage, transforms it using Azure Databricks, and writes the output to Azure Synapse Analytics. The source files are in CSV format and arrive daily at 02:00 UTC. The transformation must be idempotent and the pipeline should handle late-arriving data (up to 2 hours). What is the best approach to trigger the pipeline?

Accepted Answer

Tumbling window trigger with window size of 1 day and a late arrival window of 2 hours. A tumbling window trigger in Azure Data Factory allows you to define a fixed-size window (1 day) and a late arrival window (2 hours), which ensures idempotent processing by automatically rerunning the window for late-arriving data within the specified delay. This matches the requirement for daily batch processing at 02:00 UTC while handling data arriving up to 2 hours late.

Answer

Storage event trigger using Azure Event Grid

Answer

Schedule trigger set to 02:00 UTC daily

Answer

Event trigger on blob creation in the container

Question 9

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 10

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Table Storage

Question 11

Your company uses Azure Synapse Analytics and has deployed a pipeline that uses a Mapping Data Flow to transform data. The data flow reads from a source in Azure Blob Storage and writes to a dedicated SQL pool. You notice that the data flow is running slowly and consuming a lot of Data Flow cluster resources. You need to improve performance without increasing the cluster size. Which action should you take?

Accepted Answer

Add a partitioning step in the data flow to distribute the data across partitions based on a key column.. Adding a partitioning step in the Mapping Data Flow distributes data across partitions based on a key column, which allows parallel processing across the cluster's nodes. This reduces data shuffling and improves throughput without increasing the cluster size, directly addressing the performance bottleneck caused by skewed or unpartitioned data.

Answer

Use a self-hosted integration runtime instead of the default auto-resolve IR.

Answer

Increase the batch size in the data flow settings to reduce the number of round trips.

Answer

Change the source format to Delta Lake to leverage optimizations.

Question 12

You are designing a batch processing pipeline in Azure Databricks. The data is stored in Delta Lake and you need to perform a time-series join between two tables: 'events' (100 billion rows) and 'sessions' (10 billion rows). The join condition is on 'device_id' and a timestamp range (event_time BETWEEN session_start AND session_end). Which join strategy would be most efficient?

Accepted Answer

Use a range join with interval threshold using Delta Lake's optimized join.. Delta Lake's optimized range join leverages interval threshold pruning and data skipping to efficiently handle time-series joins on large datasets. This strategy avoids full shuffles by using min/max statistics and Bloom filters to eliminate non-matching partitions, making it far more efficient than generic join methods for 100B and 10B row tables.

Answer

Broadcast the smaller table (sessions) to all nodes.

Answer

Use a sort-merge join by repartitioning both tables on device_id.

Answer

Bucket both tables on device_id with 500 buckets.

Question 13

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by month. You notice that queries filtering on a specific month still scan all partitions. The table has a clustered columnstore index. What is the most likely cause?

Accepted Answer

The columnstore index has a large number of open rowgroups that are not compressed.. When a clustered columnstore index has a large number of open rowgroups (i.e., rowgroups that have not yet been compressed into columnstore segments), queries may fall back to the delta store, which stores data in a B-tree-like structure. This bypasses partition elimination because the delta store does not maintain the same metadata for partition pruning, causing all partitions to be scanned. Compressing open rowgroups into closed, compressed rowgroups restores proper partition elimination behavior.

Answer

The table is hash-distributed on a column other than date, preventing partition elimination.

Answer

The statistics on the date column are outdated or missing.

Answer

The table is replicated, so partition elimination does not apply.

Question 14

Your company uses Azure Data Factory to orchestrate data movement. You need to monitor pipeline runs across multiple factories and create a dashboard that shows success and failure rates over the past 30 days. What is the most efficient approach?

Accepted Answer

Configure diagnostic settings for each Data Factory to send logs to a Log Analytics workspace, then create a workbook using KQL queries.. Configuring diagnostic settings for each Data Factory to send logs to a Log Analytics workspace enables querying and visualizing pipeline runs across multiple factories in a single dashboard using Azure Monitor workbooks with KQL queries. Option A is inefficient as it requires viewing each factory individually. Option B is incorrect because Azure Storage Analytics logs storage metrics, not Data Factory pipeline runs. Option D is inefficient because manual aggregation is not scalable.

Answer

Use the Data Factory monitoring UI to view runs for each factory individually.

Answer

Enable Azure Storage Analytics and query the logs stored in a storage account.

Answer

Create alert rules in Azure Monitor for each pipeline failure and aggregate manually.

Question 15

You are developing a real-time data processing solution for a financial services company. The system ingests stock trade data from Azure Event Hubs at 50,000 events per second. Each event is a JSON object with fields: TradeID, Symbol, Price, Quantity, Timestamp. You need to calculate a 5-minute rolling average of the trade price per symbol and store the result in Azure Cosmos DB for low-latency queries. Additionally, you need to detect anomalies where the price deviates more than 10% from the rolling average within the same window, and send alerts to Azure Event Grid. You must minimize latency and ensure that the processing is stateful across multiple partitions. What should you do?

Accepted Answer

Create an Azure Stream Analytics job. Define input from Event Hubs. Use a Tumbling window of 5 minutes to compute average price per symbol. Add a custom function to compare each event's price to the average and output anomalies. Write to Cosmos DB via the Azure Cosmos DB output adapter and to Event Grid via the Event Grid output adapter.. Azure Stream Analytics (Option D) is the most appropriate service for this scenario. It can ingest from Event Hubs, perform windowed aggregations (e.g., Tumbling window for rolling average), detect anomalies using conditional logic, and output to both Cosmos DB and Event Grid. It handles partitioning automatically and provides stateful processing with low latency. Option A uses Azure Functions, which are not designed for high-throughput stateful stream processing across partitions; each function invocation is stateless and would require an external cache (Redis) adding latency and complexity. Option B incorrectly describes Azure Synapse Pipelines with Data Flow, which is a batch-oriented ETL tool and not suitable for real-time streaming. Option C uses Azure Databricks Structured Streaming, which is more complex to manage and requires additional configuration for state management and output to multiple sinks, resulting in higher operational overhead compared to Stream Analytics.

Answer

Use Azure Functions with Event Hubs trigger. In each function invocation, compute the rolling average using a distributed cache (Redis) and detect anomalies. Write to Cosmos DB and Event Grid via output bindings.

Answer

Use Azure Synapse Pipelines with a Data Flow. Set up a streaming Data Flow from Event Hubs, compute rolling average using window functions, and sink to Cosmos DB and Event Grid.

Answer

Use Azure Databricks with Structured Streaming. Read from Event Hubs using Kafka API. Perform windowed aggregations and anomaly detection using Spark SQL. Write to Cosmos DB via the Azure Cosmos DB Spark connector and to Event Grid via HTTP sink.