DP-203 Practice Test 32 — 15 Questions

Question 1

Match each Azure storage service to its primary use case. Select the Azure storage services from the options below.

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is used for big data analytics, Azure Cosmos DB for globally distributed, low-latency applications, Azure SQL Database for relational OLTP, and Azure Table Storage for NoSQL key-value stores. Options A through D describe the use cases, not the services, so they are not selected. The correct answers are E, F, G, and H.

Answer

Big data analytics

Answer

Globally distributed, low-latency applications

Answer

Relational OLTP

Answer

NoSQL key-value store

Question 2

Refer to the exhibit. A data engineer runs a Synapse Spark job that fails with the error shown. Which configuration change is most likely to resolve the issue?

Accepted Answer

Increase executor memory to 4g for Job1. The error indicates an out-of-memory (OOM) condition in the Spark executor. Increasing executor memory to 4g for Job1 provides more heap space for data processing, which directly resolves the memory exhaustion. This is the most appropriate fix because the error is specifically about memory, not CPU or parallelism.

Answer

Change the executor cores to 1

Answer

Switch to a different Spark pool with the same configuration

Answer

Reduce the number of executors to 1 for Job1

Question 3

A data engineer is designing a solution that uses Azure Data Factory to copy data from an on-premises SQL Server to Azure Synapse Analytics. The data transfer must be encrypted in transit. Which property should be configured in the linked service?

Accepted Answer

EncryptedConnection. The EncryptedConnection property in an Azure Data Factory linked service enforces encryption for data in transit between the on-premises SQL Server and Azure Synapse Analytics. When set to true, it uses TLS/SSL to encrypt the connection, ensuring that data transferred over the network is protected from interception or tampering.

Answer

ConnectionString with Integrated Security

Answer

AuthenticateVia

Answer

UseSystemTrustStore

Question 4

You are a data engineer for a financial services company. The company stores sensitive transaction data in Azure Data Lake Storage Gen2. The data is partitioned by date and loaded daily via Azure Data Factory. Recently, an audit found that the storage account allows public network access, and some containers have anonymous read access enabled. You need to secure the storage account according to the principle of least privilege while ensuring that Azure Data Factory can still load data. You must also ensure that data can be accessed by Azure Databricks for analytics. The solution must minimize administrative overhead. Which course of action should you take?

Accepted Answer

Disable public network access. Set the storage account firewall to allow access only from Azure services. Configure Azure Data Factory and Azure Databricks to use managed identities. Grant the managed identities the 'Storage Blob Data Contributor' role at the container level. Remove any anonymous access.. Disabling public network access and using managed identities with RBAC (Storage Blob Data Contributor) aligns with the principle of least privilege while minimizing administrative overhead. Azure Data Factory and Azure Databricks can authenticate via managed identities without managing keys or IP ranges, and removing anonymous access eliminates the security gap. The firewall rule allowing access only from Azure services ensures that only Azure-internal traffic can reach the storage account, which is sufficient for these services when they are in the same region.

Answer

Keep public network access enabled but restrict it to specific IP addresses. Use storage account keys for Azure Data Factory and Azure Databricks.

Answer

Disable public network access. Create private endpoints for the storage account and configure Azure Data Factory and Azure Databricks to use the private endpoints. Use RBAC to assign 'Storage Blob Data Contributor' to the managed identities.

Answer

Enable public network access with a firewall rule to allow only the Azure Data Factory and Azure Databricks IP ranges. Keep anonymous access enabled but set the containers to private.

Question 5

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

Accepted Answer

Use mapping data flows in Azure Synapse pipelines.. Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless. Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Answer

Use Azure Stream Analytics to transform the data in real time.

Answer

Use PolyBase to load data and then use T-SQL stored procedures to transform.

Answer

Use Azure Databricks notebooks with Spark to perform transformations.

Question 6

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

Accepted Answer

Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.. Partitioning the Sales table on OrderDate enables partition elimination, which allows the query optimizer to scan only the relevant partitions when filtering by date ranges. This reduces I/O and improves performance significantly, as the fact table is already hash-distributed on ProductKey, and partitioning on a different column used in WHERE clauses is a best practice in Azure Synapse Analytics dedicated SQL pools.

Answer

Create a non-clustered index on OrderDate.

Answer

Add ProductKey as a distribution column alongside the existing hash.

Answer

Change the distribution to round-robin to spread data evenly.

Question 7

An organization is using Azure Synapse Analytics and wants to implement column-level security to restrict access to sensitive columns. Which feature should they use?

Accepted Answer

Column-level security using GRANT. Column-level security in Azure Synapse Analytics is implemented using GRANT statements on specific columns, restricting access to sensitive columns. Option A is incorrect because dynamic data masking obfuscates data at query time but does not prevent access. Option B is incorrect because Azure Purview is a data governance service, not for access control. Option D is incorrect because row-level security filters rows, not columns.

Answer

Dynamic data masking

Answer

Azure Purview

Answer

Row-level security

Question 8

You are designing a data storage solution for a global e-commerce company. The company's analytics team uses Azure Synapse Serverless SQL to query Parquet files in ADLS Gen2. The data is partitioned by year, month, and day. The team frequently runs queries that aggregate sales by product category across the last 30 days. However, the queries are slow and scanning too much data. What should you do to improve performance?

Accepted Answer

Create a view that uses explicit partition pruning by filtering on year, month, and day in the WHERE clause.. Creating a view with explicit partition pruning by filtering on year, month, and day in the WHERE clause forces Synapse Serverless SQL to read only the relevant partitions (the last 30 days) instead of scanning the entire dataset. This reduces the amount of data scanned, directly improving query performance and lowering cost.

Answer

Increase the number of files per partition to improve parallelism.

Answer

Convert the Parquet files to CSV format to reduce file size.

Answer

Add a new partition by product category to the folder structure.

Question 9

You are a data engineer for a large e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool as its enterprise data warehouse. A new business requirement mandates that the Sales fact table, which contains 2 billion rows, must support real-time analytics with a maximum query latency of 1 second for aggregations on the most recent 24 hours of data. The table is currently hash-distributed on CustomerID and partitioned monthly by SaleDate. The current query performance for recent data is slow due to full partition scans. The data is ingested via Azure Event Hubs and processed by Azure Stream Analytics, which writes to staging tables every minute. You need to redesign the storage to meet the latency requirement while minimizing cost and maintaining data integrity. Which approach should you take?

Accepted Answer

Create a separate staging table for the last 24 hours of data with round-robin distribution and a clustered columnstore index. After each batch load, merge the staging table into the main partitioned Sales table. Queries for recent data should target the staging table.. It isolates the hot (recent 24-hour) data into a separate staging table with a clustered columnstore index, which is optimized for fast aggregations and high compression. This avoids full partition scans on the 2-billion-row main table, and merging into the partitioned table after each batch load maintains data integrity without requiring expensive repartitioning or additional dedicated SQL pools.

Answer

Increase partition granularity to hourly partitions for the Sales table.

Answer

Change the Sales table to use a clustered index on SaleDate and a round-robin distribution.

Answer

Provision a second dedicated SQL pool optimized for real-time queries and replicate the recent data there.

Question 10

Refer to the exhibit. A data engineer notices that Spark jobs on this cluster are running slower than expected. The cluster is using spot instances with fallback. Which factor is most likely causing the performance degradation?

Accepted Answer

The cluster is using spot instances which may be frequently reclaimed. Spot instances can be preempted, causing delays. The configuration sets 'first_on_demand' to 1, meaning only 1 node is on-demand, and the rest are spot. Spot instances can be reclaimed, leading to recomputation and slower performance. The adaptive query execution settings are generally beneficial, not harmful.

Answer

The spark.sql.adaptive settings are misconfigured

Answer

The node type Standard_DS3_v2 is too small

Answer

The autoscale configuration limits max workers to 8

Question 11

Your company uses Azure Data Factory to load data from an on-premises SQL Server database to Azure Synapse Analytics. The pipeline runs daily and the load volume is about 50 GB per run. Recently, the pipeline started failing with timeout errors during the copy activity. You need to ensure reliable data transfer. What should you do?

Accepted Answer

Use a self-hosted integration runtime and increase the copy activity timeout. For on-premises data sources, a self-hosted integration runtime (IR) is required to connect to Azure Data Factory. Increasing the copy activity timeout helps handle large data volumes (50 GB) and prevents timeout errors. Option B (PolyBase) is a technique for loading data into Azure Synapse but does not address the connectivity or timeout issue. Option C (splitting into smaller activities) could reduce load per activity but does not directly resolve the timeout; increasing timeout is more direct. Option D (Azure IR) cannot access on-premises resources, so it is not applicable.

Answer

Use PolyBase to load data in parallel

Answer

Split the copy into multiple smaller activities

Answer

Switch to Azure Integration Runtime

Question 12

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 13

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

Accepted Answer

The copy activity is not using staging and PolyBase. The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Answer

The source and sink are in different Azure regions

Answer

The Data Integration Unit (DIU) setting is too low

Answer

The source files are compressed

Question 14

Your organization uses Azure Data Lake Storage Gen2 (ADLS Gen2) and wants to transform data using Azure Databricks. The data is stored in Parquet format. You need to read the data into a Spark DataFrame. Which DataFrame reader method should you use?

Accepted Answer

spark.read.parquet(). The data is stored in Parquet format, and the Spark DataFrame reader method `spark.read.parquet()` is specifically designed to read Parquet files, which is a columnar storage format optimized for big data processing in Azure Databricks.

Answer

spark.read.avro()

Answer

spark.read.json()

Answer

spark.read.csv()

Question 15

You are designing a batch processing solution for a financial services company that processes transactions from multiple sources. The data is stored in Azure Data Lake Storage Gen2 in Parquet format. You need to perform complex transformations including joins, aggregations, and window functions, and then load the results into an Azure Synapse Analytics dedicated SQL pool. The transformations must be written in SQL and executed on a serverless Spark cluster to minimize costs. You want to manage the code in a Git repository and automate the deployment using Azure DevOps. Which approach should you recommend?

Accepted Answer

Create a Spark job definition in Azure Synapse that reads Parquet files, performs transformations using PySpark, and writes to the dedicated SQL pool using the Spark Synapse connector.. Using a Spark job definition in Azure Synapse allows you to run PySpark transformations on a serverless Spark cluster, which meets the requirement for SQL-based transformations via Spark SQL. The Spark Synapse connector enables efficient writing to a dedicated SQL pool. This approach minimizes costs by leveraging serverless Spark, and the code can be managed in Git and deployed with Azure DevOps. Option A uses Azure Databricks, which is a separate service not integrated with Synapse serverless Spark. Option B uses Mapping Data Flows, which runs on Spark but is not a serverless Spark cluster in the same sense and may incur higher costs. Option D uses a serverless SQL pool, which is not a Spark cluster and thus does not fulfill the requirement to execute on a serverless Spark cluster.

Answer

Use Azure Databricks with Auto Loader to read from Data Lake Storage, perform transformations using Spark SQL, and write to the dedicated SQL pool via JDBC.

Answer

Use Azure Data Factory with Mapping Data Flows to visually design transformations and write to the dedicated SQL pool.

Answer

Use a serverless SQL pool in Azure Synapse to query the Parquet files using T-SQL, then use CETAS to write the results to the dedicated SQL pool.