DP-203 Practice Test 34 — 15 Questions

Question 1

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

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

Accepted Answer

The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead. Materialize() only provides performance benefit when the materialized result is referenced multiple times. In this query, the materialized result is used only once, so the overhead of materialization (storing the result in memory) outweighs any benefit, potentially making the query slower. Option A is incorrect because there is no join with a Products table; the query uses a single table. Option B is incorrect because summarize does not inherently materialize results; it computes aggregations on the fly. Option C is incorrect because datetime range filters in Kusto are sargable and do not cause full table scans.

Answer

The join with the Products table forces a shuffle that bypasses the materialized result

Answer

The query uses summarize, which already materializes results internally

Answer

The datetime range filter is not sargable, causing full table scan

Question 2

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

Accepted Answer

Parquet. Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Answer

Avro

Answer

CSV

Answer

JSON

Question 3

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Accepted Answer

Use coalesce() to reduce the number of partitions without a shuffle.. `coalesce()` reduces the number of partitions without triggering a full shuffle, which minimizes the number of output files while preserving performance. Since the DataFrame already has 100 million rows and 20 columns, coalescing to fewer partitions directly reduces the number of files written, addressing the small-file problem efficiently.

Answer

Enable caching on the DataFrame before writing.

Answer

Apply bucketing on a column to group data.

Answer

Increase the number of partitions using repartition() with a larger number.

Question 4

A data engineer is monitoring Azure Data Lake Storage Gen2 costs and notices high transaction costs for a specific container. The container stores Parquet files used by Azure Databricks for read-heavy analytics. The files are accessed frequently by multiple jobs. What is the most cost-effective way to reduce transaction costs?

Accepted Answer

Enable Azure CDN to cache the files.. Enabling Azure CDN caches the frequently accessed Parquet files at edge locations, reducing the number of direct read requests to Azure Data Lake Storage Gen2. This lowers transaction costs (both read and list operations) while maintaining low-latency access for read-heavy analytics workloads. The CDN serves cached content, so the storage account incurs fewer billable transactions.

Answer

Move the data to Azure Blob Storage cool tier.

Answer

Increase the Parquet file size to maximize block size.

Answer

Convert the container to Azure Files.

Question 5

A data engineer monitors an Azure Stream Analytics job that processes real-time data. The job is falling behind, and the SU utilization is at 100%. Which action should be taken to improve performance?

Accepted Answer

Increase the number of Streaming Units (SU).. When SU utilization reaches 100%, the job is fully saturated and cannot process incoming data fast enough. Increasing the number of Streaming Units (SU) allocates more compute resources (CPU and memory) to the job, allowing it to handle higher throughput and reduce backlog. This is the direct and recommended action for resolving performance bottlenecks caused by insufficient SU capacity.

Answer

Reduce the number of Streaming Units.

Answer

Change the query compatibility level to 1.0.

Answer

Deploy a second Stream Analytics job and split the input.

Question 6

Which THREE components are part of a defense-in-depth strategy for data security in Azure?

Accepted Answer

Network security groups (NSGs) on subnets. Network security groups (NSGs) are a fundamental component of a defense-in-depth strategy because they provide network-layer segmentation and filtering. By applying NSGs to subnets, you can control inbound and outbound traffic based on source/destination IP addresses, ports, and protocols, creating a perimeter defense that limits lateral movement in case of a breach.

Answer

Azure Policy to enforce tagging

Answer

Dynamic data masking for all databases

Question 7

Which TWO actions should you take to secure data at rest in Azure Synapse Analytics dedicated SQL pools?

Accepted Answer

Use Always Encrypted with secure enclaves for specific columns.. Always Encrypted with secure enclaves encrypts sensitive columns at rest and during query processing. Option C is correct: Transparent Data Encryption (TDE) encrypts the entire database at rest. Options B, D, and E are incorrect: column-level security and Dynamic Data Masking control access or mask output but do not encrypt at rest; Azure RBAC manages permissions, not encryption.

Answer

Implement column-level security to filter sensitive columns.

Answer

Configure Dynamic Data Masking on tables containing sensitive data.

Answer

Assign Azure RBAC roles to restrict access to the storage account.

Question 8

You have an Azure Data Factory pipeline defined as shown. The pipeline is failing because the preCopyScript truncates the staging table before each run, but the table is empty on the first run. What change would you make to ensure the pipeline works correctly?

Accepted Answer

Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.. The preCopyScript runs on every pipeline execution. On the first run, the staging table does not exist, so TRUNCATE TABLE dbo.Staging would fail. By adding a check with IF OBJECT_ID, the script only truncates if the table exists. This handles the first run gracefully. Option A is wrong because removing the preCopyScript would not truncate the table on subsequent runs, potentially causing duplicate data issues. Option B is wrong because increasing writeBatchSize does not solve the truncation error. Option D is wrong because setting recursive to false affects how the source dataset handles recursive file systems, not the truncation problem.

Answer

Remove the preCopyScript entirely.

Answer

Increase the writeBatchSize to 50000 to speed up the copy.

Answer

Set recursive to false in the source.

Question 9

Which TWO Azure features can be used to encrypt data at rest in Azure Blob Storage? (Choose two.)

Accepted Answer

Customer-managed keys in Azure Key Vault. Options C and D are correct. D: Storage Service Encryption (SSE) encrypts data at rest by default. C: Customer-managed keys in Azure Key Vault provide additional control over encryption keys. Option A is wrong because Azure Disk Encryption is for VMs, not Blob Storage. Option B is wrong because Azure Information Protection is for classification and labeling, not encryption at rest. Option E is wrong because TLS is for data in transit.

Answer

Azure Disk Encryption

Answer

Azure Information Protection

Answer

Transport Layer Security (TLS)

Question 10

You are a data engineer at a healthcare analytics company. The company stores patient records in an Azure Data Lake Storage Gen2 account organized by /patient/{patientId}/year={yyyy}/month={MM}/day={dd}/*.parquet. There are 10,000 patients, and each patient has about 1 GB of data per year. The data is used by data scientists who run ad-hoc queries using Azure Synapse Serverless SQL. They complain that queries scanning multiple patients over the last year take too long and consume too much data. They often need to filter by patientId and a date range. You need to improve query performance and reduce the amount of data scanned. You cannot change the folder structure because it is used by other processes. What should you do?

Accepted Answer

Create external tables in Synapse Serverless SQL that use the folder structure as partitions, and ensure queries filter on year, month, and day.. Creating external tables in Synapse Serverless SQL with the existing folder structure as partitions allows the query engine to perform partition elimination. When queries filter on year, month, and day, Synapse Serverless SQL will only scan the relevant folders, drastically reducing data scanned and improving performance. This approach does not require changing the folder structure, which is used by other processes.

Answer

Reorganize the folder structure to /year={yyyy}/month={MM}/day={dd}/patientId={patientId}/*.parquet.

Answer

Convert the Parquet files to CSV format to improve compression and reduce file size.

Answer

Create views that aggregate data by patient and date, and instruct data scientists to query the views.

Question 11

Your company uses Azure Cosmos DB for NoSQL to store user profiles. The application frequently reads profiles by user ID (the partition key). Occasionally, the application needs to query by email address, which is not part of the partition key. What should you do to optimize the occasional queries by email?

Accepted Answer

Create a secondary (composite) index on the email field.. Creating a secondary index on the email field allows Azure Cosmos DB for NoSQL to efficiently serve queries filtering by email without scanning all partitions. Since email is not the partition key, a secondary index (specifically a composite index if needed for multi-field queries, or a single-field index) enables index-based lookup across all physical partitions, optimizing the occasional query without redesigning the data model.

Answer

Change the partition key to the email field.

Answer

Denormalize the data by storing a copy of the email in the partition key.

Answer

Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.

Question 12

Match each Azure data storage service to its primary use case.

Accepted Answer

Azure Blob Storage. All Azure data storage services listed are correctly matched to their primary use cases: Azure Blob Storage for unstructured object storage, Azure Cosmos DB for globally distributed NoSQL data, Azure Data Lake Storage Gen2 for big data analytics, and Azure SQL Database for relational OLTP workloads.

Question 13

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a monitoring strategy to detect and alert on unusual access patterns that could indicate a security breach. Which THREE services or features should you use? (Choose three.)

Accepted Answer

Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.. Options A, C, and D are correct. A: Microsoft Defender for Storage provides security alerts for unusual access patterns. C: Ingesting logs into Microsoft Sentinel allows creation of analytics rules to detect anomalous patterns. D: Diagnostic settings on the storage account collect read, write, and delete logs necessary for analysis. Option B is incorrect because Azure Policy is used for governance and enforcement of policies, not for monitoring access patterns. Option E is incorrect because Azure Monitor Metrics track transaction counts and latency but do not include detailed access logs required for detecting unusual patterns.

Answer

Apply Azure Policy to enforce encryption and access policies.

Answer

Use Azure Monitor Metrics to track storage account transactions and latency.

Question 14

Which Azure service is primarily used for orchestrating data pipelines in a cloud-native ETL workflow?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct answer because it is a cloud-native, serverless data integration service specifically designed for orchestrating and automating data pipelines. ADF provides a code-free visual interface, supports over 90 built-in connectors, and enables complex ETL/ELT workflows with control flow, data flow, and trigger-based scheduling, making it the primary orchestration tool in Azure.

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Answer

Azure Databricks

Question 15

You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?

Accepted Answer

The credential used to access ADLS Gen2 does not have sufficient permissions.. The most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.

Answer

The serverless SQL pool does not support reading Parquet files.

Answer

The external table definition is missing the SCHEMA_NAME parameter.

Answer

The DATA_COMPRESSION setting is incompatible with Parquet files.