Hard Difficulty Questions

Create a nonclustered index on the date column.

Why it fails: Helps but less effective than partitioning for large scans.

Create a clustered columnstore index on the table.

Why it fails: A clustered columnstore index already exists by default on dedicated SQL pool tables, so creating one changes nothing. Columnstore suits large fact tables and is the right structure for analytical scans; the full scan persists because the date filter cannot be pushed down without partitioning or suitable indexing on that predicate.

Change the distribution to round-robin.

Why it fails: Round-robin distribution spreads rows evenly but provides no co-location for joins, so it cannot eliminate the full scan on a date filter. It is chosen when no natural distribution key exists or to avoid skew; here a date-based partition or hash distribution would prune the scanned data.

Repartition the table by OrderStatus

Why it fails: Repartitioning by status could cause data skew and does not replace partitioning by month.

Change the distribution key to include OrderStatus

Why it fails: Changing distribution key would require rebuilding the table and may not improve status queries.

Create a materialized view that aggregates by OrderStatus and month

Why it fails: A materialized view could help but is overkill if indexing solves the problem.

Increase the cluster size to reduce shuffle overhead.

Why it fails: 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 it fails: Creating bucketed tables is a valuable optimisation for minimising shuffle in large-large joins, as it enables bucket-aware processing where only corresponding buckets are joined. However, for a join between a 10 billion-row and a 1 million-row dataset, Databricks' optimiser, particularly with Photon runtime, would typically employ a broadcast hash join. This strategy eliminates shuffle for the larger table entirely by sending the smaller dataset to all executors, which is a more effective shuffle reduction mechanism in this specific large-small scenario than bucketing both datasets.

Use Delta Lake and optimize file layout with OPTIMIZE command.

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

Increase the DWU setting to allocate more resources.

Why it fails: Increasing DWUs allocates more resources and relieves contention, but the requirement explicitly forbids scaling DWUs, so it violates the constraint. This action suits planned peak-load capacity increases where cost is acceptable, not optimisation within fixed resources.

Create materialized views for frequently joined tables.

Why it fails: Materialised views pre-compute joins, cutting repeated work, but they do not address queuing caused by concurrency limits on DWUs. They suit recurring, expensive aggregation queries on stable data, not contention between many concurrent queries competing for the same resource classes.

Implement workload classification and assign the queries to a higher importance level.

Why it fails: Raising importance reorders the queue; it does not create capacity, so total wait time is unchanged and lower-importance queries starve. Workload classification suits governing mixed workloads with defined SLAs, not relieving genuine resource contention without adding DWUs.

Create a private endpoint for the storage account and assign the data-scientists group to the private endpoint's access policy

Why it fails: Assigning the Storage Blob Data Reader role at the storage account level would grant the data-scientists group read access to all containers, but you cannot create an RBAC deny assignment that blocks all other users while allowing this group. RBAC deny assignments override allowed assignments globally, not per user/group.

Assign the Storage Blob Data Reader role to the data-scientists group at the storage account level and add a deny assignment for all other users

Why it fails: Azure RBAC does not support deny assignments for custom user scopes; deny assignments are created only by Azure itself. It is tempting because role assignments at storage account scope grant broad access, and deny assignments exist for platform-managed exclusions, but they cannot be authored to block other users here.

Use a managed identity for the data-scientists group and assign the Storage Blob Data Contributor role to the managed identity

Why it fails: Managed identities are used for authentication from Azure services, not for granting permissions to a specific user group. Assigning Storage Blob Data Contributor to a managed identity does not give the data-scientists group any access.

Blob Capacity

Why it fails: Storage metric, not pipeline performance.

SQL Pool DWU Used

Why it fails: Metric for Synapse SQL pool, not ADF.

Increase the batch interval to reduce load.

Why it fails: May reduce throughput, not reliability.

Increase the cluster size to handle spikes.

Why it fails: Does not address checkpoint corruption.

Using INSERT INTO VALUES

Why it fails: INSERT INTO VALUES loads data row by row, which is extremely inefficient in a distributed MPP system and is not recommended for any significant data volume.

Using BCP utility

Why it fails: BCP is a command-line utility that performs bulk data imports and exports, and it is fully supported in Azure Synapse SQL Pool.

Using SQL Server Integration Services (SSIS)

Why it fails: SSIS is a robust ETL tool that can connect to Synapse SQL Pool and load data efficiently, making it a valid choice.

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

Why it fails: 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 it fails: 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 it fails: SHIR TTL is for keeping warm, not for copy activity timeout.

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

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

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

Why it fails: 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 it fails: COPY INTO is optimized for smaller loads; CTAS is better for large-scale loads.

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

Why it fails: 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 it fails: Recursive can be true; it doesn't cause failure.

The writeBatchSize is too large for the sink.

Why it fails: WriteBatchSize of 10000 is reasonable.

Convert the parquet files to CSV format to reduce metadata overhead

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

Enable soft delete on the storage account to reduce read latency

Why it fails: Soft delete does not affect read performance.

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

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

Increase the MAXDOP setting

Why it fails: MAXDOP governs intra-query parallelism across CPU cores, leaving the partition-scanning behaviour of OPENROWSET untouched. It is tempting because parallelism tuning often speeds serverless queries, but pruning scanned files through folder partitioning or explicit file paths addresses the actual bottleneck.

Create materialized views on the external tables

Why it fails: Materialised views are designed to pre-compute and store the results of complex queries, significantly improving performance for repetitive analytical workloads within a dedicated SQL pool. However, serverless SQL pools query external data directly using `OPENROWSET` and do not support creating materialised views on these external tables. Therefore, this option cannot address the slow query performance caused by inefficient partition scanning in the `OPENROWSET` function. It is tempting because materialised views are a powerful optimisation in other Synapse contexts.

Partition the underlying data by a frequently filtered column

Why it fails: Partitioning the underlying files only helps if the query filters on the partition column; the stem shows the scan already spans multiple partitions, so the engine still reads them all. Partitioning is for pruning data at write time, not for accelerating ad-hoc openrowset scans.

Grant the 'Manager' role SELECT permission on the security policy.

Why it fails: Incorrect. Granting SELECT permission on a security policy is not applicable; security policies are used for row-level security, not column-level.

Create a security policy with a filter predicate on the salary column using the function, and set the state to ON with BLOCK predicate.

Why it fails: Incorrect. This describes row-level security using a security policy with a filter predicate, which filters rows, not columns.

Create an inline table-valued function that returns 1 if the user is a member of the 'Manager' role, else 0.

Why it fails: Incorrect. This describes the predicate function for row-level security, not column-level security.

Use GRANT SELECT ON OBJECT::[dbo].[Employee](Salary) TO [Manager] to grant access to the salary column.

Why it fails: GRANT SELECT on a specific column is not supported in Azure Synapse dedicated SQL pool.

The Parquet files are not using the .parquet extension.

Why it fails: Serverless SQL identifies Parquet by content, not filename extension, so files lacking .parquet are still read and would not raise a schema-mismatch error. It is tempting because extension-based format detection is common elsewhere, but the real cause is inconsistent column definitions between files.

The derivedModel option is set to false, which disables schema inference.

Why it fails: No derivedModel setting exists in Synapse serverless OPENROWSET or external tables; schema inference is governed by WITH clauses and file consistency. It is tempting because schema inference genuinely can be toggled in some engines, but the mismatch here stems from differing Parquet schemas across files.

The recursive option is causing the table to include files from subfolders that have different schemas.

Why it fails: The recursive option only controls whether subfolders are traversed; it does not reconcile differing column schemas, and the mismatch occurs among files in the specified folder. It is tempting because recursion can pull in unexpected files, but that yields extra rows, not schema errors.

A VIEW that selects only the allowed columns

Why it fails: A VIEW that selects only the allowed columns is required because it provides an additional layer of abstraction and security, ensuring that users only see permitted columns without needing to manage individual column permissions on tables.

A DENY statement on specific columns to users or roles

Why it fails: DENY is not required; absence of GRANT is enough to block access.

A row-level security policy must be in place

Why it fails: RLS is for rows, not columns.

The database user must have a default schema

Why it fails: A default schema is required for the user to access objects.

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

Why it fails: 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 it fails: 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 it fails: PolyBase is not the recommended approach for serverless SQL pool.

Increase the MAXDOP setting in the query.

Why it fails: MAXDOP governs intra-query parallelism for a single statement, not the data-scan efficiency that dominates serverless SQL pool performance over Parquet. It is tempting because parallelism sounds like a tuning lever, but serverless pools auto-scale, and the real gains come from partitioning, file sizing, and selecting only needed columns.

Convert the Parquet files to CSV format for faster parsing.

Why it fails: Converting Parquet to CSV removes columnar storage and predicate pushdown, forcing full row reads and slower scans in serverless SQL pool. It is tempting because CSV is universally readable and simple to inspect, but Parquet's columnar compression and statistics are precisely what serverless SQL pool exploits for performance.

Create materialized views on the external tables.

Why it fails: Serverless SQL pool does not support materialized views on external tables; the feature exists only in dedicated SQL pools. It is tempting because materialised views accelerate repeated queries, but in serverless the correct approach is partitioning, file compaction, and statistics on external data.

Change the distribution to round-robin on OrderDate

Why it fails: Round-robin distribution would cause data movement for joins.

Create a materialized view on OrderDate

Why it fails: A materialized view on OrderDate would not bypass the hash distribution on CustomerID, so queries filtering by date range still require a full scan of all distributions to locate relevant rows. This option is tempting because materialized views pre-aggregate data and can accelerate queries with filtering or grouping on the indexed column, making them correct when the distribution key aligns with the filter column.

Create a non-clustered index on OrderDate

Why it fails: Dedicated SQL pool does not support non-clustered indexes; only clustered columnstore indexes.

Sets the default blob service version of all storage accounts to '2020-10-02'

Why it fails: Incorrect. The policy denies non-compliant resources, it does not set the version.

Audits storage accounts to check if their default blob service version is set to a value other than '2020-10-02'

Why it fails: Incorrect. The policy uses the 'deny' effect, not 'audit'.

Denies the creation of storage accounts that have the default blob service version set to '2020-10-02'

Why it fails: Incorrect. The policy denies if the version is not set to '2020-10-02', not if it is set to that value.