Monitor and optimize data storage and processing practice questions

Decrease the 'Batch size' for the copy activity.

Why wrong: Smaller batches increase number of operations, potentially increasing timeout risk.

Change the sink to use PolyBase with staging enabled.

Why wrong: PolyBase is for bulk load into Synapse, not directly for timeout issues.

Increase the Data Integration Unit (DIU) to 8.

Why wrong: Higher DIU may not resolve timeout if the source is the bottleneck.

Run ZORDER BY on the date column.

Why wrong: Z-ordering improves data skipping, not file compaction.

Run VACUUM to delete old files.

Why wrong: VACUUM removes unreferenced files, does not compact.

Increase the number of partitions by adding a new partition column.

Why wrong: More partitions lead to more small files.

Reduce the number of Streaming Units.

Why wrong: Reducing SU would lower capacity.

Change the query compatibility level to 1.0.

Why wrong: Compatibility level affects language features, not performance.

Deploy a second Stream Analytics job and split the input.

Why wrong: Splitting across jobs adds complexity and does not directly address the existing job's bottleneck.

Create a clustered index on the region column.

Why wrong: Indexes are not supported in ADLS Gen2.

Compress the parquet files using gzip.

Why wrong: Compression reduces storage but does not reduce scan volume.

Enable hierarchical namespace on the storage account.

Why wrong: Hierarchical namespace organizes files, doesn't optimize queries.

Increase the cluster size to 16 nodes.

Why wrong: Scaling up may help but not as effectively as reducing transaction overhead.

Enable 'auto optimize' and 'optimized writes' on the Delta table.

Why wrong: These features optimize after write, not during.

Change the output format from Delta to Parquet.

Why wrong: Delta is built on Parquet; change wouldn't help.

Enable advanced data compression on all tables.

Why wrong: Compression saves storage, not compute.

Scale down the pool during business hours.

Why wrong: Scaling down during usage harms performance.

Change the distribution of large tables to ROUND_ROBIN.

Why wrong: Distribution affects query performance, not cost.

Blob Capacity

Why wrong: Storage metric, not pipeline performance.

SQL Pool DWU Used

Why wrong: Metric for Synapse SQL pool, not ADF.

Increase the number of nodes in the cluster to 16.

Why wrong: More nodes increase parallelism but the bottleneck is file listing, which is not fully parallelized.

Change the output format from JSON to Delta and enable Delta caching.

Why wrong: Delta caching helps reads but does not solve the file listing bottleneck.

Use Azure Data Factory instead of Databricks to copy the raw data.

Why wrong: ADF also lists files; the bottleneck remains.

Add a non-clustered index on frequently filtered columns.

Why wrong: Columnstore tables are optimized for scans; adding non-clustered indexes can help point lookups but not scan-heavy queries.

Change the distribution column to a column with higher cardinality.

Why wrong: The date key already has high cardinality; changing distribution key would not solve fragmentation and would require significant rework.

Change the distribution to round-robin.

Why wrong: Round-robin distribution distributes rows evenly but increases data movement during joins, potentially worsening performance.

The storage account is configured with geo-redundant storage (GRS) and data is being replicated to the secondary region.

Why wrong: ADLS Gen2 does not support GRS; replication is managed at the storage account level and would not cause ingress spikes without corresponding egress.

A Spark job is reading large amounts of data in parallel.

Why wrong: Reading data increases egress, not ingress.

An Azure Function is triggered by blob creation events and writes logs to the same account.

Why wrong: Blob creation events would appear in audit logs, contradicting the observation.

Decrease the Event Hub throughput units.

Why wrong: Reducing input rate may lower utilization but also reduces throughput, which is not an optimization goal.

Partition the output table in Azure Synapse Analytics.

Why wrong: Output partitioning improves write performance but does not affect the streaming job's processing utilization.

Use a reference data join to filter events.

Why wrong: Reference data joins can increase processing complexity and utilization.

DESCRIBE HISTORY delta_table;

Why wrong: This command only displays the history of changes, not cleanup.

OPTIMIZE delta_table;

Why wrong: OPTIMIZE compacts small files into larger ones but does not delete historical data.

FSCK REPAIR TABLE delta_table;

Why wrong: This command recovers missing files in the table directory but does not remove old versions.

Change the partition key to a different property.

Why wrong: Partition key affects data distribution but does not change total provisioned throughput.

Switch the account to serverless mode.

Why wrong: Serverless mode has its own scaling limits and may still throttle if consumption exceeds max RU/s.

Modify the indexing policy to exclude unused paths.

Why wrong: Reducing indexing cost per operation may help but the normalized RU consumption is about capacity utilization, not per-operation cost.

Enable soft delete for blobs to recover from accidental deletions.

Why wrong: Soft delete is a recovery feature, not a threat detection mechanism.

Configure firewall and virtual network service endpoints.

Why wrong: Network restrictions reduce exposure but do not detect threats.

Set up alerting on the 'Transactions' metric.

Why wrong: Metrics alone do not detect security threats; they measure performance.

Ensure data is evenly distributed across distributions.

Why wrong: Data distribution is a design aspect, not a monitoring strategy.

Configure automatic index rebuild for columnstore indexes.

Why wrong: Index maintenance is an optimization action, not a monitoring strategy factor.

To assign RBAC roles to users for the storage account.

Why wrong: RBAC role assignment is done via 'Microsoft.Authorization/roleAssignments/write'.

To enable delegation of access to a specific blob.

Why wrong: Blob-level delegation requires 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs' actions.

To allow users to set permissions on storage account containers.

Why wrong: Setting container permissions is done via different actions like 'container/update'.

Change the distribution of the fact table to round-robin and remove the unique constraint.

Why wrong: Removing the unique constraint allows duplicates, which is not acceptable for data integrity, and round-robin distribution may degrade performance.

Use a staging table and then execute a T-SQL MERGE statement to update or insert.

Why wrong: This approach works but is less efficient than using ADF's built-in upsert, and adds complexity.

Add a pre-copy script to delete existing rows that match the incoming data before the copy.

Why wrong: Deleting rows before insert may remove data that is not identical to incoming duplicates, causing data loss.

Implement Change Data Capture (CDC).

Why wrong: CDC tracks data changes, not performance optimization.

Enable Azure Search on the storage account.

Why wrong: Azure Search is a search service, not a storage optimization feature.

Use PolyBase to query the data.

Why wrong: PolyBase is for querying external data sources, not for internal optimization.

Redistribute the tables using hash distribution.

Why wrong: Distribution choice affects data movement, not memory per query.

Rebuild all columnstore indexes.

Why wrong: Rebuilding indexes improves performance but does not reduce memory spills.

Add partitioning to the tables.

Why wrong: Partitioning helps with data management, not memory allocation.

The hopping window size is too large.

Why wrong: Larger windows reduce processing frequency, not cause delay.

The late arrival tolerance is set too high.

Why wrong: Late arrival tolerance does not affect throughput.

The Event Hubs partition count does not match the Stream Analytics job's parallelism.

Why wrong: Partition count mismatch can cause issues, but the job can still handle input with enough SUs.