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

Question 1

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 2

Refer to the exhibit. The pipeline fails because the source and sink datasets do not match. The source file is a CSV with columns: CustomerID, Name, City. The sink table dbo.Customer has columns: CustomerID, Name, City, CreatedDate (default GETDATE()). The pipeline uses auto-mapping. Why does the pipeline fail?

Accepted Answer

Auto-mapping fails because the source and sink have different column counts.. Option C is correct because the source has 3 columns and the sink has 4 columns. With auto-mapping, the copy activity tries to map all source columns to sink columns by name but fails because the sink has an extra column (CreatedDate) that is not nullable and has no default value in the mapping context (the default is a SQL Server default constraint, but auto-mapping expects an explicit mapping or a NULL). The pre-copy script truncates the table, but that does not resolve the column count mismatch. Option A is wrong because the pre-copy script is valid. Option B is wrong because recursive is fine for a single file. Option D is wrong because writeBatchSize is acceptable.

Answer

The recursive setting should be false for CSV files.

Answer

The writeBatchSize is too large for the sink.

Answer

The pre-copy script is not allowed for SQL sink.

Question 3

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

Accepted Answer

Derived Column transformation. Option B is correct because the Derived Column transformation allows you to create new columns using expressions, including string functions like split(). Option A is wrong because Aggregate is for grouping and aggregations, not string manipulation. Option C is wrong because Pivot rotates data from rows to columns. Option D is wrong because Lookup is for joining data from another source.

Answer

Pivot transformation

Answer

Aggregate transformation

Answer

Lookup transformation

Question 4

You are monitoring a production data pipeline in Azure Data Factory that runs hourly. You notice that the pipeline has been failing for the last 3 hours due to a timeout error when writing to the sink Azure SQL Database. The database is a General Purpose tier with 100 DTUs. The pipeline writes approximately 1 million rows per run. What is the most effective way to prevent the timeout?

Accepted Answer

Increase the writeBatchSize in the sink settings to reduce the number of batch inserts.. Option D is correct because increasing the batch size reduces the number of round trips, which can speed up the write operation and reduce timeout risk. Option A is wrong because reducing parallelism might increase duration. Option B is wrong because increasing DTUs helps but is more costly and may not be necessary. Option C is wrong because using a staging blob could add overhead and still timeout.

Answer

Reduce the degree of copy parallelism in the copy activity.

Answer

Enable staged copy using Azure Blob Storage as an interim store.

Answer

Scale up the Azure SQL Database to a higher DTU tier.

Question 5

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

Accepted Answer

Increase spark.sql.shuffle.partitions to 800.. Option B is correct because for a 200 GB input, 400 shuffle partitions may be too few, causing large partitions and slow shuffles. Increasing partitions to a higher number (e.g., 800 or more) can improve parallelism. Option A is wrong because executor size 'Small' might be insufficient; increasing executor size could help but the question asks about configuration change, and partition count is more directly related to shuffle performance. Option C is wrong because decreasing partitions would make partitions larger. Option D is wrong because executor count of 2 is low; increasing would help but the partition count is the key issue for shuffle.

Answer

Increase the number of executors to 4.

Answer

Change executor size to 'Large' to increase memory per executor.

Answer

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Question 6

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.

Question 7

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

Accepted Answer

Use Delta Lake's OPTIMIZE command to compact small files.. Options A and D are correct. Option A is correct because optimizing the Delta table with Z-order improves data skipping for columns used in queries. Option D is correct because using Delta Lake's optimize command (bin-packing) compacts small files, improving read performance. Option B is wrong because disabling automatic schema detection can be useful but is not required for performance. Option C is wrong because caching is optional and may not help for large datasets that don't fit in memory. Option E is wrong because enabling auto compaction helps but is not a requirement; it's a feature.

Answer

Disable automatic schema detection to reduce overhead.

Answer

Cache the entire DataFrame in memory after reading.

Answer

Enable auto-compaction in Spark configuration.

Question 8

You are building a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow to perform a lookup transformation. The lookup source is a dimension table with 10 million rows. You need to optimize the lookup performance. Which THREE actions should you take?

Accepted Answer

Enable the 'Broadcast' option on the lookup source transformation if the dimension table is less than 100 MB.. Options A, B, and E are correct. Option A is correct because broadcasting the lookup table (if small enough) avoids shuffling. Option B is correct because indexing the lookup columns speeds up the lookup. Option C is wrong because increasing batch size in a lookup does not directly improve performance; it's for sink. Option D is wrong because partitioning the lookup table on the join key can help, but the question is about the data flow, not the table design. Option E is correct because narrowing the columns reduces data transfer.

Answer

Increase the batch size in the lookup transformation settings.

Answer

Partition the dimension table on the lookup key before reading.

Question 9

You are implementing a data processing solution using Azure Data Factory. You have a pipeline that copies data from Azure Blob Storage to Azure Data Lake Storage Gen2. You need to ensure that the copy activity uses managed identity for authentication and that the data is transferred securely. Which TWO configurations should you apply?

Accepted Answer

Configure the source and sink linked services to use managed identity authentication.. Options B and D are correct. Option B is correct because managed identity authentication is configured in the linked service. Option D is correct because setting integration runtime to 'AutoResolveIntegrationRuntime' uses Azure's network, ensuring data transfer remains within Azure and does not go over public internet (if using managed VNet). Option A is wrong because the copy activity does not have a 'UseManagedIdentity' setting; it's in the linked service. Option C is wrong because HTTPS is already used by default; setting it explicitly is not required. Option E is wrong because staging is not needed for security.

Answer

Set the copy activity's 'enableDataIntegrityValidation' to true.

Answer

Set the copy activity's 'UseManagedIdentity' property to true.

Answer

Enable staged copy with a temporary blob store to improve security.

Question 10

Refer to the exhibit. You deploy this ARM template to create a managed integration runtime in Azure Synapse Analytics. You notice that the integration runtime shows as 'Running' but copy activities using it are slow. The data volume is 500 GB per run. What is the most likely cause of the poor performance?

Accepted Answer

The node size (Standard_D3_v2) is too small for the data volume.. Option B is correct because the node size Standard_D3_v2 has limited memory and CPU for large data volumes. A larger node size (e.g., Standard_D8_v3) would improve performance. Option A is wrong because 2 nodes is reasonable; increasing nodes could help but the node size is more critical. Option C is wrong because the location 'AutoResolve' is fine. Option D is wrong because the integration runtime type 'Managed' is appropriate for Azure-based copy.

Answer

The integration runtime type should be 'Self-hosted' for better performance.

Answer

The number of nodes (2) is insufficient for 500 GB of data.

Answer

The location is set to 'AutoResolve' which may cause data movement across regions.

Question 11

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. You have Parquet files in Azure Data Lake Storage Gen2 partitioned by date and hour. You need to query the data for the last 7 days efficiently, minimizing the amount of data scanned. Which approach should you use?

Accepted Answer

Use OPENROWSET with a file path that limits to the last 7 days' partitions and use a WHERE clause on the date column.. Option D is correct because using OPENROWSET with a file path that restricts to the last 7 days' partitions (e.g., '/date=2025-03-*/hour=*/*.parquet') minimizes data scanned by only reading the relevant Parquet files. Applying a WHERE clause on the date column further filters rows within those files, leveraging partition pruning at the file system level. This approach avoids scanning all partitions, which is critical for cost and performance in serverless SQL pool.

Answer

Use OPENROWSET with a wildcard path that includes all partitions and then apply a WHERE clause.

Answer

Create an external table with partition definition and query with a WHERE clause on the partition column.

Answer

Create a view using PolyBase external table and query with a date filter.

Question 12

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 13

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

Accepted Answer

Enable PolyBase in the Copy activity sink settings.. Option D is correct because enabling PolyBase in the Copy activity uses the fast PolyBase loading path, which can significantly accelerate loading into dedicated SQL pool. Option A (increasing DIU) helps but is limited by the sink. Option B (increasing DWU) scales the warehouse but does not change the copy method. Option C (partitioning the table) might help at query time but not during initial load.

Answer

Increase the Data Integration Units (DIU) in the Copy activity to the maximum.

Answer

Increase the dedicated SQL pool's DWU setting to the highest tier.

Answer

Partition the staging table on a date column.

Question 14

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 15

You are designing a data processing solution for a financial services company. They need to process sensitive customer data in Azure Databricks while complying with GDPR. The data must be encrypted at rest and in transit, and access must be audited. You need to recommend a configuration that meets these requirements. Which combination of actions should you take?

Accepted Answer

Enable double encryption using Azure Key Vault with customer-managed keys and enable Azure Databricks audit logs.. Option B is correct because enabling double encryption and using Azure Key Vault for encryption keys ensures encryption at rest and in transit; Azure Databricks audit logs capture access. Option A (Azure Storage Service Encryption) encrypts at rest but not necessarily with customer-managed keys. Option C (Azure Policy) enforces compliance but does not directly encrypt data. Option D (Azure Security Center) provides monitoring but not encryption.

Answer

Enable Azure Storage Service Encryption (SSE) with platform-managed keys and configure Azure Databricks to use a VNet injection.

Answer

Apply an Azure Policy to require encryption and assign a built-in GDPR blueprint.

Answer

Use Azure Security Center to monitor for data exposure and enable Azure Defender for Storage.