Develop data processing practice questions

Use Azure Synapse Pipeline with Auto-commit and checkpointing to process streaming data.

Why wrong: Incorrect because Azure Synapse Pipeline is batch-oriented and does not provide native streaming with checkpointing from Event Hubs.

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

Why wrong: Incorrect because PolyBase cannot directly read from Event Hubs; it requires a file-based source.

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

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

Azure Synapse serverless SQL pool

Why wrong: Incorrect. Azure Synapse serverless SQL pool is designed for on-demand querying of data in data lakes and cannot process real-time streaming data.

Azure Synapse Pipeline with Mapping Data Flow

Why wrong: Incorrect. Azure Synapse Pipeline with Mapping Data Flow is a batch-oriented data transformation tool; it does not support continuous streaming from Event Hubs and cannot guarantee exactly-once semantics for streaming data.

Azure Stream Analytics

Why wrong: Incorrect. Azure Stream Analytics can process streaming data from Event Hubs and output to ADLS Gen2, but it does not natively support Delta Lake as a sink with exactly-once semantics. Stream Analytics offers at-least-once semantics by default, and exactly-once is not guaranteed for Delta Lake outputs.

Stored Procedure activity to run a merge statement

Why wrong: Incorrect. A Stored Procedure activity can execute a merge statement in the target database, but it does not handle incremental extraction from the file source; it would require external logic to identify changes.

Copy activity with a full load every time

Why wrong: Incorrect. A full load every time would process all rows daily, which is inefficient and does not meet the incremental requirement.

Change data capture (CDC) resource

Why wrong: Incorrect. Change Data Capture (CDC) in Azure Data Factory is designed for database sources (e.g., SQL Server) that support native change tracking, not for flat files like CSV in Blob Storage.

Azure Synapse Analytics (dedicated SQL pool)

Why wrong: Azure Synapse Analytics dedicated SQL pool is a data warehouse optimized for batch and interactive queries; it cannot perform real-time stream processing. It can ingest streaming data via copy commands but does not execute continuous queries on the stream.

Azure Data Factory

Why wrong: Azure Data Factory is an ETL and data integration service for scheduled or event-triggered pipelines, not a real-time stream processing engine.

Azure Logic Apps

Why wrong: Azure Logic Apps is a workflow orchestration service for event-driven integrations, not designed for continuous stream processing.

Create a dedicated folder in Azure Blob Storage for CDC logs.

Why wrong: Not required; CDC uses its own internal tables.

Provision a staging Azure SQL Database.

Why wrong: Staging is not mandatory.

Configure a full load of the entire table first.

Why wrong: Full load is optional.

The network bandwidth is insufficient; use a faster network connection.

Why wrong: While bandwidth is low, the error is connection closed, not timeout due to bandwidth.

The copy activity is using a single parallel copy; increase the degree of copy parallelism.

Why wrong: Increasing parallelism can speed up but does not address timeout.

The Self-Hosted Integration Runtime (SHIR) has a time-to-live (TTL) setting that is too short; extend the TTL.

Why wrong: SHIR TTL is for keeping warm, not for copy activity timeout.

Partition the Parquet files by date

Why wrong: While Azure Synapse serverless SQL pool does support partition pruning by folder structure (e.g., date partitions), this only reduces the amount of data scanned. It does not help the optimizer make better cardinality estimates for joins and filters, which are often the root cause of slow performance. Statistics address the optimizer's lack of metadata, making them more impactful in this scenario.

Create a materialized view on the external data

Why wrong: Materialized views are not supported in serverless SQL pool.

Create a PolyBase external table and query that

Why wrong: PolyBase is for dedicated SQL pool, not serverless.

Periodically run VACUUM to remove old versions of files that are no longer needed.

Why wrong: Incorrect: While VACUUM removes old file versions for storage management, it is not a direct performance optimization best practice.

Partition on high-cardinality columns like UserID to maximize parallelism.

Why wrong: Incorrect: Partitioning on high-cardinality columns creates many small partitions, leading to metadata bloat and degraded performance.

Split the Parquet files into many small files (e.g., 64 MB each) to maximize parallelism.

Why wrong: Many small files increase overhead and can degrade performance.

Convert the Parquet files to CSV format before loading using PolyBase.

Why wrong: CSV is less efficient than Parquet for PolyBase loads.

Use the COPY INTO command to load the Parquet files directly into the dedicated SQL pool.

Why wrong: COPY INTO is optimized for smaller loads; CTAS is better for large-scale loads.

Reduce the number of shuffle partitions to minimize data movement.

Why wrong: Reducing shuffle partitions can cause out-of-memory errors and decrease parallelism.

Enable autoscaling on the cluster to add more nodes during processing.

Why wrong: Autoscaling adds nodes but may not resolve I/O bottleneck if the storage is the limit.

Id: 1, Name: Alice, Age: 30; Id: 3, Name: Charlie, Age: 35

Why wrong: The project clause removes Id column.

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Why wrong: Bob is included but his age is 25, not >25.

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Why wrong: Bob is filtered out because Age is not >25.

Auto-mapping fails because the source and sink have different column counts.

Why wrong: Auto-mapping maps by name but expects the same number of columns or explicit mapping for extras.

The recursive setting should be false for CSV files.

Why wrong: Recursive can be true; it doesn't cause failure.

The writeBatchSize is too large for the sink.

Why wrong: WriteBatchSize of 10000 is reasonable.

Reduce the degree of copy parallelism in the copy activity.

Why wrong: Reducing the degree of copy parallelism may increase the duration of the copy operation, potentially worsening the timeout.

Enable staged copy using Azure Blob Storage as an interim store.

Why wrong: Enabling staged copy using Azure Blob Storage as an interim store adds overhead and does not directly address the batch write timeout issue.

Scale up the Azure SQL Database to a higher DTU tier.

Why wrong: Scaling up the Azure SQL Database to a higher DTU tier increases cost and does not directly fix the batch write timeout, which is more efficiently addressed by adjusting write batch size.

The integration runtime type should be 'Self-hosted' for better performance.

Why wrong: Self-hosted is for on-premises or VM data sources, not for cloud-to-cloud.

The number of nodes (2) is insufficient for 500 GB of data.

Why wrong: Node count is less impactful than node size for this volume.

The location is set to 'AutoResolve' which may cause data movement across regions.

Why wrong: AutoResolve resolves to the same region as the workspace.

Use OPENROWSET with a wildcard path that includes all partitions and then apply a WHERE clause.

Why wrong: This would scan all partitions before filtering.

Create an external table with partition definition and query with a WHERE clause on the partition column.

Why wrong: External tables with partitioning do support partition elimination, but the question asks for the best approach among options; OPENROWSET is more direct.

Create a view using PolyBase external table and query with a date filter.

Why wrong: PolyBase is not the recommended approach for serverless SQL pool.

Increase the Data Integration Units (DIU) in the data flow.

Why wrong: Adding DIU improves parallelism but does not fix the underlying skew; skewed partitions remain slow.

Repartition the data on a different column using a random distribution.

Why wrong: Repartitioning may help but is not a direct skew optimization; it could even worsen performance.

Use Azure Synapse Spark with structured streaming to read from Event Hubs, flatten the JSON using Spark SQL, and write to the dedicated SQL pool.

Why wrong: While possible, this approach introduces more complexity and may not meet the 2-second latency requirement consistently.

Use Azure Data Factory to run a tumbling window trigger that reads from Event Hubs every 2 seconds and copies data to the dedicated SQL pool.

Why wrong: Data Factory does not support true streaming; tumbling windows introduce batch delays and cannot flatten nested JSON in real-time.

Use Azure Databricks with Auto Loader to ingest data from Event Hubs and write to the dedicated SQL pool.

Why wrong: Azure Databricks, while highly effective for real-time streaming from Event Hubs using Auto Loader and writing to a dedicated SQL pool, does not fulfil the core requirement of building the streaming pipeline *within Azure Synapse Analytics*. The scenario explicitly mandates Synapse as the platform for the pipeline. Databricks is a separate analytics service, not a native component for constructing a Synapse-centric streaming solution. Nevertheless, Databricks excels in complex real-time data ingestion, transformation, and machine learning, making it a strong choice when advanced Spark processing and flexible platform selection are priorities.

Configure the pipeline to retry on failure and manually restore from backup.

Why wrong: Incorrect. Retry on failure and manual restore are not automated and can result in significant data loss or downtime.

Use Azure SQL Database active geo-replication as the source.

Why wrong: Incorrect. Azure SQL Database active geo-replication provides HA for the SQL source, but the ADF pipeline itself remains in a single region and may still fail.

Use Azure Synapse Link for Cosmos DB to enable near real-time analytics with multi-region writes.

Why wrong: Incorrect. Synapse Link for Cosmos DB enables near real-time analytics, but it does not provide high availability for the existing pipeline; it requires architectural changes and does not ensure pipeline resilience in case of regional failure.

Use Azure Data Factory to flatten the JSON and store as Parquet.

Why wrong: This involves an extra transformation step, not direct query.

Create an external table using PolyBase.

Why wrong: PolyBase requires defining external tables, which is more setup than direct query.

Use the COPY INTO command in a dedicated SQL pool.

Why wrong: COPY INTO loads data into tables; it does not query directly.

spark.read.option("mergeSchema","true").csv(path)

Why wrong: Incorrect. `mergeSchema` is not a valid option for CSV files; it is used for Delta Lake or Parquet to handle schema evolution.

spark.read.format("delta").load(path)

Why wrong: Incorrect. `format("delta")` reads Delta Lake format, not CSV files.

spark.read.csv(path)

Why wrong: Incorrect. Reading CSV without options does not infer the schema or handle schema evolution.