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

Question 1

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.. Options A, C, and E are correct. Exactly-once semantics require a unique identifier for deduplication, a watermark to handle late events, and output to a table with a primary key for upsert. Option B is incorrect because late arrival tolerance is not required for exactly-once. Option D is incorrect because batch mode is not supported for exactly-once output to Synapse.

Answer

Configure the output to use batch mode for writing.

Answer

Set the late arrival tolerance window to zero.

Question 2

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. Options B and C are correct. Azure Synapse Serverless SQL can create external tables over Data Lake storage and apply dynamic data masking. Azure Purview can classify sensitive columns for policy. Option A is incorrect because ADLS does not have built-in masking. Option D is incorrect because Azure SQL Database is a different store. Option E is incorrect because Azure Policy does not mask data.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies

Question 3

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 partitionBy('hash', 1) setting causes some rows to be dropped due to partitioning logic.. Option A is correct because the sink script has 'partitionBy('hash', 1)' which forces the data to be repartitioned into a single partition. Since the source reads 1000 rows and the repartition may cause some rows to be dropped due to hash distribution logic in the sink. However, the key point is that the sink's partitioning overrides the source partitioning and may lead to data loss if there is a mismatch. Actually, the correct cause is that the sink is using hash partitioning on a column that may have many nulls or the partition column is not specified, causing skewed distribution. But the most direct answer is that the sink's partitionBy('hash', 1) creates a single partition and the hash function might drop rows that hash to a non-existent partition? No, hash with 1 partition should include all rows. Another possibility: the source has rowLimit 1000, but sink's partitionBy may cause duplicates? Let's think. The exhibit shows partitionBy('hash', 1). In ADF, when you use hash partitioning with 1 partition, all rows go to that partition, so no rows should be lost. However, the sink has allowSchemaDrift true, but no transformations. The discrepancy might be due to the source rowLimit being applied after reading? Actually rowLimit is applied at source. The most plausible cause is that the sink uses a Delta dataset and the Delta write operation may skip rows with nulls in partition columns? Option A mentions the sink's partitioning causes some rows to be dropped. Option B mentions allowSchemaDrift causing schema mismatch; but schema is matched. Option C mentions source rowLimit being applied before transformations; there are no transformations. Option D mentions missing transformation; not needed. The correct answer is A because in some cases, if the partition column is not in the source, the hash function might fail. But the script shows no partition column specified. In ADF, if you use partitionBy('hash', 1) without specifying a column, it uses a random hash? Actually it requires a column. The script is incomplete. Given the context, Option A is the best answer.

Answer

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

Answer

The sink's allowSchemaDrift setting prevents writing rows with additional columns.

Answer

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

Question 4

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

A data engineering team is building a batch processing solution for a financial services company. Data is ingested daily from multiple sources into Azure Data Lake Storage Gen2 in CSV format. The data must be transformed (filtered, aggregated, joined) and loaded into Azure Synapse Analytics dedicated SQL pool. The team must optimize for cost and performance. The total data volume is 2 TB per day. The team has the following options:

Option A: Use Azure Data Factory pipelines with copy activity to load raw CSV files into Synapse staging tables, then use T-SQL stored procedures in Synapse to perform transformations.

Option B: Use Azure Databricks with Auto Loader to incrementally ingest CSV files, perform transformations in Spark, and write the results to Synapse using the Spark Synapse connector.

Option C: Use Azure Data Factory with mapping data flows to transform the data in a serverless environment and then write to Synapse.

Option D: Use Azure Synapse Pipelines (built on ADF) with a notebook activity that runs a PySpark notebook in Synapse Spark pool to transform and load data.

Which option should the team choose to minimize cost and management overhead while meeting performance requirements?

Accepted Answer

Option C. Option C is correct because mapping data flows run on a serverless ADF integration runtime, which scales automatically and incurs cost only per execution, minimizing management overhead. Option A requires managing staging tables and stored procedures. Option B requires managing Spark clusters. Option D requires managing Synapse Spark pools. Mapping data flows are cost-effective for batch transformations of this size.

Answer

Option B

Answer

Option A

Answer

Option D

Question 6

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 7

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.. Option C is correct because 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 round-robin distribution can improve load speed, but the primary issue is resource allocation. Option D is wrong because the data type of the clustered index does not impact PolyBase load performance.

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 8

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Accepted Answer

Delta Lake. Correct answers: A and D. Delta Lake provides ACID transactions and schema enforcement, enabling exactly-once semantics. Azure Databricks Structured Streaming can handle late-arriving data using watermarking and event-time processing. Option B is wrong because Azure Stream Analytics does not integrate natively with Delta Lake for exactly-once semantics. Option C is wrong because Azure Data Factory is not suitable for streaming processing. Option E is wrong because PolyBase is for batch loading, not streaming.

Answer

PolyBase

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 9

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: A, C, and E. Self-hosted integration runtime is required for on-premises connectivity. Azure Monitor provides monitoring and alerting. Data flow can be used for data transformation at scale. Option B is wrong because Azure Synapse Analytics is not used in this pipeline. Option D is wrong because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 10

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

Using allowCopyCommand with autoCreate is not supported.. Option B is correct because `allowCopyCommand` is set to `true`, which requires the sink table to already exist. The `tableOption` is `autoCreate`, but with `allowCopyCommand` enabled, auto-create is not supported. Option A is wrong because the expression is syntactically correct. Option C is wrong because the issue is with sink, not source. Option D is wrong because the source is delimited text, not JSON.

Answer

The source file does not have a header row.

Answer

The Derived Column expression has a syntax error.

Answer

The sink dataset is configured for JSON format.

Question 11

You are a data engineer for a healthcare company that processes patient data. You have an Azure Databricks workspace with a cluster configured for data processing. You need to implement a solution that processes streaming data from Azure Event Hubs, enriches it with reference data stored in Azure Cosmos DB, and writes the output to Delta Lake in Azure Data Lake Storage Gen2. The solution must ensure that the data processing is fault-tolerant and can handle schema evolution. The reference data is updated infrequently. You need to choose an approach that minimizes complexity and cost. What should you do?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use a streaming static join to enrich with reference data from Cosmos DB, and write to Delta Lake. Enable schema evolution on the Delta table.. Option B is correct because Auto Loader can incrementally process streaming data from Event Hubs (via Event Hubs connector), and it can automatically handle schema evolution. Broadening the reference data join to use a streaming static join in Structured Streaming is efficient for infrequently updated reference data. Option A is wrong because using Change Data Capture adds complexity. Option C is wrong because using Azure Stream Analytics would require additional service cost and integration. Option D is wrong because using batch processing for streaming data would increase latency.

Answer

Use Azure Databricks Auto Loader with Delta Live Tables to ingest streaming data, and use Change Data Capture from Cosmos DB to update the reference data inline.

Answer

Use Azure Data Factory to copy data from Event Hubs to Azure Data Lake Storage Gen2 in batches, then use Azure Databricks to process and enrich with Cosmos DB.

Answer

Use Azure Stream Analytics to ingest from Event Hubs, join with Cosmos DB reference data, and output to Azure Data Lake Storage Gen2 in Parquet format.

Question 12

You are designing a data processing solution for an e-commerce company that uses Azure Synapse Analytics. The solution must process clickstream data from a web application. The data arrives in JSON format through Azure Event Hubs. You need to load the data into a dedicated SQL pool every 5 minutes with minimal latency. The data volume is about 100 MB every 5 minutes. You want to use PolyBase for loading. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to copy data from Event Hubs to Azure Data Lake Storage Gen2 as JSON files, then use a PolyBase activity to load from ADLS Gen2 to the dedicated SQL pool.. Option A is correct because using Azure Data Factory to copy JSON data to ADLS Gen2 as a staging area, then using PolyBase to load into dedicated SQL pool, is efficient for small frequent loads. Option B is wrong because loading directly from Event Hubs using PolyBase is not supported. Option C is wrong because using Azure Stream Analytics adds unnecessary complexity. Option D is wrong because using Azure Databricks would require a cluster running continuously, increasing cost.

Answer

Use Azure Stream Analytics to transform the JSON data and output directly to the dedicated SQL pool.

Answer

Use Azure Databricks to read from Event Hubs, transform the data, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase directly from Event Hubs to dedicated SQL pool by creating an external data source that points to Event Hubs.

Question 13

You are a data engineer at a financial services company. You are developing a data processing pipeline that uses Azure Data Factory to copy transactional data from an Azure SQL Database to Azure Data Lake Storage Gen2. The pipeline runs daily and processes about 10 GB of data. You need to implement error handling for the pipeline. Specifically, if the copy activity fails due to a transient error, the pipeline should retry automatically. If the retry fails, the pipeline should log the error and send an email alert to the operations team. What should you do?

Accepted Answer

Configure the copy activity with retry policy (retry count = 2, retry interval = 30 seconds). Add a failure path to a web activity that calls an Azure Logic App to send an email.. Option A is correct because Azure Data Factory allows you to set retry count and retry interval on the activity, and you can add a web activity to send an email via Azure Logic Apps after failure. Option B is wrong because the pipeline-level retry would not allow activity-specific settings. Option C is wrong because Azure Monitor alerts would be reactive, not integrated into the pipeline. Option D is wrong because Azure Functions would require additional coding and setup.

Answer

Use Azure Functions to implement custom retry logic and send email.

Answer

Create an Azure Monitor alert for failed pipeline runs and configure an action group to send an email.

Answer

Set the pipeline retry to 2 and add a storage event trigger on the error file.

Question 14

You are designing a data lakehouse architecture in Azure using Delta Lake. The solution needs to process batch and streaming data from multiple sources, including IoT devices and CRM systems. You need to ensure data quality by enforcing schema validation and handling schema evolution. You also need to provide a unified catalog for querying. Which service should you use?

Accepted Answer

Azure Databricks Unity Catalog. Option C is correct because Azure Databricks Unity Catalog provides a unified governance solution for data and AI, including schema enforcement and evolution for Delta Lake. Option A is wrong because Azure Purview is for data discovery and lineage, not for schema enforcement. Option B is wrong because Azure Synapse Analytics is a query engine but does not provide the same schema management features as Unity Catalog. Option D is wrong because Azure Data Lake Storage is storage only.

Answer

Azure Purview

Answer

Azure Data Lake Storage Gen2

Answer

Azure Synapse Analytics serverless SQL pool

Question 15

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

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.. Option B is correct because Azure Data Factory's Copy activity has built-in support for retry with exponential backoff when you set the retry count and retry interval. You can also log retry attempts using diagnostic logs. Option A is wrong because Azure Logic Apps would be a separate service and add complexity. Option C is wrong because Azure Functions would require custom development. Option D is wrong because the Web activity does not have built-in retry with exponential backoff.

Answer

Use a Web activity with a Until loop to implement custom retry logic.

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.