DP-203 Practice Test 9 — 10 Questions

Question 1

Match each Azure Synapse Analytics component to its function.

Accepted Answer

Dedicated SQL pool: Provisioned compute resources for high-performance querying of relational data.. In Azure Synapse Analytics, Dedicated SQL pool provides provisioned compute for relational data, Serverless SQL pool queries data lake files on-demand, Apache Spark pool handles distributed processing with Spark, and Pipeline orchestrates workflows. Common confusions include mixing serverless and provisioned capabilities or attributing Spark functionality to SQL pools.

Answer

Dedicated SQL pool: Serverless querying of data in data lake.

Answer

Serverless SQL pool: Managed Spark clusters for big data analytics.

Question 2

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. 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 Window transformation calculates aggregated values over a range of rows. Option D is wrong because the Pivot transformation rotates rows to columns.

Answer

Aggregate transformation

Answer

Window transformation

Answer

Pivot transformation

Question 3

Which TWO options are correct for configuring a copy activity in Azure Data Factory to load data from Azure Blob Storage to Azure SQL Database?

Accepted Answer

Use a staging table in Azure SQL Database before inserting into the final table.. Correct options are A and B. Option A (using a staging table in Azure SQL Database before inserting into the final table) is a best practice for large data loads, enabling efficient truncate-and-reload or upsert operations. Option B (using staging via Azure Blob Storage when loading large volumes) improves performance by batching and parallelizing the load, reducing timeouts and resource contention. Option D is incorrect because PolyBase is not supported for Azure SQL Database; it is available only for Azure Synapse Analytics and SQL Server. Option C is incorrect because Azure Data Lake Storage is not the standard staging location for Azure SQL Database; Azure Blob Storage is used. Option E is incorrect because skipping staging can degrade performance for large data volumes.

Answer

Use Azure Data Lake Storage as the staging location for better throughput.

Answer

Use PolyBase to load directly from Blob Storage to Azure SQL Database.

Answer

Always skip staging to reduce latency.

Question 4

Your organization uses Azure Data Lake Storage Gen2 for a data lake. You need to prevent accidental deletion of data by enabling a soft delete policy. Which configuration is required?

Accepted Answer

Enable blob soft delete on the storage account.. Azure Data Lake Storage Gen2 supports blob soft delete, which protects against accidental deletion by retaining deleted blobs for a specified retention period. Option A is incorrect because Azure Resource Manager locks prevent the deletion or modification of the storage account itself, not the data within it. Option B is incorrect because Azure Backup is designed for backing up VMs, SQL databases, and other workloads, not for managing soft delete of blobs. Option C is incorrect because blob versioning preserves previous versions of blobs, but it does not prevent deletion of the current version; soft delete is specifically for recovery from accidental deletion.

Answer

Apply an Azure Resource Manager lock.

Answer

Configure Azure Backup for the storage account.

Answer

Enable blob versioning.

Question 5

A company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. They notice that queries are slow and want to improve performance by reducing the amount of data read. What is the most effective strategy?

Accepted Answer

Partition the data by a frequently filtered column and use file elimination in queries.. Partitioning the data in the lake and using partition elimination reduces data read. Option B is wrong because increasing the number of compute nodes is not possible in serverless; it's auto-scaling. Option C is wrong because OPENROWSET with CSV reads all files; it does not reduce data read. Option D is wrong because CETAS is for creating external tables, not for improving query performance directly.

Answer

Increase the number of compute nodes in the serverless pool.

Answer

Use OPENROWSET with CSV format instead of Parquet.

Answer

Create external tables using CETAS and query them.

Question 6

Your company uses Azure Data Lake Storage Gen2 as the central data lake. You need to process batch data using serverless Spark jobs that can be scheduled daily. Which Azure service should you use?

Accepted Answer

Azure Synapse Analytics serverless Spark pool with pipelines.. Azure Synapse Analytics provides serverless Spark pools with built-in scheduling via pipelines, allowing you to run daily batch jobs without managing clusters. Option A (Azure Batch) is for custom compute workloads, not Spark jobs. Option C (Azure Databricks) requires a job cluster that is not serverless. Option D (Azure Machine Learning) is designed for ML workflows, not general batch data processing.

Answer

Azure Batch with custom Spark containers.

Answer

Azure Databricks with a job cluster.

Answer

Azure Machine Learning with Spark compute.

Question 7

You are using Azure Stream Analytics to process real-time data from an event hub and output to Azure Synapse Analytics. You need to ensure exactly-once delivery semantics to the output. What should you configure?

Accepted Answer

Set the output to 'Exactly Once' delivery mode.. Azure Stream Analytics supports exactly-once delivery to Azure Synapse Analytics by configuring the output to 'Exactly Once' delivery mode. Option A is incorrect because the input consumption mode does not control output delivery guarantees. Option B is incorrect because event ordering and late arrival policies handle timing, not delivery semantics. Option C is incorrect because checkpointing is for managing internal query state, not for ensuring exactly-once output.

Answer

Set the input to 'Exactly Once' consumption mode.

Answer

Configure event ordering and late arrival policies.

Answer

Enable checkpointing in the query.

Question 8

Refer to the exhibit. You are reviewing an ARM template for an Azure Data Lake Storage Gen2 account. Which of the following security best practices is violated in this template?

Accepted Answer

The account allows HTTP traffic and uses an outdated TLS version. The template sets supportsHttpsTrafficOnly to false, allowing HTTP traffic, which is insecure. Additionally, minimumTlsVersion is set to TLS1_0, an outdated and insecure version. Option A is incorrect because the location uses resourceGroup().location, which is a common and acceptable practice for flexibility. Option B is incorrect because Standard_LRS is a valid SKU; Geo-redundant storage is not a security best practice requirement. Option C is incorrect because the template does enable hierarchical namespace (isHnsEnabled: true), so that is not a violation.

Answer

The location should be fixed instead of using resourceGroup().location

Answer

The SKU should be Standard_GRS for disaster recovery

Answer

The account does not enable hierarchical namespace (HNS)

Question 9

You are building a data processing solution using Azure Databricks. The solution must process streaming data from Azure Event Hubs, join it with a static reference table stored in Azure Data Lake Storage Gen2 (Parquet format), and write the output to Azure Synapse Analytics. The reference table is updated daily. Which approach minimizes latency and ensures data consistency?

Accepted Answer

Use Spark Structured Streaming with a streaming join and cache the reference table as a static DataFrame.. Using Spark Structured Streaming with a streaming join and caching the reference table as a static DataFrame minimizes latency and ensures consistency by reading the reference table once and caching it for the duration of the stream. This avoids reloading the reference table in each micro-batch (as in option C) and avoids the latency of batch processing (as in option D). Option B, foreachBatch, is useful for writing to sinks like Synapse but does not address the join performance or consistency of the reference data.

Answer

Use Spark Structured Streaming with foreachBatch to write to Synapse.

Answer

Use Spark Structured Streaming with a streaming join and load the reference table in each micro-batch.

Answer

Use a batch job that runs every hour to process the data.

Question 10

You have an Azure Stream Analytics job that writes output to Azure Synapse Analytics. You need to ensure that the job can authenticate to Synapse Analytics using a managed identity. What should you do?

Accepted Answer

Enable system-assigned managed identity on the Stream Analytics job and configure the output to use it.. To authenticate to Azure Synapse Analytics using a managed identity, you should enable the system-assigned managed identity on the Stream Analytics job (it is enabled by default) and then configure the output to use managed identity authentication. Option A is correct. Option B is incorrect because a SAS token is not a managed identity. Option C is incorrect because while a user-assigned managed identity could be used, the system-assigned managed identity is sufficient and is the simpler option. Option D is incorrect because SQL Server authentication does not use managed identity and is less secure than managed identity authentication.

Answer

Generate a shared access signature (SAS) token for the Synapse Analytics workspace.

Answer

Create a user-assigned managed identity and assign it to the Stream Analytics job.

Answer

Configure the output to use SQL Server authentication with a username and password.