Scenario practice questions

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

Trap 1: Implement Azure ExpressRoute to provide dedicated bandwidth.

Overkill and costly for this issue.

Trap 2: Increase the 'Polling Interval' setting in the copy activity.

Polling interval affects status check, not connectivity.

Trap 3: Migrate the self-hosted IR to Azure-SSIS IR.

Expensive and not a direct solution for load.

A
Implement Azure ExpressRoute to provide dedicated bandwidth.
Why wrong: Overkill and costly for this issue.
B
Increase the 'Polling Interval' setting in the copy activity.
Why wrong: Polling interval affects status check, not connectivity.
C
Scale out the self-hosted IR by adding more nodes to the cluster.
Distributes load and improves throughput.
D
Migrate the self-hosted IR to Azure-SSIS IR.
Why wrong: Expensive and not a direct solution for load.

Question 2easymultiple choice

A company ingests streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time to detect anomalies and stored in Azure Data Lake Storage Gen2 for historical analysis. The solution must minimize latency and avoid duplicate processing. Which Azure service should be used for processing?

Trap 1: Azure Data Factory

Azure Data Factory is for batch orchestration, not real-time streaming.

Trap 2: Azure Databricks with Structured Streaming

Azure Databricks can process streams but has higher latency and complexity compared to Stream Analytics for this scenario.

Trap 3: Azure Functions with Event Hubs trigger

Azure Functions can process events but lacks built-in stream processing features like windowing and exactly-once.

A
Azure Data Factory
Why wrong: Azure Data Factory is for batch orchestration, not real-time streaming.
B
Azure Databricks with Structured Streaming
Why wrong: Azure Databricks can process streams but has higher latency and complexity compared to Stream Analytics for this scenario.
C
Azure Functions with Event Hubs trigger
Why wrong: Azure Functions can process events but lacks built-in stream processing features like windowing and exactly-once.
D
Azure Stream Analytics
Azure Stream Analytics provides low-latency stream processing with exactly-once semantics and integrates with Event Hubs and Data Lake Storage.

Question 3mediummultiple choice

You are using Azure Data Factory to load data from an on-premises SQL Server to Azure Synapse Analytics. The data size is 500 GB, and you need to minimize the load time. The network bandwidth is limited. Which approach should you use?

Trap 1: Use a self-hosted integration runtime and copy directly to Synapse.

Direct copy over limited bandwidth is slow.

Trap 2: Use Azure ExpressRoute to improve network bandwidth.

ExpressRoute may not be feasible or cost-effective.

Trap 3: Use an Azure integration runtime and copy to Azure Data Lake…

Azure IR cannot access on-premises directly.

A
Use a self-hosted integration runtime and copy directly to Synapse.
Why wrong: Direct copy over limited bandwidth is slow.
B
Use a self-hosted integration runtime and stage the data in Azure Blob Storage before loading to Synapse.
Staging improves performance by splitting the copy into two phases.
C
Use Azure ExpressRoute to improve network bandwidth.
Why wrong: ExpressRoute may not be feasible or cost-effective.
D
Use an Azure integration runtime and copy to Azure Data Lake Storage, then to Synapse.
Why wrong: Azure IR cannot access on-premises directly.

Question 4hardmultiple choice

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?

Exhibit

{
  "name": "CopyDataFromBlobToSynapse",
  "properties": {
    "activities": [
      {
        "name": "CopyData",
        "type": "Copy",
        "inputs": [{"referenceName": "BlobSource", "type": "DatasetReference"}],
        "outputs": [{"referenceName": "SynapseSink", "type": "DatasetReference"}],
        "typeProperties": {
          "source": {
            "type": "DelimitedTextSource",
            "storeSettings": {
              "type": "AzureBlobStorageReadSettings",
              "recursive": true
            }
          },
          "sink": {
            "type": "SqlPoolSink",
            "writeBatchSize": 10000,
            "preCopyScript": "TRUNCATE TABLE dbo.Sales"
          },
          "translator": {
            "type": "TabularTranslator",
            "mappings": [
              {"source": "ProductID", "sink": "ProductID"},
              {"source": "SaleDate", "sink": "SaleDate"},
              {"source": "Quantity", "sink": "Quantity"},
              {"source": "Amount", "sink": "Amount"}
            ]
          }
        }
      }
    ]
  }
}

Trap 1: Add a filter in the copy activity to exclude rows with NULL Amount

Filtering would lose data, which is not ideal.

Trap 2: Modify the sink table to have a default value for the Amount column

Setting a default value does not help if the source provides NULLs; the copy activity would still fail.

Trap 3: Change the sink table column to allow NULLs

This may not be permitted due to business requirements and could cause issues later.

A
Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0
Mapping Data Flow allows you to handle NULLs by providing a default value, ensuring data integrity.
B
Add a filter in the copy activity to exclude rows with NULL Amount
Why wrong: Filtering would lose data, which is not ideal.
C
Modify the sink table to have a default value for the Amount column
Why wrong: Setting a default value does not help if the source provides NULLs; the copy activity would still fail.
D
Change the sink table column to allow NULLs
Why wrong: This may not be permitted due to business requirements and could cause issues later.

Read the full network assurance explanation →

Question 5mediummultiple choice

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

Trap 1: Azure Table Storage.

Table Storage is a key-value store without real-time analytical capabilities.

Trap 2: Azure SQL Database with in-memory OLTP.

In-memory OLTP is for transactional workloads, not high-throughput streaming analytics.

Trap 3: Azure Cosmos DB with analytical store.

Cosmos DB analytical store is for near-real-time analytics but not optimized for 10K events/sec with sub-second query latency.

A
Azure Table Storage.
Why wrong: Table Storage is a key-value store without real-time analytical capabilities.
B
Azure SQL Database with in-memory OLTP.
Why wrong: In-memory OLTP is for transactional workloads, not high-throughput streaming analytics.
C
Azure Cosmos DB with analytical store.
Why wrong: Cosmos DB analytical store is for near-real-time analytics but not optimized for 10K events/sec with sub-second query latency.
D
Azure Data Explorer (ADX).
ADX is designed for real-time analytics on large volumes of streaming data with sub-second query latency.

Question 6hardmultiple choice

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Trap 1: Ingest data via Azure Functions and write to Data Lake Storage; use…

Azure Functions add operational overhead and latency for real-time dashboards.

Trap 2: Use Azure Databricks with Structured Streaming to write to Data…

Operational overhead of managing clusters.

Trap 3: Ingest data to Azure Event Hubs, then use Event Hubs Capture to…

Event Hubs Capture is batch, not real-time for dashboards.

A
Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake
Why wrong: Azure Functions add operational overhead and latency for real-time dashboards.
B
Use Azure Stream Analytics to output to both Power BI and Data Lake Storage
Stream Analytics is serverless, supports real-time output to Power BI and batch writes to Data Lake.
C
Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery
Why wrong: Operational overhead of managing clusters.
D
Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs
Why wrong: Event Hubs Capture is batch, not real-time for dashboards.

Question 7easymulti select

You need to design a storage solution for a data lake that will be used by multiple teams for analytics. The solution must support fine-grained access control, versioning of files, and integration with Azure Purview for data cataloging. Which THREE features should you enable in Azure Data Lake Storage Gen2?

Trap 1: Managed identity authentication

Managed identity is an authentication method, not a feature of Data Lake Storage.

Trap 2: Soft delete

Soft delete is for recovering deleted data, not versioning.

A
Integration with Microsoft Purview
Purview integrates with Data Lake Storage for data cataloging.
B
Managed identity authentication
Why wrong: Managed identity is an authentication method, not a feature of Data Lake Storage.
C
Blob versioning
Versioning allows maintaining multiple versions of a file.
D
Access control lists (ACLs)
ACLs provide fine-grained permissions for directories and files.
E
Soft delete
Why wrong: Soft delete is for recovering deleted data, not versioning.

Question 8mediummulti select

You are designing a data storage solution for a manufacturing company that collects sensor data from machines. The data is stored in Azure Data Lake Storage Gen2. You need to ensure that the solution can handle large volumes of streaming data (up to 100 MB/s) and provide real-time dashboards. Which TWO services should you include?

Trap 1: Azure Analysis Services

Analysis Services is for multidimensional models, not real-time streaming.

Trap 2: Azure Data Factory

Data Factory is for batch data integration, not real-time streaming.

Trap 3: Azure Databricks

Databricks can handle streaming but is overkill for simple real-time dashboards and may introduce latency.

A
Azure Analysis Services
Why wrong: Analysis Services is for multidimensional models, not real-time streaming.
B
Azure Data Factory
Why wrong: Data Factory is for batch data integration, not real-time streaming.
C
Azure Databricks
Why wrong: Databricks can handle streaming but is overkill for simple real-time dashboards and may introduce latency.
D
Azure Stream Analytics
Stream Analytics processes streaming data and can output to real-time dashboards.
E
Azure Event Hubs
Event Hubs can ingest high-throughput streaming data.

Question 9mediummultiple choice

A company uses Azure Synapse Analytics with dedicated SQL pools. They need to allow a data scientist to read all tables in the 'sales' schema but prevent access to columns containing personally identifiable information (PII). Which feature should be used?

Trap 1: Dynamic data masking

Dynamic data masking obfuscates data but does not prevent access to the underlying data.

Trap 2: Row-level security

Row-level security filters rows based on user context, not columns.

Trap 3: Azure Active Directory authentication

Azure AD authentication controls identity, not column-level access.

A
Dynamic data masking
Why wrong: Dynamic data masking obfuscates data but does not prevent access to the underlying data.
B
Row-level security
Why wrong: Row-level security filters rows based on user context, not columns.
C
Column-level security
Column-level security restricts access to specific columns based on user or role.
D
Azure Active Directory authentication
Why wrong: Azure AD authentication controls identity, not column-level access.

Question 10easymultiple choice

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

Trap 1: Azure Cosmos DB

Cosmos DB is a NoSQL database, not relational.

Trap 2: Azure Synapse Analytics

Synapse Analytics is for large-scale analytics, not transactional workloads.

Trap 3: Azure Data Lake Storage Gen2

Data Lake Storage is for big data analytics, not relational OLTP.

A
Azure Cosmos DB
Why wrong: Cosmos DB is a NoSQL database, not relational.
C
Azure Synapse Analytics
Why wrong: Synapse Analytics is for large-scale analytics, not transactional workloads.
D
Azure Data Lake Storage Gen2
Why wrong: Data Lake Storage is for big data analytics, not relational OLTP.

Question 11hardmultiple choice

Your Azure Data Factory pipeline uses a Self-Hosted Integration Runtime (SHIR) to copy data from an on-premises SQL Server to Azure Blob Storage. The copy activity is failing with a timeout error after 30 minutes. The data volume is 50 GB. You need to optimize the data transfer performance. Which configuration change should you make first?

Trap 1: Enable staging copy via Azure Blob Storage

Staging is for cross-region or different stores, not performance

Trap 2: Increase the 'Activity retry' count

Retries do not improve performance

Trap 3: Reduce the 'Data Integration Unit' (DIU) setting

Reducing DIU would decrease performance

A
Increase the 'Degree of copy parallelism'
Parallelism improves throughput for large data
B
Enable staging copy via Azure Blob Storage
Why wrong: Staging is for cross-region or different stores, not performance
C
Increase the 'Activity retry' count
Why wrong: Retries do not improve performance
D
Reduce the 'Data Integration Unit' (DIU) setting
Why wrong: Reducing DIU would decrease performance

Question 12hardmultiple choice

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

Exhibit

Refer to the exhibit.

{
  "name": "IncrementalLoad",
  "properties": {
    "activities": [
      {
        "name": "WatermarkQuery",
        "type": "Lookup",
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT MAX(LastModified) AS NewWatermark FROM source_table"
          },
          "dataset": {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        }
      },
      {
        "name": "CopyData",
        "type": "Copy",
        "dependsOn": [
          {
            "activity": "WatermarkQuery",
            "dependencyConditions": ["Succeeded"]
          }
        ],
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT * FROM source_table WHERE LastModified > '@{activity('WatermarkQuery').output.firstRow.NewWatermark}'"
          },
          "sink": {
            "type": "AzureSqlSink",
            "preCopyScript": "TRUNCATE TABLE target_table"
          }
        },
        "inputs": [
          {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        ],
        "outputs": [
          {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        ]
      }
    ]
  }
}

Trap 1: The dependency condition should be 'Completed' instead of…

'Succeeded' is the correct condition for a successful lookup.

Trap 2: The WatermarkQuery activity failed, causing the CopyData activity…

The dependency condition is 'Succeeded', so if WatermarkQuery succeeded, CopyData runs.

Trap 3: The CopyData activity runs before the WatermarkQuery activity…

The dependsOn ensures CopyData runs after WatermarkQuery succeeds.

A
The dependency condition should be 'Completed' instead of 'Succeeded'.
Why wrong: 'Succeeded' is the correct condition for a successful lookup.
B
The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
Why wrong: The dependency condition is 'Succeeded', so if WatermarkQuery succeeded, CopyData runs.
C
The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.
The copy query filters for rows where LastModified > NewWatermark, but NewWatermark is the maximum, so no rows satisfy the condition. The previous watermark should be stored and used.
D
The CopyData activity runs before the WatermarkQuery activity completes.
Why wrong: The dependsOn ensures CopyData runs after WatermarkQuery succeeds.

Question 13mediummulti select

You are designing an Azure Stream Analytics job to process real-time IoT data from thousands of devices. The job must handle late-arriving events (up to 1 hour late) and out-of-order events (up to 5 minutes). Which two temporal policies should you configure?

Trap 1: Out of order tolerance window: 1 hour; Late arrival tolerance…

Incorrect. This option reverses the two windows, setting the out-of-order tolerance to 1 hour (too large) and the late arrival tolerance to 5 minutes (too small).

Trap 2: Watermark delay: 1 hour; Out of order tolerance: 5 minutes

Incorrect. 'Watermark delay' is not a configurable temporal policy in Azure Stream Analytics. This option uses terminology from Spark Structured Streaming and does not correspond to a valid ASA setting.

Trap 3: Use Event Hubs capture to handle late events; no additional…

Incorrect. Event Hubs capture is used for storing raw events to Azure Blob Storage or Data Lake Store, not for handling late-arriving or out-of-order events in the stream processing logic.

A
Out of order tolerance window: 5 minutes; Late arrival tolerance window: 1 hour
Ly sets the out-of-order tolerance window to 5 minutes and the late arrival tolerance window to 1 hour, matching the scenario requirements.
B
Out of order tolerance window: 1 hour; Late arrival tolerance window: 5 minutes
Why wrong: Incorrect. This option reverses the two windows, setting the out-of-order tolerance to 1 hour (too large) and the late arrival tolerance to 5 minutes (too small).
C
Watermark delay: 1 hour; Out of order tolerance: 5 minutes
Why wrong: Incorrect. 'Watermark delay' is not a configurable temporal policy in Azure Stream Analytics. This option uses terminology from Spark Structured Streaming and does not correspond to a valid ASA setting.
D
Use Event Hubs capture to handle late events; no additional configuration needed
Why wrong: Incorrect. Event Hubs capture is used for storing raw events to Azure Blob Storage or Data Lake Store, not for handling late-arriving or out-of-order events in the stream processing logic.

Question 14hardmultiple choice

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

Trap 1: The Azure Blob Storage account is throttling requests.

Error is on source side.

Trap 2: The authentication method to SQL Server is incorrect.

Would cause persistent failure.

Trap 3: The self-hosted integration runtime is not connected to the network.

Would fail consistently.

A
The SQL Server is experiencing resource contention or timeout due to heavy load.
Intermittent failures often due to resource pressure.
B
The Azure Blob Storage account is throttling requests.
Why wrong: Error is on source side.
C
The authentication method to SQL Server is incorrect.
Why wrong: Would cause persistent failure.
D
The self-hosted integration runtime is not connected to the network.
Why wrong: Would fail consistently.