DP-203 Practice Test 19 — 15 Questions

Question 1

You are designing a data processing solution that must handle personally identifiable information (PII). The data will be ingested from multiple sources into Azure Data Lake Storage. Which TWO actions should you take to protect the PII data during processing?

Accepted Answer

Use column-level security in Azure Synapse Analytics to restrict access to sensitive columns.. Options A and C are correct. Column-level security in Azure Synapse restricts access to sensitive columns. Azure Purview provides data classification and lineage. Option B (masking in Power BI) is a presentation-layer control. Option D (server-level firewall) is network security. Option E (always encrypted) is for SQL Server, not ADLS.

Answer

Apply data masking in Power BI reports to hide PII from end users.

Answer

Configure the Azure Data Lake Storage firewall to allow only specific IP addresses.

Answer

Implement SQL Server Always Encrypted for all data stored in the data lake.

Question 2

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

Accepted Answer

The copy activity is not using staging and PolyBase. Option D (The copy activity is not using staging and PolyBase) is correct because PolyBase can significantly improve load performance into Synapse. Option A (Data Integration Unit too low) is possible but less likely than using PolyBase. Option B (Source files are compressed) should not degrade performance if using compressed format. Option C (Source and sink in different region) is not the case.

Answer

The source and sink are in different Azure regions

Answer

The Data Integration Unit (DIU) setting is too low

Answer

The source files are compressed

Question 3

Which THREE actions improve the performance of a Delta table in Azure Databricks? (Choose three.)

Accepted Answer

Run VACUUM to remove old versions and compact small files. Options A, B, and E are correct. Z-ordering optimizes data layout, compaction reduces small files, and partitioning improves query pruning. Option C (Disable auto-optimize) would worsen performance. Option D (Increase spark.sql.shuffle.partitions) may help but is not specific to Delta tables.

Answer

Disable auto-optimize to reduce overhead

Answer

Increase the number of shuffle partitions

Question 4

You are building a batch processing solution in Azure Synapse Analytics that reads data from a dedicated SQL pool, applies complex transformations using Synapse Spark, and writes the results back to the dedicated SQL pool. The pipeline must run on a schedule and handle transient failures with retries. Which approach should you use?

Accepted Answer

Use Azure Synapse Pipelines with a Notebook activity that runs Spark code. Option B is correct because Azure Synapse Pipelines support scheduling, retry policies, and can orchestrate Notebook activities for Spark transformations. Option A is wrong because Azure Batch is not integrated with Synapse. Option C is wrong because Databricks is a separate service; using it would require additional integration. Option D is wrong because Azure Functions are for event-driven, short-lived tasks, not complex transformations.

Answer

Use Azure Batch with a custom application to run Spark jobs

Answer

Use Azure Functions to trigger Spark jobs on demand

Answer

Use Azure Databricks with Auto Loader and Delta Live Tables

Question 5

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Accepted Answer

Use ROUND_ROBIN distribution for the staging table. Options A and D are correct. Round-robin distribution ensures even data distribution during loads. Using CTAS with partition switching minimizes logging and fragmentation. Option B is wrong because smaller row group sizes increase columnstore segment count, reducing compression and query performance. Option C is wrong because creating indexes on all columns increases write overhead. Option E is wrong because enabling change tracking adds overhead.

Answer

Create nonclustered indexes on all columns of the target table

Answer

Set the row group size to 100,000 rows for optimal compression

Answer

Enable change tracking on the target table

Question 6

You are using Azure Stream Analytics to process real-time temperature data from IoT devices. The output must be written to Azure SQL Database. The job has been running successfully for weeks, but recently you notice that the output data has duplicate rows. The input events are unique. The job uses a windowed aggregation (TumblingWindow). What is the most likely cause of duplicates?

Accepted Answer

The job is being restarted and reprocessing data.. Option D is correct because when a Stream Analytics job recovers from a failure, it reprocesses input from the last checkpoint, which can cause duplicate output if the output is not idempotent. Option A is wrong because window size affects aggregation but not duplicates. Option B is wrong because late events can cause out-of-order results but not duplicates if handled correctly. Option C is wrong because the input is unique, so duplicates are not from input.

Answer

The job is not handling late-arriving events.

Answer

The input event hub is receiving duplicate events.

Answer

The tumbling window size is too small.

Question 7

You are designing a data ingestion pipeline for Azure Synapse Analytics using PolyBase. The source data is in Azure Blob Storage, with files in the Parquet format. The data volume is approximately 2 TB daily. You need to minimize the time to load data into a dedicated SQL pool table. Which approach should you recommend?

Accepted Answer

Create an external table pointing to the Parquet files and use CREATE TABLE AS SELECT (CTAS) to load the data.. Option B is correct because creating an external table with the Parquet file format and using CREATE TABLE AS SELECT (CTAS) allows PolyBase to leverage predicate pushdown and column pruning, which reduces data movement and improves performance. Option A is wrong because splitting files into smaller chunks increases the number of parallel reads but the overhead of many small files can degrade performance. Option C is wrong because converting to CSV adds overhead and increases data size. Option D is wrong because COPY INTO is optimized for small to medium loads, while CTAS with external tables is better for large-scale loads.

Answer

Split the Parquet files into many small files (e.g., 64 MB each) to maximize parallelism.

Answer

Convert the Parquet files to CSV format before loading using PolyBase.

Answer

Use the COPY INTO command to load the Parquet files directly into the dedicated SQL pool.

Question 8

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

Accepted Answer

Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.. Option C is correct because the temporal window functions (e.g., TumblingWindow) with a late arrival policy and exactly-once semantics are built into Stream Analytics. Option A is wrong because watermarks are a concept in Spark, not Stream Analytics. Option B is wrong because the Input Order policy in Stream Analytics allows handling late events, but exactly-once semantics are guaranteed by the combination of checkpointing and output adapters. Option D is wrong because the event ordering policy is for out-of-order events, not for late arrival.

Answer

Use a temporal window function with a 'late arrival' policy specified in the query.

Answer

Define a watermark in the query to specify a maximum out-of-order tolerance.

Answer

Set the event ordering policy to 'Adjust' to reorder events within a certain time window.

Question 9

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

Accepted Answer

Yes, because the integration runtime is self-hosted and can access on-premises data sources.. Option A is correct because a self-hosted integration runtime is designed for on-premises and VM-based data sources. Option B is wrong because managed VNet IR is for Azure-only sources. Option C is wrong because Azure IR is for cloud data sources. Option D is wrong because it is possible with a self-hosted IR.

Answer

No, you need an Azure integration runtime that is configured for on-premises access.

Answer

No, you need a managed virtual network integration runtime for on-premises sources.

Answer

Yes, but only if the integration runtime is installed on a domain-joined machine.

Question 10

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

Accepted Answer

The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version. The condition `acs:RequestVersion` requires the request to use a specific API version, which may not be met. Option A is wrong because the action is correct for reading containers. Option B is wrong because the resource path is correct. Option C is wrong because the principal is a managed identity.

Answer

The resource path is malformed; it should include the blob path

Answer

The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'

Answer

The principal is a managed identity, but the policy requires a user-assigned identity

Question 11

You are building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

Accepted Answer

Enable 'Incremental extraction' and specify the timestamp column for filtering.. Option C is correct because the 'Incremental extraction' option in a mapping data flow source transformation allows you to filter rows based on a last modified timestamp column, enabling incremental loads. Option A is wrong because 'Column pattern' is used for detecting changes in column names, not row-level timestamps. Option B is wrong because 'Row sampling' selects a random subset of rows, not based on timestamps. Option D is wrong because 'Source metadata' in the sink transformation is unrelated to incremental loading.

Answer

Use a 'Column pattern' to detect new columns and filter rows.

Answer

Configure 'Row sampling' settings to take only new rows.

Answer

Set 'Source' to 'New rows only' in the sink transformation.

Question 12

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 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

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Accepted Answer

Use a path pattern that includes {date} and {time} tokens.. Options A, D, and E are correct. Option A: Setting the output format to Avro or Parquet is common for partitioned data. Option D: Enabling 'DateTime format' in the output allows partitioning. Option E: Using a path pattern with {date} and {time} creates folder structure. Option B is wrong because partitioning is not part of event ordering. Option C is wrong because compatibility level does not affect output partitioning.

Answer

Configure the event ordering policy to adjust late events.

Answer

Set the compatibility level to 1.2 or higher.

Question 15

You are using Azure Data Factory to copy data from Azure Blob Storage to Azure SQL Database. The copy operation fails with error 'Cannot insert duplicate key'. What is the most likely cause and solution?

Accepted Answer

The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity. The error indicates duplicate rows violating a primary key or unique constraint. Enabling upsert behavior allows the copy activity to handle duplicates. Option A is wrong because source format doesn't cause duplicates. Option B is wrong because using staging doesn't fix duplicates. Option D is wrong because sink table schema is not the cause.

Answer

The source files are in an incompatible format; convert to CSV

Answer

The sink table schema does not match the source; update the schema

Answer

The copy activity is not using staging; enable staging