DP-203 Practice Test 3 — 30 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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.

Question 11

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

Accepted Answer

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0. A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Answer

Add a filter in the copy activity to exclude rows with NULL Amount

Answer

Modify the sink table to have a default value for the Amount column

Answer

Change the sink table column to allow NULLs

Question 12

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

Accepted Answer

Increase spark.sql.shuffle.partitions to 800.. Increasing spark.sql.shuffle.partitions to 800 improves parallelism for a 200 GB shuffle-heavy workload, reducing partition sizes and shuffle time. Option A is incorrect because executor count alone does not directly address shuffle partitioning. Option B may help but is less impactful than partition tuning. Option D would worsen the problem by making partitions larger.

Answer

Increase the number of executors to 4.

Answer

Change executor size to 'Large' to increase memory per executor.

Answer

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Question 13

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database. Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Answer

Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database

Answer

Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly

Answer

Use Azure Functions to process events and write to SQL Database

Question 14

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

Accepted Answer

You can query the external table using standard T-SQL SELECT statements.. An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer

Querying the external table automatically imports data into a round-robin distribution.

Answer

The table cannot be queried until the data is imported into the dedicated SQL pool.

Answer

You must first create a PolyBase external table before querying.

Question 15

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Accepted Answer

The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.. The error occurs because the Data Lake Storage Gen2 account uses a customer-managed key from a key vault for encryption. The account's managed identity must have the 'Get' and 'Wrap Key' permissions on the key vault to access the key. Without these permissions, the deployment fails. Option B correctly identifies this. Option A (soft-delete) is not required for initial access but is recommended for protection. Option C (key name/version) would cause a different error (key not found). Option D (URI format) would also cause a different error.

Answer

The key vault does not have soft-delete enabled.

Answer

The key name or version is incorrect.

Answer

The key vault URI is incorrectly formatted.

Question 16

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

Accepted Answer

Create views that filter on partition columns.. Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Answer

Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

Answer

Use OPENROWSET with the DATA_SOURCE parameter.

Answer

Increase the number of files per partition.

Question 17

You need to audit all data access to an Azure Storage account. Which Azure service should you enable?

Accepted Answer

Azure Storage analytics logs and send to Log Analytics workspace. Azure Storage analytics logs capture detailed information about successful and failed requests to a storage account, including authentication details, IP addresses, and operation types. By sending these logs to a Log Analytics workspace, you can query and analyze them using KQL, enabling comprehensive auditing of all data access. This is the correct service for auditing because it provides the granular, queryable logs required for security and compliance audits.

Answer

Azure Policy to audit storage account access

Answer

Azure Monitor metrics

Answer

Azure Security Center

Question 18

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

Accepted Answer

Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers. Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Answer

Store all data in Azure SQL Database with partitioning and drop older partitions

Answer

Use Azure Data Lake Storage Gen2 with a single storage tier

Answer

Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

Question 19

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

Accepted Answer

Azure Data Explorer (ADX).. Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and log analytics, ingesting 10,000+ events per second with sub-second query latency via its columnar storage and distributed query engine. It supports real-time analytics on streaming IoT data without requiring pre-defined schemas or indexing, making it the optimal choice for this scenario.

Answer

Azure Table Storage.

Answer

Azure SQL Database with in-memory OLTP.

Answer

Azure Cosmos DB with analytical store.

Question 20

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database