DP-203 Practice Test 22 — 15 Questions

Question 1

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?

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

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 2

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.. To optimize lookup performance in a mapping data flow, the recommended actions are: C. Enable the 'Broadcast' option on the lookup source if the dimension table is less than 100 MB. This avoids shuffling the large fact table across nodes. D. Select only the necessary columns in the lookup source transformation to reduce data transfer and memory usage. E. Ensure the dimension table has an index on the columns used for the lookup to speed up the join operation. Options A and B are not optimal: Increasing batch size (A) does not improve lookup performance; it affects sink writes. Partitioning the dimension table on the lookup key before reading (B) can be helpful, but in a data flow, partitioning is typically applied to the source or within the data flow itself, and it is not one of the top three recommended actions for lookup optimization in Synapse mapping data flows.

Answer

Increase the batch size in the lookup transformation settings.

Answer

Partition the dimension table on the lookup key before reading.

Question 3

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

Accepted Answer

Azure Synapse Pipelines with mapping data flows.. Azure Synapse Pipelines with mapping data flows provide a serverless, code-free transformation service that runs on managed Spark clusters, minimizing management overhead and costs. Mapping data flows can directly read Parquet from ADLS Gen2, perform cleaning and aggregation, and load into Azure Synapse SQL pool without requiring cluster management. Option A (Azure HDInsight with Spark) requires manual cluster provisioning and management, increasing operational overhead. Option C (custom SSIS package) is legacy, not cloud-native, and requires an integration runtime for execution. Option D (Azure Databricks with Auto Loader) provides powerful stream and batch processing but incurs higher costs for a simple batch job due to cluster management and DBU consumption.

Answer

Azure HDInsight with Spark jobs scheduled in Azure Data Factory.

Answer

Azure Data Factory with a custom SSIS package.

Answer

Azure Databricks with an Auto Loader pipeline.

Question 4

You are designing a data ingestion pipeline for Azure Data Lake Storage Gen2 using Azure Databricks. The source is an on-premises SQL Server database with incremental changes captured via change data capture (CDC). The requirement is to ensure exactly-once semantics for each row while minimizing latency. Which approach should you recommend?

Accepted Answer

Use Spark Structured Streaming in Azure Databricks to read CDC changes and write to Delta Lake.. Spark Structured Streaming in Azure Databricks, combined with Delta Lake, provides exactly-once semantics through checkpointing and transaction logs, and it is designed for low-latency streaming ingestion—ideal for CDC data. Option A is incorrect: Azure Data Factory with a tumbling window is a batch-based approach and cannot achieve exactly-once semantics for streaming data with low latency. Option B is incorrect: PolyBase is used for bulk loading data into Azure Synapse, not for streaming CDC ingestion. Option C is incorrect: Auto Loader and COPY INTO are designed for incremental batch loads of files, not for streaming row-level changes from a database.

Answer

Use Azure Data Factory with a tumbling window trigger to copy data every 5 minutes.

Answer

Use PolyBase to create external tables and run T-SQL MERGE statements.

Answer

Use Azure Databricks Auto Loader with COPY INTO command.

Question 5

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Accepted Answer

Hierarchical namespace. Azure Data Lake Storage Gen2 (ADLS Gen2) extends Azure Blob Storage by adding a hierarchical namespace, which organizes objects into a directory structure similar to a file system. This enables efficient directory-level operations (e.g., renaming or deleting a directory in O(1) time) and supports POSIX-compliant access control lists (ACLs) for fine-grained permissions. These two features are not available in standard Azure Blob Storage, which uses a flat namespace and only supports container-level access policies.

Answer

Immutable storage

Answer

Soft delete for blobs

Answer

Lifecycle management policies

Question 6

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

Accepted Answer

Change the distribution to hash on the column that is most frequently used in joins.. Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling. Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Answer

Keep round-robin distribution but increase the degree of parallelism.

Answer

Change the distribution to hash on multiple columns.

Answer

Rebuild the table as a heap to improve insert performance.

Question 7

You are developing a data processing pipeline in Azure Synapse Analytics that uses a SQL script to transform data in a dedicated SQL pool. The pipeline currently runs in the built-in serverless pool, but you want to migrate it to a dedicated SQL pool for better performance. What must you change in the pipeline?

Accepted Answer

Modify the SQL script to use T-SQL compatible with dedicated SQL pool.. Dedicated SQL pool uses T-SQL that has specific syntax differences from the serverless SQL pool used in Azure Synapse Analytics. When migrating from serverless to dedicated, you need to modify your SQL script to be compatible with dedicated SQL pool's T-SQL (e.g., different supported functions, data types, and performance features). Option A (changing the linked service) might also be necessary as part of the migration but is not the primary change needed to ensure the script runs correctly. Option B (replacing with Mapping Data Flow) is not required; the script itself can still be used. Option C (self-hosted IR) is not relevant for SQL script activities in Synapse pipelines.

Answer

Change the linked service from serverless to dedicated SQL pool.

Answer

Replace the SQL script with a Mapping Data Flow activity.

Answer

Update the integration runtime to a self-hosted IR.

Question 8

Which THREE factors should you consider when designing a real-time streaming solution using Azure Stream Analytics to process IoT data from thousands of devices?

Accepted Answer

The batch size for output to Azure Synapse Analytics to minimize write transactions.. Batching output to Azure Synapse Analytics reduces write transactions and improves cost efficiency. Option C is correct because windowing functions and late arrival policies are essential for handling out-of-order events in real-time streaming. Option E is correct because streaming units and partitioning determine throughput and scalability for processing IoT data. Option A is incorrect because reference data that changes every few seconds would require frequent reloads, defeating the purpose of static reference data in Stream Analytics. Option D is incorrect because latency requirements for Cosmos DB are not a primary design factor; Stream Analytics can write to Cosmos DB, but the main considerations are throughput and consistency, not latency.

Answer

The need to join input data with reference data that changes every few seconds.

Answer

The latency requirements for writing output to Azure Cosmos DB for NoSQL.

Question 9

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.

Question 10

You are designing a data processing solution for an e-commerce company. The company receives millions of clickstream events per hour from their website and needs to aggregate the data by product category and windowed time intervals for real-time dashboards. You need to minimize latency and cost. Which service should you use?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is the best choice because it is purpose-built for real-time stream processing, supports windowed aggregations, and integrates with Power BI for dashboards. Option A (Azure Databricks Structured Streaming) can handle streaming but is more complex and typically more expensive for simple aggregations. Option B (Azure Data Factory) is for batch data movement, not real-time. Option D (Azure Synapse Pipelines) is for orchestrating data movement, not real-time processing.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 11

Which TWO actions should you take to secure access to Azure Data Lake Storage Gen2 when using Azure Synapse Analytics serverless SQL pools? (Choose two.)

Accepted Answer

Enable firewall rules and virtual network service endpoints for the storage account.. To secure access to Azure Data Lake Storage Gen2 from Azure Synapse Analytics serverless SQL pools, two effective actions are: (A) Enable firewall rules and virtual network service endpoints to restrict network access to the storage account, and (B) Use managed identity authentication from the serverless SQL pool to the storage account, which eliminates the need to store credentials and provides secure, Azure AD-based authentication. Option C (service principal with client secret) is less secure than managed identity because it requires managing secrets. Option D (shared access signature token) is not recommended for long-term access as tokens can be leaked and need to be rotated. Option E (Azure AD passthrough authentication) is not supported for serverless SQL pools; it is only available for dedicated SQL pools.

Answer

Use a service principal with a client secret to access the storage account.

Answer

Generate a shared access signature (SAS) token for the storage account and store it securely.

Answer

Use Azure Active Directory (Azure AD) passthrough authentication for the serverless pool.

Question 12

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use replicated tables for small dimension tables. Correct: C and D. Replicated tables are optimal for small dimension tables because they avoid data movement during joins. Materialized views pre-compute and store aggregation results, improving query performance for common aggregations. A is incorrect because hash distribution on a low-cardinality column can lead to data skew and uneven distribution. B is incorrect because round-robin distribution is generally used for staging tables, not fact tables in a star schema. E is incorrect because increasing DWU is a scaling action, not a query optimization design choice.

Answer

Use hash distribution on a low-cardinality column

Answer

Use round-robin distribution for fact tables

Answer

Increase the DWU setting after every query

Question 13

Which TWO monitoring metrics in Azure Monitor for Azure Synapse Analytics dedicated SQL pool can help identify performance bottlenecks? (Choose two.)

Accepted Answer

Queued queries. Queued queries (B) indicate concurrency bottlenecks when queries are waiting for resources, while data movement (shuffle) metrics (D) help identify performance issues caused by data redistribution during query execution. CPU percentage (A) is a general resource metric but not a specific bottleneck indicator for dedicated SQL pool. Storage used (C) relates to capacity planning, not performance bottlenecks. DWU used (E) shows overall resource consumption but does not pinpoint specific bottlenecks.

Answer

CPU percentage

Answer

Storage used

Answer

DWU used

Question 14

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 15

Which THREE actions can improve the performance of a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Partition large fact tables on a date column.. Options B, D, and E are correct. Partitioning large fact tables on a date column allows partition elimination, improving query performance. Using replicated tables for small dimension tables avoids data movement during joins. Enabling result-set caching stores query results for reuse, reducing compute load. Option A is incorrect because rowstore indexes are not optimal for analytics; columnstore indexes are preferred. Option C is incorrect because round-robin distribution is not suitable for all tables and can cause excessive data shuffling.

Answer

Use rowstore indexes instead of columnstore indexes.

Answer

Use round-robin distribution for all tables.