DP-203 Practice Test 41 — 15 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 believe that external tables have been created. What is the most likely reason?

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

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 2

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.. Enabling diagnostic settings for the storage account and sending logs to Log Analytics allows you to create alert rules that trigger on 'StorageRead' failures, indicating unauthorized access attempts. Option A is incorrect because while RBAC roles limit access, the Azure Activity Log only records management plane operations, not data plane access such as read failures. Option B is incorrect because Azure Policy is for governance and compliance of resource configurations, not for monitoring and alerting on access events. Option D is incorrect because although Azure Sentinel could be used, it is a more complex SIEM solution; the question asks for what should be configured, and the simpler, direct approach is to use diagnostic settings with 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 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. 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

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.. To enforce TLS 1.2 without disrupting ongoing streaming, you must first update the Event Hubs namespace to require TLS 1.2 via the 'Minimum TLS version' setting. Then, configure the Databricks streaming job's connection string to include 'TransportType=AmqpTls' to ensure the client uses TLS 1.2. Finally, restart the streaming job to apply the new connection settings. Option A correctly describes these steps. Option B incorrectly suggests rebooting the cluster, which is unnecessary and would disrupt streaming. Option C incorrectly suggests redeploying the cluster with an init script, which is not needed and causes disruption. Option D includes an unnecessary Spark configuration change; the connection string parameter (TransportType=AmqpTls) is sufficient.

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 5

You are designing a data processing pipeline using Azure Data Factory. The pipeline must ingest data from an HTTP endpoint that returns a JSON array. The data must be transformed by flattening nested arrays and then loaded into an Azure SQL Database table. The pipeline should be triggered daily. You need to choose the appropriate activities and transformations. The solution must be cost-effective and easy to maintain. Which combination of activities should you use?

Accepted Answer

Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage, then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database.. The correct approach is Option B: Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage (staging), then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database. This is cost-effective and maintainable because it separates ingestion and transformation, allows for schema drift, and uses serverless Data Flows. Option A is wrong because Lookup is for reading a single row/value, not for bulk data ingestion; ForEach with insert would be inefficient and costly. Option C is wrong because Data Flows cannot directly read from HTTP sources; they require a dataset that is staged in a supported store like Blob Storage. Option D is wrong because it performs no transformation, so the nested JSON would not be flattened for loading into SQL.

Answer

Use a Lookup activity to read the JSON, then a ForEach activity to iterate and insert rows into SQL Database.

Answer

Use a Data Flow activity directly from HTTP source with a Flatten transformation and sink to SQL Database.

Answer

Use two Copy activities: one to copy JSON to Blob Storage, and another to copy from Blob Storage to SQL Database without transformation.

Question 6

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 7

You are a Data Engineer at Contoso Ltd. The company operates an e-commerce platform that generates streaming data from user interactions (clicks, page views, purchases) at a rate of 50,000 events per second. The data is ingested into Azure Event Hubs. You need to design a data processing solution that meets the following requirements:

1. Real-time dashboards must display aggregated metrics (e.g., total sales, active users) with a latency of less than 2 seconds.
2. Historical data must be stored in Azure Data Lake Storage Gen2 in Delta Lake format for batch analytics.
3. The solution must support exactly-once semantics for the streaming output to both the dashboard and the data lake.
4. You must use Azure Stream Analytics as the primary processing engine.
5. The output to the dashboard should use Azure Power BI, while the output to the data lake should use Azure Synapse Analytics serverless SQL pool for querying.

You have configured an Azure Stream Analytics job with Event Hubs as input. For output, you added a Power BI dataset and an Azure Data Lake Storage Gen2 output. However, you discover that the Power BI dataset is being updated with duplicate records, and the data lake output sometimes misses small windows of data during job restarts. Which two actions should you take to meet the requirements? (Choose two.)

A. Change the Power BI output to use the 'Exactly once' output mode.
B. Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once.
C. Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output.
D. Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Accepted Answer

Change the Power BI output to use the 'Exactly once' output mode. [CORRECT]. Power BI output supports 'Exactly once' output mode, which prevents duplicate records in the dashboard. Option C is correct because enabling checkpointing and using 'Exactly once' output mode for Azure Data Lake Storage Gen2 ensures exactly-once delivery to the data lake and prevents data loss during job restarts. Option B is incorrect because changing Event Hubs compatibility level to 1.0 does not guarantee exactly-once semantics; it only affects the serialization format. Option D is incorrect because using a second Stream Analytics job adds unnecessary complexity, and 'At least once' mode for Power BI would still allow duplicates.

Answer

Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once. [wrong]

Answer

Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode. [wrong]

Question 8

You are a data engineer at Northwind Traders. You have an Azure Synapse Analytics workspace with dedicated SQL pools. You need to monitor query performance to identify slow-running queries and understand resource consumption. The solution must provide historical data for the last 30 days and allow alerting when queries exceed a certain duration. You also need to export the data to a Log Analytics workspace for correlation with other metrics. What should you use?

Accepted Answer

Enable Diagnostic Settings to stream SQL pool metrics and logs to a Log Analytics workspace, then create alert rules and use KQL queries for historical analysis.. Enabling Diagnostic Settings for the dedicated SQL pool to stream logs and metrics to Log Analytics allows historical data retention (up to 30 days or more) and enables alerting and KQL queries. Option A is incorrect: DMVs provide current state, not historical data for 30 days. Option B is incorrect: Azure Storage Analytics is for storage accounts, not Synapse SQL. Option D is incorrect: Query Store is for SQL Server, not for monitoring resource consumption and doesn't integrate with Log Analytics.

Answer

Enable Diagnostic Settings for the dedicated SQL pool to send logs to Azure Storage, and query DMVs for historical data.

Answer

Use Azure Storage Analytics to analyze logs from the storage account backing the SQL pool.

Answer

Configure SQL Server Query Store and export data to Azure Blob Storage using elastic query.

Question 9

Refer to the exhibit. A data engineer receives this error when running a copy activity that loads data into a SQL staging table. The source is a CSV file. What is the most likely cause?

Accepted Answer

The 'ID' column in the source CSV file contains empty strings or missing values.. The error indicates that a NULL value is being inserted into a NOT NULL column 'ID' in the staging table. This usually happens when the source data has missing or null values for that column, or the column mapping is incorrect.

Answer

The SQL database is out of space.

Answer

The CSV file is missing the header row.

Answer

The staging table schema does not match the source schema.

Question 10

A data engineer needs to store JSON documents that are frequently updated by multiple users concurrently. The solution must support optimistic concurrency control and have built-in indexing on all fields. Which Azure data store should be used?

Accepted Answer

Azure Cosmos DB (SQL API). Azure Cosmos DB (SQL API) is the correct choice because it natively supports optimistic concurrency control via ETags (HTTP entity tags) and provides automatic indexing of all fields without requiring manual index management. This makes it ideal for storing JSON documents that are frequently updated by multiple concurrent users, as it ensures conflict detection and resolution while maintaining high performance.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 11

You are using Azure Synapse Analytics serverless SQL pool to query data in Parquet files stored in Azure Data Lake Storage Gen2. The queries are slow when filtering on a date column. You need to improve query performance without changing the data structure. What should you do?

Accepted Answer

Partition the Parquet files into folders by date. D is correct because partitioning Parquet files into folders by date enables partition elimination in Azure Synapse serverless SQL pool. When queries filter on the date column, the engine can prune entire folders from the scan, reading only the relevant Parquet files. This reduces I/O and improves performance without altering the data structure or format.

Answer

Create materialized views in the serverless SQL pool

Answer

Increase the service level objective (SLO) of the serverless SQL pool

Answer

Convert the Parquet files to CSV format

Question 12

Refer to the exhibit. You are reviewing an Azure Cosmos DB for NoSQL container configuration. The container stores customer orders. The application frequently queries orders by orderId. However, these queries are consuming high RUs and are slow. What is the most likely cause?

Accepted Answer

The unique key on orderId does not index the field; queries by orderId are cross-partition.. The unique key constraint on `orderId` does not automatically create an index for that field; it only enforces uniqueness. Without an explicit indexing policy that includes `orderId`, queries filtering by `orderId` must perform a cross-partition scan, which consumes high RUs and is slow. In Azure Cosmos DB for NoSQL, queries that cannot be served from a single physical partition due to missing index or partition key mismatch result in fan-out across all partitions, dramatically increasing RU consumption.

Answer

The _etag field is excluded from indexing, causing high RU for queries that include it.

Answer

The indexing mode is set to consistent, which causes high RU usage.

Answer

The container is provisioned with only 400 RU/s, which is too low for the workload.

Question 13

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.. Configuring the sink transformation to allow errors and log error rows to a separate file enables graceful error handling in Mapping Data Flows. This approach captures rows that fail during transformation (e.g., type conversion errors) and writes them to a designated error output, allowing the pipeline to continue processing valid rows. Option E is correct because a Conditional Split transformation can proactively identify rows that are likely to cause errors based on a condition (e.g., checking for null or invalid data types) and route them to a separate path for logging or remediation, preventing them from reaching the sink and causing failures.

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 14

Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?

Accepted Answer

Create a staging table, load data, then use CTAS and partition switching to replace the target partition.. Using a staging table with CREATE TABLE AS SELECT (CTAS) and then switching partitions replaces the entire partition without individual row modifications, which minimizes fragmentation in a clustered columnstore index. Option A (UPDATE and INSERT) is wrong because individual updates cause columnstore fragmentation. Option B (DELETE and INSERT in a single transaction) also involves row-level changes that fragment columnstore. Option C (MERGE) is wrong because MERGE operations on columnstore indexes cause significant fragmentation due to row-by-row modifications.

Answer

Use UPDATE for existing rows and INSERT for new rows.

Answer

Use DELETE and INSERT statements in a single transaction.

Answer

Use a MERGE statement to perform upserts.

Question 15

You are designing a stream processing solution using Azure Stream Analytics. The job must reference a static lookup table (product catalog) stored in Azure Blob Storage. The catalog is updated once daily. The job should automatically pick up the latest version without restarting. Which two configurations are required? (Choose two.)

Accepted Answer

Set the reference input's 'Path pattern' to include date and time placeholders. Azure Stream Analytics reference data inputs support path pattern placeholders like {date} and {time} to dynamically resolve the latest blob file. This allows the job to automatically load a new version of the static lookup table when the blob is updated, without requiring a job restart. The path pattern must be structured to match the naming convention of the uploaded file, such as 'catalog/{date}/{time}/products.csv'.

Answer

Configure the reference input with a static blob path

Answer

Use Azure Event Grid to trigger job restart on blob update

Answer

Store the reference data in Azure SQL Database instead of Blob Storage