Courseiva

CCNA Develop Data Processing Questions

75 of 261 questions · Page 2/4 · Develop Data Processing topic · Answers revealed

Practice these questionsExam hubAll questions
76
MCQmedium

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

A.Use the Azure Databricks dbutils.fs.mv() to move the file.
B.Use Azure Data Factory with a Copy activity to move the file, then delete the source.
C.Change the file's metadata to update its directory path.
D.Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.
AnswerA

The move operation in ADLS Gen2 is a metadata rename, which is instantaneous.

Why this answer

`dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Exam trap

The trap here is that candidates often assume moving large files requires copying, but Azure Data Lake Storage Gen2's hierarchical namespace enables instant metadata-only renames, making `dbutils.fs.mv()` the optimal choice.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory Copy activity physically copies the file data across folders, which is slower and incurs additional read/write costs, even though it can delete the source afterward. Option C is wrong because Azure Data Lake Storage Gen2 does not support moving files by changing metadata; directory paths are part of the file's hierarchical namespace and cannot be updated via metadata alone. Option D is wrong because `dbutils.fs.cp()` performs a full data copy, which is time-consuming for large files, and then requires an explicit delete, adding unnecessary overhead compared to a rename.

Practice this question →
77
MCQhard

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

A.Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.
B.Create a non-clustered index on OrderDate.
C.Add ProductKey as a distribution column alongside the existing hash.
D.Change the distribution to round-robin to spread data evenly.
AnswerA

Partition elimination reduces the data scanned by skipping partitions not matching the filter.

Why this answer

Partitioning the Sales table on OrderDate enables partition elimination, which allows the query optimizer to scan only the relevant partitions when filtering by date ranges. This reduces I/O and improves performance significantly, as the fact table is already hash-distributed on ProductKey, and partitioning on a different column used in WHERE clauses is a best practice in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often confuse indexing strategies from traditional SQL Server with Synapse dedicated SQL pool limitations, assuming non-clustered indexes are available, or they think partitioning must match the distribution column, when in fact they serve different purposes.

How to eliminate wrong answers

Option B is wrong because non-clustered indexes are not supported on dedicated SQL pool tables; only clustered columnstore indexes and clustered indexes are allowed, and adding a non-clustered index would fail or be ignored. Option C is wrong because a table can have only one distribution column (hash-distributed), and adding ProductKey as an additional distribution column is not possible; the table is already hash-distributed on ProductKey, which is fine for joins but does not help date-range filtering. Option D is wrong because changing to round-robin distribution would eliminate hash-based join benefits and lead to data movement during queries, worsening performance for typical fact table workloads.

Practice this question →
78
MCQhard

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

A.The join with the Products table forces a shuffle that bypasses the materialized result
B.The query uses summarize, which already materializes results internally
C.The datetime range filter is not sargable, causing full table scan
D.The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead
AnswerD

materialize() caches the result; if used once, caching is wasted.

Why this answer

Materialize() only provides performance benefit when the materialized result is referenced multiple times. In this query, the materialized result is used only once, so the overhead of materialization (storing the result in memory) outweighs any benefit, potentially making the query slower. Option A is incorrect because there is no join with a Products table; the query uses a single table.

Option B is incorrect because summarize does not inherently materialize results; it computes aggregations on the fly. Option C is incorrect because datetime range filters in Kusto are sargable and do not cause full table scans.

Practice this question →
79
Multi-Selecteasy

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Select 2 answers
A.Use coalesce() to reduce the number of partitions without a shuffle.
B.Enable caching on the DataFrame before writing.
C.Apply bucketing on a column to group data.
D.Increase the number of partitions using repartition() with a larger number.
E.Use repartition() with a smaller number of partitions.
AnswersA, E

Coalesce reduces partitions and thus output files, minimizing shuffle.

Why this answer

`coalesce()` reduces the number of partitions without triggering a full shuffle, which minimizes the number of output files while preserving performance. Since the DataFrame already has 100 million rows and 20 columns, coalescing to fewer partitions directly reduces the number of files written, addressing the small-file problem efficiently.

Exam trap

The trap here is that candidates often confuse `coalesce()` with `repartition()`, assuming both cause a shuffle, or they mistakenly think increasing partitions (Option D) will improve performance when it actually exacerbates the small-file issue.

Practice this question →
80
MCQmedium

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

A.Use Azure Functions to process each file and write to Synapse via REST API
B.Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation
C.Use Azure Databricks with Python notebooks to process the files and write to Synapse
D.Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool
AnswerD

Mapping Data Flows provide a visual interface for transformations, are serverless, and have rich monitoring via ADF.

Why this answer

Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Exam trap

The trap here is that candidates often overestimate the simplicity of Azure Functions for batch ETL or assume PolyBase alone handles transformations, when in fact ADF Mapping Data Flows are purpose-built for visual, scalable, and monitorable ETL with minimal overhead.

How to eliminate wrong answers

Option A is wrong because Azure Functions are stateless, event-driven compute units that lack native connectors for SFTP and Synapse, requiring custom code for file parsing, state management, and batch loading, which increases administrative overhead and complexity. Option B is wrong because PolyBase external tables can only load raw data into staging tables, but the transformation logic (cleaning, validation, aggregation) would need to be implemented in T-SQL stored procedures, which are harder to monitor, scale, and maintain compared to a visual ETL tool like ADF. Option C is wrong because Azure Databricks with Python notebooks introduces significant administrative overhead for cluster management, notebook orchestration, and monitoring, and requires more specialized skills than ADF’s low-code Mapping Data Flows, making it less suitable for minimizing overhead.

Practice this question →
81
MCQhard

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

A.Parquet files require special permissions that are not granted by RBAC roles
B.The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'
C.The storage account firewall does not have a 'Resource instances' exception for the managed identity
D.The notebook is using an incorrect connection string with account key
AnswerC

Firewall rules require explicit addition of the managed identity as a resource instance to allow access when the firewall is enabled.

Why this answer

When a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Exam trap

The trap here is that candidates assume 'Allow Azure services on the trusted services list' automatically includes all Azure resources with managed identities, but it only covers specific Azure platform services, not custom managed identities from services like Synapse.

How to eliminate wrong answers

Option A is wrong because Parquet files do not require special permissions beyond what RBAC roles provide; RBAC roles like 'Storage Blob Data Contributor' grant sufficient permissions to read and write Parquet files. Option B is wrong because 'Storage Blob Data Contributor' already includes all permissions of 'Storage Blob Data Reader' (read, write, delete), so adding the reader role is redundant and not the cause of the failure. Option D is wrong because the notebook uses a managed identity for authentication, not a connection string with an account key; the error is about permission denied, not an incorrect connection string or key.

Practice this question →
82
Multi-Selectmedium

You are implementing a data processing solution using Azure Data Factory. You have a pipeline that copies data from Azure Blob Storage to Azure Data Lake Storage Gen2. You need to ensure that the copy activity uses managed identity for authentication and that the data is transferred securely. Which TWO configurations should you apply?

Select 2 answers
A.Set the copy activity's 'enableDataIntegrityValidation' to true.
B.Set the copy activity's 'UseManagedIdentity' property to true.
C.Configure the source and sink linked services to use managed identity authentication.
D.Set the integration runtime to 'AutoResolveIntegrationRuntime' to ensure data stays within Azure.
E.Enable staged copy with a temporary blob store to improve security.
AnswersC, D

Linked services support managed identity for authentication.

Why this answer

Options C and D are correct. Option C is correct because managed identity authentication is configured in the linked service, not in the copy activity itself. Option D is correct because setting the integration runtime to 'AutoResolveIntegrationRuntime' uses Azure's network, ensuring data transfer remains within Azure and does not go over the public internet if using managed virtual network.

Option A is wrong because 'enableDataIntegrityValidation' is for integrity checks, not authentication. Option B is wrong because the copy activity does not have a 'UseManagedIdentity' property; managed identity is configured in the linked service. Option E is wrong because staging is not required for secure transfer; it's used for performance or polybase.

Practice this question →
83
Multi-Selecteasy

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Select 2 answers
A.Power BI Dataset
B.Azure DevOps
C.Amazon S3
D.Azure Blob Storage
E.Azure Analysis Services
AnswersC, D

Amazon S3 is supported via the Amazon S3 connector.

Why this answer

Amazon S3 is a supported source for Azure Data Factory Copy activity because ADF includes a built-in Amazon S3 connector that allows data ingestion from S3 into Azure. This connector uses the S3 REST API to read objects, supporting both public and private buckets via access keys or IAM roles.

Exam trap

The trap here is that candidates often confuse Azure Analysis Services (a semantic model) with Azure SQL Database or Azure Synapse, which are valid sources, leading them to incorrectly select it as a supported source for Copy activity.

Practice this question →
84
MCQmedium

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

A.Statistics on the table are outdated.
B.The table is not properly partitioned.
C.Data compression is not working efficiently.
D.Data is unevenly distributed across distributions, causing some distributions to be overloaded.
AnswerD

Duplicate values in hash column cause skew.

Why this answer

D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Exam trap

The trap here is that candidates often confuse distribution skew with partitioning or statistics issues, but the key clue is the mention of 'many duplicate values' in the hash-distributed column, which directly points to data skew as the root cause.

How to eliminate wrong answers

Option A is wrong because outdated statistics can cause suboptimal query plans, but the primary issue described is data skew due to hash distribution on a column with many duplicates, not statistics freshness. Option B is wrong because partitioning is a separate concept from distribution; while partitioning can help with partition elimination, it does not address the fundamental data skew caused by hash distribution on a high-duplicate column. Option C is wrong because data compression efficiency is affected by data patterns and storage, not directly by distribution skew; compression works at the page level and is not the root cause of query performance degradation from uneven distribution.

Practice this question →
85
MCQeasy

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

A.A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.
B.The Azure Blob Storage account has been moved to a different subscription.
C.The self-hosted integration runtime node has been de-registered from Azure Data Factory.
D.The blob container has reached its maximum capacity.
AnswerA

Network security updates often change firewall rules, blocking the IR.

Why this answer

The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Exam trap

The trap here is that candidates may confuse a source-side connectivity failure with a sink-side or authentication issue, but the question explicitly states a 'connection error to the on-premises SQL Server,' which points directly to network or firewall blocking at the source, not to storage capacity or SHIR registration status.

How to eliminate wrong answers

Option B is wrong because moving an Azure Blob Storage account to a different subscription does not affect the connectivity between the on-premises SHIR and the on-premises SQL Server; it would only change the storage account's resource ID and require updating linked service credentials, not cause a connection error to SQL Server. Option C is wrong because if the SHIR node were de-registered, the pipeline would fail with an integration runtime not found error, not a connection error to the on-premises SQL Server; the error message would reference the SHIR status, not a network-level timeout or refused connection. Option D is wrong because a blob container reaching maximum capacity (5 TB per container) would result in a storage write error (e.g., 403 or 409) when copying data, not a connection error to the on-premises SQL Server; the error would occur at the sink, not the source.

Practice this question →
86
MCQmedium

Your organization uses Azure Synapse Analytics to run large-scale ETL jobs. A pipeline that loads data from Azure Data Lake Storage Gen2 into a dedicated SQL pool is failing with 'Out of memory' errors during the staging step. The source data is 500 GB in size, and the SQL pool is currently set to DW500c. What should you do to resolve the issue without changing the pipeline logic?

A.Use PolyBase instead of the Copy activity.
B.Switch the source to Azure Data Lake Storage Gen1.
C.Increase the DWU of the dedicated SQL pool to a higher tier (e.g., DW1000c).
D.Partition the source data into smaller files.
AnswerC

Higher DWU provides more memory per distribution, resolving OOM errors.

Why this answer

The 'Out of memory' error during the staging step indicates that the dedicated SQL pool lacks sufficient memory resources to handle the 500 GB data load within the current DWU setting. Increasing the DWU (Data Warehouse Units) from DW500c to a higher tier, such as DW1000c, scales up the memory and compute resources available to the pool, resolving the memory pressure without altering the pipeline logic or source data.

Exam trap

The trap here is that candidates may assume partitioning the source data is a 'logical' fix without realizing it requires pipeline logic changes, or they may confuse PolyBase as a memory-saving alternative rather than a loading protocol that still relies on the pool's resources.

How to eliminate wrong answers

Option A is wrong because PolyBase is not a separate activity but a technology used within the Copy activity or other loading methods; switching to PolyBase does not inherently increase memory and would still fail under the same resource constraints. Option B is wrong because switching the source to Azure Data Lake Storage Gen1 does not affect the memory allocation of the dedicated SQL pool and introduces no performance benefit for the staging step. Option D is wrong because partitioning the source data into smaller files would require changing the pipeline logic (e.g., adding loops or multiple activities) to process files sequentially, which violates the constraint of not changing pipeline logic.

Practice this question →
87
MCQhard

Refer to the exhibit. The pipeline fails with a 'Type mismatch' error. The source file has a column 'Name' of type String, and the destination table expects 'FullName' of type String. What is the most likely cause of the failure?

A.The recursive setting on the source store is causing duplicate reads.
B.The source file does not contain a column named 'Name'.
C.The source file has leading/trailing whitespace in the column names.
D.The sink column 'FullName' is expecting a different data type than String.
AnswerB

The column mapping references a source column 'Name', but if the actual file has a different column header, the copy activity fails with type mismatch.

Why this answer

The pipeline fails with a 'Type mismatch' error because the source file does not contain a column named 'Name'. When the source dataset is configured to read a 'Name' column but the actual file lacks that column, Azure Data Factory or Synapse Pipelines cannot map it to the sink column 'FullName', resulting in a type mismatch error. The error message is misleading because the mismatch is not about data types but about missing source columns, which the service treats as a type incompatibility.

Exam trap

The trap here is that candidates assume 'Type mismatch' always refers to incompatible data types (e.g., String vs. Int), when in fact it can also be triggered by a missing source column that the pipeline expects to map, causing a schema-level mismatch.

How to eliminate wrong answers

Option A is wrong because the recursive setting on the source store controls whether subfolders are included in the read operation; it does not cause duplicate reads that would lead to a type mismatch error. Option C is wrong because leading/trailing whitespace in column names would cause a different error (e.g., column not found) but not a 'Type mismatch' error, and the pipeline would still attempt to map the column if the name matched after trimming. Option D is wrong because the sink column 'FullName' is of type String, matching the source column 'Name' type String, so there is no data type mismatch between the two; the error arises from the missing source column, not from incompatible types.

Practice this question →
88
MCQhard

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

A.Increase the service level (DWU) of the dedicated SQL pool.
B.Change the file format from CSV to Avro.
C.Combine the CSV files into fewer, larger files before loading.
D.Use Azure Data Factory to stage the data in Azure Blob Storage before loading.
AnswerC

PolyBase performs more efficiently with fewer, larger files due to reduced metadata operations.

Why this answer

Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Exam trap

The trap here is that candidates assume scaling up (DWU) or changing file formats always improves performance, but the exam specifically tests the understanding that PolyBase's parallel processing is most efficient when file sizes align with distribution boundaries, making file consolidation the most effective optimization.

How to eliminate wrong answers

Option A is wrong because increasing DWU scales resources but does not address the root cause of slow PolyBase loads—file fragmentation and metadata overhead—and may incur unnecessary cost. Option B is wrong because changing to Avro improves compression and schema evolution but does not reduce the number of file operations; the performance gain from file count reduction is more direct. Option D is wrong because staging in Azure Blob Storage adds an extra copy step without reducing the number of files PolyBase must process; the bottleneck remains the file enumeration and split overhead.

Practice this question →
89
MCQhard

You are designing a data processing solution for a financial services company. They need to process sensitive customer data in Azure Databricks while complying with GDPR. The data must be encrypted at rest and in transit, and access must be audited. You need to recommend a configuration that meets these requirements. Which combination of actions should you take?

A.Enable Azure Storage Service Encryption (SSE) with platform-managed keys and configure Azure Databricks to use a VNet injection.
B.Apply an Azure Policy to require encryption and assign a built-in GDPR blueprint.
C.Use Azure Security Center to monitor for data exposure and enable Azure Defender for Storage.
D.Enable double encryption using Azure Key Vault with customer-managed keys and enable Azure Databricks audit logs.
AnswerD

Double encryption provides encryption at rest and in transit; customer-managed keys give control; audit logs track access.

Why this answer

Enabling double encryption (via Azure Disk Encryption and Azure Storage Service Encryption with customer-managed keys) ensures encryption at rest and in transit; Azure Key Vault manages the keys, and Azure Databricks audit logs capture access for auditing. Option A (SSE with platform-managed keys) provides encryption at rest but not with customer-managed keys and does not include audit logs. Option B (Azure Policy with GDPR blueprint) enforces compliance but does not directly implement encryption or auditing.

Option C (Azure Security Center and Defender for Storage) provides monitoring but does not encrypt data or provide access auditing.

Practice this question →
90
MCQmedium

You are building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

A.Use a 'Column pattern' to detect new columns and filter rows.
B.Configure 'Row sampling' settings to take only new rows.
C.Set 'Source' to 'New rows only' in the sink transformation.
D.Enable 'Incremental extraction' and specify the timestamp column for filtering.
AnswerD

Incremental extraction filters rows based on a watermark column like LastModified.

Why this answer

Mapping data flows in Azure Synapse Analytics support incremental extraction by enabling the 'Incremental extraction' option in the source transformation and specifying a timestamp column (e.g., LastModified) to filter only new or updated rows. This allows the pipeline to load only changed data since the last run, which is essential for efficient incremental loads into a dedicated SQL pool.

Exam trap

The trap here is that candidates confuse 'incremental extraction' in the source transformation with sink-level settings or sampling options, mistakenly thinking that row sampling or sink properties can achieve incremental loads, when only the source's incremental extraction with a timestamp column provides the correct row-level filtering.

How to eliminate wrong answers

Option A is wrong because 'Column pattern' is used to dynamically detect and handle new columns in the data, not to filter rows based on a timestamp for incremental loads. Option B is wrong because 'Row sampling' is a debugging or testing feature that takes a random subset of rows, not a mechanism to select only new rows based on a timestamp. Option C is wrong because 'New rows only' is not a valid configuration in the sink transformation; the sink transformation writes data to the destination but does not control which rows are read from the source.

Practice this question →
91
MCQhard

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

A.Increase the MAXDOP setting in the query
B.Create statistics on the columns used in joins and filters
C.Move the data to a dedicated SQL pool
D.Convert the Parquet files to CSV format
AnswerB

Statistics help the optimizer generate efficient plans.

Why this answer

(Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance.

Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Practice this question →
92
MCQhard

A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?

A.Rebuild the columnstore indexes on the table
B.Convert the table to a rowstore heap
C.Create statistics on the date column
D.Increase the number of partitions for the table
AnswerA

Rebuilding reduces fragmentation and improves partition pruning.

Why this answer

Rebuilding columnstore indexes reorganizes and compresses the data, which can improve partition elimination by ensuring that each partition's columnstore segments are well-structured and aligned with the partition boundaries. Over time, as data is inserted, updated, or deleted, columnstore indexes can become fragmented, leading to inefficient partition pruning and slower query performance on date-range filters.

Exam trap

The trap here is that candidates often assume poor partition pruning is always due to incorrect partitioning strategy or missing statistics, overlooking the fact that columnstore index fragmentation can prevent effective partition elimination even when the partition scheme is correct.

How to eliminate wrong answers

Option B is wrong because converting the table to a rowstore heap would eliminate the benefits of columnar storage and compression, likely worsening query performance for analytical workloads. Option C is wrong because statistics on the date column help the optimizer estimate cardinality but do not directly fix partition pruning issues caused by fragmented columnstore indexes. Option D is wrong because increasing the number of partitions can actually degrade performance by creating too many small partitions, leading to overhead and inefficient partition elimination.

Practice this question →
93
MCQmedium

You are developing a data processing pipeline in Azure Data Factory. The pipeline must copy data from an on-premises SQL Server to Azure Blob Storage. The data volumes are large, and the network connection is unreliable. Which configuration should you use to ensure resilience and resume capability?

A.Use a staging copy with Azure Blob Storage as an intermediate store.
B.Configure the copy activity with fault tolerance and enable 'Skip incompatible rows'. Use a self-hosted integration runtime for the source.
C.Increase the 'parallelCopies' setting to 10 and use a staging table.
D.Use PolyBase to load data directly into Azure Synapse Analytics.
AnswerB

Fault tolerance allows the copy to continue despite errors, and self-hosted IR supports checkpointing for resume.

Why this answer

Using a self-hosted integration runtime enables connectivity to on-premises SQL Server, and enabling fault tolerance with 'Skip incompatible rows' allows the copy activity to continue despite transient failures or data inconsistencies. This configuration ensures resilience by automatically retrying failed operations and skipping rows that cause errors, which is critical for large data volumes over an unreliable network.

Exam trap

Microsoft often tests the misconception that staging or parallelization alone provides resilience, but the key is that fault tolerance with row-level skipping is the only option that directly handles data errors and network interruptions without failing the entire copy.

How to eliminate wrong answers

Option A is wrong because using Azure Blob Storage as an intermediate store (staging copy) adds unnecessary complexity and cost without addressing the core issue of network unreliability; it is typically used for cross-region or cross-account copies, not for resilience against transient failures. Option C is wrong because increasing 'parallelCopies' to 10 can improve throughput but does not provide fault tolerance or resume capability; it may even exacerbate failures by creating more concurrent connections that can fail independently. Option D is wrong because PolyBase is a technology for loading data into Azure Synapse Analytics, not for copying data to Azure Blob Storage, and it does not address the on-premises source or network resilience requirements.

Practice this question →
94
MCQhard

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

A.Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database
B.Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly
C.Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database
D.Use Azure Functions to process events and write to SQL Database
AnswerC

Minimal latency and operational overhead.

Why this answer

Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Exam trap

The trap here is that candidates often over-engineer the solution by choosing Databricks (Option A) for its flexibility, overlooking that Stream Analytics is purpose-built for low-latency, windowed aggregations with minimal operational overhead, while Databricks adds unnecessary complexity for simple time-based aggregations.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces significant operational overhead for cluster management and is overkill for simple minute-level aggregation, plus it adds latency from Spark job initialization and checkpointing. Option B is wrong because Event Hubs Capture stores raw data in blob storage, and using Azure Data Factory to load hourly into SQL Database introduces at least 60 minutes of latency, failing the real-time requirement. Option D is wrong because Azure Functions are stateless and event-driven, lacking built-in windowing capabilities for time-based aggregation, so you would need to implement custom state management (e.g., using Durable Functions or external storage), increasing complexity and latency.

Practice this question →
95
Multi-Selectmedium

You are building a real-time processing solution using Azure Stream Analytics. The solution must handle out-of-order events and late arrivals. Which THREE mechanisms should you configure in the Stream Analytics job?

Select 3 answers
A.Set an 'Out-of-order tolerance' window in the event ordering settings.
B.Adjust the 'Streaming units' to handle higher throughput.
C.Configure a 'Late-arrival tolerance' window.
D.Enable 'Event hub capture' to store raw events for reprocessing.
E.Choose an output adapter that supports exactly-once delivery.
AnswersA, C, E

This defines how late events can be reordered.

Why this answer

Azure Stream Analytics allows you to configure an 'Out-of-order tolerance' window in the event ordering settings. This window defines the maximum time difference that out-of-order events can be reordered before being considered late. By setting this tolerance, you ensure that events arriving slightly out of sequence are still processed correctly, which is critical for real-time analytics where event order matters.

Exam trap

The trap here is that candidates confuse scaling mechanisms (like Streaming units) or storage features (like Event Hubs Capture) with event ordering controls, which are specifically designed to manage temporal anomalies in streaming data.

Practice this question →
96
MCQmedium

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

A.The statistics on the tables are outdated or missing.
B.The tables are distributed using round-robin distribution.
C.Result-set caching is disabled.
D.The resource class for the user is set to smallrc.
AnswerA

Outdated or missing statistics can cause the optimizer to choose suboptimal access methods like table scans.

Why this answer

Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Exam trap

The trap here is that candidates often confuse performance issues caused by distribution type or resource class with the optimizer's reliance on statistics, overlooking that even with optimal distribution and sufficient resources, stale statistics force scans instead of seeks.

How to eliminate wrong answers

Option B is wrong because round-robin distribution evenly distributes data across distributions without considering join keys, which can cause data movement but does not directly cause table scans instead of seeks; scans are a symptom of missing statistics or poor index usage. Option C is wrong because result-set caching only affects repeated execution of the same query by storing results, not the initial query plan choice between scan and seek. Option D is wrong because the resource class (e.g., smallrc) controls memory and concurrency slots for the user, not the query optimizer's decision to use scans versus seeks; scans occur regardless of resource class if statistics are stale.

Practice this question →
97
Multi-Selecthard

You are a Data Engineer at Contoso Ltd. The company operates an e-commerce platform that generates streaming data from user interactions (clicks, page views, purchases) at a rate of 50,000 events per second. The data is ingested into Azure Event Hubs. You need to design a data processing solution that meets the following requirements: 1. Real-time dashboards must display aggregated metrics (e.g., total sales, active users) with a latency of less than 2 seconds. 2. Historical data must be stored in Azure Data Lake Storage Gen2 in Delta Lake format for batch analytics. 3. The solution must support exactly-once semantics for the streaming output to both the dashboard and the data lake. 4. You must use Azure Stream Analytics as the primary processing engine. 5. The output to the dashboard should use Azure Power BI, while the output to the data lake should use Azure Synapse Analytics serverless SQL pool for querying. You have configured an Azure Stream Analytics job with Event Hubs as input. For output, you added a Power BI dataset and an Azure Data Lake Storage Gen2 output. However, you discover that the Power BI dataset is being updated with duplicate records, and the data lake output sometimes misses small windows of data during job restarts. Which two actions should you take to meet the requirements? (Choose two.) A. Change the Power BI output to use the 'Exactly once' output mode. B. Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once. C. Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output. D. Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Select 2 answers
A.Change the Power BI output to use the 'Exactly once' output mode. [CORRECT]
B.Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once. [wrong]
C.Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output. [CORRECT]
D.Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode. [wrong]
AnswersA, C

Correct: Power BI output can be configured with 'Exactly once' output mode, which eliminates duplicate records by ensuring each event is written only once to the dataset.

Why this answer

Power BI output supports 'Exactly once' output mode, which prevents duplicate records in the dashboard. Option C is correct because enabling checkpointing and using 'Exactly once' output mode for Azure Data Lake Storage Gen2 ensures exactly-once delivery to the data lake and prevents data loss during job restarts. Option B is incorrect because changing Event Hubs compatibility level to 1.0 does not guarantee exactly-once semantics; it only affects the serialization format.

Option D is incorrect because using a second Stream Analytics job adds unnecessary complexity, and 'At least once' mode for Power BI would still allow duplicates.

Practice this question →
98
MCQmedium

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slower than expected. The files are large (500 MB each) and not partitioned. You need to improve query performance without moving data. What should you do?

A.Convert the Parquet files to Delta format using Azure Databricks.
B.Change the files to CSV format with a header row.
C.Use OPENROWSET with BULK and specify ROWSET_OPTIONS for better performance.
D.Create external tables with explicit file format and partition elimination hints.
AnswerD

External tables with file format optimization can improve query performance by enabling metadata-based pruning.

Why this answer

Creating external tables with file format options allows the serverless SQL pool to use metadata for optimization. Changing the file format to CSV or using OPENROWSET without external tables does not improve performance as much. Converting to Delta format requires data movement.

Partitioning the files would be best, but that requires reorganizing files.

Practice this question →
99
MCQeasy

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

A.Use a temporal window function with a 'late arrival' policy specified in the query.
B.Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.
C.Define a watermark in the query to specify a maximum out-of-order tolerance.
D.Set the event ordering policy to 'Adjust' to reorder events within a certain time window.
AnswerB

Input Order policy allows handling late events, and Stream Analytics ensures exactly-once delivery to SQL Database.

Why this answer

The Input Order section in Azure Stream Analytics job configuration allows you to set a late arrival tolerance window, which handles late-arriving events by buffering them for a specified duration. This ensures that events arriving after their timestamp are still included in the correct window for aggregation, supporting exactly-once semantics when combined with checkpointing and idempotent output to Azure SQL Database.

Exam trap

The trap here is that candidates confuse the late arrival tolerance window (set in job configuration) with window functions or watermarks used in other streaming systems, leading them to incorrectly select query-level options like temporal window functions or watermarks.

How to eliminate wrong answers

Option A is wrong because temporal window functions (e.g., TumblingWindow, HoppingWindow) define the aggregation window but do not include a 'late arrival' policy parameter; late arrival handling is configured at the job level, not in the query. Option C is wrong because watermarks are a concept in stream processing frameworks like Apache Flink, not a feature of Azure Stream Analytics; Stream Analytics uses event ordering policies instead. Option D is wrong because the 'Adjust' event ordering policy reorders events based on their timestamp but does not provide a configurable late arrival tolerance window; it only adjusts timestamps for out-of-order events within a fixed tolerance.

Practice this question →
100
MCQhard

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

A.Azure Synapse Spark with Structured Streaming
B.Azure Stream Analytics
C.Azure Data Factory mapping data flows
D.Azure Synapse dedicated SQL pool with T-SQL queries
AnswerB

Sub-second latency, native Event Hubs and Cosmos DB connectors.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Exam trap

The trap here is that candidates often confuse Azure Synapse Spark Structured Streaming (which is micro-batch, not true streaming) with a real-time engine, or they assume Azure Data Factory can handle streaming data because it supports 'real-time' triggers, but it cannot perform in-flight aggregations with sub-second latency.

How to eliminate wrong answers

Option A is wrong because Azure Synapse Spark with Structured Streaming is a batch-micro-batch engine with higher latency (typically seconds to minutes) and is not optimized for sub-5-second real-time aggregation; it is better suited for complex transformations on large datasets. Option C is wrong because Azure Data Factory mapping data flows are designed for batch ETL/ELT operations on data at rest, not for real-time streaming ingestion or low-latency aggregations. Option D is wrong because Azure Synapse dedicated SQL pool with T-SQL queries is a massively parallel processing (MPP) data warehouse for analytical queries on stored data, not for real-time stream processing; it cannot directly ingest streaming data from Event Hubs and perform sub-5-second aggregations.

Practice this question →
101
Multi-Selectmedium

Which THREE factors should you consider when designing a real-time streaming solution using Azure Stream Analytics to process IoT data from thousands of devices?

Select 3 answers
A.The need to join input data with reference data that changes every few seconds.
B.The batch size for output to Azure Synapse Analytics to minimize write transactions.
C.The windowing function and late arrival policy for handling out-of-order events.
D.The latency requirements for writing output to Azure Cosmos DB for NoSQL.
E.The number of streaming units and partitioning scheme for the input Event Hubs.
AnswersB, C, E

Batching reduces transaction costs and improves throughput.

Why this answer

Batching output to Azure Synapse Analytics reduces write transactions and improves cost efficiency. Option C is correct because windowing functions and late arrival policies are essential for handling out-of-order events in real-time streaming. Option E is correct because streaming units and partitioning determine throughput and scalability for processing IoT data.

Option A is incorrect because reference data that changes every few seconds would require frequent reloads, defeating the purpose of static reference data in Stream Analytics. Option D is incorrect because latency requirements for Cosmos DB are not a primary design factor; Stream Analytics can write to Cosmos DB, but the main considerations are throughput and consistency, not latency.

Practice this question →
102
MCQeasy

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

A.Set a cache size for the serverless SQL pool
B.Configure a dedicated SQL pool with auto-scaling
C.Use workload classification to assign resources
D.Enable auto-resume and auto-pause on the serverless SQL pool endpoint
AnswerD

Incorrect. Auto-resume and auto-pause do not scale compute resources; they only manage when the pool is active. Serverless SQL pools scale automatically without this feature.

Why this answer

Serverless SQL pools in Azure Synapse Analytics automatically scale compute resources based on workload demand without requiring any configuration. None of the provided options enable this automatic scaling. Auto-resume and auto-pause only control the pool's active state, not its compute size.

Dedicated SQL pool features like auto-scaling, cache sizing, and workload classification do not apply to serverless pools.

Exam trap

Candidates often assume that serverless SQL pools require a scaling configuration or that auto-resume/auto-pause scales compute resources. In reality, scaling is automatic and not configurable; auto-resume/auto-pause only manage availability.

How to eliminate wrong answers

Option A is wrong because serverless SQL pools do not have a configurable cache size; caching is managed automatically by the service and cannot be set by the user. Option B is wrong because a dedicated SQL pool with auto-scaling is a separate resource type that scales compute by adding or removing Data Warehouse Units (DWUs), but the question specifically asks about serverless SQL pools, which do not use dedicated compute resources. Option C is wrong because workload classification is a feature for dedicated SQL pools (formerly SQL Data Warehouse) to assign resources and priorities to different workloads; serverless SQL pools do not support workload classification as they automatically manage resource allocation.

Practice this question →
103
MCQmedium

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with timeout errors. You need to improve reliability. What should you do?

A.Use a self-hosted integration runtime with high availability
B.Enable fault tolerance and use staging
C.Change the source to Azure SQL Database
D.Increase the degree of copy parallelism
AnswerB

Fault tolerance allows the copy activity to retry on transient errors, and staging improves performance and reliability.

Why this answer

Enabling fault tolerance with staging in Azure Data Factory allows the copy activity to automatically retry transient failures (such as timeout errors) by staging intermediate data in Azure Blob Storage. This mechanism uses a two-phase commit approach: data is first written to a staging location, and then committed to the final sink only after successful validation, which isolates the pipeline from intermittent source or sink failures.

Exam trap

The trap here is that candidates often confuse high availability of the integration runtime (Option A) with fault tolerance of the copy activity, not realizing that HA only protects the IR nodes, not the data transfer itself.

How to eliminate wrong answers

Option A is wrong because using a self-hosted integration runtime with high availability improves the reliability of the integration runtime itself (e.g., node failures), but does not address timeout errors caused by the copy activity's interaction with the source or sink; it does not provide retry logic for data transfer failures. Option C is wrong because changing the source to Azure SQL Database does not resolve timeout errors in the copy activity; it merely shifts the source location, and the pipeline could still experience timeouts due to network latency or throttling. Option D is wrong because increasing the degree of copy parallelism can actually exacerbate timeout issues by overwhelming the source or sink with concurrent requests, and it does not provide any fault tolerance or retry mechanism for transient failures.

Practice this question →
104
MCQeasy

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

A.Alter Row transformation
B.Incremental copy (watermark) pattern using a Lookup activity and a Copy activity
C.Schedule trigger
D.Lookup activity alone
AnswerB

The watermark pattern uses a lookup to get the last watermark value and a copy activity to copy data changed after that watermark.

Why this answer

The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Exam trap

The trap here is that candidates often confuse a scheduling mechanism (Schedule trigger) with the actual data processing logic required for incremental loads, or they mistakenly think a single activity like Lookup or Alter Row can handle the entire incremental copy workflow without understanding the need for a watermark pattern.

How to eliminate wrong answers

Option A is wrong because Alter Row transformation is a data flow transformation used to mark rows for insert, update, upsert, or delete in a sink, but it does not provide the incremental loading logic or watermark mechanism needed to identify new/updated rows from a source. Option C is wrong because a Schedule trigger only defines when a pipeline runs (e.g., every hour), but it does not implement the incremental copy logic itself; you still need the watermark pattern inside the pipeline. Option D is wrong because a Lookup activity alone can retrieve the watermark value but cannot copy data; it must be combined with a Copy activity to actually move the incremental rows.

Practice this question →
105
MCQmedium

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

A.Avro with Deflate compression
B.CSV with Gzip compression
C.Parquet with Snappy compression
D.ORC with Zlib compression
AnswerC

Parquet is columnar and Snappy provides fast compression/decompression, ideal for Synapse dedicated SQL pool.

Why this answer

Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Exam trap

Microsoft often tests the misconception that row-based formats like Avro or CSV are suitable for analytical workloads, but the trap here is that columnar formats (Parquet/ORC) are required for optimal read performance in Synapse dedicated SQL pools, and Snappy is preferred over Zlib for speed-critical pipelines.

How to eliminate wrong answers

Option A is wrong because Avro is a row-based format that does not support columnar pruning, leading to higher I/O for analytical queries on dedicated SQL pools. Option B is wrong because CSV with Gzip compression is row-oriented and not splittable at the row level, causing poor parallelism and slower read performance in Synapse. Option D is wrong because ORC with Zlib compression offers higher compression ratios but significantly slower decompression compared to Snappy, which can bottleneck read performance in Synapse's MPP engine.

Practice this question →
106
Multi-Selecthard

Which TWO are required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Select 2 answers
A.A linked service to Azure SQL Database.
B.A dataset that references the stored procedure.
C.A self-hosted integration runtime.
D.A staging blob storage account.
E.A stored procedure activity in the pipeline.
AnswersA, E

The linked service provides connection details.

Why this answer

To run a stored procedure in Azure SQL Database from Azure Data Factory, only two components are required: a linked service to Azure SQL Database, which defines the connection and authentication, and a stored procedure activity in the pipeline that specifies the stored procedure name and parameters directly. A dataset is not required because the stored procedure activity can define the stored procedure reference inline. Options like a self-hosted integration runtime or staging storage are unnecessary for this task.

Exam trap

A common trap is thinking that a self-hosted integration runtime or staging storage is required. For Azure SQL Database (without a firewall blocking public access), the default auto-resolve IR works fine. Staging is only needed for large data movements, not for running a stored procedure.

Also, some may mistakenly believe a dataset is optional, but it is required for defining the stored procedure reference.

Practice this question →
107
MCQhard

You are designing a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow with Azure Integration Runtime (IR). The pipeline runs slowly and you notice that the IR's data movement is limited by the number of cores. Which configuration should you adjust to improve performance?

A.Enable staging for the copy activity within the data flow.
B.Increase the 'Data Flow Compute Type' and 'Core Count' in the Azure IR settings.
C.Use a Self-Hosted IR instead of Azure IR for data flows.
D.Increase the 'Number of partitions' in the source transformation.
AnswerB

These settings directly allocate more resources to mapping data flows.

Why this answer

The Azure Integration Runtime (IR) for mapping data flows uses a Spark cluster, and its performance is directly tied to the compute resources allocated. By increasing the 'Data Flow Compute Type' (e.g., from General Purpose to Memory Optimized) and the 'Core Count' (e.g., from 4 to 8 or 16 cores), you provide more parallel processing power, which directly addresses the core-limited data movement bottleneck.

Exam trap

The trap here is that candidates confuse the 'Number of partitions' setting (which controls data parallelism within the flow) with the Azure IR's core count (which controls the Spark cluster's overall compute capacity), leading them to pick D instead of B.

How to eliminate wrong answers

Option A is wrong because enabling staging for the copy activity is used to offload data movement to a staging blob store for PolyBase or COPY statement scenarios, not to increase the core count of the Azure IR for mapping data flows. Option C is wrong because Self-Hosted IR is designed for on-premises or private network data sources and does not improve the Spark-based compute performance of a mapping data flow; in fact, it adds network latency. Option D is wrong because increasing the 'Number of partitions' in the source transformation can improve parallelism within the data flow, but it does not change the underlying Azure IR's core count or compute type, which is the root cause of the core-limited bottleneck.

Practice this question →
108
MCQeasy

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

A.Azure HDInsight with Hive
B.Azure Analysis Services
C.Azure Data Factory with Mapping Data Flows
D.Azure Databricks with PySpark
AnswerC

Mapping Data Flows provide code-free data transformation at scale.

Why this answer

Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Practice this question →
109
MCQeasy

Your company uses Azure Data Lake Storage Gen2 as the central data lake. You need to process batch data using serverless Spark jobs that can be scheduled daily. Which Azure service should you use?

A.Azure Batch with custom Spark containers.
B.Azure Synapse Analytics serverless Spark pool with pipelines.
C.Azure Databricks with a job cluster.
D.Azure Machine Learning with Spark compute.
AnswerB

Synapse provides serverless Spark pools with automatic scaling and built-in scheduling via pipelines.

Why this answer

Azure Synapse Analytics provides serverless Spark pools with built-in scheduling via pipelines, allowing you to run daily batch jobs without managing clusters. Option A (Azure Batch) is for custom compute workloads, not Spark jobs. Option C (Azure Databricks) requires a job cluster that is not serverless.

Option D (Azure Machine Learning) is designed for ML workflows, not general batch data processing.

Practice this question →
110
Multi-Selecteasy

Which TWO of the following are required components to set up a data pipeline that uses Change Data Capture (CDC) to incrementally load data from SQL Server to Azure Synapse using Azure Data Factory?

Select 2 answers
A.CDC enabled on the source SQL Server database and tables
B.A staging Azure Blob Storage account
C.A Lookup activity to get the last watermark
D.A stored procedure in the source database to capture changes
E.A linked service to the Azure Synapse dedicated SQL pool
AnswersA, E

CDC must be enabled on the source to track changes.

Why this answer

Change Data Capture (CDC) must be enabled on the source SQL Server database and the specific tables you intend to track. Without CDC enabled, SQL Server will not generate the change tracking tables (e.g., cdc.<capture_instance>_CT) that Azure Data Factory’s CDC connector reads to identify inserts, updates, and deletes. This is a prerequisite for any incremental load using the native CDC mechanism in ADF.

Exam trap

The trap here is that candidates often confuse CDC-based incremental loading with watermark-based incremental loading, leading them to incorrectly select a Lookup activity (Option C) or a staging storage account (Option B) as required components.

Practice this question →
111
MCQmedium

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

A.Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.
B.Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.
C.Azure Databricks with structured streaming and a sliding window.
D.Azure Analysis Services to process streaming data directly from Event Hubs.
AnswerA

Stream Analytics provides built-in windowing functions and late-arrival handling, perfect for this scenario.

Why this answer

Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Exam trap

The trap here is that candidates often confuse tumbling windows (fixed, non-overlapping) with sliding windows (continuous, overlapping) or assume that any compute service (like Functions or Databricks) can easily replicate Stream Analytics' built-in windowing and late-arrival handling, ignoring the complexity of state management and exactly-once semantics.

How to eliminate wrong answers

Option B is wrong because Azure Functions triggered by Event Hubs do not provide built-in windowing or late-arrival policy support; you would have to manually implement stateful aggregation, which is complex and error-prone. Option C is wrong because Azure Databricks with structured streaming uses a sliding window, not a tumbling window, and does not offer a native late-arrival policy configuration as simple as Stream Analytics; it also introduces unnecessary overhead for this real-time dashboard scenario. Option D is wrong because Azure Analysis Services is an OLAP engine for analytical queries on pre-aggregated data, not a real-time stream processing service; it cannot directly process streaming data from Event Hubs.

Practice this question →
112
Multi-Selecteasy

You are designing a data processing solution in Azure Data Factory that uses mapping data flows. You need to perform type conversions on incoming data. Which two transformations can be used to change data types? (Choose two.)

Select 2 answers
A.Conditional Split
B.Derived Column
C.Assert
D.Sort
E.Select
AnswersB, E

Derived Column allows type conversion via expressions.

Why this answer

Options B and E are correct. Derived Column can change data types through expressions, and Select can cast types during column projection. Option C (Assert) is incorrect because it only validates and routes rows based on conditions; it does not convert data types.

Option A (Conditional Split) and Option D (Sort) are also incorrect.

Practice this question →
113
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must support incremental loading of data from an Azure SQL Database to a dedicated SQL pool using PolyBase. Which approach should you use to minimize data movement and maximize performance?

A.Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.
B.Create external tables in the dedicated SQL pool that reference the source data, then use CREATE TABLE AS SELECT (CTAS) to load incrementally.
C.Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.
D.Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.
AnswerB

PolyBase external tables enable direct query of source data, and CTAS allows efficient incremental loading with minimal data movement.

Why this answer

Using external tables with PolyBase in Azure Synapse Analytics allows you to directly query the source Azure SQL Database without moving the data first. The CREATE TABLE AS SELECT (CTAS) statement then loads only the incremental data into the dedicated SQL pool, minimizing data movement by leveraging PolyBase's parallel streaming capability for maximum performance.

Exam trap

The trap here is that candidates often assume external tables are only for static data or Hadoop, but PolyBase in Synapse supports external tables against Azure SQL Database for efficient incremental loading, making options that introduce extra hops (like Data Factory or bcp) seem more familiar but less optimal.

How to eliminate wrong answers

Option A is wrong because the bcp utility exports data to a text file, which introduces an intermediate storage step and additional I/O overhead, increasing data movement and latency compared to direct PolyBase access. Option C is wrong because Azure Data Factory copy activity moves data through an intermediate staging area (e.g., Azure Blob Storage), which adds extra data transfer and storage costs, whereas PolyBase can read directly from the source without staging. Option D is wrong because Azure Databricks with the Spark connector requires moving data out of Azure SQL Database into a Spark cluster for processing, then writing back to the dedicated SQL pool, which increases data movement and complexity compared to the native PolyBase approach.

Practice this question →
114
Multi-Selecteasy

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Select 3 answers
A.Use a path pattern that includes {date} and {time} tokens.
B.Configure the event ordering policy to adjust late events.
C.Set the output serialization format to Avro or Parquet.
D.Set the compatibility level to 1.2 or higher.
E.Enable 'Write to blob storage partitioned by time' in the output settings.
AnswersA, C, E

Tokens in the path pattern create folder structure based on date and time.

Why this answer

Azure Stream Analytics supports custom path patterns for Blob Storage output, where {date} and {time} tokens automatically resolve to the processing date and hour (in UTC). This allows partitioning output files into a folder structure like 'YYYY/MM/DD/HH', enabling efficient querying by date and hour without post-processing.

Exam trap

The trap here is that candidates confuse event ordering policies or compatibility levels with output partitioning, but only the path pattern tokens and the 'Write to blob storage partitioned by time' toggle (which enables the {date}/{time} tokens) directly control folder structure.

Practice this question →
115
MCQhard

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

A.Row-level security (RLS)
B.Column-level security
C.Dynamic data masking
D.Microsoft Defender for Cloud
AnswerB

Column-level security restricts access to specific columns.

Why this answer

Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Exam trap

The trap here is that candidates often confuse Dynamic data masking with access control, but masking only hides data from the UI while still allowing underlying access, whereas column-level security actually prevents unauthorized users from reading the column data at all.

How to eliminate wrong answers

Option A is wrong because Row-level security (RLS) restricts access to rows based on user identity or context, not columns, so it cannot limit visibility of specific columns like credit card numbers. Option C is wrong because Dynamic data masking obfuscates data at query time but does not prevent authorized users from viewing the original data if they have direct access; it is not a permission-based access control. Option D is wrong because Microsoft Defender for Cloud is a security monitoring and threat protection service, not a data access control feature for restricting column visibility in Synapse Analytics.

Practice this question →
116
MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

A.Round-robin distribution.
B.Partitioned table with a partition key.
C.Hash distribution on a column that is frequently used in joins and aggregations.
D.Replicated distribution.
AnswerC

Hash distribution collocates rows with the same key, reducing data movement.

Why this answer

Hash distribution on a column frequently used in joins and aggregations is the best choice for a fact table with billions of rows in a dedicated SQL pool. It distributes rows across distributions based on a hash of the distribution column, ensuring that rows with the same key value are co-located on the same distribution. This minimizes data movement during joins and aggregations, as the data required for these operations is already local to each distribution, significantly improving query performance.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning alone can optimize data movement across nodes, but partitioning operates within a distribution and does not affect how data is distributed across compute resources.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering data relationships, which leads to excessive data movement during joins and aggregations, degrading performance for large fact tables. Option B is wrong because partitioning is a data organization technique within a distribution, not a distribution strategy; it helps with data management and partition elimination but does not control how data is distributed across compute nodes, so it cannot minimize data movement across distributions. Option D is wrong because replicated distribution copies the entire table to each compute node, which is impractical for a fact table with billions of rows due to massive storage overhead and write performance penalties; it is intended for small dimension tables, not large fact tables.

Practice this question →
117
MCQhard

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

A.The Parquet files are not using the .parquet extension.
B.The derivedModel option is set to false, which disables schema inference.
C.The serverless SQL pool infers schema from the first file and expects all files to have the same schema.
D.The recursive option is causing the table to include files from subfolders that have different schemas.
AnswerC

Serverless SQL uses schema inference from the first file; subsequent files with different schemas cause errors.

Why this answer

Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised.

This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Exam trap

The trap here is that candidates assume serverless SQL can automatically handle heterogeneous schemas (like Spark does with mergeSchema), but in reality it requires all files to share the exact same schema as the first file it reads.

How to eliminate wrong answers

Option A is wrong because the .parquet extension is not required; serverless SQL can infer Parquet format from the file's binary header, not the file extension. Option B is wrong because the derivedModel option does not exist in serverless SQL table creation; schema inference is always enabled and cannot be disabled via such an option. Option D is wrong because the recursive option controls whether subfolders are scanned, but schema mismatch errors occur even without recursion if files in the same folder have different schemas; recursion is not the root cause.

Practice this question →
118
MCQeasy

You are designing a data processing pipeline using Azure Data Factory. The pipeline must ingest data from an HTTP endpoint that returns a JSON array. The data must be transformed by flattening nested arrays and then loaded into an Azure SQL Database table. The pipeline should be triggered daily. You need to choose the appropriate activities and transformations. The solution must be cost-effective and easy to maintain. Which combination of activities should you use?

A.Use a Lookup activity to read the JSON, then a ForEach activity to iterate and insert rows into SQL Database.
B.Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage, then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database.
C.Use a Data Flow activity directly from HTTP source with a Flatten transformation and sink to SQL Database.
D.Use two Copy activities: one to copy JSON to Blob Storage, and another to copy from Blob Storage to SQL Database without transformation.
AnswerB

This is the standard pattern: ingest, transform, load.

Why this answer

The correct approach is Option B: Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage (staging), then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database. This is cost-effective and maintainable because it separates ingestion and transformation, allows for schema drift, and uses serverless Data Flows. Option A is wrong because Lookup is for reading a single row/value, not for bulk data ingestion; ForEach with insert would be inefficient and costly.

Option C is wrong because Data Flows cannot directly read from HTTP sources; they require a dataset that is staged in a supported store like Blob Storage. Option D is wrong because it performs no transformation, so the nested JSON would not be flattened for loading into SQL.

Practice this question →
119
MCQmedium

A company is ingesting streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time and stored in Azure Synapse Analytics for reporting. The solution must handle late-arriving data and ensure exactly-once semantics. Which Azure service should you use for stream processing?

A.Azure Data Factory with Event Hubs source
B.Azure Synapse Spark Structured Streaming
C.Azure Stream Analytics
D.Azure Event Hubs with Capture
AnswerC

Provides exactly-once delivery and can handle late arrivals.

Why this answer

(Azure Stream Analytics) is correct because it supports exactly-once semantics for output to Azure Synapse Analytics, handles late-arriving data via adjustable event ordering policies, and is purpose-built for real-time stream processing. Option A (Azure Data Factory) is incorrect as it is primarily a batch ETL service and not designed for near-real-time streaming. Option B (Azure Synapse Spark Structured Streaming) can process streaming data but does not provide exactly-once semantics to Synapse out of the box without additional configuration.

Option D (Azure Event Hubs with Capture) is incorrect because it only captures raw event data to storage and does not perform stream processing or guarantee exactly-once delivery to Synapse.

Practice this question →
120
MCQhard

You are reviewing a mapping data flow in Azure Data Factory that reads a CSV file from ADLS Gen2 and writes to an Azure Synapse Analytics dedicated SQL pool. The data flow includes a Derived Column transformation with the expression: `column1 == "Error" ? toString(column1) : column1`. The pipeline fails with an error indicating that the sink table could not be created. What is the most likely cause?

A.The source file does not have a header row.
B.The Derived Column expression has a syntax error.
C.Using allowCopyCommand with autoCreate is not supported.
D.The sink dataset is configured for JSON format.
AnswerD

If the sink dataset is configured for JSON format, but the sink is an Azure Synapse dedicated SQL pool table, there will be a format mismatch. The data flow expects a table format for the sink, and attempting to create a table with JSON format settings could cause the error.

Why this answer

In a mapping data flow, the sink dataset must match the destination format. For an Azure Synapse dedicated SQL pool, the dataset should be of type 'Azure Synapse Analytics' (table), not JSON. If the dataset is set to JSON, the pipeline will fail when trying to create the sink table because the format is incompatible.

Option C is incorrect because allowCopyCommand is a feature of the Copy activity, not mapping data flows.

Practice this question →
121
MCQhard

You are monitoring an Azure Synapse Pipeline that uses a Mapping Data Flow. The data flow processes 2 GB of data from a CSV source and writes to a Delta sink. The pipeline fails with a 'DataFlowException: Operation aborted' error after running for 45 minutes. The cluster is configured with 8 cores. What is the most likely cause?

A.The cluster size is too small for the data volume.
B.The CSV source contains malformed rows that cause parsing errors.
C.The data flow cluster's time-to-live (TTL) is set to 45 minutes and the job exceeded it.
D.The data flow is using the Spark cluster's default timeout setting.
AnswerC

The default TTL for data flow clusters is 60 minutes, but if custom set to 45 minutes, the cluster may be terminated during long-running jobs.

Why this answer

The error 'Operation aborted' after exactly 45 minutes aligns with the default time-to-live (TTL) setting for Azure Synapse Mapping Data Flow clusters. When the TTL expires, the cluster is terminated, and any running job is aborted. The 8-core cluster and 2 GB data volume are not inherently problematic for a 45-minute window, but the TTL default of 45 minutes causes the abort if the job runs longer than that.

Exam trap

The trap here is that candidates confuse the TTL (a Synapse cluster lifecycle setting) with a Spark job timeout or a data volume issue, leading them to incorrectly select cluster size or malformed data as the cause.

How to eliminate wrong answers

Option A is wrong because 8 cores can process 2 GB of data within 45 minutes under normal conditions; the error is not due to insufficient cluster size but rather a timeout. Option B is wrong because malformed rows would cause a parsing error (e.g., 'MalformedRecordException'), not a generic 'Operation aborted' error after a fixed duration. Option D is wrong because the Spark cluster's default timeout is not a configurable setting that causes this specific error; the TTL is a Synapse-specific cluster lifecycle setting, not a Spark-level timeout.

Practice this question →
122
MCQmedium

You are designing a data processing pipeline in Azure Data Factory that ingests data from an on-premises SQL Server database to Azure Data Lake Storage Gen2. The data volume is large (500 GB). The network connection between on-premises and Azure is limited to 100 Mbps. You need to minimize the time to transfer the initial full load while ensuring data integrity. Which approach should you recommend?

A.Use Azure Data Factory copy activity with parallel connections
B.Use Azure ExpressRoute to increase bandwidth
C.Compress the data using GZip and use copy activity
D.Use Azure Data Box to copy the data offline
AnswerD

Data Box transfers data physically, bypassing network limitations.

Why this answer

Azure Data Box physically ships the data, bypassing network bandwidth limitations for large initial loads. Option A is wrong because it would take over 11 hours even at full bandwidth, and network may not be stable. Option B is wrong because it compresses but still uses network.

Option C is wrong because VPN adds overhead.

Practice this question →
123
MCQeasy

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

A.FTP
B.Azure Blob Storage
C.HTTP
D.Rest service
AnswerA

FTP linked service supports username and password authentication.

Why this answer

Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Exam trap

The trap here is that candidates often confuse the FTP connector with the HTTP or REST connector because they think all file transfers can be handled by generic web protocols, but FTP has its own distinct authentication and command set that requires a dedicated connector.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it is a destination or source for Azure's blob storage service, not a connector for external FTP servers; it cannot authenticate against an FTP server. Option C (HTTP) is wrong because the HTTP connector uses HTTP/HTTPS protocols and does not support FTP-specific authentication or directory listing commands. Option D (Rest service) is wrong because REST connectors are designed for RESTful APIs using JSON/XML payloads, not for FTP protocol operations like LIST or RETR.

Practice this question →
124
MCQmedium

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

A.Set the 'Staging' option to 'Use staging'
B.Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'
C.Enable staging for the sink and use PolyBase
D.Set the 'Partition option' to 'Round robin' on the source
AnswerB

More compute resources speed up data flow execution.

Why this answer

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

Exam trap

The trap here is that candidates confuse Mapping Data Flow performance tuning with Copy Activity optimizations, such as PolyBase or staging, which are irrelevant to Spark-based data flows.

How to eliminate wrong answers

Option A is wrong because setting 'Staging' to 'Use staging' in a Mapping Data Flow is not a valid configuration; staging is used for copy activities, not for data flows. Option C is wrong because enabling staging for the sink and using PolyBase is a performance optimization for Copy Activity, not for Mapping Data Flow, which uses Spark-native connectors. Option D is wrong because setting the 'Partition option' to 'Round robin' on the source distributes data evenly but does not address the root cause of slow performance, which is insufficient compute resources for the Spark cluster.

Practice this question →
125
MCQhard

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options: Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase. Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase. Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse. Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source. Which option should you choose?

A.Option C
B.Option A
C.Option D
D.Option B
AnswerD

Mapping data flow supports schema drift and uses staging for PolyBase.

Why this answer

It handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency.

Option D requires additional log-shipping, increasing complexity and latency.

Practice this question →
126
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

A.Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.
B.Use OPENROWSET with the DATA_SOURCE parameter.
C.Create views that filter on partition columns.
D.Increase the number of files per partition.
AnswerC

Allows partition elimination when querying.

Why this answer

Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Exam trap

The trap here is that candidates often assume that simply using external tables or OPENROWSET automatically provides partition pruning, but in serverless SQL pools, partition elimination only occurs when the query explicitly references the partition columns in the WHERE clause, typically through a view or inline filter.

How to eliminate wrong answers

Option A is wrong because CETAS creates external tables that store query results as new files, but it does not inherently optimize query performance or reduce data scanned for existing partitioned Parquet files; it is a data movement operation, not a query optimization technique. Option B is wrong because OPENROWSET with the DATA_SOURCE parameter allows querying files directly, but without explicit partition column filters in the WHERE clause, the serverless pool cannot perform partition elimination and will scan all files. Option D is wrong because increasing the number of files per partition increases metadata overhead and can degrade query performance due to more file open/read operations, and it does not reduce the amount of data scanned.

Practice this question →
127
Multi-Selectmedium

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Select 2 answers
A.Wrap the data flow in a Try-Catch activity in the pipeline.
B.Set the data flow's error handling to 'Abort on error' to stop processing on first failure.
C.Enable schema drift on the source to automatically handle data type mismatches.
D.Configure the sink transformation to allow errors and log error rows to a separate file.
E.Use a Conditional Split transformation to separate rows that cause errors based on a condition.
AnswersD, E

Sink can be configured to continue on error and write error rows to a file.

Why this answer

Configuring the sink transformation to allow errors and log error rows to a separate file enables graceful error handling in Mapping Data Flows. This approach captures rows that fail during transformation (e.g., type conversion errors) and writes them to a designated error output, allowing the pipeline to continue processing valid rows. Option E is correct because a Conditional Split transformation can proactively identify rows that are likely to cause errors based on a condition (e.g., checking for null or invalid data types) and route them to a separate path for logging or remediation, preventing them from reaching the sink and causing failures.

Exam trap

The trap here is that candidates often confuse pipeline-level error handling (like Try-Catch) with data flow-level error handling, or they assume schema drift can fix data type mismatches, when in fact it only handles structural changes at the source.

Practice this question →
128
MCQhard

You are a data engineer working for a logistics company. You have an existing Azure Data Factory pipeline that ingests data from a REST API to Azure Data Lake Storage Gen2. The API has rate limiting that can cause failures. You need to implement a solution that can handle rate limiting by retrying with exponential backoff. The pipeline should also log the number of retries for each API call. What should you do?

A.Configure the Copy activity with retry policy using exponential backoff by setting the retry count and retry interval. Enable diagnostic logs to capture retry details.
B.Use a Web activity with a Until loop to implement custom retry logic.
C.Use an Azure Function as a custom activity in Azure Data Factory to implement retry logic with exponential backoff.
D.Use Azure Logic Apps to call the API and then copy the response to Azure Data Lake Storage Gen2.
AnswerB

Correct. Using a Web activity with an Until loop allows you to implement custom retry logic with exponential backoff, and you can log retry counts.

Why this answer

Azure Data Factory Copy activity does not support exponential backoff; the retry interval is fixed. Using a Web activity inside an Until loop allows you to implement custom retry logic with exponential backoff by adjusting the wait time dynamically. This also enables logging the number of retries.

Option A is incorrect because the Copy activity's retry uses a fixed interval, not exponential backoff. Option C is more complex than necessary. Option D introduces an additional service without leveraging ADF's orchestration capabilities.

Practice this question →
129
MCQmedium

Refer to the exhibit. You are querying the sys.external_tables view in an Azure Synapse Analytics serverless SQL pool. The query returns no rows, but you believe that external tables have been created. What is the most likely reason?

A.The external tables are using PolyBase, which is not supported in serverless SQL pool.
B.Serverless SQL pool does not support external tables; you must use a dedicated SQL pool.
C.The external tables were created using OPENROWSET, not CREATE EXTERNAL TABLE, so they do not appear in sys.external_tables.
D.The user does not have permission to view the sys.external_tables view.
AnswerC

OPENROWSET queries do not create external table metadata; they are ad-hoc queries.

Why this answer

The user believes external tables have been created, but if they were created using CREATE EXTERNAL TABLE, they would appear in sys.external_tables. Since no rows are returned, it indicates that the external tables were not created via CREATE EXTERNAL TABLE; instead, the user likely used OPENROWSET to query data directly, which does not create a catalog entry. Therefore, the correct answer is C.

Exam trap

The trap here is that candidates may assume any external data access creates a catalog entry, but the exam tests the specific difference between DDL-based external tables and ad-hoc OPENROWSET queries in serverless SQL pool.

How to eliminate wrong answers

Option A is wrong because PolyBase is fully supported in serverless SQL pool for reading external data; it is not unsupported. Option B is wrong because serverless SQL pool does support external tables via CREATE EXTERNAL TABLE, and they are visible in sys.external_tables. Option D is wrong because if the user lacked permission to view sys.external_tables, the query would typically return an error or no rows, but the question states the user knows external tables exist, making a permission issue less likely than the metadata not being populated due to using OPENROWSET.

Practice this question →
130
MCQmedium

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

A.The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version
B.The resource path is malformed; it should include the blob path
C.The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'
D.The principal is a managed identity, but the policy requires a user-assigned identity
AnswerA

The condition requires API version 2019-12-12, which may not be used.

Why this answer

The policy condition requires the request to include the API version '2021-06-08' via the `acs:RequestVersion` condition key. Managed identity requests to Azure Data Lake Storage Gen2 use a default API version that may not match this specific version, causing the deny. The condition explicitly checks the request's API version, and if it does not match, access is denied regardless of other permissions.

Exam trap

The trap here is that candidates often overlook condition keys in Azure RBAC policies and focus only on the action or scope, assuming the deny is due to an incorrect role assignment or resource path, rather than a version-matching condition that blocks the request.

How to eliminate wrong answers

Option B is wrong because the resource path in the policy is correctly scoped to the storage account and container level; Azure RBAC policies for containers do not require the blob path in the resource path, as the action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is for listing containers, not reading blobs. Option C is wrong because the action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is correct for reading container properties or listing blobs within a container; the action for reading blob data itself is 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read', but the question states the managed identity needs to read from the 'data' container, which could include listing blobs, and the policy's action is not the cause of the deny. Option D is wrong because Azure RBAC policies do not distinguish between system-assigned and user-assigned managed identities; both are treated as service principals and can be assigned roles without any policy restriction on identity type.

Practice this question →
131
MCQmedium

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

A.Use an auto-scaling cluster with spot instances.
B.Use a fixed-size cluster with premium tier.
C.Use a Photon-accelerated cluster with premium tier.
D.Use an interactive cluster with a large number of workers.
AnswerA

Auto-scaling and spot instances provide cost-effectiveness and scalability.

Why this answer

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

Exam trap

The trap here is that candidates often assume premium tier or Photon acceleration automatically improves cost-effectiveness, but these features address performance or governance, not the core requirement of scaling with variable workloads and minimizing cost via spot pricing.

How to eliminate wrong answers

Option B is wrong because a fixed-size cluster cannot scale to handle variable workloads, leading to either over-provisioning (higher costs) or under-provisioning (performance degradation). Option C is wrong because Photon-accelerated clusters are optimized for high-performance SQL and DataFrame workloads, but they do not inherently address cost-effectiveness for variable workloads; the premium tier adds features like role-based access control but does not enable scaling or spot pricing. Option D is wrong because an interactive cluster with a large number of workers is designed for ad-hoc analysis and collaboration, not for cost-effective batch processing; it lacks auto-scaling and spot instance support, leading to higher costs during idle periods.

Practice this question →
132
MCQmedium

You are designing a data pipeline in Azure Data Factory (ADF) that copies data from an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The pipeline must run daily and handle incremental loads efficiently. Which sink dataset type and copy method should you use?

A.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use the Copy activity with PolyBase enabled.
B.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and enable the built-in Upsert option.
C.Use Azure Blob Storage as the sink dataset, then use PolyBase to load into the dedicated SQL pool.
D.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use Stored Procedure with staging table and PolyBase.
AnswerD

This combination enables high-throughput ingestion and supports incremental loading via merge logic in the stored procedure.

Why this answer

It uses a staging table and PolyBase to efficiently load incremental data into Azure Synapse Analytics dedicated SQL pool. PolyBase provides high-throughput parallel loading, and the stored procedure handles the merge logic (upsert) to manage incremental changes. This approach is recommended for large-scale, daily incremental loads to Synapse.

Exam trap

The trap here is that candidates assume the built-in Upsert option works for all Azure SQL targets, but it is not supported for Azure Synapse Analytics dedicated SQL pool, requiring a custom staging-and-merge pattern instead.

How to eliminate wrong answers

Option A is wrong because the Copy activity with PolyBase enabled does not natively support incremental upsert logic; it only supports bulk insert or append, not merge operations. Option B is wrong because the built-in Upsert option is not available for Azure Synapse Analytics dedicated SQL pool as a sink in ADF Copy activity; it is only supported for Azure SQL Database and SQL Server. Option C is wrong because using Azure Blob Storage as an intermediate sink adds unnecessary complexity and latency; PolyBase can load directly from ADF into Synapse without an intermediate Blob Storage hop.

Practice this question →
133
MCQeasy

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

A.Use Azure Stream Analytics to transform the data in real time.
B.Use PolyBase to load data and then use T-SQL stored procedures to transform.
C.Use Azure Databricks notebooks with Spark to perform transformations.
D.Use mapping data flows in Azure Synapse pipelines.
AnswerD

Correct. Mapping data flows in Azure Synapse pipelines are serverless, provide a visual interface for data transformations, and integrate directly with Azure Synapse pipelines, making them ideal for cleansing and transforming data before loading into a dedicated SQL pool.

Why this answer

Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless.

Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Practice this question →
134
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

A.Keep round-robin distribution but increase the degree of parallelism.
B.Change the distribution to hash on multiple columns.
C.Change the distribution to hash on the column that is most frequently used in joins.
D.Rebuild the table as a heap to improve insert performance.
AnswerC

Hash distribution on a join key reduces data shuffling.

Why this answer

Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling.

Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Practice this question →
135
MCQeasy

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

A.Ensure the Integration Runtime is registered and online
B.Check if the Blob Storage endpoint is accessible from the Integration Runtime
C.Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled
D.Verify that the SQL Server login credentials are correct
AnswerC

Timeout often indicates network blocking or SQL Server not listening on TCP/IP.

Why this answer

The 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Exam trap

The trap here is that candidates assume a 'Connection timed out' error is always a firewall or network issue, overlooking the SQL Server-side protocol configuration that must be explicitly enabled for remote TCP connections.

How to eliminate wrong answers

Option A is wrong because the question states that the Integration Runtime is already verified as running, so re-checking its registration and online status is redundant and does not address the timeout. Option B is wrong because the error is a connection timeout to SQL Server, not to Blob Storage; the pipeline fails before data transfer begins, so Blob Storage accessibility is irrelevant at this stage. Option D is wrong because incorrect login credentials would result in an authentication error (e.g., 'Login failed'), not a 'Connection timed out' error, which is a network-level issue.

Practice this question →
136
MCQhard

You are designing a near-real-time data processing solution for a retail company. The source is a Kafka cluster on-premises. The target is an Azure Synapse Dedicated SQL Pool. The solution must handle up to 10,000 events per second with less than 5-minute latency. Which Azure service should you use to ingest the data?

A.Azure Event Hubs (with Kafka protocol support)
B.Azure Data Lake Storage Gen2
C.Azure IoT Hub
D.Azure Stream Analytics
AnswerA

Event Hubs supports Kafka protocol and can ingest 10K events/sec with low latency.

Why this answer

Azure Event Hubs with Kafka protocol support is the correct choice because it provides a fully managed, high-throughput data ingestion service that can handle up to 10,000 events per second with sub-second latency, and it natively supports the Kafka protocol, allowing direct integration with your on-premises Kafka cluster without custom code or additional gateways. This meets the near-real-time requirement (<5-minute latency) and scales to the specified throughput.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics as an ingestion service, but it is a processing engine that requires an ingestion layer (like Event Hubs) first, and they may overlook that Azure Event Hubs natively supports the Kafka protocol, making it the direct replacement for Kafka ingestion in Azure.

How to eliminate wrong answers

Option B (Azure Data Lake Storage Gen2) is wrong because it is a hierarchical file store designed for batch analytics and data lake storage, not a real-time event ingestion service; it cannot natively consume Kafka streams or provide sub-5-minute latency for streaming data. Option C (Azure IoT Hub) is wrong because it is optimized for device-to-cloud telemetry from IoT devices, not for high-throughput event streams from a Kafka cluster, and it imposes device identity and throttling limits that are unsuitable for 10,000 events per second from a non-IoT source. Option D (Azure Stream Analytics) is wrong because it is a stream processing engine that requires an input source (like Event Hubs) to ingest data; it cannot directly ingest from Kafka on-premises and is not an ingestion service itself.

Practice this question →
137
MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

A.Azure Data Factory
B.Azure Blob Storage
C.Azure Event Hubs
D.Azure Analysis Services
AnswerC

Event Hubs is designed for streaming data ingestion and works with Synapse.

Why this answer

Azure Event Hubs is a big data streaming platform and event ingestion service that integrates with Synapse Analytics for streaming data. Option A is incorrect because Azure Data Factory is primarily for batch data integration, not real-time streaming. Option B is incorrect because Azure Blob Storage is a storage service, not an ingestion service.

Option D is incorrect because Azure Analysis Services is for semantic modeling, not streaming ingestion.

Practice this question →
138
MCQmedium

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

A.Azure Batch
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Synapse Pipelines
AnswerB

Stream Analytics provides real-time stream processing with SQL-like queries.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Synapse Pipelines with stream processing because they support 'real-time' triggers, but these services are fundamentally batch-oriented and cannot achieve the sub-second latency required for continuous stream aggregation.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a batch processing service for running large-scale parallel jobs, not designed for near real-time stream processing; it introduces significant latency due to job scheduling and queuing. Option C is wrong because Azure Data Factory is an ETL and data orchestration service focused on batch data movement and transformation, lacking native support for continuous stream processing. Option D is wrong because Azure Synapse Pipelines are built on the same orchestration engine as Data Factory and are intended for batch-oriented workflows, not for real-time stream aggregation.

Practice this question →
139
MCQmedium

Your company uses Azure Synapse Analytics to run a data warehouse. You have a dedicated SQL pool with a hash-distributed fact table named Sales. The distribution column is ProductID. You notice that queries against the Sales table are slow due to data skew. After analysis, you find that a few products (e.g., ProductID 100, 200) account for 80% of the rows. You need to optimize query performance without redesigning the entire table. You also need to minimize data movement during queries. Which action should you take?

A.Change the distribution to round-robin.
B.Increase the number of distributions to 120.
C.Change the distribution to replicate for the Sales table.
D.Create non-clustered indexes on the ProductID column.
AnswerA

Correct. Round-robin distribution evenly distributes rows, eliminating skew. Although it can increase data movement during joins, it is an effective fix for severe skew without redesign.

Why this answer

Round-robin distribution distributes data evenly across distributions, eliminating skew. However, it may increase data movement for joins. Given the severe skew, round-robin is a reasonable trade-off.

Option B is wrong because increasing the number of distributions does not fix skew. Option C is wrong because changing to replicate distribution is not suitable for large fact tables. Option D is wrong because creating non-clustered indexes does not address distribution skew.

Practice this question →
140
MCQhard

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

A.Enable staging and use PolyBase as the copy method for the sink.
B.Change the copy behavior to 'sequential' to reduce load on the source.
C.Increase the degree of copy parallelism (DOP) to the maximum value supported.
D.Split the source data into multiple smaller files and use multiple copy activities running in parallel.
AnswerA

Staging with PolyBase dramatically improves performance for large data loads.

Why this answer

Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Exam trap

The trap here is that candidates often assume increasing parallelism (DOP) or splitting data into multiple activities is always better, but they overlook that PolyBase's MPP integration with Synapse is the only option that fundamentally changes the data loading mechanism from a serial to a parallel bulk operation.

How to eliminate wrong answers

Option B is wrong because changing the copy behavior to 'sequential' would reduce parallelism and increase the copy duration, not reduce it. Option C is wrong because increasing the degree of copy parallelism (DOP) to the maximum value supported can cause resource contention and throttling on the source Azure SQL Database, often leading to diminishing returns or even slower performance. Option D is wrong because splitting the source data into multiple smaller files and using multiple copy activities running in parallel would require additional orchestration and staging, and without PolyBase or staging, each copy activity would still use the slow row-by-row INSERT method, making it less effective than a single PolyBase-based load.

Practice this question →
141
MCQeasy

Refer to the exhibit. You have created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. When you query the external table, you get an error that the external table is not accessible. What should you check first?

A.Check that the external table's LOCATION path is relative to the container and does not start with a slash.
B.Verify that the serverless SQL pool has been granted the 'Storage Blob Data Reader' role on the storage account.
C.Ensure that the external file format is correctly referencing the Parquet format.
D.Confirm that the Snappy compression codec is supported by the serverless SQL pool.
AnswerB

The serverless SQL pool needs read permissions on the storage account to access the data.

Why this answer

The error 'external table is not accessible' in Azure Synapse serverless SQL pool typically indicates an authorization failure when the SQL pool attempts to read the underlying Parquet files in Azure Data Lake Storage Gen2. Serverless SQL pool uses its own service identity to access storage, and it must be granted the 'Storage Blob Data Reader' role on the storage account at the storage account scope to have read permissions. Without this role assignment, the SQL pool cannot authenticate to the storage, resulting in the access error.

Exam trap

The trap here is that candidates often confuse 'external table not accessible' with file path or format issues, but the error message specifically points to a permissions/authorization problem, not a configuration or syntax error.

How to eliminate wrong answers

Option A is wrong because the LOCATION path in an external table for serverless SQL pool must be relative to the container and should not start with a slash; however, an incorrect path format would cause a 'file not found' or 'path does not exist' error, not an 'external table is not accessible' error which is specifically about permissions. Option C is wrong because if the external file format incorrectly references the Parquet format, the error would be about format mismatch or parsing failure (e.g., 'Cannot parse file'), not about table accessibility. Option D is wrong because Snappy compression is fully supported by serverless SQL pool for Parquet files; an unsupported codec would cause a decompression error, not an access-denied error.

Practice this question →
142
MCQhard

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

A.Enable staging using Azure Blob Storage and use PolyBase to load into Synapse
B.Increase the Data Integration Units (DIU) for the copy activity
C.Change the copy method to 'Bulk insert'
D.Set the Fault Tolerance option to skip incompatible rows
AnswerA

Staging improves performance by using parallel uploads to Blob Storage.

Why this answer

When network bandwidth is limited, staging data in Azure Blob Storage allows the copy activity to use PolyBase, which leverages Azure's internal high-speed network for the final load into Synapse. This bypasses the constrained on-premises-to-cloud link for the bulk of the data transfer, significantly improving throughput.

Exam trap

The trap here is that candidates assume increasing DIU or changing the copy method directly speeds up data movement, when in fact the real bottleneck is the network link, and only staging with PolyBase offloads the heavy data transfer to Azure's internal network.

How to eliminate wrong answers

Option B is wrong because Data Integration Units (DIU) control parallelism within the copy activity but do not address the underlying network bandwidth bottleneck; increasing DIU on a constrained link can actually worsen contention. Option C is wrong because 'Bulk insert' is the default method for loading into Synapse and does not change the data path; it still sends all data over the limited network connection. Option D is wrong because Fault Tolerance skips incompatible rows to avoid failures, but it has no impact on data transfer speed or network utilization.

Practice this question →
143
MCQmedium

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

A.Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations
B.Cache the data in memory after reading
C.Use bucketing with a fixed number of buckets
D.Partition the data by a high-cardinality column
AnswerA

Z-ordering co-locates related data, reducing data shuffling.

Why this answer

Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Exam trap

The trap here is that candidates often confuse partitioning (which can increase shuffle) with Z-ordering (which reduces shuffle by improving data locality without creating new partitions), leading them to choose bucketing or high-cardinality partitioning as a solution for shuffle minimization.

How to eliminate wrong answers

Option B is wrong because caching data in memory after reading only speeds up repeated access to the same data but does not reduce shuffle operations during transformations or aggregations; shuffle is caused by data movement across partitions, not by I/O latency. Option C is wrong because bucketing with a fixed number of buckets can actually increase shuffle operations if the bucketing columns do not align with the join or aggregation keys, and it does not inherently minimize shuffle; it is primarily used for optimizing joins and aggregations when the number of buckets matches the cluster parallelism. Option D is wrong because partitioning by a high-cardinality column (e.g., a column with many unique values) creates many small partitions, which leads to excessive shuffle overhead and task scheduling inefficiency, increasing rather than minimizing shuffle operations.

Practice this question →
144
MCQmedium

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data from multiple sources. You need to ensure that the data is encrypted at rest and in transit, and that access to the data is audited. Which combination of Azure services should you use?

A.Azure Data Lake Storage (encrypted at rest), Azure Synapse Analytics (TDE and SSL), and Microsoft Purview
B.Azure Blob Storage (encrypted at rest), Azure HDInsight, and Azure Log Analytics
C.Azure SQL Database (TDE and SSL), Azure Analysis Services, and Microsoft Purview
D.Azure Data Lake Storage (encrypted at rest), Microsoft Fabric, and Azure Monitor
AnswerA

Azure Storage provides encryption at rest; Synapse supports TDE for at-rest and SSL for in-transit; Purview provides data lineage and auditing.

Why this answer

Azure Data Lake Storage provides encryption at rest using Azure Storage Service Encryption (SSE) with 256-bit AES, and Azure Synapse Analytics supports Transparent Data Encryption (TDE) for at-rest encryption and SSL/TLS for in-transit encryption. Microsoft Purview enables data governance and auditing by capturing lineage, classification, and access activity logs, meeting the compliance requirements for sensitive financial data.

Exam trap

The trap here is that candidates often confuse Azure Monitor or Log Analytics with Microsoft Purview for auditing, but Microsoft Purview is the dedicated service for data governance, classification, and access auditing, while Azure Monitor is for operational monitoring and does not provide data-level audit trails.

How to eliminate wrong answers

Option B is wrong because Azure HDInsight does not natively enforce encryption at rest for data stored in its managed disks or external storage without additional configuration, and Azure Log Analytics focuses on monitoring and diagnostics rather than auditing data access at the granularity required for sensitive customer data. Option C is wrong because Azure Analysis Services does not provide built-in auditing of data access at the storage level; it is an analytical engine that relies on underlying data sources for encryption, and Microsoft Purview is correctly included but the combination lacks a scalable storage layer for multiple sources. Option D is wrong because Microsoft Fabric is a unified analytics platform that does not inherently provide the same level of granular access auditing as Microsoft Purview, and Azure Monitor is designed for infrastructure monitoring, not data access auditing.

Practice this question →
145
MCQmedium

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

A.Implement workload management to prioritize queries.
B.Use OPENROWSET with explicit file path filtering.
C.Enable result-set caching on the serverless SQL pool.
D.Create materialized views on the Parquet files.
AnswerB

OPENROWSET with path filtering prunes partitions and improves performance.

Why this answer

Using OPENROWSET with explicit file path filtering allows partition pruning by limiting the scan to the specified folders, thus improving query performance for frequently accessed partitions. Option A is wrong because workload management is a feature for dedicated SQL pool, not serverless. Option C is wrong because result-set caching is not supported in serverless SQL pool; it is a feature of dedicated SQL pool.

Option D is wrong because materialized views are not supported in serverless SQL pool.

Practice this question →
146
MCQmedium

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

A.The core count of 8 is insufficient for the data flow.
B.The TTL setting is too low, causing the cluster to be recreated frequently.
C.The AutoResolve location cannot be used for Managed IR.
D.The integration runtime type should be 'Self-Hosted' for data flows.
AnswerB

Low TTL leads to frequent cluster teardown and startup delays.

Why this answer

The first data flow execution takes a long time because the TTL (time-to-live) setting of 10 minutes causes the cluster to be deallocated shortly after the previous run. When a new data flow starts after the TTL expires, a new cluster must be provisioned from scratch, which adds significant startup latency. A higher TTL (e.g., 60 minutes) would keep the cluster warm for subsequent executions, reducing cold-start delays.

Exam trap

The trap here is that candidates may attribute the slow first execution to insufficient compute resources (Option A) rather than recognizing that the TTL setting directly controls cluster reuse and cold-start latency.

How to eliminate wrong answers

Option A is wrong because the core count of 8 is a default value and is not inherently insufficient; the issue is cluster startup time, not compute capacity. Option C is wrong because 'AutoResolve' is a valid and recommended location setting for a Managed integration runtime in Azure Synapse, as it automatically selects the optimal region. Option D is wrong because a Self-Hosted IR is not required for data flows; Managed IR is the correct and supported runtime type for executing data flows in Azure Synapse.

Practice this question →
147
MCQhard

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

A.The dependency condition should be 'Completed' instead of 'Succeeded'.
B.The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
C.The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.
D.The CopyData activity runs before the WatermarkQuery activity completes.
AnswerC

The copy query filters for rows where LastModified > NewWatermark, but NewWatermark is the maximum, so no rows satisfy the condition. The previous watermark should be stored and used.

Why this answer

The WatermarkQuery activity retrieves the maximum LastModified value from the source. If the CopyData activity's source query filters for rows where LastModified equals that value (e.g., using a parameter reference), it will only copy rows with that exact timestamp. In an incremental load scenario, the correct filter should be LastModified greater than the previous watermark value, not equal to the current maximum.

Since the current maximum is the highest timestamp, no rows will have a timestamp greater than it, resulting in zero rows copied. Option A is wrong because the dependency condition 'Succeeded' is appropriate and the pipeline succeeded. Option B is wrong because the WatermarkQuery activity succeeded (no failure indicated).

Option D is wrong because the WatermarkQuery activity runs before the CopyData activity due to the dependency, so no ordering issue exists.

Practice this question →
148
MCQmedium

You are designing a data processing pipeline in Azure Synapse Analytics that ingests streaming data from Azure Event Hubs and stores it in a dedicated SQL pool. The data must be available for querying within 5 minutes of ingestion. Which processing approach should you recommend?

A.Use Azure Data Factory with a tumbling window trigger set to 5 minutes.
B.Use Azure Stream Analytics with a dedicated SQL pool output and configure a 1-minute window.
C.Use PolyBase to load data from Event Hubs into the dedicated SQL pool every 5 minutes.
D.Use Spark Structured Streaming in Azure Synapse to write micro-batches every 5 minutes.
AnswerB

Stream Analytics provides sub-minute latency and is designed for real-time ingestion into Synapse dedicated SQL pool.

Why this answer

Azure Stream Analytics is purpose-built for real-time stream processing and can output directly to a dedicated SQL pool. By configuring a 1-minute window, you ensure data is materialized in the SQL pool well within the 5-minute SLA, meeting the latency requirement with headroom.

Exam trap

The trap here is that candidates confuse batch-oriented tools (Data Factory, PolyBase) or general-purpose streaming frameworks (Spark Structured Streaming) with the dedicated, low-latency stream processing service (Stream Analytics) that is optimized for sub-minute latency to Synapse SQL pools.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory with a tumbling window trigger is a batch-oriented orchestration tool, not a streaming engine; it cannot process Event Hubs data in near real-time and introduces at least 5 minutes of latency before the trigger even fires. Option C is wrong because PolyBase is a bulk-load technology for reading external data sources like Azure Blob Storage or Data Lake, not for ingesting streaming data from Event Hubs; it cannot connect to Event Hubs directly. Option D is wrong because Spark Structured Streaming in Synapse writes micro-batches every 5 minutes, which meets the 5-minute SLA only at the boundary; any processing delay or checkpoint overhead could push latency beyond the requirement, and it lacks the native, low-latency integration with dedicated SQL pool that Stream Analytics provides.

Practice this question →
149
MCQmedium

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

A.The user does not have read permission on the table.
B.The table is not registered in the Spark metastore; it is only in the lakehouse.
C.The Parquet file format is incompatible with the Delta source.
D.The Delta table is corrupted.
AnswerB

In Synapse, lakehouse tables are stored in a separate catalog; the Spark session's default catalog may not include it, so the table is not found.

Why this answer

In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Exam trap

Microsoft often tests the misconception that a table existing in the lakehouse automatically makes it visible to Spark notebooks, when in fact the Spark metastore and lakehouse catalog are separate metadata layers that must be explicitly synchronized.

How to eliminate wrong answers

Option A is wrong because an AnalysisException for 'Table or view not found' is a metadata resolution error, not a permission error; a lack of read permission would typically produce a SecurityException or AccessDeniedException. Option C is wrong because Parquet and Delta are both columnar formats based on Parquet, and writing to Parquet from a Delta source does not cause incompatibility; the error occurs before any read/write operation begins. Option D is wrong because a corrupted Delta table would cause read failures (e.g., file not found, checksum mismatch) during data access, not a table-not-found error at the metadata level.

Practice this question →
150
MCQeasy

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

A.Run CONVERT TO DELTA on the underlying Parquet files.
B.Run OPTIMIZE to compact small files.
C.Run DESCRIBE HISTORY to analyze file sizes.
D.Run VACUUM to delete old files.
AnswerB

OPTIMIZE compacts small files into larger ones, improving read performance.

Why this answer

The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Exam trap

The trap here is that candidates confuse VACUUM (which cleans up old files) with OPTIMIZE (which compacts files), or think DESCRIBE HISTORY is a performance-tuning command rather than a diagnostic tool.

How to eliminate wrong answers

Option A is wrong because CONVERT TO DELTA is used to convert existing Parquet files into a Delta table format, not to compact small files within an already existing Delta table. Option C is wrong because DESCRIBE HISTORY only shows the transaction log of operations performed on the table, such as writes and compactions; it does not modify or optimize file sizes. Option D is wrong because VACUUM removes old, unreferenced data files that are no longer needed for time travel or rollback, but it does not compact or merge small files into larger ones.

Practice this question →
← PreviousPage 2 of 4 · 261 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Develop Data Processing questions.

Start 20-question session