Microsoft Azure Data Engineer Associate DP-203 DP-203 Questions 526–600 | Page 8/11

526

Multi-Selectmedium

Which TWO actions should you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool when working with large fact tables?

Select 2 answers

A.Use replicated distribution for the fact table.

B.Use round-robin distribution to evenly distribute data.

C.Create statistics on columns used in WHERE clauses.

D.Use clustered index instead of columnstore index.

E.Implement table partitioning on a date column.

AnswersC, E

Statistics help the optimizer choose efficient query plans.

Why this answer

Creating statistics on columns used in WHERE clauses enables the Azure Synapse Analytics dedicated SQL pool query optimizer to generate more accurate cardinality estimates, leading to better join strategies and index selections. Without up-to-date statistics, the optimizer may choose suboptimal plans, especially for large fact tables where data distribution skew is common.

Exam trap

The trap here is that candidates often confuse distribution methods (replicated, round-robin, hash) with performance tuning for large fact tables, overlooking that statistics maintenance is a critical and separate optimization step that directly impacts query plan quality.

Full explanation →

527

Multi-Selectmedium

You are designing a data processing solution that must handle personally identifiable information (PII). The data will be ingested from multiple sources into Azure Data Lake Storage. Which TWO actions should you take to protect the PII data during processing?

Select 2 answers

A.Apply data masking in Power BI reports to hide PII from end users.

B.Use column-level security in Azure Synapse Analytics to restrict access to sensitive columns.

C.Configure the Azure Data Lake Storage firewall to allow only specific IP addresses.

D.Implement SQL Server Always Encrypted for all data stored in the data lake.

E.Use Azure Purview to classify and label PII data in the data lake.

AnswersB, E

Column-level security allows fine-grained access control at the database level.

Why this answer

Column-level security in Azure Synapse Analytics allows you to restrict access to specific columns containing PII, such as Social Security numbers or email addresses, without altering the underlying data. This is a native feature of Synapse that can be applied during processing to ensure that only authorized users or roles can view sensitive columns, directly addressing the requirement to protect PII during data processing.

Exam trap

The trap here is that candidates often confuse data masking (which only hides data at the presentation layer) with column-level security (which restricts access at the database processing layer), leading them to select Option A instead of Option B.

Full explanation →

528

MCQhard

An organization is using Azure Data Factory to ingest data from multiple on-premises SQL Server databases into Azure Synapse Analytics. They need to ensure that sensitive data is masked during ingestion before landing in the staging area. What is the best approach?

A.Apply an Azure Policy that masks sensitive data in Azure Synapse Analytics.

B.Use Azure SQL Database dynamic data masking on the source databases.

C.Use a Mapping Data Flow with derived column transformations to mask sensitive columns.

D.Use Azure Purview to classify and mask sensitive data automatically.

AnswerC

Mapping Data Flow allows you to apply transformations like mask using derived columns before writing to staging.

Why this answer

Mapping Data Flow in Azure Data Factory allows you to transform data during ingestion. Using derived column transformations, you can apply masking functions (e.g., substituting characters, hashing) to sensitive columns before writing to the staging area. This approach masks data before it reaches the staging area, meeting the requirement.

Option A (Azure Policy) is for compliance and cannot mask data. Option B (Dynamic Data Masking on source) masks data at query time, not during ingestion. Option D (Azure Purview) is for data governance and classification, not for masking data during a pipeline.

Full explanation →

529

Multi-Selectmedium

Which TWO actions should you take to optimize performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Select 2 answers

A.Disable index on the target table after loading.

B.Use a large batch size (e.g., 100 MB) for each copy operation.

C.Use round-robin distribution for the staging table.

D.Use a small batch size (e.g., 1 MB) for each copy operation.

E.Use clustered columnstore index on the target table during load.

AnswersB, C

Large batches reduce number of transactions.

Why this answer

Using a large batch size (e.g., 100 MB) for each copy operation minimizes the number of round trips and transaction commits, which significantly improves throughput when loading large volumes of data into a dedicated SQL pool. The PolyBase or COPY statement in Azure Synapse performs best when batches are large enough to leverage parallel processing and reduce overhead from frequent small writes.

Exam trap

The trap here is that candidates often confuse batch size optimization with transaction log management, mistakenly thinking smaller batches reduce log pressure, when in fact larger batches reduce overall load time and improve throughput in Azure Synapse's distributed architecture.

Full explanation →

530

Multi-Selectmedium

Which THREE actions can improve the performance of a dedicated SQL pool in Azure Synapse Analytics?

Select 3 answers

A.Use rowstore indexes instead of columnstore indexes.

B.Partition large fact tables on a date column.

C.Use round-robin distribution for all tables.

D.Use replicated tables for small dimension tables.

E.Enable result-set caching.

AnswersB, D, E

Partitioning enables partition elimination, reducing data scanned.

Why this answer

Options B, D, and E are correct. Partitioning large fact tables on a date column allows partition elimination, improving query performance. Using replicated tables for small dimension tables avoids data movement during joins.

Enabling result-set caching stores query results for reuse, reducing compute load. Option A is incorrect because rowstore indexes are not optimal for analytics; columnstore indexes are preferred. Option C is incorrect because round-robin distribution is not suitable for all tables and can cause excessive data shuffling.

Full explanation →

531

Multi-Selectmedium

You are designing a hybrid data storage architecture for a global e-commerce platform. Which two Azure services should you combine to achieve low-latency read access for users worldwide and durable archival storage for compliance?

Select 2 answers

A.Azure Cosmos DB

B.Azure Table Storage

C.Azure Blob Storage

D.Azure SQL Database

E.Azure Files

AnswersA, C

Why this answer

Azure Cosmos DB is correct because it provides globally distributed, multi-region write and read capabilities with turnkey global distribution, enabling low-latency access for users worldwide via its multi-homing API. Azure Blob Storage is correct because it offers durable, tiered archival storage (e.g., Cool, Archive access tiers) at low cost, meeting compliance requirements for long-term retention of data such as transaction logs or invoices.

Exam trap

The trap here is that candidates often confuse Azure Table Storage with Cosmos DB's Table API, assuming Table Storage supports global distribution, when in fact only Cosmos DB's Table API (a different service) provides multi-region replication and low-latency reads.

Why the other options are wrong

Table Storage is a single-region service with higher latency.

Global distribution requires complex sharding and is not as seamless as Cosmos DB.

Not designed for archival or global low-latency reads.

Full explanation →

532

MCQmedium

You are monitoring an Azure Data Lake Storage Gen2 account using Azure Monitor. You need to be alerted when the number of storage account requests exceeds 20,000 per hour. What is the most efficient way to set up this alert?

A.Create a Log Analytics workspace and write a KQL query to count requests.

B.Create an Activity Log alert for 'List Storage Account Keys' events.

C.Create a metric alert on the 'Transactions' metric with a threshold of 20,000 and aggregation granularity of 1 hour.

D.Use Azure Advisor to recommend scaling.

AnswerC

Metric alerts are efficient and built-in.

Why this answer

The 'Transactions' metric in Azure Monitor can be used to count the number of requests to the storage account, and you can set a metric alert with a threshold of 20,000 aggregated over an hour. This is the most efficient method as it directly uses the metric without needing complex queries. Option A is wrong because it requires creating a Log Analytics workspace and writing a KQL query, which is more complex and less efficient than a metric alert.

Option B is wrong because Activity Log alerts are for management events like 'List Storage Account Keys', not for data transaction counts. Option D is wrong because Azure Advisor provides recommendations, not custom alerting on specific metric thresholds.

Full explanation →

533

MCQeasy

You need to store streaming data from Azure Event Hubs into Azure Data Lake Storage Gen2 in near real-time. The data should be stored in Avro format with a folder structure: /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. Which Azure service should you use to ingest the data?

A.Event Hubs Capture feature to automatically capture events to ADLS Gen2.

B.Azure Stream Analytics with a job that reads from Event Hubs and writes to ADLS Gen2.

C.Azure Data Factory with a tumbling window trigger to copy data from Event Hubs every 5 minutes.

D.Azure Databricks with Auto Loader to read from Event Hubs and write to ADLS Gen2.

AnswerB

Stream Analytics supports Avro output and custom partition path patterns.

Why this answer

Azure Stream Analytics is the correct choice because it natively supports reading from Event Hubs and writing to ADLS Gen2 with built-in time-based partitioning into the exact folder structure /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. It provides near real-time processing with sub-minute latency and can output data in Avro format directly, meeting all requirements without additional code or orchestration.

Exam trap

The trap here is that candidates often choose Event Hubs Capture because it seems like a simple 'capture to storage' feature, but they overlook the requirement for near real-time per-minute partitioning, which Capture cannot achieve due to its fixed 5-minute minimum window.

How to eliminate wrong answers

Option A is wrong because Event Hubs Capture writes data in fixed 5-minute or 300 MB windows, not in near real-time per minute, and its folder structure is /{EventHub}/{Namespace}/{YYYY}/{MM}/{DD}/{HH}/{mm} but cannot dynamically include the event hub name as a folder variable in the path. Option C is wrong because Azure Data Factory with a tumbling window trigger introduces at least 5 minutes of latency and is designed for batch processing, not near real-time streaming, and cannot read from Event Hubs directly without a staging layer. Option D is wrong because Azure Databricks with Auto Loader is optimized for incremental file ingestion from cloud storage, not for streaming from Event Hubs; it would require additional structured streaming code and incurs cluster startup and runtime overhead, making it less suitable for simple near real-time ingestion.

Full explanation →

534

MCQeasy

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

A.Enable checkpointing to truncate the lineage.

B.Decrease the number of partitions to reduce overhead.

C.Increase the executor memory setting in the Spark configuration.

D.Use the cache() action on intermediate DataFrames.

AnswerC

Increasing executor memory provides more heap space to avoid OOM errors.

Why this answer

An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Exam trap

The trap here is that candidates often confuse memory issues with partitioning or caching optimizations, but the immediate fix for an out-of-memory error is to increase executor memory, not to reduce parallelism or persist data.

How to eliminate wrong answers

Option A is wrong because checkpointing truncates the lineage and helps with recovery and plan optimization, but it does not directly increase available memory or resolve an out-of-memory error. Option B is wrong because decreasing the number of partitions reduces parallelism and can actually increase memory pressure per partition, worsening the out-of-memory issue. Option D is wrong because using cache() persists intermediate DataFrames in memory, which consumes additional memory and can exacerbate the out-of-memory error rather than resolving it.

Full explanation →

535

MCQhard

A data engineering team uses Azure Stream Analytics to process real-time IoT data. They notice that the job's watermark delay is increasing over time, and the output is falling behind. The input is from Event Hubs with 10 partitions. The job uses a 5-minute hopping window with a 1-minute hop. What is the most likely cause?

A.The hopping window size is too large.

B.The late arrival tolerance is set too high.

C.The job is under-provisioned in terms of Streaming Units (SUs).

D.The Event Hubs partition count does not match the Stream Analytics job's parallelism.

AnswerC

Low SUs cause backpressure, increasing watermark delay.

Why this answer

The increasing watermark delay and falling behind output indicate that the Stream Analytics job cannot keep up with the input throughput. With a 5-minute hopping window (1-minute hop) processing 10 Event Hubs partitions, the job requires sufficient Streaming Units (SUs) to handle the compute load. Under-provisioned SUs cause backpressure, leading to rising watermark delay as the job struggles to process events within the window boundaries.

Exam trap

The trap here is that candidates often confuse watermark delay with configuration issues like window size or late arrival tolerance, but the progressive nature of the delay points directly to resource starvation (SU under-provisioning) rather than a static configuration problem.

How to eliminate wrong answers

Option A is wrong because the hopping window size (5 minutes with 1-minute hop) is a standard temporal window configuration and does not inherently cause watermark delay; larger windows actually reduce computational frequency. Option B is wrong because setting the late arrival tolerance too high would allow more late events to be included, potentially increasing watermark delay, but the question states the delay is increasing over time, which is a symptom of insufficient processing capacity, not a configuration that would cause progressive delay. Option D is wrong because Stream Analytics automatically handles partition alignment with Event Hubs partitions when the job's parallelism is set to 1 (default) or when using the same partition count; mismatched partition counts do not cause increasing watermark delay but may cause uneven data distribution or idle partitions.

Full explanation →

536

MCQeasy

Your team is developing a data processing solution that uses Azure Databricks to transform streaming data from Azure Event Hubs. The transformation includes joining the stream with a static reference table stored in Azure Data Lake Storage Gen2. You need to implement the join efficiently. Which approach should you use?

A.Use a watermark on both sides and perform a stream-stream join

B.Use a broadcast join with the static DataFrame loaded from Delta Lake

C.Use foreachBatch to micro-batch the stream and perform a batch join

D.Use a stream-stream join by converting the static table to a stream

AnswerB

Broadcast join avoids shuffling and is efficient for streaming-static joins.

Why this answer

When joining a streaming DataFrame with a static reference table, Spark Structured Streaming can optimize the join by broadcasting the static DataFrame to all nodes, avoiding shuffles. This is efficient since the reference data is small and static. Option A is wrong because watermarks are used for stream-stream joins, not streaming-static joins.

Option C is wrong because foreachBatch with a batch join adds unnecessary complexity and does not leverage built-in optimizations. Option D is wrong because converting a static table to a stream is not appropriate; the reference table is static and should be read as a batch DataFrame.

Full explanation →

537

MCQmedium

You are building a data processing solution in Azure Synapse Analytics. The solution requires creating a table that stores sales transactions. The table will be used for both point-of-sale lookups and large aggregation queries. The data is not updated frequently. Which table distribution should you recommend?

A.ROUND_ROBIN

B.HASH on TransactionID

C.HASH on SalesDate

D.REPLICATE

AnswerA

ROUND_ROBIN evenly distributes data and works well for mixed workloads.

Why this answer

ROUND_ROBIN is the correct choice because the table is used for both point-of-sale lookups (single-row queries) and large aggregation queries, and the data is not updated frequently. ROUND_ROBIN distributes data evenly across all distributions without a hash key, which provides the best overall performance for mixed workloads where no single distribution key optimizes both lookup and aggregation patterns. It avoids data skew and allows parallel processing for aggregations while still supporting efficient lookups when combined with appropriate indexes.

Exam trap

The trap here is that candidates often choose HASH distribution thinking it always improves query performance, but they overlook that without a clear join or grouping column that matches the hash key, HASH can cause data skew and actually degrade mixed workload performance compared to ROUND_ROBIN.

How to eliminate wrong answers

Option B (HASH on TransactionID) is wrong because hashing on a high-cardinality column like TransactionID would distribute data evenly but would not optimize large aggregation queries that typically group by date or region, and it would not improve point-of-sale lookups unless the lookup filter includes TransactionID. Option C (HASH on SalesDate) is wrong because hashing on a date column can cause severe data skew if most transactions occur on a few dates, leading to uneven distribution and poor query performance; it also does not optimize point-of-sale lookups that rarely filter by date alone. Option D (REPLICATE) is wrong because replication is designed for small dimension tables (typically < 2 GB) and would be impractical for a large sales transaction table, causing excessive storage and maintenance overhead.

Full explanation →

538

MCQeasy

You need to ensure that an Azure Data Factory pipeline can copy data from an Azure SQL Database that is behind a private endpoint. The Data Factory should use a managed virtual network. What should you configure?

A.Install a self-hosted integration runtime on a VM in the same virtual network.

B.Use the default Azure integration runtime.

C.Enable managed virtual network for the Data Factory and create a managed private endpoint for the SQL Database.

D.Use Azure Bastion to connect the Data Factory to the SQL Database.

AnswerC

Managed private endpoints enable secure connectivity over private network.

Why this answer

A managed private endpoint in the Data Factory's managed virtual network allows secure access to the SQL Database's private endpoint. Option A is wrong because the integration runtime must be in the same virtual network. Option B is wrong because self-hosted IR is for on-premises data sources.

Option D is wrong because Azure Bastion is for VM access, not data factory.

Full explanation →

539

MCQmedium

A company uses Azure Synapse Analytics to process large datasets. They need to transform JSON data stored in Azure Data Lake Storage Gen2 into a star schema. Which data processing approach minimizes data movement and leverages the compute closest to the data?

A.Use Azure Data Factory to copy the JSON data into Azure SQL Database, then use T-SQL to transform.

B.Use Azure Data Factory with SSIS to transform and load into dedicated SQL pool.

C.Load data into a Spark DataFrame in Synapse notebooks, transform, and write back.

D.Create external tables on the JSON files using PolyBase, then use CREATE EXTERNAL TABLE AS SELECT (CETAS) to write transformed Parquet files.

AnswerD

Minimizes movement by querying in place.

Why this answer

It uses PolyBase external tables and CETAS to transform JSON data directly in Azure Data Lake Storage Gen2, minimizing data movement by leveraging the compute power of the dedicated SQL pool or serverless SQL pool closest to the data. This approach reads JSON in place, transforms it into Parquet format, and writes the star schema tables back to the data lake without copying data to an intermediate store.

Exam trap

The trap here is that candidates often assume Spark notebooks (Option C) are always the best for JSON transformation, but PolyBase with CETAS is more efficient for minimizing data movement because it processes data in-place using SQL compute without loading entire datasets into memory.

How to eliminate wrong answers

Option A is wrong because it copies JSON data into Azure SQL Database first, incurring unnecessary data movement and network transfer, and uses T-SQL in a separate compute environment rather than leveraging compute closest to the data lake. Option B is wrong because it uses SSIS, which requires an Azure-SSIS Integration Runtime and moves data through a separate orchestration layer, adding latency and cost without utilizing Synapse-native processing. Option C is wrong because while Spark DataFrames in Synapse notebooks can process JSON, they require spinning up a Spark pool and loading data into memory, which involves more data movement and overhead compared to the serverless or dedicated SQL pool PolyBase approach that processes data directly in the storage layer.

Full explanation →

540

MCQhard

Refer to the exhibit. You have an Azure Synapse Analytics workspace. You need to ensure that data processing jobs can access the Data Lake Storage Gen2 account using a managed identity. What should you do?

A.Use the SQL admin login credentials to access the storage account

B.Enable the system-assigned managed identity on the Synapse workspace and assign it the 'Storage Blob Data Contributor' role on the storage account

C.Create a private endpoint connection between the workspace and the storage account

D.Configure the storage account firewall to allow access from the Synapse workspace

AnswerB

The managed identity needs RBAC permissions on the storage account.

Why this answer

Azure Synapse Analytics supports system-assigned managed identities, which provide a secure, passwordless authentication method for accessing Azure Data Lake Storage Gen2. By enabling the managed identity on the Synapse workspace and assigning it the 'Storage Blob Data Contributor' role, you grant the workspace's data processing jobs the necessary permissions to read, write, and delete data in the storage account without managing credentials.

Exam trap

The trap here is that candidates often confuse network-level access controls (firewall rules or private endpoints) with identity-based authorization (RBAC), mistakenly thinking that allowing network traffic alone is sufficient for data access.

How to eliminate wrong answers

Option A is wrong because using SQL admin login credentials to access a storage account is not supported; SQL authentication is for database access, not for Azure Storage RBAC. Option C is wrong because creating a private endpoint ensures network-level isolation and private connectivity, but it does not grant the identity permissions to access the storage account; RBAC role assignment is still required. Option D is wrong because configuring the storage account firewall to allow access from the Synapse workspace only controls network traffic, not authentication or authorization; the managed identity still needs the appropriate RBAC role to perform data operations.

Full explanation →

541

MCQeasy

Your Azure Data Lake Storage Gen2 account stores sensitive data. You need to audit who accesses the data and when, and you want to send the audit logs to a Log Analytics workspace for analysis. What should you configure?

A.Azure Activity Logs

B.Microsoft Sentinel

C.Azure Monitor alerts

D.Diagnostic settings on the storage account

AnswerD

Diagnostic settings enable streaming of data plane audit logs to Log Analytics.

Why this answer

Diagnostic settings on the storage account can stream audit logs (like read, write, delete) to Log Analytics for analysis. Option A is incorrect because Azure Activity Logs capture control plane operations, not data plane access. Option B is incorrect because Microsoft Sentinel is a SIEM that would consume logs from diagnostic settings, not a direct configuration for log collection.

Option C is incorrect because Azure Monitor alerts are for notifications based on metrics or logs, not for collecting logs.

Full explanation →

542

Matchingmedium

Match each Azure service to its primary purpose in a data engineering pipeline.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Scalable data lake for analytics workloads

Unified analytics platform with SQL and Spark

Cloud-based ETL and data integration service

Real-time stream processing service

Apache Spark-based analytics platform

Why these pairings

The correct matches are: Azure Data Lake Storage Gen2 as a scalable data lake, Azure Synapse Analytics as a unified data warehousing platform, Azure Data Factory for data integration and orchestration, and Azure Databricks for Spark-based analytics. Common confusions include swapping storage and integration services, or mistaking data lakes for data warehousing.

Full explanation →

543

MCQeasy

You are using Azure Data Factory to copy data from Azure Blob Storage to Azure SQL Database. The copy operation fails with error 'Cannot insert duplicate key'. What is the most likely cause and solution?

A.The source files are in an incompatible format; convert to CSV

B.The sink table schema does not match the source; update the schema

C.The copy activity is not using staging; enable staging

D.The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity

AnswerD

Upsert behavior can handle duplicate key violations.

Why this answer

The error 'Cannot insert duplicate key' indicates that the sink table in Azure SQL Database has a primary key or unique constraint, and the source data contains rows with duplicate key values. Enabling upsert in the copy activity allows Azure Data Factory to handle duplicates by updating existing rows instead of failing, resolving the conflict.

Exam trap

The trap here is that candidates often confuse duplicate key errors with schema or format issues, or think staging is required for deduplication, but the correct solution is to use upsert to handle primary key conflicts.

How to eliminate wrong answers

Option A is wrong because an incompatible format would cause parsing or schema errors, not a duplicate key violation. Option B is wrong because a schema mismatch would result in column mapping or type conversion errors, not a duplicate key issue. Option C is wrong because staging is used for performance optimization or PolyBase/COPY statement scenarios, not for handling duplicate key conflicts.

Full explanation →

544

Multi-Selecthard

Which THREE of the following are valid methods to load data into Azure Synapse Analytics?

Select 3 answers

A.Azure Data Factory pipeline

B.COPY INTO T-SQL command

C.BULK INSERT with a data source from Azure Blob Storage

D.SQL Server Integration Services (SSIS) package

E.PolyBase from Azure Blob Storage

AnswersA, B, E

Data Factory can copy data into Synapse using the Copy activity.

Why this answer

Azure Data Factory (ADF) is a fully managed cloud-based ETL service that provides native connectors to Azure Synapse Analytics. It supports high-throughput data movement using the PolyBase engine or staged copy, making it a valid and recommended method for loading data at scale into Synapse dedicated SQL pools.

Exam trap

The trap here is that candidates often confuse BULK INSERT (which works only for SQL Server on-premises or IaaS VMs) with the COPY INTO command (which is the Synapse-specific equivalent for Azure Blob Storage), leading them to incorrectly select Option C as valid.

Full explanation →

545

MCQmedium

Refer to the exhibit. You have an Azure Data Factory with two triggers defined as shown. The DailyTrigger runs the CopyPipeline every day at midnight UTC. The BlobTrigger runs the ProcessPipeline when a blob is created in the /input/ folder. You notice that the ProcessPipeline is not executing even though blobs are being created. What is the most likely cause?

A.The blobPathBeginsWith property is missing the container name.

B.The BlobEventsTrigger is configured to listen to the wrong event type.

C.The ProcessPipeline expects parameters that are not provided by the trigger.

D.The storage account does not have an event subscription configured for blob creation.

AnswerD

BlobEventsTrigger requires an event subscription to route events to Data Factory.

Why this answer

The most likely cause is that the storage account does not have an event subscription configured for blob creation. The BlobEventsTrigger in Azure Data Factory relies on an event subscription from the storage account to the trigger. Without this subscription, the trigger will not fire even when blobs are created in the specified path.

Option D correctly identifies this issue. Option A is incorrect because the blobPathBeginsWith property includes the container name prefix; Option B is incorrect because the trigger is configured to listen to blob creation events; Option C is incorrect because the ProcessPipeline does not require parameters from the trigger.

Full explanation →

546

MCQhard

You are troubleshooting a pipeline in Azure Data Factory that copies data from an Azure Blob Storage to an Azure Synapse Analytics dedicated SQL pool. The pipeline fails with the error: 'PolyBase requires a varchar(max) column to be less than 1 MB.' Which action should you take to resolve this issue?

A.Modify the source data to truncate varchar(max) columns to 8000 characters.

B.Configure the copy activity to use staging via Azure Blob Storage.

C.Increase the 'batchSize' property in the copy activity to 10000.

D.Disable PolyBase in the sink settings and use bulk insert instead.

AnswerB

Using staging allows PolyBase to handle large varchar(max) columns by breaking them into smaller chunks.

Why this answer

The error indicates that PolyBase is being used for the copy operation, and it has a limitation that varchar(max) columns must be less than 1 MB. Configuring staging via Azure Blob Storage (option B) allows the copy activity to use PolyBase with staging, which automatically splits large varchar(max) values into manageable chunks, bypassing the 1 MB limit. This is the recommended approach in Azure Data Factory for loading large string data into Synapse dedicated SQL pools.

Exam trap

The trap here is that candidates often assume the error requires truncating data or switching to bulk insert, but the correct solution leverages PolyBase's staging feature to handle large columns without data loss.

How to eliminate wrong answers

Option A is wrong because truncating varchar(max) columns to 8000 characters is a data loss solution and does not address the PolyBase limitation; PolyBase's 1 MB limit is on the total size of the column value, not character count, and truncation may still exceed 1 MB if the data is multi-byte. Option C is wrong because the 'batchSize' property controls the number of rows per batch for bulk insert operations, not the size of individual columns, and it does not affect PolyBase's varchar(max) size restriction. Option D is wrong because disabling PolyBase and using bulk insert would work but is less performant and not the recommended resolution; the error specifically occurs when PolyBase is enabled, and using staging with PolyBase is the intended fix.

Full explanation →

547

MCQmedium

A company uses Azure SQL Database for an OLTP application. They need to run complex analytical queries without impacting OLTP performance. Which solution should they implement?

A.Enable automatic tuning

B.Enable geo-replication

C.Create a readable secondary replica

D.Create a failover group

AnswerC

Correct. A readable secondary replica allows read-only queries without affecting the primary.

Why this answer

Creating a readable secondary replica (Option C) offloads read-only analytical queries to a synchronized copy of the database, isolating them from the primary OLTP workload. Azure SQL Database supports this via Active Geo-Replication or Hyperscale named replicas, ensuring the primary remains unaffected by heavy analytical processing.

Exam trap

The trap here is that candidates confuse high-availability features (failover groups, geo-replication) with workload isolation, assuming any replication solves the performance impact, whereas only a dedicated readable secondary explicitly separates read-only analytical traffic from the primary OLTP workload.

How to eliminate wrong answers

Option A is wrong because automatic tuning focuses on index and query plan optimization for the primary database, not on isolating analytical workloads. Option B is wrong because geo-replication provides disaster recovery and read-scale capabilities, but its primary purpose is not to offload complex analytical queries without impacting OLTP; it can be used for read-only workloads but is not the optimal solution for analytical isolation. Option D is wrong because a failover group manages geo-replication and failover for high availability, not for distributing analytical queries away from the primary.

Full explanation →

548

Multi-Selectmedium

You are using Azure Stream Analytics to process real-time data from an Event Hub. Which TWO of the following are valid output sinks?

Select 2 answers

A.Azure Synapse Analytics

B.Azure Blob Storage

C.Azure Queue Storage

D.Azure Files

E.Azure Table Storage

AnswersA, B

Supported via a dedicated SQL pool.

Why this answer

Azure Stream Analytics supports Azure Synapse Analytics as a native output sink, allowing you to write real-time streaming results directly into dedicated SQL pools for high-performance analytics. This is achieved via the built-in Azure Synapse Analytics output adapter, which uses PolyBase or COPY INTO for efficient bulk ingestion.

Exam trap

The trap here is that candidates often confuse Azure Table Storage or Queue Storage as valid sinks because they are general Azure storage services, but Stream Analytics has a specific, limited set of supported output sinks documented in official Microsoft documentation.

Full explanation →

549

Multi-Selecthard

Which TWO actions should be taken to secure data at rest in Azure Data Lake Storage Gen2?

Select 2 answers

A.Enable soft delete for blobs

B.Configure firewall and virtual network rules

C.Enable customer-managed keys in Azure Key Vault

D.Assign storage blob data contributor roles to users

E.Use client-side encryption with Azure Key Vault

AnswersB, C

Restricts network access to storage.

Why this answer

Configuring firewall and virtual network rules restricts network-level access to the storage account, preventing unauthorized traffic from reaching the data at rest. Option C is correct because enabling customer-managed keys (CMK) in Azure Key Vault provides an additional encryption layer for data at rest, allowing you to control and rotate encryption keys independently of Azure-managed keys.

Exam trap

The trap here is confusing data protection mechanisms (soft delete, RBAC, client-side encryption) with the specific Azure-native controls for securing data at rest (network isolation and encryption key management).

Full explanation →

550

MCQeasy

Your company runs an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage daily. Recently, the pipeline has been failing with the error: 'Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation, Error details: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).' The FTP server administrator confirms that the file exists and the credentials are correct. You need to resolve the issue with minimal administrative effort. What should you do?

A.Use an SFTP connector instead of FTP

B.Reset the FTP server credentials in the linked service

C.Check the file path and correct the case sensitivity in the dataset

D.Ask the FTP administrator to re-upload the file

AnswerC

FTP servers often use case-sensitive paths.

Why this answer

The error code 550 indicates that the file is not found or access is denied on the FTP server. Since the file exists and credentials are correct, the most likely cause is a case-sensitive file path mismatch. Option C is correct because checking and correcting the case sensitivity in the dataset resolves this.

Option A is incorrect because switching to SFTP does not address the case sensitivity issue. Option B is incorrect because resetting credentials is unnecessary when they are already correct. Option D is incorrect because re-uploading the file does not fix the path reference problem.

Full explanation →

551

Multi-Selecthard

Which THREE of the following are required to implement column-level security in Azure Synapse Analytics dedicated SQL pool?

Select 1 answer

A.A GRANT statement on specific columns to users or roles

B.A VIEW that selects only the allowed columns

C.A DENY statement on specific columns to users or roles

D.A row-level security policy must be in place

E.The database user must have a default schema

AnswersA

GRANT allows access to specified columns.

Why this answer

Column-level security in Azure Synapse Analytics dedicated SQL pool is implemented using GRANT statements on specific columns. By granting SELECT on only certain columns to a user or role, you restrict access to sensitive data at the column level. While a VIEW can help simplify permission management, it is not mandatory.

A database user does not require a default schema specifically for column-level security; it is a general database requirement. Therefore, only Option A is strictly required.

Exam trap

The trap here is that candidates often confuse column-level security with row-level security or assume that DENY statements can be used at the column level, but Azure Synapse only supports GRANT for column-level permissions and does not support DENY on individual columns.

Full explanation →

552

MCQeasy

You are implementing a data pipeline using Azure Data Factory. The source is an on-premises SQL Server database. Which Azure Data Factory component is required to connect to the on-premises data source?

A.Azure Integration Runtime

B.Self-hosted Integration Runtime

C.Managed Virtual Network Integration Runtime

D.Azure Data Factory Gateway

AnswerB

Why this answer

A self-hosted integration runtime (IR) is required to connect Azure Data Factory to on-premises SQL Server because it provides the compute environment for data movement between on-premises networks and Azure. It must be installed on a machine inside the corporate firewall, enabling secure communication via outbound HTTPS (port 443) to Azure. This is the only IR type that can access private, on-premises data sources directly.

Exam trap

The trap here is that candidates often confuse the Self-hosted Integration Runtime with the Azure Integration Runtime, not realizing that only the self-hosted variant can bridge on-premises and cloud networks, while the Azure IR is restricted to cloud-to-cloud scenarios.

Why the other options are wrong

Azure IR runs in the cloud and cannot access on-premises networks directly.

Managed VNet IR is for secure access to Azure resources, not on-premises.

While historically called Gateway, the correct term is Self-hosted Integration Runtime.

Full explanation →

553

MCQmedium

Refer to the exhibit. You are monitoring the CopyDataPipeline in Azure Data Factory. The copy activity is failing with timeout errors. What is the most likely cause?

A.The writeBatchTimeout is set too low (30 seconds), causing timeouts

B.The enableStaging is false, causing network congestion

C.The writeBatchSize is too large (10000), exceeding SQL limits

D.The recursive property is set to true, causing infinite loops

AnswerA

30 seconds may be insufficient for large batches.

Why this answer

The copy activity in Azure Data Factory is failing with timeout errors because the `writeBatchTimeout` is set to 30 seconds, which is too low for the volume of data being written to the sink. This property defines the maximum time allowed for a single batch write operation to complete; when it expires, the activity times out. Increasing this value (e.g., to 120 seconds or more) accommodates larger or slower writes, resolving the timeout.

Exam trap

The trap here is that candidates often confuse `writeBatchTimeout` with `writeBatchSize`, assuming a large batch size causes timeouts, but the timeout is a separate property that controls how long the system waits for a batch to complete, not the size of the batch itself.

How to eliminate wrong answers

Option B is wrong because `enableStaging` being false does not cause network congestion; staging is an optional intermediate storage for large data transfers or to enable PolyBase, and its absence does not inherently lead to timeouts. Option C is wrong because `writeBatchSize` of 10000 rows is within typical SQL Database limits (default batch size is 10,000 rows for bulk insert), and exceeding limits would cause row-level errors, not timeout errors. Option D is wrong because `recursive` property controls whether subdirectories are processed in file-based sources (e.g., Blob storage) and has no effect on timeout behavior in a copy activity; it cannot cause infinite loops.

Full explanation →

554

MCQmedium

A data engineer is designing a batch processing pipeline that reads data from Azure Blob Storage, transforms it using Azure Databricks, and writes the output to Azure Synapse Analytics. The source files are in CSV format and arrive daily at 02:00 UTC. The transformation must be idempotent and the pipeline should handle late-arriving data (up to 2 hours). What is the best approach to trigger the pipeline?

A.Storage event trigger using Azure Event Grid

B.Schedule trigger set to 02:00 UTC daily

C.Tumbling window trigger with window size of 1 day and a late arrival window of 2 hours

D.Event trigger on blob creation in the container

AnswerC

Ensures idempotency and handles late data by allowing up to 2 hours delay.

Why this answer

A tumbling window trigger in Azure Data Factory allows you to define a fixed-size window (1 day) and a late arrival window (2 hours), which ensures idempotent processing by automatically rerunning the window for late-arriving data within the specified delay. This matches the requirement for daily batch processing at 02:00 UTC while handling data arriving up to 2 hours late.

Exam trap

Microsoft often tests the distinction between schedule triggers (fixed time) and tumbling window triggers (window-based with late arrival handling), where candidates mistakenly choose a simple schedule trigger because they overlook the late-arriving data requirement.

How to eliminate wrong answers

Option A is wrong because a Storage event trigger using Azure Event Grid fires on every blob creation event, which would cause duplicate processing for late-arriving data and does not guarantee idempotency without custom deduplication logic. Option B is wrong because a Schedule trigger set to 02:00 UTC daily cannot handle late-arriving data; it runs only at the scheduled time and misses files that arrive after the trigger execution. Option D is wrong because an Event trigger on blob creation in the container is event-driven and will process each blob individually, leading to non-idempotent behavior and potential out-of-order processing for late-arriving files.

Full explanation →

555

Matchingmedium

Match each Azure monitoring service to its function.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Collect and analyze telemetry from Azure resources

Query and analyze log data

Numerical data from Azure resources

Interactive analytics on large telemetry datasets

Why these pairings

The correct matches are: Azure Monitor collects telemetry, Azure Log Analytics queries logs, Azure Application Insights monitors web apps, and Azure Service Health provides service health alerts. Common confusions occur between Azure Monitor and Application Insights, and between Azure Monitor and Log Analytics.

Full explanation →

556

MCQeasy

You need to store log data from multiple Azure services in a single location for long-term retention and cost-effective querying. The data is append-only and rarely modified. Which storage solution should you use?

A.Azure SQL Database

B.Azure Data Lake Storage Gen2

C.Azure Table Storage

D.Azure Cosmos DB

AnswerB

ADLS Gen2 provides scalable, cost-effective storage for log data with analytics capabilities.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is optimized for append-only, rarely modified data like logs, offering hierarchical namespace, POSIX-like ACLs, and cost-effective tiered storage (hot/cool/archive). It supports high-throughput querying via Azure Synapse, Athena, or Spark, making it ideal for long-term retention and analytics on immutable log data.

Exam trap

The trap here is that candidates confuse 'append-only' with a database requirement and pick Azure SQL Database or Cosmos DB, overlooking that log storage prioritizes cost and schema-on-read over transactional consistency or low-latency writes.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational OLTP store with high transaction costs and schema rigidity, not designed for append-only log blobs or cost-effective long-term retention. Option C is wrong because Azure Table Storage is a NoSQL key-value store for semi-structured data with limited query capabilities (only on partition/row key) and no native support for hierarchical namespaces or large-scale analytics. Option D is wrong because Azure Cosmos DB is a globally distributed, multi-model database optimized for low-latency reads/writes and real-time applications, not for cost-effective archival of append-only logs; its RU-based pricing makes long-term storage expensive.

Full explanation →

557

Multi-Selecthard

You are monitoring an Azure Data Lake Storage Gen2 account that stores streaming data from IoT devices. You notice that query performance on the data in Parquet format is degrading over time. You need to improve query performance for both current and future data. Which TWO actions should you take?

Select 2 answers

A.Move frequently accessed data to Azure SQL Database.

B.Partition the data by a column commonly used in filter conditions.

C.Convert the Parquet files to Delta Lake format and enable file compaction.

D.Enable soft delete on the storage account to optimize read performance.

E.Migrate the data to Azure NetApp Files for lower latency.

AnswersB, C

Partitioning reduces the amount of data scanned per query.

Why this answer

Partitioning the data by a column commonly used in filter conditions (e.g., date, device ID) enables predicate pushdown in query engines like Azure Synapse or Spark, allowing them to skip irrelevant partitions and scan only the necessary files. This directly addresses the performance degradation by reducing the amount of data read during queries, and it benefits both current and future data when applied consistently.

Exam trap

The trap here is that candidates often confuse data protection features (like soft delete) or storage migration options (like Azure SQL or NetApp Files) with performance optimization techniques, failing to recognize that partitioning and file format optimization are the standard solutions for improving query performance on large-scale Parquet data in a data lake.

Full explanation →

558

MCQmedium

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

A.Azure Cosmos DB

B.Azure SQL Database

C.Azure Synapse Analytics

D.Azure Table Storage

AnswerB

Why this answer

Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its low-latency and global distribution capabilities, overlooking that it does not provide native relational joins or the strong consistency required for transactional workloads, which Azure SQL Database is specifically designed for.

Why the other options are wrong

Cosmos DB is NoSQL and while it can be configured for strong consistency, it does not natively support complex joins across multiple tables as efficiently as a relational database.

Synapse is a data warehouse for analytics, not designed for transactional workloads with frequent updates.

Table Storage is a NoSQL key-value store with limited query capabilities and no support for complex joins.

Full explanation →

559

MCQeasy

You are designing a streaming job in Azure Stream Analytics. The job needs to count the number of events per device type every 10 seconds. The input is from Event Hubs. Which query should you use?

A.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

B.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10)

C.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

D.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)

AnswerB

Tumbling window outputs exactly every 10 seconds.

Why this answer

A TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.

Exam trap

The trap here is that candidates confuse HoppingWindow with TumblingWindow, thinking a hop size of 1 second still produces 10-second intervals, but HoppingWindow emits results at every hop, not at the window duration, leading to incorrect output frequency.

How to eliminate wrong answers

Option A is wrong because SessionWindow(second, 10, 30) defines session windows based on inactivity gaps, not fixed 10-second intervals; the 30-second timeout means windows can be much longer than 10 seconds, violating the requirement. Option C is wrong because HoppingWindow(second, 10, 1) creates overlapping windows that emit results every 1 second, not every 10 seconds, leading to redundant counts. Option D is wrong because SlidingWindow(second, 10) produces a continuous stream of results for every event within the last 10 seconds, not discrete 10-second intervals, so it does not count events 'every 10 seconds' as a batch.

Full explanation →

560

Multi-Selectmedium

Which TWO features can be used to audit access to data in Azure Storage? (Choose two.)

Select 2 answers

A.Azure Monitor diagnostic settings

B.Azure Storage analytics logs

C.Azure RBAC role assignments

D.Azure Policy

E.Microsoft Defender for Cloud

AnswersA, B

Sends logs to Log Analytics for querying

Why this answer

Options A and B are correct. Option A: Azure Monitor diagnostic settings can be configured to send resource logs (including storage audit logs) to Log Analytics, Storage, or Event Hubs for auditing. Option B: Storage Analytics logs provide detailed information about successful and failed requests to a storage account, which can be used for auditing.

Option C is incorrect because Azure RBAC role assignments are for access control, not auditing. Option D is incorrect because Azure Policy enforces compliance rules, not auditing. Option E is incorrect because Microsoft Defender for Cloud provides security alerts and threat protection, but not detailed access auditing.

Full explanation →

561

MCQhard

You are a data engineer at a financial services company. The company uses Azure Synapse Analytics with a dedicated SQL pool for its data warehouse. The current table 'FactTransactions' is 2 TB and uses round-robin distribution. Query performance is poor for queries that frequently filter on 'CustomerID' and join with a 'DimCustomer' table (10 GB, replicated). You need to redesign the table to improve query performance while minimizing data movement during queries. The solution must also support incremental data loading with minimal overhead. You cannot change the storage size limit or add more DWU. What should you do?

A.Replicate the FactTransactions table to all distributions.

B.Keep round-robin distribution but add indexes on CustomerID and TransactionDate.

C.Change distribution to hash on TransactionDate and partition by month.

D.Recreate the table using hash distribution on CustomerID and use CTAS for incremental loads.

AnswerD

Collocates data on CustomerID, reducing data movement; CTAS handles incremental loads.

Why this answer

Hash distribution on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution, which eliminates data movement during joins with the replicated DimCustomer table. Using CTAS (CREATE TABLE AS SELECT) for incremental loads allows you to efficiently rebuild the table with minimal overhead by loading only new data into a staging table, then swapping partitions or using CTAS to replace the target table without blocking reads.

Exam trap

The trap here is that candidates often choose hash distribution on a date column (Option C) thinking it helps with time-based queries, but the question specifically requires improving join performance on CustomerID, so the distribution key must match the join key to avoid data movement.

How to eliminate wrong answers

Option A is wrong because replicating a 2 TB FactTransactions table to all distributions would exceed the storage capacity (each distribution would hold a full copy, multiplying storage by 60 distributions) and is not feasible given the storage size limit. Option B is wrong because round-robin distribution distributes rows randomly across distributions, so queries filtering on CustomerID still require data shuffling across nodes to gather matching rows, and indexes on a columnstore table are not effective for point lookups in a distributed environment. Option C is wrong because hash distribution on TransactionDate does not align with the join key (CustomerID), so joins with DimCustomer would still cause data movement; partitioning by month adds maintenance overhead and does not reduce data shuffling for the join.

Full explanation →

562

MCQmedium

You are using Azure Synapse Analytics dedicated SQL pool to run a query that joins a large fact table (10 billion rows) and a small dimension table (1 million rows). The query is slow. Which distribution strategy should you use for the dimension table to improve performance?

A.Round-robin distribute the dimension table.

B.Hash-distribute the dimension table on its primary key.

C.Replicate the dimension table to all compute nodes.

D.Hash-distribute the dimension table on the foreign key column.

AnswerC

Replication avoids data movement for small tables.

Why this answer

Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Exam trap

The trap here is that candidates often choose hash distribution on the foreign key (Option D) thinking it aligns the join keys, but they overlook that the fact table is typically distributed on a different column (e.g., its own primary key or a date column), so the join still requires data movement, whereas replication is the optimal strategy for small dimension tables in a star schema.

How to eliminate wrong answers

Option A is wrong because round-robin distribution spreads the dimension table evenly across distributions without any alignment with the fact table, causing all join operations to require data movement (shuffle) across nodes, which is highly inefficient for a large fact table. Option B is wrong because hash-distributing the dimension table on its primary key does not align with the fact table's distribution key (typically the foreign key), so the join will still require redistributing one or both tables unless the fact table is also hash-distributed on the same column. Option D is wrong because hash-distributing the dimension table on the foreign key column would scatter its rows across distributions, but the fact table is likely hash-distributed on a different column (e.g., its own primary key or a different foreign key), so the join would still cause data movement; moreover, dimension tables are typically small and benefit more from replication than from hash distribution.

Full explanation →

563

MCQhard

Your company uses Azure Synapse Analytics and has deployed a pipeline that uses a Mapping Data Flow to transform data. The data flow reads from a source in Azure Blob Storage and writes to a dedicated SQL pool. You notice that the data flow is running slowly and consuming a lot of Data Flow cluster resources. You need to improve performance without increasing the cluster size. Which action should you take?

A.Use a self-hosted integration runtime instead of the default auto-resolve IR.

B.Increase the batch size in the data flow settings to reduce the number of round trips.

C.Add a partitioning step in the data flow to distribute the data across partitions based on a key column.

D.Change the source format to Delta Lake to leverage optimizations.

AnswerC

Partitioning the data can improve parallelism and performance.

Why this answer

Adding a partitioning step in the Mapping Data Flow distributes data across partitions based on a key column, which allows parallel processing across the cluster's nodes. This reduces data shuffling and improves throughput without increasing the cluster size, directly addressing the performance bottleneck caused by skewed or unpartitioned data.

Exam trap

The trap here is that candidates often confuse increasing batch size (Option B) with improving parallelism, but batch size only affects sink write operations, not the internal data processing distribution that causes cluster resource exhaustion.

How to eliminate wrong answers

Option A is wrong because using a self-hosted integration runtime (IR) would introduce network latency and management overhead, and it does not address the internal data flow processing inefficiency; the default auto-resolve IR is optimized for Azure services. Option B is wrong because increasing the batch size in data flow settings reduces round trips to the sink but does not resolve the root cause of slow transformation performance, which is data distribution and parallelism within the cluster. Option D is wrong because changing the source format to Delta Lake does not inherently improve performance for a Mapping Data Flow reading from Blob Storage; Delta Lake optimizations (like Z-ordering) require a Delta Lake engine and are not applicable to the current pipeline's source format.

Full explanation →

564

MCQhard

You are designing a batch processing pipeline in Azure Databricks. The data is stored in Delta Lake and you need to perform a time-series join between two tables: 'events' (100 billion rows) and 'sessions' (10 billion rows). The join condition is on 'device_id' and a timestamp range (event_time BETWEEN session_start AND session_end). Which join strategy would be most efficient?

A.Broadcast the smaller table (sessions) to all nodes.

B.Use a range join with interval threshold using Delta Lake's optimized join.

C.Use a sort-merge join by repartitioning both tables on device_id.

D.Bucket both tables on device_id with 500 buckets.

AnswerB

Delta Lake supports range join optimization with interval thresholds, reducing data shuffle.

Why this answer

Delta Lake's optimized range join leverages interval threshold pruning and data skipping to efficiently handle time-series joins on large datasets. This strategy avoids full shuffles by using min/max statistics and Bloom filters to eliminate non-matching partitions, making it far more efficient than generic join methods for 100B and 10B row tables.

Exam trap

Microsoft often tests the misconception that broadcasting a large table is acceptable if it fits in memory, but the trap here is that candidates overlook the driver memory limit and assume broadcast join scales linearly, while the correct answer requires understanding Delta Lake's specialized range join optimization for time-series data.

How to eliminate wrong answers

Option A is wrong because broadcasting a 10 billion row 'sessions' table would exceed driver memory and cause out-of-memory errors; broadcast joins are only suitable for small tables (typically < 1 GB). Option C is wrong because a sort-merge join with repartitioning on 'device_id' alone does not optimize the timestamp range condition, leading to a full shuffle of both massive tables and poor performance. Option D is wrong because bucketing on 'device_id' with 500 buckets does not address the range join predicate; it only co-locates rows by hash, but the timestamp range still requires a cross-join-like comparison within each bucket, which is inefficient.

Full explanation →

565

MCQmedium

You have an Azure Data Factory (ADF) pipeline that runs hourly to ingest data from an on-premises SQL Server into Azure Data Lake Storage Gen2. The pipeline includes a Copy activity that transfers all rows from a source table 'Sales' (approximately 10 million rows) to a Parquet file in the data lake. Recently, you notice that the pipeline runtime has increased from 15 minutes to over an hour. The source database CPU utilization is normal, and the network bandwidth is not saturated. You check ADF monitoring and see high 'Data integration unit' consumption and frequent 'BlobWrite' throttling errors. The storage account is in the same region as the ADF. You need to reduce the pipeline runtime. What should you do?

A.Change the storage account to Premium tier to increase throughput limits.

B.Modify the pipeline to use incremental loads instead of full loads each time.

C.Replace the Copy activity with an Azure Databricks notebook to process the data.

D.Use PolyBase in the Copy activity to load data directly into Azure Synapse Analytics.

AnswerB

Reduces data volume per run, decreasing storage throttling and runtime.

Why this answer

The pipeline runtime has increased due to frequent BlobWrite throttling errors, indicating that the storage account is hitting its write request limits. By modifying the pipeline to use incremental loads instead of full loads each hour, you reduce the volume of data written per execution, which lowers the number of write operations and avoids throttling. This directly addresses the root cause without requiring a storage tier upgrade or a complete architectural change.

Exam trap

The trap here is that candidates often assume throttling errors require a storage tier upgrade (Option A) or a compute change (Option C), when the real solution is to reduce the volume of data written per execution by implementing incremental loading.

How to eliminate wrong answers

Option A is wrong because upgrading to Premium tier increases throughput for block blobs but does not eliminate the fundamental issue of writing 10 million rows every hour; throttling can still occur if the write request rate exceeds the account limits, and the cost increase may not be justified. Option C is wrong because replacing the Copy activity with an Azure Databricks notebook adds complexity and overhead without addressing the storage throttling; the bottleneck is at the sink (BlobWrite), not the compute, and Databricks would still write to the same storage account. Option D is wrong because PolyBase is used for loading data into Azure Synapse Analytics, not for writing to Azure Data Lake Storage Gen2; it does not apply to the current sink and would not resolve the BlobWrite throttling errors.

Full explanation →

566

MCQhard

You are developing a real-time data processing solution for a financial services company. The system ingests stock trade data from Azure Event Hubs at 50,000 events per second. Each event is a JSON object with fields: TradeID, Symbol, Price, Quantity, Timestamp. You need to calculate a 5-minute rolling average of the trade price per symbol and store the result in Azure Cosmos DB for low-latency queries. Additionally, you need to detect anomalies where the price deviates more than 10% from the rolling average within the same window, and send alerts to Azure Event Grid. You must minimize latency and ensure that the processing is stateful across multiple partitions. What should you do?

A.Use Azure Functions with Event Hubs trigger. In each function invocation, compute the rolling average using a distributed cache (Redis) and detect anomalies. Write to Cosmos DB and Event Grid via output bindings.

B.Use Azure Synapse Pipelines with a Data Flow. Set up a streaming Data Flow from Event Hubs, compute rolling average using window functions, and sink to Cosmos DB and Event Grid.

C.Use Azure Databricks with Structured Streaming. Read from Event Hubs using Kafka API. Perform windowed aggregations and anomaly detection using Spark SQL. Write to Cosmos DB via the Azure Cosmos DB Spark connector and to Event Grid via HTTP sink.

D.Create an Azure Stream Analytics job. Define input from Event Hubs. Use a Tumbling window of 5 minutes to compute average price per symbol. Add a custom function to compare each event's price to the average and output anomalies. Write to Cosmos DB via the Azure Cosmos DB output adapter and to Event Grid via the Event Grid output adapter.

AnswerD

Stream Analytics provides native support for windowing, stateful processing, and multiple outputs.

Why this answer

Azure Stream Analytics (Option D) is the most appropriate service for this scenario. It can ingest from Event Hubs, perform windowed aggregations (e.g., Tumbling window for rolling average), detect anomalies using conditional logic, and output to both Cosmos DB and Event Grid. It handles partitioning automatically and provides stateful processing with low latency.

Option A uses Azure Functions, which are not designed for high-throughput stateful stream processing across partitions; each function invocation is stateless and would require an external cache (Redis) adding latency and complexity. Option B incorrectly describes Azure Synapse Pipelines with Data Flow, which is a batch-oriented ETL tool and not suitable for real-time streaming. Option C uses Azure Databricks Structured Streaming, which is more complex to manage and requires additional configuration for state management and output to multiple sinks, resulting in higher operational overhead compared to Stream Analytics.

Full explanation →

567

MCQmedium

Your company uses Azure Data Factory to orchestrate data movement. You need to monitor pipeline runs across multiple factories and create a dashboard that shows success and failure rates over the past 30 days. What is the most efficient approach?

A.Use the Data Factory monitoring UI to view runs for each factory individually.

B.Enable Azure Storage Analytics and query the logs stored in a storage account.

C.Configure diagnostic settings for each Data Factory to send logs to a Log Analytics workspace, then create a workbook using KQL queries.

D.Create alert rules in Azure Monitor for each pipeline failure and aggregate manually.

AnswerC

Correct, centralizes logs and enables cross-factory monitoring with workbooks.

Why this answer

Configuring diagnostic settings for each Data Factory to send logs to a Log Analytics workspace enables querying and visualizing pipeline runs across multiple factories in a single dashboard using Azure Monitor workbooks with KQL queries. Option A is inefficient as it requires viewing each factory individually. Option B is incorrect because Azure Storage Analytics logs storage metrics, not Data Factory pipeline runs.

Option D is inefficient because manual aggregation is not scalable.

Full explanation →

568

MCQmedium

A manufacturing company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data arrives in JSON format with a nested structure. You need to transform the data into a tabular format for downstream analytics using Azure Synapse Pipelines. Which data flow transformation should you use?

A.Aggregate transformation

B.Flatten transformation

C.Window transformation

D.Pivot transformation

AnswerB

The Flatten transformation in mapping data flows unpacks nested arrays into rows.

Why this answer

The Flatten transformation in mapping data flows unpacks nested arrays into rows. Option A is wrong because the Aggregate transformation groups data but does not flatten nested structures. Option C is wrong because the Window transformation calculates aggregated values over a range of rows.

Option D is wrong because the Pivot transformation rotates rows to columns.

Full explanation →

569

Multi-Selecteasy

You are designing a data storage solution for a retail company that needs to store semi-structured IoT sensor data from thousands of devices. The data is ingested in near real-time, and queries will involve filtering by device ID and timestamp. The solution must minimize storage costs while supporting interactive queries. Which TWO Azure data storage options are most appropriate?

Select 2 answers

A.Azure Table Storage

B.Azure Cosmos DB

C.Azure Blob Storage

D.Azure Data Lake Storage Gen2

E.Azure SQL Database

AnswersC, D

Cost-effective for large volumes of semi-structured data.

Why this answer

Azure Blob Storage (C) is correct because it provides a cost-effective, scalable object store for semi-structured IoT data, supporting near real-time ingestion via REST APIs or SDKs and enabling interactive queries through Azure Data Lake Storage Gen2's hierarchical namespace and integration with query engines like Azure Synapse Serverless SQL or PolyBase. Azure Data Lake Storage Gen2 (D) is correct as it builds on Blob Storage with a hierarchical namespace, optimized for analytics workloads and interactive queries using tools like Azure Synapse or Databricks, while minimizing costs through tiered storage and lifecycle management.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its low-latency querying capabilities, overlooking that the question emphasizes minimizing storage costs for large volumes of semi-structured IoT data, where object storage (Blob/Data Lake) is far cheaper and still supports interactive queries via analytics engines.

Full explanation →

570

MCQmedium

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data, and that access is audited. Which approach should you use to control access to the data lake?

A.Use Azure managed identities for all user access

B.Use Azure RBAC roles and ACLs on directories/files, and enable storage analytics logging

C.Use shared access signatures (SAS) with stored access policies

D.Configure a virtual network service endpoint and firewall rules

AnswerB

RBAC provides coarse access, ACLs provide fine-grained, and logging audits access.

Why this answer

Azure RBAC roles provide coarse-grained control (e.g., assigning Storage Blob Data Contributor at the storage account level), while ACLs (POSIX-style) on directories and files enable fine-grained permissions for specific users or groups. Enabling storage analytics logging (specifically the 'StorageRead', 'StorageWrite', and 'StorageDelete' logs) captures all authenticated requests, meeting the auditing requirement. This combination directly addresses the need for both authorized access control and auditability in Azure Data Lake Storage Gen2.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall/service endpoints) or service-level authentication (managed identities) with user-level authorization and auditing, forgetting that Azure Data Lake Storage Gen2 requires a combination of RBAC for coarse control and ACLs for fine-grained permissions, with logging explicitly enabled for audit trails.

How to eliminate wrong answers

Option A is wrong because Azure managed identities are designed for authenticating Azure services (e.g., an Azure Function) to access resources without storing credentials, not for controlling individual user access or auditing user-level actions. Option C is wrong because shared access signatures (SAS) with stored access policies provide time-limited, delegated access but do not support fine-grained user-level permissions (e.g., per-directory or per-file) and are not suitable for auditing individual user access; SAS tokens are typically used for temporary, scoped access rather than persistent user authorization. Option D is wrong because configuring a virtual network service endpoint and firewall rules controls network-level access (i.e., which IPs or VNets can reach the storage account) but does not authenticate or authorize individual users, nor does it provide auditing of user actions.

Full explanation →

571

MCQeasy

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

A.Increase the length of the destination columns in the SQL table to accommodate the source data.

B.Set 'enable identity insert' to true.

C.Use auto-create table option in the copy activity.

D.Enable staging copy to use PolyBase.

AnswerA

Direct fix for truncation error.

Why this answer

The error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists.

Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Full explanation →

572

MCQhard

You are designing a data processing solution in Azure Databricks that uses Unity Catalog. The security team requires that all users authenticate using Microsoft Entra ID and that access to tables is governed by attribute-based access control (ABAC) using table tags. Which feature should you enable?

A.Column-level security masks. [wrong]

B.Dynamic views with user context functions. [wrong]

C.Row-level security filters. [wrong]

D.Table tags with access control lists (ACLs) in Unity Catalog. [CORRECT]

AnswerD

Table tags with ACLs in Unity Catalog provide role-based access control (RBAC), not attribute-based access control (ABAC). ABAC requires dynamic views with user context functions.

Why this answer

In Unity Catalog, attribute-based access control (ABAC) is implemented through table tags combined with access control lists (ACLs). Table tags allow data to be classified, and ACLs can then be configured based on those tags to enforce attribute-based access. This directly satisfies the requirement of using table tags for ABAC.

Option B (dynamic views with user context functions) can provide similar controls but does not inherently leverage table tags and is not the dedicated ABAC feature in Unity Catalog.

Full explanation →

573

MCQmedium

You are a data engineer at a manufacturing company. You need to process sensor data from IoT devices that arrive in real time. The data is sent to Azure Event Hubs. You need to aggregate the data over 5-minute windows and store the results in Azure Data Lake Storage Gen2 in Parquet format. The solution should minimize cost and use serverless components. Which solution should you use?

A.Use Azure Stream Analytics to create a query with a tumbling window of 5 minutes, and output the results to Azure Data Lake Storage Gen2 in Parquet format.

B.Use Azure Databricks with Structured Streaming to read from Event Hubs, aggregate with a sliding window, and write to ADLS Gen2 in Parquet.

C.Use Azure Data Factory with a tumbling window trigger to run a pipeline every 5 minutes that copies data from Event Hubs to ADLS Gen2.

D.Use Azure Functions with an Event Hubs trigger to aggregate data in memory and write to ADLS Gen2.

AnswerA

Serverless, real-time, and cost-effective.

Why this answer

Azure Stream Analytics is a serverless, cost-effective solution for real-time stream processing with windowed aggregations. It can output directly to ADLS Gen2 in Parquet. Option B is wrong because Azure Databricks with Structured Streaming requires a running cluster, which is not serverless and incurs cost.

Option C is wrong because Azure Data Factory is not designed for real-time streaming. Option D is wrong because Azure Functions would require custom code and may not handle large throughput efficiently.

Full explanation →

574

MCQeasy

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline is failing with a 'Gateway is offline' error. What is the most likely cause?

A.The Azure Integration Runtime is being used instead of a Self-Hosted Integration Runtime.

B.The Azure Integration Runtime is not configured to use the correct region.

C.The source SQL Server is not configured to allow remote connections from Azure.

D.The Self-Hosted Integration Runtime is not running or cannot connect to the Azure Data Factory service.

AnswerD

Correct: The SHIR is the bridge between on-premises and cloud; if it's offline, the pipeline cannot access the on-premises SQL Server.

Why this answer

The Self-Hosted Integration Runtime (SHIR) acts as the gateway between on-premises data sources and Azure Data Factory. If the SHIR is not running or cannot communicate with the Azure Data Factory service, the pipeline fails with a 'Gateway is offline' error. Option A is incorrect because using the Azure Integration Runtime for an on-premises source would cause a different error, not 'Gateway is offline'.

Option B is incorrect because region configuration for the Azure Integration Runtime is irrelevant when a SHIR is required. Option C is incorrect because the error relates to the gateway, not to SQL Server remote connection settings.

Full explanation →

575

MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are slow. They have already created statistics on all columns used in WHERE clauses and JOIN predicates. What should they do next to improve query performance?

A.Enable result-set caching.

B.Increase the DWU setting for the dedicated SQL pool.

C.Create additional statistics on all columns.

D.Partition the table on a frequently filtered column.

AnswerD

Partitioning enables partition elimination, reducing the amount of data scanned.

Why this answer

Partitioning on a frequently filtered column enables partition elimination, reducing the amount of data scanned and improving query performance. Option A (result-set caching) caches query results for repeated queries, but this question is about slow queries on a large fact table, not repeated queries. Option B (increasing DWU) might improve performance by providing more resources, but it is a more costly approach and not the best first step; partitioning is a better design optimization.

Option C (creating additional statistics on all columns) is unnecessary because they already have statistics on columns used in WHERE and JOIN; statistics on all columns would waste resources and not help.

Full explanation →

576

MCQmedium

Your organization uses Azure Synapse Analytics serverless SQL pools to query data in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data via the serverless SQL endpoint, while minimizing administrative overhead. What should you use?

A.Enable Microsoft Entra ID authentication and grant users permissions via Azure RBAC on the storage account.

B.Use managed identities for the serverless SQL pool.

C.Use storage account access keys for authentication.

D.Use shared access signatures (SAS) tokens generated for each user.

AnswerA

Microsoft Entra ID pass-through authentication allows users to authenticate with their Azure AD identities, and RBAC controls access to storage, minimizing overhead.

Why this answer

Microsoft Entra ID authentication allows users to authenticate with their existing identities, and Azure RBAC on the storage account provides granular, identity-based access control without managing separate SQL logins or credentials, minimizing administrative overhead. Option B is incorrect because managed identities are designed for service-to-service authentication, not for individual users. Option C is incorrect because storage account access keys provide broad, shared access that does not support per-user identity and auditing.

Option D is incorrect because SAS tokens require token generation and management per user, adding overhead.

Full explanation →

577

MCQhard

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query is slow and you suspect that the file layout is not optimized. You examine the files and find that each file is 50 MB. What should you do to improve query performance?

A.Partition the data into folders by date

B.Compress the files with Gzip

C.Convert the files to CSV format to reduce overhead

D.Merge the small files into larger files of at least 100 MB each

AnswerD

Larger files reduce metadata overhead and improve query performance.

Why this answer

Azure Synapse serverless SQL pool performs best when reading files of at least 100 MB each. Small files (50 MB) cause excessive metadata operations, partition discovery, and I/O overhead, leading to slower queries. Merging them into larger files reduces the number of file open/close operations and improves parallelism efficiency.

Exam trap

The trap here is that candidates often focus on compression or format conversion to improve performance, but the real bottleneck in serverless SQL pool with many small files is the metadata and scheduling overhead, not the data size itself.

How to eliminate wrong answers

Option A is wrong because partitioning by date does not address the core issue of small file size; while partitioning can help with predicate pushdown, it does not reduce the overhead of reading many small files. Option B is wrong because compressing with Gzip can actually degrade performance in serverless SQL pool, as Gzip is not splittable and forces sequential reads, whereas the pool prefers splittable formats like Snappy or uncompressed Parquet. Option C is wrong because converting to CSV format increases overhead due to lack of columnar storage, schema inference, and predicate pushdown, making queries slower than with optimized Parquet files.

Full explanation →

578

MCQhard

You are designing a data lake architecture for a healthcare company. The solution must support fine-grained access control at the file level, encryption at rest and in transit, and integration with Microsoft Purview for data lineage. Which storage solution should you recommend?

A.Azure NetApp Files.

B.Azure Files.

C.Azure Data Lake Storage Gen2 (ADLS Gen2).

D.Azure Blob Storage.

AnswerC

ADLS Gen2 provides ACLs, encryption, and Purview integration.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with POSIX-like access control lists (ACLs) for fine-grained file-level permissions, supports encryption at rest (Azure Storage Service Encryption) and in transit (TLS 1.2+), and natively integrates with Microsoft Purview for automated data lineage and cataloging. This makes it ideal for healthcare scenarios requiring strict compliance and auditability.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with ADLS Gen2, assuming blob storage's container-level permissions are sufficient for file-level control, but the hierarchical namespace and POSIX ACLs are exclusive to ADLS Gen2 and required for the fine-grained access described.

How to eliminate wrong answers

Option A is wrong because Azure NetApp Files provides NFS/SMB file shares with ACLs but lacks native integration with Microsoft Purview for data lineage and is not optimized for large-scale analytics workloads like data lakes. Option B is wrong because Azure Files offers SMB file shares with ACLs but does not support the hierarchical namespace or POSIX ACLs needed for fine-grained file-level control in a data lake, and its Purview integration is limited compared to ADLS Gen2. Option D is wrong because Azure Blob Storage provides encryption and Purview integration but lacks a hierarchical namespace and POSIX ACLs, making it impossible to enforce fine-grained access control at the individual file level.

Full explanation →

579

MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They need to ensure that only users with a specific Azure AD group can query a particular schema. Which approach should they use?

A.Configure a server-level firewall rule to block other users.

B.Use the GRANT statement to grant SELECT on the schema to the Azure AD group.

C.Create a row-level security policy on all tables in the schema.

D.Apply dynamic data masking to the schema.

AnswerB

GRANT schema permission controls access at schema level.

Why this answer

The GRANT statement in Azure Synapse dedicated SQL pool allows you to assign permissions directly to Azure AD groups. By granting SELECT on the schema to the specific Azure AD group, only members of that group can query objects within that schema, meeting the requirement precisely.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or data obfuscation techniques (masking, RLS) with access control, when the correct solution is a straightforward permission grant using T-SQL's GRANT statement.

How to eliminate wrong answers

Option A is wrong because server-level firewall rules control network access to the entire Azure SQL logical server, not granular schema-level access for specific Azure AD groups. Option C is wrong because row-level security (RLS) restricts access to specific rows within tables based on a predicate function, not entire schemas or tables at the schema level. Option D is wrong because dynamic data masking obfuscates sensitive data in query results but does not prevent users from querying the schema or seeing the underlying data with appropriate permissions.

Full explanation →

580

MCQhard

A company has an Azure Data Lake Storage Gen2 account. They want to ensure that only users with the 'Data Reader' role can access files in a specific container, while other users cannot list or read files. The storage account has hierarchical namespace enabled. What is the most secure and manageable approach?

A.Assign the Storage Blob Data Reader role at the storage account level and use row-level security

B.Generate a shared access signature (SAS) token for each user

C.Configure a storage firewall to allow only the Data Reader role's IP addresses

D.Set POSIX-like access control lists (ACLs) on the container folder for the Data Reader role

AnswerD

ACLs provide fine-grained permissions at the file/directory level for specific users/groups.

Why this answer

Azure Data Lake Storage Gen2 with hierarchical namespace enabled supports POSIX-like access control lists (ACLs) at the container and folder level. By setting ACLs on the specific container folder to grant 'Read' and 'Execute' permissions only to the 'Data Reader' role (or its associated security group), you enforce least-privilege access without affecting other containers. This approach is both secure and manageable, as ACLs are inherited by default and can be centrally managed via Azure RBAC integration.

Exam trap

The trap here is that candidates often confuse row-level security (a database concept) with file-level security in Data Lake Storage, or they assume that a storage firewall can filter by user role, when in fact it only filters by network source IP.

How to eliminate wrong answers

Option A is wrong because row-level security (RLS) is a feature of Azure SQL Database and Azure Synapse SQL, not Azure Data Lake Storage Gen2; it cannot be applied to files in a storage container. Option B is wrong because generating a SAS token for each user is not manageable at scale, introduces token management overhead, and does not leverage Azure AD-based role assignments for centralized access control. Option C is wrong because a storage firewall restricts access based on network IP addresses, not user roles; it cannot differentiate between users who have the 'Data Reader' role and those who do not, and it would block all traffic from non-whitelisted IPs regardless of role membership.

Full explanation →

581

MCQeasy

You need to monitor the health of your Azure Data Factory pipelines and set up alerts for failures. Which Azure service should you use to collect and analyze pipeline run logs?

A.Azure Purview

B.Azure Log Analytics

C.Azure Monitor

D.Azure Sentinel

AnswerC

Collects metrics and logs for Azure Data Factory.

Why this answer

Azure Monitor is the primary service for collecting and analyzing pipeline run logs and metrics in Azure Data Factory. Option A is wrong because Azure Purview is a data governance service, not for monitoring. Option B is wrong because Azure Log Analytics is a component of Azure Monitor used for querying logs, but Azure Monitor itself is the service that collects and analyzes the logs.

Option D is wrong because Azure Sentinel is a SIEM (Security Information and Event Management) solution, not for pipeline monitoring.

Full explanation →

582

MCQmedium

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

A.Parquet

B.CSV

C.JSON

D.Avro

AnswerC

Preserves schema flexibility and is easy to store.

Why this answer

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

Exam trap

The trap here is that candidates often choose Parquet or Avro for their performance benefits, forgetting that raw data ingestion with varying schemas must prioritize schema flexibility over query optimization, which JSON uniquely provides.

How to eliminate wrong answers

Option A is wrong because Parquet is a columnar storage format that requires a fixed schema at write time, making it unsuitable for raw data with a varying schema; any schema mismatch would cause ingestion failures or data truncation. Option B is wrong because CSV is a flat, row-oriented format that cannot natively represent nested or hierarchical JSON structures without complex flattening, and it lacks schema flexibility for varying fields. Option D is wrong because Avro is a binary format with a schema embedded in the file, but it still requires a predefined schema for serialization, which conflicts with the requirement of a varying schema from the API.

Full explanation →

583

MCQhard

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

A.The dimension table is not actually replicated due to its size exceeding the replication threshold

B.The query is running with a low resource class

C.Result set caching is enabled

D.Statistics are outdated on the dimension table

AnswerA

If the dimension table is too large to be replicated, it will be distributed and cause shuffle.

Why this answer

The 'ShuffleMove' operation in a Synapse dedicated SQL pool query plan indicates that data is being moved between distributions to complete the join. If the small dimension table is supposed to be replicated but exceeds the replication threshold (default 60 GB compressed), it will not be replicated and instead remains hash-distributed. This forces a shuffle of the large fact table's data to align with the dimension table's distribution, causing significant data movement and slow performance.

Exam trap

The trap here is that candidates often assume a 'ShuffleMove' is always caused by a join key mismatch or poor statistics, but the specific scenario of a dimension table failing to replicate due to size is a common and subtle cause that directly triggers data movement.

How to eliminate wrong answers

Option B is wrong because a low resource class affects concurrency and memory allocation, but it would not introduce a 'ShuffleMove' operation; it would manifest as spilling to tempdb or timeouts. Option C is wrong because result set caching improves performance for repeated queries by storing results, and it does not cause a 'ShuffleMove' operation; in fact, it would reduce the need for shuffles. Option D is wrong because outdated statistics can lead to suboptimal cardinality estimates and poor join choices, but they do not directly cause a 'ShuffleMove' operation; the shuffle is a physical data movement decision based on distribution type, not statistics.

Full explanation →

584

MCQeasy

Your company uses Azure Synapse Analytics dedicated SQL pool to store a fact table with 2 billion rows. You need to improve query performance for a workload that frequently aggregates sales by date and product category. Which distribution and index type should you use?

A.Hash-distribute on product_category and use a clustered columnstore index.

B.Replicate the table and use a clustered index.

C.Round-robin distribution and a clustered columnstore index.

D.Hash-distribute on date and use a clustered index.

AnswerA

Hash distribution on the join/aggregation key improves performance; columnstore is ideal for large data volumes.

Why this answer

Hash-distributing on product_category ensures that rows with the same product category are co-located on the same distribution, enabling local aggregation without data movement. A clustered columnstore index provides high compression and batch-mode processing, which is ideal for large fact tables and analytical workloads that aggregate millions of rows by columns like date and product_category.

Exam trap

The trap here is that candidates often choose round-robin distribution (Option C) thinking it balances data evenly, but they overlook that it causes data shuffling for any aggregation on a non-distribution column, while hash distribution on the grouping column avoids that overhead entirely.

How to eliminate wrong answers

Option B is wrong because replicating a 2-billion-row table is impractical due to storage overhead and the 60-distribution replication limit, and a clustered index lacks the columnar compression and batch-mode execution needed for large-scale aggregation. Option C is wrong because round-robin distribution distributes rows randomly, causing data movement during joins and aggregations, which degrades performance for frequent grouping by product_category. Option D is wrong because hash-distributing on date scatters rows with the same product_category across distributions, forcing expensive shuffle operations during aggregation, and a clustered index is less efficient than columnstore for analytical queries.

Full explanation →

585

MCQeasy

Your team is using Azure Data Factory to orchestrate a data pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully during testing. However, after moving to production, you notice that the pipeline fails intermittently with connectivity errors. You need to ensure reliable data transfer. What should you implement?

A.Use Azure Integration Runtime (IR)

B.Use PolyBase for data transfer

C.Configure Azure VPN Gateway

D.Use Self-Hosted Integration Runtime

AnswerD

Self-hosted IR acts as a gateway connecting ADF to on-premises resources.

Why this answer

A Self-Hosted Integration Runtime provides a dedicated gateway for on-premises connectivity, enabling Azure Data Factory to access on-premises data sources reliably. Option A is wrong because Azure Integration Runtime cannot connect to on-premises networks. Option B is wrong because PolyBase is used for loading data into Azure Synapse Analytics, not for resolving connectivity issues.

Option C is wrong because Azure VPN Gateway is a general VPN solution and does not directly address Data Factory connectivity to on-premises sources.

Full explanation →

586

MCQmedium

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to process CSV files that arrive in a 'raw' container, transform them into Parquet format, and write them to a 'curated' container. The transformation includes filtering out rows with null values in the 'customer_id' column and adding a partition column 'year' based on the 'order_date'. You use Azure Synapse Pipelines. Which activity should you use for the transformation?

A.Stored procedure activity

B.Notebook activity with PySpark

C.Copy data activity

D.Data flow activity

AnswerD

Data flows provide visual transformation with built-in mapping.

Why this answer

The Data Flow activity in Azure Synapse Pipelines is designed for code-free, visual data transformations at scale. It can directly read CSV files from the 'raw' container, filter out rows with null 'customer_id' values using a conditional split or filter transformation, derive a 'year' column from 'order_date' using a derived column transformation, and write the results as Parquet files to the 'curated' container—all without writing code. This makes it the optimal choice for this ETL scenario.

Exam trap

The trap here is that candidates often confuse the Copy data activity's basic mapping capabilities with the full transformation logic needed for filtering and deriving new columns, leading them to choose Option C instead of recognizing that Data Flow is required for row-level and column-level transformations.

How to eliminate wrong answers

Option A is wrong because a Stored Procedure activity is used to execute SQL commands against a database (e.g., Azure SQL Database or Synapse SQL pool), not to perform file-based transformations on Data Lake Storage Gen2. Option B is wrong because a Notebook activity with PySpark can perform the transformation, but it requires writing and maintaining Spark code, which is overkill for a simple filter-and-partition operation that can be done visually with Data Flow; the question implies a preference for pipeline-native activities. Option C is wrong because the Copy data activity only copies data from source to sink with optional schema mapping and basic column mappings, but it cannot perform row-level filtering (e.g., removing nulls) or derive new columns like 'year' from 'order_date'—it lacks transformation logic.

Full explanation →

587

MCQhard

You are designing a data storage solution for an Azure Data Lake Storage Gen2 account that will store sensitive customer data. The solution must enforce that all data is encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, you need to prevent data from being accessed by any Azure service except Azure Synapse Analytics. Which combination of configurations should you implement?

A.Enable Azure Storage encryption with customer-managed keys stored in Azure Key Vault and configure a firewall with a service endpoint for Azure Synapse Analytics

B.Enable Azure Storage encryption with customer-managed keys and use a shared access signature (SAS) token for Azure Synapse Analytics

C.Enable Azure Storage encryption with customer-managed keys and assign an Azure Policy denying public network access

D.Enable Azure Storage encryption with Microsoft-managed keys and configure a private endpoint

AnswerA

This combination provides CMK encryption and restricts network access to Azure Synapse Analytics only.

Why this answer

It combines two essential controls: Azure Storage encryption with customer-managed keys (CMK) stored in Azure Key Vault ensures data is encrypted at rest using keys you control, and a firewall with a service endpoint for Azure Synapse Analytics restricts network access so that only traffic from Azure Synapse Analytics can reach the storage account. This meets both the encryption and access restriction requirements.

Exam trap

The trap here is that candidates often confuse service endpoints with private endpoints or SAS tokens, thinking any network restriction or key management approach will suffice, but the question specifically requires both CMK and service-specific access control, which only a service endpoint for Azure Synapse Analytics combined with a firewall provides.

How to eliminate wrong answers

Option B is wrong because a shared access signature (SAS) token grants time-limited, delegated access to specific resources but does not restrict access to only Azure Synapse Analytics; it can be used by any client with the token, and it does not enforce network-level isolation. Option C is wrong because assigning an Azure Policy denying public network access only blocks public endpoints but does not explicitly allow only Azure Synapse Analytics; it would block all access unless a private endpoint or service endpoint is configured, and it does not address the encryption requirement (CMK is already enabled, but the policy alone does not enforce the service-specific restriction). Option D is wrong because it uses Microsoft-managed keys instead of customer-managed keys, which fails the explicit requirement for CMK stored in Azure Key Vault, even though a private endpoint provides network isolation.

Full explanation →

588

Multi-Selectmedium

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 as Parquet files partitioned by date. Which TWO of the following statements are true regarding querying this data? (Select TWO.)

Select 2 answers

A.You can use the filepath() function in the query to retrieve the partition column values.

B.Partition elimination is automatically applied when filtering on the partition column in the WHERE clause.

C.You must create an external table to query Parquet files; OPENROWSET is not supported.

D.You can create indexes on the serverless SQL pool to improve query performance.

E.You can only query a single file at a time; wildcards are not supported.

AnswersA, B

The filepath function returns the partition path values.

Why this answer

The `filepath()` function in a serverless SQL pool query returns the file path of the row being read. When data is partitioned by date in Azure Data Lake Storage Gen2, the partition column values are embedded in the folder structure (e.g., `/year=2023/month=01/day=15/`). Using `filepath(1)`, `filepath(2)`, etc., you can extract these values directly in the query without needing to parse the path manually.

Exam trap

The trap here is that candidates often assume serverless SQL pools behave like dedicated SQL pools, leading them to think indexes are needed or that external tables are mandatory, when in fact serverless pools are schema-on-read and rely on file metadata and statistics for performance.

Full explanation →

589

MCQmedium

You are designing a data storage solution for a retail company that needs to store semi-structured JSON data from IoT sensors. The data is ingested continuously and must support both real-time analytics and batch processing. Which Azure data store should you recommend?

A.Azure Data Lake Storage Gen2

B.Azure Blob Storage

C.Azure SQL Database

D.Azure Cosmos DB

AnswerA

ADLS Gen2 combines Blob Storage with a hierarchical namespace and is designed for big data analytics.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical file system with the scalability and low cost of Azure Blob Storage, making it ideal for storing semi-structured JSON data from IoT sensors. It supports both real-time analytics (via services like Azure Stream Analytics or Apache Spark) and batch processing (via tools like Azure Data Factory or PolyBase) without data movement, and it natively handles JSON files with schema-on-read capabilities.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB (Option D) because it natively supports JSON and real-time access, but they overlook the requirement for batch processing and cost-effective storage at scale, which ADLS Gen2 is designed for as a data lake solution.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because while it can store JSON data, it lacks a hierarchical namespace, making it less efficient for directory-based operations and batch processing patterns that require folder-level security or rename operations, which ADLS Gen2 provides. Option C (Azure SQL Database) is wrong because it is a relational database optimized for structured data and transactional workloads, not for storing semi-structured JSON at scale with schema-on-read; it would require schema definition and indexing, adding latency and cost for high-volume IoT ingestion. Option D (Azure Cosmos DB) is wrong because it is a NoSQL database designed for low-latency, transactional access to semi-structured data, but it is not optimized for batch processing or large-scale analytical queries on raw JSON files; it is better suited for operational workloads rather than the combined real-time and batch analytics scenario described.

Full explanation →

590

Multi-Selectmedium

Which THREE considerations should be evaluated when designing a partitioning strategy for a large fact table in Azure Synapse Dedicated SQL Pool?

Select 3 answers

A.Align partition boundaries with distribution key

B.Use hash distribution for all fact tables

C.Ensure partition elimination is possible in queries

D.Limit the number of partitions to fewer than 60

E.Choose a partition column with high cardinality

AnswersA, C, D

Prevents data movement.

Why this answer

Aligning partition boundaries with the distribution key in Azure Synapse Dedicated SQL Pool ensures that data movement during partition switching or merging is minimized, as each distribution contains its own set of partitions. This alignment avoids cross-distribution data transfers, which can significantly improve performance and maintainability of large fact tables.

Exam trap

The trap here is that candidates often confuse partition column cardinality with distribution key cardinality, assuming high cardinality is always beneficial, but for partitioning, low cardinality is required to avoid creating too many small partitions that hurt performance.

Full explanation →

591

MCQhard

A healthcare company stores patient records in Azure Blob Storage. The compliance team requires that all data be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, the storage account must be accessible only from a specific virtual network (VNet) and must support versioning to protect against accidental deletion. The storage account is currently using Microsoft-managed keys and has public network access enabled. You need to implement the required changes with minimal downtime. Which course of action should you take?

A.Create a new storage account in the same region with infrastructure encryption enabled and customer-managed keys configured. Enable versioning and VNet access. Use AzCopy to copy data from the old account to the new one. Update applications to use the new storage account. Delete the old account.

B.Modify the existing storage account to use customer-managed keys by updating the encryption settings in the Azure portal. Then, enable versioning and configure firewall rules to allow access from the VNet.

C.Delete the existing storage account and recreate it with the same name, enabling customer-managed keys, versioning, and VNet access. Restore data from backups.

D.Create a new storage account with the same name in a different region. Enable infrastructure encryption and customer-managed keys. Use Azure Data Factory to copy data from the old account to the new one. Then, delete the old account and update applications to point to the new account.

AnswerA

This meets all requirements. The new account is created with CMK support, versioning, and VNet access. Data is migrated with AzCopy, and applications are updated.

Why this answer

Creating a new storage account with infrastructure encryption and customer-managed keys (CMK) is the only way to enable infrastructure encryption, which cannot be enabled on an existing account. Using AzCopy ensures minimal downtime by copying data in the background while applications continue to use the old account until the cutover. Enabling versioning and VNet access on the new account meets all compliance requirements without disrupting the existing environment.

Exam trap

The trap here is that candidates assume infrastructure encryption can be enabled on an existing storage account, but it is a creation-time-only setting, making a new account the only viable path to meet the compliance requirement with minimal downtime.

How to eliminate wrong answers

Option B is wrong because you cannot enable infrastructure encryption on an existing storage account; it must be set at creation time. Additionally, changing encryption settings from Microsoft-managed to customer-managed keys on an existing account does not enable infrastructure encryption, which is required by the compliance team. Option C is wrong because deleting and recreating the account with the same name causes significant downtime, and restoring from backups is not a minimal-downtime approach; also, infrastructure encryption cannot be enabled on a recreated account with the same name if the original was created without it.

Option D is wrong because creating the new account in a different region violates the requirement to keep data in the same region, and Azure Data Factory is not the optimal tool for a one-time bulk copy; AzCopy is more efficient for this scenario.

Full explanation →

592

MCQmedium

A company uses Azure Synapse Analytics with a dedicated SQL pool. They need to ensure that a team of data scientists can query all tables in the 'sales' schema but cannot modify any data or schema objects. Which role should the team be assigned?

A.db_owner

B.db_datareader

C.db_ddladmin

D.db_datawriter

AnswerB

db_datareader grants read access to all tables.

Why this answer

The `db_datareader` role grants read-only access to all user tables in a database, allowing the team to query all tables in the 'sales' schema without the ability to modify data or schema objects. This aligns perfectly with the requirement for data scientists to perform SELECT queries only.

Exam trap

The trap here is that candidates often confuse `db_datareader` with `db_datawriter` or assume `db_ddladmin` is required for querying, not realizing that read-only access is specifically granted by `db_datareader` without any write or schema modification capabilities.

How to eliminate wrong answers

Option A is wrong because `db_owner` provides full control over the database, including the ability to modify data and schema, which violates the requirement. Option C is wrong because `db_ddladmin` allows execution of Data Definition Language (DDL) commands like CREATE, ALTER, and DROP, enabling schema modifications. Option D is wrong because `db_datawriter` grants INSERT, UPDATE, and DELETE permissions, allowing data modification.

Full explanation →

593

MCQhard

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected due to excessive data movement operations. You need to minimize data movement without changing the distribution columns. Which table design approach should you recommend?

A.Use replicated tables for small dimension tables

B.Use round-robin distribution for dimension tables

C.Use hash distribution for all tables

D.Use partitioning on join columns

AnswerA

Replicated tables store a full copy on each node, eliminating data shuffling for joins.

Why this answer

Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables.

Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.

Full explanation →

594

MCQmedium

Your company is building a real-time analytics solution for monitoring manufacturing equipment. Sensors send JSON data every second to an Azure Event Hubs instance. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and hour. You use Azure Stream Analytics to read from Event Hubs and write to ADLS Gen2. Currently, the output is writing many small Parquet files (under 1 MB each), which is causing performance issues when reading the data. You need to optimize the output to produce fewer, larger files while maintaining low latency. What should you do?

A.Partition the output by minute instead of hour to distribute data more

B.Change the output format to Avro to improve compression

C.Increase the 'Maximum events per batch' setting in the Stream Analytics output to ADLS Gen2

D.Decrease the 'Maximum events per batch' setting to reduce latency

AnswerC

Buffering more events per batch produces larger files.

Why this answer

Increasing the 'Maximum events per batch' setting in the Stream Analytics output to ADLS Gen2 allows more events to be accumulated before writing a file, resulting in fewer, larger Parquet files. This directly addresses the small-file problem while maintaining low latency, as the batching is time-bound and does not introduce excessive delay.

Exam trap

The trap here is that candidates may confuse partitioning granularity with file sizing, incorrectly assuming finer partitioning (Option A) or format changes (Option B) will solve the small-file problem, when the actual solution is to adjust the batching threshold in the output sink.

How to eliminate wrong answers

Option A is wrong because partitioning by minute instead of hour would create even more partitions, leading to more small files and worsening the performance issue. Option B is wrong because changing the output format to Avro does not inherently reduce the number of files; it only changes the serialization format, and Avro may not provide better compression than Parquet for this scenario. Option D is wrong because decreasing the 'Maximum events per batch' setting would cause more frequent writes with fewer events, increasing the number of small files and degrading read performance.

Full explanation →

595

MCQmedium

You are reviewing a Spark job definition in Azure Synapse Analytics. The job aggregates sales data. The job runs successfully but takes longer than expected. You notice that dynamic allocation is disabled and the executor instances are fixed at 10. The cluster has a maximum of 20 nodes. What is the most likely reason for the slow performance?

A.The file path is incorrect, causing data read errors.

B.The job cannot scale out beyond 10 executors because dynamic allocation is disabled.

C.The job is not parallelized because of a single partition.

D.The executor memory is too low for the aggregation.

AnswerB

With dynamic allocation off, the job is limited to 10 executors.

Why this answer

With dynamic allocation disabled and executor instances fixed at 10, the Spark job cannot utilize additional cluster resources even though the cluster supports up to 20 nodes. This means the job is artificially constrained to 10 executors, limiting parallelism and causing slower performance despite available compute capacity.

Exam trap

The trap here is that candidates may overlook the explicit configuration detail (dynamic allocation disabled, fixed 10 executors) and instead focus on generic performance issues like memory or partitioning, missing the direct scaling limitation.

How to eliminate wrong answers

Option A is wrong because an incorrect file path would cause job failures or data read errors, not simply slower performance; the job runs successfully. Option B is wrong because it is actually the correct answer. Option C is wrong because a single partition would cause extreme underutilization and likely very slow processing, but the question states the job aggregates sales data and runs successfully, implying some parallelism exists; the fixed executor count is the more direct bottleneck.

Option D is wrong because while low executor memory can cause spilling to disk and slowdowns, the question specifically highlights disabled dynamic allocation and fixed executors as the observed configuration, making insufficient scaling the primary issue.

Full explanation →

596

Multi-Selectmedium

You are designing a data transformation pipeline using Azure Databricks. The pipeline reads from Azure Data Lake Storage Gen2, performs aggregations, and writes to a Synapse dedicated SQL pool. Which three configurations should you implement to optimize performance and minimize cost? (Choose three.)

Select 3 answers

A.Enable Delta Lake on the storage account

B.Use Auto Optimize and Optimized Writes on Delta tables

C.Enable Photon engine for Spark SQL operations

D.Use a single-node cluster to reduce cost

E.Disable autoscaling to avoid cost variability

F.Use default Spark shuffle partitions (200)

AnswersA, B, C

Why this answer

Enabling Delta Lake on the storage account allows you to use Delta tables, which provide ACID transactions, scalable metadata handling, and unified batch/streaming capabilities. This is essential for reliable and performant data transformations in Azure Databricks, especially when reading from ADLS Gen2 and writing to Synapse.

Exam trap

The trap here is that candidates often assume cost savings come from reducing cluster size (single-node) or disabling autoscaling, but in practice these choices hurt performance and can increase total cost due to longer runtimes and resource contention.

Why the other options are wrong

Single-node cluster cannot handle large data volumes efficiently.

Autoscaling helps minimize cost by scaling down during idle periods.

Default may not be optimal; tuning shuffle partitions is recommended.

Full explanation →

597

MCQhard

A data engineering team uses Azure Data Factory to load data from Azure SQL Database to Azure Data Lake Storage Gen2. They notice that the pipeline runs fail intermittently due to transient errors. They need to implement a retry policy with exponential backoff. What is the most efficient way to achieve this?

A.Use a 'Validation' activity before the copy to check source availability

B.Create a custom .NET activity to handle retries

C.Add a 'Until' loop with a wait activity in the pipeline

D.Configure the 'Retry' property on the copy activity with a count and exponential backoff interval

AnswerD

Built-in retry with exponential backoff.

Why this answer

Azure Data Factory natively supports configuring a 'Retry' property on activities, including Copy activities, with an exponential backoff interval. This built-in mechanism automatically retries the activity upon transient failures without requiring custom logic, making it the most efficient and maintainable approach for handling intermittent errors.

Exam trap

The trap here is that candidates may overcomplicate the solution by choosing a custom loop or validation activity, overlooking that Azure Data Factory's native 'Retry' property with exponential backoff is the simplest and most efficient built-in mechanism for handling transient errors.

How to eliminate wrong answers

Option A is wrong because a 'Validation' activity only checks source availability before the copy starts; it does not retry the copy operation itself if a transient error occurs during data transfer. Option B is wrong because creating a custom .NET activity introduces unnecessary complexity, development overhead, and maintenance burden when Azure Data Factory already provides a native retry feature. Option C is wrong because an 'Until' loop with a wait activity requires manual implementation of retry logic and exponential backoff, which is less efficient and more error-prone than using the built-in 'Retry' property.

Full explanation →

598

MCQmedium

You are using Azure Purview to scan an Azure Data Lake Storage Gen2 account. After scanning, you notice that some files are not classified. What is the most likely reason?

A.The storage account is not registered in Purview

B.The files are in Parquet format

C.The classification rules are disabled

D.The file types are not included in the scan rule set

AnswerD

Default rule sets may not include all file types.

Why this answer

Purview uses scan rule sets to determine which file types to scan and apply classifications. If the file type is not included in the scan rule set, those files will be skipped during scanning, leading to no classification. Option A is incorrect because if the storage account were not registered, no files would be scanned at all.

Option B is incorrect because Parquet files are supported and can be classified. Option C is incorrect because if classification rules were disabled, no files would be classified, not just some.

Full explanation →

599

Multi-Selecteasy

Which TWO actions help optimize data storage costs in Azure Data Lake Storage Gen2?

Select 2 answers

A.Enable soft delete for blobs.

B.Enable geo-redundant storage (GRS) for the storage account.

C.Configure lifecycle management policies to move data to cool or archive tiers.

D.Enable encryption at rest using customer-managed keys.

E.Use locally redundant storage (LRS) for temporary data.

AnswersC, E

Lifecycle policies reduce cost by tiering infrequently accessed data.

Why this answer

Azure Data Lake Storage Gen2 supports lifecycle management policies that automatically transition data to cooler tiers (cool or archive) based on age or usage patterns. Moving infrequently accessed data to lower-cost tiers directly reduces storage costs without manual intervention.

Exam trap

The trap here is that candidates often confuse cost-optimization features (like tiering) with data protection or security features (like soft delete, GRS, or encryption), which serve different purposes and may actually increase costs.

Full explanation →

600

MCQmedium

You are monitoring an Azure Data Factory pipeline that runs every hour. The pipeline uses a Copy activity to copy data from Azure SQL Database to Azure Blob Storage. Recently, the pipeline has been failing with a 'Timeout' error. The source SQL database has a large number of records. What should you do to resolve the timeout?

A.Enable staging and use PolyBase or COPY statement for the copy activity.

B.Decrease the 'writeBatchSize' to 1000.

C.Increase the 'timeout' value in the copy activity settings.

D.Use a query with 'queryTimeout' set to 7200 seconds.

AnswerA

Staging with PolyBase/COPY allows data to be copied in parallel and avoids timeouts.

Why this answer

Enabling staging with PolyBase or the COPY statement offloads the data transfer to Azure Data Lake or Blob Storage, bypassing the bottleneck of the Copy activity's default data movement. This approach is specifically designed for large-scale data loads from Azure SQL Database, as it uses the database's bulk export capabilities and avoids the timeout by not relying on the Copy activity's internal query execution.

Exam trap

The trap here is that candidates often assume increasing timeouts (options C or D) will fix the issue, but the real problem is the Copy activity's default command timeout limitation, which requires a fundamentally different data movement approach like staging with PolyBase or COPY statement.

How to eliminate wrong answers

Option B is wrong because decreasing 'writeBatchSize' to 1000 reduces the number of rows written per batch to the sink, which does not address the source-side timeout; the timeout occurs during the data read from Azure SQL Database, not during the write to Blob Storage. Option C is wrong because increasing the 'timeout' value in the copy activity settings only extends the overall activity duration but does not resolve the underlying issue of the source query exceeding the default command timeout (typically 30 seconds) when reading a large number of records. Option D is wrong because setting 'queryTimeout' to 7200 seconds in a query only applies to the query execution on the source database, but the Copy activity's default command timeout for the source dataset is separate and still limited; moreover, the real solution is to use a bulk export mechanism like PolyBase or COPY statement, not just extending the query timeout.

Full explanation →

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 526–600