DP-203 Practice Questions 2 — 50 Questions

Question 1

Refer to the exhibit. A data engineer notices that the copy activity sometimes copies 0 rows despite reading 1 million rows. What is the most likely cause?

Accepted Answer

The source query includes a 'WHERE' clause that filters out all rows on alternate runs.. In Azure Data Factory, the copy activity can be configured for incremental loads by using a source query with a WHERE clause that filters rows based on a watermark (e.g., last modified timestamp). On pipeline runs where no new or updated rows exist in the source table, this WHERE clause will return zero rows. Although the source table contains 1 million rows, the incremental filter causes the copy activity to read and write 0 rows. This is the most likely cause of the described behavior.

Answer

The source table is being updated between pipeline runs, but the copy activity is not configured to upsert.

Answer

The pipeline is using a full load each time, overwriting the sink, so the second run sees the same data and skips because the sink already has it.

Answer

The sink is a file system that fails to write due to permission issues.

Question 2

You are designing a data processing pipeline that ingests data from a REST API endpoint every hour. The API returns JSON data with a varying schema. You need to store the raw data in Azure Data Lake Storage Gen2 and later process it using Azure Databricks. Which file format should you use for the raw data storage?

Accepted Answer

JSON. C is correct because the raw data arrives from a REST API with a varying JSON schema, and storing it in JSON format preserves the exact structure and schema variability without data loss or transformation. JSON is schema-on-read, meaning the raw data can be ingested as-is into Azure Data Lake Storage Gen2 and later processed by Azure Databricks, which natively supports JSON parsing. This avoids premature schema enforcement that would occur with columnar or binary formats.

Answer

Parquet

Answer

CSV

Answer

Avro

Question 3

Which TWO actions should you take to secure sensitive data in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Use access control lists (ACLs) to restrict access to specific directories. Option B is correct because Azure Data Lake Storage Gen2 supports POSIX-style access control lists (ACLs) that let you grant or deny read/write/execute permissions at the directory and file level, which is essential for least-privilege access to sensitive data. Option E is correct because enabling encryption at rest with customer-managed keys stored in Azure Key Vault gives you control over the key lifecycle and satisfies compliance requirements for protecting sensitive data at rest. Option A is wrong because enabling public network access from all networks exposes the storage account to the internet and increases attack surface. Option C is wrong because allowing anonymous access permits unauthenticated reads and is a security risk, not a protection measure. Option D is wrong because disabling soft delete removes a recovery safeguard and does nothing to secure sensitive data.

Answer

Enable public network access from all networks for ease of use

Answer

Allow anonymous access to enable sharing

Answer

Disable soft delete to prevent accidental retention of deleted data

Question 4

A data engineer needs to store semi-structured JSON logs from IoT devices. The data will be queried using SQL and must support high-throughput writes. Which Azure data store is most appropriate?

Accepted Answer

Azure Cosmos DB with Core (SQL) API. Azure Cosmos DB with Core (SQL) API is the most appropriate choice because it natively stores semi-structured JSON documents, supports high-throughput writes with single-digit millisecond latency, and allows querying the JSON data directly using SQL syntax. Its schema-agnostic design and automatic indexing make it ideal for IoT workloads where device telemetry arrives at high velocity and must be immediately queryable.

Answer

Azure Blob Storage with JSON blobs

Answer

Azure Data Lake Storage Gen2 with JSON files and PolyBase

Answer

Azure SQL Database with JSON columns

Question 5

You need to ensure that sensitive data stored in Azure SQL Database is encrypted at rest. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) should be enabled to ensure sensitive data stored in Azure SQL Database is encrypted at rest. TDE encrypts the database, backups, and log files at rest without requiring changes to the application.

Answer

Always Encrypted

Answer

Azure Information Protection

Answer

Dynamic Data Masking

Question 6

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The exhibit shows the encryption configuration. What is the effect of setting infrastructureEncryption to Enabled?

Accepted Answer

It adds a second layer of encryption at the infrastructure level, ensuring data is encrypted at rest with two different keys.. Enabling infrastructureEncryption adds a second layer of encryption at the infrastructure level, ensuring data is encrypted at rest with two different keys (double encryption). Option A is incorrect because enabling infrastructure encryption does not disable customer-managed key encryption; it adds an additional layer. Option B is incorrect because infrastructure encryption applies to data at rest, not in transit between nodes. Option C is incorrect because infrastructure encryption applies to the entire Azure Synapse Analytics workspace, not just SQL pools, and it is not transparent data encryption (TDE); it is a separate infrastructure-level encryption.

Answer

It disables encryption using the customer-managed key.

Answer

It enables encryption of data in transit between nodes.

Answer

It enables transparent data encryption for SQL pools.

Question 7

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. Enabling soft delete with a 30-day retention period allows recovery of accidentally deleted blobs within that window. A lifecycle management rule automatically deletes blobs older than 90 days, meeting the retention policy with minimal administrative effort.

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 8

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline fails intermittently with network connectivity errors. You need to ensure reliable data transfer with minimal latency. Which solution should you recommend?

Accepted Answer

Deploy a self-hosted IR with high availability on two nodes. A self-hosted Integration Runtime (IR) is required for Azure Data Factory to reach on-premises SQL Server, and deploying it with high availability across two nodes eliminates the single point of failure causing intermittent connectivity errors. The self-hosted IR runs inside the corporate network, so it uses the existing network path rather than requiring new VPN infrastructure, and HA mode ensures failover if one node goes down. This directly addresses both reliability and minimal latency.

Answer

Set up a site-to-site VPN gateway

Answer

Stage data in Azure Blob Storage using sharded files

Answer

Use an Azure Integration Runtime instead

Question 9

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Avoid data skew by choosing a good distribution key.. Option B is correct because choosing a good distribution key prevents data skew, which otherwise causes some distributions to hold disproportionate rows and forces the query engine to move data across nodes, degrading performance in a dedicated SQL pool. Option D is correct because replicated tables copy the full table to every compute node, eliminating data movement for joins; this is recommended for small dimension tables under roughly 1 GB (or up to 2 GB compressed). Option E is correct because hash distribution on a high-cardinality column spreads rows evenly across the 60 distributions, which is ideal for large fact tables and minimizes skew. Option A is wrong because clustered columnstore indexes are the default and preferred storage for large tables in dedicated SQL pools, delivering high compression and fast analytical scans. Option C is wrong because round-robin distribution is a fallback for staging or tables with no clear join key, not a blanket best practice for all large fact tables, since it can cause costly data movement during joins.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.

Question 10

You need to monitor resource utilization for an Azure Synapse Analytics dedicated SQL pool. Which Azure Monitor metric shows the percentage of allocated DWU being used?

Accepted Answer

DWU used. The 'DWU used' metric in Azure Monitor reports the percentage of allocated Data Warehouse Units (DWUs) being consumed by the dedicated SQL pool. It is the direct measure of how much of the provisioned compute capacity is in use, making it the correct answer for monitoring DWU utilization. This metric is available in the Azure portal under the Synapse workspace's Metrics blade.

Answer

Memory percentage

Answer

Data IO percentage

Answer

CPU percentage

Question 11

Which TWO Azure services can be used to audit data access and changes in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Storage account diagnostic settings.. Storage account diagnostic settings (C) are correct because they can stream Data Lake Storage Gen2 resource logs—such as Read, Write, and Delete operations on blobs and ADLS Gen2 filesystems—to a Log Analytics workspace, storage account, or Event Hub, providing the raw audit trail of data access and changes. Azure Monitor and Microsoft Sentinel (D) are correct because Azure Monitor collects and queries those diagnostic logs (via Log Analytics/KQL) for auditing, while Microsoft Sentinel ingests the same storage/ADLS logs to detect, alert on, and investigate suspicious data-access activity. Microsoft Entra ID sign-in logs (A) only record authentication and token issuance events, not per-file data-plane operations, so they cannot audit data access or changes. Azure Backup reports (B) cover backup job status and protected-item health, not data-plane read/write/delete auditing. Azure Policy (E) evaluates and enforces resource configuration compliance (control plane), not individual data access or modification events.

Answer

Microsoft Entra ID sign-in logs.

Answer

Azure Backup reports.

Answer

Azure Policy.

Question 12

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

Accepted Answer

Redistribute the fact table on the join column using hash distribution.. Redistributing the fact table on the join column using hash distribution will most likely reduce data movement. In a dedicated SQL pool, hash distribution on the join column ensures that rows with the same join key are colocated on the same distribution, minimizing the need to shuffle data during joins.

Answer

Change the distribution type to round-robin.

Answer

Update statistics on all columns used in joins.

Answer

Increase the number of distributions to 120.

Question 13

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

Accepted Answer

The statistics on the tables are outdated or missing.. Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Answer

The tables are distributed using round-robin distribution.

Answer

Result-set caching is disabled.

Answer

The resource class for the user is set to smallrc.

Question 14

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Accepted Answer

Delta Lake provides time travel capabilities for accessing historical data versions.. Option A is correct because Delta Lake stores a transaction log (_delta_log) that records every commit, enabling time travel queries such as SELECT * FROM table VERSION AS OF n or TIMESTAMP AS OF, which is valuable for auditing, reproducing past results, and recovering from bad writes in a daily batch pipeline. Option D is correct because Delta Lake provides ACID transactions on top of ADLS Gen2, so concurrent writers and readers see consistent snapshots and partial or failed writes are not exposed, which matters when multiple jobs or retries touch the same output. Option B is wrong because schema evolution is actually a Delta Lake strength (mergeSchema and automatic schema evolution in MERGE/append), while Parquet requires manual rewrites or workarounds. Option C is wrong because Delta Lake does not automatically compress data to reduce storage cost; it relies on Parquet compression plus OPTIMIZE/Z-ORDER and VACUUM for layout and cleanup, and it adds transaction-log overhead. Option E is wrong because Azure Databricks natively reads and writes Parquet files, including via Spark and external tables.

Answer

Parquet is easier to implement for schema evolution than Delta Lake.

Answer

Delta Lake reduces storage costs by automatically compressing data.

Answer

Parquet files are not natively supported by Azure Databricks.

Question 15

Your organization needs to ensure that all data stored in Azure Data Lake Storage Gen2 is encrypted at rest using Microsoft-managed keys. What is the default encryption method?

Accepted Answer

Storage Service Encryption (SSE) with Microsoft-managed keys.. Azure Data Lake Storage Gen2 is built on Azure Blob Storage, which automatically encrypts all data at rest using Storage Service Encryption (SSE) with Microsoft-managed keys by default. This encryption is always enabled and cannot be disabled. Microsoft-managed keys are used unless you choose to use customer-managed keys.

Answer

Transparent Data Encryption (TDE) on the storage account.

Answer

Client-side encryption with keys stored in Azure Key Vault.

Answer

Azure Disk Encryption on the storage nodes.

Question 16

You are designing a data processing solution in Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 and you need to perform transformations using Apache Spark. The security requirements mandate that all data in transit must be encrypted and that the storage account must not be accessible from the public internet. What should you configure?

Accepted Answer

Enable the storage account firewall and add a private endpoint for Azure Databricks to use.. It satisfies both security requirements: encrypting data in transit and preventing public internet access. A private endpoint uses Azure Private Link to connect Azure Databricks to the storage account over the Microsoft backbone network, ensuring all traffic stays within the Azure network and is encrypted via TLS. The storage account firewall is then configured to deny all public traffic, so only the private endpoint can access the storage account, meeting the 'not accessible from the public internet' mandate.

Answer

Disable TLS on the storage account and use a shared access signature (SAS) token for authentication.

Answer

Use Azure Databricks with VNet injection and configure a service endpoint for the storage account.

Answer

Configure the storage account to use HTTPS only and enable firewall rules to allow only Azure services.

Question 17

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

Accepted Answer

Parquet with Snappy compression. Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Answer

Avro with Deflate compression

Answer

CSV with Gzip compression

Answer

ORC with Zlib compression

Question 18

You have an Azure Synapse Analytics serverless SQL pool. You need to monitor the number of queries that are currently executing. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_requests. Sys.dm_exec_returns detailed information about each request currently executing on the serverless SQL pool, including its state, command, and session ID. This DMV is specifically designed for monitoring active queries.

Option A (sys.dm_resource_governor_workload_groups) shows workload group configuration and resource statistics, not current requests.

Option B (sys.dm_exec_query_stats) provides cumulative performance statistics for cached query plans, not currently executing queries.

Option D (sys.dm_exec_sessions) contains session-level information but does not indicate which sessions are actively executing a request.

Answer

sys.dm_resource_governor_workload_groups

Answer

sys.dm_exec_query_stats

Answer

sys.dm_exec_sessions

Question 19

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Answer

Azure HDInsight with Hive

Answer

Azure Analysis Services

Answer

Azure Databricks with PySpark

Question 20

You are designing a data storage solution for a retail company that expects high volumes of small, time-series sensor data from thousands of IoT devices. The data must be stored cost-effectively and queried by time range with low latency. Which Azure data store should you recommend?

Accepted Answer

Azure Data Explorer (ADX). Azure Data Explorer (ADX) is optimized for high-volume, time-series data ingestion and low-latency queries over time ranges. It uses a columnar storage engine and automatic indexing, making it cost-effective for sensor data from thousands of IoT devices.

Answer

Azure Cosmos DB with analytical store

Answer

Azure SQL Database with columnstore indexes

Answer

Azure Blob Storage with Azure Data Lake Storage Gen2