DP-203 Practice Test 17 — 15 Questions

Question 1

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

Accepted Answer

Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.. Option A is correct because the self-hosted IR's memory and network stability are common causes of 'Connection closed' errors with large data volumes. Option B (disable fault tolerance) would actually make the issue worse. Option C (change to staging copy) might help but is not the first step. Option D (increase TTL) addresses only idle connections, not active data transfers.

Answer

Increase the 'connection timeout' setting in the linked service to 30 minutes.

Answer

Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

Answer

Disable fault tolerance in the copy activity to improve performance.

Question 2

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Answer

Azure Synapse Spark with Structured Streaming

Answer

Azure Data Factory mapping data flows

Answer

Azure Synapse dedicated SQL pool with T-SQL queries

Question 3

Refer to the exhibit. You have created an external table in Azure Synapse serverless SQL pool as shown. You run a query: SELECT ProductID, SUM(Amount) FROM dbo.ExternalSales WHERE SaleDate > '2024-01-01' GROUP BY ProductID. The query is slow and scans all files in the /sales/ folder, which contains data from 2023 and 2024. The files are partitioned by year and month in the folder structure, e.g., /sales/year=2023/month=01/. What should you do to improve query performance?

Accepted Answer

Recreate the external table with a partition definition on SaleDate column using the folder structure. Option A is correct because the query performance is slow due to full file scanning. By recreating the external table with a partition definition on the SaleDate column that maps to the folder structure (e.g., /sales/year=2023/month=01/), Azure Synapse serverless SQL pool can perform partition elimination, reading only the relevant partitions for the WHERE clause filter (SaleDate > '2024-01-01'). This drastically reduces the amount of data scanned, improving query speed.

Answer

Recreate the external table with a partition on ProductID

Answer

Create statistics on the SaleDate column

Answer

Change the file format to CSV to improve read performance

Question 4

Which TWO features of Azure Databricks help manage data governance and security for sensitive data?

Accepted Answer

Secret Scopes. Secret Scopes (B) allow secure storage and referencing of sensitive credentials (e.g., API keys, database passwords) in Azure Databricks, preventing hardcoding in notebooks. Unity Catalog (E) provides fine-grained access control, data lineage, and centralized metadata management across workspaces, enabling governance of sensitive data through policies and auditing.

Answer

Structured Streaming

Answer

Auto Loader

Answer

Delta Live Tables

Question 5

Which TWO strategies reduce data movement in Azure Synapse Analytics pipelines? (Choose two.)

Accepted Answer

Use serverless SQL pool to query data in place. Option C is correct because serverless SQL pools in Azure Synapse Analytics allow you to query data directly from files in Azure Data Lake Storage or other external sources without moving the data into a dedicated SQL pool. This eliminates data movement entirely by using the compute resources of the serverless pool to process queries in place, leveraging the OPENROWSET or CREATE EXTERNAL TABLE syntax to read data from its original location.

Answer

Use Data Flow to transform data before loading

Answer

Use Stored Procedure activity to insert data

Answer

Use Copy activity to move data from source to staging

Question 6

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

Accepted Answer

Create new columns or modify existing columns using expressions.. The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Answer

Aggregate data by grouping rows.

Answer

Sort data in ascending or descending order.

Answer

Rename or drop columns.

Question 7

You are building a data transformation in Azure Databricks using PySpark. The data includes a column 'timestamp' in string format 'yyyy-MM-dd HH:mm:ss'. You need to convert this to a timestamp type and extract the date part for partitioning. Which code snippet should you use?

Accepted Answer

df.withColumn('date', to_date('timestamp', 'yyyy-MM-dd HH:mm:ss')). Option C is correct because `to_date` with the format string 'yyyy-MM-dd HH:mm:ss' converts the string column to a date type, extracting only the date part (year, month, day) as required for partitioning. This matches the requirement to convert the timestamp string to a date for partitioning, not a full timestamp.

Answer

df.withColumn('date', col('timestamp').cast('date'))

Answer

df.withColumn('date', to_timestamp('timestamp', 'yyyy-MM-dd HH:mm:ss'))

Answer

df.withColumn('date', to_date('timestamp'))

Question 8

You are designing a data processing pipeline in Azure Synapse Analytics that ingests streaming data from Azure Event Hubs and stores it in a dedicated SQL pool. The data must be available for querying within 5 minutes of ingestion. Which processing approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a dedicated SQL pool output and configure a 1-minute window.. Azure Stream Analytics is purpose-built for real-time stream processing and can output directly to a dedicated SQL pool. By configuring a 1-minute window, you ensure data is materialized in the SQL pool well within the 5-minute SLA, meeting the latency requirement with headroom.

Answer

Use Azure Data Factory with a tumbling window trigger set to 5 minutes.

Answer

Use PolyBase to load data from Event Hubs into the dedicated SQL pool every 5 minutes.

Answer

Use Spark Structured Streaming in Azure Synapse to write micro-batches every 5 minutes.

Question 9

A small business uses Azure SQL Database as their operational data store. They want to build a simple reporting solution that refreshes daily. The data volume is about 10 GB. They have limited budget and no dedicated data engineering team. They need to extract data from Azure SQL Database, perform basic transformations (e.g., aggregations, joins), and load into a reporting table in the same database. They want to minimize administrative effort and cost. Which approach should they take?

Option A: Use Azure Data Factory with a copy activity to copy data from the source to a staging table, then use a stored procedure activity to run T-SQL transformations.

Option B: Use Azure Logic Apps to periodically query the source and insert transformed data into the reporting table.

Option C: Use Azure Databricks notebooks scheduled via Azure Data Factory to perform the ETL.

Option D: Use Azure SQL Database elastic job to run a T-SQL script that performs the ETL within the database.

Accepted Answer

Option D. Option D is correct because using an Azure SQL Database elastic job to run a T-SQL script directly within the database is the simplest, lowest-cost, and lowest-administration approach for this scenario. It avoids external services, requires no data movement, and leverages existing SQL skills, making it ideal for a small business with limited budget and no dedicated data engineering team.

Answer

Option B

Answer

Option A

Answer

Option C

Question 10

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

Accepted Answer

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0. Option D is correct: Adding a derived column transformation in a Mapping Data Flow allows you to replace NULLs with a default value. Option A is wrong because it changes the sink schema which may not be allowed. Option B is wrong because filtering out rows with NULLs would lose data. Option C is wrong because setting the column to allow NULLs might not be permitted and could impact downstream processes.

Answer

Add a filter in the copy activity to exclude rows with NULL Amount

Answer

Modify the sink table to have a default value for the Amount column

Answer

Change the sink table column to allow NULLs

Question 11

You are designing a streaming data solution for IoT devices that generate 10,000 events per second. The data must be processed with sub-second latency and then stored in Azure Data Lake Storage Gen2 for archival. Which Azure service should you use for the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is designed for real-time stream processing with sub-second latency and can output to Azure Data Lake Storage Gen2. Option A (HDInsight including Spark) is batch-oriented and has higher latency. Option C (Data Factory) is for orchestration, not streaming. Option D (Event Hubs) is an ingestion service, not a processing service.

Answer

Azure HDInsight including Spark Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

Question 12

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

Accepted Answer

The storage account creation will be denied and the request will fail.. Option A is correct because the policy denies creation of storage accounts that do not have HTTPS enforced (supportsHttpsTrafficOnly = false). The policy is evaluated at creation time and will deny the request. Option B is wrong because the policy has deny effect, not audit. Option C is wrong because the policy does not set the property automatically. Option D is wrong because the policy is evaluated during creation, not after.

Answer

The storage account will be created, but the policy will be evaluated later during a compliance scan.

Answer

The storage account will be created with HTTPS enabled automatically.

Answer

The storage account will be created, but an audit event will be logged.

Question 13

You are designing a data processing solution in Azure using Azure Data Lake Storage Gen2 as the storage layer. You need to ensure that data ingested from various sources is immutable and can be used for both batch and streaming workloads. Which storage design pattern should you implement?

Accepted Answer

Implement a medallion architecture with bronze, silver, and gold layers.. Option A is correct because a medallion architecture (bronze, silver, gold) is a common pattern for organizing data in a lakehouse, providing immutability in bronze, transformations in silver, and aggregations in gold. It supports both batch and streaming. Option B is wrong because a star schema is a dimensional modeling approach for data warehouses, not a storage pattern for immutability. Option C is wrong because a data vault is an enterprise data modeling pattern, not specifically for immutability in a lake. Option D is wrong because a normalized relational model is not suited for big data lake scenarios.

Answer

Store data in a normalized relational database structure.

Answer

Use a data vault model with hubs, links, and satellites.

Answer

Design a star schema with fact and dimension tables.

Question 14

You are designing a near-real-time data processing solution that ingests millions of events per second from IoT devices. The data must be aggregated on a per-minute basis and stored in Azure Data Lake Storage Gen2 for long-term analytics. The solution must also support alerting when certain thresholds are exceeded. Which combination of Azure services should you use?

Accepted Answer

Azure IoT Hub, Azure Stream Analytics, and Azure Functions.. Option D is correct because Azure IoT Hub ingests device data, Azure Stream Analytics performs real-time aggregation and alerting, and Azure Functions can write aggregated results to Data Lake Storage. Option A (Azure Event Hubs) is a generic event broker but lacks device management capabilities. Option B (Azure Databricks) is more suited for complex analytics than simple aggregation. Option C (Azure Data Explorer) is for ad-hoc analytics, not for streaming aggregation with alerting.

Answer

Azure Event Hubs, Azure Data Factory, and Azure SQL Database.

Answer

Azure IoT Hub, Azure Databricks with Structured Streaming, and Azure Data Lake Storage Gen2.

Answer

Azure Event Hubs, Azure Data Explorer, and Power BI.

Question 15

Your company uses Azure Data Lake Storage Gen2 and Azure Databricks for data processing. Some Parquet files in the lake are written with a schema that includes a column 'address' of struct type. A downstream process expects 'address' to be a string. You need to transform the data in a way that minimizes read overhead and does not rewrite the entire dataset. Which approach should you use?

Accepted Answer

Apply a schema-on-read transformation that casts the column to string when reading.. Option C is correct because schema-on-read allows you to read the struct and cast it to string without rewriting files. Option A is incorrect because rewriting is costly. Option B is incorrect because schema evolution does not change data types. Option D is incorrect because it would create a new copy.

Answer

Use schema evolution in Delta Lake to change the column type.

Answer

Create a view that selects the column as a string and save it as a new table.

Answer

Rewrite the Parquet files with the correct schema using a Spark job.