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

Question 1

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

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

Accepted Answer

The table is not registered in the Spark metastore; it is only in the lakehouse.. In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Answer

The user does not have read permission on the table.

Answer

The Parquet file format is incompatible with the Delta source.

Answer

The Delta table is corrupted.

Question 2

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 3

You need to process a large number of small files (each < 1 MB) from Azure Blob Storage in Azure Synapse Analytics. The processing is I/O-bound due to many small file operations. Which approach should you use to improve performance?

Accepted Answer

Use 'spark.sql.files.maxPartitionBytes' to coalesce small files into larger partitions.. `spark.sql.files.maxPartitionBytes` controls the maximum number of bytes packed into a single partition when reading files. By increasing this value, Spark coalesces many small files into fewer, larger partitions, reducing the overhead of task scheduling and I/O operations. This directly addresses the I/O-bound bottleneck caused by processing numerous small files in Azure Synapse Analytics.

Answer

Use wildcard paths to read multiple files at once.

Answer

Enable optimized write on the Spark session.

Answer

Convert the files to a binary format like Avro before processing.

Question 4

Which TWO of the following are valid ways to handle late-arriving data in a streaming solution with Azure Stream Analytics? (Choose two.)

Accepted Answer

Configure the 'late arrival tolerance' window in the event ordering settings up to 21 days.. Option D is correct because Azure Stream Analytics exposes an Event Ordering configuration with a Late arrival tolerance setting that can be extended up to a maximum of 21 days, allowing the job to accept and process events that arrive after their event-time timestamp within that window. Option E is correct because a temporal (time-based) join in Stream Analytics, using the DATEDIFF/OVER clause, lets you correlate a late-arriving event with previously processed events that fall within a defined historical time window, which is the standard pattern for enriching or reconciling late data. Option A is not a valid built-in mechanism: Stream Analytics does not automatically reprocess the entire stream from the beginning on late-data detection, and doing so would require manual job restart/checkpoint manipulation rather than a supported handling feature. Option B is not valid because there is no 'LateDataHandler' extension point or custom Azure Function hook in the Stream Analytics query language for late data. Option C is not valid because reference data inputs are for slowly changing lookup data (e.g., metadata), not for storing and replaying late-arriving stream events.

Answer

Reprocess the entire stream from the beginning when late data is detected.

Answer

Implement a custom Azure Function as a 'LateDataHandler' in the query.

Answer

Use a reference data input to store late-arriving events.

Question 5

You are building a data processing solution that requires exactly-once semantics when writing to Azure Event Hubs from Azure Stream Analytics. Which output format should you configure?

Accepted Answer

Avro. Azure Stream Analytics supports exactly-once semantics when writing to Event Hubs only when using Avro serialization. This is because Avro provides a compact binary format with embedded schema, enabling Stream Analytics to track and deduplicate events precisely during output, which is required for exactly-once delivery. JSON, CSV, and Parquet do not support the necessary metadata and checkpointing mechanisms for exactly-once guarantees in this specific integration.

Answer

JSON

Answer

CSV

Answer

Parquet

Question 6

You are designing a data ingestion pipeline for Azure Synapse Analytics using PolyBase. The source data is in Azure Blob Storage, with files in the Parquet format. The data volume is approximately 2 TB daily. You need to minimize the time to load data into a dedicated SQL pool table. Which approach should you recommend?

Accepted Answer

Create an external table pointing to the Parquet files and use CREATE TABLE AS SELECT (CTAS) to load the data.. Using CREATE TABLE AS SELECT (CTAS) with an external table pointing to Parquet files leverages PolyBase's massively parallel processing capabilities. While the COPY INTO command does support Parquet format in dedicated SQL pools, CTAS offers superior performance for large-scale daily ingestion (2 TB) as it allows for distribution and index optimizations during the load, and it reads the compressed columnar Parquet data directly, minimizing I/O. CTAS also integrates seamlessly with PolyBase's external table architecture, making it the optimal choice for minimizing load time.

Answer

Split the Parquet files into many small files (e.g., 64 MB each) to maximize parallelism.

Answer

Convert the Parquet files to CSV format before loading using PolyBase.

Answer

Use the COPY INTO command to load the Parquet files directly into the dedicated SQL pool.

Question 7

You are building a data pipeline in Azure Data Factory to copy data from an on-premises SQL Server database to Azure Blob Storage. The pipeline must run daily and handle incremental updates. The on-premises SQL Server table has a LastModifiedDate column that is updated when a row changes. What is the most efficient way to implement incremental loads?

Accepted Answer

Use a Lookup activity to get the maximum LastModifiedDate from the destination, then use a Copy activity with a query that filters rows where LastModifiedDate > that value.. It uses a Lookup activity to retrieve the maximum LastModifiedDate from the destination (Azure Blob Storage), then passes that value as a parameter to a Copy activity that queries only rows where LastModifiedDate exceeds it. This minimizes data transfer by reading only new or changed rows, and it avoids the overhead of enabling Change Data Capture or performing full loads, making it the most efficient approach for incremental loads in Azure Data Factory.

Answer

Enable Change Data Capture (CDC) on the SQL Server database and use an ADF mapping data flow to read changes.

Answer

Use a tumbling window trigger with a window size of 1 day and copy all data from the source each time.

Answer

Perform a full load every day and use a Delete activity to remove duplicates.

Question 8

Your company uses Azure Synapse Analytics and has deployed a pipeline that uses a Mapping Data Flow to transform data. The data flow reads from a source in Azure Blob Storage and writes to a dedicated SQL pool. You notice that the data flow is running slowly and consuming a lot of Data Flow cluster resources. You need to improve performance without increasing the cluster size. Which action should you take?

Accepted Answer

Add a partitioning step in the data flow to distribute the data across partitions based on a key column.. Adding a partitioning step in the Mapping Data Flow distributes data across partitions based on a key column, which allows parallel processing across the cluster's nodes. This reduces data shuffling and improves throughput without increasing the cluster size, directly addressing the performance bottleneck caused by skewed or unpartitioned data.

Answer

Use a self-hosted integration runtime instead of the default auto-resolve IR.

Answer

Increase the batch size in the data flow settings to reduce the number of round trips.

Answer

Change the source format to Delta Lake to leverage optimizations.

Question 9

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

Accepted Answer

Enable staging and use PolyBase as the copy method for the sink.. Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Answer

Change the copy behavior to 'sequential' to reduce load on the source.

Answer

Increase the degree of copy parallelism (DOP) to the maximum value supported.

Answer

Split the source data into multiple smaller files and use multiple copy activities running in parallel.

Question 10

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

Accepted Answer

Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.. The Input Order section in Azure Stream Analytics job configuration allows you to set a late arrival tolerance window, which handles late-arriving events by buffering them for a specified duration. This ensures that events arriving after their timestamp are still included in the correct window for aggregation, supporting exactly-once semantics when combined with checkpointing and idempotent output to Azure SQL Database.

Answer

Use a temporal window function with a 'late arrival' policy specified in the query.

Answer

Define a watermark in the query to specify a maximum out-of-order tolerance.

Answer

Set the event ordering policy to 'Adjust' to reorder events within a certain time window.

Question 11

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure the sink transformation to allow errors and log error rows to a separate file.. Option D is correct because in a Mapping Data Flow you can configure the sink transformation's error handling to 'Allow error' (or 'Continue on error') and redirect the failed rows to a separate error/log file, which lets the pipeline keep processing valid rows while capturing conversion failures for later analysis. Option E is correct because a Conditional Split transformation can route rows that meet a defined error condition (for example, a failed type cast or null check) into a separate branch, enabling graceful handling and isolation of problematic records within the data flow. Option A is not appropriate because Try-Catch is a pipeline-level control flow activity and cannot catch row-level conversion errors occurring inside a Mapping Data Flow's execution engine. Option B is incorrect because 'Abort on error' stops the entire data flow on the first failure, which is the opposite of graceful error handling. Option C is incorrect because schema drift handles changing or unexpected column structures, not data type conversion failures on existing columns.

Answer

Wrap the data flow in a Try-Catch activity in the pipeline.

Answer

Set the data flow's error handling to 'Abort on error' to stop processing on first failure.

Answer

Enable schema drift on the source to automatically handle data type mismatches.

Question 12

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Accepted Answer

Use a path pattern that includes {date} and {time} tokens.. Option A is correct because Azure Stream Analytics blob output supports dynamic path patterns using {date} and {time} tokens, which create date- and hour-based folder hierarchies such as {date}/{time} for easy querying. Option C is correct because selecting Avro or Parquet serialization produces structured, columnar or row-based files that pair well with partitioned blob paths and are efficient for downstream analytical queries. Option E is correct because enabling 'Write to blob storage partitioned by time' in the output settings activates time-based partitioning so Stream Analytics writes files into date/hour folders. Option B is not correct because the event ordering policy only controls late and out-of-order event handling, not output file partitioning. Option D is not correct because the compatibility level controls Stream Analytics SQL language behavior, not blob output partitioning.

Answer

Configure the event ordering policy to adjust late events.

Answer

Set the compatibility level to 1.2 or higher.

Question 13

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

Accepted Answer

The storage account creation will be denied and the request will fail.. The Azure Policy definition shown in the exhibit uses the 'Deny' effect for the 'Secure transfer to storage accounts should be enabled' policy. When a data engineer attempts to create a storage account with 'Enable secure transfer' set to Disabled, the policy evaluation occurs at resource creation time (before the resource is provisioned). Because the request violates the policy's condition (HTTPS traffic not enforced), Azure Resource Manager denies the creation request and returns a failure, preventing the non-compliant storage account from being created.

Answer

The storage account will be created, but the policy will be evaluated later during a compliance scan.

Answer

The storage account will be created with HTTPS enabled automatically.

Answer

The storage account will be created, but an audit event will be logged.

Question 14

Refer to the exhibit. You are querying the sys.external_tables view in an Azure Synapse Analytics serverless SQL pool. The query returns no rows, but you believe that external tables have been created. What is the most likely reason?

Accepted Answer

The external tables were created using OPENROWSET, not CREATE EXTERNAL TABLE, so they do not appear in sys.external_tables.. The user believes external tables have been created, but if they were created using CREATE EXTERNAL TABLE, they would appear in sys.external_tables. Since no rows are returned, it indicates that the external tables were not created via CREATE EXTERNAL TABLE; instead, the user likely used OPENROWSET to query data directly, which does not create a catalog entry. Therefore, the correct answer is C.

Answer

The external tables are using PolyBase, which is not supported in serverless SQL pool.

Answer

Serverless SQL pool does not support external tables; you must use a dedicated SQL pool.

Answer

The user does not have permission to view the sys.external_tables view.

Question 15

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

Accepted Answer

Yes, because the integration runtime is self-hosted and can access on-premises data sources.. The ARM template snippet defines a self-hosted integration runtime, which is designed to be installed on a local machine inside the corporate network. This allows it to directly connect to on-premises SQL Server databases using native drivers (e.g., ADO.NET) and then copy data to Azure Blob Storage via the Azure cloud. Self-hosted IRs are the correct choice for hybrid data movement scenarios where the source is on-premises and the destination is cloud-based.

Answer

No, you need an Azure integration runtime that is configured for on-premises access.

Answer

No, you need a managed virtual network integration runtime for on-premises sources.

Answer

Yes, but only if the integration runtime is installed on a domain-joined machine.