Design and develop data processing practice questions

Use Azure Data Factory to copy the JSON data into Azure SQL Database, then use T-SQL to transform.

Why wrong: Copies data unnecessarily.

Use Azure Data Factory with SSIS to transform and load into dedicated SQL pool.

Why wrong: SSIS is less optimal for this scenario.

Load data into a Spark DataFrame in Synapse notebooks, transform, and write back.

Why wrong: Loads data into memory, causing movement.

Use Azure Data Factory mapping data flows with schema drift enabled, mapping to a fixed sink schema.

Why wrong: Sink schema would reject new columns.

Define a fixed schema in the source and ignore any new columns.

Why wrong: Ignores drift, losing data.

Use Azure Stream Analytics to pre-process and enforce schema.

Why wrong: Batch processing, not streaming.

Increase the driver memory to 28 GB.

Why wrong: Driver memory not the bottleneck.

Increase the number of workers to 20.

Why wrong: Increases parallelism but may not address memory per task.

Reduce spark.sql.shuffle.partitions to 100.

Why wrong: Increases partition size, worsening OOM.

Azure Event Hubs -> Azure HDInsight (Kafka) -> Azure Cosmos DB

Why wrong: Overengineered for this scenario.

Azure IoT Hub -> Azure Databricks (Structured Streaming) -> Azure Cosmos DB

Why wrong: Databricks streaming can work but is less straightforward.

Azure Event Hubs -> Azure Data Factory -> Azure Cosmos DB

Why wrong: Data Factory is not real-time.

Use a TRUNCATE statement before each insert.

Why wrong: Not atomic; duplicates can occur if job fails after truncate.

Use a MERGE statement with a unique key to upsert data.

Why wrong: May still cause duplicates if source has same key multiple times.

Use CREATE TABLE AS SELECT (CTAS) with a unique constraint.

Why wrong: CTAS doesn't handle restarts well.

Azure SQL Database

Why wrong: Not designed for streaming.

Apache Kafka on HDInsight

Why wrong: Less integrated with Azure ecosystem.

Azure Data Lake Storage Gen2

Why wrong: Storage layer, not processing.

Increase the batch interval to reduce load.

Why wrong: May reduce throughput, not reliability.

Increase the cluster size to handle spikes.

Why wrong: Does not address checkpoint corruption.

Azure Data Factory

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

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.

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.

Storage event trigger using Azure Event Grid

Why wrong: Similar to event trigger, may cause duplicate processing.

Schedule trigger set to 02:00 UTC daily

Why wrong: Does not handle late-arriving data.

Event trigger on blob creation in the container

Why wrong: May fire multiple times for the same file and requires deduplication logic.

Convert the parquet files to CSV format to reduce metadata overhead

Why wrong: CSV is less efficient than parquet for analytical queries.

Enable soft delete on the storage account to reduce read latency

Why wrong: Soft delete does not affect read performance.

Apply Z-order clustering on the parquet files using Azure Databricks

Why wrong: Z-order improves within-file skipping, but the issue is partition pruning.

The self-hosted integration runtime version is outdated; update it to the latest version

Why wrong: Version updates are automatic and unlikely to cause sudden timeouts.

The copy activity is not using staging; enable staging through Azure Blob Storage

Why wrong: Staging helps with large transfers but not necessarily with timeouts.

The source query timeout in the copy activity is too low; increase it to 3600 seconds

Why wrong: Increasing timeout may delay failure but doesn't address root cause.

Increase the number of worker nodes in the cluster

Why wrong: More workers may not reduce the overhead of many small files.

Convert the CSV files to Parquet format

Why wrong: Parquet is efficient but doesn't reduce file count overhead.

Use Delta Lake caching to store the data in memory

Why wrong: Caching helps after first read, but initial read still slow.

Process all data synchronously to ensure consistency

Why wrong: Synchronous processing increases latency.

Use a single large cluster for all workloads to simplify management

Why wrong: May cause resource contention and doesn't meet SLAs.

Use a single node for orchestration to reduce complexity

Why wrong: Single node can become a bottleneck.

Integration with Power BI for real-time dashboards

Why wrong: Both integrate with Power BI.

Maximum allowed latency for late-arriving data

Why wrong: Both handle late data with windowing.

The integration runtime status is "Offline"

Why wrong: The status is Online.

Auto-update is disabled, preventing the IR from updating

Why wrong: Auto-update is enabled.

Self-contained interactive authoring is disabled, causing connectivity issues

Why wrong: Interactive authoring is enabled.

Increase the executor memory and cores in the Spark pool configuration to handle larger shuffles.

Why wrong: Increasing memory may help with data skew but does not address the root cause of reading the entire 5 TB dataset daily; the job will still take too long.

Repartition the data on the 'product_category' column with a higher number of partitions (e.g., 2000).

Why wrong: Repartitioning on a column with relatively low cardinality (product category) may cause uneven data distribution and additional shuffling overhead, worsening performance.

Use a broadcast join hint on the fact table to reduce shuffle operations.

Why wrong: Broadcast join is used for joining a small table with a large table; there is no join in the described job, so this is not applicable.

The data being transferred is skewed, causing the sink to be overwhelmed.

Why wrong: Data skew affects processing performance, not the ability to establish a TCP connection to the database.

The SQL Server database is experiencing high CPU utilization during the pipeline execution window.

Why wrong: High CPU would cause slow queries or timeouts but would not produce a TCP connection timeout error; the error is about establishing a connection, not executing queries.

The self-hosted integration runtime is running out of memory during peak loads.

Why wrong: Memory exhaustion would typically result in 'out of memory' errors or slow performance, not TCP connection timeouts.

Parquet is easier to implement for schema evolution than Delta Lake.

Why wrong: Delta Lake supports schema evolution more easily than Parquet.

Delta Lake reduces storage costs by automatically compressing data.

Why wrong: Delta Lake does not automatically compress data; storage costs depend on the underlying file format.

Parquet files are not natively supported by Azure Databricks.

Why wrong: Azure Databricks natively supports reading and writing Parquet files.

Modify the query to use TIMESTAMP BY on the EventHubs enqueued time instead of the event's Timestamp field.

Why wrong: Enqueued time reflects ingestion time, not the event time, which could cause incorrect windowing.

Change the tumbling window to a hopping window with a 1-minute hop size to increase the frequency of output updates.

Why wrong: Hopping windows still have fixed boundaries and do not handle late arrivals without a late arrival policy.

Add a second Stream Analytics job to process late-arriving events separately and union the results.

Why wrong: Adding a second job is complex and may still miss events unless properly coordinated.

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.

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.