DP-203 Practice Test 3 — 30 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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

Question 11

You have an Azure Synapse Analytics dedicated SQL pool that handles both high-priority real-time queries and low-priority batch jobs. You need to ensure that high-priority queries always get the resources they need, while batch jobs do not starve. What should you configure?

Accepted Answer

Create workload groups for high-priority and low-priority queries, assigning appropriate importance and resource percentages. Workload groups in a dedicated SQL pool let you classify requests into groups and assign each group an importance level (e.g., HIGH for real-time queries, LOW for batch) plus a resource percentage (min/max memory and concurrency). This ensures high-priority queries preempt lower-priority ones for resources while guaranteeing batch jobs a floor so they don't starve. It is the native workload management mechanism for dedicated SQL pools.

Answer

Enable result-set caching for the high-priority queries

Answer

Enable data compression on the tables used by batch jobs

Answer

Create materialized views for the batch job queries

Question 12

Your team is using Azure Synapse Analytics to process sensitive customer data. You need to ensure that column-level security is applied to a specific table so that only users with a certain role can view certain columns. Which feature should you use?

Accepted Answer

Column-level security (CLS). Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table based on the user's role or permissions. It is implemented using GRANT and DENY statements on individual columns, ensuring that only authorized users can view sensitive columns. This directly addresses the requirement to apply column-level security to a specific table.

Answer

Row-level security (RLS)

Answer

Azure Purview data policies

Answer

Dynamic data masking (DDM)

Question 13

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 14

Refer to the exhibit. An ARM template deploys an Azure Synapse Analytics workspace. What is the purpose of the 'managedVirtualNetwork' property set to 'default'?

Accepted Answer

It enables the workspace to use a managed virtual network for network isolation. Setting 'managedVirtualNetwork' to 'default' in an ARM template for Azure Synapse Analytics enables a managed virtual network that provides network isolation for the workspace. This allows the workspace to use private endpoints and managed private endpoints for secure data integration without exposing traffic to the public internet. It is a foundational setting for implementing a secure, network-isolated Synapse environment.

Answer

It configures the workspace to use a user-assigned managed identity

Answer

It disables public network access to the workspace

Answer

It creates the workspace in a private endpoint configuration

Question 15

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

Accepted Answer

Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'. Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Answer

Set the 'Staging' option to 'Use staging'

Answer

Enable staging for the sink and use PolyBase

Answer

Set the 'Partition option' to 'Round robin' on the source

Question 16

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

Accepted Answer

Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers. Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Answer

Store all data in Azure SQL Database with partitioning and drop older partitions

Answer

Use Azure Data Lake Storage Gen2 with a single storage tier

Answer

Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

Question 17

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

Accepted Answer

Use an auto-scaling cluster with spot instances.. Auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Answer

Use a fixed-size cluster with premium tier.

Answer

Use a Photon-accelerated cluster with premium tier.

Answer

Use an interactive cluster with a large number of workers.

Question 18

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 19

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.. Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless. Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

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 20

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected. After reviewing the query plans, you see that some queries are spilling to tempdb. What should you do to reduce tempdb spills?

Accepted Answer

Increase the resource class for the user executing the queries.. Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Answer

Redistribute the tables using hash distribution.

Answer

Rebuild all columnstore indexes.

Answer

Add partitioning to the tables.