DP-203 Practice Exam 52 — 20 Questions

Question 1

Which TWO of the following are required components to set up a data pipeline that uses Change Data Capture (CDC) to incrementally load data from SQL Server to Azure Synapse using Azure Data Factory?

Accepted Answer

CDC enabled on the source SQL Server database and tables. Change Data Capture (CDC) must be enabled on the source SQL Server database and the specific tables you intend to track. Without CDC enabled, SQL Server will not generate the change tracking tables (e.g., cdc.<capture_instance>_CT) that Azure Data Factory’s CDC connector reads to identify inserts, updates, and deletes. This is a prerequisite for any incremental load using the native CDC mechanism in ADF.

Answer

A staging Azure Blob Storage account

Answer

A Lookup activity to get the last watermark

Answer

A stored procedure in the source database to capture changes

Question 2

Your company stores JSON documents in Azure Cosmos DB Core (SQL) API. You need to improve query performance for a common filter on the 'status' field and a sort on 'timestamp'. Which three actions should you take?

Accepted Answer

Create a composite index on (status, timestamp). A composite index on (status, timestamp) is correct because Azure Cosmos DB Core (SQL) API uses composite indexes to efficiently support queries with multiple filter and sort conditions. This index allows the query engine to first filter on the 'status' field and then sort by 'timestamp' in a single index seek, avoiding a full scan or in-memory sort. Without this composite index, the query would require a cross-partition scan or an expensive sort operation, degrading performance.

Answer

Denormalize the status field into a separate container

Answer

Disable indexing on the timestamp field

Question 3

Match each storage redundancy option to its description in Azure Storage.

Accepted Answer

LRS: Data is synchronously replicated three times within a single datacenter.. Common redundancies: LRS uses local replication, ZRS uses zonal replication, and GRS adds a secondary region. Distractors mix these concepts.

Answer

LRS: Data is replicated across multiple availability zones.

Answer

GRS: Data is replicated synchronously within a single datacenter.

Question 4

Refer to the exhibit. A user with Storage Blob Data Reader role on the container rawdata cannot list files under /2023/07/. What is the most likely reason?

Accepted Answer

The directory ACL does not grant 'execute' permission to the user. In Azure Data Lake Storage Gen2, listing files in a directory requires both read (r) and execute (x) permissions on the directory itself. The Storage Blob Data Reader role grants read access to blob data but does not automatically grant the execute permission on directory ACLs. Without execute permission on the /2023/07/ directory, the user cannot traverse or list its contents, even though they can read blobs they know the path to.

Answer

The user does not have Storage Blob Data Contributor role

Answer

The user is not the owner of the directory

Answer

The container name is misspelled

Question 5

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline occasionally fails due to transient errors. Which monitoring solution should you use to get alerts on failures and analyze trends over time?

Accepted Answer

Azure Monitor with Log Analytics workspace. Azure Monitor with alerts and Log Analytics provides historical analysis and alerting. Option A (Data Factory Monitor) is for real-time monitoring but lacks long-term trend analysis. Option C (Azure Dashboard) is a visualization tool. Option D (Event Grid) is for event-driven notifications, not analysis.

Answer

Azure Event Grid subscription for pipeline failures

Answer

Azure Dashboard pinned with pipeline metrics

Answer

Azure Data Factory Monitor in the Azure portal

Question 6

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2. You want to minimize cost and complexity. Which Azure Data Factory feature should you use?

Accepted Answer

Use the 'Incremental copy' capability with change tracking enabled on the source. Azure Data Factory's 'Incremental copy' capability with change tracking on Azure SQL Database automatically identifies and transfers only the changed rows since the last run, using the built-in change tracking mechanism. This minimizes cost and complexity by avoiding custom logic for watermark columns or full reloads, as it handles the delta extraction natively.

Answer

Use a Lookup activity to get the maximum timestamp from the sink and filter the source

Answer

Use a Stored Procedure activity to delete and reinsert data

Answer

Use a Mapping Data Flow to compare source and sink

Question 7

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

Accepted Answer

Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.. A 'Connection closed' error during large data transfers (50 GB) often occurs due to resource exhaustion or network instability on the self-hosted integration runtime (IR) machine. Insufficient memory or CPU on the IR node or unreliable network connectivity can cause the connection to drop. This is the first thing to troubleshoot. Option B (increasing connection timeout) might help if the error is due to timeouts, but 'Connection closed' typically indicates an abrupt termination, not a timeout. Option C (staged copy) can improve reliability by using Azure Blob as an intermediate store, but it adds complexity and is not the first step. Option D (disabling fault tolerance) would make the pipeline less resilient and is not recommended; fault tolerance helps handle transient errors.

Answer

Increase the 'connection timeout' setting in the linked service to 30 minutes.

Answer

Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

Answer

Disable fault tolerance in the copy activity to improve performance.

Question 8

You are designing a data processing solution in Azure Synapse Analytics. The solution must support incremental loading of data from an Azure SQL Database to a dedicated SQL pool using PolyBase. Which approach should you use to minimize data movement and maximize performance?

Accepted Answer

Create external tables in the dedicated SQL pool that reference the source data, then use CREATE TABLE AS SELECT (CTAS) to load incrementally.. Using external tables with PolyBase in Azure Synapse Analytics allows you to directly query the source Azure SQL Database without moving the data first. The CREATE TABLE AS SELECT (CTAS) statement then loads only the incremental data into the dedicated SQL pool, minimizing data movement by leveraging PolyBase's parallel streaming capability for maximum performance.

Answer

Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.

Answer

Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.

Answer

Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.

Question 9

Which of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Azure Storage Service Encryption (SSE) with Microsoft-managed keys. Azure Storage Service Encryption (SSE) with Microsoft-managed keys encrypts data at rest automatically for Azure Data Lake Storage Gen2 using 256-bit AES encryption. This is enabled by default for all storage accounts, ensuring data written to disk is encrypted before being persisted, with no additional configuration required.

Answer

Azure Active Directory (Azure AD) authentication for storage accounts

Answer

Configure firewall rules to restrict IP access

Question 10

You are designing a hybrid data storage architecture for a global e-commerce platform. Which two Azure services should you combine to achieve low-latency read access for users worldwide and durable archival storage for compliance?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is correct because it provides globally distributed, multi-region write and read capabilities with turnkey global distribution, enabling low-latency access for users worldwide via its multi-homing API. Azure Blob Storage is correct because it offers durable, tiered archival storage (e.g., Cool, Archive access tiers) at low cost, meeting compliance requirements for long-term retention of data such as transaction logs or invoices.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Files

Question 11

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Accepted Answer

Store data in Parquet format. Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Answer

Use many small files (under 64 MB) to increase parallelism

Answer

Store data in nested folder structures for better organization

Question 12

Which THREE factors should you consider when choosing between Azure Data Factory Mapping Data Flows and Azure Synapse Spark pools for data transformation?

Accepted Answer

Ease of use: Mapping Data Flows provide a visual designer, while Spark requires code.. Azure Data Factory Mapping Data Flows offer a visual, no-code designer for building data transformations, which lowers the barrier for users who are not proficient in programming. In contrast, Azure Synapse Spark pools require writing code in languages like PySpark, Scala, or SQL, making them more suitable for developers comfortable with coding. This distinction directly addresses ease of use as a key factor in choosing between the two services.

Answer

Scheduling: Only Data Flows can be scheduled via triggers.

Answer

Data volume: Data Flows are limited to 100 GB, while Spark can handle petabytes.

Question 13

Refer to the exhibit. You are deploying the ARM template to create an Azure Synapse Analytics workspace. The deployment fails because the managed resource group cannot be created. What is the most likely reason?

Accepted Answer

The managedResourceGroupName property is not allowed; Azure Synapse creates the resource group automatically. The `managedResourceGroupName` property is not allowed in an ARM template for Azure Synapse Analytics. Azure Synapse automatically creates and manages its own managed resource group (e.g., `managed-rg-<workspace-name>`) to hold provisioned resources like Spark pools and SQL pools. Specifying this property in the template causes a validation error, as the service requires full control over the naming and lifecycle of this group.

Answer

The apiVersion is invalid for this resource type

Answer

The location is missing the 'properties' section

Answer

The storage account URL must use the blob endpoint, not DFS

Question 14

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

Accepted Answer

Incremental copy (watermark) pattern using a Lookup activity and a Copy activity. The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Answer

Alter Row transformation

Answer

Schedule trigger

Answer

Lookup activity alone

Question 15

You are designing a data processing pipeline in Azure Synapse Analytics. The pipeline must ingest streaming data from Azure Event Hubs, perform real-time aggregations, and store the results in a dedicated SQL pool. Which component should you use to perform the real-time transformations?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for processing streaming data from sources like Azure Event Hubs. It supports SQL-based query language for performing aggregations, windowing functions, and temporal joins, and can directly output results to a dedicated SQL pool in Azure Synapse Analytics. This makes it the optimal component for ingesting streaming data, performing real-time transformations, and storing aggregated results in a Synapse dedicated SQL pool.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 16

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

Accepted Answer

Delta Lake format. Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Answer

Auto Loader

Answer

Photon engine

Answer

Unity Catalog

Question 17

You are a data engineer for a retail company. The company uses Azure Data Lake Storage Gen2 to store raw transaction data partitioned by date. Each day, a folder is created with the format 'YYYY/MM/DD' containing thousands of small JSON files (each ~10 KB). An Azure Databricks job runs daily to read the previous day's folder, transform the data, and write to a Delta table for reporting. Over time, the job's execution time has increased from 15 minutes to over 2 hours. The job uses a cluster with 4 nodes (each 16 GB memory). Monitoring shows that the job spends most of its time in the 'listing files' stage. Which optimization should you implement to reduce the job duration?

Accepted Answer

Pre-process the raw data to coalesce small JSON files into larger parquet files (e.g., 256 MB each).. The job spends most of its time in the 'listing files' stage because reading thousands of small JSON files (each ~10 KB) from Azure Data Lake Storage Gen2 incurs high metadata operation overhead. Coalescing these small files into larger Parquet files (e.g., 256 MB each) reduces the number of files that Spark must list and process, dramatically cutting down the listing stage time and improving overall throughput.

Answer

Increase the number of nodes in the cluster to 16.

Answer

Change the output format from JSON to Delta and enable Delta caching.

Answer

Use Azure Data Factory instead of Databricks to copy the raw data.

Question 18

Which THREE factors should be considered when choosing between Azure Synapse Dedicated SQL Pool and Azure SQL Database for a data warehouse workload?

Accepted Answer

Concurrency and workload management. Azure Synapse Dedicated SQL Pool provides built-in workload management features like workload classification, importance, and concurrency slots, which are essential for managing mixed analytical workloads with varying resource demands. Azure SQL Database, while supporting resource governance, lacks the granular workload management capabilities needed for complex data warehouse scenarios with concurrent queries.

Answer

Need for geo-replication

Answer

Integration with Azure Data Lake Storage

Question 19

Refer to the exhibit. The pipeline executes a Spark job definition that reads from the input path and writes to the output path. The job fails with an error indicating that the output path already exists. The job is configured with default behavior. What configuration change should you make to allow the job to overwrite the existing output?

Accepted Answer

Add a Delete activity before the Spark job to delete the output folder if it exists.. The default behavior of Spark in Azure Synapse Analytics is to fail if the output path already exists. By adding a Delete activity before the Spark job, you explicitly remove the existing folder, allowing the subsequent Spark job to write without conflict. This approach works with any save mode, including the default 'error' mode, and is a common pattern in Synapse pipelines for idempotent data processing.

Answer

Modify the Spark job definition to include 'overwrite' save mode.

Answer

Change the output path in the arguments to a non-existing folder.

Answer

Set the pipeline property 'outputMode' to 'overwrite' on the SynapseSparkJob activity.

Question 20

You are designing a data processing solution using Azure Databricks. The data is stored in Delta Lake format. You need to ensure that when you read the latest version of the table, you only see committed data and not uncommitted transactions. Which isolation level should you use?

Accepted Answer

SnapshotIsolation. Snapshot isolation is the correct choice because it provides a consistent view of the table by reading only the latest committed data, ignoring any uncommitted transactions. In Delta Lake, snapshot isolation ensures that readers see a snapshot of the table at a specific version, which includes only committed changes, making it ideal for read consistency without blocking concurrent writes.

Answer

WriteSerializable

Answer

Serializable

Answer

ReadUncommitted