DP-203 Hard Questions — 25 Difficult Questions

Question 1

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Partition the table on the date column.. In Azure Synapse Analytics dedicated SQL pool, partitioning a large fact table on a date column is the most effective way to improve query performance when queries filter on that date column. Partitioning enables partition elimination, so the query only scans the relevant partitions instead of the entire table. While a clustered columnstore index (B) is the default and provides compression and performance benefits, it does not by itself eliminate full table scans for date filters unless partitioning is also applied. A nonclustered index (A) is not ideal for large fact tables in Synapse, and changing distribution to round-robin (C) would likely worsen performance for joins and aggregations.

Answer

Create a nonclustered index on the date column.

Answer

Create a clustered columnstore index on the table.

Answer

Change the distribution to round-robin.

Question 2

Your company uses Azure Synapse Analytics for its enterprise data warehouse. The main fact table, OrdersFact, is distributed using hash on OrderID. It has 10 billion rows. The table is partitioned by month. Recently, the data engineering team added a new column 'OrderStatus' that is used in many queries with filters like 'WHERE OrderStatus = 'Shipped''. These queries are scanning the entire table because the partition pruning is not effective. You need to improve query performance for these status-based queries without redesigning the entire table. What should you do?

Accepted Answer

Create a non-clustered columnstore index on OrderStatus. Creating a non-clustered columnstore index on OrderStatus allows SQL Server to perform batch mode processing and predicate pushdown, enabling efficient rowgroup elimination without requiring a full table scan. This index stores OrderStatus in a compressed, columnar format that can be quickly filtered, and it does not require redesigning the existing hash distribution or monthly partitioning.

Answer

Repartition the table by OrderStatus

Answer

Change the distribution key to include OrderStatus

Answer

Create a materialized view that aggregates by OrderStatus and month

Question 3

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 4

You are monitoring an Azure Synapse Analytics dedicated SQL pool that is experiencing performance degradation during peak hours. You notice that some queries are being queued due to resource contention. You need to optimize query performance without scaling the Data Warehouse Units (DWUs). Which action should you take?

Accepted Answer

Configure result-set caching for the dedicated SQL pool.. Result-set caching can significantly reduce query time for repeated queries by storing results in SSD, reducing resource contention. Option A is wrong because increasing DWUs changes the scale, which is not allowed per the requirement. Option B is wrong because materialized views help but do not directly address contention from repeated queries. Option D is wrong because workload classification manages concurrency but does not reduce resource usage for repeated queries.

Answer

Increase the DWU setting to allocate more resources.

Answer

Create materialized views for frequently joined tables.

Answer

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

Question 5

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to ensure that only the 'data-scientists' group can read files in the 'processed' container, while denying access to all other users. You have already configured the storage account firewall to allow access only from your corporate network. What should you do next?

Accepted Answer

Configure access control lists (ACLs) on the 'processed' container to grant read and execute permissions to the data-scientists group and set the default ACL to deny all. In Azure Data Lake Storage Gen2 with hierarchical namespace, access control lists (ACLs) provide fine-grained permissions at the directory and file level. By granting read and execute permissions to the 'data-scientists' group on the 'processed' container and setting the default ACL to deny all, you ensure only that group can read files. Option A is incorrect because private endpoints control network access, not identity-based permissions. Option B is incorrect because RBAC roles (like Storage Blob Data Reader) grant permissions at the storage account or container level, and Azure RBAC does not support deny assignments that would block specific users while allowing others at the same scope; a deny assignment would block everyone. Option C is incorrect because managed identities are used for authenticating Azure services, not for granting permissions to a security group.

Answer

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

Answer

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

Answer

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

Question 6

Which THREE metrics from Azure Monitor should be used to diagnose performance bottlenecks in an Azure Data Factory pipeline?

Accepted Answer

Pipeline Succeeded Rerun Count. Pipeline Succeeded Rerun Count (A) is correct because a high number of reruns indicates that the pipeline is repeatedly failing and retrying, which directly points to a performance bottleneck such as resource contention or throttling. This metric helps identify pipelines that are not completing successfully on the first attempt, signaling underlying issues that degrade throughput.

Answer

Blob Capacity

Answer

SQL Pool DWU Used

Question 7

A company uses Azure Databricks to process streaming data from Event Hubs. The data is written to a Delta table. The job occasionally fails due to checkpoint corruption. Which THREE measures should you implement to improve reliability?

Accepted Answer

Configure checkpointing to a durable storage like Azure Data Lake Storage.. Option A is correct because Structured Streaming checkpoints (the write-ahead log and offset/commit metadata) must live on a durable, fault-tolerant store such as Azure Data Lake Storage (abfss://) rather than ephemeral DBFS or local disk, so a cluster restart or node failure cannot corrupt or lose checkpoint state. Option D is correct because setting failOnDataLoss to false lets the stream continue when Event Hubs data is no longer available (for example, after retention expiry or offset gaps), preventing a corrupted or missing-offset checkpoint from permanently failing the job. Option E is correct because transient failures (throttling, network blips, Event Hubs 5xx responses) are the leading cause of job restarts that can leave checkpoints half-written; a retry policy with exponential backoff lets the query recover without manual intervention. Option B is not appropriate because increasing the batch interval only changes trigger cadence and does not address checkpoint durability or corruption recovery. Option C is not appropriate because scaling the cluster adds compute capacity for throughput spikes but does nothing to protect or repair checkpoint state.

Answer

Increase the batch interval to reduce load.

Answer

Increase the cluster size to handle spikes.

Question 8

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using the COPY statement. For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Answer

Using INSERT INTO VALUES

Answer

Using BCP utility

Answer

Using SQL Server Integration Services (SSIS)

Question 9

You have a pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection closed' error after 1 hour. The data volume is 500 GB over a slow network. What is the most likely cause and solution?

Accepted Answer

The source SQL Server query command timeout is set to 1 hour; increase the command timeout or use staging copy.. The 'Connection closed' error after exactly 1 hour indicates the SQL Server source query command timeout (default 60 minutes) is being hit. When copying 500 GB over a slow network, the query execution exceeds this timeout, causing the connection to drop. Increasing the command timeout or using staging copy (which breaks the query into chunks) resolves this by allowing the query to run longer or reducing the per-query duration.

Answer

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

Answer

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

Answer

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

Question 10

You are designing a data ingestion pipeline for Azure Synapse Analytics using PolyBase. The source data is in Azure Blob Storage, with files in the Parquet format. The data volume is approximately 2 TB daily. You need to minimize the time to load data into a dedicated SQL pool table. Which approach should you recommend?

Accepted Answer

Create an external table pointing to the Parquet files and use CREATE TABLE AS SELECT (CTAS) to load the data.. Using CREATE TABLE AS SELECT (CTAS) with an external table pointing to Parquet files leverages PolyBase's massively parallel processing capabilities. While the COPY INTO command does support Parquet format in dedicated SQL pools, CTAS offers superior performance for large-scale daily ingestion (2 TB) as it allows for distribution and index optimizations during the load, and it reads the compressed columnar Parquet data directly, minimizing I/O. CTAS also integrates seamlessly with PolyBase's external table architecture, making it the optimal choice for minimizing load time.

Answer

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

Answer

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

Answer

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

Question 11

Refer to the exhibit. The pipeline fails because the source and sink datasets do not match. The source file is a CSV with columns: CustomerID, Name, City. The sink table dbo.Customer has columns: CustomerID, Name, City, CreatedDate (default GETDATE()). The pipeline uses auto-mapping. Why does the pipeline fail?

Accepted Answer

The pre-copy script is not allowed for SQL sink.. The pipeline fails because a pre-copy script is specified for the SQL sink, but pre-copy scripts are not allowed for SQL sinks in Azure Data Factory. This causes an error during the copy activity, even though auto-mapping could handle the extra column with a default value. The other options are incorrect: auto-mapping can handle extra sink columns with default values (so A is wrong), recursive setting does not affect this scenario, and writeBatchSize is not relevant to the failure.

Answer

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

Answer

The recursive setting should be false for CSV files.

Answer

The writeBatchSize is too large for the sink.

Question 12

A multinational corporation uses Azure Data Lake Storage Gen2 to store petabytes of parquet files partitioned by date and hour. Data scientists report that queries on the last 7 days of data take over 30 minutes, while queries on older data are fast. The storage account uses the default Azure Blob Storage hierarchical namespace. Which action will MOST improve query performance on recent data?

Accepted Answer

Optimize the partition layout by partitioning by date first, then by hour, to reduce the number of partitions scanned for recent data. Partitioning by date first, then by hour, ensures that queries filtering on the last 7 days scan only the relevant date partitions, drastically reducing the amount of data read. In Azure Data Lake Storage Gen2, the hierarchical namespace allows partition pruning at the directory level, so a date-first layout minimizes the number of partitions scanned for recent data, directly addressing the performance bottleneck.

Answer

Convert the parquet files to CSV format to reduce metadata overhead

Answer

Enable soft delete on the storage account to reduce read latency

Answer

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

Question 13

Your Azure Synapse Analytics workspace uses serverless SQL pools for ad-hoc querying. Users report that queries are slow. You examine the execution plan and see that the query scans multiple partitions in the openrowset. What is the best way to improve performance?

Accepted Answer

Add a WHERE clause on the partition column. In serverless SQL pools, partition elimination only occurs when the query includes a filter predicate on the partition column. Adding a WHERE clause on the partition column lets the engine skip irrelevant partitions in the OPENROWSET scan, dramatically reducing I/O and query time. Without that predicate, the engine must scan every partition even if the data is partitioned.

Answer

Increase the MAXDOP setting

Answer

Create materialized views on the external tables

Answer

Partition the underlying data by a frequently filtered column

Question 14

A company uses Azure Synapse Analytics with a dedicated SQL pool. Data engineers need to implement column-level security so that only users with the 'Manager' role can see salary columns. Which TWO actions should they take?

Accepted Answer

Create a stored procedure that checks the user's role and returns the appropriate columns.. To implement column-level security in Azure Synapse Analytics dedicated SQL pool, a valid approach is to create a stored procedure that checks the user's role and returns only permitted columns, as dedicated SQL pool does not support column-level GRANT permissions or native column-level security. GRANT SELECT on a specific column (Option E) is not supported in dedicated SQL pool and will result in an error. Options C and D describe components of row-level security, not column-level security.

Answer

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

Answer

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

Answer

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

Answer

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

Question 15

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised. This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Answer

The Parquet files are not using the .parquet extension.

Answer

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

Answer

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

Question 16

Which THREE of the following are required to implement column-level security in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

A GRANT statement on specific columns to users or roles. Column-level security in Azure Synapse Analytics dedicated SQL pool is implemented using GRANT statements on specific columns. By granting SELECT on only certain columns to a user or role, you restrict access to sensitive data at the column level. While a VIEW can help simplify permission management, it is not mandatory. A database user does not require a default schema specifically for column-level security; it is a general database requirement. Therefore, only Option A is strictly required.

Answer

A VIEW that selects only the allowed columns

Answer

A DENY statement on specific columns to users or roles

Answer

A row-level security policy must be in place

Answer

The database user must have a default schema

Question 17

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. You have Parquet files in Azure Data Lake Storage Gen2 partitioned by date and hour. You need to query the data for the last 7 days efficiently, minimizing the amount of data scanned. Which approach should you use?

Accepted Answer

Use OPENROWSET with a file path that limits to the last 7 days' partitions and use a WHERE clause on the date column.. Using OPENROWSET with a file path that restricts to the last 7 days' partitions (e.g., '/date=2025-03-*/hour=*/*.parquet') minimizes data scanned by only reading the relevant Parquet files. Applying a WHERE clause on the date column further filters rows within those files, leveraging partition pruning at the file system level. This approach avoids scanning all partitions, which is critical for cost and performance in serverless SQL pool.

Answer

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

Answer

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

Answer

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

Question 18

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution will query data stored in Parquet files in Azure Data Lake Storage Gen2. You need to ensure that the queries are optimized for performance. Which action should you take?

Accepted Answer

Partition the Parquet files by date and use partition pruning in the query.. In Azure Synapse serverless SQL pool, performance is dominated by how much data must be read from storage, since there is no persistent compute or indexing. Partitioning Parquet files by date and filtering on the partition column enables partition pruning, so the engine reads only the relevant folders instead of scanning the entire dataset. This dramatically reduces I/O and query time, making D the correct optimization.

Answer

Increase the MAXDOP setting in the query.

Answer

Convert the Parquet files to CSV format for faster parsing.

Answer

Create materialized views on the external tables.

Question 19

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Partition the table on OrderDate. Partitioning the table on OrderDate enables partition elimination, which significantly reduces the amount of data scanned for date-range queries, improving performance without altering the distribution. Option A is incorrect because changing to round-robin distribution would increase data movement during joins and aggregations. Option B is incorrect because materialized views are pre-aggregated and not optimized for row-level date filtering; they involve storage and maintenance overhead. Option D is incorrect because non-clustered indexes can be used in dedicated SQL pool but are not as effective as partition elimination for large fact tables scanned sequentially, and they require additional storage and maintenance.

Answer

Change the distribution to round-robin on OrderDate

Answer

Create a materialized view on OrderDate

Answer

Create a non-clustered index on OrderDate

Question 20

You are reviewing an Azure Policy definition. What does this policy do?

Accepted Answer

Denies the creation or update of a storage account if its default blob service version is not set to '2020-10-02'. The policy denies any storage account creation or update if the default blob service version is not set to '2020-10-02'. Option B is wrong because the policy denies non-compliant resources, it does not set the version. Option C is wrong because the policy uses the 'deny' effect, not 'audit'. Option D is wrong because it denies if the version is not set to the specified value, not if it is set to that value.

Answer

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

Answer

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

Answer

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