DP-203 Practice Test 13 — 15 Questions

Question 1

A retail company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The data is loaded nightly from Azure Data Lake Storage Gen2 using PolyBase. Recently, the load process started failing with the error 'External table 'sales' is not accessible because the location does not exist or is used by another process.' You verify that the storage account, container, and file path are correct. The file is a CSV file named 'sales_20250301.csv' and it exists. Other files in the same container load successfully. What is the most likely cause of the error?

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

Accepted Answer

The file is being written to or is locked by another process during the PolyBase read.. The error 'location does not exist or is used by another process' specifically indicates that the file is locked by another process. In Azure Data Lake Storage Gen2, when a file is being written or modified, it can be locked by the writing process (e.g., a data ingestion pipeline or another ETL job). PolyBase attempts to read the file while it is still being written, causing the error. Option D correctly identifies this concurrency issue.

Answer

The network connectivity between Synapse and the storage account is intermittent.

Answer

The CSV file has an incorrect number of columns or contains a header row that mismatches the schema.

Answer

The storage account key used in the external data source has expired.

Question 2

A company uses Azure SQL Database for an OLTP application. They need to run complex analytical queries without impacting OLTP performance. Which solution should they implement?

Accepted Answer

Create a readable secondary replica. Creating a readable secondary replica (Option C) offloads read-only analytical queries to a synchronized copy of the database, isolating them from the primary OLTP workload. Azure SQL Database supports this via Active Geo-Replication or Hyperscale named replicas, ensuring the primary remains unaffected by heavy analytical processing.

Answer

Enable automatic tuning

Answer

Enable geo-replication

Answer

Create a failover group

Question 3

A company uses Azure Data Lake Storage Gen2 as the data lake. The data engineering team needs to ensure that sensitive data such as credit card numbers are masked when queried by non-admin users. The solution must be implemented within the data lake without moving data to another store. Which TWO features should they use? (Choose two.)

Accepted Answer

Azure Synapse Serverless SQL with dynamic data masking. Azure Synapse Serverless SQL can query data directly from Azure Data Lake Storage Gen2 and supports dynamic data masking (DDM) to obfuscate sensitive columns like credit card numbers from non-admin users. This allows masking to be applied at query time without moving or transforming the underlying data in the lake.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies

Question 4

You are optimizing an Azure Synapse Analytics dedicated SQL pool that runs a mix of reporting and ETL workloads. The ETL jobs often encounter resource wait times due to concurrent reporting queries. You need to ensure that ETL jobs always get the resources they need. Which two actions should you take? (Choose two.)

Accepted Answer

Assign HIGH importance to the ETL workload classifier.. The correct actions are B and E. Assigning HIGH importance to the ETL workload classifier (B) ensures that ETL queries are prioritized over lower-importance reporting queries. Creating a workload group for ETL with a minimum resource percentage and assigning it to a dedicated resource pool (E) guarantees a baseline of resources for ETL, preventing resource starvation. Option A (increasing DWU) adds overall resources but does not guarantee ETL gets priority. Option C (result set caching) and Option D (materialized views) improve reporting performance but do not ensure ETL resource allocation.

Answer

Increase the DWU (Data Warehouse Units) to provide more overall resources.

Answer

Enable result set caching for reporting queries.

Answer

Create materialized views for common reporting aggregations.

Question 5

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.. Azure Stream Analytics supports custom path patterns for Blob Storage output, where {date} and {time} tokens automatically resolve to the processing date and hour (in UTC). This allows partitioning output files into a folder structure like 'YYYY/MM/DD/HH', enabling efficient querying by date and hour without post-processing.

Answer

Configure the event ordering policy to adjust late events.

Answer

Set the compatibility level to 1.2 or higher.

Question 6

An Azure Data Factory pipeline runs multiple times daily, loading data from an on-premises SQL Server to Azure Blob Storage. You notice that the pipeline sometimes fails due to transient network errors. You need to implement a retry policy with exponential backoff. Which configuration should you apply?

Accepted Answer

Set the activity's retry property to 3 and enable exponential backoff.. Azure Data Factory activity retry now supports exponential backoff. You can enable it by setting the retry property and enabling exponential backoff. Option B correctly configures 3 retries with exponential backoff. Option C only sets a fixed interval without exponential backoff. Options A and D are incorrect because retry is configured at the activity level, not pipeline or trigger level.

Answer

Set the pipeline's retry property to 3 and retry interval to 60 seconds.

Answer

Set the activity's retry property to 3 and retry secs to 60.

Answer

Set the trigger's retry policy to 3 with exponential backoff.

Question 7

Your company uses Azure Synapse Analytics for its enterprise data warehouse. The main fact table, OrdersFact, is distributed using hash on OrderID. It has 10 billion rows. The table is partitioned by month. Recently, the data engineering team added a new column 'OrderStatus' that is used in many queries with filters like 'WHERE OrderStatus = 'Shipped''. These queries are scanning the entire table because the partition pruning is not effective. You need to improve query performance for these status-based queries without redesigning the entire table. What should you do?

Accepted Answer

Create a non-clustered columnstore index on OrderStatus. Creating a non-clustered columnstore index on OrderStatus allows SQL Server to perform batch mode processing and predicate pushdown, enabling efficient rowgroup elimination without requiring a full table scan. This index stores OrderStatus in a compressed, columnar format that can be quickly filtered, and it does not require redesigning the existing hash distribution or monthly partitioning.

Answer

Repartition the table by OrderStatus

Answer

Change the distribution key to include OrderStatus

Answer

Create a materialized view that aggregates by OrderStatus and month

Question 8

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 9

Refer to the exhibit. An ARM template deploys an Azure Synapse Analytics workspace. What is the purpose of the 'managedVirtualNetwork' property set to 'default'?

Accepted Answer

It enables the workspace to use a managed virtual network for network isolation. Setting 'managedVirtualNetwork' to 'default' in an ARM template for Azure Synapse Analytics enables a managed virtual network that provides network isolation for the workspace. This allows the workspace to use private endpoints and managed private endpoints for secure data integration without exposing traffic to the public internet. It is a foundational setting for implementing a secure, network-isolated Synapse environment.

Answer

It configures the workspace to use a user-assigned managed identity

Answer

It disables public network access to the workspace

Answer

It creates the workspace in a private endpoint configuration

Question 10

You need to design a storage solution for a data lake that will be used by multiple teams for analytics. The solution must support fine-grained access control, versioning of files, and integration with Azure Purview for data cataloging. Which THREE features should you enable in Azure Data Lake Storage Gen2?

Accepted Answer

Integration with Microsoft Purview. Microsoft Purview integration is required for data cataloging, enabling automated scanning, classification, and lineage tracking of data stored in Azure Data Lake Storage Gen2. This directly supports the requirement for data cataloging in the scenario.

Answer

Managed identity authentication

Answer

Soft delete

Question 11

You are designing a data processing solution that must handle personally identifiable information (PII). The data will be ingested from multiple sources into Azure Data Lake Storage. Which TWO actions should you take to protect the PII data during processing?

Accepted Answer

Use column-level security in Azure Synapse Analytics to restrict access to sensitive columns.. Column-level security in Azure Synapse Analytics allows you to restrict access to specific columns containing PII, such as Social Security numbers or email addresses, without altering the underlying data. This is a native feature of Synapse that can be applied during processing to ensure that only authorized users or roles can view sensitive columns, directly addressing the requirement to protect PII during data processing.

Answer

Apply data masking in Power BI reports to hide PII from end users.

Answer

Configure the Azure Data Lake Storage firewall to allow only specific IP addresses.

Answer

Implement SQL Server Always Encrypted for all data stored in the data lake.

Question 12

You are optimizing an Azure Synapse Analytics pipeline that uses mapping data flows. The pipeline runs slowly when processing 100 GB of data. Which THREE settings should you adjust to improve performance?

Accepted Answer

Set 'partition option' to 'Round robin' with a higher number of partitions.. Options A, C, and E are correct. Setting a higher number of partitions with Round robin partitioning improves parallelism across nodes. Increasing Compute type to Memory Optimized and increasing cores provides more resources for data processing. Using Optimize shuffle reduces data movement during joins and aggregations, improving performance. Option B (tumbling window trigger) is a scheduling mechanism, not a performance tuning setting. Option D (Data flow debug) is for development and debugging, not for production performance optimization.

Answer

Set a tumbling window trigger to run the pipeline every 5 minutes.

Answer

Enable 'Data flow debug' to monitor execution details.

Question 13

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 (ADLS Gen2) as its data lake. A team of data scientists needs to process a massive dataset (approximately 5 TB) stored in Parquet format in the data lake. The dataset contains sales transactions from the past 10 years. The data scientists run a Spark job daily using Azure Synapse Analytics (serverless Spark pool) to compute aggregated sales metrics by product category and region. The job reads the entire dataset each day, performs transformations, and writes the aggregated results back to the data lake. Over the past few weeks, the job has been taking longer to complete, and the data scientists have reported that the job now takes over 6 hours, exceeding the acceptable SLA of 4 hours. They suspect the issue is related to data skew or suboptimal partitioning. You need to optimize the job to reduce execution time. Which approach should you take?

Accepted Answer

Implement incremental processing using Auto Loader with 'directoryListing' mode to process only new files since the last run.. The job reads the entire 5 TB dataset daily, which is inefficient when only new data needs processing. Auto Loader with 'directoryListing' mode incrementally identifies and processes only new files since the last run, drastically reducing the data volume and execution time. This directly addresses the root cause of the SLA breach—reading unchanged historical data repeatedly—rather than tuning resources or partitioning.

Answer

Increase the executor memory and cores in the Spark pool configuration to handle larger shuffles.

Answer

Repartition the data on the 'product_category' column with a higher number of partitions (e.g., 2000).

Answer

Use a broadcast join hint on the fact table to reduce shuffle operations.

Question 14

A company uses Azure Synapse Analytics with dedicated SQL pools. They notice that query performance degrades significantly during peak hours. They have already scaled up the Data Warehouse Units (DWU) to the maximum. Which action should they take next to improve performance?

Accepted Answer

Enable result-set caching.. When a dedicated SQL pool is already at maximum DWU, further scaling is not possible. Enabling result-set caching stores query results in the SSD-based cache of the SQL pool, allowing repeated queries to be served directly from cache without re-scanning data or re-computing aggregations. This reduces I/O and CPU pressure during peak hours, improving performance for recurring queries without requiring additional compute resources.

Answer

Rebuild all clustered columnstore indexes.

Answer

Increase the number of concurrency slots.

Answer

Move the data to Azure Data Lake Storage Gen2.

Question 15

Refer to the exhibit. You are reviewing a Data Factory JSON definition. The factory has a user-assigned managed identity configured. However, the linked service to Azure Storage uses an account key. What security improvement should you recommend?

Accepted Answer

Modify the linked service to use the managed identity for authentication. The recommended security improvement is to modify the linked service to use the managed identity for authentication (option B). The user-assigned managed identity is already configured but not being used; instead, the linked service is using an account key, which requires storing a secret. Using the managed identity eliminates the need for secrets and leverages Azure AD authentication, which is more secure and easier to manage. Option A (firewall rule) addresses network access but not authentication. Option C (remove managed identity and use service principal) would replace one identity-based approach with another, but the managed identity is already in place and should be utilized. Option D (store key in Key Vault) still involves storing a secret, which is less secure than using the managed identity directly when possible.

Answer

Add a firewall rule to limit access to the storage account

Answer

Remove the managed identity and use a service principal

Answer

Keep the account key but store it in Azure Key Vault