Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 151–225

760 questions total · 11pages · All types, answers revealed

Take a mock examExam hub
Page 2

Page 3 of 11

Page 4
151
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 →
152
MCQhard

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

A.Move the data to Azure Blob Storage cool tier.
B.Increase the Parquet file size to maximize block size.
C.Convert the container to Azure Files.
D.Enable Azure CDN to cache the files.
AnswerD

Azure CDN caches data at edge locations, reducing the number of direct read transactions to the storage account.

Why this answer

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

Exam trap

The trap here is that candidates often assume increasing file size (Option B) reduces costs because fewer files mean fewer transactions, but they overlook that each read of a large file still incurs a single transaction per API call, and transaction costs are per operation, not per file size.

How to eliminate wrong answers

Option A is wrong because moving data to Azure Blob Storage cool tier reduces storage costs but does not reduce transaction costs; in fact, cool tier has higher per-transaction charges, which would increase costs for read-heavy workloads. Option B is wrong because increasing Parquet file size to maximize block size does not reduce transaction costs; Azure Data Lake Storage Gen2 uses hierarchical namespace and transactions are counted per API call (e.g., per file read), not per block size, so larger files reduce the number of files but each read still incurs a transaction. Option C is wrong because converting the container to Azure Files introduces SMB protocol overhead and is designed for file shares, not optimized for read-heavy analytics with Parquet files; it would increase latency and complexity without reducing transaction costs.

Full explanation →
153
Multi-Selecthard

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

Select 3 answers
A.Azure Policy to enforce tagging
B.Network security groups (NSGs) on subnets
C.Data classification and labeling
D.Encryption at rest for storage accounts
E.Dynamic data masking for all databases
AnswersB, C, D

NSGs provide network-level security by filtering traffic.

Why this answer

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

Exam trap

The trap here is that candidates often confuse governance controls (like Azure Policy tagging) with actual security controls, or they assume dynamic data masking is a core defense layer when it is merely a data obfuscation feature that does not prevent unauthorized access or encryption.

Full explanation →
154
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

More SU provides more processing power.

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 →
155
MCQeasy

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline occasionally fails due to transient errors. Which monitoring solution should you use to get alerts on failures and analyze trends over time?

A.Azure Event Grid subscription for pipeline failures
B.Azure Monitor with Log Analytics workspace
C.Azure Dashboard pinned with pipeline metrics
D.Azure Data Factory Monitor in the Azure portal
AnswerB

Provides alerting and long-term trend analysis via KQL queries.

Why this answer

Azure Monitor with alerts and Log Analytics provides historical analysis and alerting. Option A (Data Factory Monitor) is for real-time monitoring but lacks long-term trend analysis. Option C (Azure Dashboard) is a visualization tool.

Option D (Event Grid) is for event-driven notifications, not analysis.

Full explanation →
156
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 →
157
Multi-Selectmedium

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

Select 2 answers
A.Use Always Encrypted with secure enclaves for specific columns.
B.Implement column-level security to filter sensitive columns.
C.Enable Transparent Data Encryption (TDE) on the SQL pool.
D.Configure Dynamic Data Masking on tables containing sensitive data.
E.Assign Azure RBAC roles to restrict access to the storage account.
AnswersA, C

Always Encrypts protects data at rest and in use.

Why this answer

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

Exam trap

Candidates often confuse Always Encrypted with Dynamic Data Masking or mistake TDE for a column-level solution. Remember: TDE encrypts the whole database, Always Encrypted protects specific columns.

Full explanation →
158
MCQhard

A company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. They notice that queries are slow and want to improve performance by reducing the amount of data read. What is the most effective strategy?

A.Partition the data by a frequently filtered column and use file elimination in queries.
B.Increase the number of compute nodes in the serverless pool.
C.Use OPENROWSET with CSV format instead of Parquet.
D.Create external tables using CETAS and query them.
AnswerA

Partitioning the data in the lake and using partition elimination reduces data read.

Why this answer

Partitioning the data in the lake and using partition elimination reduces data read. Option B is wrong because increasing the number of compute nodes is not possible in serverless; it's auto-scaling. Option C is wrong because OPENROWSET with CSV reads all files; it does not reduce data read.

Option D is wrong because CETAS is for creating external tables, not for improving query performance directly.

Full explanation →
159
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 allow you to control the encryption keys used for Storage Service Encryption.

Why this answer

Options C and D are correct. D: Storage Service Encryption (SSE) encrypts data at rest by default. C: Customer-managed keys in Azure Key Vault provide additional control over encryption keys.

Option A is wrong because Azure Disk Encryption is for VMs, not Blob Storage. Option B is wrong because Azure Information Protection is for classification and labeling, not encryption at rest. Option E is wrong because TLS is for data in transit.

Full explanation →
160
Multi-Selecthard

You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which THREE of the following actions will most likely improve query performance?

Select 3 answers
A.Create materialized views for frequently used aggregations
B.Use a smaller distribution column to improve data distribution
C.Partition large fact tables on a date column
D.Enable result-set caching for repetitive queries
E.Convert all tables to heap tables to avoid index maintenance
AnswersA, C, D

Materialized views store precomputed results, speeding up queries.

Why this answer

Materialized views precompute and store aggregated results, reducing the need to scan large tables on every query. Option C is correct because partitioning large fact tables on a date column enables partition elimination, which reduces the amount of data scanned for queries that filter on the partition key. Option D is correct because result-set caching allows repeated queries to return cached results directly without recomputation.

Option B is incorrect: using a smaller distribution column does not necessarily improve data distribution and can cause data skew if the column has low cardinality. Option E is incorrect: converting all tables to heap tables removes indexes, which typically degrades query performance; heaps are primarily used for staging or loading data, not for performance optimization.

Full explanation →
161
MCQmedium

You are working with Azure Synapse Analytics serverless SQL pool. You need to query a set of Parquet files located in ADLS Gen2. The files have nested columns (structs and arrays). Which function should you use to flatten the nested data?

A.OPENJSON
B.PIVOT
C.UNNEST
D.CROSS APPLY
AnswerA

OPENJSON can parse nested JSON structures and flatten them into rows.

Why this answer

OPENJSON is the correct function because it parses JSON text and returns objects and properties from JSON input as rows and columns. In Azure Synapse serverless SQL pool, when Parquet files contain nested columns (structs and arrays), they are exposed as JSON strings, and OPENJSON can flatten these nested structures into a relational format. This allows you to query complex nested data directly without needing to pre-process the files.

Exam trap

The trap here is that candidates confuse CROSS APPLY with a flattening function, but CROSS APPLY only invokes a table-valued function (like OPENJSON) and does not flatten data by itself.

How to eliminate wrong answers

Option B (PIVOT) is wrong because PIVOT rotates table-valued expressions by turning unique values from one column into multiple columns in the output; it does not flatten nested data. Option C (UNNEST) is wrong because UNNEST is a PostgreSQL function for expanding arrays into rows; it is not supported in Azure Synapse serverless SQL pool. Option D (CROSS APPLY) is wrong because CROSS APPLY joins a table with a table-valued function, but it does not inherently flatten nested columns; it would require an additional function like OPENJSON to parse the nested data first.

Full explanation →
162
MCQeasy

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2. You want to minimize cost and complexity. Which Azure Data Factory feature should you use?

A.Use a Lookup activity to get the maximum timestamp from the sink and filter the source
B.Use the 'Incremental copy' capability with change tracking enabled on the source
C.Use a Stored Procedure activity to delete and reinsert data
D.Use a Mapping Data Flow to compare source and sink
AnswerB

This is the simplest and most cost-effective method.

Why this answer

Azure Data Factory's 'Incremental copy' capability with change tracking on Azure SQL Database automatically identifies and transfers only the changed rows since the last run, using the built-in change tracking mechanism. This minimizes cost and complexity by avoiding custom logic for watermark columns or full reloads, as it handles the delta extraction natively.

Exam trap

The trap here is that candidates often confuse the 'Incremental copy' capability with manual watermark-based approaches (Option A) or assume that a Mapping Data Flow (Option D) is the only way to compare datasets, overlooking the native, cost-optimized change tracking integration.

How to eliminate wrong answers

Option A is wrong because using a Lookup activity to get the maximum timestamp from the sink and filter the source requires manual implementation of a watermark column, which adds complexity and does not leverage Azure SQL Database's native change tracking, potentially missing deletes or updates. Option C is wrong because using a Stored Procedure activity to delete and reinsert data performs a full reload of the target, which is costly and inefficient for incremental loading, and does not minimize cost or complexity. Option D is wrong because using a Mapping Data Flow to compare source and sink requires a full scan of both datasets to identify differences, which is resource-intensive and expensive, defeating the goal of minimizing cost and complexity.

Full explanation →
163
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 →
164
MCQmedium

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

A.Azure Blob Storage
B.Azure Cosmos DB
C.Azure Data Lake Storage Gen2
D.Azure SQL Database
AnswerA, B, C, D

Azure Blob Storage is indeed the primary service for unstructured object storage.

Why this answer

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

Exam trap

Candidates may think only one service is correct, but this is a matching question where all options are valid matches for their respective use cases.

Full explanation →
165
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

This configures 3 retries with 300 seconds (5 minutes) interval.

Why this answer

The correct configuration is to set the retry property to 3 and retryIntervalInSeconds to 300 in the activity policy. This ensures up to three retries with a 5-minute (300-second) delay between each attempt. Option A is incorrect because Azure Data Factory supports a fixed retry interval, not exponential backoff via the Retry policy.

Option C is incorrect because timeout is separate from retry configuration; retry is configured at the activity level. Option D is incorrect because the correct property name is 'retry' not 'maxRetries', and the delay is specified in seconds as 'retryIntervalInSeconds'.

Full explanation →
166
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

Correct: Defender for Storage provides built-in threat detection for Azure Storage.

Why this answer

Options A, C, and D are correct. A: Microsoft Defender for Storage provides security alerts for unusual access patterns. C: Ingesting logs into Microsoft Sentinel allows creation of analytics rules to detect anomalous patterns.

D: Diagnostic settings on the storage account collect read, write, and delete logs necessary for analysis. Option B is incorrect because Azure Policy is used for governance and enforcement of policies, not for monitoring access patterns. Option E is incorrect because Azure Monitor Metrics track transaction counts and latency but do not include detailed access logs required for detecting unusual patterns.

Full explanation →
167
Multi-Selectmedium

Which TWO actions should you take to ensure that only authorized users can access sensitive data in an Azure Synapse Analytics dedicated SQL pool?

Select 2 answers
A.Configure Azure Active Directory authentication
B.Enable dynamic data masking on all columns
C.Implement row-level security
D.Implement column-level security
E.Enable transparent data encryption
AnswersC, D

Row-level security filters rows based on user identity to prevent unauthorized access.

Why this answer

Row-level security (RLS) and column-level security are the two correct actions because they directly restrict data access at the row and column granularity within a dedicated SQL pool. RLS uses security predicates to filter which rows a user can query, while column-level security denies access to specific columns for unauthorized principals. Both are native features of Azure Synapse dedicated SQL pools that enforce authorization on the data plane.

Exam trap

The trap here is confusing data protection features (masking, encryption) with access control features (RLS, column-level security), leading candidates to select dynamic data masking or TDE instead of the correct granular authorization mechanisms.

Full explanation →
168
MCQmedium

You are designing a data processing pipeline in Azure Synapse Analytics. The pipeline must ingest streaming data from Azure Event Hubs, perform real-time aggregations, and store the results in a dedicated SQL pool. Which component should you use to perform the real-time transformations?

A.Azure Databricks with Structured Streaming
B.Azure Data Factory
C.Azure Synapse Pipelines
D.Azure Stream Analytics
AnswerD

Azure Stream Analytics is optimized for real-time stream processing and can output to Synapse dedicated SQL pool.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for processing streaming data from sources like Azure Event Hubs. It supports SQL-based query language for performing aggregations, windowing functions, and temporal joins, and can directly output results to a dedicated SQL pool in Azure Synapse Analytics. This makes it the optimal component for ingesting streaming data, performing real-time transformations, and storing aggregated results in a Synapse dedicated SQL pool.

Exam trap

Microsoft often tests the distinction between batch and real-time processing services, and the trap here is that candidates may confuse Azure Synapse Pipelines or Azure Data Factory as capable of real-time streaming, when in fact they are batch-oriented orchestration tools.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming, while capable of real-time processing, introduces additional complexity and cost, and is not the native or most straightforward choice for directly outputting to a dedicated SQL pool in Synapse; it typically requires a separate write path or connector. Option B is wrong because Azure Data Factory is an orchestration and ETL service for batch data movement and transformation, not designed for real-time streaming ingestion or continuous aggregation. Option C is wrong because Azure Synapse Pipelines are built on Azure Data Factory and share the same batch-oriented architecture, lacking native support for real-time stream processing and windowed aggregations.

Full explanation →
169
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 →
170
MCQhard

You are a data engineer for a global e-commerce company. You need to design a data processing solution using Azure Databricks that processes real-time clickstream data from Azure Event Hubs. The solution must join the streaming data with a slowly changing dimension (SCD) table that stores product details. The SCD table is stored in Azure Data Lake Storage Gen2 as Delta format and is updated every few hours. The joined results must be written to a Delta table for near-real-time dashboards. The key requirement is to ensure that the join always uses the latest version of the SCD data without reprocessing the entire stream. The solution must minimize latency and cost. Which approach should you recommend?

A.Use Spark Structured Streaming with foreachBatch and read the SCD table inside the foreachBatch function.
B.Use Spark Structured Streaming with a static DataFrame for the SCD table and refresh it periodically using a trigger that reads the latest snapshot.
C.Use Spark Structured Streaming with a batch read of the SCD table in each micro-batch using spark.read.
D.Use Spark Structured Streaming with a streaming join on the SCD table by converting it to a stream using readStream.
AnswerB

Periodic refresh of a static DataFrame minimizes overhead and ensures latest data.

Why this answer

It uses a static DataFrame for the SCD table and refreshes it periodically using a trigger (e.g., a time-based or file-based trigger). This approach ensures that the join always uses the latest version of the SCD data without reprocessing the entire stream, as the static DataFrame is re-read only when the SCD is updated. It minimizes latency and cost by avoiding the overhead of reading the SCD in every micro-batch (as in Option A) or using a streaming join (as in Option D), which is not suitable for batch-updated SCD tables.

Exam trap

The trap here is that candidates often assume that reading the SCD table in every micro-batch (Option A or C) is the simplest way to get the latest data, but they overlook the significant performance and cost penalties of full table scans in each micro-batch, especially for large SCD tables.

How to eliminate wrong answers

Option A is wrong because foreachBatch with a read of the SCD table inside the function would read the SCD in every micro-batch, causing high latency and cost due to repeated full scans of the Delta table, and it does not efficiently handle the periodic updates of the SCD. Option C is wrong because reading the SCD table in each micro-batch using spark.read would also perform a full scan of the Delta table every micro-batch, leading to excessive I/O and cost, and it does not leverage the periodic refresh pattern. Option D is wrong because converting the SCD table to a stream using readStream is inappropriate for a slowly changing dimension that is updated every few hours; streaming the SCD would require it to be a continuous stream of changes (e.g., from a change data capture feed), which is not the case here, and it would add unnecessary complexity and cost.

Full explanation →
171
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

Immutability policies enforce WORM (Write Once, Read Many) for a specified duration.

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 →
172
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 in Synapse Pipelines provide serverless, code-free transformation with minimal management.

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 →
173
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 provides a scalable and fault-tolerant way to process streaming data from Event Hubs. Using a streaming static join efficiently enriches the stream with infrequently updated reference data from Cosmos DB without the complexity of change data capture. Writing to Delta Lake enables schema evolution natively, and the solution minimizes cost by leveraging existing Databricks infrastructure.

Option A is incorrect because Auto Loader is for batch file ingestion, not streaming from Event Hubs, and Change Data Capture adds unnecessary complexity. Option B is incorrect because using batch processing (Azure Data Factory copy) increases latency and complexity compared to a streaming approach. Option C is incorrect because Azure Stream Analytics would introduce additional service cost and lacks the flexibility of Databricks for schema evolution and advanced transformations.

Full explanation →
174
MCQhard

You are a data engineer for a large e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool as its enterprise data warehouse. A new business requirement mandates that the Sales fact table, which contains 2 billion rows, must support real-time analytics with a maximum query latency of 1 second for aggregations on the most recent 24 hours of data. The table is currently hash-distributed on CustomerID and partitioned monthly by SaleDate. The current query performance for recent data is slow due to full partition scans. The data is ingested via Azure Event Hubs and processed by Azure Stream Analytics, which writes to staging tables every minute. You need to redesign the storage to meet the latency requirement while minimizing cost and maintaining data integrity. Which approach should you take?

A.Increase partition granularity to hourly partitions for the Sales table.
B.Change the Sales table to use a clustered index on SaleDate and a round-robin distribution.
C.Create a separate staging table for the last 24 hours of data with round-robin distribution and a clustered columnstore index. After each batch load, merge the staging table into the main partitioned Sales table. Queries for recent data should target the staging table.
D.Provision a second dedicated SQL pool optimized for real-time queries and replicate the recent data there.
AnswerC

The staging table is small, enabling fast queries for recent data; merging maintains the historical archive.

Why this answer

It isolates the hot (recent 24-hour) data into a separate staging table with a clustered columnstore index, which is optimized for fast aggregations and high compression. This avoids full partition scans on the 2-billion-row main table, and merging into the partitioned table after each batch load maintains data integrity without requiring expensive repartitioning or additional dedicated SQL pools.

Exam trap

The trap here is that candidates assume finer partitioning (Option A) always improves query performance, but they overlook that partition elimination still requires scanning the entire partition, and that a separate hot table with columnstore indexing is more efficient for real-time aggregations on a sliding window of recent data.

How to eliminate wrong answers

Option A is wrong because increasing partition granularity to hourly partitions would increase metadata overhead and partition management complexity, and full partition scans would still occur on the most recent partition, failing to meet the 1-second latency requirement. Option B is wrong because changing to a clustered index on SaleDate with round-robin distribution eliminates data locality for joins on CustomerID and introduces full data movement for aggregations, while round-robin distribution is unsuitable for large fact tables due to lack of partition elimination. Option D is wrong because provisioning a second dedicated SQL pool duplicates storage and compute costs unnecessarily, and replicating data introduces latency and complexity in maintaining consistency, violating the cost-minimization requirement.

Full explanation →
175
MCQhard

A company uses Azure Data Factory to copy sensitive data from on-premises SQL Server to Azure Blob Storage. They must ensure that data is encrypted in transit and at rest. Which combination of features should they use?

A.Use Always Encrypted in SQL Server and customer-managed keys in Blob Storage.
B.Set up a VPN between on-premises and Azure, and use Azure Disk Encryption.
C.Configure the copy activity to use TLS and enable Azure Storage Service Encryption.
D.Use HTTPS for the copy activity and enable Azure Storage Service Encryption.
AnswerC

TLS encrypts data in transit; Storage Service Encryption encrypts at rest automatically.

Why this answer

Azure Data Factory's copy activity uses TLS (Transport Layer Security) to encrypt data in transit between the on-premises SQL Server and Azure Blob Storage, and Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption. This combination satisfies both encryption requirements without additional complexity.

Exam trap

The trap here is that candidates often confuse HTTPS with TLS, thinking HTTPS is the encryption mechanism for Data Factory copy activities, when in fact TLS is the underlying protocol used by the self-hosted integration runtime for secure data transfer.

How to eliminate wrong answers

Option A is wrong because Always Encrypted in SQL Server encrypts data at the column level within the database, but it does not encrypt data in transit during the copy operation; customer-managed keys in Blob Storage are for at-rest encryption but do not address transit encryption. Option B is wrong because a VPN encrypts the network tunnel between on-premises and Azure, but it does not encrypt data at rest in Blob Storage; Azure Disk Encryption is for IaaS VMs, not PaaS Blob Storage. Option D is wrong because HTTPS is a protocol that encrypts data in transit, but it is not the default or recommended encryption method for Data Factory copy activities; TLS is the standard, and while SSE handles at-rest encryption, the option incorrectly specifies HTTPS instead of TLS.

Full explanation →
176
MCQhard

You are a data engineer for a healthcare company. You have a production Azure Synapse Analytics dedicated SQL pool (DW500c) that hosts patient data. The pool is used for both ETL and reporting. You need to ensure that reporting queries always get resources even during heavy ETL loads. You also need to monitor query performance and set up alerts when certain queries exceed a threshold. You have configured workload management using workload groups and classifiers. However, reporting queries are still waiting for resources when ETL is running. You check the sys.dm_pdw_exec_requests DMV and see that ETL queries are using the largest resource class. You need to modify the configuration to guarantee resources for reporting. What should you do?

A.Create a new workload group for reporting with min_percentage_resource set to 30%
B.Set importance to HIGH for the reporting workload group
C.Increase the DWU setting to DW1000c
D.Change the classifier for reporting queries to use the same workload group as ETL but with a different resource class
AnswerA

Guarantees a minimum resource allocation for reporting.

Why this answer

Creating a separate workload group for reporting with a minimum percentage of resources (min_percentage_resource) guarantees a baseline amount of resources for reporting queries, isolating them from ETL even when ETL is using a large resource class. This is the correct solution. Option B (importance) can help order queries within the same group but does not guarantee resource availability if the group has no minimum.

Option C (increasing DWU) adds more overall resources but does not isolate; reporting may still be starved if ETL uses them. Option D (changing classifier) does not guarantee resources because both queries would compete in the same group.

Full explanation →
177
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

This configures server-side encryption with CMK.

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 →
178
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

This enables dynamic filtering of new records.

Why this answer

The current query uses a static date. For incremental loads, a watermark column like OrderDate with a last run timestamp is needed, typically using a lookup activity or variable.

Full explanation →
179
MCQeasy

You are analyzing the exhibit from an Azure Monitor metric query for a storage account. What is the primary purpose of this query?

A.To calculate the average number of block blobs in the hot tier.
B.To identify the time period with the highest blob count.
C.To measure the total size of all block blobs in the account.
D.To retrieve the average count of block blobs per hour.
AnswerD

Metric BlobCount with aggregation Average and filter on BlobType equals BlockBlob.

Why this answer

The query uses the 'avg' aggregation on the 'BlobCount' metric, which calculates the average number of blobs over the specified time granularity (e.g., per hour). The result shows the average count of block blobs per hour, not the total count or the count in a specific tier. This aligns with option D, as the query is designed to retrieve the average count of block blobs per hour.

Exam trap

The trap here is that candidates often confuse 'avg' with 'sum' or 'max', leading them to incorrectly think the query calculates total blob count or identifies peak periods, rather than recognizing that 'avg' specifically computes the average value over the time granularity.

How to eliminate wrong answers

Option A is wrong because the query does not filter by blob tier (hot, cool, or archive); it retrieves the average count of all block blobs, not just those in the hot tier. Option B is wrong because the query uses the 'avg' aggregation, which returns an average value over the time period, not the maximum or peak blob count; to identify the time period with the highest blob count, you would need to use the 'max' aggregation. Option C is wrong because the query measures 'BlobCount', which is the number of blobs, not their size; to measure total size, you would use the 'BlobCapacity' metric.

Full explanation →
180
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

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 →
181
MCQmedium

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a folder structure that separates data by sensitivity level. Which access control method should you use?

A.Use a storage account firewall and virtual network service endpoints
B.Use storage account keys for all access
C.Use Azure RBAC at the resource group level and ACLs on directories
D.Use shared access signatures (SAS) for each folder
AnswerC

C is correct because RBAC provides coarse control and ACLs provide fine-grained folder-level permissions.

Why this answer

Azure Data Lake Storage Gen2 supports both Azure RBAC at the resource group level for coarse-grained control and POSIX-like ACLs on directories for fine-grained, sensitivity-based access. This combination allows you to assign read/write/execute permissions per directory, enabling a folder structure that separates data by sensitivity level without compromising security.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall, VNet) or shared access signatures with the directory-level ACLs required for sensitivity-based folder separation, overlooking that only ACLs provide the POSIX-style granularity needed for hierarchical data lakes.

How to eliminate wrong answers

Option A is wrong because a storage account firewall and virtual network service endpoints control network-level access to the entire storage account, not granular folder-level permissions by sensitivity. Option B is wrong because storage account keys provide full administrative access to the entire account, bypassing any folder-level sensitivity controls and violating the principle of least privilege. Option D is wrong because shared access signatures (SAS) grant time-limited, delegated access to specific containers or blobs, but they cannot enforce POSIX ACLs on directories and are not designed for persistent, sensitivity-based folder structures.

Full explanation →
182
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

ADX is built for high-speed ingestion of streaming data, supports JSON, and provides sub-second query performance for dashboards.

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 →
183
MCQeasy

A retail company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The data is loaded nightly from Azure Data Lake Storage Gen2 using PolyBase. Recently, the load process started failing with the error 'External table 'sales' is not accessible because the location does not exist or is used by another process.' You verify that the storage account, container, and file path are correct. The file is a CSV file named 'sales_20250301.csv' and it exists. Other files in the same container load successfully. What is the most likely cause of the error?

A.The network connectivity between Synapse and the storage account is intermittent.
B.The CSV file has an incorrect number of columns or contains a header row that mismatches the schema.
C.The storage account key used in the external data source has expired.
D.The file is being written to or is locked by another process during the PolyBase read.
AnswerD

The error 'used by another process' indicates a file lock, typically because the file is still being written or another reader has an exclusive lock.

Why this answer

The error 'location does not exist or is used by another process' specifically indicates that the file is locked by another process. In Azure Data Lake Storage Gen2, when a file is being written or modified, it can be locked by the writing process (e.g., a data ingestion pipeline or another ETL job). PolyBase attempts to read the file while it is still being written, causing the error.

Option D correctly identifies this concurrency issue.

Exam trap

The trap here is that candidates often confuse file-locking errors with authentication or schema issues, but the specific wording 'used by another process' directly points to a concurrency/lock conflict rather than connectivity or data format problems.

How to eliminate wrong answers

Option A is wrong because intermittent network connectivity would typically cause timeout or connection reset errors, not a 'location does not exist or is used by another process' error. Option B is wrong because schema mismatches (incorrect column count or header row) would produce data conversion or parsing errors, not a location accessibility error. Option C is wrong because an expired storage account key would result in an authentication failure (e.g., 403 Forbidden), not a location-not-found or file-locked error.

Full explanation →
184
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

RBAC roles grant coarse permissions (e.g., Storage Blob Data Contributor) while ACLs provide fine-grained permissions on directories and files, enabling least privilege.

Why this answer

Azure RBAC combined with POSIX-like ACLs allows fine-grained permissions at the directory and file level, supporting the principle of least privilege without modifying firewall settings. Option D is incorrect because shared access signatures (SAS) grant time-limited access but are not fine-grained at the directory/file level and can be complex to manage. Option B is incorrect because managed identities provide identity-based access but still require RBAC or ACLs for fine-grained control.

Option C is incorrect because storage account firewall rules apply at the account level, not at the directory or file level.

Full explanation →
185
MCQmedium

You are designing a change data capture (CDC) pipeline to ingest incremental changes from an on-premises SQL Server database into Azure Data Lake Storage Gen2. The pipeline must run every 5 minutes and handle high-volume DML changes. Which Azure service should you use to capture the changes with low latency?

A.Azure Data Share to share the SQL Server data and capture changes.
B.Azure Data Factory with a change data capture (CDC) source in the mapping data flow.
C.Azure Synapse Pipelines with a copy activity that uses a query to capture changes.
D.Azure Databricks with Auto Loader and Delta Live Tables to capture changes.
AnswerB

ADF supports CDC from SQL Server with low latency via mapping data flows.

Why this answer

Azure Data Factory's mapping data flow includes a native CDC source that can connect to SQL Server and capture incremental DML changes (inserts, updates, deletes) with low latency. This approach uses change tracking or change data capture features in SQL Server to identify changes, and the pipeline can run every 5 minutes to meet the high-volume requirement without custom coding.

Exam trap

The trap here is that candidates often confuse Azure Data Factory's copy activity (which requires manual change tracking) with the mapping data flow's native CDC source, leading them to choose option C or D, which are better suited for different ingestion patterns.

How to eliminate wrong answers

Option A is wrong because Azure Data Share is designed for sharing snapshots or incremental data between organizations, not for capturing low-latency CDC from an on-premises SQL Server into Azure Data Lake Storage Gen2. Option C is wrong because Azure Synapse Pipelines' copy activity with a query-based approach typically requires manual watermarking and cannot natively capture DML changes with low latency; it lacks the built-in CDC source that mapping data flow provides. Option D is wrong because Azure Databricks with Auto Loader and Delta Live Tables is optimized for streaming file ingestion (e.g., from cloud storage), not for directly capturing CDC from an on-premises SQL Server database.

Full explanation →
186
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 sub-second latency stream processing and native output to Azure Data Lake Storage Gen2.

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 →
187
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

Columnar format reduces I/O and improves compression.

Why this answer

Columnar formats like Parquet store data by column rather than by row, which significantly reduces I/O when querying only a subset of columns (common in time-range queries). This compression and column pruning directly lowers storage costs and speeds up scan-heavy analytical queries on append-only IoT telemetry data.

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 →
188
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

Simplifies staging and leverages PolyBase for efficient loading.

Why this answer

It uses Azure Data Factory to stage the JSON data from Event Hubs into Azure Data Lake Storage Gen2 (ADLS Gen2) as JSON files, and then uses a PolyBase activity to load the data from ADLS Gen2 into the dedicated SQL pool. This approach meets the requirement of using PolyBase for loading, handles the 100 MB every 5 minutes with minimal latency, and leverages the efficient bulk loading capability of PolyBase. Option A is incorrect because Azure Stream Analytics does not support output directly to a dedicated SQL pool; it can output to Azure SQL Database or other sinks, but not dedicated SQL pool, and it does not use PolyBase.

Option C is incorrect because using Azure Databricks with JDBC does not utilize PolyBase and would require a continuously running cluster, increasing cost and complexity. Option D is incorrect because PolyBase cannot create an external data source pointing directly to Event Hubs; PolyBase external data sources only support Azure Blob Storage, ADLS Gen2, and Hadoop, not Event Hubs.

Full explanation →
189
MCQeasy

You are configuring Azure Synapse Analytics dedicated SQL pool. To optimize query performance for a large fact table that is frequently filtered on date and region columns, which table distribution and indexing strategy should you recommend?

A.Hash distribution on date column with clustered index.
B.Replicated distribution with heap index.
C.Round-robin distribution with clustered index.
D.Hash distribution on region column with clustered columnstore index.
AnswerD

Hash distribution on a high-cardinality column improves joins and filtering; columnstore is efficient for large tables.

Why this answer

The best recommendation because hash distribution on the region column evenly distributes the data across distributions (assuming high cardinality), and clustering with a columnstore index provides excellent compression and query performance for large fact tables, especially when filtered on non-distribution columns like date. Option A (hash on date) risks data skew if date cardinality is low. Option B (replicated) is designed for small dimension tables, not large fact tables.

Option C (round-robin) distributes rows randomly, preventing partition elimination and reducing filter performance.

Full explanation →
190
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

Many small files cause overhead; PolyBase is optimized for fewer, larger files.

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 →
191
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

Joins on non-distribution keys require data movement.

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 →
192
Multi-Selecthard

You are optimizing an Azure Synapse Analytics dedicated SQL pool that is experiencing high concurrency and frequent resource class contention. You need to improve query performance and reduce contention without changing the workload. Which two actions should you take? (Choose two.)

Select 2 answers
A.Increase the DWU (Data Warehouse Units) to allocate more resources.
B.Implement workload isolation to separate critical queries into dedicated resource groups.
C.Create materialized views to pre-aggregate data.
D.Use workload classification to assign importance and resource allocation to different queries.
E.Enable result set caching to reduce repeated query execution.
AnswersB, D

Workload isolation creates dedicated resource groups for critical queries, ensuring they have reserved resources and reducing contention with other workloads.

Why this answer

Options B and D are correct. Workload isolation and workload classification help manage resource allocation and reduce contention. Option A is wrong because increasing DWU may help but does not address contention directly.

Option C is wrong because materialized views improve performance but do not reduce contention. Option E is wrong because result set caching helps read workloads but not contention.

Full explanation →
193
MCQmedium

You are configuring security for an Azure Synapse Analytics workspace that uses a serverless SQL pool. The workspace is connected to Azure Data Lake Storage Gen2 via a managed identity. You need to ensure that only the Synapse workspace can access the storage account, and no other Azure service or user can access it directly. The storage account should not be accessible from the public internet. What should you do?

A.Configure the storage account firewall to allow only the Synapse workspace's public IP address.
B.Enable 'Allow trusted Microsoft services to access this storage account' on the firewall.
C.Use Azure RBAC to assign the Storage Blob Data Contributor role to the Synapse workspace managed identity.
D.Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access.
AnswerD

Private endpoint ensures private connectivity; disabling public access restricts others.

Why this answer

Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access. This ensures that only the Synapse workspace, which is connected via the private endpoint, can access the storage account. The managed identity is used for authentication, but the private endpoint restricts network access.

Option A is incorrect because public IP addresses can change and do not provide secure, private connectivity. Option B is incorrect because allowing trusted Microsoft services would permit other Azure services to access the storage account, not just the Synapse workspace. Option C is incorrect because RBAC alone does not restrict network access; the storage account would still be publicly accessible.

Full explanation →
194
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

Allows columns to be added without breaking the pipeline.

Why this answer

Enabling 'Allow schema drift' in the source transformation tells Azure Data Factory (ADF) mapping data flows to dynamically accept incoming columns that are not defined in the schema at design time. This is the primary built-in mechanism for handling schema drift without manual intervention, as it automatically propagates new columns through the data flow.

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 →
195
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

Mapping Data Flow allows you to handle NULLs by providing a default value, ensuring data integrity.

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 →
196
MCQhard

A company is running a Spark job on Azure Databricks that processes 500 GB of data daily. The job frequently fails with 'OutOfMemoryError' during shuffles. The cluster uses 10 workers of type Standard_DS3_v2 (14 GB memory each). Which configuration change should you make to improve stability without over-provisioning?

A.Set spark.sql.shuffle.partitions to a higher value, e.g., 500.
B.Increase the driver memory to 28 GB.
C.Increase the number of workers to 20.
D.Reduce spark.sql.shuffle.partitions to 100.
AnswerA

Reduces data per partition, easing memory.

Why this answer

The 'OutOfMemoryError' during shuffles indicates that individual partitions are too large for the executor memory. Increasing `spark.sql.shuffle.partitions` to 500 reduces the amount of data per partition, lowering memory pressure during shuffle operations. This directly addresses the error without adding more hardware.

Exam trap

The trap here is that candidates often assume adding more workers (Option C) is the only way to fix memory errors, but the question tests understanding that partition size, not just cluster size, is the root cause of shuffle OOM errors.

How to eliminate wrong answers

Option B is wrong because increasing driver memory does not help with executor-side shuffle memory issues; the driver is not involved in shuffle data processing. Option C is wrong because adding more workers increases parallelism but does not reduce the size of each partition unless the number of partitions is also increased; it would over-provision resources without fixing the root cause. Option D is wrong because reducing `spark.sql.shuffle.partitions` to 100 would make each partition larger, worsening the memory pressure and increasing the likelihood of OutOfMemoryError.

Full explanation →
197
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 management allows you to assign appropriate resources to queries based on their priority, reducing conflicts.

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 →
198
Multi-Selecthard

You are designing a data processing solution in Azure Synapse Analytics. You need to load data from multiple sources into a dedicated SQL pool. Which THREE of the following are best practices for loading data?

Select 3 answers
A.Use round-robin distribution for staging tables
B.Use a staging table to load data and then insert into the final table
C.Split data into many small files to increase parallelism
D.Use PolyBase or COPY statement for parallel loading
E.Enable clustered columnstore index during load to improve query performance
AnswersA, B, D

Round-robin distributes data evenly for fast load.

Why this answer

Round-robin distribution is recommended for staging tables because it distributes data evenly across all distributions without requiring a hash key, minimizing data movement during the load process. Since staging tables are temporary and used for intermediate storage, the even distribution ensures that the subsequent INSERT...SELECT operation into the final table benefits from maximum parallelism and reduced skew.

Exam trap

The trap here is that candidates often assume more files always increase parallelism, but Azure Synapse Analytics optimizes for fewer, larger files to minimize metadata operations and maximize throughput.

Full explanation →
199
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

Retry with backoff handles transient connection failures.

Why this answer

Adding a retry policy with a backoff interval handles transient connection issues. Option D is wrong because increasing parallel copies does not solve connection refused errors. Option B is wrong because Azure ExpressRoute is costly and unnecessary for FTP.

Option C is wrong because migrating to SFTP may not resolve connection refused if the server is overloaded.

Full explanation →
200
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 by date enables partition pruning, reducing data scanned and improving query performance.

Why this answer

Partitioning Parquet files by a commonly filtered column, such as date, allows Azure Synapse serverless SQL pool to perform partition pruning, which eliminates scanning unnecessary partitions and reduces the amount of data read. Option A is incorrect because increasing MAXDOP (maximum degree of parallelism) can lead to resource contention and may not improve query performance in serverless SQL pool. Option B is incorrect because Parquet is a columnar format optimized for analytics and is more efficient than CSV for querying large datasets.

Option C is incorrect because materialized views are not supported in serverless SQL pool; they are only available in dedicated SQL pool.

Full explanation →
201
Multi-Selectmedium

Which TWO actions should you take to ensure that data at rest is encrypted in Azure Synapse Analytics dedicated SQL pool?

Select 2 answers
A.Enable Always Encrypted with secure enclaves.
B.Enable infrastructure-level encryption using double encryption.
C.Apply column-level encryption using ENCRYPTBYPASSPHRASE.
D.Create a customer-managed key in Azure Key Vault.
E.Enable Transparent Data Encryption (TDE) using service-managed keys.
AnswersB, E

Azure Storage double encryption provides additional layer at rest.

Why this answer

The correct actions are B and E. Transparent Data Encryption (TDE) with service-managed keys (Option E) encrypts the entire dedicated SQL pool at rest by default. Infrastructure-level encryption using double encryption (Option B) adds a second layer of encryption at the infrastructure level, providing extra protection.

Option A (Always Encrypted with secure enclaves) is for client-side encryption during queries, not at rest. Option C (column-level encryption with ENCRYPTBYPASSPHRASE) encrypts specific columns, not the entire pool. Option D (creating a customer-managed key in Azure Key Vault) is a prerequisite for TDE with customer-managed keys, but the action to enable encryption itself is TDE; Option E with service-managed keys already achieves at-rest encryption without requiring a customer-managed key.

Full explanation →
202
MCQhard

Refer to the exhibit. You have an Azure Synapse pipeline that runs a Spark notebook daily. The notebook uses the inputDate parameter to filter data. The notebook successfully processes data for '2024-01-01' but fails for '2024-01-02' with an error that the 'sales' table does not exist. The 'sales' table is created daily by a preceding job. What is the most likely cause?

A.The notebook expects a table named 'sales_20240102' but the preceding job creates 'sales_20240101'
B.The notebook activity should have a dependency on the job that creates the table
C.The Spark pool does not have permissions to read the storage account where the table data is stored
D.The pipeline parameter 'inputDate' is not being passed to the notebook correctly
AnswerA

The notebook likely constructs table name from the date parameter, and the table for the new date hasn't been created.

Why this answer

The error indicates that the notebook is looking for a table named 'sales_20240102' (based on the inputDate parameter for '2024-01-02'), but the preceding job creates a table named 'sales_20240101' (the previous day's table). This mismatch occurs because the notebook dynamically constructs the table name using the inputDate parameter, and the preceding job likely creates the table with a date suffix that does not align with the current inputDate. The correct answer is A because the table naming convention is inconsistent between the two processes.

Exam trap

The trap here is that candidates may assume the error is due to missing dependencies or permissions, but the real issue is a logical mismatch in table naming conventions between the table creation job and the notebook's expected table name.

How to eliminate wrong answers

Option B is wrong because adding a dependency on the job that creates the table would only ensure the job runs before the notebook, but it would not fix the naming mismatch between the table created and the table expected. Option C is wrong because the error message explicitly states the 'sales' table does not exist, not a permission issue; a permissions error would typically manifest as an 'Access Denied' or 'AuthorizationFailure' exception. Option D is wrong because if the inputDate parameter were not passed correctly, the notebook would likely fail for all dates or use a default value, not fail specifically for '2024-01-02' while succeeding for '2024-01-01'.

Full explanation →
203
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

Cosmos DB natively supports JSON documents with automatic indexing and global distribution.

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 →
204
Drag & Dropmedium

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

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

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 →
205
MCQmedium

You are developing a data processing pipeline in Azure Synapse Analytics that uses a SQL script to transform data in a dedicated SQL pool. The pipeline currently runs in the built-in serverless pool, but you want to migrate it to a dedicated SQL pool for better performance. What must you change in the pipeline?

A.Change the linked service from serverless to dedicated SQL pool.
B.Replace the SQL script with a Mapping Data Flow activity.
C.Update the integration runtime to a self-hosted IR.
D.Modify the SQL script to use T-SQL compatible with dedicated SQL pool.
AnswerD

Dedicated SQL pool has a different T-SQL surface area; some functions may need to be rewritten.

Why this answer

Dedicated SQL pool uses T-SQL that has specific syntax differences from the serverless SQL pool used in Azure Synapse Analytics. When migrating from serverless to dedicated, you need to modify your SQL script to be compatible with dedicated SQL pool's T-SQL (e.g., different supported functions, data types, and performance features). Option A (changing the linked service) might also be necessary as part of the migration but is not the primary change needed to ensure the script runs correctly.

Option B (replacing with Mapping Data Flow) is not required; the script itself can still be used. Option C (self-hosted IR) is not relevant for SQL script activities in Synapse pipelines.

Full explanation →
206
MCQhard

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query data in Azure Data Lake Storage Gen2. The data is stored in Delta Lake format. Which of the following statements is true regarding querying Delta Lake tables with serverless SQL pool?

A.Serverless SQL pool supports Delta Lake only if the files are in Parquet format.
B.You can use the OPENROWSET function with the BULK option and FORMAT='DELTA' to query Delta Lake tables.
C.Serverless SQL pool cannot query Delta Lake tables; you must convert them to Parquet first.
D.You must create an external table in serverless SQL pool using the CREATE EXTERNAL TABLE statement with the Delta format.
AnswerB

OPENROWSET with FORMAT='DELTA' allows querying Delta Lake tables directly.

Why this answer

Serverless SQL pool in Azure Synapse Analytics supports querying Delta Lake tables directly using the OPENROWSET function with the BULK option and FORMAT='DELTA'. This allows you to read Delta Lake data stored in Azure Data Lake Storage Gen2 without needing to convert it to Parquet or create external tables first. The Delta format is natively supported, enabling time travel and schema evolution features.

Exam trap

The trap here is that candidates assume Delta Lake requires special handling or conversion to Parquet, but serverless SQL pool natively supports Delta via OPENROWSET with FORMAT='DELTA', making options A and C incorrect, while option D misleads by suggesting external tables are the primary method.

How to eliminate wrong answers

Option A is wrong because serverless SQL pool supports Delta Lake natively, not only when files are in Parquet format; Delta Lake itself uses Parquet as the underlying storage format but adds transaction logs and metadata. Option C is wrong because serverless SQL pool can query Delta Lake tables directly using OPENROWSET with FORMAT='DELTA', so conversion to Parquet is unnecessary. Option D is wrong because while you can create external tables for Delta Lake, the correct and simplest method is to use OPENROWSET with FORMAT='DELTA', not a CREATE EXTERNAL TABLE statement with Delta format (which is not supported for external tables in serverless SQL pool).

Full explanation →
207
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 →
208
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 gives low-latency reads; ADLS Gen2 supports analytics.

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 →
209
Multi-Selecthard

Which THREE considerations are important when designing a data processing solution using Azure Databricks for ETL workloads? (Select three.)

Select 3 answers
A.Auto-scaling of compute resources
B.Use of Delta Lake for data reliability
C.Fixed pricing model
D.Minimum network bandwidth
E.Cluster configuration and autoscaling
AnswersA, B, E

Auto-scaling helps handle variable workloads efficiently.

Why this answer

Auto-scaling of compute resources ensures that the cluster can adjust to workload demands, optimizing cost and performance. Option B is correct because Delta Lake provides ACID transactions, schema enforcement, and optimization features that enhance data reliability in ETL pipelines. Option E is correct because proper cluster configuration and autoscaling are essential for managing resources efficiently.

Option C is incorrect because Azure Databricks uses a pay-as-you-go model, not a fixed pricing model, making it more flexible. Option D is incorrect because minimum network bandwidth is not a typical design consideration for Databricks ETL workloads, as network performance is generally sufficient.

Full explanation →
210
MCQhard

You are designing a data lake on Azure Data Lake Storage Gen2. The data will be used by both batch processing (Spark) and interactive querying (Azure Synapse Serverless SQL). The data is partitioned by date and stored as Parquet. What is the optimal folder structure to minimize cross-partition scans for both workloads?

A.All files in a single folder
B./year/month/day/ (e.g., /2023/12/25/)
C./yyyy-mm-dd/ (e.g., /2023-12-25/)
D.Files named by date (e.g., data_20231225.parquet)
AnswerB

Why this answer

(/year/month/day/) is optimal because it aligns with Hive-style partitioning, which both Spark and Azure Synapse Serverless SQL can leverage for partition pruning. Spark uses partition discovery to read only relevant directories, and Synapse Serverless SQL uses the file path metadata to filter partitions, minimizing cross-partition scans and reducing data read overhead.

Exam trap

The trap here is that candidates often assume a flat date-based folder or filename pattern is sufficient for partitioning, but both Spark and Synapse Serverless SQL require hierarchical folder structures to enable automatic partition pruning and avoid full scans.

Why the other options are wrong

A

No partitioning at all, causing full scans.

C

Single-level partitioning does not allow efficient pruning for yearly or monthly queries.

D

Partition pruning requires folder hierarchy, not file names.

Full explanation →
211
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 exactly-once semantics and handles late-arriving data.

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 →
212
Multi-Selecthard

Which THREE factors should be considered when choosing between Azure Stream Analytics and Azure Databricks for a real-time data processing solution?

Select 3 answers
A.Integration with Power BI for real-time dashboards
B.Need for complex transformations and machine learning model integration
C.Volume of data per second (throughput)
D.Requirement for exactly-once semantics
E.Maximum allowed latency for late-arriving data
AnswersB, C, D

Databricks supports complex ML pipelines natively.

Why this answer

Azure Databricks provides native support for complex transformations (e.g., windowed aggregations, multi-step ETL) and seamless integration with machine learning libraries (e.g., MLflow, Spark MLlib), which are not natively available in Azure Stream Analytics. Stream Analytics uses a SQL-like query language and is optimized for simpler, declarative transformations, making Databricks the better choice when advanced analytics or ML model scoring is required in real-time pipelines.

Exam trap

The trap here is that candidates often assume Power BI integration or late-arriving data handling are unique to one service, when in fact both services support these features, and the key differentiators are throughput scalability, exactly-once semantics, and the ability to perform complex transformations with ML integration.

Full explanation →
213
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

The LOCATION must match exactly. If the files are not directly under 'sales/2024/03/', they won't be read.

Why this answer

The LOCATION in the external table is 'sales/2024/03/' which is a subfolder. The files must be directly in that folder. If the files are in a different folder structure (e.g., with partition folders like year=2024/month=03), the external table will not find them.

Also, check that the credential has access.

Full explanation →
214
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

ADLS Gen2 is designed for big data analytics, supports hierarchical namespace, and is cost-effective for long-term storage.

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 →
215
MCQmedium

When designing a data processing solution using Azure Databricks, what is the recommended approach to handle schema evolution when reading data from Delta Lake tables?

A.Set the option 'mergeSchema' to 'true' on write
B.Set the option 'overwriteSchema' to 'true' on write
C.Manually alter the table schema using ALTER TABLE
D.Ignore schema changes and use 'failOnDataLoss' flag
AnswerA

Why this answer

In Delta Lake, schema evolution is automatically handled by setting the 'mergeSchema' option to 'true' on write operations. This allows new columns to be added or existing column types to be safely widened without manual intervention, preserving existing data and metadata integrity.

Exam trap

The trap here is that candidates often confuse 'mergeSchema' with 'overwriteSchema', mistakenly thinking both handle schema evolution similarly, but 'overwriteSchema' replaces the entire schema and can cause data loss, while 'mergeSchema' safely merges new columns or type changes.

Why the other options are wrong

B

overwriteSchema replaces the entire schema and can cause data loss.

C

Manual approach is error-prone and not recommended for automated pipelines.

D

failOnDataLoss is for streaming jobs, not for schema evolution.

Full explanation →
216
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

Delta table provides a reliable way to track processed files and can be updated incrementally.

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 →
217
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

Correct: Sensitivity labels help enforce protection policies and are used in monitoring.

Why this answer

The correct answers are C, D, and E. Creating custom sensitivity labels in Microsoft Purview Information Protection (option C) allows data to be tagged with sensitivity levels. Integrating Azure Purview with Microsoft Defender for Cloud Apps (option D) provides monitoring of access to sensitive data.

Automated scanning in Azure Purview (option E) discovers and classifies data automatically. Option A is incorrect because Azure Policy is used for governance and compliance enforcement, not for data classification or monitoring. Option B is incorrect because Azure Sentinel is a security information and event management (SIEM) solution, not a data classification tool.

Full explanation →
218
MCQeasy

You are designing a batch processing pipeline that reads CSV files from Azure Blob Storage, performs aggregations using Azure Databricks, and writes results to Azure Synapse Analytics. The pipeline must handle schema drift (new columns appearing in source files). Which approach should you recommend?

A.Use Azure Data Factory mapping data flows with schema drift enabled, mapping to a fixed sink schema.
B.Define a fixed schema in the source and ignore any new columns.
C.Use Spark with mergeSchema option when reading, and write using a Delta table to evolve schema automatically.
D.Use Azure Stream Analytics to pre-process and enforce schema.
AnswerC

Handles schema drift automatically.

Why this answer

Spark's `mergeSchema` option, when used with Delta Lake, automatically evolves the schema to accommodate new columns in CSV files. This allows the batch pipeline to handle schema drift without manual intervention, and writing to a Delta table ensures the schema evolution is persisted and compatible with downstream writes to Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse schema drift handling with schema enforcement, assuming that a fixed sink schema or streaming pre-processing can accommodate dynamic schema changes, when in fact only a schema-on-read approach like Spark's `mergeSchema` with Delta Lake provides the necessary flexibility for batch pipelines.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory mapping data flows with schema drift enabled can handle new columns, but mapping to a fixed sink schema would discard or fail on those new columns, defeating the purpose of handling drift. Option B is wrong because defining a fixed schema and ignoring new columns directly contradicts the requirement to handle schema drift, leading to data loss or pipeline failures. Option D is wrong because Azure Stream Analytics is designed for real-time streaming data, not batch processing, and it enforces a fixed schema rather than evolving it dynamically.

Full explanation →
219
MCQeasy

You are designing a data pipeline in Azure Data Factory that processes streaming data from Azure Event Hubs and stores it in Azure Data Lake Storage Gen2. The data must be encrypted at rest and in transit. Which configuration ensures encryption in transit?

A.Enable encryption at rest using Azure Storage Service Encryption.
B.Use HTTPS endpoint for Azure Data Lake Storage Gen2.
C.Configure the Azure Data Factory integration runtime to use TLS 1.2.
D.Deploy Azure Firewall to inspect traffic between Event Hubs and Data Lake.
AnswerB

HTTPS (HTTP over TLS) encrypts data in transit between clients and Azure Data Lake Storage Gen2.

Why this answer

HTTPS ensures encryption in transit for Azure Data Lake Storage Gen2. Option A is wrong because encryption at rest does not cover data in transit. Option C is wrong because while TLS 1.2 is important, the integration runtime configuration does not directly apply to the storage endpoint; the storage account itself must use HTTPS.

Option D is wrong because Azure Firewall does not provide encryption; it filters traffic.

Full explanation →
220
MCQeasy

You need to monitor the performance of an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a self-hosted integration runtime. Which metric is most important to monitor to ensure the self-hosted IR is not a bottleneck?

A.Pipeline duration metric
B.Queue depth for the self-hosted IR
C.Number of active connections to the IR
D.Data read and data written metrics for the pipeline
AnswerB

High queue depth indicates the IR is unable to process activities quickly enough.

Why this answer

Queue depth for the self-hosted IR is the most important metric to monitor because it indicates how many activities are queued waiting for the IR to process. A high queue depth suggests the IR is overloaded and becoming a bottleneck. In contrast, pipeline duration (A) reflects total time but does not isolate IR performance.

Active connections (C) measure concurrent loads but not queuing. Data read/written (D) track throughput, but a bottleneck may appear even with high throughput if the IR cannot keep up.

Full explanation →
221
MCQeasy

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

A.Azure Disk Encryption
B.Azure Storage Firewall
C.Azure Storage Service Encryption (SSE) with customer-managed keys
D.Azure Information Protection
AnswerC

Correct. SSE encrypts data at rest and can use CMK from Key Vault.

Why this answer

Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Exam trap

The trap here is confusing Azure Disk Encryption (which encrypts VM disks) with Azure Storage Service Encryption (which encrypts blob data), leading candidates to select Option A when the requirement is for blob-level encryption with customer-managed keys.

How to eliminate wrong answers

Option A is wrong because Azure Disk Encryption uses BitLocker or DM-Crypt to encrypt OS and data disks of virtual machines, not the data stored in Azure Blob Storage. Option B is wrong because Azure Storage Firewall controls network access to the storage account via IP rules and virtual network rules, it does not provide encryption at rest. Option D is wrong because Azure Information Protection is a classification and labeling solution for documents and emails, not an encryption mechanism for data at rest in Azure Blob Storage.

Full explanation →
222
MCQeasy

An organization is using Azure Synapse Analytics and wants to implement column-level security to restrict access to sensitive columns. Which feature should they use?

A.Dynamic data masking
B.Azure Purview
C.Column-level security using GRANT
D.Row-level security
AnswerC

Column-level security in Azure Synapse Analytics uses GRANT statements on specific columns to restrict access to sensitive data.

Why this answer

Column-level security in Azure Synapse Analytics is implemented using GRANT statements on specific columns, restricting access to sensitive columns. Option A is incorrect because dynamic data masking obfuscates data at query time but does not prevent access. Option B is incorrect because Azure Purview is a data governance service, not for access control.

Option D is incorrect because row-level security filters rows, not columns.

Full explanation →
223
MCQeasy

You have an Azure Data Factory pipeline that uses a Copy activity to move data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with a timeout error. You need to improve the reliability of the data transfer. Which configuration change should you make?

A.Use staged copy with an intermediate Azure Blob Storage.
B.Use PolyBase as the sink.
C.Enable fault tolerance and configure skip incompatible rows.
D.Increase the retry count in the pipeline activity.
AnswerC

This allows the copy to continue even if some rows fail, improving reliability.

Why this answer

Enabling fault tolerance and configuring 'skip incompatible rows' allows the Copy activity to continue processing even when some rows cause errors (e.g., type conversion failures), which can manifest as timeouts when the activity repeatedly retries the same problematic rows. This setting improves reliability by skipping rows that cannot be copied, preventing the entire pipeline from failing on intermittent data issues.

Exam trap

The trap here is that candidates confuse 'fault tolerance' with 'retry policy,' assuming that increasing retries is the only way to handle failures, whereas fault tolerance addresses row-level errors that cause timeouts without requiring a full activity restart.

How to eliminate wrong answers

Option A is wrong because using staged copy with an intermediate Azure Blob Storage is designed to improve performance for large data transfers or to enable PolyBase, not to address timeout errors caused by incompatible rows or transient failures. Option B is wrong because PolyBase is a sink for loading data into Azure Synapse Analytics (SQL Data Warehouse), not for Azure Blob Storage, and it does not resolve timeout errors in a Copy activity. Option D is wrong because increasing the retry count only re-executes the entire activity on failure, which can exacerbate timeout issues if the root cause is incompatible rows or data skew, leading to longer execution times without addressing the underlying problem.

Full explanation →
224
MCQmedium

You are designing a solution for a social media company that needs to store user profile data with strong consistency and low latency (under 10 ms) for reads and writes. The data model is simple key-value with occasional queries on secondary attributes. Which Azure data store meets these requirements?

A.Azure Table Storage
B.Azure SQL Database with clustered index on user ID
C.Azure Cache for Redis with persistence
D.Azure Cosmos DB with session consistency and secondary indexes
AnswerD

Cosmos DB provides <10 ms latency, strong consistency, and indexing on any attribute.

Why this answer

Azure Cosmos DB with session consistency and secondary indexes meets the requirements because it provides single-digit-millisecond latency for both reads and writes, supports strong consistency (session consistency offers monotonic reads and writes), and allows efficient queries on secondary attributes via indexing. This makes it ideal for a key-value store with occasional secondary attribute queries, unlike simpler stores that lack indexing or consistency guarantees.

Exam trap

The trap here is that candidates often choose Azure Cache for Redis (Option C) for low latency, overlooking that it lacks secondary indexes for attribute queries and does not provide strong consistency for writes, which are critical for profile data with occasional secondary lookups.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage does not support secondary indexes, making queries on non-key attributes inefficient (full table scans), and its consistency model is only eventual by default, not strong. Option B is wrong because Azure SQL Database, while supporting strong consistency and secondary indexes, typically has higher latency (often 10-30 ms) for simple key-value operations due to relational overhead and network round-trips, failing the under-10-ms requirement. Option C is wrong because Azure Cache for Redis with persistence is an in-memory cache designed for low latency but lacks built-in secondary indexes for querying non-key attributes, and its persistence model (RDB/AOF) does not guarantee strong consistency for writes (e.g., data loss on failover).

Full explanation →
225
MCQhard

A company is migrating an on-premises Hadoop cluster to Azure. The cluster uses Hive tables stored as Parquet files on HDFS. They want to minimize changes to existing Hive queries and continue using HiveQL. Which Azure storage solution should they choose?

A.Azure HDInsight with Hive and Azure Data Lake Storage Gen2
B.Azure SQL Database with PolyBase
C.Azure Databricks with Delta Lake
D.Azure Synapse Analytics with external tables
AnswerA

HDInsight provides Hive-compatible environment; ADLS Gen2 replaces HDFS seamlessly.

Why this answer

Azure HDInsight with Hive and Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a fully managed Hadoop service that supports HiveQL with minimal changes. ADLS Gen2 offers a hierarchical namespace and is optimized for Hadoop workloads, allowing the existing Parquet files on HDFS to be directly mounted and queried without data movement or schema changes.

Exam trap

The trap here is that candidates often confuse 'supporting HiveQL' with 'running Hive on any Azure service,' but only HDInsight provides the native Hive runtime and HDFS-compatible storage (ADLS Gen2) needed to run existing HiveQL queries unchanged.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database with PolyBase is a relational database engine that does not natively support HiveQL or the Hadoop file system; it would require rewriting queries and converting Parquet files to a relational format. Option C is wrong because Azure Databricks with Delta Lake is an Apache Spark-based platform that, while supporting HiveQL via Spark SQL, introduces Delta Lake's transactional layer, which changes the storage format and requires query modifications; it is not a direct Hive-on-HDFS replacement. Option D is wrong because Azure Synapse Analytics with external tables uses PolyBase to query external data, but it does not run HiveQL natively and requires creating external table definitions, altering the query interface and adding complexity.

Full explanation →
Page 2

Page 3 of 11

Page 4

All pages

1234567891011

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

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