Scenario practice questions

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 2mediummultiple 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 3hardmulti select

Which TWO options are valid ways to load data into Azure Synapse SQL Pool? (Choose two.)

Trap 1: Using INSERT INTO VALUES

Incorrect. INSERT is row-by-row and not suitable for bulk loading.

Trap 2: Using BCP utility

Incorrect. BCP is for SQL Server, not Synapse SQL Pool.

Trap 3: Using SQL Server Integration Services (SSIS)

Incorrect. SSIS is not directly supported for Synapse SQL Pool.

A
Using INSERT INTO VALUES
Why wrong: Incorrect. INSERT is row-by-row and not suitable for bulk loading.
B
Using BCP utility
Why wrong: Incorrect. BCP is for SQL Server, not Synapse SQL Pool.
C
Using the COPY statement
Correct. COPY is the preferred bulk load method.
D
Using SQL Server Integration Services (SSIS)
Why wrong: Incorrect. SSIS is not directly supported for Synapse SQL Pool.
E
Using PolyBase to load from Azure Blob Storage
Correct. PolyBase is a valid loading method.

Question 4mediummulti 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…

This swaps the policies; late arrival should be larger than out-of-order.

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

Event Hubs capture is for storing raw events, not for handling out-of-order or late arrival in Stream Analytics.

B
Out of order tolerance window: 1 hour; Late arrival tolerance window: 5 minutes
Why wrong: This swaps the policies; late arrival should be larger than out-of-order.
C
Watermark delay: 1 hour; Out of order tolerance: 5 minutes
Watermark delay is not directly configurable; it's derived from the two tolerance windows.
D
Use Event Hubs capture to handle late events; no additional configuration needed
Why wrong: Event Hubs capture is for storing raw events, not for handling out-of-order or late arrival in Stream Analytics.

Question 5mediummultiple choice

You are designing a data storage solution for a global e-commerce company. The company needs to store clickstream data from millions of users with high write throughput and low-latency reads for real-time analytics. The data is semi-structured and includes nested JSON objects. Which Azure data store should you recommend?

Trap 1: Azure Table Storage

Table Storage is a simple key-value store and does not support nested JSON queries efficiently.

Trap 2: Azure SQL Database

Azure SQL Database supports JSON but has limited write throughput for high-velocity data.

Trap 3: Azure Blob Storage

Blob Storage is not optimized for low-latency queries on nested JSON.

A
Azure Cosmos DB
Azure Cosmos DB provides high throughput, low-latency, and native JSON support.
B
Azure Table Storage
Why wrong: Table Storage is a simple key-value store and does not support nested JSON queries efficiently.
C
Azure SQL Database
Why wrong: Azure SQL Database supports JSON but has limited write throughput for high-velocity data.
D
Azure Blob Storage
Why wrong: Blob Storage is not optimized for low-latency queries on nested JSON.

Question 6easymultiple choice

A small business uses Azure SQL Database as their operational data store. They want to build a simple reporting solution that refreshes daily. The data volume is about 10 GB. They have limited budget and no dedicated data engineering team. They need to extract data from Azure SQL Database, perform basic transformations (e.g., aggregations, joins), and load into a reporting table in the same database. They want to minimize administrative effort and cost. Which approach should they take?

Option A: Use Azure Data Factory with a copy activity to copy data from the source to a staging table, then use a stored procedure activity to run T-SQL transformations.

Option B: Use Azure Logic Apps to periodically query the source and insert transformed data into the reporting table.

Option C: Use Azure Databricks notebooks scheduled via Azure Data Factory to perform the ETL.

Option D: Use Azure SQL Database elastic job to run a T-SQL script that performs the ETL within the database.

Trap 1: Option B

Not suitable for complex transformations.

Trap 2: Option A

Adds unnecessary cost and complexity.

Trap 3: Option C

Too heavy for small scale.

A
Option B
Why wrong: Not suitable for complex transformations.
B
Option A
Why wrong: Adds unnecessary cost and complexity.
C
Option D
Simple, cost-effective, and fits requirements.
D
Option C
Why wrong: Too heavy for small scale.

Read the full NAT/PAT explanation →

Question 7mediummultiple choice

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?

Trap 1: Store data in a normalized relational database structure.

Normalized relational models are not optimal for big data lake scenarios.

Trap 2: Use a data vault model with hubs, links, and satellites.

Data vault is for enterprise data warehousing, not specifically for lake storage.

Trap 3: Design a star schema with fact and dimension tables.

Star schema is for dimensional modeling, not for immutability or streaming.

A
Store data in a normalized relational database structure.
Why wrong: Normalized relational models are not optimal for big data lake scenarios.
B
Implement a medallion architecture with bronze, silver, and gold layers.
The medallion architecture provides data immutability and supports both processing paradigms.
C
Use a data vault model with hubs, links, and satellites.
Why wrong: Data vault is for enterprise data warehousing, not specifically for lake storage.
D
Design a star schema with fact and dimension tables.
Why wrong: Star schema is for dimensional modeling, not for immutability or streaming.

Question 8mediummultiple choice

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

Trap 1: Stored procedure activity

Stored procedures are for SQL databases, not file transformations.

Trap 2: Notebook activity with PySpark

While possible, data flow is more appropriate for no-code transformation.

Trap 3: Copy data activity

Copy activity does not support row-level transformations.

A
Stored procedure activity
Why wrong: Stored procedures are for SQL databases, not file transformations.
B
Notebook activity with PySpark
Why wrong: While possible, data flow is more appropriate for no-code transformation.
C
Copy data activity
Why wrong: Copy activity does not support row-level transformations.
D
Data flow activity
Data flows provide visual transformation with built-in mapping.

Question 9hardmulti select

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

Trap 1: Stream Analytics can output to any Azure data store without custom…

Output to some data stores may require custom adapters or Azure Functions.

Trap 2: Stream Analytics guarantees exactly-once processing semantics.

Stream Analytics provides at-least-once semantics; exactly-once depends on sink.

A
You can join streaming data with static reference data for enrichment.
Reference data joins are a key feature of Stream Analytics.
B
Stream Analytics supports windowing functions like Tumbling, Hopping, and Sliding windows natively.
These windowing functions are built-in.
C
Stream Analytics can output to any Azure data store without custom code.
Why wrong: Output to some data stores may require custom adapters or Azure Functions.
D
Stream Analytics guarantees exactly-once processing semantics.
Why wrong: Stream Analytics provides at-least-once semantics; exactly-once depends on sink.
E
You can use custom JavaScript or C# functions for complex transformations.
Custom functions are supported for advanced logic.

Question 10hardmultiple choice

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

Trap 1: Use Azure Data Factory with a Copy activity to load data into a…

Data Flow can be used but may be overkill; also partitioning not addressed.

Trap 2: Use Azure Databricks to read the CSV files, perform upserts, and…

Adds complexity and cost.

Trap 3: Use PolyBase to create external tables on the CSV files and then…

Does not handle upserts or late-arriving data.

A
Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.
Why wrong: Data Flow can be used but may be overkill; also partitioning not addressed.
B
Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.
Why wrong: Adds complexity and cost.
C
Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.
Why wrong: Does not handle upserts or late-arriving data.
D
Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.
Handles upserts and late-arriving data effectively.

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

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

Trap 1: Azure Cosmos DB with single-region writes and strong consistency

Strong consistency increases write latency and single-region limits throughput.

Trap 2: Azure Data Lake Storage Gen2 with hierarchical namespace

Data Lake Storage is optimized for analytics, not low-latency writes.

Trap 3: Azure Blob Storage with hot tier and append blobs

Blob storage has higher write latency and append blobs are not ideal for high-throughput ingestion.

B
Azure Cosmos DB with single-region writes and strong consistency
Why wrong: Strong consistency increases write latency and single-region limits throughput.
C
Azure Data Lake Storage Gen2 with hierarchical namespace
Why wrong: Data Lake Storage is optimized for analytics, not low-latency writes.
D
Azure Blob Storage with hot tier and append blobs
Why wrong: Blob storage has higher write latency and append blobs are not ideal for high-throughput ingestion.

Question 13easymultiple 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 14mediummultiple choice

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

Trap 1: Azure Cosmos DB

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

Trap 2: Azure Synapse Analytics

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

Trap 3: Azure Table Storage

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

A
Azure Cosmos DB
Why wrong: Cosmos DB is NoSQL and while it can be configured for strong consistency, it does not natively support complex joins across multiple tables as efficiently as a relational database.
C
Azure Synapse Analytics
Why wrong: Synapse is a data warehouse for analytics, not designed for transactional workloads with frequent updates.
D
Azure Table Storage
Why wrong: Table Storage is a NoSQL key-value store with limited query capabilities and no support for complex joins.