DP-203 Practice Test 22 — 15 Questions

Question 1

A manufacturing company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data arrives in JSON format with a nested structure. You need to transform the data into a tabular format for downstream analytics using Azure Synapse Pipelines. Which data flow transformation should you use?

Accepted Answer

Flatten transformation. Option B is correct because the Flatten transformation in mapping data flows unpacks nested arrays into rows. Option A is wrong because the Aggregate transformation groups data but does not flatten nested structures. Option C is wrong because the Pivot transformation rotates rows to columns. Option D is wrong because the Window transformation calculates aggregated values over a range of rows.

Answer

Aggregate transformation

Answer

Window transformation

Answer

Pivot transformation

Question 2

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

Accepted Answer

Use OPENROWSET with explicit file path filtering.. Option C is correct because the OPENROWSET function with file path filtering can prune partitions by specifying the path to specific folders. Option A is wrong because materialized views are not supported in serverless SQL pool. Option B is wrong because result-set caching is for dedicated SQL pool. Option D is wrong because workload management is for dedicated SQL pool.

Answer

Implement workload management to prioritize queries.

Answer

Enable result-set caching on the serverless SQL pool.

Answer

Create materialized views on the Parquet files.

Question 3

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

Accepted Answer

Azure Synapse Analytics dynamic data masking. Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs. Azure Data Lake Storage does not provide masking.

Answer

Azure API Management policies

Answer

Azure Data Lake Storage access control lists (ACLs)

Answer

Azure Purview classification and labeling

Question 4

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 5

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

Accepted Answer

Power BI with DirectQuery mode and configure automatic page refresh.. Option A is correct because Power BI with DirectQuery mode can refresh at sub-minute intervals and query the database directly. Option B (Azure Analysis Services) is for tabular models, not real-time dashboards. Option C (Azure Data Explorer) is optimized for large-scale analytics, not for direct connection to Azure SQL Database. Option D (Azure Synapse Serverless SQL) is for querying data lakes, not for real-time dashboards.

Answer

Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.

Answer

Azure Data Explorer (ADX) with a continuous export to Power BI.

Answer

Azure Synapse Serverless SQL pool with Power BI import mode.

Question 6

You are building a streaming pipeline in Azure Synapse Analytics to ingest real-time sensor data from IoT devices. The data must be processed with a 2-second latency and stored in a dedicated SQL pool for reporting. The source emits JSON messages with a nested structure. Which approach should you use to ingest and transform the data?

Accepted Answer

Use Azure Stream Analytics to ingest data from Azure Event Hubs, apply a query to flatten the JSON, and output directly to the dedicated SQL pool.. Option B is correct because Azure Stream Analytics can ingest from Event Hubs, flatten JSON in real-time, and write to Synapse SQL pool via the built-in output adapter. Option A (Databricks Auto Loader) is for batch/streaming but not optimal for sub-2-second latency and nested JSON flattening without additional code. Option C (Spark structured streaming) is possible but more complex. Option D (Azure Data Factory) is for batch/orchestration, not real-time streaming.

Answer

Use Azure Synapse Spark with structured streaming to read from Event Hubs, flatten the JSON using Spark SQL, and write to the dedicated SQL pool.

Answer

Use Azure Data Factory to run a tumbling window trigger that reads from Event Hubs every 2 seconds and copies data to the dedicated SQL pool.

Answer

Use Azure Databricks with Auto Loader to ingest data from Event Hubs and write to the dedicated SQL pool.

Question 7

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.. Option C is correct because Synapse Pipelines (built on ADF) can orchestrate mapping data flows that run on serverless Spark clusters, minimizing management overhead. Option A (HDInsight) requires cluster management. Option B (Databricks) incurs higher costs for simple batch. Option D (SSIS) is legacy and not cloud-native.

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 8

You are optimizing an Azure Synapse Analytics pipeline that uses mapping data flows. The pipeline runs slowly when processing 100 GB of data. Which THREE settings should you adjust to improve performance?

Accepted Answer

Set 'partition option' to 'Round robin' with a higher number of partitions.. Options A, C, and D are correct. Partitioning improves parallelism. Data flow debug is for development only. Optimize shuffle reduces data movement. Compute type and core count affect resource allocation. Option B (debug) is for testing. Option E (tumbling window) is for streaming.

Answer

Set a tumbling window trigger to run the pipeline every 5 minutes.

Answer

Enable 'Data flow debug' to monitor execution details.

Question 9

Your organization has an Azure Data Factory pipeline that executes a series of activities to transform data. One of the activities is an Azure Databricks notebook that should run only if the previous activity succeeds. You need to configure the pipeline to handle failures gracefully and send an email alert if the Databricks activity fails. What should you do?

Accepted Answer

Add a failure output path from the Databricks activity to a Web activity that calls an email API.. Option C is correct because you can add a failure path to the Databricks activity and attach a Web activity to send email. Option A is wrong because dependency conditions are on success, not failure. Option B is wrong because a failure policy on the activity does not send alerts. Option D is wrong because a Schedule trigger does not handle failures.

Answer

Configure a retry policy and a timeout for the Databricks activity.

Answer

Use a Schedule trigger to run the pipeline and check for failures using Azure Monitor.

Answer

Set a dependency condition on the Databricks activity to 'Succeeded' and add a Send Email activity on the success path.

Question 10

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

Accepted Answer

The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead. Option B is correct because materialize() only helps if the result is referenced multiple times; in this query, TotalSales is used only once, so materialize() adds overhead without benefit. Option A is wrong because the query is already using summarize. Option C is wrong because datetime filtering is fine. Option D is wrong because order by does not conflict with materialize.

Answer

The join with the Products table forces a shuffle that bypasses the materialized result

Answer

The query uses summarize, which already materializes results internally

Answer

The datetime range filter is not sargable, causing full table scan

Question 11

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Answer

Azure HDInsight with Hive

Answer

Azure Analysis Services

Answer

Azure Databricks with PySpark

Question 12

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.

Question 13

You need to transform semi-structured JSON data into a tabular format for analysis in Azure Synapse Analytics. The data is stored in ADLS Gen2. Which feature should you use to query the JSON data directly without loading it into a table?

Accepted Answer

Use the OPENROWSET function in a serverless SQL pool.. Option A is correct because OPENROWSET in Synapse serverless SQL can query JSON files directly. Option B (COPY INTO) loads data. Option C (PolyBase) requires external tables. Option D (Azure Data Factory) is an orchestration tool.

Answer

Use Azure Data Factory to flatten the JSON and store as Parquet.

Answer

Create an external table using PolyBase.

Answer

Use the COPY INTO command in a dedicated SQL pool.

Question 14

Your company uses Azure Data Factory to load data from an on-premises SQL Server database to Azure Synapse Analytics. The pipeline runs daily and the load volume is about 50 GB per run. Recently, the pipeline started failing with timeout errors during the copy activity. You need to ensure reliable data transfer. What should you do?

Accepted Answer

Use a self-hosted integration runtime and increase the copy activity timeout. Option B (Use a self-hosted integration runtime and increase the timeout) is correct because a self-hosted IR is required for on-premises sources, and increasing the timeout can handle larger volumes. Option A (Use Azure Integration Runtime) does not connect to on-premises. Option C (Use PolyBase) is for loading into Synapse but does not address the connectivity or timeout. Option D (Split the data into smaller chunks) might help but is not the primary solution; increasing timeout is more direct.

Answer

Use PolyBase to load data in parallel

Answer

Split the copy into multiple smaller activities

Answer

Switch to Azure Integration Runtime

Question 15

Which TWO components are required to set up a streaming data pipeline using Azure Synapse Analytics? (Select two.)

Accepted Answer

Azure Event Hubs. Option A is correct because Event Hubs is a common ingestion source for streaming. Option C is correct because Azure Synapse Pipelines (or the underlying Spark engine) can process streaming data. Option B is wrong because Data Factory is for batch. Option D is wrong because Analysis Services is for OLAP. Option E is wrong because Blob Storage is a destination, not a required component.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Blob Storage