Develop data processing practice questions

Use PolyBase to load data directly from Event Hubs to the dedicated SQL pool.

Why wrong: PolyBase is designed for batch loading from external data sources like Azure Blob Storage, not for streaming ingestion from Event Hubs.

Use COPY INTO statement to ingest data from Event Hubs into the dedicated SQL pool.

Why wrong: COPY INTO is for batch loading from files, not for streaming data from Event Hubs.

Enable Event Hubs Capture to write data to Azure Data Lake Storage and then load using PolyBase.

Why wrong: This approach adds latency and does not provide real-time streaming processing; also, it does not directly address transient failure recovery in the pipeline.

Increase the cluster size to reduce shuffle overhead.

Why wrong: Increasing cluster size may improve parallelism but does not reduce the amount of data shuffled.

Create bucketed tables on the join key for both datasets.

Why wrong: Bucketing can reduce shuffle if both tables are bucketed on the same key, but it requires pre-processing; broadcast join is simpler and more efficient.

Use Delta Lake and optimize file layout with OPTIMIZE command.

Why wrong: OPTIMIZE improves file layout but does not directly reduce shuffle during joins.

Enable checkpointing to truncate the lineage.

Why wrong: Checkpointing helps with lineage but does not free memory directly.

Decrease the number of partitions to reduce overhead.

Why wrong: Decreasing partitions may cause data skew and memory issues on fewer executors.

Use the cache() action on intermediate DataFrames.

Why wrong: Caching can help with iterative algorithms but increases memory usage.

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use the Copy activity with PolyBase enabled.

Why wrong: PolyBase alone does not handle incremental upserts; a stored procedure with merge logic is needed.

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and enable the built-in Upsert option.

Why wrong: The built-in Upsert option is not available for dedicated SQL pool in ADF copy activity.

Use Azure Blob Storage as the sink dataset, then use PolyBase to load into the dedicated SQL pool.

Why wrong: This adds an unnecessary intermediate step and does not provide built-in incremental loading.

Use a TumblingWindow with duration of 5 minutes and output every 5 minutes.

Why wrong: Tumbling windows output only at the end of each 5-minute window, not every minute.

Use a SlidingWindow with duration 5 minutes and output every 1 minute.

Why wrong: Sliding windows are event-driven and output for each event, not on a fixed schedule.

Use a SessionWindow with timeout 5 minutes and maximum duration 10 minutes.

Why wrong: Session windows are based on gaps in data, not suitable for fixed-interval rolling averages.

Enable caching on the DataFrame before writing.

Why wrong: Caching does not reduce the number of output files.

Apply bucketing on a column to group data.

Why wrong: Bucketing organizes data into buckets but does not necessarily reduce file count; it can increase it.

Increase the number of partitions using repartition() with a larger number.

Why wrong: More partitions result in more output files.

WriteSerializable

Why wrong: WriteSerializable is for write conflicts, not read isolation.

Serializable

Why wrong: Serializable is an isolation level but not specifically the one that reads only committed data; Snapshot is the default for reads.

ReadUncommitted

Why wrong: ReadUncommitted allows reading uncommitted data.

The cluster size is too small for the data volume.

Why wrong: 2 GB with 8 cores is generally sufficient; the error is not resource-related.

The CSV source contains malformed rows that cause parsing errors.

Why wrong: Malformed rows would cause a different error, not 'Operation aborted'.

The data flow is using the Spark cluster's default timeout setting.

Why wrong: Default Spark timeout is typically higher than 45 minutes.

Use a ForEach activity to iterate over a fixed number of pages.

Why wrong: ForEach requires a fixed array; pagination is dynamic and unknown upfront.

Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

Why wrong: This adds complexity; the Copy activity natively supports pagination.

Use an Until activity to loop until the API returns no more pages.

Why wrong: Until is used for conditional loops but not the most efficient; the Copy activity's built-in pagination is simpler.

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

Why wrong: Session window is based on inactivity, not fixed time.

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

Why wrong: Hopping window with hop 1 produces results every second, not every 10 seconds.

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)

Why wrong: Sliding window outputs for each event, not on a fixed interval.

PIVOT

Why wrong: PIVOT is for converting rows to columns, not flattening nested data.

UNNEST

Why wrong: UNNEST is not a T-SQL function; it exists in other SQL dialects like PostgreSQL.

CROSS APPLY

Why wrong: CROSS APPLY is used with table-valued functions, but not specifically for flattening nested columns in Parquet.

Use 'overwrite' mode and enable 'delta.autoOptimize.optimizeWrite' = true.

Why wrong: Overwrite replaces the entire table, not suitable for incremental dedup.

Use 'ignore' mode and set 'delta.autoCompact' = true.

Why wrong: Ignore mode skips writing if file exists, not dedup within batch.

Use 'error' mode and enable 'delta.merge.onSchemaMismatch' = true.

Why wrong: Error mode fails on duplicates, not desired.

In the source transformation, set the 'Optimize' tab to 'Use current partitioning'.

Why wrong: Source optimization affects read, not sink write partitions.

Decrease the cluster size to 4 cores.

Why wrong: Cluster size affects parallelism but not partition count directly.

In the pipeline activity, set the 'Data flow' property 'partitionOption' to 'Single partition'.

Why wrong: The partition option is set within the data flow transformations, not at pipeline level.

Azure Databricks with Structured Streaming

Why wrong: Databricks is separate from Synapse; the question specifies using Synapse.

Azure Synapse serverless SQL pool

Why wrong: Serverless SQL pool is for querying, not streaming ingest.

Azure Stream Analytics

Why wrong: Stream Analytics provides at-least-once semantics, not exactly-once.

Broadcast the smaller table (sessions) to all nodes.

Why wrong: Sessions table is huge (10B rows), broadcasting it would cause memory issues.

Use a sort-merge join by repartitioning both tables on device_id.

Why wrong: Sort-merge join still shuffles both tables, which is expensive.

Bucket both tables on device_id with 500 buckets.

Why wrong: Bucketing helps equi-joins but not range conditions on timestamp.

Decrease the 'writeBatchSize' to 1000.

Why wrong: Decreasing batch size reduces throughput and may increase duration.

Increase the 'timeout' value in the copy activity settings.

Why wrong: Increasing timeout may delay failure but does not address the root cause of long-running queries.

Use a query with 'queryTimeout' set to 7200 seconds.

Why wrong: Query timeout is for source query execution, not for copy activity timeout.

Azure Blob Storage

Why wrong: Azure Blob Storage linked service is for Azure storage, not external FTP.

HTTP

Why wrong: HTTP linked service is for HTTP/HTTPS endpoints, not FTP.

Rest service

Why wrong: REST linked service is for REST APIs.

Round-robin distribute the dimension table.

Why wrong: Round-robin still requires data movement for join.

Hash-distribute the dimension table on its primary key.

Why wrong: Hash distribution would require shuffling during join with fact table.

Hash-distribute the dimension table on the foreign key column.

Why wrong: Still requires shuffle; replication is better.

Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.

Why wrong: bcp does not support incremental loading and requires manual file management.

Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.

Why wrong: Using ADF copy activity adds overhead of moving data through an intermediate staging area.

Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.

Why wrong: Azure Databricks introduces an additional compute layer, increasing complexity and latency.

Modify the source data to truncate varchar(max) columns to 8000 characters.

Why wrong: Truncating data may cause data loss and is not recommended.

Increase the 'batchSize' property in the copy activity to 10000.

Why wrong: Batch size controls the number of rows per batch, not column size limits.

Disable PolyBase in the sink settings and use bulk insert instead.

Why wrong: Disabling PolyBase may resolve the error but reduces performance.