Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 76–150

509 questions total · 7pages · All types, answers revealed

Take a mock examExam hub
Page 1

Page 2 of 7

Page 3
76
MCQmedium

You are designing a data processing solution in Azure using Azure Data Lake Storage Gen2 as the storage layer. You need to ensure that data ingested from various sources is immutable and can be used for both batch and streaming workloads. Which storage design pattern should you implement?

A.Store data in a normalized relational database structure.
B.Implement a medallion architecture with bronze, silver, and gold layers.
C.Use a data vault model with hubs, links, and satellites.
D.Design a star schema with fact and dimension tables.
AnswerB

The medallion pattern layers bronze raw, silver cleansed and gold curated data in ADLS Gen2. Bronze preserves ingested data immutably for replay, while silver and gold serve batch and streaming consumers, meeting the immutability and dual-workload constraints.

Why this answer

The medallion architecture (bronze, silver, gold) is the correct pattern because it enforces immutability at the bronze layer (raw ingested data is never modified), while providing progressively refined, query-optimized views for both batch and streaming workloads in Azure Data Lake Storage Gen2. This design supports schema-on-read, enables reprocessing from raw data, and aligns with lakehouse principles for unified analytics.

Exam trap

The trap here is that candidates confuse data modeling patterns (star schema, data vault) with storage layer design patterns, assuming any structured approach ensures immutability, when in fact only the medallion architecture explicitly separates raw immutable storage from refined layers for batch and streaming workloads.

How to eliminate wrong answers

Option A is wrong because a normalized relational database structure is designed for transactional consistency (OLTP) and does not support immutability or efficient storage of raw, schema-on-read data in a data lake; it also introduces coupling that hinders reprocessing. Option C is wrong because a data vault model (hubs, links, satellites) is a data warehouse modeling technique for auditing and historical tracking, not a storage pattern for immutability or unified batch/streaming in a data lake. Option D is wrong because a star schema with fact and dimension tables is a dimensional modeling approach for analytical queries in a data warehouse, not a pattern for raw data immutability or handling streaming ingestion in Azure Data Lake Storage Gen2.

Full explanation →
77
MCQhard

You are a data engineer for a multinational e-commerce company. The company uses Azure Synapse Analytics as its data warehouse. The current fact table, SalesFact, is distributed using hash distribution on the CustomerID column. It has 2 billion rows and is 2 TB in size. Recently, the business team has been running many queries that aggregate sales by product category and date, and these queries are experiencing high data movement and long execution times. The product dimension table (ProductDim) has 100,000 rows and is 100 MB. The date dimension table (DateDim) has 5,000 rows and is 5 MB. You need to redesign the storage to minimize data movement for these aggregation queries. You cannot change the fact table distribution key to ProductID because of other critical queries that rely on CustomerID. What should you do?

A.Create materialized views on the fact table that aggregate by product category and date
B.Replicate the ProductDim and DateDim tables to all compute nodes
C.Partition the fact table by date and keep the same distribution
D.Change the fact table distribution to round-robin and create non-clustered indexes on ProductID and DateID
AnswerB

Replicating the small ProductDim and DateDim tables places a full copy on every compute node, eliminating data movement during joins with the hash-distributed fact table. Aggregations by product category and date then execute locally, cutting shuffle overhead while CustomerID distribution stays unchanged.

Why this answer

Replicating small dimension tables (ProductDim at 100 MB and DateDim at 5 MB) to all compute nodes eliminates the need to shuffle these tables across nodes during joins. In Azure Synapse Analytics, replicated tables are copied to each distribution, so when the fact table (hash-distributed on CustomerID) joins with ProductDim and DateDim on ProductID and DateID, no data movement occurs for the dimension tables. This directly reduces the high data movement and long execution times for aggregation queries by product category and date.

Exam trap

The trap here is that candidates often choose materialized views (Option A) thinking they solve all aggregation performance issues, but they overlook that data movement from joins with non-replicated dimension tables remains the bottleneck, whereas table replication directly addresses the shuffle cost for small dimension tables.

How to eliminate wrong answers

Option A is wrong because materialized views in Azure Synapse Analytics pre-aggregate data but still require the underlying fact table's distribution; they do not eliminate data movement when joining with non-replicated dimension tables, and the queries would still suffer from shuffling ProductDim and DateDim. Option C is wrong because partitioning the fact table by date improves partition elimination for date-range filters but does not reduce data movement during joins; the hash distribution on CustomerID remains, so joins on ProductID and DateID still require redistributing the fact table or dimension tables. Option D is wrong because changing to round-robin distribution would distribute fact table rows randomly, causing even more data movement for all joins and aggregations, and non-clustered indexes do not address the fundamental distribution issue for large-scale aggregation queries.

Full explanation →
78
MCQmedium

You are developing an Azure Databricks notebook that processes a large Delta Lake table. You must add a derived column that depends on the latest value of a watermark stored in a small reference table, and the notebook must refresh this value before each micro-batch. You need to ensure the reference data is re-read on every micro-batch rather than cached once. Which approach should you use?

A.Persist the reference table with the MEMORY_AND_DISK storage level before starting the stream.
B.Broadcast the reference table as a static DataFrame and join it to the streaming DataFrame.
C.Enable the spark.databricks.delta.cache.enabled option on the streaming query.
D.Use the foreachBatch sink and, inside the function, query the reference table with a fresh read on each invocation.
AnswerD

foreachBatch gives you a Python or Scala function that runs once per micro-batch with the batch's DataFrame. Performing a fresh read of the reference table inside that function guarantees the watermark is retrieved anew for every micro-batch, which satisfies the refresh requirement while still allowing DataFrame operations on the batch.

Why this answer

Structured Streaming captures static DataFrames when a query starts, so joins against them do not see later changes. foreachBatch executes a user-defined function for each micro-batch and permits arbitrary operations, including a fresh read of the reference table. Reading the watermark inside that function ensures the latest value is used for the derived column on every micro-batch, which is precisely the stated requirement.

Exam trap

The trap here is assuming that caching or persisting the reference table will keep it current across micro-batches.

Full explanation →
79
MCQeasy

Your organization uses Azure Data Lake Storage Gen2 (ADLS Gen2) and wants to transform data using Azure Databricks. The data is stored in Parquet format. You need to read the data into a Spark DataFrame. Which DataFrame reader method should you use?

A.spark.read.avro()
B.spark.read.json()
C.spark.read.parquet()
D.spark.read.csv()
AnswerC

Parquet is a columnar file format, and Spark's DataFrameReader exposes a dedicated parquet method that reads such files directly from ADLS Gen2 into a DataFrame, inferring the schema automatically without needing an explicit schema definition or generic format call.

Why this answer

The data is stored in Parquet format, and the Spark DataFrame reader method `spark.read.parquet()` is specifically designed to read Parquet files, which is a columnar storage format optimized for big data processing in Azure Databricks.

Exam trap

The trap here is that candidates may confuse file format reader methods (e.g., using `spark.read.avro()` for Parquet data) due to assuming all binary formats are interchangeable, but each reader method is strictly tied to its specific file format.

How to eliminate wrong answers

Option A is wrong because `spark.read.avro()` is used for Avro format, not Parquet. Option B is wrong because `spark.read.json()` is for JSON files, which are text-based and not columnar like Parquet. Option D is wrong because `spark.read.csv()` is for CSV files, which are row-based and lack the compression and schema efficiency of Parquet.

Full explanation →
80
MCQeasy

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?

A.sys.dm_resource_governor_workload_groups
B.sys.dm_exec_query_stats
C.sys.dm_exec_requests
D.sys.dm_exec_sessions
AnswerC

sys.dm_exec_requests shows currently executing requests in the serverless SQL pool, including state, command, and session ID.

Why this answer

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.

Full explanation →
81
MCQmedium

You have an Azure Data Lake Storage Gen2 account used by an Azure Synapse Analytics serverless SQL pool. Analysts run ad-hoc queries against CSV and Parquet files. You need to reduce the amount of data scanned by these queries without changing file contents. What should you do?

A.Convert all CSV files to Parquet and store them in a single folder.
B.Create a partitioned folder hierarchy by date and query with OPENROWSET using a wildcard path.
C.Increase the DWU setting on the serverless SQL pool.
D.Enable hierarchical namespace on the storage account.
AnswerB

Partitioning folders by date lets the serverless SQL pool prune irrelevant directories, so a query filtered on date reads only the matching partition folders instead of the whole container. Combining that with an OPENROWSET wildcard path scoped to those folders directly reduces bytes scanned and cost, and it requires no rewrite of the underlying files.

Why this answer

Serverless SQL pools charge per byte scanned, so the effective lever is reducing the files a query must read. Organizing data into a folder hierarchy that mirrors common filter columns, such as date, allows partition elimination, and an OPENROWSET path that targets those folders avoids reading unrelated data. This keeps files unchanged while cutting scanned volume and cost.

Exam trap

The trap here is assuming that switching to a columnar format alone eliminates scanning, when without a filter-aligned folder layout the engine still reads every file in the container.

Full explanation →
82
MCQeasy

A data engineer needs to store semi-structured JSON log files from a web application. Each log entry is about 1 KB. The logs are rarely queried (once a month) and must be retained for 7 years for compliance. The solution must minimize storage cost. Which storage option should be used?

A.Store the logs in Azure SQL Database as a table.
B.Store the logs in Azure Files share.
C.Store the logs in Azure Blob Storage with cool access tier.
D.Store the logs in Azure Cosmos DB with a JSON container.
AnswerC

Blob Storage cool tier is low-cost for infrequent access, suitable for logs.

Why this answer

Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB (D) because it natively supports JSON, but they overlook the extreme cost of storing and rarely querying 7 years of data in a globally distributed, high-throughput NoSQL database, which is optimized for frequent, low-latency access, not archival.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database designed for structured, transactional workloads with frequent queries, and it incurs high storage and compute costs for 7 years of 1 KB log entries, making it far more expensive than blob storage for rarely accessed data. Option B is wrong because Azure Files provides SMB/NFS file shares primarily for shared file access in VMs or on-premises apps, not for cost-effective, long-term archival of semi-structured logs, and it lacks the tiered pricing and lifecycle management of blob storage. Option D is wrong because Azure Cosmos DB is a NoSQL database optimized for low-latency, globally distributed, and frequently queried data; storing 7 years of rarely accessed logs in Cosmos DB would incur prohibitive costs due to its per-request unit (RU) pricing and storage charges, far exceeding blob storage costs.

Full explanation →
83
MCQeasy

You are creating an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage daily. The on-premises network restricts inbound connections, and you need a secure connection without exposing the SQL Server to the public internet. What should you use to connect to the on-premises SQL Server?

A.Azure ExpressRoute
B.Azure Integration Runtime with a managed virtual network
C.Azure Private Link
D.Self-hosted integration runtime
AnswerD

A self-hosted integration runtime is installed on a machine within the on-premises network. It establishes outbound connections to Azure Data Factory, allowing the service to dispatch copy activities to it. This enables secure access to on-premises data sources without inbound firewall openings. It is the standard mechanism for connecting Data Factory to private network resources.

Why this answer

To connect Azure Data Factory to an on-premises SQL Server without exposing it to the internet, you install a self-hosted integration runtime on a machine in the on-premises network. This runtime initiates outbound connections to Azure, so no inbound firewall rules are needed. It is the correct and standard solution for hybrid data movement with Data Factory.

Exam trap

The trap here is selecting a networking service like ExpressRoute or Private Link, which solve connectivity but do not provide the execution engine needed for Data Factory to access on-premises data.

Full explanation →
84
MCQeasy

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

A.Azure API Management policies
B.Azure Synapse Analytics dynamic data masking
C.Azure Data Lake Storage access control lists (ACLs)
D.Azure Purview classification and labeling
AnswerB

Dynamic data masking in Azure Synapse Analytics applies masking rules at query time on designated columns, so PII stays intact at rest while unauthorised users see obfuscated values. This satisfies the requirement to anonymise PII dynamically for analytics without altering the underlying stored data.

Why this answer

Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs.

Azure Data Lake Storage does not provide masking.

Full explanation →
85
MCQmedium

You are a data engineer for a financial services company. The company uses Azure Data Lake Storage Gen2 as its data lake. You have a directory structure where each customer has a folder containing transaction files in CSV format. The security team requires that each customer's data be accessible only to that customer's users. You need to implement fine-grained access control using Azure Data Lake Storage Gen2's POSIX-like ACLs. However, you have thousands of customers, and managing ACLs individually is not feasible. What should you do?

A.Create a shared access signature (SAS) token for each customer and distribute it securely
B.Use POSIX ACLs on each customer folder, assigning permissions to individual user identities
C.Use row-level security in Azure Data Lake Storage Gen2
D.Create an Azure AD group for each customer, add users to the group, and assign ACLs to the group on the customer folder
AnswerD

Assigning POSIX ACLs to a Microsoft Entra ID group per customer folder satisfies the fine-grained, per-customer isolation requirement without editing thousands of individual user entries. Group membership changes propagate automatically, so onboarding or removing a customer's users needs no ACL rewrite, keeping administration feasible at scale.

Why this answer

Azure Data Lake Storage Gen2 supports POSIX-like ACLs that can be assigned to Azure AD security groups. By creating one Azure AD group per customer, adding the customer's users to that group, and then assigning the group the appropriate read/execute ACLs on the customer's folder, you achieve scalable, fine-grained access control without managing thousands of individual user ACLs. This approach aligns with the principle of least privilege and simplifies administration.

Exam trap

The trap here is that candidates often confuse row-level security (a SQL-based feature) with file-system access control in Azure Data Lake Storage Gen2, or they mistakenly believe that SAS tokens can provide granular directory-level isolation, when in fact SAS tokens operate at the container or storage account level and cannot enforce per-folder ACLs.

How to eliminate wrong answers

Option A is wrong because shared access signature (SAS) tokens provide delegated access at the storage account or container level, not at the directory or file level, and managing thousands of SAS tokens securely is operationally complex and does not integrate with Azure AD identity-based access control. Option B is wrong because assigning POSIX ACLs to individual user identities for thousands of customers is not feasible due to the Azure AD limit of 32 ACL entries per file or directory and the administrative overhead of managing individual user permissions at scale. Option C is wrong because row-level security is a feature of Azure SQL Database and Azure Synapse Analytics dedicated SQL pools, not of Azure Data Lake Storage Gen2, which uses POSIX ACLs and RBAC for access control.

Full explanation →
86
MCQmedium

You have an Azure Data Lake Storage Gen2 account that stores large volumes of parquet files. A reporting application frequently queries a specific subset of data filtered by a 'region' column. To minimize query latency and cost, which optimization should you implement?

A.Partition the data by region in the folder structure.
B.Create a clustered index on the region column.
C.Compress the parquet files using gzip.
D.Enable hierarchical namespace on the storage account.
AnswerA

Partitioning parquet files by region lets the reporting application prune folders and read only the relevant region's data. This reduces bytes scanned per query, cutting both latency and cost compared with scanning the full dataset on every request.

Why this answer

Partitioning the data by region in the folder structure (e.g., /region=NorthAmerica/...) enables Azure Data Lake Storage Gen2 and query engines like Azure Synapse or PolyBase to perform partition pruning. This skips scanning irrelevant files entirely, reducing I/O and query latency while lowering cost by minimizing data processed.

Exam trap

The trap here is that candidates confuse compression (Option C) with partitioning, thinking reducing file size alone minimizes I/O, but without partition pruning the engine still scans all files, negating the benefit.

How to eliminate wrong answers

Option B is wrong because clustered indexes are a SQL Server/PaaS feature and are not supported on Parquet files in Azure Data Lake Storage Gen2; they apply only to relational tables in a database. Option C is wrong because compressing Parquet files with gzip does not reduce the amount of data scanned for a filtered query—Parquet already uses column-level compression (e.g., Snappy, ZSTD), and gzip adds CPU overhead without improving partition pruning. Option D is wrong because enabling hierarchical namespace is a prerequisite for folder-based partitioning, not an optimization itself; it must already be enabled to create the partitioned folder structure.

Full explanation →
87
Multi-Selectmedium

You are using Azure Synapse Analytics to process data in a dedicated SQL pool. You need to ensure that queries against a large fact table perform well. The fact table is partitioned by date and distributed by a product key. Which two actions should you take? (Choose two.)

Select 2 answers
A.Create a nonclustered index on the date column.
B.Create a clustered columnstore index on the fact table.
C.Create a replicated table for the fact table.
D.Use round-robin distribution for the fact table.
E.Use hash distribution on the product key.
AnswersB, E

Clustered columnstore indexes are the default and most efficient storage format for large fact tables in dedicated SQL pools. They provide high compression and batch mode execution, significantly improving query performance for analytical workloads. They are particularly effective when combined with partitioning and distribution, as they allow segment elimination and parallel scans.

Why this answer

For a large fact table in a dedicated SQL pool, a clustered columnstore index provides optimal compression and query performance. Hash distribution on the product key ensures that joins with dimension tables on that key are co-located, reducing data movement. Together, these actions address both storage and distribution for analytical queries.

Exam trap

The trap here is assuming that traditional row-store indexes like nonclustered indexes are beneficial, when columnstore indexes are the preferred choice for fact tables in dedicated SQL pools.

Full explanation →
88
Multi-Selectmedium

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Select 3 answers
A.Enable TLS 1.0 for compatibility with legacy clients
B.Enable Azure Storage Service Encryption (SSE) for data at rest
C.Configure firewall rules to allow only trusted IPs
D.Use Azure RBAC and ACLs to control access to data
E.Require HTTPS for all data transfers
AnswersB, D, E

Azure Storage Service Encryption automatically encrypts data at rest using 256-bit AES, covering the stem's at-rest security requirement for Azure Data Lake Storage Gen2. It applies transparently to blobs and files with Microsoft-managed or customer-managed keys, requiring no application changes.

Why this answer

Option B is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting stored files and blobs even if the underlying media is compromised. Option D is correct because Data Lake Storage Gen2 supports a hierarchical namespace where Azure RBAC controls management-plane and broad data access while POSIX-style ACLs provide fine-grained file and directory permissions, ensuring only authorized identities can read or modify data. Option E is correct because requiring secure transfer (HTTPS) enforces TLS encryption of data in transit between clients and the storage account, preventing eavesdropping or tampering on the network.

Option A is not correct because TLS 1.0 is deprecated and insecure; enabling it weakens transport security rather than strengthening it, and Azure recommends TLS 1.2 or higher. Option C is not correct because firewall IP restrictions are a network-perimeter control, not a mechanism for securing data at rest or in transit, and they do not encrypt or authorize data access by themselves.

Exam trap

The trap here is that candidates may confuse network security controls (firewalls) or legacy protocol compatibility (TLS 1.0) with actual data encryption mechanisms, leading them to select options that address access or connectivity rather than encryption of data at rest and in transit.

Full explanation →
89
MCQmedium

You are developing a data processing pipeline for a gaming company that uses Azure Databricks. The pipeline processes game event data from Azure Event Hubs. You need to detect cheating patterns by analyzing events in real time. The solution must be able to handle high throughput and low latency. The output should be written to Azure Cosmos DB for real-time dashboards. Which approach should you use?

A.Use Azure Databricks Structured Streaming to read from Event Hubs, use Spark SQL and machine learning to detect cheating patterns, and write to Cosmos DB using the Azure Cosmos DB Spark connector.
B.Use Azure Functions with Event Hubs trigger to process each event and write to Cosmos DB.
C.Use Azure Data Factory with continuous copy to load data into Cosmos DB and then use Azure Synapse Analytics to detect patterns.
D.Use Azure Stream Analytics to query the stream for cheating patterns and output to Cosmos DB.
AnswerA

Structured Streaming reads Event Hubs incrementally, satisfying the low-latency, high-throughput requirement, while Spark SQL and MLlib detect cheating patterns in-stream. The Azure Cosmos DB Spark connector writes micro-batch results directly to Cosmos DB, enabling real-time dashboards without a separate serving layer.

Why this answer

Azure Databricks Structured Streaming is designed for scalable, low-latency stream processing and integrates with Event Hubs and Cosmos DB via connectors. It allows using Spark SQL and machine learning libraries to detect cheating patterns in real time. This approach handles high throughput and provides the necessary analytics capabilities.

The other options either lack the required real-time processing or the advanced analytics.

Exam trap

DP-203 often tests the choice between stream processing services. The trap is assuming Azure Stream Analytics is always the best for real-time analytics, but when advanced machine learning and Spark capabilities are needed, Databricks Structured Streaming is the correct choice.

How to eliminate wrong answers

Option B is wrong because Azure Functions with Event Hubs trigger is suitable for lightweight, event-driven processing but not for complex stream analytics with machine learning at scale; it may not handle high throughput and low latency as efficiently. Option C is wrong because Azure Data Factory with continuous copy is for data movement, not real-time pattern detection, and Azure Synapse Analytics is more batch-oriented. Option D is wrong because Azure Stream Analytics can query streams but lacks the advanced machine learning and Spark SQL capabilities of Databricks, and may not handle the complexity of cheating pattern detection as well.

Full explanation →
90
Multi-Selecteasy

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

Select 2 answers
A.Microsoft Entra ID sign-in logs.
B.Azure Backup reports.
C.Storage account diagnostic settings.
D.Azure Monitor and Microsoft Sentinel.
E.Azure Policy.
AnswersC, D

Storage account diagnostic settings stream control-plane and data-plane events, including authenticated read, write and delete operations, to Log Analytics, storage or Event Hubs. This provides the audit trail of data access and changes required by the stem.

Why this answer

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.

Full explanation →
91
MCQeasy

You are developing an Azure Databricks notebook to process streaming data from Azure Event Hubs. The notebook must write the processed data to a Delta table with exactly-once processing guarantees. You need to configure the write operation. Which option should you use?

A.Write the stream to a Delta table using the `writeStream` method with `format("delta")` and a checkpoint location.
B.Write the stream to a Parquet file using `writeStream` with `format("parquet")` and a checkpoint location.
C.Write the stream to an Azure SQL Database using `writeStream` with `format("jdbc")` and a checkpoint location.
D.Use `write` instead of `writeStream` to write the DataFrame to a Delta table.
AnswerA

Using `writeStream` with Delta format and a checkpoint location enables structured streaming with exactly-once semantics. Delta Lake's transaction log ensures idempotent writes, and the checkpoint tracks progress to avoid reprocessing. This is the correct approach for streaming ingestion into Delta tables with exactly-once guarantees.

Why this answer

Structured streaming to Delta Lake with `writeStream` and a checkpoint location provides exactly-once processing because Delta's transaction log and the checkpoint work together to ensure each record is processed once. Other formats like Parquet lack transactional guarantees, and batch writes cannot handle streaming data. Writing to SQL via JDBC also lacks built-in exactly-once semantics.

Exam trap

The trap here is confusing at-least-once with exactly-once, and assuming any streaming sink with a checkpoint provides exactly-once guarantees.

Full explanation →
92
Multi-Selectmedium

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

Select 2 answers
A.Disable automatic schema detection to reduce overhead.
B.Use Delta Lake's OPTIMIZE command to compact small files.
C.Use Delta Lake Z-order optimization on frequently filtered columns.
D.Cache the entire DataFrame in memory after reading.
E.Enable auto-compaction in Spark configuration.
AnswersB, C

Compacting small files improves read performance.

Why this answer

The OPTIMIZE command in Delta Lake compacts small files into larger ones, reducing the number of files that Spark must read during subsequent queries and writes. This is critical for large datasets where many small files can cause significant overhead in file listing and task scheduling. Option C is correct because Z-order optimization on frequently filtered columns improves data skipping, allowing Delta Lake to prune irrelevant files during scans, which dramatically reduces I/O and speeds up query performance.

Exam trap

The trap here is that candidates confuse auto-compaction as a Spark configuration (Option E) when it is actually a Delta Lake table property, and they overlook that caching (Option D) is not beneficial for write-heavy pipelines with large datasets.

Full explanation →
93
MCQmedium

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?

A.Avro
B.Parquet
C.CSV
D.JSON
AnswerB

Parquet stores data columnar with embedded schema and statistics, so Synapse serverless SQL reads only referenced columns and skips row groups via predicate pushdown. This satisfies the optimal query performance constraint for semi-structured JSON logs, whereas row-based formats force full scans and schema inference at runtime.

Why this answer

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.

Exam trap

The trap here is that candidates often assume semi-structured data must stay in its native JSON format for simplicity, overlooking that columnar formats like Parquet can natively store nested JSON structures via repeated fields and maps, while providing massive performance gains in serverless SQL engines.

How to eliminate wrong answers

Option A is wrong because Avro is a row-oriented format that, while efficient for write-heavy and schema-evolving scenarios, does not support column pruning or predicate pushdown as effectively as Parquet, leading to higher I/O and slower query performance in Synapse Serverless SQL. Option C is wrong because CSV is a plain-text, row-oriented format with no built-in compression or indexing, forcing full table scans and increasing data transfer costs, which degrades query performance. Option D is wrong because storing logs as raw JSON files results in verbose, uncompressed data that lacks schema enforcement and columnar optimization, causing Synapse Serverless SQL to parse entire files without the benefits of predicate pushdown or efficient compression.

Full explanation →
94
Multi-Selecthard

Which TWO Azure services can be used to monitor data pipeline runs and set up alerts for failures in Azure Data Factory?

Select 2 answers
A.Azure Data Factory monitoring views
B.Azure Log Analytics
C.Azure Sentinel
D.Azure Monitor
E.Azure Automation
AnswersB, D

Azure Log Analytics is part of Azure Monitor and is used to query pipeline logs and create alerts for failures.

Why this answer

Options B and D are correct. Azure Monitor is the primary service for collecting Azure Data Factory metrics and logs and for configuring alerts. Azure Log Analytics is used to query those logs and create failure alerts.

Option A is not a standalone Azure service; Azure Data Factory monitoring views are a built-in feature and do not independently provide alerting. Option C is a SIEM/SOAR security service, not for pipeline monitoring. Option E is for automation, not monitoring/alerting.

Full explanation →
95
MCQeasy

You need to process streaming data from Azure Event Hubs and store the results in Azure Cosmos DB for a real-time dashboard. The solution must handle duplicate events and ensure exactly-once processing. Which Azure service should you use?

A.Azure Data Factory
B.Azure Functions with Event Hubs trigger
C.Azure Stream Analytics
D.Azure Databricks Structured Streaming
AnswerC

Supports exactly-once semantics with Event Hubs.

Why this answer

(Azure Stream Analytics) is correct because it provides exactly-once processing when configured with Event Hubs and Cosmos DB output. Option A (Azure Data Factory) is batch-oriented. Option B (Azure Functions) may have at-least-once guarantees.

Option D (Azure Databricks) can achieve exactly-once but requires more configuration.

Full explanation →
96
MCQeasy

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?

A.Increase the number of Streaming Units (SU).
B.Reduce the number of Streaming Units.
C.Change the query compatibility level to 1.0.
D.Deploy a second Stream Analytics job and split the input.
AnswerA

Raising Streaming Units directly addresses the saturated compute capacity: each SU bundles CPU and memory, so adding SUs partitions the query across more nodes, relieving the 100% utilisation bottleneck. This satisfies the stem's constraint that the job is falling behind solely because existing SU allocation is exhausted.

Why this answer

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.

Exam trap

The trap here is that candidates may think reducing SU or splitting the job is a valid optimization, but the correct response is to increase SU when utilization is at 100%, as this directly addresses the resource bottleneck.

How to eliminate wrong answers

Option B is wrong because reducing the number of Streaming Units would further starve the job of resources, worsening the backlog and increasing latency. Option C is wrong because changing the query compatibility level to 1.0 does not affect resource allocation or throughput; it only alters query language features and behavior, which cannot resolve a 100% SU utilization issue. Option D is wrong because deploying a second Stream Analytics job and splitting the input does not address the root cause of resource saturation; it adds complexity and may cause ordering or partitioning issues without guaranteeing improved performance, and the original job would still be overloaded.

Full explanation →
97
Multi-Selecthard

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Select 2 answers
A.Delta Lake
B.Azure Databricks Structured Streaming
C.PolyBase
D.Azure Stream Analytics
E.Azure Data Factory
AnswersA, B

Provides ACID transactions and supports exactly-once semantics.

Why this answer

Delta Lake is correct because it provides ACID transactions, schema enforcement, and time travel capabilities, which are essential for handling late-arriving data and ensuring exactly-once semantics when combined with Structured Streaming. It allows you to merge late records into existing Delta tables using merge operations (upserts) without corrupting the data state.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics with Databricks Structured Streaming, assuming both can achieve exactly-once semantics with Delta Lake, but Stream Analytics does not write directly to Delta tables and lacks the transactional guarantees needed for idempotent late-arriving data processing in Databricks.

Full explanation →
98
Multi-Selecteasy

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

Select 2 answers
A.Azure Disk Encryption
B.Azure Information Protection
C.Customer-managed keys in Azure Key Vault
D.Storage Service Encryption (SSE)
E.Transport Layer Security (TLS)
AnswersC, D

Customer-managed keys in Azure Key Vault satisfy the at-rest encryption requirement by letting you supply your own RSA key that wraps the account's data encryption key, rather than relying on Microsoft-managed keys. This gives you control over rotation and revocation, meeting the stem's demand for a Blob Storage encryption feature.

Why this answer

Option C (Customer-managed keys in Azure Key Vault) is correct because Azure Blob Storage supports server-side encryption with customer-managed keys (CMK), where the key encryption key is stored in Azure Key Vault and used to wrap the account's data encryption key, providing control over the encryption of data at rest. Option D (Storage Service Encryption, SSE) is correct because SSE is the built-in Azure Storage feature that automatically encrypts blob data at rest using AES-256 before it is persisted to disk, and it is enabled by default for all storage accounts. Option A (Azure Disk Encryption) is not correct because it encrypts OS and data disks attached to Azure VMs (using BitLocker/DM-Crypt), not blobs in a storage account.

Option B (Azure Information Protection) is not correct because it classifies and protects files at the application/document level rather than providing storage-account-level encryption at rest for blobs. Option E (Transport Layer Security) is not correct because TLS protects data in transit over the network, not data at rest.

Full explanation →
99
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads a 500 GB staging table and then applies transformations using a stored procedure. The procedure performs many single-row updates against a large fact table, and the load now exceeds its window. You need to reduce the duration of the transformation step. What should you do?

A.Enable result set caching on the stored procedure so repeated executions reuse computed results.
B.Increase the resource class of the user running the stored procedure to grant more memory per query.
C.Convert the single-row updates to a CTAS-based pattern that creates a new table from a SELECT joining staging and fact data, then renames it.
D.Add a clustered columnstore index to the staging table to accelerate the updates.
AnswerC

Dedicated SQL pool is optimized for bulk, set-based operations, and CREATE TABLE AS SELECT writes results in parallel with minimal logging. Building a new fact table from a join of staging and existing fact data avoids the row-by-row overhead of updates, which are slow and log-heavy. Renaming via RENAME OBJECT swaps the new table into place atomically.

Why this answer

Dedicated SQL pool performs best with set-based, minimally logged bulk operations. Replacing many single-row updates with a CREATE TABLE AS SELECT that joins staging to the fact table, followed by a RENAME OBJECT to swap tables, eliminates row-level logging and locking, cutting the transformation step to a parallel bulk write.

Exam trap

The trap here is tuning memory or indexes when the real cost is the row-by-row update pattern that dedicated SQL pool handles poorly.

Full explanation →
100
MCQeasy

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?

A.Create a secondary (composite) index on the email field.
B.Change the partition key to the email field.
C.Denormalize the data by storing a copy of the email in the partition key.
D.Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.
AnswerA

A secondary index allows efficient queries on non-partition key fields.

Why this answer

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.

Exam trap

The trap here is that candidates often assume a secondary index is unnecessary or that changing the partition key is the only way to optimize non-key queries, but Azure Cosmos DB supports secondary indexes for non-partition key fields, and altering the partition key would disrupt the primary access pattern.

How to eliminate wrong answers

Option B is wrong because changing the partition key to email would break the primary access pattern (reads by user ID), causing cross-partition queries for the frequent user ID lookups and likely exceeding request unit (RU) costs. Option C is wrong because denormalizing by storing a copy of the email in the partition key does not change the partition key itself; the partition key remains user ID, so queries by email would still require a cross-partition scan unless a secondary index is used. Option D is wrong because using the change feed to maintain a separate container keyed by email introduces operational complexity and eventual consistency, and is overkill for occasional queries; a secondary index is simpler and directly supported.

Full explanation →
101
MCQeasy

You need to ensure that an Azure Data Factory pipeline retries a failed activity up to three times with a 5-minute delay between retries. How should you configure the activity?

A.Configure the Retry policy on the pipeline activity as 'Exponential' with count 3
B.Set retry to 3 and retryIntervalInSeconds to 300 in the activity policy
C.Set the activity timeout to 15 minutes and enable retry
D.Set maxRetries to 3 and delay to 5 minutes in the pipeline JSON
AnswerB

The activity policy's retry property sets the maximum retry count, and retryIntervalInSeconds defines the pause between attempts. Setting retry to 3 and retryIntervalInSeconds to 300 yields three retries at five-minute intervals, exactly matching the stated requirement.

Why this answer

Azure Data Factory activity policies expose two retry-related properties: 'retry' (an integer count) and 'retryIntervalInSeconds' (the fixed delay between attempts). Setting retry to 3 and retryIntervalInSeconds to 300 produces exactly three retries with a 5-minute (300-second) fixed interval, matching the requirement. This is configured on the activity's policy object in the pipeline JSON or via the Author UI.

Exam trap

DP-203 often tests the exact ADF property names — candidates pick 'maxRetries' or 'Exponential' because those names appear in other Azure services, but ADF specifically uses 'retry' and 'retryIntervalInSeconds'.

How to eliminate wrong answers

Option A is wrong because ADF does not offer an 'Exponential' retry policy on activities — retry intervals in ADF are fixed, not exponential (that pattern exists in other services like AWS Step Functions or Azure Functions, not ADF activity policy). Option C is wrong because activity timeout controls how long a single execution may run before being cancelled; it does not control retry count or delay, and 15 minutes is unrelated to 3 retries × 5 minutes. Option D is wrong because 'maxRetries' and 'delay' are not the correct ADF property names — the actual schema uses 'retry' and 'retryIntervalInSeconds', so this JSON would fail validation.

Full explanation →
102
Multi-Selecthard

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.)

Select 3 answers
A.Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.
B.Apply Azure Policy to enforce encryption and access policies.
C.Ingest the logs into Microsoft Sentinel and create analytics rules for anomalous patterns.
D.Enable diagnostic settings on the storage account to collect read, write, and delete logs.
E.Use Azure Monitor Metrics to track storage account transactions and latency.
AnswersA, C, D

Microsoft Defender for Storage continuously analyses data-plane telemetry and raises security alerts for anomalous access, such as unusual locations or suspicious enumeration. This directly satisfies the requirement to detect and alert on access patterns indicating a possible breach in the hierarchical-namespace account.

Why this answer

Option A is correct because Microsoft Defender for Storage analyzes data-plane telemetry on the storage account and raises security alerts for suspicious activity such as unusual access patterns, anomalous data exfiltration, or access from unusual locations, which directly addresses breach detection. Option C is correct because Microsoft Sentinel can ingest storage logs and use analytics rules (including built-in anomaly and threat-detection templates) to correlate events and alert on anomalous access patterns across the environment. Option D is correct because enabling diagnostic settings on the storage account is the prerequisite that exports read, write, and delete data-plane logs (the StorageRead/StorageWrite/StorageDelete categories) to a Log Analytics workspace, Event Hub, or storage account, providing the raw telemetry that Sentinel and Defender for Storage rely on for anomaly detection.

Option B does not belong because Azure Policy enforces configuration and compliance (for example, requiring encryption or HTTPS), but it does not detect or alert on unusual access patterns. Option E does not belong because Azure Monitor Metrics provides aggregate numeric time-series data such as transaction counts, latency, and availability, not per-request access detail needed to identify anomalous access patterns.

Full explanation →
103
MCQeasy

You use Azure Data Lake Storage Gen2 with a hierarchical namespace. You need to delegate permissions to a group of data scientists so they can create folders and upload files only within a specific directory path. What is the best way to achieve this?

A.Use a stored access policy to grant permissions to the directory.
B.Set ACL entries on the specific directory path granting read, write, and execute permissions to the users.
C.Generate a shared access signature (SAS) with permissions scoped to the specific directory.
D.Assign the Storage Blob Data Contributor role to the users at the storage account level.
AnswerB

POSIX ACLs on the target directory grant read, write and execute to the scientists, scoping access to that path only. This satisfies the constraint of limiting folder creation and uploads to a specific directory, which RBAC roles cannot do at path level.

Why this answer

Azure Data Lake Storage Gen2 with hierarchical namespace supports POSIX-like ACLs at the directory and file level. To delegate permissions to a group of data scientists so they can create folders and upload files only within a specific directory path, you set ACL entries on that directory granting the necessary permissions (read, write, execute) to the group. This allows granular access control without granting broader permissions at the storage account level.

Exam trap

The trap here is confusing SAS tokens with ACLs. Many candidates think SAS is the go-to for granular access, but in ADLS Gen2 with hierarchical namespace, ACLs are the native and recommended method for directory-level permissions. Also, some might choose the broad role assignment for simplicity, ignoring the least privilege requirement.

How to eliminate wrong answers

Option A is wrong because a stored access policy is used for shared access signatures (SAS) on containers, not for direct ACL management on directories. Option C is wrong because a SAS provides temporary, token-based access and is not the best method for persistent, directory-level permission delegation to a group; it also lacks the granularity of ACLs for hierarchical namespaces. Option D is wrong because assigning the Storage Blob Data Contributor role at the storage account level grants overly broad permissions across the entire account, violating the principle of least privilege.

Full explanation →
104
MCQhard

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?

A.The credential used to access ADLS Gen2 does not have sufficient permissions.
B.The serverless SQL pool does not support reading Parquet files.
C.The external table definition is missing the SCHEMA_NAME parameter.
D.The DATA_COMPRESSION setting is incompatible with Parquet files.
AnswerA

Insufficient permissions (e.g., missing Storage Blob Data Reader role) cause zero rows.

Why this answer

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.

Exam trap

The trap here is that candidates assume a missing or misconfigured schema parameter (like SCHEMA_NAME) would cause zero rows, but in reality, permission issues are the primary cause of empty results when the data path is correct and the file format is supported.

How to eliminate wrong answers

Option B is wrong because Azure Synapse serverless SQL pool fully supports reading Parquet files, including partitioned data like /year=2024/. Option C is wrong because the SCHEMA_NAME parameter is optional in CREATE EXTERNAL TABLE and is used for schema binding, not for data access or row retrieval. Option D is wrong because DATA_COMPRESSION is not applicable to Parquet files; Parquet has its own internal compression (e.g., Snappy, gzip) and the setting is ignored or causes an error, not silent zero rows.

Full explanation →
105
MCQeasy

You are configuring security for an Azure Data Lake Storage Gen2 account that stores sensitive data. You need to ensure that all data access is logged and that you can audit who accessed the data and when. You also need to retain the logs for 90 days. What should you do?

A.Configure diagnostic settings to send logs to a Log Analytics workspace.
B.Enable soft delete for blobs.
C.Use Azure Storage Analytics logging to a storage account.
D.Enable Azure Defender for Storage.
AnswerA

Configuring diagnostic settings for Azure Data Lake Storage Gen2 allows you to stream resource logs, such as StorageRead and StorageWrite, to a Log Analytics workspace. These logs capture details about each access, including the identity, operation, and timestamp. You can then query and retain the logs in Log Analytics for 90 days (or more) to meet auditing requirements.

Why this answer

Diagnostic settings in Azure allow you to export resource logs to a Log Analytics workspace, where you can analyze and retain them for up to 90 days (or longer with custom retention). This provides the necessary auditing capability for Data Lake Storage Gen2 access.

Exam trap

The trap here is confusing threat detection with access logging; Azure Defender alerts on suspicious activity but does not provide a full audit trail.

Full explanation →
106
MCQhard

You maintain an Azure Stream Analytics job that reads from an Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. During peak load the job produces late-arriving events that are dropped, and downstream reports show missing rows. You must retain and process events that arrive after the watermark by up to several minutes without changing the input. What should you configure?

A.Change the output to use a partitioned write pattern with a batch size of one row.
B.Set the event ordering policy out-of-order tolerance window to the required number of minutes.
C.Configure the Event Hubs input to use a consumer group with a longer retention period.
D.Increase the streaming units allocated to the job so more events are processed per second.
AnswerB

The out-of-order tolerance window tells the job how long to wait for events whose application timestamps are earlier than events already processed. Raising it to cover the observed delay allows those late events to be admitted and included in windowed aggregates instead of being discarded, which directly addresses the missing rows without modifying the Event Hubs input.

Why this answer

Late-arriving events are governed by the event ordering policy, whose out-of-order tolerance window defines how long the job accepts events behind the current watermark. Setting the window to cover the observed delay lets those events participate in temporal queries, restoring the missing rows without touching the upstream Event Hubs configuration or scaling compute.

Exam trap

The trap here is treating missing late events as a throughput problem and scaling streaming units, when the real control is the temporal tolerance in the event ordering policy.

Full explanation →
107
MCQhard

A financial services company needs to store transaction data for audit purposes. The data must be immutable and cannot be modified or deleted for 7 years. Which Azure storage feature should be used?

A.Azure Blob Storage immutability policy (time-based retention).
B.Azure Blob Storage soft delete.
C.Azure Blob Storage versioning.
D.Azure Files share snapshots.
AnswerA

A time-based retention policy on Azure Blob Storage enforces write-once, read-many (WORM) semantics, blocking modification or deletion of transaction blobs until the retention interval lapses. Setting it to seven years directly satisfies the audit requirement for immutability, unlike standard blob storage where objects remain freely overwritable.

Why this answer

Azure Blob Storage immutability policy with time-based retention ensures that blobs cannot be modified or deleted for a specified retention period (e.g., 7 years). This meets the audit requirement for immutable storage by locking the data at the storage level, preventing any writes or deletes until the retention interval expires. The policy is enforced at the container level and applies to all blobs within, making it the correct choice for regulatory compliance.

Exam trap

The trap here is that candidates confuse soft delete or versioning with immutability, not realizing that only a locked time-based retention policy provides the strict write-once, read-many guarantee required for audit data that cannot be modified or deleted for a fixed duration.

How to eliminate wrong answers

Option B is wrong because soft delete only protects against accidental deletion by retaining deleted blobs for a configurable period, but it does not prevent modification or provide true immutability; data can still be overwritten. Option C is wrong because versioning preserves previous versions of a blob when overwritten or deleted, but it does not block writes or deletes—new versions can be created, and the current version can be modified, violating immutability. Option D is wrong because Azure Files share snapshots are point-in-time read-only copies of a file share, but they do not enforce a write-once-read-many (WORM) state on the live share; the original files can still be modified or deleted.

Full explanation →
108
MCQeasy

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

A.Azure HDInsight with Spark jobs scheduled in Azure Data Factory.
B.Azure Synapse Pipelines with mapping data flows.
C.Azure Data Factory with a custom SSIS package.
D.Azure Databricks with an Auto Loader pipeline.
AnswerB

Mapping data flows run on Synapse Spark clusters that scale down when idle, and the visual transformation logic needs no cluster management. This satisfies the stem's cost-minimisation and low-overhead constraints while cleaning and aggregating the Parquet files.

Why this answer

Azure Synapse Pipelines with mapping data flows provide a serverless, code-free transformation service that runs on managed Spark clusters, minimizing management overhead and costs. Mapping data flows can directly read Parquet from ADLS Gen2, perform cleaning and aggregation, and load into Azure Synapse SQL pool without requiring cluster management. Option A (Azure HDInsight with Spark) requires manual cluster provisioning and management, increasing operational overhead.

Option C (custom SSIS package) is legacy, not cloud-native, and requires an integration runtime for execution. Option D (Azure Databricks with Auto Loader) provides powerful stream and batch processing but incurs higher costs for a simple batch job due to cluster management and DBU consumption.

Full explanation →
109
MCQhard

You are a data engineer for a healthcare company that processes patient data. You have an Azure Databricks workspace with a cluster configured for data processing. You need to implement a solution that processes streaming data from Azure Event Hubs, enriches it with reference data stored in Azure Cosmos DB, and writes the output to Delta Lake in Azure Data Lake Storage Gen2. The solution must ensure that the data processing is fault-tolerant and can handle schema evolution. The reference data is updated infrequently. You need to choose an approach that minimizes complexity and cost. What should you do?

A.Use Azure Databricks Auto Loader with Delta Live Tables to ingest streaming data, and use Change Data Capture from Cosmos DB to update the reference data inline.
B.Use Azure Data Factory to copy data from Event Hubs to Azure Data Lake Storage Gen2 in batches, then use Azure Databricks to process and enrich with Cosmos DB.
C.Use Azure Stream Analytics to ingest from Event Hubs, join with Cosmos DB reference data, and output to Azure Data Lake Storage Gen2 in Parquet format.
D.Use Azure Databricks Structured Streaming to read from Event Hubs, use a streaming static join to enrich with reference data from Cosmos DB, and write to Delta Lake. Enable schema evolution on the Delta table.
AnswerD

Simplifies processing and handles schema evolution.

Why this answer

Azure Databricks Structured Streaming can read from Event Hubs, and a streaming static join allows enriching with reference data from Cosmos DB (which is updated infrequently). Writing to Delta Lake with schema evolution enabled handles schema changes. This approach minimizes complexity and cost by using native Databricks features without additional services.

Exam trap

The trap is that candidates might choose Delta Live Tables or Stream Analytics for streaming, but the question emphasizes minimizing complexity and cost, making Structured Streaming with static join the best fit.

How to eliminate wrong answers

Option A is wrong because using Change Data Capture from Cosmos DB adds complexity and may not be necessary for infrequently updated reference data; also, Auto Loader with Delta Live Tables is more for batch or file-based ingestion, not directly for Event Hubs streaming. Option B is wrong because Azure Data Factory batch copying introduces latency and does not provide true streaming; it also adds cost and complexity. Option C is wrong because Azure Stream Analytics cannot directly join with Cosmos DB reference data in a streaming manner without additional setup, and outputting Parquet does not provide Delta Lake's schema evolution and ACID benefits.

Full explanation →
110
MCQmedium

A financial services firm stores trade records in an Azure Data Lake Storage Gen2 account. Regulatory requirements mandate that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that the key be rotated every 90 days without re-uploading any data. The storage account currently uses Microsoft-managed keys. What should you do to meet these requirements with the least administrative effort?

A.Configure the storage account to use customer-managed keys stored in Azure Key Vault, and create a new key version in Key Vault every 90 days.
B.Deploy Azure Disk Encryption on the storage account and schedule a Key Vault key rotation task using Azure Automation.
C.Create a new storage account with customer-managed keys and migrate all trade records using Azure Data Factory, then delete the original account.
D.Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a lifecycle management policy to re-encrypt blobs every 90 days.
AnswerA

Configuring customer-managed keys for the storage account links encryption at rest to a key in Azure Key Vault. Creating a new key version every 90 days rotates the key without requiring any data re-upload, because Azure Storage automatically uses the latest key version. This directly satisfies both the CMK mandate and the rotation requirement with minimal ongoing effort.

Why this answer

Customer-managed keys in Azure Key Vault allow the storage account to use an encryption key controlled by the organization. Rotating the key is achieved by creating a new key version in Key Vault; Azure Storage automatically picks up the latest version, so no data re-upload or re-encryption job is needed. This satisfies both the CMK mandate and the 90-day rotation requirement with minimal administrative effort.

Exam trap

The trap here is assuming that key rotation requires re-encrypting or migrating data, when Azure Storage automatically uses the latest key version once customer-managed keys are configured.

Full explanation →
111
MCQeasy

Your company uses Azure Data Lake Storage Gen2. You need to ensure that data at rest is encrypted using a customer-managed key stored in Azure Key Vault. What should you configure?

A.Use Azure Policy to audit storage accounts without encryption.
B.Enable 'Azure Storage encryption' with customer-managed keys in the storage account's encryption blade.
C.Implement client-side encryption in the application code.
D.Enable 'Infrastructure encryption' for double encryption.
AnswerB

Configuring customer-managed keys in the storage account's encryption blade satisfies the requirement for encryption at rest using a key held in Azure Key Vault. Azure Storage encryption applies AES-256 to all data at rest, and selecting customer-managed keys delegates key control to Key Vault rather than Microsoft-managed keys.

Why this answer

Azure Storage encryption with customer-managed keys is configured in the encryption blade of the storage account. This ensures data at rest is encrypted using a key stored in Azure Key Vault. Option A is incorrect because Azure Policy can audit or enforce encryption but does not configure customer-managed keys.

Option C is incorrect because client-side encryption encrypts data before it reaches Azure Storage, not at rest. Option D is incorrect because infrastructure encryption provides a second encryption layer but does not use customer-managed keys for the primary encryption.

Full explanation →
112
MCQmedium

Refer to the exhibit. A data engineer wants to copy only new orders from an Azure SQL database to Azure Data Lake Storage Gen2. The pipeline runs daily at midnight. What should be added to the pipeline to ensure incremental loads?

A.Add a filter activity after the copy to remove duplicates.
B.Use a Lookup activity to get the last run timestamp and modify the query to use it.
C.Enable staging with a staging table.
D.Change the copy behavior to 'MergeFiles'.
AnswerB

A Lookup activity retrieves the stored last-run timestamp from a control table, and the source query filters orders newer than that value, so each daily run copies only new rows. This watermark pattern delivers the incremental load the stem requires.

Why this answer

To perform incremental loads from Azure SQL Database to ADLS Gen2, the pipeline must know the last successful run timestamp to fetch only new records. A Lookup activity can retrieve the last run timestamp from a control table or a file, and then the copy activity's source query can be parameterized to filter records based on that timestamp. This ensures only new orders are copied daily.

Exam trap

The trap is confusing post-copy deduplication with true incremental extraction; candidates might think filtering after copy is sufficient, but it still transfers all data and does not scale.

How to eliminate wrong answers

Option A is wrong because adding a filter activity after the copy to remove duplicates does not achieve incremental loading; it would still copy all data and then filter, which is inefficient and does not reduce the volume of data transferred. Option C is wrong because enabling staging with a staging table is used for performance optimization during copy, not for incremental data extraction. Option D is wrong because changing the copy behavior to 'MergeFiles' affects how files are written to the destination (merging multiple files into one), not how data is selected from the source.

Full explanation →
113
MCQmedium

You manage an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and completes successfully. You need to be alerted when the pipeline duration exceeds 60 minutes. You want to minimize administrative effort. What should you do?

A.Add a Web activity in the pipeline that calls a Logic App to send an email if the pipeline runs longer than 60 minutes.
B.Configure a diagnostic setting to send activity logs to a Log Analytics workspace, then create a log search alert rule using a query that filters for pipeline runs with duration greater than 60 minutes.
C.Create an Azure Monitor alert rule on the ADFPipelineRun metric with a threshold of 60 minutes.
D.Enable Azure Monitor alerts on the Failed Runs metric of the pipeline and set the threshold to 1.
AnswerB

Diagnostic settings route Azure Data Factory activity logs, including pipeline run records with a Duration property, to Log Analytics. A log search alert rule can run a scheduled query such as ADFPipelineRun | where Duration > 60m and trigger an action group when results are found. This is the standard, low-effort method to alert on pipeline duration without custom code or external monitoring.

Why this answer

To alert on pipeline duration, you need access to run-level logs that include the Duration property. Diagnostic settings export Azure Data Factory activity logs to Log Analytics, where you can write a query to find runs exceeding 60 minutes and attach an alert rule. This is the least-effort, native Azure solution.

Metric-based alerts on pipeline metrics like ADFPipelineRun or Failed Runs do not capture duration, and embedding custom activities adds unnecessary complexity.

Exam trap

The trap here is assuming that Azure Monitor metric alerts can directly monitor pipeline duration, when duration is only available in activity logs requiring a log search alert.

Full explanation →
114
MCQhard

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

A.Azure Cosmos DB with multi-region writes and eventual consistency
B.Azure Cosmos DB with single-region writes and strong consistency
C.Azure Data Lake Storage Gen2 with hierarchical namespace
D.Azure Blob Storage with hot tier and append blobs
AnswerA

Cosmos DB with multi-region writes and eventual consistency accepts writes at any region without cross-region coordination, giving the lowest write latency and high throughput. This satisfies the write-heavy, globally distributed ingestion requirement, while still serving dashboard reads.

Why this answer

Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Exam trap

The trap here is that candidates often assume strong consistency is required for real-time dashboards, but eventual consistency is sufficient for write-heavy IoT scenarios where occasional stale reads are acceptable, and multi-region writes drastically reduce latency compared to single-region writes.

Why the other options are wrong

B

Strong consistency increases write latency and single-region limits throughput.

C

Data Lake Storage is optimized for analytics, not low-latency writes.

D

Blob storage has higher write latency and append blobs are not ideal for high-throughput ingestion.

Full explanation →
115
MCQmedium

A company is designing a data storage solution for streaming IoT telemetry data. The data is JSON-formatted, arrives at up to 10,000 events per second, and must be stored for at least 30 days for real-time dashboards and ad-hoc querying. The solution must minimize operational overhead and query latency. Which Azure service should they use?

A.Azure Blob Storage with Azure Data Lake Storage Gen2
B.Azure Data Explorer (ADX)
C.Azure Cosmos DB with analytical store
D.Azure SQL Database with elastic query
AnswerB

Azure Data Explorer ingests high-volume JSON telemetry at millions of events per second and stores it in a columnar engine optimised for low-latency analytical queries, meeting the 10,000 events per second, 30-day retention and real-time dashboard requirements with minimal operational overhead.

Why this answer

Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and time-series data, ingesting up to 10,000 events per second with low latency. Its columnar storage and indexing enable sub-second queries on JSON data for real-time dashboards, while the 30-day retention is natively configurable via caching and soft-delete policies. This minimizes operational overhead by eliminating the need for manual partitioning or index tuning.

Exam trap

The trap here is that candidates confuse Azure Data Explorer with Azure Data Lake Storage, assuming that a data lake can serve real-time dashboards, but ADLS Gen2 lacks the indexing and query engine needed for sub-second latency on streaming data.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage with ADLS Gen2 is optimized for large-scale batch analytics and data lakes, not for real-time, sub-second queries on streaming data; querying JSON blobs directly incurs high latency and requires additional compute (e.g., Azure Synapse or Databricks). Option C is wrong because Azure Cosmos DB with analytical store is designed for transactional workloads with real-time analytics on operational data, but its ingestion throughput for 10,000 events/second of pure telemetry would be costly and over-provisioned, and the analytical store is better suited for hybrid transactional/analytical processing (HTAP) rather than pure streaming telemetry. Option D is wrong because Azure SQL Database with elastic query is a relational OLTP system not optimized for high-velocity JSON ingestion or time-series queries; it would require extensive schema design, indexing, and sharding to handle 10,000 events/second, and query latency would be higher due to row-based storage.

Full explanation →
116
MCQeasy

You are responsible for securing an Azure Synapse Analytics workspace that contains sensitive data. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

A.Always Encrypted with column encryption keys.
B.Transparent Data Encryption (TDE) with a customer-managed key.
C.Transparent Data Encryption (TDE) with a service-managed key.
D.Azure Storage Service Encryption with a customer-managed key.
AnswerB

TDE with a customer-managed key allows you to bring your own key stored in Azure Key Vault. This gives you control over the encryption key, including rotation and revocation. It satisfies the requirement to encrypt data at rest using a customer-managed key in Azure Key Vault.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault provides encryption at rest for Azure Synapse Analytics dedicated SQL pools. This allows you to manage the encryption key, including rotation and revocation, and meets compliance requirements for customer-managed keys.

Exam trap

The trap here is confusing Always Encrypted with TDE, or assuming that Azure Storage Service Encryption applies to Synapse dedicated SQL pools.

Full explanation →
117
MCQeasy

You need to store JSON files from an external partner in Azure Blob Storage. The files contain sensitive financial data. Which access method provides the highest security while allowing the partner to upload files?

A.Share the storage account access key with the partner
B.Configure a firewall to allow only the partner's IP address
C.Generate a shared access signature (SAS) with write-only permission and expiry time
D.Enable anonymous public access to a container
AnswerC

A Shared Access Signature (SAS) with write-only permission and an expiry time allows the partner to upload files without exposing the account key. It limits access to exactly what is needed (write only) and automatically revokes after the expiry, providing the highest security for sensitive financial data.

Why this answer

A Shared Access Signature (SAS) with write-only permission and an expiry time provides delegated, time-limited access to Azure Blob Storage without exposing the storage account key. This ensures the partner can upload JSON files but cannot read, modify, or list existing blobs, and access automatically revokes after the expiry, meeting the highest security requirement for sensitive financial data.

Exam trap

The trap here is that candidates often confuse IP-based firewalls (Option B) as a security method, but firewalls do not authenticate users or control data-plane permissions, and they can be bypassed if the partner's IP changes or if the partner uses a shared network.

How to eliminate wrong answers

Option A is wrong because sharing the storage account access key grants full administrative control (read, write, delete, list) over all blobs and containers, violating the principle of least privilege and exposing the key to potential compromise. Option B is wrong because a firewall restricting to the partner's IP address does not authenticate the partner's identity or control permissions; it only limits network-level access, and the partner's IP may change, leading to access failures or security gaps. Option D is wrong because enabling anonymous public access allows anyone on the internet to read and list blobs without any authentication, completely exposing sensitive financial data.

Full explanation →
118
MCQhard

You are designing a data lake in Azure Data Lake Storage Gen2 for a large enterprise. You need to ensure that only authorized users can access the data, and you must implement the principle of least privilege. Which security mechanism should you use to grant fine-grained access to specific directories and files without modifying the underlying storage account firewall settings?

A.Azure RBAC roles combined with POSIX-like ACLs
B.Managed identities for Azure resources
C.Storage account firewall rules
D.Shared access signatures (SAS)
AnswerA

Azure RBAC grants coarse access at account, container or folder scope, while POSIX-like ACLs refine permissions down to individual directories and files for specific principals. Combining both enforces least privilege without touching the storage account firewall.

Why this answer

Azure RBAC roles combined with POSIX-like ACLs is correct because Azure Data Lake Storage Gen2 supports a hierarchical namespace that enables POSIX-style access control lists (ACLs) at the directory and file level, allowing fine-grained permissions (read, write, execute) for specific Azure AD principals. RBAC handles coarse-grained control at the management group, subscription, or storage account scope, while ACLs provide the granular, object-level authorization required for least privilege. This combination does not require altering the storage account firewall, which controls network access rather than identity-based permissions.

Exam trap

DP-203 often tests the misconception that storage account firewall rules or SAS tokens provide identity-based, fine-grained access control, when in fact only the combination of RBAC and POSIX ACLs delivers directory-level least privilege without network changes.

How to eliminate wrong answers

Option B is wrong because managed identities are used to authenticate Azure resources to other services, not to grant fine-grained access to directories and files for users. Option C is wrong because storage account firewall rules restrict network access based on IP address or virtual network, not user identity or directory-level permissions. Option D is wrong because shared access signatures (SAS) grant time-limited, delegated access via a token but do not provide the persistent, identity-based, fine-grained ACL model needed for least privilege across many users and directories.

Full explanation →
119
MCQmedium

You are designing a streaming data solution for IoT devices that generate 10,000 events per second. The data must be processed with sub-second latency and then stored in Azure Data Lake Storage Gen2 for archival. Which Azure service should you use for the stream processing?

A.Azure HDInsight including Spark Structured Streaming
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Event Hubs
AnswerB

Azure Stream Analytics provides fully managed, sub-second stream processing with built-in windowing and direct output to Data Lake Storage Gen2. Its low-latency engine handles 10,000 events per second while archiving to ADLS Gen2, meeting both latency and storage requirements.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for real-time stream processing with sub-second latency, and it natively integrates with Azure Data Lake Storage Gen2 for output. It can handle 10,000 events per second using its streaming unit scaling, and its SQL-like query language allows for low-latency transformations without the overhead of cluster management.

Exam trap

The trap here is that candidates often confuse Azure Event Hubs (ingestion) with Azure Stream Analytics (processing), or they overcomplicate the solution by choosing HDInsight Spark when a simpler, fully managed service meets the sub-second latency requirement.

How to eliminate wrong answers

Option A is wrong because Azure HDInsight including Spark Structured Streaming introduces additional latency from cluster startup and resource allocation, and it is overkill for a simple streaming pipeline that does not require complex batch or machine learning workloads. Option C is wrong because Azure Data Factory is an orchestration and ETL service designed for batch data movement and transformation, not for sub-second streaming processing. Option D is wrong because Azure Event Hubs is a data ingestion service that can receive 10,000 events per second, but it does not perform stream processing; it only acts as a buffer or event broker before processing.

Full explanation →
120
Multi-Selecteasy

You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?

Select 2 answers
A.Store data in columnar format (e.g., Parquet)
B.Create indexes on all columns
C.Enable row-level security
D.Partition the data by date
E.Enable geo-redundant storage
AnswersA, D

Parquet stores data column-wise, so time-range queries read only the timestamp and relevant value columns rather than entire rows, cutting I/O dramatically. Its built-in compression and encoding shrink the append-only telemetry at rest, lowering storage costs while sustaining the high-velocity ingestion the scenario demands.

Why this answer

Option A is correct because storing append-only IoT telemetry in a columnar format such as Parquet enables column pruning and compression, so time-range queries scan only the needed timestamp/value columns and consume far less I/O and storage, reducing cost. Option D is correct because partitioning the data by date (e.g., a partition per day or month) lets the query engine perform partition pruning, skipping entire partitions outside the requested time range, which dramatically improves time-range query performance and lowers scanned data volume. Option B is not appropriate because indexing every column on high-velocity append-only data adds heavy write and storage overhead while providing little benefit for range scans on time-series data.

Option C does not belong because row-level security is an access-control feature, not a query-performance or cost optimization. Option E is not relevant because geo-redundant storage improves durability and disaster recovery across regions but does not optimize time-range query performance and typically increases cost.

Exam trap

The trap here is that candidates often confuse indexing (B) with partitioning, but for append-only analytical workloads, indexes add write overhead and cost without benefit, while date partitioning directly enables partition elimination for time-range queries.

Full explanation →
121
MCQmedium

You are designing a data processing solution for an e-commerce company that uses Azure Synapse Analytics. The solution must process clickstream data from a web application. The data arrives in JSON format through Azure Event Hubs. You need to load the data into a dedicated SQL pool every 5 minutes with minimal latency. The data volume is about 100 MB every 5 minutes. You want to use PolyBase for loading. Which approach should you use?

A.Use Azure Stream Analytics to transform the JSON data and output directly to the dedicated SQL pool.
B.Use Azure Data Factory with a Copy activity to copy data from Event Hubs to Azure Data Lake Storage Gen2 as JSON files, then use a PolyBase activity to load from ADLS Gen2 to the dedicated SQL pool.
C.Use Azure Databricks to read from Event Hubs, transform the data, and write to the dedicated SQL pool using JDBC.
D.Use PolyBase directly from Event Hubs to dedicated SQL pool by creating an external data source that points to Event Hubs.
AnswerB

PolyBase cannot read Event Hubs directly; it queries external tables over ADLS Gen2 or Blob Storage. Landing the JSON via a Copy activity first satisfies the 5-minute, 100 MB latency requirement, then a PolyBase activity loads it into the dedicated SQL pool.

Why this answer

Using Azure Data Factory to copy data from Event Hubs to ADLS Gen2 as JSON files, then using PolyBase to load into the dedicated SQL pool, is the recommended approach. PolyBase can efficiently load large volumes from ADLS Gen2, and Data Factory provides a scalable, low-latency pipeline. This approach leverages PolyBase's parallel loading capabilities and is cost-effective for 100 MB every 5 minutes.

Exam trap

DP-203 often tests the misconception that PolyBase can directly connect to Event Hubs, but it requires an intermediate storage layer like ADLS Gen2.

How to eliminate wrong answers

Option A is wrong because Azure Stream Analytics can output to a dedicated SQL pool, but it may not be as efficient for batch loading every 5 minutes and could incur higher costs. Option C is wrong because Azure Databricks with JDBC writes may not scale as well as PolyBase and could be more expensive. Option D is wrong because PolyBase cannot directly connect to Event Hubs as an external data source; it requires a supported data source like ADLS Gen2 or Blob Storage.

Full explanation →
122
MCQhard

You are optimizing a pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure Synapse Analytics. The pipeline uses a copy activity with PolyBase. The data is partitioned by date in Blob Storage. You notice that the load is slow. What is the most likely cause?

A.The source files are stored in Azure Blob Storage instead of Data Lake Storage Gen2
B.The source files are in CSV format instead of Parquet
C.The source files are too many and too small (e.g., thousands of 1 MB files)
D.The sink table has a clustered columnstore index
AnswerC

PolyBase reads many small files inefficiently because each file incurs separate metadata and open/close overhead, and parallelism is limited by file count rather than volume. Thousands of 1 MB files therefore throttle throughput, making file granularity—not total data size—the bottleneck in this Blob Storage to Synapse copy.

Why this answer

PolyBase in Azure Synapse Analytics performs best when reading large, contiguous files. When the source contains thousands of small files (e.g., 1 MB each), PolyBase must initiate a separate read operation for each file, causing excessive overhead from file open/close operations and metadata requests. This dramatically reduces throughput compared to reading fewer, larger files.

Exam trap

The trap here is that candidates often focus on file format (Parquet vs. CSV) or storage type (Blob vs. ADLS Gen2) as the primary performance factor, when in reality the number and size of files is a more common and impactful bottleneck in PolyBase loads.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage is fully supported as a PolyBase source; Data Lake Storage Gen2 offers hierarchical namespace benefits but does not inherently improve PolyBase load speed. Option B is wrong because while Parquet is more efficient for analytics due to columnar storage and compression, CSV is still a valid PolyBase source and the primary bottleneck here is file count, not format. Option D is wrong because a clustered columnstore index is actually recommended for Synapse Analytics tables to improve query performance and compression; it does not slow down the PolyBase load itself.

Full explanation →
123
MCQhard

You are tuning a dedicated SQL pool in Azure Synapse Analytics. A query that joins two large tables (fact_sales and dim_product) is slow. The fact_sales table is hash-distributed on product_id, and dim_product is replicated. You notice that the query plan shows a shuffle move. What is the most likely cause?

A.The fact_sales table uses clustered columnstore index.
B.The dim_product table is replicated, causing a broadcast join.
C.Statistics are out of date on both tables.
D.The join condition does not include the distribution key for fact_sales.
AnswerD

Hash distribution on product_id means rows with the same product_id reside on one distribution. If the join predicate omits product_id, the engine cannot co-locate fact and dimension rows, forcing a shuffle move to redistribute data before the join completes.

Why this answer

When the join condition does not include the distribution key (product_id) of the hash-distributed fact_sales table, the SQL engine cannot perform a collocated join. Instead, it must shuffle data across distributions to satisfy the join, which introduces expensive data movement. The shuffle move in the query plan directly indicates this redistribution.

Exam trap

The trap here is that candidates often confuse a shuffle move with a broadcast join or blame indexing, but the root cause is the mismatch between the join key and the distribution key, which forces data movement regardless of other optimizations.

How to eliminate wrong answers

Option A is wrong because a clustered columnstore index is optimized for large fact tables and typically improves query performance; it would not cause a shuffle move. Option B is wrong because a replicated table (dim_product) is designed to avoid shuffles by having a copy on each distribution, enabling a broadcast join without data movement. Option C is wrong because out-of-date statistics can lead to suboptimal plans but do not directly force a shuffle move; the shuffle is a structural requirement based on the join key not matching the distribution key.

Full explanation →
124
MCQhard

You are developing an Azure Databricks notebook that reads a large Delta table, performs a join with a smaller reference table, and writes the result back to Delta Lake. The job runs on a cluster with autoscaling enabled and frequently spills to disk during the join. You need to reduce shuffle and improve performance without changing the result. Which action should you take?

A.Repartition the large Delta table by the join key before the join.
B.Cache the large Delta table in memory before performing the join.
C.Broadcast the smaller reference table in the join using a broadcast hint.
D.Increase the spark.sql.shuffle.partitions value from the default to a much larger number.
AnswerC

Broadcasting the smaller table replicates it to every executor, eliminating the shuffle of the large Delta table during the join. This directly reduces disk spills and network I/O, which are the observed symptoms. Because the reference table is small, the memory overhead is acceptable, and the join result is unchanged, satisfying the requirement to improve performance without altering output.

Why this answer

When joining a large Delta table with a small reference table, broadcasting the small table avoids shuffling the large table entirely. Each executor receives a copy of the small table and performs the join locally, which removes the network exchange and disk spills observed in the job. The result set is identical, and the memory cost is modest because the broadcast side is small.

Exam trap

The trap here is reaching for shuffle-partition tuning or caching when the real fix for a large-to-small join is broadcasting the small side to eliminate the shuffle altogether.

Full explanation →
125
MCQeasy

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be organized for efficient querying by a Synapse Analytics serverless SQL pool. The data is currently stored as many small CSV files in a flat directory. You need to improve query performance and reduce the cost of scanning unnecessary data. What should you do?

A.Combine all CSV files into one large CSV file.
B.Create an external table in the serverless SQL pool that points to the CSV files.
C.Move the data to Azure Blob Storage and enable hierarchical namespace.
D.Convert the data to Parquet format and partition it by admission date.
AnswerD

Parquet is a columnar format that enables efficient compression and column pruning, reducing I/O and cost. Partitioning by admission date allows the serverless SQL pool to skip irrelevant partitions, further minimizing data scanned. This combination significantly improves query performance and reduces cost for date-filtered queries.

Why this answer

Converting to Parquet, a columnar format, enables column pruning and better compression, reducing the amount of data scanned. Partitioning by admission date allows the serverless SQL pool to prune partitions based on query filters. Together, these changes optimize query performance and lower cost.

Exam trap

The trap here is focusing on file count or storage service rather than the data format and partitioning, which are the primary drivers of query performance and cost in serverless SQL pools.

Full explanation →
126
MCQeasy

You have an Azure Data Lake Storage Gen2 account that contains sensitive data. You need to ensure that data is encrypted at rest and that you control the encryption keys. You also need to be able to audit key usage. What should you implement?

A.Azure Information Protection with Bring Your Own Key (BYOK).
B.Customer-managed keys (CMK) in Azure Key Vault for Azure Storage encryption.
C.Storage Service Encryption with Microsoft-managed keys and Azure Monitor logs.
D.Azure Disk Encryption with Azure Key Vault.
AnswerB

Customer-managed keys allow you to use your own keys in Azure Key Vault to encrypt the storage account. This gives you control over key rotation and access, and you can audit key usage through Key Vault logging. It meets the requirements for encryption at rest, key control, and auditing.

Why this answer

Customer-managed keys in Azure Key Vault allow you to encrypt an Azure Storage account with your own keys, providing control over key lifecycle and enabling auditing via Key Vault logs. This satisfies the need for encryption at rest, key control, and auditability. Other options either do not apply to storage accounts or do not grant key control.

Exam trap

The trap here is confusing Azure Disk Encryption with Storage Service Encryption. Azure Disk Encryption encrypts VM disks, not storage accounts, and does not provide the required control over keys for Data Lake Storage Gen2.

Full explanation →
127
Multi-Selectmedium

Which TWO Azure services can be used to perform real-time data processing on streaming data?

Select 2 answers
A.Azure Synapse Analytics (dedicated SQL pool)
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Logic Apps
E.Azure Databricks
AnswersB, E

Azure Stream Analytics runs continuous SQL-like queries over event streams from Event Hubs or IoT Hub, emitting results with sub-second latency. This satisfies the real-time processing requirement, since batch engines and storage services cannot transform unbounded data as it arrives.

Why this answer

Azure Stream Analytics (B) is purpose-built for real-time stream processing: it ingests continuous data from sources such as Azure Event Hubs, IoT Hub, or Blob Storage and runs SQL-like queries with temporal windows to produce low-latency outputs. Azure Databricks (E) also supports real-time processing because its Structured Streaming engine (built on Apache Spark) can continuously process streaming data from Event Hubs, Kafka, or IoT Hub with exactly-once semantics. Azure Synapse Analytics dedicated SQL pool (A) is a batch-oriented MPP data warehouse, not a real-time streaming engine, so it does not fit.

Azure Data Factory (C) is an orchestration/ETL service for scheduled batch data movement and transformation, not continuous stream processing. Azure Logic Apps (D) is a workflow automation and integration service triggered by events, but it does not perform real-time analytical stream processing.

Exam trap

Candidates often assume that any service capable of ingesting streaming data (like Synapse dedicated SQL pool) qualifies as real-time processing, or they overlook Databricks because it is often associated with batch analytics. The trap is that Synapse's dedicated SQL pool is a data warehouse, not a streaming engine; Databricks' Structured Streaming is a powerful real-time processing engine on Azure.

Full explanation →
128
Multi-Selecteasy

Which TWO techniques can you use to handle schema drift in Azure Data Factory mapping data flows?

Select 2 answers
A.Enable 'Allow schema drift' in the source transformation
B.Use derived column transformation to handle each new column manually
C.Use column pattern matching to automatically map columns with similar names
D.Use assertion rules to reject rows with unknown columns
E.Use a fixed schema mapping to ignore unknown columns
AnswersA, C

Enabling 'Allow schema drift' in the source transformation lets mapping data flows read columns absent from the defined schema and carry them through the pipeline, directly satisfying the requirement to handle drifting source structures without failure.

Why this answer

Option A is correct because enabling 'Allow schema drift' on the source transformation in a mapping data flow lets the flow read columns that are not defined in the dataset schema at design time, so newly arriving columns flow through instead of being dropped. Option C is correct because column pattern matching (rule-based mapping) lets you define rules such as name or type patterns so that columns with similar names are automatically mapped without editing the flow for every new column. Option B is not appropriate because manually adding a derived column for each new column defeats the purpose of automated drift handling and requires redeployment for every schema change.

Option D is wrong because assertion rules validate row conditions and can fail or reject rows, but they do not adapt the flow to new or renamed columns. Option E is wrong because a fixed schema mapping ignores unknown columns, which is the opposite of handling schema drift.

Exam trap

The trap here is that candidates often confuse 'handling schema drift' with 'ignoring or rejecting unknown columns' (options D and E), or they think manual column-by-column handling (option B) is a valid technique, when in fact ADF provides automated drift handling through the source setting and pattern matching.

Full explanation →
129
MCQhard

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

A.Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0
B.Add a filter in the copy activity to exclude rows with NULL Amount
C.Modify the sink table to have a default value for the Amount column
D.Change the sink table column to allow NULLs
AnswerA

A Derived Column transformation evaluates an expression per row, replacing NULL 'Amount' values with 0 before the sink write. This satisfies the sink's NOT NULL constraint while preserving every source row, avoiding data loss that filtering or rejecting rows would cause.

Why this answer

A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Exam trap

The trap here is that candidates often assume a default value on the column will automatically replace NULLs during a bulk insert, but in Azure Synapse and most SQL databases, a default only applies when the column is not referenced in the INSERT statement, not when NULL is explicitly provided.

How to eliminate wrong answers

Option B is wrong because filtering out rows with NULL Amount would cause data loss, which violates the requirement to not lose data. Option C is wrong because adding a default value to the sink table column only applies when a column is omitted from an INSERT statement; the copy activity explicitly inserts NULLs, which still violates the NOT NULL constraint regardless of a default. Option D is wrong because changing the sink table column to allow NULLs would alter the schema, potentially breaking downstream dependencies or business rules that require Amount to be non-null.

Full explanation →
130
MCQhard

You are implementing a mapping data flow in Azure Data Factory that joins a large fact table in Azure Synapse Analytics with a slowly changing dimension (SCD) table in Azure SQL Database. The fact table has 500 million rows and the dimension has 2 million rows. You need to optimize the join performance and minimize data movement. The dimension table is small enough to fit in memory. Which join type should you configure in the data flow?

A.Sort-merge join
B.Cross join
C.Broadcast join
D.Hash join
AnswerC

A broadcast join sends the smaller dataset to all compute nodes, allowing the larger dataset to be partitioned and processed locally without shuffling. Since the dimension table has only 2 million rows and fits in memory, broadcasting it eliminates the need to shuffle the 500-million-row fact table, drastically reducing data movement and improving performance. This is the optimal choice for joining a large fact with a small dimension.

Why this answer

Broadcast join is designed for scenarios where one dataset is small enough to fit in memory. By broadcasting the dimension table, the large fact table is not shuffled, minimizing data movement and improving performance. Other join types like sort-merge or hash without broadcast would require shuffling the large fact table, leading to unnecessary data movement and slower execution.

Exam trap

The trap here is assuming that a hash join always avoids shuffling, when in mapping data flows only a broadcast join explicitly sends the small table to all nodes to avoid moving the large table.

Full explanation →
131
MCQmedium

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You notice that some queries are being queued due to resource class conflicts. What should you implement to optimize performance and reduce queuing?

A.Scale the dedicated SQL pool to a higher DWU level
B.Configure workload management with workload groups and classifiers
C.Create materialized views for the most common aggregations
D.Enable result-set caching for frequently run queries
AnswerB

Workload groups with classifiers assign queries to resource buckets based on rules, giving each workload dedicated memory and concurrency. This reduces resource class conflicts and queuing by preventing competing queries from contending for the same resources.

Why this answer

Workload management with workload groups and classifiers allows you to assign queries to different resource classes and prioritize them, directly addressing resource class conflicts and reducing queuing. Option A is incorrect: scaling the pool to a higher DWU increases overall resources but does not specifically manage resource class contention; it may also incur additional cost without solving the root issue. Option C is incorrect: materialized views improve query performance by pre-aggregating data but do not affect concurrency or queuing.

Option D is incorrect: result-set caching reduces repeated computation for identical queries but does not resolve queuing caused by resource class conflicts.

Full explanation →
132
MCQmedium

You are building an Azure Data Factory pipeline that calls an external REST API returning a JSON array of records. The API paginates results using a 'nextLink' field in the response body, and the number of pages varies per run. You must ingest all pages into Azure Blob Storage in a single pipeline run. Which activity configuration should you use?

A.A Copy activity with an HTTP source and no pagination rule, relying on the API to return all records in one response.
B.A Web activity followed by a ForEach activity that iterates over a hardcoded page count.
C.A Copy activity with an HTTP source and a range-based pagination rule that increments a query parameter until no records are returned.
D.A Copy activity with an HTTP source and pagination rule set to 'NextLink' using the body field that contains the next page URL.
AnswerD

The Copy activity's HTTP connector supports pagination rules that read the next page URL from a response body field. Pointing the rule at the 'nextLink' property makes the activity follow each server-provided link until the field is absent or null. This handles a variable number of pages within one activity and writes the combined result to Blob Storage.

Why this answer

When a REST API advertises the next page through a body field such as nextLink, the HTTP connector's pagination rule must be set to that field. The Copy activity then requests each returned URL in turn until the field is missing, capturing a variable number of pages in one run without custom looping.

Exam trap

The trap here is choosing range or offset pagination when the API hands back an explicit next-page URL in the response body.

Full explanation →
133
MCQmedium

You are building an Azure Stream Analytics job that reads from an Azure Event Hub capturing device telemetry. The job must emit results into an Azure Synapse Analytics dedicated SQL pool. You need to minimize latency and avoid intermediate storage. What should you do?

A.Configure the job to output to Azure Cosmos DB, then use a Synapse pipeline to copy data into the dedicated SQL pool.
B.Configure the Stream Analytics job to output directly to the dedicated SQL pool using the Azure Synapse Analytics output adapter.
C.Use Azure Functions as the output to insert rows into the dedicated SQL pool.
D.Write the Stream Analytics output to Azure Blob Storage, then use PolyBase in the dedicated SQL pool to load the data.
AnswerB

Stream Analytics natively supports Azure Synapse Analytics as an output, writing directly to a dedicated SQL pool table via the built-in connector. This avoids staging the data in Blob Storage or Data Lake, reducing end-to-end latency and eliminating an extra hop. The connector batches rows for efficient inserts, so it suits near-real-time ingestion into a dedicated SQL pool without custom code.

Why this answer

Stream Analytics provides a first-class output adapter for Azure Synapse Analytics dedicated SQL pools, enabling direct writes without staging. This minimizes latency and avoids intermediate storage, matching the requirement. The other approaches insert extra services or storage layers, adding delay and operational overhead that the scenario specifically seeks to avoid.

Exam trap

The trap here is assuming that a dedicated SQL pool cannot be written to directly from a streaming job and that data must always be staged in Blob Storage or Data Lake first.

Full explanation →
134
MCQeasy

You have an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage. The pipeline runs successfully most of the time, but occasionally fails with a 'FTP server connection refused' error during peak hours. You need to minimize these failures with minimal cost. What should you do?

A.Add a retry policy to the copy activity with a backoff interval.
B.Set up Azure ExpressRoute to improve network reliability.
C.Migrate the FTP server to SFTP.
D.Increase the parallel copy count in the copy activity.
AnswerA

Transient connection refusals during peak load are best absorbed by retry logic. A retry policy with backoff interval reattempts the copy activity after progressively longer waits, resolving intermittent FTP failures at negligible cost and no infrastructure changes.

Why this answer

A retry policy with a backoff interval on the copy activity handles transient 'connection refused' errors during peak hours by automatically reattempting after a delay, smoothing over temporary FTP server saturation. It is the lowest-cost, least-invasive fix and directly targets intermittent failures without infrastructure changes.

Exam trap

The trap is choosing an infrastructure or protocol change (ExpressRoute, SFTP) for what is a transient, intermittent error — the exam rewards the minimal-cost, built-in retry mechanism.

How to eliminate wrong answers

Option B is wrong because ExpressRoute is a dedicated private network connection with significant cost and provisioning time, and it does not address FTP server-side connection limits during peak load. Option C is wrong because migrating to SFTP changes the protocol and security posture but does not resolve connection-refused errors caused by server capacity or connection limits. Option D is wrong because increasing parallel copy count would open more concurrent connections, likely worsening the FTP server's connection-refused condition rather than alleviating it.

Full explanation →
135
MCQhard

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution will query data stored in Parquet files in Azure Data Lake Storage Gen2. You need to ensure that the queries are optimized for performance. Which action should you take?

A.Increase the MAXDOP setting in the query.
B.Convert the Parquet files to CSV format for faster parsing.
C.Create materialized views on the external tables.
D.Partition the Parquet files by date and use partition pruning in the query.
AnswerD

Partitioning Parquet files by date lets the serverless SQL pool read only relevant folders, since partition pruning eliminates scanning of unrelated files. This directly reduces data scanned and I/O, which is the dominant performance constraint for serverless queries over Data Lake Storage Gen2.

Why this answer

In Azure Synapse serverless SQL pool, performance is dominated by how much data must be read from storage, since there is no persistent compute or indexing. Partitioning Parquet files by date and filtering on the partition column enables partition pruning, so the engine reads only the relevant folders instead of scanning the entire dataset. This dramatically reduces I/O and query time, making D the correct optimization.

Exam trap

DP-203 often tests whether candidates know serverless SQL pool limitations; the trap is picking materialized views (a dedicated pool feature) or MAXDOP tuning, which do not address the I/O-bound nature of serverless queries.

How to eliminate wrong answers

Option A is wrong because MAXDOP controls the degree of parallelism for a query, but serverless SQL pool manages parallelism automatically and MAXDOP does not reduce the volume of data scanned — the primary bottleneck. Option B is wrong because CSV is row-based and uncompressible compared to columnar Parquet; converting to CSV would increase I/O and slow parsing, the opposite of optimization. Option C is wrong because materialized views are not supported on external tables in serverless SQL pool (they are a dedicated SQL pool feature), so this action is not even possible.

Full explanation →
136
MCQeasy

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

A.Azure Table Storage
B.Azure Cosmos DB
C.Azure Data Lake Storage Gen2
D.Azure SQL Database
AnswerB

Azure Cosmos DB stores schema-agnostic JSON natively and indexes every property automatically, so evolving schemas need no migrations. Its multi-region replication delivers the required global distribution, while single-digit-millisecond reads and writes satisfy the low-latency constraint. The SQL API queries semi-structured documents directly, unlike fixed-schema relational services.

Why this answer

Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's key-value model with document storage, overlooking that it lacks native JSON support and global low-latency query capabilities, while Azure Cosmos DB is specifically designed for these requirements.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store that does not natively support JSON documents or complex nested structures; it requires manual serialization and lacks global distribution with low-latency guarantees. Option C is wrong because Azure Data Lake Storage Gen2 is optimized for large-scale batch analytics and data lakes, not for low-latency transactional queries on semi-structured data. Option D is wrong because Azure SQL Database requires a fixed relational schema and does not handle dynamic schema changes without manual migrations, nor does it offer the same turnkey global distribution as Cosmos DB.

Full explanation →
137
Drag & Dropmedium

Drag and drop the steps to implement incremental data loading using Azure Data Factory into the correct order.

Drag or tap steps into the slots.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Incremental loading requires a watermark column. The pipeline retrieves the last watermark, copies data changed since then, then updates the watermark for next run.

Full explanation →
138
MCQmedium

You are a data engineer at a healthcare company. Your Azure Synapse Analytics workspace contains a dedicated SQL pool that holds patient records. A new compliance rule requires that all queries against the dedicated SQL pool be audited, and that any attempt to access data from an unauthorized IP address be logged. You need to configure auditing for the dedicated SQL pool. What should you do?

A.Enable auditing on the Azure Synapse Analytics workspace and set the audit log destination to a Log Analytics workspace.
B.Enable Microsoft Defender for SQL on the dedicated SQL pool and set the audit log destination to Azure Storage.
C.Configure Azure SQL Database auditing for the dedicated SQL pool by using the master database.
D.Enable auditing on the dedicated SQL pool and configure the audit log destination to Azure Storage, Log Analytics, or Event Hubs.
AnswerD

Dedicated SQL pool auditing is configured directly on the pool and supports Azure Storage, Log Analytics, and Event Hubs as destinations. This captures all queries and unauthorized IP access attempts, meeting the compliance requirement. The setting is found in the Azure portal under the dedicated SQL pool's Security section, and it can also be set via PowerShell or Azure CLI.

Why this answer

Auditing for an Azure Synapse Analytics dedicated SQL pool must be enabled directly on the pool, not at the workspace or Azure SQL Database level. The audit logs can be sent to Azure Storage, Log Analytics, or Event Hubs, providing a full record of queries and unauthorized access attempts. This configuration satisfies the compliance requirement to audit all queries and log unauthorized IP access attempts.

Exam trap

The trap here is assuming that workspace-level auditing or Microsoft Defender for SQL automatically covers dedicated SQL pool auditing, when in fact dedicated SQL pool auditing is configured separately on the pool itself.

Full explanation →
139
MCQmedium

You are building an Azure Synapse Analytics pipeline that processes JSON files landing in Azure Data Lake Storage Gen2. The files contain nested arrays representing order line items. You need to flatten this nested structure into a tabular format within a Mapping Data Flow before loading to a dedicated SQL pool. The solution must minimize data movement and avoid writing intermediate files to storage. Which transformation should you use to flatten the nested arrays?

A.Flatten transformation
B.Derived Column transformation
C.Aggregate transformation
D.Pivot transformation
AnswerA

The Flatten transformation in Mapping Data Flows is specifically designed to unroll nested arrays into multiple rows, preserving parent column values. It operates in-memory during the data flow execution, so no intermediate storage is required. This directly addresses the requirement to flatten nested JSON arrays without writing intermediate files, making it the correct choice for this scenario.

Why this answer

Flatten transformation is purpose-built for unrolling nested arrays into rows within Mapping Data Flows. It processes data in-memory during pipeline execution, eliminating the need for intermediate files. Derived Column, Aggregate, and Pivot transformations do not change the grain from array elements to rows, so they cannot produce the required tabular output from nested JSON.

Exam trap

The trap here is assuming that any transformation that manipulates columns can flatten arrays, but only Flatten explicitly unrolls nested structures into rows.

Full explanation →
140
Multi-Selectmedium

Which THREE options are valid ways to transform data in Azure Synapse Analytics?

Select 3 answers
A.Use Power Query online in Synapse pipelines.
B.Use T-SQL scripts in a dedicated SQL pool.
C.Use Mapping Data Flows in Synapse pipelines.
D.Use Spark notebooks in Synapse Spark pools.
E.Use Azure Machine Learning pipelines for data wrangling.
AnswersB, C, D

T-SQL is a primary way to transform data in Synapse SQL pools.

Why this answer

T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Exam trap

The trap here is that candidates often confuse Power Query Online (a Power BI/ADF feature) with Mapping Data Flows (a Synapse pipeline activity), or assume Azure Machine Learning pipelines are valid for data wrangling in Synapse, when in fact they are separate services for ML lifecycle management.

Full explanation →
141
MCQeasy

You are tasked with designing a data storage solution for a social media analytics company. They need to store user profile data (JSON) and social media posts (text and images). The data is used for machine learning models that require fast random access to individual user profiles and the ability to run analytical queries over posts. The solution must provide low-latency reads for user profiles (milliseconds) and support for large-scale analytics on posts. Which combination of Azure data services should you recommend?

A.Azure Cosmos DB for user profiles and Azure Data Lake Storage Gen2 for posts
B.Azure Cosmos DB for both user profiles and posts
C.Azure SQL Database for both user profiles and posts
D.Azure Table Storage for user profiles and Azure Blob Storage for posts
AnswerA

Cosmos DB delivers single-digit-millisecond point reads for JSON profiles through its partitioned, indexed document store, meeting the low-latency requirement. Data Lake Storage Gen2 provides hierarchical namespace and massively parallel analytical engines for large-scale post analytics, satisfying the second half of the workload.

Why this answer

Azure Cosmos DB provides low-latency (millisecond) reads for user profiles via its indexing and partitioning capabilities, ideal for fast random access. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with Blob Storage, enabling large-scale analytical queries on posts using tools like Azure Synapse Analytics or Spark, while efficiently storing text and images.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for both workloads (Option B) because they assume its multi-model support handles analytics, but they overlook that Cosmos DB is a transactional database not designed for large-scale analytical queries, while ADLS Gen2 is purpose-built for data lakes and analytics.

How to eliminate wrong answers

Option B is wrong because using Azure Cosmos DB for both profiles and posts would be cost-prohibitive for large-scale analytics on posts, as Cosmos DB is optimized for transactional workloads, not petabyte-scale analytical queries, and lacks native support for hierarchical file storage. Option C is wrong because Azure SQL Database is a relational store that struggles with semi-structured JSON profiles and large binary images, and it cannot scale to handle massive analytical workloads on posts without significant performance degradation and cost. Option D is wrong because Azure Table Storage is a NoSQL key-value store that does not support complex queries or indexing for fast random access to JSON profiles, and Azure Blob Storage lacks a hierarchical namespace and native analytical integration, making large-scale analytics inefficient.

Full explanation →
142
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. The solution must support exactly-once semantics and handle late-arriving data. Which Azure service should you use to implement this solution?

A.Azure Data Factory with tumbling window trigger.
B.Azure Stream Analytics.
C.Azure Functions with Event Hubs trigger.
D.Azure HDInsight Spark Structured Streaming.
AnswerB

Azure Stream Analytics provides built-in exactly-once processing guarantees and event-time handling with configurable late-arrival and out-of-order tolerances, satisfying both constraints. Alternatives such as Spark Structured Streaming or Event Hubs Capture require additional engineering to achieve the same delivery and watermark semantics.

Why this answer

Azure Stream Analytics is the correct choice because it natively integrates with Azure Event Hubs and dedicated SQL pools, supports exactly-once semantics through checkpointing and output deduplication, and provides built-in handling for late-arriving data via configurable late arrival tolerance windows and out-of-order event policies.

Exam trap

The trap here is that candidates often confuse batch-oriented services like Azure Data Factory with streaming solutions, or assume that any event-driven compute (like Azure Functions) can provide exactly-once semantics and late-arriving data handling without understanding the specialized streaming engine requirements.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory with a tumbling window trigger is a batch-oriented orchestration service that cannot process streaming data in real time; it lacks native support for exactly-once semantics in streaming contexts and cannot handle late-arriving data with event-time ordering. Option C is wrong because Azure Functions with an Event Hubs trigger processes events individually or in small batches, does not provide built-in exactly-once output guarantees to a dedicated SQL pool, and lacks native support for late-arriving data handling such as watermarking or out-of-order policies. Option D is wrong because Azure HDInsight Spark Structured Streaming requires significant manual configuration for exactly-once semantics (e.g., managing checkpoint locations and idempotent sinks) and does not offer the same level of integrated, low-latency output to dedicated SQL pools as Azure Stream Analytics; it also adds operational overhead for cluster management.

Full explanation →
143
MCQeasy

Your company is migrating an on-premises SQL Server database to Azure SQL Database. The database includes a large fact table with hourly updates. You need to minimize downtime during migration. Which Azure service should you use to replicate data continuously?

A.Use Azure SQL Managed Instance as a target
B.Use Azure Data Factory with a copy activity
C.Use Azure Database Migration Service with online migration mode
D.Use Azure Synapse Link for SQL Server
AnswerC

Azure Database Migration Service online migration mode replicates ongoing changes continuously while the source stays live, so cutover downtime is minimised. This directly satisfies the stem's requirement for continuous replication of an hourly-updated fact table.

Why this answer

Azure Database Migration Service (DMS) with online migration mode uses continuous change data capture (CDC) to replicate ongoing changes from the source SQL Server to Azure SQL Database with minimal downtime. This is the only option that supports near-zero downtime migration by synchronizing data continuously until the final cutover, which is critical for a large fact table with hourly updates.

Exam trap

The trap here is that candidates confuse Azure Data Factory's copy activity (a batch tool) with continuous replication, or assume Azure SQL Managed Instance inherently supports migration, when in fact DMS online mode is the specific service designed for minimal-downtime migrations.

How to eliminate wrong answers

Option A is wrong because Azure SQL Managed Instance is a target platform, not a migration service; it does not provide continuous data replication from an on-premises source. Option B is wrong because Azure Data Factory with a copy activity is a batch-oriented ETL tool that performs periodic snapshots, not continuous real-time replication, and would require downtime for the final data sync. Option D is wrong because Azure Synapse Link for SQL Server is designed for real-time analytics on operational data in Azure Synapse, not for migrating databases to Azure SQL Database with continuous replication.

Full explanation →
144
MCQmedium

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Analytics pointing to Parquet files in ADLS Gen2. The query 'SELECT * FROM Sales' returns 0 rows, but the files exist. What is the most likely cause?

A.The credential does not have read permission on the storage account.
B.The files are stored in a different container or path.
C.The external table is not refreshed after creation.
D.The Parquet files are compressed with Gzip instead of Snappy.
AnswerB

External tables reference an explicit LOCATION path, so a mismatch between that path and where the Parquet files actually reside yields an empty result set rather than an error. Correcting the container or folder path in the table definition resolves the zero-row query.

Why this answer

When an external table in Azure Synapse Analytics points to Parquet files in ADLS Gen2 and returns 0 rows despite files existing, the most likely cause is that the files are stored in a different container or path than the one specified in the external table's location. This mismatch means the table is querying an empty or non-existent location.

Exam trap

The trap is assuming that 0 rows indicate a permission or format issue, when the most common cause is a simple path mismatch; candidates might overlook the importance of verifying the exact file location.

How to eliminate wrong answers

Option A is wrong because if the credential lacked read permission, the query would typically fail with a permission error, not return 0 rows. Option C is wrong because external tables in Synapse do not require a refresh after creation; they are metadata definitions that query the underlying files directly. Option D is wrong because Parquet files compressed with Gzip instead of Snappy would still be readable by Synapse (Parquet supports various compression codecs), and if there were an issue, it would likely produce an error, not 0 rows.

Full explanation →
145
MCQmedium

You are a data engineer at a healthcare company. You have an Azure Data Lake Storage Gen2 account named sthealthcare with a container named records. The container holds sensitive patient data in Parquet files. You need to ensure that only users who are members of the Azure AD group named ClinicalResearchers can read the data, while users in the group DataEngineers can read and write. Access must be managed at the directory level and must not affect other containers in the storage account. What should you do?

A.Assign the Storage Blob Data Reader role to ClinicalResearchers and Storage Blob Data Contributor role to DataEngineers at the storage account scope.
B.Enable hierarchical namespace on the storage account, then set POSIX-like ACLs on the records directory, granting read to ClinicalResearchers and read-write to DataEngineers.
C.Configure Azure RBAC roles on the records container: assign Storage Blob Data Reader to ClinicalResearchers and Storage Blob Data Contributor to DataEngineers.
D.Create a shared access signature (SAS) token for each user in ClinicalResearchers and DataEngineers with appropriate permissions, and distribute the tokens.
AnswerB

Azure Data Lake Storage Gen2 supports POSIX-like ACLs when hierarchical namespace is enabled. Applying ACLs at the directory level allows granular control per user or group without affecting other containers. This meets the requirement for directory-level access and least privilege. The groups must exist in Azure AD and be assigned as principals on the ACL entries.

Why this answer

The requirement is to manage access at the directory level within a container without affecting other containers. Azure Data Lake Storage Gen2 ACLs, enabled by hierarchical namespace, allow setting permissions on directories and files. By applying ACLs on the records directory, you grant the appropriate groups read or read-write access only to that directory and its contents.

This satisfies least privilege and directory-level control.

Exam trap

The trap here is assuming that Azure RBAC role assignments at the container level provide directory-level granularity, when in fact only POSIX ACLs on a hierarchical namespace-enabled account can enforce per-directory permissions.

Full explanation →
146
MCQeasy

A company is designing a data storage solution for IoT device telemetry data. The data is append-only, needs to be stored cost-effectively for long-term analytics, and must support querying by device ID and timestamp. Which Azure storage solution should they use?

A.Azure Data Lake Storage Gen2
B.Azure Cosmos DB
C.Azure SQL Database
D.Azure Blob Storage with hot access tier
AnswerA

Azure Data Lake Storage Gen2 suits append-only telemetry because its hierarchical namespace organises data into directories partitioned by device ID and timestamp, enabling efficient partition pruning during analytics queries. It also layers the cost-effective Hot, Cool and Archive tiers over Blob storage, satisfying the long-term retention requirement without sacrificing query performance.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines the cost-effective, append-only blob storage of Azure Blob Storage with a hierarchical namespace that enables directory-level operations and POSIX-like access control. This makes it ideal for storing large volumes of IoT telemetry data at low cost while supporting efficient querying by device ID and timestamp through partition pruning in tools like Azure Synapse Analytics or Apache Spark.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with ADLS Gen2, assuming that Blob Storage alone supports hierarchical namespace and efficient querying, when in fact ADLS Gen2 is required for the hierarchical namespace and POSIX-like directory structure that enables partition pruning and cost-effective analytics on append-only data.

How to eliminate wrong answers

Option B (Azure Cosmos DB) is wrong because it is a NoSQL database optimized for low-latency, transactional workloads with flexible schemas, not for cost-effective long-term storage of append-only telemetry data; its RU-based pricing model becomes prohibitively expensive for high-volume, append-only IoT data. Option C (Azure SQL Database) is wrong because it is a relational database designed for OLTP workloads with strong consistency and indexing, but its per-core licensing and storage costs are too high for storing petabytes of append-only telemetry data, and it does not natively support the hierarchical namespace needed for efficient partition pruning by device ID and timestamp. Option D (Azure Blob Storage with hot access tier) is wrong because while it provides cost-effective storage, the hot access tier incurs higher storage costs than the cool or archive tiers, and without the hierarchical namespace of ADLS Gen2, querying by device ID and timestamp requires full scans or external indexing, making it less efficient for analytics workloads.

Full explanation →
147
MCQmedium

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

A.Use Blob Storage event triggers to invoke Azure Functions
B.Use Azure Synapse Pipelines with a schedule and full load each time
C.Use Azure Data Factory with watermark columns in the source
D.Store processed file names in a Delta table and compare with source folder listing
AnswerD

Recording processed filenames in a Delta table gives transactional, queryable state that survives failures, and comparing it against the source listing identifies new files. This supports incremental processing while allowing late-arriving files within the 2-hour window to be picked up.

Why this answer

Storing processed file names in a Delta table allows the pipeline to track which files have already been ingested, supporting incremental processing and handling late-arriving data up to 2 hours. By comparing the current source folder listing against the Delta table, the pipeline can identify only new or late-arriving files, avoiding reprocessing and ensuring exactly-once semantics. This approach integrates seamlessly with Azure Databricks and Delta Lake's ACID transactions, providing reliable state management for batch pipelines.

Exam trap

The trap here is that candidates often choose Azure Data Factory with watermark columns (Option C) because it is a common incremental load pattern, but they overlook that watermark columns apply to row-based sources with change tracking, not to file-based sources where the challenge is tracking which files have been processed.

How to eliminate wrong answers

Option A is wrong because Blob Storage event triggers invoke Azure Functions in near-real-time, which is suitable for event-driven or streaming patterns, not for a batch pipeline that needs to track processed files incrementally with a 2-hour late-arrival window. Option B is wrong because using Azure Synapse Pipelines with a schedule and full load each time ignores incremental processing requirements and would reprocess all data, leading to inefficiency and inability to handle late-arriving data without overwriting. Option C is wrong because Azure Data Factory with watermark columns in the source is designed for incremental loads based on a timestamp or numeric column, but the scenario involves tracking files in a folder structure, not rows in a table, and watermark columns cannot track individual file names or handle late-arriving files that appear after the watermark value has advanced.

Full explanation →
148
Multi-Selecthard

Your organization uses Azure Purview for data governance. You need to ensure that sensitive data is properly classified and that access to it is monitored. Which THREE actions should you take? (Choose three.)

Select 3 answers
A.Define Azure Policy initiatives to enforce classification on all storage accounts.
B.Use Azure Sentinel to classify data as it is ingested.
C.Create custom sensitivity labels in Microsoft Purview Information Protection and apply them to data sources.
D.Integrate Azure Purview with Microsoft Defender for Cloud Apps to monitor access to sensitive data.
E.Set up automated scanning in Azure Purview to discover and classify sensitive data.
AnswersC, D, E

Custom sensitivity labels in Microsoft Purview Information Protection extend classification beyond built-in types, letting you tag organisation-specific sensitive data and apply those labels across registered data sources. This satisfies the stem's requirement that sensitive data be properly classified, while label usage feeds Purview's monitoring of access to labelled assets.

Why this answer

Option C is correct because Microsoft Purview Information Protection sensitivity labels (created in the compliance portal) are the mechanism for tagging data with sensitivity classifications, and Purview can extend these labels to data sources such as Azure SQL, Storage, and Synapse for consistent classification. Option D is correct because integrating Azure Purview with Microsoft Defender for Cloud Apps enables monitoring of user access and activities on sensitive data that Purview has classified, providing anomaly detection and governance over access. Option E is correct because Azure Purview's automated scanning uses built-in and custom classification rules to discover and classify sensitive data (for example, credit card or national ID patterns) across registered sources, which is the core way to ensure data is properly classified.

Option A is not correct because Azure Policy initiatives enforce configuration and compliance states on resources; they do not perform data classification of the contents within storage accounts. Option B is not correct because Azure Sentinel is a SIEM/SOAR solution for security analytics and threat detection, not a data classification engine for ingested data.

Exam trap

DP-203 often tests the boundary between governance tools — candidates confuse Azure Policy (resource configuration enforcement) and Azure Sentinel (SIEM) with Purview's actual classification and monitoring capabilities, picking them as if they performed data classification.

Full explanation →
149
Multi-Selectmedium

You are designing a data storage solution in Azure Synapse Analytics dedicated SQL pool. The solution must support efficient loading of large volumes of data from external sources and provide high query performance for reporting. You need to choose two table distribution types that are most appropriate for large fact tables and dimension tables respectively. (Choose two.)

Select 2 answers
A.External table
B.Replicated table
C.Hash-distributed table
D.Temporary table
E.Round-robin table
AnswersB, C

Replicated tables store a full copy of the table on every compute node. They are best for small dimension tables because they eliminate the need for data movement during joins with large fact tables. This reduces query latency and improves performance for star schema queries.

Why this answer

For a dedicated SQL pool, large fact tables should be hash-distributed on a column frequently used in joins to minimize data movement. Small dimension tables should be replicated so that joins with fact tables avoid data movement. Round-robin is for staging, and external and temporary tables are not distribution types.

This combination optimizes query performance for star schema workloads.

Exam trap

The trap here is assuming that round-robin distribution is always best for even data spread, or that external tables can replace internal tables for performance-critical fact tables.

Full explanation →
150
Multi-Selectmedium

You are building an Azure Stream Analytics job that processes JSON telemetry from Azure Event Hubs. The events contain a nested array field named `readings` with sensor values. You need to transform the data so that each sensor reading becomes a separate output row, and then write the results to Azure Synapse Analytics. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Use the STREAMING TIMESTAMP BY clause to reorder events and then apply a windowing function to explode the array.
B.Use the CROSS APPLY operator with the GetArrayElements function to expand the `readings` array into individual events.
C.Configure the output to Azure Synapse Analytics with the 'Allow user-defined functions' setting enabled.
D.Use the GetArrayElements function in a subquery to extract array elements and then use a JOIN to correlate them with the parent event.
E.Use the JavaScript UDF to parse the JSON and return a single concatenated string of all readings.
AnswersB, D

CROSS APPLY combined with GetArrayElements is the correct method in Stream Analytics to flatten a nested array. It returns one row per array element, enabling downstream processing of each sensor reading separately. This is exactly what the scenario requires to transform nested JSON into a flat rowset for Synapse Analytics.

Why this answer

The requirement is to flatten a nested array into separate rows. In Azure Stream Analytics, this is achieved by using GetArrayElements with either CROSS APPLY or a JOIN in a subquery. Both methods return one row per array element, which can then be written to Synapse Analytics.

Other options do not perform the necessary row expansion.

Exam trap

The trap here is assuming that a JavaScript UDF or output setting can flatten arrays, when the correct approach requires a specific array-expanding function in the query.

Full explanation →
Page 1

Page 2 of 7

Page 3

All pages

1234567

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

Secure, monitor, and optimize data storage and data processingDevelop data processingDesign and implement data storage
See all domains with question counts →