Courseiva

CCNA Develop data processing Questions

75 of 261 questions · Page 1/4 · Develop data processing · Answers revealed

1
MCQhard

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

A.The output schema in Synapse does not match the Stream Analytics output.
B.The Event Hubs has a large number of late-arriving events.
C.The tumbling window size is too large.
D.The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).
AnswerD

Insufficient SUs cause backpressure and watermark delay.

Why this answer

A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Exam trap

The trap here is that candidates may confuse a watermark delay alert with late-arriving events (Option B), but the alert indicates the job is falling behind overall, not just handling late data, and the 30-minute delay points to insufficient compute resources rather than data timing issues.

How to eliminate wrong answers

Option A is wrong because a schema mismatch between Stream Analytics output and Synapse would cause data write errors or failures, not a watermark delay alert or a 30-minute output delay. Option B is wrong because a large number of late-arriving events would increase the watermark delay but the alert specifically indicates the job is falling behind overall processing, not just handling late data; late events are managed by the late arrival policy and do not inherently cause a 30-minute delay. Option C is wrong because a 1-minute tumbling window is small and appropriate for real-time analytics; a larger window would reduce processing frequency, not cause delay.

2
MCQhard

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data in Azure Databricks with encryption at rest and in transit, and must also support row-level security (RLS). Which combination of features should you use?

A.Use Azure SQL Database with row-level security and connect Databricks via JDBC
B.Use Azure Databricks with Azure Key Vault for encryption and implement RLS in application code
C.Use Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted
D.Use Azure Databricks with Delta Lake and enable row-level security on the Delta table
AnswerD

Delta Lake in Databricks supports RLS natively, and encryption is handled automatically.

Why this answer

Delta Lake in Azure Databricks now supports row-level security (RLS) natively through Delta Sharing and Spark SQL predicates, allowing you to enforce data access policies directly on the Delta table without external systems. Combined with Azure Databricks' built-in encryption at rest (using Azure Storage Service Encryption or customer-managed keys) and in transit (TLS 1.2+), this meets all requirements without introducing additional services.

Exam trap

The trap here is that candidates often assume row-level security is only available in traditional relational databases like SQL Server or Azure SQL Database, but Delta Lake in Azure Databricks has supported RLS since runtime 10.4 LTS, making it a fully native option for data lakehouse architectures.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with row-level security requires JDBC connectivity, which introduces a separate database service and does not leverage Databricks' native Delta Lake capabilities; it also adds latency and complexity for encryption management. Option B is wrong because implementing RLS in application code is not a native feature of Azure Databricks and violates the principle of defense in depth—RLS must be enforced at the storage layer, not in application logic, to prevent bypass. Option C is wrong because Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted does not provide row-level security; dynamic data masking only obfuscates data at query results, and always encrypted protects data at rest and in transit but does not filter rows based on user permissions.

3
MCQeasy

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

A.Service Principal with a client secret stored in the notebook
B.Azure Key Vault-backed secret scope
C.Shared access signature (SAS) token
D.Storage account access key
AnswerB

Secret scopes securely reference secrets from Key Vault.

Why this answer

Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure.

Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

4
MCQmedium

You are developing a streaming solution with Azure Stream Analytics that outputs results to Azure Synapse Analytics. The output requires exactly-once semantics to avoid duplicates. Which configuration should you use?

A.Use an Azure Function output to write data idempotently to Synapse.
B.Configure the output to Azure Synapse Analytics with 'Exactly once' output mode.
C.Set the event serialization format to Avro and enable 'Exactly once' event delivery.
D.Output to Azure Blob Storage and then use a COPY statement in Synapse.
AnswerB

Stream Analytics supports exactly-once output to Synapse dedicated SQL pool.

Why this answer

Azure Stream Analytics supports an 'Exactly once' output mode for Azure Synapse Analytics, which ensures that each event is written exactly once to the destination, preventing duplicates. This mode uses a combination of checkpointing and transactional writes to guarantee idempotent delivery without requiring custom logic.

Exam trap

The trap here is that candidates often confuse 'exactly-once' with 'at-least-once' and assume that idempotent writes via Azure Functions or staging in Blob Storage can achieve the same guarantee, but Stream Analytics' built-in output mode is the only supported way to guarantee exactly-once delivery to Synapse without custom complexity.

How to eliminate wrong answers

Option A is wrong because Azure Functions do not natively provide exactly-once semantics for Stream Analytics outputs; they are at-least-once by default, and implementing idempotency in a function would require custom state management and is not a built-in feature. Option C is wrong because Avro serialization and 'Exactly once event delivery' are not supported output configurations for Azure Synapse Analytics; Stream Analytics uses its own output mode for Synapse, not event delivery settings. Option D is wrong because writing to Blob Storage and then using a COPY statement introduces at-least-once semantics from Stream Analytics to Blob, and the COPY statement can still produce duplicates if the same file is loaded multiple times, failing to achieve end-to-end exactly-once semantics.

5
MCQhard

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

A.Add more partitions to the Event Hubs.
B.Increase the number of Streaming Units (SUs) for the Stream Analytics job.
C.Replace the output with Azure Functions for each event.
D.Change the input to a reference data input.
AnswerB

More SUs provide more compute power to process events faster.

Why this answer

Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help.

Using Azure Functions for output may add overhead.

6
Multi-Selecthard

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Select 2 answers
A.Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.
B.Enable Transparent Data Encryption (TDE) on the database.
C.Create a security policy with filter predicates to restrict access to specific columns.
D.Use GRANT SELECT on specific columns to authorized users.
E.Implement Always Encrypted to encrypt columns at the application level.
AnswersA, D

Dynamic data masking hides sensitive data from non-privileged users.

Why this answer

Dynamic data masking obfuscates sensitive columns for unauthorized users at query time, providing column-level security without altering the underlying data. Option D is correct because column-level security in Azure Synapse dedicated SQL pools uses GRANT SELECT on specific columns to authorized users, restricting access to sensitive columns. Option B is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not at the column level.

Option C is incorrect because security policies with filter predicates are used for row-level security, not column-level security. Option E is incorrect because Always Encrypted encrypts columns at the application level, which is separate from column-level security in Synapse.

7
MCQeasy

You are designing a data processing solution for a real-time dashboard. The source data is streaming from IoT devices at a rate of 10,000 events per second. Each event is under 1 KB. You need to store the raw data in a staging area before processing, and you must minimize storage costs. Which storage solution should you use?

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

Blob Storage provides low-cost object storage for large volumes of streaming data, ideal for staging before processing.

Why this answer

Azure Blob Storage is cost-effective for large volumes of streaming data that will be processed later. Azure Data Lake Storage Gen2 is built on Blob Storage but adds hierarchical namespace, which is not required for simple staging. Azure SQL Database and Azure Cosmos DB are more expensive and suited for transactional or real-time query workloads.

8
MCQeasy

You are processing CSV files in Azure Synapse Analytics serverless SQL pool. The files contain newline characters within quoted fields, causing parsing errors. Which file format option should you specify to handle this correctly?

A.Use ROWTERMINATOR = '0x0a'
B.Use FORMAT = 'CSV'
C.Use FIELDQUOTE = '"'
D.Use PARSER_VERSION = '2.0'
AnswerD

Parser version 2.0 supports quoted fields with embedded newlines.

Why this answer

PARSER_VERSION = '2.0' in Azure Synapse serverless SQL pool supports quoted fields that contain embedded newline characters (CR/LF). The parser version 2.0 is designed to handle CSV files with complex quoting rules, including newlines within quoted fields, which the older parser version 1.0 cannot parse correctly.

Exam trap

The trap here is that candidates often assume FIELDQUOTE or ROWTERMINATOR can fix embedded newlines, but only the parser version upgrade (2.0) changes the underlying parsing logic to treat newlines inside quotes as part of the field value.

How to eliminate wrong answers

Option A is wrong because ROWTERMINATOR = '0x0a' only specifies the row delimiter (line feed) and does not address the issue of newline characters inside quoted fields; it would still cause parsing errors when newlines appear within quotes. Option B is wrong because FORMAT = 'CSV' is a general format specification that does not by itself enable handling of embedded newlines in quoted fields; it relies on the parser version for such behavior. Option C is wrong because FIELDQUOTE = '"' defines the quote character (default is double quote) but does not change how the parser treats newlines within quoted fields; the parser version determines that behavior.

9
Multi-Selecteasy

Which TWO options are correct for configuring a copy activity in Azure Data Factory to load data from Azure Blob Storage to Azure SQL Database?

Select 2 answers
A.Use a staging table in Azure SQL Database before inserting into the final table.
B.Use staging via Azure Blob Storage when loading large volumes to improve performance.
C.Use Azure Data Lake Storage as the staging location for better throughput.
D.Use PolyBase to load directly from Blob Storage to Azure SQL Database.
E.Always skip staging to reduce latency.
AnswersA, B

Staging table allows data validation and transformation before final load.

Why this answer

Correct options are A and B. Option A (using a staging table in Azure SQL Database before inserting into the final table) is a best practice for large data loads, enabling efficient truncate-and-reload or upsert operations. Option B (using staging via Azure Blob Storage when loading large volumes) improves performance by batching and parallelizing the load, reducing timeouts and resource contention.

Option D is incorrect because PolyBase is not supported for Azure SQL Database; it is available only for Azure Synapse Analytics and SQL Server. Option C is incorrect because Azure Data Lake Storage is not the standard staging location for Azure SQL Database; Azure Blob Storage is used. Option E is incorrect because skipping staging can degrade performance for large data volumes.

10
MCQeasy

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

A.Pivot transformation
B.Aggregate transformation
C.Lookup transformation
D.Derived Column transformation
AnswerD

Derived column can use expressions to split strings.

Why this answer

The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Exam trap

The trap here is that candidates often confuse the Derived Column transformation with the Split transformation (which does not exist in mapping data flows) or mistakenly think the Pivot transformation can re-arrange column data, when in fact Derived Column is the correct choice for column-level string operations.

How to eliminate wrong answers

Option A is wrong because the Pivot transformation is used to rotate data from rows into columns by aggregating values, not for splitting a single column into multiple columns. Option B is wrong because the Aggregate transformation is designed to perform calculations like sum, count, or average over groups of rows, not for row-level string manipulation. Option C is wrong because the Lookup transformation is used to join data from a reference dataset based on a key, not to parse or split column values.

11
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Azure Stream Analytics is optimized for low-latency streaming and can directly ingest from Event Hubs and write to SQL pools.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Exam trap

The trap here is that candidates often confuse 'near real-time' with 'batch processing' and choose Azure Data Factory (option C) because it is a familiar data integration tool, overlooking that it lacks native streaming capabilities and introduces latency from scheduled pipeline runs.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces additional overhead from cluster startup time and micro-batch processing, which typically results in higher latency (seconds to minutes) compared to Stream Analytics' continuous processing model. Option C is wrong because Azure Data Factory is a batch-oriented ETL/ELT orchestration service that does not support native streaming ingestion; it polls sources on a schedule, introducing minutes of latency. Option D is wrong because Azure Functions with Event Hub trigger processes events one at a time in a serverless compute model, which can lead to cold-start delays and lacks built-in windowing, aggregation, and exactly-once semantics for streaming workloads.

12
Multi-Selecthard

Which THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Select 3 answers
A.A Mapping Data Flow to transform the data before load.
B.A Lookup activity to retrieve the last processed watermark.
C.Azure Functions to process change data.
D.A Copy activity to transfer changed data to Synapse.
E.Change Data Capture (CDC) enabled on the source Azure SQL Database tables.
AnswersB, D, E

Needed to get the last high-water mark.

Why this answer

A Lookup activity is required to retrieve the last processed watermark (e.g., a timestamp or LSN) from a control table or source query. This watermark defines the incremental boundary for CDC, ensuring only new or changed rows are loaded in each pipeline run. Without it, the pipeline would have no way to track progress and would either reload all data or miss changes.

Exam trap

The trap here is that candidates often assume CDC requires a Mapping Data Flow or Azure Functions for transformation or processing, when in fact the core pattern uses only Lookup and Copy activities with change tracking metadata.

13
Multi-Selecteasy

Which TWO data processing activities in Azure Data Factory allow you to run custom code?

Select 2 answers
A.Data Flow activity
B.Copy activity
C.Custom activity
D.Azure Function activity
E.Stored Procedure activity
AnswersC, D

Runs custom code on Azure Batch.

Why this answer

The Custom activity in Azure Data Factory allows you to execute custom code by running a .NET or PowerShell script in an Azure Batch service. This enables you to implement complex transformation logic that is not natively supported by ADF's built-in activities, such as custom data validation or integration with third-party APIs.

Exam trap

The trap here is that candidates often confuse the Custom activity with the Azure Function activity, thinking both run custom code, but the question asks for 'custom code' in the context of ADF's native activities—Azure Function activity is also correct because it runs custom code via an Azure Function, making it a valid choice alongside Custom activity.

14
MCQeasy

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

A.Use a staging table to load data before transformation
B.Increase the batch size in the data flow
C.Use PolyBase to load data into the dedicated SQL pool
D.Partition the data flow by a key column
AnswerD

Partitioning allows parallel execution across nodes, improving throughput.

Why this answer

Partitioning the data flow by a key column (Option D) enables parallel processing across multiple compute nodes, reducing data shuffling and improving throughput. In Azure Synapse data flows, partitioning distributes the workload evenly, which directly addresses the slow performance and high resource consumption observed in the pipeline.

Exam trap

The trap here is that candidates often confuse data flow optimization with data loading optimization, leading them to select PolyBase (Option C) or staging tables (Option A) instead of recognizing that partitioning directly addresses compute and parallelism issues in the transformation layer.

How to eliminate wrong answers

Option A is wrong because using a staging table adds an extra I/O step and does not inherently optimize the data flow's compute or parallelism; it is typically used for incremental loads or error handling, not performance tuning. Option B is wrong because increasing the batch size in a data flow can actually degrade performance by causing memory pressure and longer processing times per batch, as the default batch size is already optimized for most scenarios. Option C is wrong because PolyBase is a data loading technology for dedicated SQL pools, not a performance optimization for data flows; it addresses data ingestion speed, not the transformation logic within a data flow.

15
Multi-Selectmedium

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Select 3 answers
A.Pivot
B.Derived Column
C.Union All
D.Aggregate
E.Merge Join
AnswersA, B, D

Used to rotate data from rows to columns.

Why this answer

The Pivot transformation in Mapping Data Flows allows you to rotate data from rows into columns, enabling aggregation and restructuring of datasets. It is a native transformation in Azure Data Factory's Mapping Data Flows, supporting group-by columns, pivot keys, and aggregated column values.

Exam trap

The trap here is that candidates confuse the 'Union All' and 'Merge Join' names from other tools (like SSIS or T-SQL) with the actual transformation names in Azure Data Factory Mapping Data Flows, leading them to select options that sound familiar but are not available.

16
MCQmedium

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

A.The copy activity is not using staging and PolyBase
B.The source and sink are in different Azure regions
C.The Data Integration Unit (DIU) setting is too low
D.The source files are compressed
AnswerA

PolyBase dramatically improves load performance.

Why this answer

The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Exam trap

The trap here is that candidates often assume DIU settings are the primary performance lever, but for Synapse sinks, the staging/PolyBase mechanism is the critical factor that can improve performance by orders of magnitude.

How to eliminate wrong answers

Option B is wrong because the question explicitly states that the source and sink are in the same region, so cross-region latency is not the issue. Option C is wrong because Data Integration Units (DIUs) control parallelism within the Copy activity, but even with maximum DIUs, the row-by-row insert into Synapse is the bottleneck, not the DIU setting. Option D is wrong because compressed source files can actually improve performance by reducing network transfer time, and ADF can decompress them efficiently; compression is not inherently a cause of poor copy performance.

17
Multi-Selecthard

Which THREE of the following are valid ways to authenticate to Azure Data Lake Storage Gen2 from Azure Synapse serverless SQL pool?

Select 3 answers
A.Storage account key
B.Managed identity of the Synapse workspace
C.Service principal with certificate
D.Microsoft Entra ID (Azure AD) pass-through authentication
E.Shared access signature (SAS) token
AnswersB, D, E

Serverless SQL pool can use the workspace's managed identity to access storage.

Why this answer

Managed identity of the Synapse workspace (Option B) is correct because Azure Synapse serverless SQL pool can use the workspace's system-assigned managed identity to authenticate to Azure Data Lake Storage Gen2 without storing any credentials. This is enabled by granting the managed identity the appropriate RBAC role (e.g., Storage Blob Data Contributor) on the storage account, and then using the `IDENTITY='Managed Identity'` parameter in the `OPENROWSET` or `CREATE EXTERNAL TABLE AS SELECT` statements. It provides a secure, credential-free authentication method that is fully supported by serverless SQL pool.

Exam trap

The trap here is that candidates often assume storage account keys (Option A) are universally supported across all Azure Synapse components, but serverless SQL pool explicitly does not support key-based authentication, requiring token-based methods instead.

18
Multi-Selecthard

Which TWO options are correct about using Azure Stream Analytics for real-time data processing?

Select 2 answers
A.You can join streaming data with static reference data for enrichment.
B.Stream Analytics supports windowing functions like Tumbling, Hopping, and Sliding windows natively.
C.Stream Analytics can output to any Azure data store without custom code.
D.Stream Analytics guarantees exactly-once processing semantics.
E.You can use custom JavaScript or C# functions for complex transformations.
AnswersA, B

Reference data joins are a key feature of Stream Analytics.

Why this answer

Correct: A (reference data joins for enrichment) and B (windowing functions for time-based aggregations). Option C is wrong because while Stream Analytics supports many output sinks, not all Azure data stores are supported out-of-the-box; some require custom code or connectors. Option D is wrong because Stream Analytics provides at-least-once processing semantics, not exactly-once; exactly-once depends on the sink configuration and output adapter.

Option E is incorrect because Stream Analytics natively supports custom JavaScript functions for complex transformations, but it does not natively support C# functions; C# functions require integration with Azure Functions, making this option misleading.

19
MCQeasy

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

A.The job is using a different runtime that does not support Python.
B.The file is too large for DBFS.
C.The job cluster does not have permission to access DBFS.
D.The file was uploaded to the workspace filesystem, not to DBFS.
AnswerD

Workspace files are not automatically in DBFS.

Why this answer

The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path.

Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue.

Option C is incorrect because the job cluster automatically has permission to access DBFS.

20
MCQhard

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

A.Increase the maximum query concurrency limit
B.Provision a dedicated SQL pool with more DTUs
C.Create a clustered columnstore index on the date column
D.Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*)
AnswerD

Partition elimination reduces data scanned.

Why this answer

Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Exam trap

The trap here is that candidates often confuse serverless SQL pool with dedicated SQL pool and incorrectly choose to create indexes or scale resources, not realizing that serverless SQL pool relies on external data partitioning and file-skipping techniques rather than internal indexing or provisioning.

How to eliminate wrong answers

Option A is wrong because increasing the maximum query concurrency limit does not improve the performance of a single query; it only allows more concurrent queries to run, which could even degrade individual query performance due to resource contention. Option B is wrong because provisioning a dedicated SQL pool with more DTUs increases costs and is not a serverless SQL pool feature; serverless SQL pool scales automatically and does not use DTUs, so this would be an expensive and incorrect solution. Option C is wrong because clustered columnstore indexes are not supported in serverless SQL pool; they are a feature of dedicated SQL pools, and creating one on a date column in a serverless context is not possible.

21
MCQhard

You are reviewing a Mapping Data Flow in Azure Data Factory that copies data from a CSV file to an Azure Synapse table. The data flow uses 'allowSchemaDrift: true' and 'validateSchema: false'. After running the pipeline, you notice that the target table does not have the expected columns. The CSV file sometimes has extra columns. What is the most likely reason?

A.Schema drift is not enabled on the source.
B.The sink mapping is explicitly defined and does not include auto-mapping for drifted columns.
C.The sink dataset has a fixed schema that does not allow drift.
D.The source dataset has a fixed schema that does not include extra columns.
AnswerB

The sink script defines input columns, so extra columns are not mapped.

Why this answer

When 'allowSchemaDrift' is enabled on the source, drifted columns are detected but will only be written to the sink if auto-mapping is used. If the sink mapping is explicitly defined (e.g., column-by-column mappings), it overrides auto-mapping and drifted columns are ignored. Since the target table is missing expected columns, the explicit mapping likely excludes the drifted columns.

Exam trap

The trap here is that candidates assume enabling 'allowSchemaDrift' on the source automatically writes all columns to the sink, but they overlook that explicit sink mappings override auto-mapping and exclude drifted columns.

How to eliminate wrong answers

Option A is wrong because the question states 'allowSchemaDrift: true' on the source, so schema drift is enabled. Option C is wrong because the sink dataset's schema does not control drift behavior at runtime; the sink's 'allowSchemaDrift' and mapping settings in the data flow determine whether drifted columns are written. Option D is wrong because the source dataset's schema does not prevent drift detection; the 'allowSchemaDrift' setting on the source overrides the dataset schema, and the CSV file's extra columns are still read as drifted columns.

22
Multi-Selecthard

You are developing a data processing pipeline in Azure Data Factory. The pipeline uses a mapping data flow to transform data. You need to ensure that the data flow can handle schema drift from the source. Which THREE of the following actions should you take? (Select THREE.)

Select 3 answers
A.Use a 'Select' transformation to explicitly choose only the known columns.
B.Enable 'Allow schema drift' in the source settings.
C.Set 'Ignore drift column types' in the sink settings to avoid type mismatch errors.
D.Use 'Column pattern' transformations to apply transformations to drifted columns.
E.Add a 'Derived Column' transformation for every possible column that might be added.
AnswersB, C, D

This allows the data flow to accept additional columns.

Why this answer

Enabling 'Allow schema drift' in the source settings of a mapping data flow tells Azure Data Factory to accept incoming columns that are not defined in the source schema. This is essential for handling schema drift, as it prevents the pipeline from failing when new columns appear in the source data.

Exam trap

The trap here is that candidates often think they must explicitly define every column or use static transformations to handle schema drift, when in fact Azure Data Factory's mapping data flow provides dynamic mechanisms like 'Allow schema drift', column patterns, and drift type handling to automatically accommodate unknown columns.

23
MCQhard

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

A.storageAccountType
B.maxSizeBytes
C.collation
D.sku.name
AnswerD

The sku name specifies the Data Warehouse Unit (DWU) level, e.g., DW100c or DW200c.

Why this answer

Sku.name. In an Azure Synapse Analytics dedicated SQL pool, the performance level (e.g., DW200c) is defined by the 'sku.name' parameter in the ARM template. Option A (storageAccountType) controls data redundancy, not performance.

Option B (maxSizeBytes) sets the maximum storage size. Option C (collation) determines the default collation for the database, which affects language support. Therefore, to adjust performance to DW200c, modify the sku.name parameter.

24
Multi-Selecthard

You are designing a data processing solution that must handle both batch and stream processing in a unified manner. You need to choose a technology that supports a single code base for both processing modes. Which TWO technologies meet this requirement?

Select 2 answers
A.Azure Databricks with Delta Live Tables
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure SQL Database with Change Tracking
E.Apache Spark Structured Streaming in Azure Synapse
AnswersA, E

Delta Live Tables supports both batch and streaming with a unified declarative pipeline.

Why this answer

Azure Databricks with Delta Live Tables (DLT) supports both batch and stream processing using a single code base by allowing you to define streaming and batch data sources declaratively in the same pipeline. DLT automatically handles incremental processing for streaming and full refreshes for batch, enabling unified logic without separate code paths.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics as a unified solution because it handles streaming, but they overlook that it lacks native batch processing support, whereas Apache Spark Structured Streaming (in Databricks or Synapse) truly enables a single code base for both modes.

25
Multi-Selecteasy

Which TWO actions should you take to optimize a Spark job in Azure Synapse Analytics that is experiencing excessive shuffling and long execution times?

Select 2 answers
A.Coalesce the number of partitions to reduce overhead.
B.Enable checkpointing to persist intermediate results.
C.Broadcast one of the tables if it is small enough to fit in memory.
D.Disable dynamic resource allocation.
E.Increase the number of shuffle partitions using 'spark.sql.shuffle.partitions'.
AnswersC, E

Broadcast join eliminates shuffle by replicating the small table to all executors.

Why this answer

Broadcasting a small table avoids shuffle operations during joins, as the small table is sent to all executors. Option E is correct because increasing the number of shuffle partitions (via spark.sql.shuffle.partitions) can distribute data more evenly, reducing the size of each shuffle block and improving parallelism. Option A is incorrect: coalescing reduces the number of partitions, which may increase the amount of data shuffled per task and worsen skew.

Option B is incorrect: checkpointing improves fault tolerance but does not directly reduce shuffling. Option D is incorrect: disabling dynamic resource allocation can lead to resource contention or waste, not optimized shuffling.

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

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

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

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

30
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'.

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

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

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

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

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

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

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

38
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'.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

55
MCQeasy

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

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

Supports exactly-once semantics with Event Hubs.

Why this answer

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

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

56
Multi-Selecthard

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

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

Provides ACID transactions and supports exactly-once semantics.

Why this answer

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

Exam trap

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

57
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

58
MCQeasy

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

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

This is the simplest and most cost-effective method.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

59
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

60
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

61
MCQeasy

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

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

Mapping data flows in Synapse Pipelines provide serverless, code-free transformation with minimal management.

Why this answer

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

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

62
MCQhard

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

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

Simplifies processing and handles schema evolution.

Why this answer

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

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

63
MCQmedium

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

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

Azure Stream Analytics provides sub-second latency stream processing and native output to Azure Data Lake Storage Gen2.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

64
MCQmedium

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

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

Simplifies staging and leverages PolyBase for efficient loading.

Why this answer

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

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

65
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

66
Multi-Selecteasy

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

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

Allows columns to be added without breaking the pipeline.

Why this answer

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

Exam trap

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

67
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

68
Multi-Selecthard

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

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

Round-robin distributes data evenly for fast load.

Why this answer

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

Exam trap

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

69
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

70
MCQmedium

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

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

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

Why this answer

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

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

71
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

72
Multi-Selectmedium

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

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

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

Why this answer

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

Exam trap

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

73
Multi-Selecthard

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

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

Auto-scaling helps handle variable workloads efficiently.

Why this answer

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

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

74
MCQmedium

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

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

Azure Stream Analytics provides exactly-once semantics and handles late-arriving data.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

75
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Page 1 of 4 · 261 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Develop data processing questions.