DP-203 Develop data processing Practice Test 11 — 15 Questions

Question 1

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?

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

Accepted Answer

Apply a schema-on-read transformation that casts the column to string when reading.. Schema-on-read allows you to cast the 'address' column from struct to string at query time without modifying the underlying Parquet files. This approach minimizes read overhead by avoiding a full data rewrite and leverages Spark's ability to apply transformations during the read path, which is efficient for downstream processes that expect a string type.

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.

Question 2

A company is ingesting streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time and stored in Azure Synapse Analytics for reporting. The solution must handle late-arriving data and ensure exactly-once semantics. Which Azure service should you use for stream processing?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is correct because it supports exactly-once semantics for output to Azure Synapse Analytics, handles late-arriving data via adjustable event ordering policies, and is purpose-built for real-time stream processing. Option A (Azure Data Factory) is incorrect as it is primarily a batch ETL service and not designed for near-real-time streaming. Option B (Azure Synapse Spark Structured Streaming) can process streaming data but does not provide exactly-once semantics to Synapse out of the box without additional configuration. Option D (Azure Event Hubs with Capture) is incorrect because it only captures raw event data to storage and does not perform stream processing or guarantee exactly-once delivery to Synapse.

Answer

Azure Data Factory with Event Hubs source

Answer

Azure Synapse Spark Structured Streaming

Answer

Azure Event Hubs with Capture

Question 3

You are designing a data processing solution for a large e-commerce company. The data includes sensitive customer information that must be encrypted at rest and in transit. The solution uses Azure Data Lake Storage Gen2 and Azure Databricks. Compliance requires that all access to the data is logged and monitored. Which combination of security features should you implement?

Accepted Answer

Enable Azure Storage encryption at rest, enforce HTTPS, and configure diagnostic settings to log all read/write operations.. It covers encryption at rest (Azure Storage encryption), encryption in transit (HTTPS), and logging via diagnostic settings for read/write operations. Option A is incorrect because Azure RBAC does not log access details; it only controls permissions. Option B is incorrect because Azure Firewall restricts network traffic but does not provide logging of data access or encryption. Option D is incorrect because Transparent Data Encryption (TDE) is a feature for Azure SQL databases, not for Azure Data Lake Storage Gen2.

Answer

Enable Azure RBAC for storage and use Azure AD authentication.

Answer

Use Azure Firewall to restrict access and enable Azure AD Authentication for Databricks.

Answer

Enable transparent data encryption (TDE) on the storage account and use service endpoints.

Question 4

A data engineer is building a pipeline in Azure Data Factory to load data from Azure Blob Storage to Azure Synapse Analytics using PolyBase. The pipeline must handle large files efficiently and minimize load time. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Split large files into multiple smaller files (e.g., 256 MB each).. To optimize PolyBase loads for large files, splitting files into smaller chunks (e.g., 256 MB) enables parallel processing and reduces load time. Configuring PolyBase with the 'RejectValue' option allows handling of bad rows without failing the entire load, improving resilience. Option B (using a staging table) is not a PolyBase-specific optimization; staging is used for other loading methods like COPY INTO. Option C (converting to Parquet) is not directly related to PolyBase performance and is not required. Option D (copy activity with staging) may use PolyBase but the question asks for PolyBase-specific actions, and staging is not a PolyBase feature. Therefore, the correct actions are A and E.

Answer

Use a staging table to stage the data before loading.

Answer

Convert files to Parquet format for better compression.

Answer

Use the copy activity with staging enabled.

Question 5

You are designing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs, and the output is to Azure Synapse Analytics. The solution must guarantee exactly-once delivery to Synapse. Which THREE configurations are required? (Choose three.)

Accepted Answer

Define a watermark strategy in the query to handle late-arriving events.. For exactly-once delivery to Azure Synapse Analytics from Stream Analytics, you need: a unique identifier column in the output table to enable deduplication (D), a watermark strategy to handle late-arriving events (B), and the output table must have a primary key to support upsert operations (E). Batch mode (A) is not supported for exactly-once delivery; instead, the output should use row-level insert/upsert. Setting the late arrival tolerance to zero (C) is not required; it is used to discard events that arrive after the tolerance, but it does not contribute to exactly-once semantics.

Answer

Configure the output to use batch mode for writing.

Answer

Set the late arrival tolerance window to zero.

Question 6

A company uses Azure Data Lake Storage Gen2 as the data lake. The data engineering team needs to ensure that sensitive data such as credit card numbers are masked when queried by non-admin users. The solution must be implemented within the data lake without moving data to another store. Which TWO features should they use? (Choose two.)

Accepted Answer

Azure Synapse Serverless SQL with dynamic data masking. Azure Synapse Serverless SQL can query data directly from Azure Data Lake Storage Gen2 and supports dynamic data masking (DDM) to obfuscate sensitive columns like credit card numbers from non-admin users. This allows masking to be applied at query time without moving or transforming the underlying data in the lake.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies

Question 7

Refer to the exhibit. A data engineer is reviewing a Mapping Data Flow in Azure Data Factory. The source reads 1000 rows from a CSV file. The sink writes to a Delta dataset with optimizeWrite enabled. The data flow currently has no transformations. The engineer runs the data flow and notices that the sink writes only 500 rows. What is the most likely cause?

Accepted Answer

The sink's allowSchemaDrift setting prevents writing rows with additional columns.. The sink's allowSchemaDrift setting, when set to false, prevents writing rows that have additional columns beyond the target schema. Since the source CSV may contain rows with extra columns (e.g., inconsistent formatting or varying column counts), those rows are silently dropped during the sink operation. This accounts for the reduction from 1000 rows read to 500 rows written.

Answer

The source's rowLimit setting limits the number of rows read, but the sink writes only unique rows.

Answer

The sink's partitionBy('hash', 1) setting causes some rows to be dropped due to partitioning logic.

Answer

The data flow has no transformations, so the sink cannot write data.

Question 8

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options:

Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase.

Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase.

Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse.

Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source.

Which option should you choose?

Accepted Answer

Option B. It handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency. Option D requires additional log-shipping, increasing complexity and latency.

Answer

Option C

Answer

Option A

Answer

Option D

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. 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. (Note: Option C corresponds to the approach listed as Option D in the original question options.)

Answer

Option B

Answer

Option A

Answer

Option C

Question 10

You are implementing a data pipeline in Azure Synapse Analytics that uses PolyBase to load data from Azure Data Lake Storage Gen2 into a dedicated SQL pool. The pipeline runs nightly and processes approximately 500 GB of data. You notice that the load operation is slow and frequently times out. What should you do to improve performance?

Accepted Answer

Increase the resource class for the loading user.. Increasing the resource class in dedicated SQL pool provides more resources (CPU, memory) to the PolyBase load operation, which directly addresses the timeout issue. Option A is wrong because columnstore indexes are relevant for query performance, not load speed. Option B is wrong because the data type of the clustered index does not impact PolyBase load performance. Option D is wrong because round-robin distribution can improve load speed, but the primary issue is resource allocation.

Answer

Create a columnstore index on the target table.

Answer

Use a clustered index on the target table.

Answer

Change the distribution of the target table to round-robin.

Question 11

You are developing a data pipeline in Azure Data Factory that ingests data from multiple on-premises SQL Server databases to Azure Data Lake Storage Gen2. The data volume is about 1 TB per day. You need to ensure the pipeline can handle the volume and provide monitoring and alerting. Which THREE components should you include?

Accepted Answer

Self-hosted integration runtime. Correct answers: B, C, and E. Self-hosted integration runtime (B) is required for connectivity to on-premises SQL Server databases. Azure Monitor (C) provides monitoring and alerting for the pipeline. Data flow activity (E) allows data transformation at scale. Option A is incorrect because Azure Synapse Analytics pipeline is not used in this context; the pipeline is in Azure Data Factory. Option D is incorrect because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 12

You are reviewing a mapping data flow in Azure Data Factory that reads a CSV file from ADLS Gen2 and writes to an Azure Synapse Analytics dedicated SQL pool. The data flow includes a Derived Column transformation with the expression: `column1 == "Error" ? toString(column1) : column1`. The pipeline fails with an error indicating that the sink table could not be created. What is the most likely cause?

Accepted Answer

The sink dataset is configured for JSON format.. In a mapping data flow, the sink dataset must match the destination format. For an Azure Synapse dedicated SQL pool, the dataset should be of type 'Azure Synapse Analytics' (table), not JSON. If the dataset is set to JSON, the pipeline will fail when trying to create the sink table because the format is incompatible. Option C is incorrect because allowCopyCommand is a feature of the Copy activity, not mapping data flows.

Answer

The source file does not have a header row.

Answer

The Derived Column expression has a syntax error.

Answer

Using allowCopyCommand with autoCreate is not supported.

Question 13

You are a data engineer working for a logistics company. You have an existing Azure Data Factory pipeline that ingests data from a REST API to Azure Data Lake Storage Gen2. The API has rate limiting that can cause failures. You need to implement a solution that can handle rate limiting by retrying with exponential backoff. The pipeline should also log the number of retries for each API call. What should you do?

Accepted Answer

Use a Web activity with a Until loop to implement custom retry logic.. Azure Data Factory Copy activity does not support exponential backoff; the retry interval is fixed. Using a Web activity inside an Until loop allows you to implement custom retry logic with exponential backoff by adjusting the wait time dynamically. This also enables logging the number of retries. Option A is incorrect because the Copy activity's retry uses a fixed interval, not exponential backoff. Option C is more complex than necessary. Option D introduces an additional service without leveraging ADF's orchestration capabilities.

Answer

Configure the Copy activity with retry policy using exponential backoff by setting the retry count and retry interval. Enable diagnostic logs to capture retry details.

Answer

Use an Azure Function as a custom activity in Azure Data Factory to implement retry logic with exponential backoff.

Answer

Use Azure Logic Apps to call the API and then copy the response to Azure Data Lake Storage Gen2.

Question 14

You are designing a data processing solution for a media company that uses Azure Synapse Analytics. The solution must process video metadata stored in Azure Cosmos DB and combine it with user interaction data from Azure Data Lake Storage Gen2. The combined data must be stored in a dedicated SQL pool for reporting. The data volume is moderate, and the processing should be done using T-SQL. Which approach should you use?

Accepted Answer

Use PolyBase to create external tables on Cosmos DB and ADLS Gen2, then use INSERT...SELECT to load into dedicated SQL pool.. PolyBase is correct because it allows T-SQL queries to access external data in Cosmos DB and ADLS Gen2 directly, and then INSERT...SELECT can load the combined data into a dedicated SQL pool table. This meets the requirement to use T-SQL and process moderate data volumes. Other options use Spark or Data Factory, which are not T-SQL based.

Answer

Use Azure Data Factory with Copy activities to load data from Cosmos DB and ADLS Gen2 into the dedicated SQL pool, then use a stored procedure to merge.

Answer

Use Azure Databricks to read from Cosmos DB and ADLS Gen2, transform with Spark SQL, and write to the dedicated SQL pool using JDBC.

Answer

Use the serverless SQL pool to create external tables on Cosmos DB and ADLS Gen2, then use CREATE EXTERNAL TABLE AS SELECT (CETAS) to write the combined data to a table in the dedicated SQL pool.

Question 15

You are developing a data processing pipeline in Azure Databricks that processes streaming data from Azure Event Hubs. You need to ensure that the pipeline can recover from failures and process data exactly once. The pipeline writes to Delta Lake. Which approach should you use?

Accepted Answer

Use Structured Streaming with a Delta Lake sink and specify a checkpoint location on Azure Data Lake Storage Gen2.. Using Structured Streaming with a Delta Lake sink and specifying a checkpoint location on Azure Data Lake Storage Gen2 enables exactly-once processing. Delta Lake's ACID transactions guarantee idempotent writes, and checkpointing stores stream offsets for recovery. Option A is incorrect because Azure Stream Analytics does not integrate natively with Delta Lake. Option B is incorrect because while foreachBatch can be used for custom processing, the direct Delta Lake sink with checkpointing is the recommended approach for exactly-once semantics. Option D is incorrect because Auto Loader is for batch ingestion from files, not streaming from Event Hubs.

Answer

Use Azure Stream Analytics to process the stream and output to Delta Lake via Azure Data Lake Storage Gen2.

Answer

Use Structured Streaming with foreachBatch to write micro-batches to Delta Lake, and set the checkpoint location to Azure Data Lake Storage Gen2.

Answer

Use Auto Loader to ingest streaming data from Event Hubs and write to Delta Lake with checkpointing.