DP-203 Develop data processing Practice Test 5 — 15 Questions

Question 1

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?

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

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 2

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query data in Azure Data Lake Storage Gen2. The data is stored as Parquet files partitioned by date. Which TWO of the following statements are true regarding querying this data? (Select TWO.)

Accepted Answer

You can use the filepath() function in the query to retrieve the partition column values.. The `filepath()` function in a serverless SQL pool query returns the file path of the row being read. When data is partitioned by date in Azure Data Lake Storage Gen2, the partition column values are embedded in the folder structure (e.g., `/year=2023/month=01/day=15/`). Using `filepath(1)`, `filepath(2)`, etc., you can extract these values directly in the query without needing to parse the path manually.

Answer

You must create an external table to query Parquet files; OPENROWSET is not supported.

Answer

You can create indexes on the serverless SQL pool to improve query performance.

Answer

You can only query a single file at a time; wildcards are not supported.

Question 3

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?

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

Accepted Answer

Enable 'Allow schema drift' in the source settings.. Enabling 'Allow schema drift' in the source settings of a mapping data flow tells Azure Data Factory to accept incoming columns that are not defined in the source schema. This is essential for handling schema drift, as it prevents the pipeline from failing when new columns appear in the source data.

Answer

Use a 'Select' transformation to explicitly choose only the known columns.

Answer

Add a 'Derived Column' transformation for every possible column that might be added.

Question 4

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to support both batch and near-real-time data ingestion. Which TWO of the following methods can you use to ingest data into a dedicated SQL pool? (Select TWO.)

Accepted Answer

CREATE TABLE AS SELECT (CTAS) from external tables.. CREATE TABLE AS SELECT (CTAS) from external tables is correct because it allows you to load data from external storage (e.g., Azure Blob Storage or Azure Data Lake Storage) into a dedicated SQL pool in a single, parallelized operation. This method leverages the MPP (Massively Parallel Processing) architecture of Synapse SQL pools to efficiently ingest large volumes of data for batch processing.

Answer

COPY INTO command.

Answer

Azure Logic Apps with SQL connector.

Answer

Azure Data Factory with a copy activity using native sink.

Question 5

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The template defines a managed virtual network integration runtime. You need to ensure that the integration runtime can run mapping data flows with a time-to-live (TTL) of 10 minutes. What is the purpose of the 'timeToLive' property in this configuration?

Accepted Answer

It defines how long the cluster will be kept alive after a data flow completes, allowing subsequent data flows to reuse the cluster.. The 'timeToLive' property in an Azure Synapse Analytics managed virtual network integration runtime controls how long the cluster remains alive after a mapping data flow completes. By setting a TTL of 10 minutes, subsequent data flows can reuse the same warm cluster, avoiding the 5–10 minute cold start time for new clusters. This optimizes performance and reduces latency for consecutive data flow executions.

Answer

It sets the timeout for the integration runtime to connect to the data sources.

Answer

It specifies the maximum duration a data flow activity can run before timing out.

Answer

It determines the maximum number of concurrent data flows that can run on the cluster.

Question 6

Refer to the exhibit. You have an Azure Data Factory dataset definition for a Parquet file stored in Azure Data Lake Storage Gen2. You attempt to use this dataset as a source in a copy activity, but the copy activity fails with an error indicating that the file is not found. The file 'sales_orders.parquet' exists at the specified path. What is the most likely cause of the error?

Accepted Answer

The dataset is configured with a specific file name, but the data is partitioned across multiple folders; you should use a wildcard pattern in the folderPath.. The dataset definition specifies a single file name ('sales_orders.parquet') in the 'fileName' property, but the actual data is likely stored across multiple Parquet files in a partitioned folder structure (e.g., 'sales_orders/year=2024/month=01/'). When a copy activity uses this dataset as a source, Azure Data Factory looks for the exact file name at the specified path, not finding any file because the data is spread across subfolders. Using a wildcard pattern (e.g., '*.parquet') in the 'folderPath' or setting 'fileName' to '*' allows the copy activity to read all Parquet files in the folder, resolving the 'file not found' error.

Answer

The compression codec 'snappy' is not supported for Parquet files.

Answer

The dataset type should be 'AzureBlobStorageLocation' instead of 'AzureBlobFSLocation'.

Answer

The linked service 'ADLSGen2' is not properly configured with the storage account key.

Question 7

Refer to the exhibit. You have created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. When you query the external table, you get an error that the external table is not accessible. What should you check first?

Accepted Answer

Verify that the serverless SQL pool has been granted the 'Storage Blob Data Reader' role on the storage account.. The error 'external table is not accessible' in Azure Synapse serverless SQL pool typically indicates an authorization failure when the SQL pool attempts to read the underlying Parquet files in Azure Data Lake Storage Gen2. Serverless SQL pool uses its own service identity to access storage, and it must be granted the 'Storage Blob Data Reader' role on the storage account at the storage account scope to have read permissions. Without this role assignment, the SQL pool cannot authenticate to the storage, resulting in the access error.

Answer

Check that the external table's LOCATION path is relative to the container and does not start with a slash.

Answer

Ensure that the external file format is correctly referencing the Parquet format.

Answer

Confirm that the Snappy compression codec is supported by the serverless SQL pool.

Question 8

Your organization uses Azure Synapse Analytics to run large-scale ETL jobs. A pipeline that loads data from Azure Data Lake Storage Gen2 into a dedicated SQL pool is failing with 'Out of memory' errors during the staging step. The source data is 500 GB in size, and the SQL pool is currently set to DW500c. What should you do to resolve the issue without changing the pipeline logic?

Accepted Answer

Increase the DWU of the dedicated SQL pool to a higher tier (e.g., DW1000c).. The 'Out of memory' error during the staging step indicates that the dedicated SQL pool lacks sufficient memory resources to handle the 500 GB data load within the current DWU setting. Increasing the DWU (Data Warehouse Units) from DW500c to a higher tier, such as DW1000c, scales up the memory and compute resources available to the pool, resolving the memory pressure without altering the pipeline logic or source data.

Answer

Use PolyBase instead of the Copy activity.

Answer

Switch the source to Azure Data Lake Storage Gen1.

Answer

Partition the source data into smaller files.

Question 9

You are designing a near-real-time data processing solution for a retail company. The source is a Kafka cluster on-premises. The target is an Azure Synapse Dedicated SQL Pool. The solution must handle up to 10,000 events per second with less than 5-minute latency. Which Azure service should you use to ingest the data?

Accepted Answer

Azure Event Hubs (with Kafka protocol support). Azure Event Hubs with Kafka protocol support is the correct choice because it provides a fully managed, high-throughput data ingestion service that can handle up to 10,000 events per second with sub-second latency, and it natively supports the Kafka protocol, allowing direct integration with your on-premises Kafka cluster without custom code or additional gateways. This meets the near-real-time requirement (<5-minute latency) and scales to the specified throughput.

Answer

Azure Data Lake Storage Gen2

Answer

Azure IoT Hub

Answer

Azure Stream Analytics

Question 10

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

Accepted Answer

A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.. The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Answer

The Azure Blob Storage account has been moved to a different subscription.

Answer

The self-hosted integration runtime node has been de-registered from Azure Data Factory.

Answer

The blob container has reached its maximum capacity.

Question 11

You are building a data processing solution in Azure Synapse Analytics. The solution requires creating a table that stores sales transactions. The table will be used for both point-of-sale lookups and large aggregation queries. The data is not updated frequently. Which table distribution should you recommend?

Accepted Answer

ROUND_ROBIN. ROUND_ROBIN is the correct choice because the table is used for both point-of-sale lookups (single-row queries) and large aggregation queries, and the data is not updated frequently. ROUND_ROBIN distributes data evenly across all distributions without a hash key, which provides the best overall performance for mixed workloads where no single distribution key optimizes both lookup and aggregation patterns. It avoids data skew and allows parallel processing for aggregations while still supporting efficient lookups when combined with appropriate indexes.

Answer

HASH on TransactionID

Answer

HASH on SalesDate

Answer

REPLICATE

Question 12

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 13

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

Accepted Answer

Data is unevenly distributed across distributions, causing some distributions to be overloaded.. D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Answer

Statistics on the table are outdated.

Answer

The table is not properly partitioned.

Answer

Data compression is not working efficiently.

Question 14

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Answer

Row-level security (RLS)

Answer

Dynamic data masking

Answer

Microsoft Defender for Cloud

Question 15

You are processing streaming data from IoT devices using Azure Stream Analytics. The data includes temperature readings and device IDs. You need to calculate the average temperature per device over a 5-minute window, sliding every 1 minute. Which window function should you use?

Accepted Answer

Hop window. A Hop window in Azure Stream Analytics allows you to specify a window size (5 minutes) and a hop size (1 minute), creating overlapping windows that slide forward every minute. This matches the requirement to calculate the average temperature per device over a 5-minute period, recalculated every minute, as the hop window outputs results at each hop interval while retaining data across overlapping windows.

Answer

Session window

Answer

Sliding window

Answer

Tumbling window