Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 76150

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

Page 1

Page 2 of 11

Page 3
76
MCQhard

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

A.Increase the number of Streaming Units (SUs) allocated to the job.
B.Decrease the watermark delay interval.
C.Increase the late arrival tolerance window.
D.Increase the number of partitions in the output table.
AnswerA

More SUs provide more processing capacity, reducing latency.

Why this answer

The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Exam trap

The trap here is that candidates often confuse output-side tuning (like partitioning or sink configuration) with the actual processing bottleneck, overlooking that high SU% utilization directly indicates the Stream Analytics job itself is the limiting factor.

How to eliminate wrong answers

Option B is wrong because decreasing the watermark delay interval would make the job emit results more frequently, but it does not increase processing capacity; with SU utilization already at 90%, this could worsen backpressure and increase latency. Option C is wrong because increasing the late arrival tolerance window only allows the job to handle out-of-order events for a longer period; it does not improve throughput or reduce latency caused by resource saturation. Option D is wrong because increasing the number of partitions in the output table improves write parallelism on the Synapse side, but the bottleneck is the Stream Analytics job's processing capacity (90% SU utilization), not the output sink's partitioning.

77
MCQmedium

You are designing a data processing solution that requires running custom Python scripts for data transformation. The scripts have dependencies on specific libraries that are not pre-installed in the environment. You need a fully managed, serverless compute option that allows you to install custom libraries. Which service should you use?

A.Azure Databricks with cluster-scoped libraries
B.Azure Functions with custom handlers
C.Azure Container Instances with a custom Docker image
D.Azure Synapse Analytics serverless SQL pool
AnswerA

Databricks allows installing libraries at cluster level and is fully managed.

Why this answer

Azure Databricks with cluster-scoped libraries is the correct choice because it provides a fully managed, serverless Apache Spark environment where you can install custom Python libraries (e.g., via PyPI or Maven) at the cluster level. This allows your custom Python scripts to run with all required dependencies without managing any infrastructure, directly meeting the requirement for a serverless compute option with custom library support.

Exam trap

The trap here is that candidates often confuse serverless compute with any service that can run code, but Azure Functions lacks native support for installing arbitrary Python libraries without custom containerization, while Azure Databricks provides a purpose-built, managed environment for data processing with easy library management.

How to eliminate wrong answers

Option B is wrong because Azure Functions with custom handlers is a serverless compute option, but it does not natively support installing arbitrary Python libraries via package managers like pip; you would need to bundle dependencies in a deployment package or use a custom container, which adds complexity and is not as straightforward as cluster-scoped libraries. Option C is wrong because Azure Container Instances with a custom Docker image is not fully managed in the sense of serverless compute for data transformation; you must build, maintain, and version the Docker image yourself, and it lacks the integrated cluster management and auto-scaling of a data processing service like Databricks. Option D is wrong because Azure Synapse Analytics serverless SQL pool is designed for T-SQL queries over data in data lakes, not for running custom Python scripts with library dependencies; it does not support installing Python libraries or executing arbitrary Python code.

78
Drag & Dropmedium

Drag and drop the steps to convert data from CSV to Parquet format using Azure Data Factory into the correct order.

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

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

Why this order

Define source (CSV) and sink (Parquet) datasets, then a copy activity with mapping, run, and monitor.

79
MCQmedium

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

A.Use a ForEach activity to iterate over a fixed number of pages.
B.Use a Lookup activity to retrieve the total number of pages and then use a ForEach.
C.Use an Until activity to loop until the API returns no more pages.
D.Use a Copy activity with pagination rules enabled in the source.
AnswerD

The Copy activity supports pagination for REST APIs using pagination rules (e.g., next URL).

Why this answer

The Copy activity in Azure Data Factory supports pagination rules, allowing it to automatically iterate through API pages without a separate loop activity. Option A is incorrect because a ForEach activity would need to know the exact number of pages upfront, which is not dynamic. Option B is incorrect because the Lookup activity retrieves a single value and cannot iterate through pages.

Option C is incorrect because an Until activity would require custom logic to check for the next page, whereas the Copy activity with pagination rules handles this natively.

Exam trap

A common mistake is to think you need a loop activity (ForEach or Until) to handle pagination, but the Copy activity can be configured with pagination rules to automatically page through REST API responses.

80
MCQhard

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

A.Enable PolyBase in the Copy activity sink settings.
B.Increase the Data Integration Units (DIU) in the Copy activity to the maximum.
C.Increase the dedicated SQL pool's DWU setting to the highest tier.
D.Partition the staging table on a date column.
AnswerA

PolyBase provides the fastest way to load data into dedicated SQL pool by leveraging its parallel architecture.

Why this answer

Enabling PolyBase in the Copy activity sink settings allows the data to be loaded into the dedicated SQL pool using the high-throughput PolyBase technology, which leverages the SQL pool's distributed architecture and can read data directly from Azure Blob Storage in parallel. This bypasses the bottleneck of row-by-row inserts, drastically reducing load time for large datasets like 500 GB.

Exam trap

The trap here is that candidates often assume scaling up resources (DIU or DWU) is the universal fix for slow pipelines, but the real bottleneck is the sink write method, and PolyBase is the only option that changes the data loading protocol from row-by-row to bulk parallel loading.

How to eliminate wrong answers

Option B is wrong because increasing Data Integration Units (DIU) in the Copy activity only scales the compute resources used by the Azure Integration Runtime for data movement, but the primary bottleneck here is the sink-side write performance to the dedicated SQL pool, not the source read or network throughput. Option C is wrong because increasing the DWU setting of the dedicated SQL pool improves overall query performance but does not address the fundamental issue that the Copy activity is using a slow, row-by-row insert method; the sink write path remains the bottleneck. Option D is wrong because partitioning the staging table on a date column does not improve the throughput of the initial data load; partitioning is beneficial for query pruning and maintenance operations, not for accelerating bulk inserts into a clustered columnstore index.

81
MCQhard

A data engineer runs the Azure CLI command shown in the exhibit. The blob is stored in Azure Blob Storage. The team previously set a lifecycle management rule to move blobs to the Archive tier after 30 days. The blob was created 45 days ago. What is the most likely reason the blob is still in the Cool tier?

A.The lifecycle management rule only applies to blobs in the Hot tier.
B.The lifecycle management rule was configured after the blob was created, and it can take up to 24 hours for the rule to be evaluated.
C.The blob is in a container that is excluded from the lifecycle rule.
D.The blob must be in the Hot tier for the rule to move it to Archive.
AnswerB

Correct. Lifecycle rules are evaluated once per day, so there may be a delay.

Why this answer

The lifecycle management rule is evaluated by Azure Storage once per day. If the rule was configured after the blob was created, it may not have been evaluated yet, and it can take up to 24 hours for the rule to apply. Since the blob is 45 days old and still in Cool tier, the most likely reason is that the rule has not yet been evaluated after its configuration.

Exam trap

The trap here is that candidates assume lifecycle rules are evaluated immediately or that blobs must be in Hot tier to be moved to Archive, but Azure's daily evaluation cycle and the ability to move from Cool to Archive are the key nuances tested.

How to eliminate wrong answers

Option A is wrong because lifecycle management rules can apply to blobs in any tier (Hot, Cool, or Archive) unless explicitly filtered by tier in the rule definition. Option C is wrong because there is no indication in the scenario that the container is excluded; the question states a rule was set, and exclusion would require explicit configuration. Option D is wrong because lifecycle rules can move blobs from Cool to Archive directly; blobs do not need to be in Hot tier first.

82
MCQeasy

You are designing a data storage solution for a marketing analytics platform. The platform collects clickstream data from websites and needs to store it for both real-time dashboards and historical analysis. The data is semi-structured (JSON) and arrives at a rate of 10,000 events per second. You need to choose an Azure storage solution that can handle the ingestion rate, support schema-on-read, and integrate with Azure Databricks for advanced analytics. The solution must also be cost-effective for long-term storage. What should you use?

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

ADLS Gen2 meets all requirements: high throughput, schema-on-read, Databricks integration, cost-effective.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, providing high-throughput ingestion (up to 60 GB/s per account) to handle 10,000 events per second of semi-structured JSON data. It supports schema-on-read natively, allowing Azure Databricks to query the data directly using Spark without prior schema definition, and its tiered storage (hot, cool, archive) makes it cost-effective for long-term historical analysis.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its real-time capabilities, overlooking that the question emphasizes cost-effective long-term storage and schema-on-read for historical analysis, which ADLS Gen2 handles far more efficiently and cheaply than Cosmos DB's per-request-unit pricing model.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for structured data with a fixed schema, not for semi-structured JSON clickstream data, and it lacks the hierarchical namespace and high-throughput ingestion needed for 10,000 events per second. Option C is wrong because Azure Cosmos DB is optimized for low-latency real-time access with its multi-model API, but it is significantly more expensive for long-term storage of high-volume historical data and does not natively integrate with Azure Databricks for schema-on-read analytics as efficiently as ADLS Gen2. Option D is wrong because Azure SQL Database is a relational database requiring a predefined schema (schema-on-write), which conflicts with the schema-on-read requirement, and its ingestion rate and cost model are not designed for high-velocity semi-structured data at 10,000 events per second.

83
MCQmedium

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The template defines a managed virtual network integration runtime. You need to ensure that the integration runtime can run mapping data flows with a time-to-live (TTL) of 10 minutes. What is the purpose of the 'timeToLive' property in this configuration?

A.It defines how long the cluster will be kept alive after a data flow completes, allowing subsequent data flows to reuse the cluster.
B.It sets the timeout for the integration runtime to connect to the data sources.
C.It specifies the maximum duration a data flow activity can run before timing out.
D.It determines the maximum number of concurrent data flows that can run on the cluster.
AnswerA

TTL keeps the cluster warm for reuse, reducing startup time.

Why this answer

The 'timeToLive' property in an Azure Synapse Analytics managed virtual network integration runtime controls how long the cluster remains alive after a mapping data flow completes. By setting a TTL of 10 minutes, subsequent data flows can reuse the same warm cluster, avoiding the 5–10 minute cold start time for new clusters. This optimizes performance and reduces latency for consecutive data flow executions.

Exam trap

The trap here is that candidates confuse 'timeToLive' with activity timeout or concurrency limits, because all three involve time or capacity constraints, but TTL specifically governs cluster reuse after a data flow completes, not execution duration or parallelism.

How to eliminate wrong answers

Option B is wrong because the connection timeout to data sources is configured separately via linked service properties or the 'connectVia' runtime settings, not through the 'timeToLive' property. Option C is wrong because the maximum duration a data flow activity can run is set by the activity's 'timeout' property in the pipeline, not by the integration runtime's TTL. Option D is wrong because the maximum number of concurrent data flows is controlled by the 'concurrency' property on the integration runtime, not by 'timeToLive'.

84
Multi-Selecthard

Which THREE security features are available in Azure Data Lake Storage Gen2 to protect data at rest and in transit? (Choose three.)

Select 3 answers
A.Azure Storage firewalls and virtual network rules
B.Azure Information Protection
C.Encryption at rest using Storage Service Encryption (SSE)
D.Azure ADLS Gen2 supports HTTPS for data in transit.
E.Azure Policy
AnswersA, C, D

Restrict access to specific networks.

Why this answer

Options A, C, and D are correct. Azure Storage firewalls and virtual network rules provide network-level security to restrict access. Encryption at rest using Storage Service Encryption (SSE) protects data at rest with AES-256 encryption.

HTTPS ensures data in transit is encrypted. Option B (Azure Information Protection) is a classification and labeling service, not a built-in storage security feature. Option E (Azure Policy) is for governance and compliance, not direct data protection.

85
MCQhard

Refer to the exhibit. You deploy the ARM template to create a storage account. After deployment, a developer reports that they cannot access the storage account from an Azure Databricks notebook running in the same virtual network as specified in the subnet rule. What is the most likely reason?

A.The storage account uses customer-managed keys from Key Vault, which requires additional permissions.
B.The firewall default action is Deny, and there are no IP rules allowing the Databricks cluster's public IP.
C.The Databricks cluster is not using a managed virtual network with a service endpoint to the storage account.
D.The storage account has hierarchical namespace enabled, which blocks non-ADLS Gen2 tools.
AnswerC

Correct. The most likely reason is that the Databricks cluster is not using a managed virtual network with a service endpoint to the storage account, so traffic from the cluster is not recognized as coming from the allowed subnet.

Why this answer

The storage account firewall is configured with a default deny and a subnet rule allowing traffic from a specific virtual network subnet. For an Azure Databricks notebook to access the storage account, the Databricks workspace must be deployed with a managed virtual network and the cluster must use that same subnet with a service endpoint to the storage account. If the Databricks cluster is not using a managed virtual network or the subnet is not correctly configured, the traffic will be blocked.

Option A is incorrect because customer-managed keys from Key Vault do not block access; they are for encryption. Option B is incorrect because the firewall's default deny is expected, but the subnet rule should allow traffic from the Databricks VNet, not the cluster's public IP (Databricks clusters in a managed VNet use private IPs). Option D is incorrect because hierarchical namespace (ADLS Gen2) does not block access; it enables it.

86
MCQhard

Refer to the exhibit. An Azure Data Factory instance uses a self-hosted integration runtime. The exhibit shows the properties of the integration runtime. The data engineer notices that copy activities are failing with errors indicating that the integration runtime is not available. What is the most likely cause?

A.The integration runtime status is "Offline"
B.Auto-update is disabled, preventing the IR from updating
C.The integration runtime version is outdated and needs to be manually updated
D.Self-contained interactive authoring is disabled, causing connectivity issues
AnswerC

The version is behind the pushed version, indicating auto-update has not applied the latest update.

Why this answer

The exhibit shows the integration runtime version as '5.24.8345.1' and the status as 'Online', but copy activities are failing. The most likely cause is that the self-hosted IR version is outdated and no longer compatible with the Azure Data Factory service endpoints, leading to connectivity failures. Auto-update being disabled (Option B) would prevent automatic updates, but the core issue is the outdated version itself, which requires manual intervention to update.

Exam trap

The trap here is that candidates see the status 'Online' and assume the IR is fully functional, overlooking that version incompatibility can cause operational failures even when the IR appears connected.

How to eliminate wrong answers

Option A is wrong because the exhibit clearly shows the integration runtime status as 'Online', not 'Offline', so the IR is technically reachable. Option B is wrong because while auto-update being disabled can lead to an outdated version, the question asks for the most likely cause of the copy activity failures, and the direct cause is the outdated version (Option C), not the disabled auto-update setting itself. Option D is wrong because self-contained interactive authoring is a feature for authoring and debugging in the self-hosted IR environment, and disabling it does not cause the IR to become unavailable for copy activities; it only affects authoring capabilities.

87
Multi-Selectmedium

A company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. They notice that some queries are using more memory than expected, causing resource contention. Which TWO actions should they take to diagnose and optimize memory usage?

Select 2 answers
A.Enable result-set caching.
B.Increase the resource class for the users running the heavy queries.
C.Scale up the DWU setting.
D.Query the sys.dm_pdw_exec_requests DMV to identify queries with high memory grants.
E.Rebuild clustered columnstore indexes.
AnswersB, D

Larger resource classes provide more memory per query.

Why this answer

Increasing the resource class for users running heavy queries allocates more memory to those queries, reducing resource contention by ensuring they have sufficient memory to execute efficiently. Option D is correct because querying sys.dm_pdw_exec_requests DMV allows you to identify queries with high memory grants, which is the first step in diagnosing which queries are consuming excessive memory and need optimization.

Exam trap

The trap here is that candidates often confuse scaling up the DWU (Option C) as a diagnostic action, but it is a reactive scaling measure that does not help identify which queries are causing the memory issue, whereas querying the DMV and adjusting resource classes are targeted diagnostic and optimization steps.

88
Multi-Selectmedium

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

Select 2 answers
A.Create nonclustered indexes on all columns of the target table
B.Use ROUND_ROBIN distribution for the staging table
C.Set the row group size to 100,000 rows for optimal compression
D.Enable change tracking on the target table
E.Use CREATE TABLE AS SELECT (CTAS) with partition switching
AnswersB, E

Round-robin distributes data evenly, speeding up loads.

Why this answer

Using ROUND_ROBIN distribution for a staging table ensures that data is evenly distributed across all distributions without any data movement, which is the fastest way to load data into a dedicated SQL pool. This distribution type is ideal for staging tables because it minimizes load time and avoids the overhead of hash distribution key computation during the initial data ingestion phase.

Exam trap

The trap here is that candidates often confuse the purpose of indexes and distribution types, mistakenly thinking that adding indexes on all columns will speed up loading, when in fact it degrades performance, and they overlook that ROUND_ROBIN is specifically designed for fast staging loads, not for query performance.

89
MCQhard

You are designing a near-real-time data processing solution that ingests millions of events per second from IoT devices. The data must be aggregated on a per-minute basis and stored in Azure Data Lake Storage Gen2 for long-term analytics. The solution must also support alerting when certain thresholds are exceeded. Which combination of Azure services should you use?

A.Azure Event Hubs, Azure Data Factory, and Azure SQL Database.
B.Azure IoT Hub, Azure Stream Analytics, and Azure Functions.
C.Azure IoT Hub, Azure Databricks with Structured Streaming, and Azure Data Lake Storage Gen2.
D.Azure Event Hubs, Azure Data Explorer, and Power BI.
AnswerB

IoT Hub ingests device data, Stream Analytics performs per-minute aggregation and alerting, and Functions can output to Data Lake Storage.

Why this answer

Azure IoT Hub is designed for secure, bidirectional communication with IoT devices and can ingest millions of events per second. Azure Stream Analytics provides low-latency, SQL-based stream processing to aggregate data on a per-minute basis and can output to Azure Data Lake Storage Gen2 for long-term analytics. Additionally, Stream Analytics can trigger Azure Functions for alerting when thresholds are exceeded, enabling near-real-time responses.

Exam trap

The trap here is that candidates often choose Azure Databricks (Option C) for streaming because of its popularity, but they overlook that Azure Stream Analytics is purpose-built for low-latency, SQL-based stream processing and alerting with minimal code, making it more appropriate for this near-real-time IoT scenario.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is a batch-oriented ETL service, not suited for near-real-time streaming ingestion or per-minute aggregation, and Azure SQL Database is not optimized for high-velocity IoT event ingestion or long-term analytics at scale. Option C is wrong because while Azure Databricks with Structured Streaming can handle streaming, it introduces higher latency and operational overhead compared to Azure Stream Analytics for simple per-minute aggregation and alerting, and it lacks native integration for alerting without additional services. Option D is wrong because Azure Data Explorer is optimized for interactive analytics on large volumes of data, not for real-time alerting or per-minute aggregation in a streaming pipeline, and Power BI alone cannot trigger alerts without additional components.

90
MCQmedium

Your team uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are running slower than expected. You need to improve query performance by reducing the amount of data scanned. Which two features should you implement? (Select two.)

A.Partition the data in the data lake and use partition elimination in queries.
B.Enable result-set caching for the serverless SQL pool.
C.Enable auto-optimize on the Delta Lake tables.
D.Create materialized views on the serverless SQL pool.
E.Use file pruning by specifying file paths in the OPENROWSET query.
AnswerA, E

Partition elimination allows the query to skip irrelevant partitions, reducing data scanned.

Why this answer

Options A and E are correct. Partition elimination (A) reduces data scanned by skipping irrelevant partitions. File pruning (E) reduces data scanned by reading only specified files via OPENROWSET.

Option B is wrong because result-set caching caches query results but does not reduce the amount of data scanned on the first execution. Option C is wrong because auto-optimize is a feature for Delta Lake tables and is not applicable to serverless SQL pool queries. Option D is wrong because materialized views are not supported in serverless SQL pool, and even if they were, they would not reduce the data scanned by the original query.

91
MCQeasy

Refer to the exhibit. You have a mapping data flow in Azure Data Factory that aggregates sales data. The data flow runs successfully but the sink table contains only the total sum per run instead of per product. What is missing?

A.The source dataset is not filtering by date
B.The aggregate transformation does not have a groupBy column
C.The data flow is missing a filter transformation
D.The sink dataset is not configured to append
AnswerB

Without groupBy, it aggregates all rows.

Why this answer

The aggregate transformation must include a groupBy column (e.g., ProductID) to produce per-product totals. Without a groupBy, the aggregate sums all rows into a single total per run. Option A is incorrect because filtering by date would not fix the lack of grouping.

Option C is incorrect because a filter transformation is unrelated to aggregation grouping. Option D is incorrect because the sink append mode does not affect the aggregation logic.

92
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that some queries are slow due to high data movement. What should you do to minimize data movement for queries that join large fact tables?

A.Use round-robin distribution for all tables.
B.Partition both tables on the join keys.
C.Hash-distribute the fact tables on the join keys.
D.Use replicated tables for all large fact tables.
AnswerC

Hash distribution on join keys colocates rows and minimizes data movement.

Why this answer

Hash-distributing the fact tables on the join keys ensures that rows with the same join key value are placed on the same distribution node. This eliminates the need to shuffle data across nodes during the join, minimizing data movement and improving query performance in Azure Synapse dedicated SQL pool.

Exam trap

The trap here is that candidates confuse partitioning with distribution, thinking that partitioning on join keys reduces data movement, when in fact only hash distribution on the join key ensures collocation across nodes.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering join keys, which does not reduce data movement for joins and can actually increase it. Option B is wrong because partitioning on join keys organizes data within a distribution but does not control data placement across distributions; data movement still occurs when joining across partitions. Option D is wrong because replicated tables are suitable for small dimension tables, not large fact tables, as replicating large tables would consume excessive storage and negate the benefits of scale-out.

93
Multi-Selecthard

Which THREE metrics should you monitor for an Azure Synapse Analytics dedicated SQL pool to ensure optimal performance?

Select 3 answers
A.tempdb usage
B.DWU usage
C.Queued queries
D.Login failures
E.Total storage size
AnswersA, B, C

High tempdb usage can slow queries.

Why this answer

Options A, B, and C are correct. tempdb usage (A) indicates contention and performance issues. DWU usage (B) shows resource utilization and can help determine if scaling is needed. Queued queries (C) indicate concurrency bottlenecks and performance degradation.

Option D (Login failures) is not a performance metric, it's security. Option E (Total storage size) is capacity monitoring, not performance.

94
MCQhard

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a security strategy that allows users to read only specific folders within a container. Which authorization method should you use?

A.Storage account shared key
B.Azure RBAC roles (e.g., Storage Blob Data Contributor) at the container level
C.Shared access signatures (SAS) with folder-level permissions
D.Access control lists (ACLs) on the folder
AnswerD

ACLs allow granular permissions at directory level

Why this answer

ACLs (Access Control Lists) in Azure Data Lake Storage Gen2 can be applied to individual folders, enabling granular read permissions. Option A is incorrect because a storage account shared key grants full access to the entire account. Option B is incorrect because Azure RBAC roles like Storage Blob Data Contributor apply at the container level, affecting all folders within.

Option C is incorrect because shared access signatures (SAS) can be scoped to a container or a file, but not to a specific folder within a container.

95
Multi-Selecthard

Which THREE factors should you consider when choosing between rowstore and columnstore indexes in Azure Synapse Analytics?

Select 3 answers
A.The table contains many NULL values in indexed columns.
B.The table will be partitioned frequently.
C.The table size is expected to be over 1 TB.
D.The table has a high number of singleton lookups by a primary key.
E.The workload is heavy on aggregations and large scans.
AnswersC, D, E

Columnstore compression is more effective on large tables.

Why this answer

Columnstore indexes in Azure Synapse Analytics are optimized for large-scale data warehousing workloads, where table sizes exceeding 1 TB benefit from high compression and columnar storage, significantly improving scan and aggregation performance. Rowstore indexes, in contrast, are less efficient for such large datasets due to higher I/O and storage overhead.

Exam trap

The trap here is that candidates may mistakenly think NULL handling or partitioning frequency are key differentiators, when in fact the core decision hinges on workload type—aggregations/scans (columnstore) versus singleton lookups (rowstore)—and table size thresholds like 1 TB where columnstore compression becomes critical.

96
Drag & Dropmedium

Drag and drop the steps to configure Azure Stream Analytics job with event input and Power BI output into the correct order.

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

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

Why this order

First, set up the event hub as the data source. Then create the Stream Analytics job, configure input and output, write the query, and start it.

97
MCQhard

You are building a batch processing solution in Azure Synapse Analytics that reads data from a dedicated SQL pool, applies complex transformations using Synapse Spark, and writes the results back to the dedicated SQL pool. The pipeline must run on a schedule and handle transient failures with retries. Which approach should you use?

A.Use Azure Batch with a custom application to run Spark jobs
B.Use Azure Synapse Pipelines with a Notebook activity that runs Spark code
C.Use Azure Functions to trigger Spark jobs on demand
D.Use Azure Databricks with Auto Loader and Delta Live Tables
AnswerB

Synapse Pipelines support scheduling and retries for Spark notebooks.

Why this answer

Azure Synapse Pipelines with a Notebook activity is the correct approach because it natively integrates Synapse Spark for complex transformations and supports scheduling and retry policies for transient failures. This allows you to read from a dedicated SQL pool, process data in Spark, and write back to the pool without external orchestration, leveraging the built-in pipeline reliability features.

Exam trap

The trap here is that candidates may confuse Azure Batch or Azure Functions as viable alternatives for Spark job orchestration, overlooking the native integration and retry capabilities of Synapse Pipelines within the same service.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a general-purpose batch computing service that does not natively integrate with Synapse Spark or dedicated SQL pools, requiring custom application development and lacking the seamless scheduling and retry capabilities of Synapse Pipelines. Option C is wrong because Azure Functions are event-driven and stateless, not designed for long-running Spark jobs or built-in retry logic for transient failures in a scheduled batch pipeline. Option D is wrong because Azure Databricks with Auto Loader and Delta Live Tables is a separate platform that does not natively integrate with Azure Synapse dedicated SQL pools, requiring additional connectivity setup and missing the unified orchestration provided by Synapse Pipelines.

98
MCQmedium

You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?

A.Round-robin distribution
B.Hash distribution on CustomerID
C.Replicate distribution
D.Hash distribution on Date
AnswerB

Hash distribution on CustomerID ensures collocation for joins on that key, reducing data movement.

Why this answer

Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.

Exam trap

The trap here is that candidates often assume partitioning and distribution should be on the same column (Date) to optimize date-range queries, but this ignores that distribution on the join key (CustomerID) is what minimizes data movement for the most common query pattern involving both CustomerID and Date filters.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without any logical grouping, causing every query that filters on CustomerID to require full data movement across all distributions. Option C is wrong because replicate distribution copies the entire table to each distribution node, which is impractical for a large fact table due to storage overhead and write performance degradation. Option D is wrong because hash distribution on Date would cause data movement for queries filtering on CustomerID, as the distribution key does not align with the join or filter column, and partitioning on Date already provides local pruning without needing distribution on the same column.

99
MCQmedium

You are designing a data storage solution for a real-time analytics application that ingests IoT sensor data. The data must be stored in a format that supports both streaming ingestion and batch processing with low latency for queries. Which Azure storage solution should you use?

A.Azure Blob Storage with hot access tier
B.Azure SQL Database with change data capture
C.Azure Cosmos DB with analytical store
D.Azure Data Lake Storage Gen2
AnswerD

Supports hierarchical namespace, streaming and batch ingestion, and low-latency queries through Azure Synapse.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, enabling both streaming ingestion (via Event Hubs, Kafka, or Spark Structured Streaming) and batch processing (via PolyBase, Azure Synapse, or Databricks) while supporting low-latency queries through its POSIX-like file system and optimized columnar formats like Parquet. It directly addresses the requirement for a unified storage layer that handles real-time and batch workloads without data movement.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage (option A) with ADLS Gen2, assuming the hot access tier supports streaming ingestion and low-latency queries, but they overlook the critical need for a hierarchical namespace and native batch processing capabilities that only ADLS Gen2 provides.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage with hot access tier lacks a hierarchical namespace, making it inefficient for low-latency queries and batch processing patterns that rely on directory structures and atomic rename operations; it is designed for object storage, not for real-time analytics with streaming ingestion. Option B is wrong because Azure SQL Database with change data capture is optimized for transactional workloads and relational queries, not for high-volume streaming ingestion of IoT sensor data, and it introduces latency and cost overhead for large-scale batch processing. Option C is wrong because Azure Cosmos DB with analytical store is designed for globally distributed, multi-model data with automatic indexing, but it is not optimized for batch processing with columnar formats like Parquet and incurs higher latency for large-scale analytical queries compared to ADLS Gen2's native integration with Spark and Synapse.

100
MCQeasy

You need to transform JSON data containing nested arrays into a tabular format for analysis in Azure Synapse Analytics. Which transformation in Azure Data Factory or Synapse Pipelines should you use?

A.Join transformation
B.Derived Column transformation
C.Aggregate transformation
D.Flatten transformation
AnswerD

Flatten is specifically designed to unroll nested array elements into separate rows in mapping data flows.

Why this answer

The Flatten transformation is specifically designed to denormalize nested JSON arrays into a tabular format by expanding array elements into multiple rows while preserving parent attributes. In Azure Data Factory and Synapse Pipelines, this transformation handles complex hierarchical structures like arrays of objects, making it the correct choice for converting JSON with nested arrays into a row-based dataset suitable for analysis in Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse the Flatten transformation with the Unpivot transformation, which pivots columns into rows but does not handle nested JSON arrays, or they mistakenly think the Derived Column transformation can handle array expansion through expressions.

How to eliminate wrong answers

Option A is wrong because the Join transformation combines two streams based on matching keys, not for flattening nested arrays within a single JSON document. Option B is wrong because the Derived Column transformation creates or modifies columns using expressions but cannot expand nested arrays into multiple rows. Option C is wrong because the Aggregate transformation performs grouping and summarization operations (e.g., SUM, COUNT) and does not handle the denormalization of nested array structures.

101
MCQhard

You are designing a data processing solution for a large e-commerce company. The data includes sensitive customer information that must be encrypted at rest and in transit. The solution uses Azure Data Lake Storage Gen2 and Azure Databricks. Compliance requires that all access to the data is logged and monitored. Which combination of security features should you implement?

A.Enable Azure RBAC for storage and use Azure AD authentication.
B.Use Azure Firewall to restrict access and enable Azure AD Authentication for Databricks.
C.Enable Azure Storage encryption at rest, enforce HTTPS, and configure diagnostic settings to log all read/write operations.
D.Enable transparent data encryption (TDE) on the storage account and use service endpoints.
AnswerC

Meets all requirements.

Why this answer

It covers encryption at rest (Azure Storage encryption), encryption in transit (HTTPS), and logging via diagnostic settings for read/write operations. Option A is incorrect because Azure RBAC does not log access details; it only controls permissions. Option B is incorrect because Azure Firewall restricts network traffic but does not provide logging of data access or encryption.

Option D is incorrect because Transparent Data Encryption (TDE) is a feature for Azure SQL databases, not for Azure Data Lake Storage Gen2.

102
MCQeasy

You need to monitor the performance of an Azure Stream Analytics job in real time. Which Azure service should you use to track the job's resource utilization (e.g., SU % utilization) and set up alerts when the job is approaching its capacity?

A.Azure Monitor
B.Azure Advisor
C.Microsoft Sentinel
D.Azure Log Analytics
AnswerA

Azure Monitor provides real-time metrics (e.g., SU % utilization) and alerting capabilities for Stream Analytics jobs.

Why this answer

Azure Monitor provides real-time metrics such as SU % utilization for Azure Stream Analytics jobs and allows you to set up alerts based on these metrics. Option B is incorrect because Azure Advisor offers best practice recommendations, not real-time monitoring and alerts. Option C is incorrect because Microsoft Sentinel is a SIEM tool for security analytics.

Option D is incorrect because Azure Log Analytics is used for log analysis, not primarily for real-time metric alerts on resource utilization.

103
MCQmedium

You are a data engineer at a retail company. You need to design a batch processing solution that ingests daily sales data from multiple stores. Each store uploads a CSV file to a dedicated folder in Azure Data Lake Storage Gen2. The files have the same schema but may have minor variations in column order and include null values. After ingestion, you must clean the data by removing rows with null values in the 'SalesAmount' column, convert the 'Date' column from string to date type, and aggregate sales by product category. The output should be stored as Parquet files partitioned by year and month in the same Data Lake. You need to choose a compute service and implement the transformation with minimal coding effort. The solution must be cost-effective and require no cluster management. What should you do?

A.Use Azure Synapse Serverless SQL pool. Create external tables over the CSV files, write a T-SQL query to filter, cast, aggregate, and use CETAS to write Parquet partitions.
B.Use Azure Synapse Dedicated SQL pool. Load CSV files via PolyBase, transform with T-SQL, and use CREATE TABLE AS SELECT to output partitioned Parquet.
C.Use Azure Data Factory with a Mapping Data Flow. Configure the source to read all CSV files from the ADLS Gen2 folder. Add a Filter transformation to remove null SalesAmount, a Derived Column to parse the Date, and an Aggregate to sum sales by category. Sink to ADLS Gen2 as Parquet with partition by year and month.
D.Use Azure Databricks with a PySpark notebook. Mount the ADLS Gen2 storage, read CSV files with schema inference, filter, cast, aggregate, and write partitioned Parquet.
AnswerC

Mapping Data Flow provides code-free transformations and handles schema drift.

Why this answer

Azure Data Factory Mapping Data Flows provide a code-free visual interface for transformations. They can read multiple CSV files from ADLS Gen2, handle schema variations, filter nulls, parse dates, aggregate, and write partitioned Parquet files. This meets the requirements of minimal coding, cost-effectiveness (pay-per-execution), and no cluster management.

A is incorrect because while Azure Synapse Serverless SQL pool can query CSV files and use CETAS to write Parquet, it requires T-SQL coding, handling schema drift is more complex, and partitioning via CETAS can be cumbersome. Also, serverless SQL is optimized for ad-hoc querying rather than batch ETL. B is incorrect because Azure Synapse Dedicated SQL pool requires provisioning and managing a dedicated cluster, incurring ongoing costs even when idle.

It also involves more coding with T-SQL and PolyBase, and does not align with 'no cluster management' requirement. D is incorrect because Azure Databricks requires creating and managing a cluster, and coding in PySpark or Scala, which contradicts 'minimal coding effort' and 'no cluster management'.

104
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?

A.The table is not partitioned by OrderDate
B.Statistics on the join columns are outdated
C.The table should use round-robin distribution instead
D.The join columns are not aligned; data must be shuffled across distributions
AnswerD

Correct. Since the join is on OrderDate but the table is hash-distributed on CustomerID, the join columns are not aligned across distributions. This forces data shuffling, which is the primary cause of slow performance.

Why this answer

D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning on the join column solves the data movement issue, when in fact distribution alignment is the critical factor for collocated joins in a distributed MPP system.

How to eliminate wrong answers

Option A is wrong because partitioning by OrderDate would help with partition elimination for scans or maintenance, but it does not address the fundamental issue of data movement required when join columns are not aligned with the distribution key. Option B is wrong because outdated statistics can cause suboptimal query plans, but the primary performance bottleneck here is the physical data movement across distributions, not statistics. Option C is wrong because round-robin distribution distributes rows evenly without any key, which would still require full data movement for any join, making performance even worse than hash distribution on a non-join column.

105
Multi-Selectmedium

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

Select 2 answers
A.Log Analytics
B.Microsoft Sentinel
C.Azure Policy
D.Azure Monitor
E.Azure Advisor
AnswersA, D

Log Analytics queries logs and can trigger alerts.

Why this answer

Log Analytics and Azure Monitor are the correct choices because they directly support monitoring and alerting for Azure Data Factory pipeline runs. Microsoft Sentinel is a SIEM that consumes logs from Log Analytics but is not a primary monitoring service for ADF. Azure Policy enforces governance rules and does not provide monitoring.

Azure Advisor offers recommendations for optimization, not real-time monitoring or alerts.

106
MCQhard

You are designing a solution to store semi-structured JSON logs from a web application in Azure Cosmos DB. The logs are written once and rarely read. The application writes up to 10,000 documents per second, and each document is about 2 KB. You need to minimize RU/s cost. Which API and indexing policy should you choose?

A.Azure Cosmos DB for Cassandra with default indexing policy.
B.Azure Cosmos DB for Table with default indexing policy.
C.Azure Cosmos DB for NoSQL with default indexing policy (automatic indexing of all fields).
D.Azure Cosmos DB for MongoDB with a custom indexing policy that only includes a wildcard index on _id and disables automatic indexing.
AnswerD

Disabling automatic indexing and indexing only _id minimizes RU consumption for writes.

Why this answer

Azure Cosmos DB for MongoDB with a custom indexing policy that disables automatic indexing and only includes a wildcard index on _id minimizes RU/s cost for write-heavy, rarely read workloads. Each write operation consumes RUs proportional to the number of indexed paths; by eliminating automatic indexing of all fields, you drastically reduce the per-document write RU charge. The MongoDB API supports this fine-grained indexing control, making it ideal for high-throughput ingestion of semi-structured JSON logs.

Exam trap

The trap here is that candidates often assume the NoSQL API (Option C) is always the best choice for JSON data, overlooking that its default indexing policy incurs significant RU overhead for write-heavy workloads, whereas the MongoDB API allows disabling indexing to minimize cost.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB for Cassandra uses a default indexing policy that indexes all columns, which would incur high RU/s costs for the 10,000 writes per second. Option B is wrong because Azure Cosmos DB for Table also indexes all properties by default, leading to unnecessary RU consumption for write-heavy, rarely read logs. Option C is wrong because Azure Cosmos DB for NoSQL with default automatic indexing of all fields would maximize RU/s cost per write, which directly contradicts the requirement to minimize cost.

107
Drag & Dropmedium

Drag and drop the steps to set up Azure Data Factory pipeline with parameterization and dynamic expressions into the correct order.

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

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

Why this order

Create the pipeline, define parameters, use them in activities and linked services, then trigger with values.

108
MCQeasy

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

A.The input alias is incorrect.
B.The JOIN syntax is incorrect for reference data.
C.The input type is Stream; it should be Reference.
D.The output type is set to ReferenceData; it should be a different type.
AnswerC

Correct. The error directly indicates the input type is Stream when it should be Reference. Changing the input type to Reference resolves the error.

Why this answer

The error 'Reference data input must be of type Reference, not Stream' explicitly indicates that the input configured as Stream should be set to Reference type. In Azure Stream Analytics, reference data inputs are used for static lookup data, and they must be declared as Reference input type. Setting the input type to Stream when it should be Reference causes this error.

Therefore, option C is correct. Option D is incorrect because the error message does not relate to output configuration; it is strictly about input type mismatch.

Exam trap

Candidates may be misled by the wording 'Reference data input' and think it's about output, but the error is straightforward: the input type is wrong. The trap is overthinking or assuming a complex output issue when the error message directly states the input type must be Reference.

How to eliminate wrong answers

Option A is wrong because the input alias being incorrect would cause a different error, such as 'Input alias not found' or 'Invalid input alias', not a type mismatch error. Option B is wrong because the JOIN syntax for reference data (e.g., using temporal join with ON clause) is correct; the error is about the input type, not the syntax. Option C is wrong because it states the input type is Stream when it should be Reference, which is actually the correct diagnosis of the problem, but the question asks for the cause of the error, and the answer option D correctly identifies that the output type is not the issue; the error is caused by the input being Stream instead of Reference, so C is a distractor that describes the problem rather than the cause as framed in the options.

109
Multi-Selectmedium

You are building a data processing pipeline in Azure Synapse Analytics that ingests data from Azure Blob Storage and writes to a dedicated SQL pool. You need to ensure the pipeline can handle schema changes in the source data without failing. Which TWO actions should you take?

Select 2 answers
A.Use staged copy with an intermediate storage.
B.Enable schema drift in a Mapping Data Flow activity.
C.Define a fixed schema in the source dataset.
D.Use PolyBase to load data into the dedicated SQL pool.
E.Use the Copy activity with AutoCreateTable enabled.
AnswersB, E

Mapping Data Flow can automatically handle schema drift by mapping new columns.

Why this answer

Mapping Data Flows in Azure Synapse Analytics support schema drift, which allows the pipeline to dynamically handle changes in source data structure (e.g., new columns, changed data types) without failing. By enabling schema drift, the data flow can automatically detect and propagate these changes downstream, ensuring resilience against schema evolution.

Exam trap

The trap here is that candidates often confuse PolyBase or staged copy with schema drift handling, but those features are designed for performance or staging, not for dynamic schema adaptation.

110
MCQhard

Match each Azure storage service to its primary use case. Select the Azure storage services from the options below.

A.Big data analytics
B.Globally distributed, low-latency applications
C.Relational OLTP
D.NoSQL key-value store
E.Azure Data Lake Storage Gen2
F.Azure Cosmos DB
G.Azure SQL Database
H.Azure Table Storage
AnswerE, F, G, H

Azure Data Lake Storage Gen2 is correctly matched with big data analytics.

Why this answer

Azure Data Lake Storage Gen2 is used for big data analytics, Azure Cosmos DB for globally distributed, low-latency applications, Azure SQL Database for relational OLTP, and Azure Table Storage for NoSQL key-value stores. Options A through D describe the use cases, not the services, so they are not selected. The correct answers are E, F, G, and H.

Exam trap

Candidates often confuse Azure Data Lake Storage with Blob Storage; Data Lake Storage is Blob Storage with hierarchical namespace, optimized for analytics.

111
MCQhard

Refer to the exhibit. A data engineer runs a Synapse Spark job that fails with the error shown. Which configuration change is most likely to resolve the issue?

A.Change the executor cores to 1
B.Switch to a different Spark pool with the same configuration
C.Reduce the number of executors to 1 for Job1
D.Increase executor memory to 4g for Job1
AnswerD

Increasing executor memory provides more heap space, which directly addresses the OutOfMemoryError.

Why this answer

The error indicates an out-of-memory (OOM) condition in the Spark executor. Increasing executor memory to 4g for Job1 provides more heap space for data processing, which directly resolves the memory exhaustion. This is the most appropriate fix because the error is specifically about memory, not CPU or parallelism.

Exam trap

The trap here is that candidates might confuse memory issues with parallelism or executor count, leading them to choose options that reduce parallelism (A or C) instead of directly increasing memory.

How to eliminate wrong answers

Option A is wrong because reducing executor cores to 1 would decrease parallelism and potentially increase memory pressure per core, but it does not address the root cause of insufficient memory. Option B is wrong because switching to a different Spark pool with the same configuration would not change the memory allocation per executor, so the OOM error would persist. Option C is wrong because reducing the number of executors to 1 would concentrate all data processing into a single executor, likely worsening memory pressure and causing the same OOM error.

112
MCQeasy

A company uses Azure Data Lake Storage Gen2 to store sensor data. They notice that queries on the data are slow. Which feature should they enable to optimize query performance without moving data?

A.Implement Change Data Capture (CDC).
B.Enable Azure Search on the storage account.
C.Use PolyBase to query the data.
D.Enable hierarchical namespace on the storage account.
AnswerD

Hierarchical namespace organizes data in directories, improving query performance.

Why this answer

Enabling hierarchical namespace on Azure Data Lake Storage Gen2 organizes blobs into a directory hierarchy, which allows query engines like Azure Synapse Analytics and Apache Spark to perform directory-level pruning and partition elimination. This reduces the amount of data scanned during queries, directly improving performance without requiring data movement or restructuring.

Exam trap

The trap here is that candidates often confuse PolyBase (a query engine) with a storage optimization feature, or assume that enabling a search service or CDC will improve query performance on static data, when in fact the hierarchical namespace is the only option that directly optimizes storage layout for faster queries.

How to eliminate wrong answers

Option A is wrong because Change Data Capture (CDC) is a pattern for tracking row-level changes in relational databases (e.g., Azure SQL Database) and does not optimize query performance on static data in Data Lake Storage. Option B is wrong because Azure Search is a cognitive search service for indexing and full-text search over unstructured content, not a query acceleration feature for analytical workloads on Data Lake Storage. Option C is wrong because PolyBase is a data virtualization technology for querying external data sources (e.g., Hadoop, Azure Blob Storage) from SQL Server or Azure Synapse, but it does not enable a performance optimization on the storage account itself; it is a query engine, not a storage-level feature.

113
MCQmedium

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

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

The medallion architecture provides data immutability and supports both processing paradigms.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

114
MCQhard

A multinational corporation uses Azure Data Lake Storage Gen2 to store petabytes of parquet files partitioned by date and hour. Data scientists report that queries on the last 7 days of data take over 30 minutes, while queries on older data are fast. The storage account uses the default Azure Blob Storage hierarchical namespace. Which action will MOST improve query performance on recent data?

A.Convert the parquet files to CSV format to reduce metadata overhead
B.Enable soft delete on the storage account to reduce read latency
C.Optimize the partition layout by partitioning by date first, then by hour, to reduce the number of partitions scanned for recent data
D.Apply Z-order clustering on the parquet files using Azure Databricks
AnswerC

Recent data queries scan fewer partitions, improving performance.

Why this answer

Partitioning by date first, then by hour, ensures that queries filtering on the last 7 days scan only the relevant date partitions, drastically reducing the amount of data read. In Azure Data Lake Storage Gen2, the hierarchical namespace allows partition pruning at the directory level, so a date-first layout minimizes the number of partitions scanned for recent data, directly addressing the performance bottleneck.

Exam trap

The trap here is that candidates often confuse partition layout optimization with data format or clustering techniques, overlooking that the hierarchical namespace in ADLS Gen2 makes directory-level partition pruning the most impactful lever for time-range queries.

How to eliminate wrong answers

Option A is wrong because converting parquet to CSV would increase file size and read overhead, as CSV lacks columnar compression and predicate pushdown capabilities, making queries slower, not faster. Option B is wrong because soft delete is a data protection feature that adds metadata overhead for deleted objects and does not reduce read latency; it actually increases storage costs and can degrade performance due to additional index lookups. Option D is wrong because Z-order clustering in Azure Databricks optimizes data layout within a partition for multi-dimensional queries, but it does not reduce the number of partitions scanned; the primary issue is scanning too many partitions, not intra-partition data skew.

115
MCQhard

You are a data engineer for a financial services company. The company stores sensitive transaction data in Azure Data Lake Storage Gen2. The data is partitioned by date and loaded daily via Azure Data Factory. Recently, an audit found that the storage account allows public network access, and some containers have anonymous read access enabled. You need to secure the storage account according to the principle of least privilege while ensuring that Azure Data Factory can still load data. You must also ensure that data can be accessed by Azure Databricks for analytics. The solution must minimize administrative overhead. Which course of action should you take?

A.Keep public network access enabled but restrict it to specific IP addresses. Use storage account keys for Azure Data Factory and Azure Databricks.
B.Disable public network access. Create private endpoints for the storage account and configure Azure Data Factory and Azure Databricks to use the private endpoints. Use RBAC to assign 'Storage Blob Data Contributor' to the managed identities.
C.Enable public network access with a firewall rule to allow only the Azure Data Factory and Azure Databricks IP ranges. Keep anonymous access enabled but set the containers to private.
D.Disable public network access. Set the storage account firewall to allow access only from Azure services. Configure Azure Data Factory and Azure Databricks to use managed identities. Grant the managed identities the 'Storage Blob Data Contributor' role at the container level. Remove any anonymous access.
AnswerD

This meets security requirements, uses managed identities for authentication, and applies least privilege via RBAC.

Why this answer

Disabling public network access and using managed identities with RBAC (Storage Blob Data Contributor) aligns with the principle of least privilege while minimizing administrative overhead. Azure Data Factory and Azure Databricks can authenticate via managed identities without managing keys or IP ranges, and removing anonymous access eliminates the security gap. The firewall rule allowing access only from Azure services ensures that only Azure-internal traffic can reach the storage account, which is sufficient for these services when they are in the same region.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing private endpoints (Option B) or IP-based firewalls (Option A/C), not realizing that the 'Allow Azure services' firewall rule combined with managed identities provides a simpler, least-privilege-compliant path for first-party Azure services.

How to eliminate wrong answers

Option A is wrong because keeping public network access enabled and using storage account keys violates least privilege—keys are shared secrets that are hard to rotate and audit, and IP restriction alone does not prevent access from other Azure services or compromised endpoints. Option B is wrong because creating private endpoints adds significant administrative overhead (VNet configuration, DNS resolution, peering) and is not necessary when the services can use managed identities over the Azure backbone network; it also does not address the anonymous access issue directly. Option C is wrong because enabling public network access with firewall rules for IP ranges is brittle—Azure Data Factory and Azure Databricks IP ranges can change without notice, leading to failures, and keeping anonymous access enabled (even with private containers) still allows enumeration of container names and potential misconfiguration.

116
MCQmedium

Your organization uses Azure Synapse Analytics to run large-scale queries. You need to implement a solution that automatically pauses the dedicated SQL pool when no activity is detected for a specified period, and resumes it when a connection is attempted. What should you configure?

A.You cannot configure auto-pause for a dedicated SQL pool.
B.Use Azure Automation to run a PowerShell script that pauses and resumes the pool.
C.Create an Azure Function that pauses the pool on a schedule.
D.Set the auto-pause delay in the SQL pool properties.
AnswerA

Auto-pause is only for serverless SQL pools.

Why this answer

Azure Synapse Analytics dedicated SQL pool does not support auto-pause; auto-pause is only available for serverless SQL pools. Option B (Azure Automation runbook) could be used as a custom solution but is not a built-in feature. Option C (Azure Functions) also provides a custom approach but is not a native configuration.

Option D (setting auto-pause delay) applies only to serverless SQL pools, not dedicated SQL pools. Therefore, the only accurate statement is that you cannot configure auto-pause for a dedicated SQL pool.

117
MCQmedium

You are designing a data ingestion pipeline for Azure Synapse Analytics. The pipeline will load sensitive financial data from an on-premises SQL Server to a dedicated SQL pool. The data must be encrypted at rest and in transit. Which combination of features should you use?

A.Use Always Encrypted in SQL Server and Azure Key Vault for column encryption.
B.Enable Azure Disk Encryption on the source server and use a VPN gateway.
C.Implement client-side encryption using Azure Storage client library and store keys in Microsoft Purview.
D.Use Azure Synapse Transparent Data Encryption (TDE) and enforce TLS 1.2 for connections.
AnswerD

TDE encrypts data at rest in Synapse, and TLS secures data in transit.

Why this answer

Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, and enforcing TLS 1.2 ensures encryption in transit. Option A is wrong because Always Encrypted is for column-level encryption, not for the entire pipeline. Option B is wrong because Azure Disk Encryption is for IaaS VMs, not for Azure Synapse.

Option C is wrong because client-side encryption is not built-in for Synapse ingestion.

118
MCQmedium

You are designing a solution to monitor the performance of Azure Synapse Analytics dedicated SQL pools. You need to identify queries that are consuming more than 100 GB of memory and have been running for more than 30 minutes. Which DMV should you query?

A.sys.dm_pdw_resource_waits
B.sys.dm_pdw_nodes
C.sys.dm_pdw_exec_requests
D.sys.dm_pdw_waits
AnswerC

Contains memory and duration columns for queries.

Why this answer

Sys.dm_pdw_exec_requests, is correct because it provides detailed information about active or recently completed queries, including memory consumption (estimated_memory_kb) and duration (start_time, total_elapsed_time). You can filter this DMV to find queries with memory > 100 GB and runtime > 30 minutes. Option A (sys.dm_pdw_resource_waits) shows resource wait statistics, not memory usage.

Option B (sys.dm_pdw_nodes) provides node-level information, not query details. Option D (sys.dm_pdw_waits) shows wait types for queries, not memory or duration.

119
MCQmedium

You are designing a data storage solution for a healthcare organization that stores patient records. The solution must comply with HIPAA and support point-in-time restore (PITR) for the last 35 days. The data is frequently accessed for reporting. Which Azure data service should you use?

A.Azure Cosmos DB
B.Azure Blob Storage with point-in-time restore
C.Azure SQL Database
D.Azure Data Lake Storage Gen2
AnswerC

Azure SQL Database supports PITR up to 35 days and is HIPAA-eligible when configured properly.

Why this answer

Azure SQL Database is the correct choice because it natively supports point-in-time restore (PITR) for up to 35 days (configurable from 7 to 35 days) and is a HIPAA-eligible service when configured with encryption, auditing, and network security. It provides transactional consistency required for patient records and supports frequent reporting workloads with features like columnstore indexes and read replicas.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage's point-in-time restore (which only applies to container-level recovery of blobs) with the transactional point-in-time restore needed for relational patient records, or they assume Cosmos DB's multi-model capabilities make it suitable for structured healthcare data despite its lack of ACID compliance and limited backup retention.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB does not support point-in-time restore for the last 35 days; its backup retention is limited to 8 hours for continuous backups or 30 days for periodic backups, and it is not a relational database, making it unsuitable for structured patient records requiring ACID transactions. Option B is wrong because Azure Blob Storage with point-in-time restore is designed for object storage, not transactional data; it lacks relational query capabilities, foreign key constraints, and native support for HIPAA-compliant auditing and row-level security required for patient records. Option D is wrong because Azure Data Lake Storage Gen2 is optimized for big data analytics and hierarchical namespace storage, not for transactional workloads with point-in-time restore; it does not provide built-in PITR for individual records and is not designed for frequent reporting on structured patient data.

120
MCQmedium

You are optimizing an Azure Data Factory pipeline that moves data from Azure Blob Storage to Azure SQL Database. The pipeline currently uses a Copy activity with a staging setting enabled. You notice high DTU consumption on the Azure SQL Database during the copy operation. How can you reduce the impact on the source database?

A.Configure the Copy activity to use staging with Azure Blob Storage and then use a stored procedure to bulk insert.
B.Use PolyBase to load data directly from Blob Storage to Azure SQL Database.
C.Enable parallel copy in the Copy activity settings.
D.Increase the DTU of the Azure SQL Database to handle the load.
AnswerA

Staging allows the copy to offload transformation to Blob Storage, and bulk insert reduces DTU consumption.

Why this answer

Staging the copy to Azure Blob Storage and then using a stored procedure for bulk insert reduces the direct load on Azure SQL Database, lowering DTU consumption during the copy. Option B is incorrect because PolyBase is designed for Azure Synapse Analytics, not Azure SQL Database. Option C (parallel copy) can increase throughput but also increases DTU usage, not reducing it.

Option D (increasing DTU) is a scaling measure, not a reduction technique.

121
Multi-Selectmedium

A data engineer is building a pipeline in Azure Data Factory to load data from Azure Blob Storage to Azure Synapse Analytics using PolyBase. The pipeline must handle large files efficiently and minimize load time. Which TWO actions should the engineer take? (Choose two.)

Select 2 answers
A.Split large files into multiple smaller files (e.g., 256 MB each).
B.Use a staging table to stage the data before loading.
C.Convert files to Parquet format for better compression.
D.Use the copy activity with staging enabled.
E.Configure PolyBase settings to use 'RejectValue' for handling bad records.
AnswersA, E

Enables parallelism.

Why this answer

To optimize PolyBase loads for large files, splitting files into smaller chunks (e.g., 256 MB) enables parallel processing and reduces load time. Configuring PolyBase with the 'RejectValue' option allows handling of bad rows without failing the entire load, improving resilience. Option B (using a staging table) is not a PolyBase-specific optimization; staging is used for other loading methods like COPY INTO.

Option C (converting to Parquet) is not directly related to PolyBase performance and is not required. Option D (copy activity with staging) may use PolyBase but the question asks for PolyBase-specific actions, and staging is not a PolyBase feature. Therefore, the correct actions are A and E.

122
Multi-Selecthard

Which TWO actions should you take to secure access to Azure Data Lake Storage Gen2 when using Azure Synapse Analytics serverless SQL pools? (Choose two.)

Select 2 answers
A.Enable firewall rules and virtual network service endpoints for the storage account.
B.Use managed identity authentication from the serverless SQL pool to the storage account.
C.Use a service principal with a client secret to access the storage account.
D.Generate a shared access signature (SAS) token for the storage account and store it securely.
E.Use Azure Active Directory (Azure AD) passthrough authentication for the serverless pool.
AnswersA, B

Network restrictions reduce the attack surface.

Why this answer

To secure access to Azure Data Lake Storage Gen2 from Azure Synapse Analytics serverless SQL pools, two effective actions are: (A) Enable firewall rules and virtual network service endpoints to restrict network access to the storage account, and (B) Use managed identity authentication from the serverless SQL pool to the storage account, which eliminates the need to store credentials and provides secure, Azure AD-based authentication. Option C (service principal with client secret) is less secure than managed identity because it requires managing secrets. Option D (shared access signature token) is not recommended for long-term access as tokens can be leaked and need to be rotated.

Option E (Azure AD passthrough authentication) is not supported for serverless SQL pools; it is only available for dedicated SQL pools.

123
MCQhard

You are designing a data storage solution for a global e-commerce company. The company's analytics team uses Azure Synapse Serverless SQL to query Parquet files in ADLS Gen2. The data is partitioned by year, month, and day. The team frequently runs queries that aggregate sales by product category across the last 30 days. However, the queries are slow and scanning too much data. What should you do to improve performance?

A.Increase the number of files per partition to improve parallelism.
B.Convert the Parquet files to CSV format to reduce file size.
C.Create a view that uses explicit partition pruning by filtering on year, month, and day in the WHERE clause.
D.Add a new partition by product category to the folder structure.
AnswerC

Serverless SQL can eliminate partitions when filters are on partition columns.

Why this answer

Creating a view with explicit partition pruning by filtering on year, month, and day in the WHERE clause forces Synapse Serverless SQL to read only the relevant partitions (the last 30 days) instead of scanning the entire dataset. This reduces the amount of data scanned, directly improving query performance and lowering cost.

Exam trap

The trap here is that candidates often think adding more partitions (Option D) or changing file formats (Option B) will help, but they fail to recognize that the real bottleneck is the lack of partition pruning in the query itself, not the storage layout.

How to eliminate wrong answers

Option A is wrong because increasing the number of files per partition does not reduce the amount of data scanned; it can actually increase metadata overhead and slow down queries due to more file open/close operations. Option B is wrong because converting Parquet to CSV would increase file size (Parquet is columnar and compressed, CSV is row-based and uncompressed), leading to more data scanned and slower performance. Option D is wrong because adding a partition by product category would create a deep folder hierarchy that does not help with the existing partition pruning on date columns; the queries already filter by date, so the date-based partition structure is sufficient, and adding another partition level would not reduce the scan for the last 30 days.

124
MCQmedium

Your company uses Azure Data Factory to load data from an on-premises SQL Server database to Azure Synapse Analytics. The pipeline runs daily and the load volume is about 50 GB per run. Recently, the pipeline started failing with timeout errors during the copy activity. You need to ensure reliable data transfer. What should you do?

A.Use a self-hosted integration runtime and increase the copy activity timeout
B.Use PolyBase to load data in parallel
C.Split the copy into multiple smaller activities
D.Switch to Azure Integration Runtime
AnswerA

A self-hosted IR is required for on-premises sources, and increasing the timeout can handle 50 GB loads without timing out.

Why this answer

For on-premises data sources, a self-hosted integration runtime (IR) is required to connect to Azure Data Factory. Increasing the copy activity timeout helps handle large data volumes (50 GB) and prevents timeout errors. Option B (PolyBase) is a technique for loading data into Azure Synapse but does not address the connectivity or timeout issue.

Option C (splitting into smaller activities) could reduce load per activity but does not directly resolve the timeout; increasing timeout is more direct. Option D (Azure IR) cannot access on-premises resources, so it is not applicable.

125
MCQhard

Refer to the exhibit. You have an Azure Data Factory dataset definition for a Parquet file stored in Azure Data Lake Storage Gen2. You attempt to use this dataset as a source in a copy activity, but the copy activity fails with an error indicating that the file is not found. The file 'sales_orders.parquet' exists at the specified path. What is the most likely cause of the error?

A.The dataset is configured with a specific file name, but the data is partitioned across multiple folders; you should use a wildcard pattern in the folderPath.
B.The compression codec 'snappy' is not supported for Parquet files.
C.The dataset type should be 'AzureBlobStorageLocation' instead of 'AzureBlobFSLocation'.
D.The linked service 'ADLSGen2' is not properly configured with the storage account key.
AnswerA

While the exhibit shows a single file, the error suggests the file is not found, likely because the dataset expects a different path structure. Using wildcards would allow the copy activity to find the file within the partition structure.

Why this answer

The dataset definition specifies a single file name ('sales_orders.parquet') in the 'fileName' property, but the actual data is likely stored across multiple Parquet files in a partitioned folder structure (e.g., 'sales_orders/year=2024/month=01/'). When a copy activity uses this dataset as a source, Azure Data Factory looks for the exact file name at the specified path, not finding any file because the data is spread across subfolders. Using a wildcard pattern (e.g., '*.parquet') in the 'folderPath' or setting 'fileName' to '*' allows the copy activity to read all Parquet files in the folder, resolving the 'file not found' error.

Exam trap

The trap here is that candidates often assume the error is due to authentication or configuration issues (like linked service keys or location types) rather than recognizing that the dataset's explicit file name prevents ADF from scanning subdirectories for partitioned files.

How to eliminate wrong answers

Option B is wrong because Snappy is a widely supported compression codec for Parquet files in Azure Data Factory; it is not the cause of a 'file not found' error. Option C is wrong because 'AzureBlobFSLocation' is the correct location type for Azure Data Lake Storage Gen2 (which uses the Blob Filesystem API), while 'AzureBlobStorageLocation' is for Azure Blob Storage (which uses the Blob REST API); using the wrong type would cause a different error, not a 'file not found' error. Option D is wrong because if the linked service were misconfigured (e.g., wrong storage account key), the error would typically indicate authentication or authorization failure, not a 'file not found' error.

126
MCQmedium

You are configuring a data pipeline in Azure Data Factory that uses a Mapping Data Flow. The source is a SQL Server table with 50 million rows. The sink is a Delta table in ADLS Gen2. The pipeline runs slowly. You need to improve performance by reducing the number of partitions in the data flow. Which setting should you adjust?

A.In the source transformation, set the 'Optimize' tab to 'Use current partitioning'.
B.Decrease the cluster size to 4 cores.
C.In the sink transformation, set the 'Optimize' tab to 'Single partition'.
D.In the pipeline activity, set the 'Data flow' property 'partitionOption' to 'Single partition'.
AnswerC

Sink optimization controls how data is written; setting to single partition reduces file count.

Why this answer

Setting the sink transformation's 'Optimize' tab to 'Single partition' forces all data to be written into a single partition, which reduces the number of output files and the associated metadata overhead. This can improve performance when the downstream processing or storage benefits from fewer, larger files, especially for small-to-medium datasets or when the sink is a Delta table that must be compacted later.

Exam trap

The trap here is that candidates often confuse the 'Optimize' tab settings on the sink with the 'Partition option' in the source or pipeline activity, or mistakenly think that reducing cluster size (Option B) is a valid performance tuning technique for partition reduction.

How to eliminate wrong answers

Option A is wrong because 'Use current partitioning' in the source transformation does not reduce partitions; it preserves the existing partition count from the source, which may still be high and cause slow performance. Option B is wrong because decreasing the cluster size to 4 cores reduces compute resources, which typically worsens performance rather than improving it, and does not directly control the number of partitions in the data flow. Option D is wrong because the 'partitionOption' property is not available at the pipeline activity level for Mapping Data Flows; partition settings are configured within the data flow transformations themselves, not in the pipeline activity properties.

127
MCQhard

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

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

Replication eliminates data movement for small dimension tables.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

128
MCQeasy

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

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

The parquet() method reads Parquet files directly.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

129
MCQhard

Your company uses Azure Data Lake Storage Gen2 and Azure Databricks for data processing. Some Parquet files in the lake are written with a schema that includes a column 'address' of struct type. A downstream process expects 'address' to be a string. You need to transform the data in a way that minimizes read overhead and does not rewrite the entire dataset. Which approach should you use?

A.Use schema evolution in Delta Lake to change the column type.
B.Apply a schema-on-read transformation that casts the column to string when reading.
C.Create a view that selects the column as a string and save it as a new table.
D.Rewrite the Parquet files with the correct schema using a Spark job.
AnswerB

Efficient, no rewrite needed.

Why this answer

Schema-on-read allows you to cast the 'address' column from struct to string at query time without modifying the underlying Parquet files. This approach minimizes read overhead by avoiding a full data rewrite and leverages Spark's ability to apply transformations during the read path, which is efficient for downstream processes that expect a string type.

Exam trap

The trap here is that candidates often confuse schema evolution (which modifies metadata but still requires a rewrite for type changes) with schema-on-read (which applies transformations at query time without altering storage), leading them to choose option A incorrectly.

How to eliminate wrong answers

Option A is wrong because schema evolution in Delta Lake can change column types but requires rewriting the underlying data files (e.g., using ALTER TABLE ALTER COLUMN), which incurs write overhead and does not meet the requirement to avoid rewriting the entire dataset. Option C is wrong because creating a view that selects the column as a string and saving it as a new table would duplicate the data, resulting in additional storage and write overhead, contradicting the goal of minimizing read overhead without rewriting. Option D is wrong because rewriting the Parquet files with a Spark job explicitly rewrites the entire dataset, which is the opposite of the requirement to avoid rewriting.

130
Multi-Selectmedium

Which TWO of the following are built-in components of Azure Data Factory that can be used to perform data transformation without writing custom code?

Select 2 answers
A.Mapping Data Flow
B.Copy Activity
C.Data Flow Script
D.Azure Function Activity
E.Stored Procedure Activity
AnswersA, C

Mapping Data Flows allow visual data transformation without code.

Why this answer

Mapping Data Flow (A) is a built-in Azure Data Factory component that allows you to design data transformations visually using a graphical interface, without writing any code. It executes at scale on Azure Databricks clusters and supports operations like joins, aggregations, and pivots, making it a code-free transformation tool.

Exam trap

The trap here is that candidates often confuse the Copy Activity with a transformation tool because it can perform simple column mapping and data type conversions, but it lacks the ability to handle complex, multi-step transformations like those in Mapping Data Flows.

131
MCQhard

You are designing a batch processing solution for a financial services company that processes transactions from multiple sources. The data is stored in Azure Data Lake Storage Gen2 in Parquet format. You need to perform complex transformations including joins, aggregations, and window functions, and then load the results into an Azure Synapse Analytics dedicated SQL pool. The transformations must be written in SQL and executed on a serverless Spark cluster to minimize costs. You want to manage the code in a Git repository and automate the deployment using Azure DevOps. Which approach should you recommend?

A.Use Azure Databricks with Auto Loader to read from Data Lake Storage, perform transformations using Spark SQL, and write to the dedicated SQL pool via JDBC.
B.Use Azure Data Factory with Mapping Data Flows to visually design transformations and write to the dedicated SQL pool.
C.Create a Spark job definition in Azure Synapse that reads Parquet files, performs transformations using PySpark, and writes to the dedicated SQL pool using the Spark Synapse connector.
D.Use a serverless SQL pool in Azure Synapse to query the Parquet files using T-SQL, then use CETAS to write the results to the dedicated SQL pool.
AnswerC

Correct. A Spark job definition in Azure Synapse runs on a serverless Spark cluster, uses PySpark (including Spark SQL) for complex transformations, writes to a dedicated SQL pool via the Synapse connector, and integrates with Git and Azure DevOps.

Why this answer

Using a Spark job definition in Azure Synapse allows you to run PySpark transformations on a serverless Spark cluster, which meets the requirement for SQL-based transformations via Spark SQL. The Spark Synapse connector enables efficient writing to a dedicated SQL pool. This approach minimizes costs by leveraging serverless Spark, and the code can be managed in Git and deployed with Azure DevOps.

Option A uses Azure Databricks, which is a separate service not integrated with Synapse serverless Spark. Option B uses Mapping Data Flows, which runs on Spark but is not a serverless Spark cluster in the same sense and may incur higher costs. Option D uses a serverless SQL pool, which is not a Spark cluster and thus does not fulfill the requirement to execute on a serverless Spark cluster.

132
MCQeasy

You have an Azure Synapse Analytics serverless SQL pool. You need to monitor the number of queries that are currently executing. Which dynamic management view should you query?

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

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

Why this answer

Sys.dm_exec_returns detailed information about each request currently executing on the serverless SQL pool, including its state, command, and session ID. This DMV is specifically designed for monitoring active queries.

Option A (sys.dm_resource_governor_workload_groups) shows workload group configuration and resource statistics, not current requests.

Option B (sys.dm_exec_query_stats) provides cumulative performance statistics for cached query plans, not currently executing queries.

Option D (sys.dm_exec_sessions) contains session-level information but does not indicate which sessions are actively executing a request.

133
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

134
Multi-Selecthard

You are designing a data storage solution that must support both operational and analytical workloads using a single copy of data. Which TWO technologies should you consider?

Select 2 answers
A.Azure SQL Database with materialized views
B.Azure SQL Database with change data capture (CDC)
C.Azure SQL Database with PolyBase
D.Azure Synapse Link for Cosmos DB
E.Azure Cosmos DB with analytical store (HTAP)
AnswersD, E

Azure Synapse Link for Cosmos DB enables HTAP by providing near real-time analytics on operational data stored in Cosmos DB, using a separate analytical store that is automatically synchronized.

Why this answer

The requirement is to support both operational and analytical workloads on a single copy of data, which is characteristic of HTAP (Hybrid Transactional/Analytical Processing) solutions. Among the options, Azure Synapse Link for Cosmos DB (D) and Azure Cosmos DB with analytical store (HTAP) (E) are specifically designed for HTAP, synchronizing operational data to an analytical store without separate copies. Azure SQL Database with materialized views (A) improves query performance but does not enable HTAP on a single copy; change data capture (B) tracks changes but is not HTAP; and PolyBase (C) is a data virtualization tool that queries external data sources, not an in-database HTAP solution.

Therefore, D and E are correct.

Exam trap

Candidates may assume that PolyBase, being part of Azure SQL Database, can handle both operational and analytical workloads on the same data. However, PolyBase is for data virtualization and external queries, not for in-database HTAP. The real HTAP solutions are Azure Synapse Link and analytical store.

135
MCQhard

You are configuring Microsoft Purview to scan an Azure Data Explorer (ADX) cluster. You define the JSON shown in the exhibit. However, the scan fails with an authentication error. What is the most likely cause?

A.The ADX cluster is behind a firewall and Purview cannot connect.
B.The database name in the scan configuration is incorrect.
C.The ADX cluster does not have a managed identity enabled for Purview.
D.The region of the ADX cluster does not match the Purview account region.
AnswerC

Purview uses managed identity for authentication; it must be enabled.

Why this answer

Purview requires a system-assigned managed identity (or user-assigned) to be enabled on the ADX cluster and granted permissions. Option A (firewall) could be a cause, but the error is authentication specifically. Option B (region mismatch) is not a common issue.

Option D (incorrect database) would cause a different error.

136
MCQmedium

You are a data engineer for a financial services company. You manage an Azure Data Lake Storage Gen2 account that stores real-time stock trade data ingested from Azure Event Hubs via Azure Stream Analytics. The data is partitioned by date and symbol. Each day, a downstream Azure Databricks job runs an ETL process to aggregate trades into 5-minute bars and writes the results to a separate container. The Databricks job runs on a cluster with 10 worker nodes (Standard_DS3_v2) using Auto-Scaling enabled (2-10 workers). Recently, the job has been taking longer than expected, and you observe that the cluster is often at 10 workers but still the job duration increased by 30%. The storage account shows high transaction costs. You suspect the issue is related to how data is read. What should you do to optimize the job's performance and reduce costs?

A.Convert the data to Avro format to reduce file size.
B.Increase the maximum number of workers to 20 and use a larger instance type.
C.Modify the Stream Analytics job to output larger files (e.g., set the minimum file size to 100 MB) and use coalesce in Databricks to reduce the number of output partitions.
D.Move the data to Azure Blob Storage Premium tier to reduce latency.
AnswerC

Larger input files reduce metadata overhead, and coalescing reduces output files, improving performance and reducing costs.

Why this answer

The performance issue stems from reading many small files (small file problem) in Azure Data Lake Storage Gen2, which increases transaction costs and slows down Spark jobs. By configuring Stream Analytics to output larger files (e.g., minimum 100 MB) and using coalesce in Databricks to reduce output partitions, you minimize the number of files read/written, reducing overhead and transaction costs. This directly addresses the root cause—high transaction costs and cluster saturation at 10 workers—without unnecessary scaling or tier changes.

Exam trap

The trap here is that candidates often assume performance issues require scaling up (more workers or larger instances) or changing storage tiers, when the real problem is inefficient data layout (small files) causing excessive I/O and transaction costs.

How to eliminate wrong answers

Option A is wrong because converting to Avro reduces file size but does not solve the small file problem; it may even increase the number of small files if the output is not coalesced, and Avro's compression benefits are marginal for already-compressed data. Option B is wrong because increasing workers to 20 and using larger instances would increase costs without fixing the underlying issue of many small files; the cluster is already at max workers (10) and still slow, indicating a bottleneck in file I/O, not compute capacity. Option D is wrong because moving to Azure Blob Storage Premium tier improves latency but does not reduce the number of transactions or small files; it would increase costs without addressing the root cause of high transaction costs from reading many small files.

137
MCQeasy

You are designing a data pipeline that uses Azure Data Factory to copy data from an Azure SQL database to Azure Data Lake Storage Gen2. The data contains personally identifiable information (PII) that must be masked. Which Data Factory feature should you use?

A.Use a copy activity with a query to select only non-PII columns.
B.Use a stored procedure activity to mask data in the source before copy.
C.Enable staging on the copy activity to use PolyBase.
D.Use a mapping data flow to apply a mask transformation on PII columns.
AnswerD

Data flows support mask transformations.

Why this answer

Mapping Data Flows in Azure Data Factory provide built-in transformations for data masking, such as the Mask transformation, which can obfuscate PII columns during the data flow. Option A is incorrect because a copy activity with a query can only filter columns but does not support masking; it simply selects a subset of columns without transformation. Option B is incorrect because while a stored procedure activity can execute masking logic on the source, it requires additional setup and does not integrate seamlessly with Data Factory's native transformation capabilities.

Option C is incorrect because staging with PolyBase is used to improve bulk copy performance, not for data masking.

138
MCQeasy

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

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

Dynamic data masking hides sensitive data from non-privileged users in query results.

Why this answer

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

Azure Data Lake Storage does not provide masking.

139
MCQmedium

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

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

Group-based ACL assignment is scalable and manageable.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

140
MCQmedium

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

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

Partition elimination reduces data scanned.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

141
MCQmedium

You are a data engineer for a financial services company. You have an Azure Data Lake Storage Gen2 account containing historical trade data organized by date in the format 'yyyy/MM/dd'. Each day's data is stored as a collection of Parquet files. The data is used by a team of analysts who run ad-hoc queries using Azure Synapse Serverless SQL. Recently, the analysts have reported that queries scanning multiple months of data are slow. The storage account uses LRS with a general-purpose v2 tier. You have enabled hierarchical namespace. The data is not partitioned in any other way. You need to improve query performance without moving data or changing the storage tier. What should you do?

A.Create external tables with partition definition using the directory structure and ensure queries filter on the date column.
B.Increase the query timeout setting in Azure Synapse Studio.
C.Redistribute the data using hash distribution on the date column.
D.Increase the data warehouse units (DWU) for the serverless SQL endpoint.
AnswerA

Partition elimination reduces data scanned, improving performance.

Why this answer

Azure Synapse Serverless SQL can leverage the directory structure of Azure Data Lake Storage Gen2 as virtual partitions. By creating external tables with a partition definition that maps to the 'yyyy/MM/dd' folder hierarchy and ensuring queries filter on the date column, the serverless SQL engine performs partition elimination. This reduces the amount of data scanned, directly addressing the slow performance when querying multiple months of data without moving data or changing the storage tier.

Exam trap

The trap here is that candidates may confuse serverless SQL with dedicated SQL pool concepts, such as hash distribution or DWU scaling, and fail to recognize that partition elimination via external table definitions is the only viable optimization for serverless SQL when data remains in the lake.

How to eliminate wrong answers

Option B is wrong because increasing the query timeout setting in Azure Synapse Studio does not improve query performance; it only allows the query to run longer before failing, which does not address the root cause of slow data scans. Option C is wrong because hash distribution is a concept for dedicated SQL pools (provisioned) in Azure Synapse, not for serverless SQL endpoints; serverless SQL does not support redistributing data with hash distribution, and the data remains in the lake. Option D is wrong because serverless SQL endpoints do not use data warehouse units (DWU); DWU is a scaling metric for dedicated SQL pools, and serverless SQL scales automatically based on the amount of data processed, so increasing DWU is not applicable.

142
MCQhard

Refer to the exhibit. The pipeline executes a Spark job definition that reads from the input path and writes to the output path. The job fails with an error indicating that the output path already exists. The job is configured with default behavior. What configuration change should you make to allow the job to overwrite the existing output?

A.Modify the Spark job definition to include 'overwrite' save mode.
B.Change the output path in the arguments to a non-existing folder.
C.Set the pipeline property 'outputMode' to 'overwrite' on the SynapseSparkJob activity.
D.Add a Delete activity before the Spark job to delete the output folder if it exists.
AnswerD

Using a Delete activity in the pipeline to remove the existing output folder before running the Spark job ensures the job can write without conflict.

Why this answer

The default behavior of Spark in Azure Synapse Analytics is to fail if the output path already exists. By adding a Delete activity before the Spark job, you explicitly remove the existing folder, allowing the subsequent Spark job to write without conflict. This approach works with any save mode, including the default 'error' mode, and is a common pattern in Synapse pipelines for idempotent data processing.

Exam trap

The trap here is that candidates assume Spark's 'overwrite' save mode can be set via pipeline properties or activity settings, but in Synapse pipelines, the Spark job definition's code must be modified to include the save mode, and the pipeline itself does not expose such a property for batch jobs.

How to eliminate wrong answers

Option A is wrong because modifying the Spark job definition to include 'overwrite' save mode would require changing the Spark code itself, not just the pipeline configuration, and the question states the job is configured with default behavior, implying no code changes are allowed. Option B is wrong because changing the output path to a non-existing folder would avoid the error but does not overwrite the existing output; it simply writes to a different location, which does not meet the requirement to overwrite the existing output. Option C is wrong because the SynapseSparkJob activity does not have an 'outputMode' property; this property exists for streaming jobs (e.g., in Structured Streaming) but not for batch Spark job definitions in Synapse pipelines.

143
Multi-Selectmedium

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

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

SSE encrypts data at rest by default.

Why this answer

Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption, which is transparent to applications and meets compliance requirements. This is a fundamental security control for protecting data stored in Azure Data Lake Storage Gen2.

Exam trap

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

144
MCQmedium

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

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

Supports high throughput and complex analytics.

Why this answer

Azure Databricks Structured Streaming can handle high throughput and low latency, and it can write to Cosmos DB using the Azure Cosmos DB Spark connector. Option B is wrong because Azure Functions with Event Hubs trigger would process each event individually, which may struggle with very high throughput and lacks the flexibility for complex pattern detection using machine learning. Option C is wrong because Azure Data Factory is not designed for real-time processing; it is an orchestration tool for batch data movement.

Option D is wrong because Azure Stream Analytics is simpler but may not provide the same flexibility for custom machine learning models as Databricks.

145
Multi-Selecteasy

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

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

Diagnostic settings log read/write operations.

Why this answer

Options C and D are correct. Storage account diagnostic settings enable logging of read, write, and delete operations to Azure Data Lake Storage Gen2, which can be used for auditing. Azure Monitor collects these logs and integrates with Microsoft Sentinel for advanced security monitoring and threat detection.

Option A is incorrect because Microsoft Entra ID sign-in logs track user authentication, not data access at the storage level. Option B is incorrect because Azure Backup reports focus on backup status, not auditing data changes. Option E is incorrect because Azure Policy enforces compliance rules but does not audit data access or modification events.

146
Multi-Selectmedium

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

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

Compacting small files improves read performance.

Why this answer

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

Exam trap

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

147
MCQmedium

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

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

Columnar, compressed, optimized for query performance.

Why this answer

Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Exam trap

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

How to eliminate wrong answers

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

148
MCQeasy

Refer to the exhibit. You are deploying the ARM template to create an Azure Synapse Analytics workspace. The deployment fails because the managed resource group cannot be created. What is the most likely reason?

A.The managedResourceGroupName property is not allowed; Azure Synapse creates the resource group automatically
B.The apiVersion is invalid for this resource type
C.The location is missing the 'properties' section
D.The storage account URL must use the blob endpoint, not DFS
AnswerA

You cannot specify a custom managed resource group name.

Why this answer

The `managedResourceGroupName` property is not allowed in an ARM template for Azure Synapse Analytics. Azure Synapse automatically creates and manages its own managed resource group (e.g., `managed-rg-<workspace-name>`) to hold provisioned resources like Spark pools and SQL pools. Specifying this property in the template causes a validation error, as the service requires full control over the naming and lifecycle of this group.

Exam trap

The trap here is that candidates often assume they can control the naming of all Azure resources in an ARM template, but Azure Synapse enforces automatic management of its internal resource group, and specifying it triggers a validation error rather than a runtime failure.

How to eliminate wrong answers

Option B is wrong because the `apiVersion` shown in the exhibit (e.g., `2021-06-01-preview` or similar) is valid for the `Microsoft.Synapse/workspaces` resource type; an invalid apiVersion would produce a different error, not a managed resource group creation failure. Option C is wrong because the `location` property is a required top-level property for the resource, and it does not need a nested `properties` section; the error is unrelated to location formatting. Option D is wrong because Azure Synapse supports both blob (`*.blob.core.windows.net`) and DFS (`*.dfs.core.windows.net`) endpoints for the default storage account; using DFS is actually recommended for optimal performance with Synapse pipelines and Spark, and it would not cause a managed resource group creation failure.

149
MCQeasy

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to monitor for unauthorized access attempts and ensure compliance with regulatory requirements. You want to be alerted when someone attempts to access data without proper permissions. What should you configure?

A.Assign RBAC roles to limit access and rely on Azure Activity Log.
B.Configure Azure Policy to audit storage account access.
C.Enable diagnostic settings for the storage account, send logs to Log Analytics, and create an alert rule for 'StorageRead' failures.
D.Enable Azure Sentinel and connect storage logs.
AnswerC

Diagnostic logs capture detailed access, alerts can be set on specific conditions.

Why this answer

Enabling diagnostic settings for the storage account and sending logs to Log Analytics allows you to create alert rules that trigger on 'StorageRead' failures, indicating unauthorized access attempts. Option A is incorrect because while RBAC roles limit access, the Azure Activity Log only records management plane operations, not data plane access such as read failures. Option B is incorrect because Azure Policy is for governance and compliance of resource configurations, not for monitoring and alerting on access events.

Option D is incorrect because although Azure Sentinel could be used, it is a more complex SIEM solution; the question asks for what should be configured, and the simpler, direct approach is to use diagnostic settings with Log Analytics alerts.

150
Multi-Selectmedium

Which TWO actions should you take to reduce costs associated with an Azure Synapse Dedicated SQL Pool that is used for reporting during business hours only?

Select 2 answers
A.Pause the pool during non-business hours.
B.Enable advanced data compression on all tables.
C.Scale down the pool during business hours.
D.Change the distribution of large tables to ROUND_ROBIN.
E.Implement result set caching for frequently run queries.
AnswersA, E

Stops compute billing when not in use.

Why this answer

Pausing a Dedicated SQL Pool stops billing for compute resources (DWU) while retaining storage costs. Since the pool is only needed for reporting during business hours, pausing it during non-business hours directly eliminates compute charges for that period, which is the most significant cost driver.

Exam trap

Microsoft often tests the distinction between compute cost reduction (pausing/scaling) and storage/performance optimizations (compression, distribution, caching), leading candidates to confuse storage-saving actions with compute-saving actions.

Page 1

Page 2 of 11

Page 3

All pages