DP-203 Practice Exam 55 — 20 Questions

Question 1

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 know 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.. In Azure Synapse Analytics serverless SQL pool, external tables are created using the CREATE EXTERNAL TABLE statement, and they are listed in the sys.external_tables view. However, if you query data directly via OPENROWSET without creating an external table, those ad-hoc queries do not register any metadata in sys.external_tables. Since the question states that external tables have been created but the view returns no rows, the most likely reason is that the tables were actually created using OPENROWSET, not CREATE EXTERNAL TABLE, so they are not cataloged in the system view.

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 2

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

Accepted Answer

Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations. Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Answer

Cache the data in memory after reading

Answer

Use bucketing with a fixed number of buckets

Answer

Partition the data by a high-cardinality column

Question 3

You are optimizing an Azure Synapse serverless SQL pool query that queries Parquet files in Azure Data Lake Storage. The query takes longer than expected. You notice that the query reads more data than necessary. What is the most effective way to reduce the amount of data scanned?

Accepted Answer

Create external tables with explicit schema and partition by a frequently filtered column. Option B is correct because partitioning external tables in Azure Synapse serverless SQL allows the query engine to perform partition elimination, reading only the subdirectories that match the filter criteria. This directly reduces the amount of data scanned from Parquet files in ADLS, addressing the core issue of reading unnecessary data.

Answer

Split large Parquet files into smaller files of 100 MB each

Answer

Use SELECT with column pruning to only retrieve necessary columns

Answer

Increase the query's resource allocation by using a larger service level objective

Question 4

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Select two.)

Accepted Answer

Azure Data Factory. Azure Data Factory is a cloud-based ETL service that provides a code-free visual interface for orchestrating data movement and transformation at scale. It supports data flows, which allow you to perform transformations like aggregations, joins, and filtering without writing code, making it a correct choice for data transformation in a pipeline.

Answer

Azure Monitor

Answer

Azure Storage

Answer

Azure Event Hubs

Question 5

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. You have Parquet files in Azure Data Lake Storage Gen2 partitioned by date and hour. You need to query the data for the last 7 days efficiently, minimizing the amount of data scanned. Which approach should you use?

Accepted Answer

Use OPENROWSET with a file path that limits to the last 7 days' partitions and use a WHERE clause on the date column.. Option D is correct because using OPENROWSET with a file path that restricts to the last 7 days' partitions (e.g., '/date=2025-03-*/hour=*/*.parquet') minimizes data scanned by only reading the relevant Parquet files. Applying a WHERE clause on the date column further filters rows within those files, leveraging partition pruning at the file system level. This approach avoids scanning all partitions, which is critical for cost and performance in serverless SQL pool.

Answer

Use OPENROWSET with a wildcard path that includes all partitions and then apply a WHERE clause.

Answer

Create an external table with partition definition and query with a WHERE clause on the partition column.

Answer

Create a view using PolyBase external table and query with a date filter.

Question 6

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

Accepted Answer

Scale out the self-hosted IR by adding more nodes to the cluster.. Option C is correct because scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because moving to Azure-SSIS IR is expensive and unnecessary. Option B is wrong because increasing polling interval does not fix timeouts. Option D is wrong because Azure ExpressRoute is a costly network upgrade.

Answer

Implement Azure ExpressRoute to provide dedicated bandwidth.

Answer

Increase the 'Polling Interval' setting in the copy activity.

Answer

Migrate the self-hosted IR to Azure-SSIS IR.

Question 7

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.. Option C is correct because Azure Synapse pipelines support mapping data flows, which are serverless and provide a visual interface for transformations. Option A is wrong because PolyBase is for loading, not transformation. Option B is wrong because Azure Databricks would require a cluster. Option D is wrong because Azure Stream Analytics is for streaming, not batch transformations.

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 8

Your company has a data lake in Azure Data Lake Storage Gen2 that stores sensitive customer information. You need to implement fine-grained access control so that data engineers can read all data, data scientists can read only anonymized data, and auditors can view access logs. The solution must use Azure role-based access control (RBAC) and access control lists (ACLs). You also need to enable auditing of read operations. What should you do?

Accepted Answer

Assign Storage Blob Data Contributor RBAC role to data engineers at the storage account level, use ACLs to deny read access to raw data for data scientists, and enable diagnostic settings for read requests to Log Analytics.. Option D is correct: Use Azure RBAC roles for coarse permissions (e.g., Storage Blob Data Contributor for engineers), use ACLs for fine-grained control (e.g., deny read for data scientists on raw data), and enable diagnostic settings to log read operations to Log Analytics. Option A is incorrect: Storage Analytics logs are deprecated. Option B is incorrect: Lifecycle management is for tiering, not access control. Option C is incorrect: RBAC only cannot provide granular control at file level.

Answer

Use Azure RBAC to assign Storage Blob Data Reader to data scientists, and configure lifecycle management to move raw data to archive tier.

Answer

Assign RBAC roles at the storage account level and enable Storage Analytics logs for read operations.

Answer

Assign RBAC roles at the container level to grant read access to all users, and use Azure Policy to audit access.

Question 9

You are responsible for managing an Azure Data Lake Storage Gen2 account that stores parquet files for analytics. You need to implement a data retention policy that automatically deletes files older than 90 days in the 'logs' container. Additionally, you need to ensure that no data is lost due to accidental deletion; you want to be able to recover deleted files within 30 days. You also need to monitor the storage account for unusual access patterns. The solution must minimize administrative effort. What should you do?

Accepted Answer

Enable soft delete with a retention period of 30 days and configure a lifecycle management rule to delete blobs older than 90 days. Option B is correct because enabling soft delete provides recovery within the retention period (30 days), and using a lifecycle management policy can automatically delete files older than 90 days. Option A is wrong because Azure Policy does not manage retention or recovery. Option C is wrong because snapshots are for blobs, not for ADLS Gen2 directories. Option D is wrong because it does not provide automatic deletion based on age.

Answer

Create an Azure Policy to enforce tag-based retention and use Azure Monitor to alert on access

Answer

Enable versioning and configure a retention policy in Azure Policy

Answer

Use Azure Backup for the storage account and manually delete old files

Question 10

You have an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage. The pipeline runs successfully most of the time, but occasionally fails with a 'FTP server connection refused' error during peak hours. You need to minimize these failures with minimal cost. What should you do?

Accepted Answer

Add a retry policy to the copy activity with a backoff interval.. Option D is correct because adding a retry policy with a short delay handles transient connection issues. Option A is wrong because increasing the number of parallel copies does not solve connection refused errors. Option B is wrong because Azure ExpressRoute is costly and unnecessary for FTP. Option C is wrong because migrating to SFTP may not resolve connection refused if the server is overloaded.

Answer

Set up Azure ExpressRoute to improve network reliability.

Answer

Migrate the FTP server to SFTP.

Answer

Increase the parallel copy count in the copy activity.

Question 11

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to monitor for unauthorized access attempts and ensure compliance with regulatory requirements. You want to be alerted when someone attempts to access data without proper permissions. What should you configure?

Accepted Answer

Enable diagnostic settings for the storage account, send logs to Log Analytics, and create an alert rule for 'StorageRead' failures.. Option D is correct because diagnostic settings send logs to Log Analytics, and Azure Monitor alerts can be created to detect unauthorized access. Option A is wrong because Azure Policy is for governance, not real-time alerts. Option B is wrong because RBAC is for access control, not monitoring. Option C is wrong because Azure Sentinel is for SIEM, but simpler solution is Log Analytics alerts.

Answer

Assign RBAC roles to limit access and rely on Azure Activity Log.

Answer

Configure Azure Policy to audit storage account access.

Answer

Enable Azure Sentinel and connect storage logs.

Question 12

Your organization uses Azure SQL Database with Active Geo-Replication for disaster recovery. You need to ensure that all connections to the database use Microsoft Entra ID authentication and that access is audited. You also want to minimize the attack surface by disabling SQL authentication. What should you do?

Accepted Answer

Enable 'Azure AD-only authentication' in the Azure SQL Database server settings and remove all SQL Server authenticated logins.. Option C is correct: Set 'Azure AD-only authentication' to 'True' in the Azure SQL Database server properties. This disables SQL authentication and enforces Entra ID. Option A is incorrect: Azure RBAC controls management plane, not database access. Option B is incorrect: SQL authentication is still enabled. Option D is incorrect: Conditional Access policies work with Entra ID but don't disable SQL authentication.

Answer

Configure Conditional Access policies to require MFA for database access.

Answer

Create a server-level firewall rule to allow only specific IP addresses and enable SQL authentication.

Answer

Create an Azure RBAC role to restrict access to the database and assign it to users.

Question 13

Contoso Ltd. runs a real-time analytics solution on Azure Databricks with data streaming from Event Hubs. They need to ensure that all data in transit between Event Hubs and Databricks is encrypted using TLS 1.2 or higher. Currently, the Event Hubs namespace is configured with the default TLS version (1.0). The Databricks cluster uses a public endpoint. Compliance requires that only TLS 1.2 is accepted. You need to configure the environment to enforce TLS 1.2 without disrupting ongoing streaming. What should you do?

Accepted Answer

Update the Event Hubs namespace to require TLS 1.2, then modify the Databricks streaming job's connection string to include 'TransportType=AmqpTls' and restart the streaming job.. Step 1: Update the Event Hubs namespace to require TLS 1.2 via 'Minimum TLS version' setting. Step 2: Configure the Databricks cluster to connect using TLS 1.2 by setting spark.conf to 'spark.eventhubs.connectionString' with TLS parameter. Step 3: Restart the streaming job to apply new connection settings. Option D includes all steps in order. Option A reboots without config change. Option B uses Azure CLI but doesn't cover Databricks. Option C only updates Event Hubs but not Databricks.

Answer

Change the Event Hubs namespace minimum TLS version to 1.2 in the Azure portal, then reboot the Databricks cluster.

Answer

In the Event Hubs namespace, set 'Minimum TLS version' to 1.2 and redeploy the Databricks cluster with a new init script that forces TLS 1.2.

Answer

Use Azure CLI to set the Event Hubs namespace TLS version to 1.2 and update the Databricks cluster's Spark configuration to use TLS 1.2.

Question 14

You are optimizing an Azure Synapse Analytics dedicated SQL pool that is experiencing high concurrency and frequent resource class contention. You need to improve query performance and reduce contention without changing the workload. Which two actions should you take? (Choose two.)

Accepted Answer

Implement workload isolation to separate critical queries into dedicated resource groups.. Options B and D are correct. Workload isolation and workload classification help manage resource allocation and reduce contention. Option A is wrong because increasing DWU may help but does not address contention directly. Option C is wrong because result set caching helps read workloads but not contention. Option E is wrong because materialized views improve performance but do not reduce contention.

Answer

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

Answer

Create materialized views to pre-aggregate data.

Answer

Enable result set caching to reduce repeated query execution.

Question 15

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 16

Refer to the exhibit. A data engineer notices that the target SQL table contains duplicate rows after a pipeline run. Which change to the pipeline configuration would prevent duplicates?

Accepted Answer

Add a 'Upsert' setting in SqlSink with a key column. The preCopyScript truncates the table before copy, but if the pipeline is run multiple times and truncation fails or is skipped, duplicates can occur. Using upsert semantics or adding a watermark could help, but the simplest fix is to ensure truncation is reliable. However, the best practice is to use a merge/upsert pattern. Among the options, adding a surrogate key and using upsert is most effective.

Answer

Remove the 'preCopyScript'

Answer

Change 'writeBatchSize' to 5000

Answer

Set 'recursive' to false

Question 17

You are designing a data storage solution for a marketing analytics platform. The platform collects clickstream data from websites and needs to store it for both real-time dashboards and historical analysis. The data is semi-structured (JSON) and arrives at a rate of 10,000 events per second. You need to choose an Azure storage solution that can handle the ingestion rate, support schema-on-read, and integrate with Azure Databricks for advanced analytics. The solution must also be cost-effective for long-term storage. What should you use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, providing high-throughput ingestion (up to 60 GB/s per account) to handle 10,000 events per second of semi-structured JSON data. It supports schema-on-read natively, allowing Azure Databricks to query the data directly using Spark without prior schema definition, and its tiered storage (hot, cool, archive) makes it cost-effective for long-term historical analysis.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 18

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.. Option C is correct because 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 19

You are a data engineer for a financial services company. The company uses Azure Data Lake Storage Gen2 as its data lake. You have a directory structure where each customer has a folder containing transaction files in CSV format. The security team requires that each customer's data be accessible only to that customer's users. You need to implement fine-grained access control using Azure Data Lake Storage Gen2's POSIX-like ACLs. However, you have thousands of customers, and managing ACLs individually is not feasible. What should you do?

Accepted Answer

Create an Azure AD group for each customer, add users to the group, and assign ACLs to the group on the customer folder. Option D is correct because Azure Data Lake Storage Gen2 supports POSIX-like ACLs that can be assigned to Azure AD security groups. By creating one Azure AD group per customer, adding the customer's users to that group, and then assigning the group the appropriate read/execute ACLs on the customer's folder, you achieve scalable, fine-grained access control without managing thousands of individual user ACLs. This approach aligns with the principle of least privilege and simplifies administration.

Answer

Create a shared access signature (SAS) token for each customer and distribute it securely

Answer

Use POSIX ACLs on each customer folder, assigning permissions to individual user identities

Answer

Use row-level security in Azure Data Lake Storage Gen2

Question 20

You are a data engineer for a gaming company that uses Azure Data Lake Storage Gen2. The data lake stores player event data in JSON format. The data is organized by date and event type. The analytics team frequently runs queries that filter by player ID to analyze individual player behavior. These queries are slow because they scan entire daily partitions. You need to improve the performance of queries that filter by player ID without restructuring the entire data lake. The data is stored as JSON files. What should you do?

Accepted Answer

Convert the data from JSON to Parquet format and partition by player ID. Option B is correct because converting JSON to Parquet enables columnar storage, which significantly reduces I/O by reading only the columns needed for queries. Partitioning by player ID further improves performance by allowing partition elimination, so queries filter only the relevant partitions instead of scanning entire daily partitions. This approach directly addresses the slow queries without restructuring the entire data lake.

Answer

Compress the JSON files using gzip to reduce I/O

Answer

Create indexes on the player ID field in the data lake

Answer

Repartition the data by hour to reduce the data scanned per partition

DP-203 Practice Exam 55 — 20 Questions

Exhibit

DP-203 Practice Exam 55 — 20 Questions

Exhibit