DP-203 Practice Test 29 — 15 Questions

Question 1

Drag and drop the steps to implement Slowly Changing Dimension (SCD) Type 2 in Azure Synapse Analytics dedicated SQL pool into the correct order.

Accepted Answer

Stage data, then identify changes, then expire old records, then insert new versions.. SCD Type 2: stage data, merge to find changes, expire old records, insert new versions, and update attributes (if needed).

Answer

Stage data, then insert new versions, then expire old records.

Answer

Identify changes, then stage data, then expire old records, then insert new versions.

Answer

Stage data, then expire old records, then identify changes, then insert new versions.

Question 2

You are a data engineer at a retail company. You need to develop a data processing solution in Azure Synapse Analytics that reads sales transactions from Parquet files stored in Azure Data Lake Storage Gen2, transforms the data by aggregating daily sales per store, and writes the results to a dedicated SQL pool table for reporting. The transformation logic must be reusable and maintained in a source control system. You want to minimize administrative overhead and leverage serverless resources where possible. Which approach should you recommend?

Accepted Answer

Use a serverless SQL pool to query the Parquet files via OPENROWSET, then use CETAS to write the aggregated results to the dedicated SQL pool using an external table.. A serverless SQL pool can directly query Parquet files using OPENROWSET, and the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement can transform and store aggregated results in a dedicated SQL pool table via PolyBase. This approach uses serverless resources for transformation, minimizing administrative overhead and avoiding the need to manage Spark pools. Option A is incorrect because Azure Data Factory with Mapping Data Flows runs on Spark clusters, which adds administrative overhead and does not align with the goal of minimizing management. Option C is incorrect because creating an Azure Synapse Spark notebook requires provisioning and managing a Spark pool, which increases administrative overhead. Option D is incorrect because using PolyBase in a dedicated SQL pool requires creating external tables and performing INSERT...SELECT, which is less flexible for reusable transformation logic compared to CETAS in serverless SQL pool.

Answer

Use Azure Data Factory with Mapping Data Flows to read Parquet files, perform aggregations, and write to the dedicated SQL pool.

Answer

Create an Azure Synapse Spark notebook that reads Parquet files, performs aggregation using PySpark, and writes the results to the dedicated SQL pool using the Spark Synapse connector.

Answer

Use PolyBase in a dedicated SQL pool to create external tables over the Parquet files, then use INSERT...SELECT to load aggregated data into the target table.

Question 3

Drag and drop the steps to set up Azure Data Lake Storage Gen2 hierarchical namespace for a data lake into the correct order.

Accepted Answer

Enable hierarchical namespace on storage account, create a container, create directories, set permissions, upload data.. The storage account must have hierarchical namespace enabled. Then create a container, directories, set permissions, and upload data.

Answer

Enable hierarchical namespace on storage account, create directories, create a container, set permissions, upload data.

Answer

Enable hierarchical namespace on storage account, create a container, set permissions, create directories, upload data.

Answer

Create a container, enable hierarchical namespace on storage account, create directories, set permissions, upload data.

Question 4

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from IoT devices and store it in a dedicated SQL pool for reporting. The data volume is high (millions of events per hour), and you need to optimize for both ingestion speed and query performance. You also need to ensure that the data can be partitioned by date for efficient maintenance. Which architecture should you recommend?

Accepted Answer

Use Event Hubs Capture to store data in Avro files in Blob Storage and then query with external tables.. The correct architecture. Event Hubs Capture automatically captures streaming data into Avro files in Blob Storage, which can be organized by date in folder structures. External tables in Azure Synapse can query these files with partition elimination, providing good query performance. This approach handles high-volume ingestion efficiently without impacting the streaming pipeline. Option A is incorrect because PolyBase in Azure Synapse does not natively support the Delta format (it supports Parquet, ORC, etc.), so loading Delta format using PolyBase is not feasible. Option B is incorrect because direct writes from Stream Analytics to a dedicated SQL pool are not efficient for millions of events per hour due to batch constraints and potential bottlenecks. Option C is incorrect because Azure SQL Database with elastic scaling is intended for online transaction processing (OLTP) workloads, not large-scale analytical processing.

Answer

Ingest data to Azure Data Lake Storage Gen2 in Delta format, then use PolyBase to load into a dedicated SQL pool partitioned by date.

Answer

Use Azure Stream Analytics to write directly to a dedicated SQL pool with a time-based window.

Answer

Store data in Azure SQL Database with elastic scaling and use linked server queries.

Question 5

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is partitioned by date and ingested daily. You notice that the Delta table has many small files, causing slow read performance. Which strategy should you recommend to optimize the table for faster queries?

Accepted Answer

Run OPTIMIZE on the table to compact small files.. Running OPTIMIZE on a Delta Lake table compacts many small files into larger ones, reducing the number of files that need to be read during queries. This directly addresses the slow read performance caused by the small file problem, which is common in daily partitioned ingestion. OPTIMIZE uses bin-packing to merge files up to a target size (default 256 MB), improving scan efficiency without changing the data.

Answer

Run ZORDER BY on the date column.

Answer

Run VACUUM to delete old files.

Answer

Increase the number of partitions by adding a new partition column.

Question 6

You are monitoring Azure Data Factory pipelines. You need to set up an alert when a pipeline run fails. What is the most efficient way to achieve this?

Accepted Answer

Create an Azure Monitor alert based on the 'Failed pipeline runs' metric.. The most efficient way to alert on pipeline failures is to use the 'Failed pipeline runs' metric in Azure Monitor, as it is a native metric provided by Azure Data Factory without additional configuration. Option A (logging to custom table) requires extra activities and setup. Option B (diagnostic settings and log alerts) is possible but more complex than using the metric. Option D (Application Insights) is not directly integrated for pipeline run monitoring.

Answer

Add an activity in each pipeline to log failures to a custom table in Log Analytics.

Answer

Enable diagnostic settings to send pipeline runs to a Log Analytics workspace and create a log alert.

Answer

Use Application Insights to monitor pipeline executions.

Question 7

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

Accepted Answer

The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).. A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Answer

The output schema in Synapse does not match the Stream Analytics output.

Answer

The Event Hubs has a large number of late-arriving events.

Answer

The tumbling window size is too large.

Question 8

You are building a data processing pipeline in Azure Synapse Analytics. The pipeline should read data from Azure Data Lake Storage Gen2 (Parquet files), apply transformations using a mapping data flow, and write the results to a dedicated SQL pool table. The source data contains personally identifiable information (PII). You need to mask the PII columns (e.g., email) using a data masking function within the data flow. Which transformation should you use?

Accepted Answer

Derived Column transformation. The Derived Column transformation in mapping data flows allows you to create new columns or modify existing ones using expressions, including built-in data masking functions like `mask()`, `maskEmail()`, or `substring()`. This is the correct transformation to apply PII masking on columns such as email addresses within the data flow pipeline before writing to the dedicated SQL pool.

Answer

Join transformation

Answer

Aggregate transformation

Answer

Pivot transformation

Question 9

You are monitoring an Azure Data Lake Storage Gen2 account using Metrics and Audit logs. You notice that the 'Ingress' metric shows a sudden spike but the 'Egress' metric remains stable. There are no new storage events in the audit log. What is the most likely cause?

Accepted Answer

An Azure Data Factory pipeline is writing intermediate results to the storage account.. An Azure Data Factory pipeline writing intermediate results to the storage account would cause a spike in 'Ingress' (data written into the account) without a corresponding increase in 'Egress' (data read from the account). The absence of new storage events in the audit log suggests the writes are not triggering blob-level events (e.g., BlobCreated events), which is consistent with Data Factory writing intermediate files using the Azure Blob Storage REST API or SDK without enabling event grid notifications for those specific operations.

Answer

The storage account is configured with geo-redundant storage (GRS) and data is being replicated to the secondary region.

Answer

A Spark job is reading large amounts of data in parallel.

Answer

An Azure Function is triggered by blob creation events and writes logs to the same account.

Question 10

Your company uses Azure Data Lake Storage Gen2 and needs to implement a data retention policy that automatically deletes files older than 90 days in a specific container. What should you use?

Accepted Answer

Azure Storage lifecycle management rule with a filter for the container and a delete action after 90 days.. Azure Storage lifecycle management rules can be applied at the container level to automatically delete blobs after a specified age (90 days), providing a native, cost-effective, and automated retention solution. Option A is incorrect because Azure Data Factory can be scheduled to delete files, but it requires manual pipeline setup and incurs additional costs compared to lifecycle management. Option B is incorrect because Azure Policy with a deny effect prevents the creation or modification of resources, not the automatic deletion of existing files. Option D is incorrect because Azure Purview is a data governance service for cataloging and lineage, not for automated data deletion.

Answer

Azure Data Factory pipeline with a Delete activity scheduled to run daily.

Answer

Azure Policy with a deny effect for files older than 90 days.

Answer

Azure Purview data lifecycle policy.

Question 11

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 12

A company runs a mission-critical Azure Data Factory pipeline that ingests data every hour from Azure Blob Storage into Azure Synapse Dedicated SQL Pool. Recently, the pipeline has been failing with timeout errors during the copy activity. The source blob files are around 500 MB each. Which configuration change would MOST effectively reduce the likelihood of timeout errors?

Accepted Answer

Enable 'Enable staging' and set 'Degree of copy parallelism' to a higher value.. Enabling staging allows the copy activity to use Azure Blob Storage as an intermediate staging area, which breaks the 500 MB files into manageable chunks and uses parallel staging writes to the Dedicated SQL Pool. This reduces the load on the single copy session and prevents timeout errors by leveraging the staging engine's retry and parallelization capabilities.

Answer

Decrease the 'Batch size' for the copy activity.

Answer

Change the sink to use PolyBase with staging enabled.

Answer

Increase the Data Integration Unit (DIU) to 8.

Question 13

Which TWO data processing activities in Azure Data Factory allow you to run custom code?

Accepted Answer

Custom activity. The Custom activity in Azure Data Factory allows you to execute custom code by running a .NET or PowerShell script in an Azure Batch service. This enables you to implement complex transformation logic that is not natively supported by ADF's built-in activities, such as custom data validation or integration with third-party APIs.

Answer

Data Flow activity

Answer

Copy activity

Answer

Stored Procedure activity

Question 14

Which THREE security features are available for Azure SQL Database? (Choose three.)

Accepted Answer

Auditing. Auditing is a built-in security feature for Azure SQL Database that tracks database events and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. It helps maintain regulatory compliance, understand database activity, and gain insights into discrepancies and anomalies that could indicate security concerns.

Answer

Always On availability groups

Answer

Firewall rules

Question 15

Your company has an Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that automatically moves data between the 'PRIMARY' filegroup and a secondary filegroup based on data age, without manual intervention. Which feature should you use?

Accepted Answer

Partition switching. Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to efficiently move data between filegroups by switching partitions between tables. By aligning partitions with data age, you can automatically transfer older data to a secondary filegroup without manual intervention, using a scheduled process like a stored procedure or Azure Data Factory.

Answer

Materialized views

Answer

Azure Data Lake Storage tiering

Answer

PolyBase