DP-203 Practice Test 6 — 10 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