DP-203 Practice Questions 2 — 50 Questions

Question 1

Your organization is implementing a data lake using Azure Data Lake Storage Gen2. You have a folder structure like '/data/landing/' for raw data and '/data/curated/' for cleaned data. The data is ingested daily from various sources. You need to ensure that data in the curated zone is immutable and cannot be modified or deleted by anyone, including administrators, for compliance reasons. However, data in the landing zone should be modifiable. What should you do?

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

Accepted Answer

Enable immutable storage with a time-based retention policy on the curated zone container. Azure Data Lake Storage Gen2 supports immutable storage at the container level, which enforces a time-based retention policy that prevents any data from being modified or deleted—even by administrators—until the retention period expires. This directly meets the compliance requirement for the curated zone, while leaving the landing zone container unaffected and modifiable.

Answer

Remove the 'Delete' permission from the storage account key

Answer

Set ACLs on the curated zone folder to deny write and delete for all users

Answer

Use Azure RBAC to deny delete and write permissions for all users on the curated zone folder

Question 2

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage

Question 3

Match each Azure data integration tool to its typical use case.

Accepted Answer

Azure Data Factory: Orchestrate and automate data movement and transformation at scale.. In this matching exercise, the correct pairs are: Azure Data Factory for orchestration, Azure Synapse Analytics for data warehousing, Azure Databricks for data engineering/ML, and Azure Stream Analytics for real-time streaming. Common confusions involve swapping orchestration and streaming roles.

Answer

Azure Data Factory: Process real-time streaming data from IoT devices.

Answer

Azure Synapse Analytics: Orchestrate data movement at scale.

Question 4

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Accepted Answer

Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.. Dynamic data masking obfuscates sensitive columns for unauthorized users at query time, providing column-level security without altering the underlying data. Option D is correct because column-level security in Azure Synapse dedicated SQL pools uses GRANT SELECT on specific columns to authorized users, restricting access to sensitive columns. Option B is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not at the column level. Option C is incorrect because security policies with filter predicates are used for row-level security, not column-level security. Option E is incorrect because Always Encrypted encrypts columns at the application level, which is separate from column-level security in Synapse.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a security policy with filter predicates to restrict access to specific columns.

Answer

Implement Always Encrypted to encrypt columns at the application level.

Question 5

Which THREE metrics from Azure Monitor should be used to diagnose performance bottlenecks in an Azure Data Factory pipeline?

Accepted Answer

Pipeline Succeeded Rerun Count. Pipeline Succeeded Rerun Count (A) is correct because a high number of reruns indicates that the pipeline is repeatedly failing and retrying, which directly points to a performance bottleneck such as resource contention or throttling. This metric helps identify pipelines that are not completing successfully on the first attempt, signaling underlying issues that degrade throughput.

Answer

Blob Capacity

Answer

SQL Pool DWU Used

Question 6

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

Accepted Answer

FTP. Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Answer

Azure Blob Storage

Answer

HTTP

Answer

Rest service

Question 7

Refer to the exhibit. A data engineer runs a pipeline copy activity and gets this error. What is the most likely cause of the failure?

Accepted Answer

The self-hosted integration runtime is not running or unreachable.. The error indicates the self-hosted integration runtime is not ready. This typically happens when the IR is not running or has network issues.

Answer

The destination storage account is inaccessible.

Answer

The pipeline parameters are missing.

Answer

The source database is offline.

Question 8

You are designing a streaming data solution for IoT devices that generate 10,000 events per second. The data must be processed with sub-second latency and then stored in Azure Data Lake Storage Gen2 for archival. Which Azure service should you use for the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for real-time stream processing with sub-second latency, and it natively integrates with Azure Data Lake Storage Gen2 for output. It can handle 10,000 events per second using its streaming unit scaling, and its SQL-like query language allows for low-latency transformations without the overhead of cluster management.

Answer

Azure HDInsight including Spark Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

Question 9

A data engineer needs to process a large dataset stored in Azure Blob Storage using Azure Databricks. The dataset consists of millions of small CSV files. The processing job is slow due to the overhead of reading many small files. Which technique should be used to improve performance?

Accepted Answer

Coalesce the small files into larger files using a Databricks notebook. Coalescing the millions of small CSV files into larger files reduces the metadata overhead and I/O operations when reading from Azure Blob Storage. Databricks can then process fewer, larger files more efficiently, as each task handles a substantial data chunk rather than incurring the cost of opening and closing many small files.

Answer

Increase the number of worker nodes in the cluster

Answer

Convert the CSV files to Parquet format

Answer

Use Delta Lake caching to store the data in memory

Question 10

You need to transform data in Azure Synapse Analytics using a language that supports procedural logic and error handling. Which option should you use?

Accepted Answer

T-SQL stored procedures. T-SQL stored procedures are the correct choice because they support procedural logic (e.g., IF/ELSE, loops, TRY/CATCH) and error handling within Azure Synapse Analytics dedicated SQL pools. This allows you to encapsulate complex data transformation logic, handle runtime errors gracefully, and manage transactions, which is not possible with declarative objects like views or external tables.

Answer

CREATE VIEW

Answer

PolyBase

Answer

CREATE EXTERNAL TABLE

Question 11

A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?

Accepted Answer

Rebuild the columnstore indexes on the table. Rebuilding columnstore indexes reorganizes and compresses the data, which can improve partition elimination by ensuring that each partition's columnstore segments are well-structured and aligned with the partition boundaries. Over time, as data is inserted, updated, or deleted, columnstore indexes can become fragmented, leading to inefficient partition pruning and slower query performance on date-range filters.

Answer

Convert the table to a rowstore heap

Answer

Create statistics on the date column

Answer

Increase the number of partitions for the table

Question 12

A company ingests streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time to detect anomalies and stored in Azure Data Lake Storage Gen2 for historical analysis. The solution must minimize latency and avoid duplicate processing. Which Azure service should be used for processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Event Hubs as input and Data Lake Storage Gen2 as output, and provides built-in exactly-once delivery semantics to avoid duplicate processing. It also supports temporal windowing and anomaly detection functions natively, making it ideal for this IoT anomaly detection scenario.

Answer

Azure Data Factory

Answer

Azure Databricks with Structured Streaming

Answer

Azure Functions with Event Hubs trigger

Question 13

You are using Azure Data Factory to load data from an on-premises SQL Server to Azure Synapse Analytics. The data size is 500 GB, and you need to minimize the load time. The network bandwidth is limited. Which approach should you use?

Accepted Answer

Use a self-hosted integration runtime and stage the data in Azure Blob Storage before loading to Synapse.. Using a self-hosted integration runtime with staging via Azure Blob Storage allows you to stage the data in Azure before loading to Synapse, which is faster over limited bandwidth. Option A (copy directly) is slower due to the bandwidth limitation. Option C (Azure ExpressRoute) requires additional setup and cost, but the question asks for the approach that minimizes load time with limited bandwidth, and staging is the most efficient. Option D (Azure Data Lake Storage) is similar to staging but not as optimized for the scenario.

Answer

Use a self-hosted integration runtime and copy directly to Synapse.

Answer

Use Azure ExpressRoute to improve network bandwidth.

Answer

Use an Azure integration runtime and copy to Azure Data Lake Storage, then to Synapse.

Question 14

Match each Azure service to its primary purpose in a data engineering pipeline.

Accepted Answer

Azure Data Lake Storage Gen2: Scalable data lake for big data analytics.. The correct matches are: Azure Data Lake Storage Gen2 as a scalable data lake, Azure Synapse Analytics as a unified data warehousing platform, Azure Data Factory for data integration and orchestration, and Azure Databricks for Spark-based analytics. Common confusions include swapping storage and integration services, or mistaking data lakes for data warehousing.

Answer

Azure Data Lake Storage Gen2: Cloud data integration service for orchestrating data movement.

Answer

Azure Synapse Analytics: Scalable data lake for big data analytics.

Question 15

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options:

Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase.

Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase.

Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse.

Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source.

Which option should you choose?

Accepted Answer

Option B. It handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency. Option D requires additional log-shipping, increasing complexity and latency.

Answer

Option C

Answer

Option A

Answer

Option D

Question 16

You are developing a data processing pipeline in Azure Data Factory. The pipeline must copy data from an on-premises SQL Server to Azure Blob Storage. The data volumes are large, and the network connection is unreliable. Which configuration should you use to ensure resilience and resume capability?

Accepted Answer

Configure the copy activity with fault tolerance and enable 'Skip incompatible rows'. Use a self-hosted integration runtime for the source.. Using a self-hosted integration runtime enables connectivity to on-premises SQL Server, and enabling fault tolerance with 'Skip incompatible rows' allows the copy activity to continue despite transient failures or data inconsistencies. This configuration ensures resilience by automatically retrying failed operations and skipping rows that cause errors, which is critical for large data volumes over an unreliable network.

Answer

Use a staging copy with Azure Blob Storage as an intermediate store.

Answer

Increase the 'parallelCopies' setting to 10 and use a staging table.

Answer

Use PolyBase to load data directly into Azure Synapse Analytics.

Question 17

Which TWO Azure services can be used to implement a data lakehouse architecture with Delta Lake?

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides a unified analytics platform with native Delta Lake support, enabling ACID transactions, schema enforcement, and time travel on data lakes. Delta Lake is an open-source storage layer that brings reliability to data lakes, and Azure Databricks is the primary service for running Delta Lake workloads at scale.

Answer

Azure Data Factory

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 18

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

Accepted Answer

Review the job logs in Azure Data Lake Analytics to identify the specific script error.. The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Answer

Change the output format to Parquet to reduce file size.

Answer

Migrate the U-SQL script to Azure Synapse Spark pool.

Answer

Increase the degree of parallelism for the U-SQL job.

Question 19

Drag and drop the steps to implement Azure Data Lake Storage Gen2 lifecycle management to move data to cool and archive tiers into the correct order.

Accepted Answer

Create Azure Data Lake Storage Gen2 account, then define a lifecycle management rule with conditions and actions, then apply the rule to the account, then verify data movement.. After creating the account, define a lifecycle rule with conditions and actions, then apply it.

Answer

Define a lifecycle management rule, then create an Azure Data Lake Storage Gen2 account, then apply the rule, then verify data movement.

Answer

Create Azure Data Lake Storage Gen2 account, then apply a lifecycle management rule, then define the rule conditions and actions, then verify data movement.

Answer

Define a lifecycle management rule, then apply it to the account, then create the Azure Data Lake Storage Gen2 account, then verify data movement.

Question 20

Drag and drop the steps to implement incremental data loading using Azure Data Factory into the correct order.

Accepted Answer

Lookup the last watermark value from control table, then copy data from source where timestamp > watermark, then update watermark with max timestamp from copied data.. Incremental loading requires a watermark column. The pipeline retrieves the last watermark, copies data changed since then, then updates the watermark for next run.

Answer

Copy data from source, then lookup the last watermark value, then update watermark.

Answer

Update watermark with max timestamp from source, then lookup the last watermark, then copy data.

Answer

Lookup the last watermark value, then update watermark with max timestamp, then copy data.