DP-203 Hard Questions — 25 Difficult Questions

Question 1

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

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

Accepted Answer

Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.. ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON is an online operation that compresses rowgroups with deleted rows without blocking writes. This directly addresses the poor columnstore segment quality. Option B is wrong because dropping and recreating the clustered columnstore index is an offline operation that blocks writes. Option C is wrong because changing the distribution key would require recreating the table, which is offline and does not specifically fix columnstore segment quality. Option D is wrong because ALTER INDEX REBUILD is also an offline operation that blocks writes.

Answer

Drop and recreate the clustered columnstore index.

Answer

Re-cluster the table using a different distribution key.

Answer

Run ALTER INDEX REBUILD on the clustered columnstore index.

Question 2

You are designing a delta lake architecture in Azure Synapse Analytics. Which TWO practices should you follow to ensure ACID transactions and data consistency?

Accepted Answer

Use the Delta Lake transaction log for all write operations. The Delta Lake transaction log is the core mechanism that enables ACID transactions. Every write operation (insert, update, delete, merge) is recorded as an atomic commit in the transaction log, ensuring that concurrent readers see a consistent snapshot and that partial writes are never visible. Without this log, Delta Lake cannot guarantee atomicity or isolation.

Answer

Disable schema evolution to prevent accidental schema changes

Answer

Run OPTIMIZE commands frequently to compact small files

Answer

Store data in CSV format to simplify schema enforcement

Question 3

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 4

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 5

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

Accepted Answer

The dimension table is not actually replicated due to its size exceeding the replication threshold. The 'ShuffleMove' operation in a Synapse dedicated SQL pool query plan indicates that data is being moved between distributions to complete the join. If the small dimension table is supposed to be replicated but exceeds the replication threshold (default 60 GB compressed), it will not be replicated and instead remains hash-distributed. This forces a shuffle of the large fact table's data to align with the dimension table's distribution, causing significant data movement and slow performance.

Answer

The query is running with a low resource class

Answer

Result set caching is enabled

Answer

Statistics are outdated on the dimension table

Question 6

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Azure Stream Analytics can output directly to Azure Synapse Analytics dedicated SQL pool using the built-in Azure Synapse Analytics output adapter, which supports high-throughput, low-latency writes. This enables streaming data to be available for queries within seconds, meeting the 5-second latency requirement without intermediate storage or batch processing steps.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds

Question 7

Refer to the exhibit. The pipeline fails with a 'Type mismatch' error. The source file has a column 'Name' of type String, and the destination table expects 'FullName' of type String. What is the most likely cause of the failure?

Accepted Answer

The source file does not contain a column named 'Name'.. The pipeline fails with a 'Type mismatch' error because the source file does not contain a column named 'Name'. When the source dataset is configured to read a 'Name' column but the actual file lacks that column, Azure Data Factory or Synapse Pipelines cannot map it to the sink column 'FullName', resulting in a type mismatch error. The error message is misleading because the mismatch is not about data types but about missing source columns, which the service treats as a type incompatibility.

Answer

The recursive setting on the source store is causing duplicate reads.

Answer

The source file has leading/trailing whitespace in the column names.

Answer

The sink column 'FullName' is expecting a different data type than String.

Question 8

A company uses Azure Data Factory to copy sensitive data from on-premises SQL Server to Azure Blob Storage. They must ensure that data is encrypted in transit and at rest. Which combination of features should they use?

Accepted Answer

Configure the copy activity to use TLS and enable Azure Storage Service Encryption.. Azure Data Factory's copy activity uses TLS (Transport Layer Security) to encrypt data in transit between the on-premises SQL Server and Azure Blob Storage, and Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption. This combination satisfies both encryption requirements without additional complexity.

Answer

Use Always Encrypted in SQL Server and customer-managed keys in Blob Storage.

Answer

Set up a VPN between on-premises and Azure, and use Azure Disk Encryption.

Answer

Use HTTPS for the copy activity and enable Azure Storage Service Encryption.

Question 9

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data in Azure Databricks with encryption at rest and in transit, and must also support row-level security (RLS). Which combination of features should you use?

Accepted Answer

Use Azure Databricks with Delta Lake and enable row-level security on the Delta table. Delta Lake in Azure Databricks now supports row-level security (RLS) natively through Delta Sharing and Spark SQL predicates, allowing you to enforce data access policies directly on the Delta table without external systems. Combined with Azure Databricks' built-in encryption at rest (using Azure Storage Service Encryption or customer-managed keys) and in transit (TLS 1.2+), this meets all requirements without introducing additional services.

Answer

Use Azure SQL Database with row-level security and connect Databricks via JDBC

Answer

Use Azure Databricks with Azure Key Vault for encryption and implement RLS in application code

Answer

Use Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted

Question 10

You are designing a solution to store telemetry data from millions of devices. Each device sends a JSON payload every 5 seconds. The data must be partitioned by device ID and time for efficient querying and must support real-time streaming ingestion. Which Azure storage solution should you recommend?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it offers a multi-model, globally distributed database service with native support for real-time streaming ingestion via the Change Feed and automatic indexing. Its partition key design (device ID + time) enables efficient querying across millions of devices, and it guarantees single-digit millisecond read/write latencies essential for telemetry data arriving every 5 seconds.

Answer

Azure SQL Database

Answer

Azure Blob Storage with Azure Event Hubs

Answer

Azure Table Storage

Question 11

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

Accepted Answer

Combine the CSV files into fewer, larger files before loading.. Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Answer

Increase the service level (DWU) of the dedicated SQL pool.

Answer

Change the file format from CSV to Avro.

Answer

Use Azure Data Factory to stage the data in Azure Blob Storage before loading.

Question 12

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

Accepted Answer

Enable staging and use PolyBase as the copy method for the sink.. Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Answer

Change the copy behavior to 'sequential' to reduce load on the source.

Answer

Increase the degree of copy parallelism (DOP) to the maximum value supported.

Answer

Split the source data into multiple smaller files and use multiple copy activities running in parallel.

Question 13

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query data in Azure Data Lake Storage Gen2. The data is stored in Delta Lake format. Which of the following statements is true regarding querying Delta Lake tables with serverless SQL pool?

Accepted Answer

You can use the OPENROWSET function with the BULK option and FORMAT='DELTA' to query Delta Lake tables.. Serverless SQL pool in Azure Synapse Analytics supports querying Delta Lake tables directly using the OPENROWSET function with the BULK option and FORMAT='DELTA'. This allows you to read Delta Lake data stored in Azure Data Lake Storage Gen2 without needing to convert it to Parquet or create external tables first. The Delta format is natively supported, enabling time travel and schema evolution features.

Answer

Serverless SQL pool supports Delta Lake only if the files are in Parquet format.

Answer

Serverless SQL pool cannot query Delta Lake tables; you must convert them to Parquet first.

Answer

You must create an external table in serverless SQL pool using the CREATE EXTERNAL TABLE statement with the Delta format.

Question 14

Refer to the exhibit. A Bicep file is used to deploy an Azure Synapse Analytics workspace. What is the purpose of the 'purviewConfiguration' property?

Accepted Answer

It links the workspace to a Microsoft Purview account for data lineage and cataloging. The 'purviewConfiguration' property in a Bicep file for Azure Synapse Analytics links the workspace to a Microsoft Purview account. This integration enables automated data lineage tracking, cataloging, and discovery across the Synapse environment, allowing users to search for and govern data assets directly from Purview. Without this property, the Synapse workspace operates independently of Purview's unified data governance capabilities.

Answer

It configures automated backups of the Synapse workspace

Answer

It enables monitoring of data movement by Azure Monitor

Answer

It connects the workspace to a data catalog for pipeline sources

Question 15

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Partition switching can be used to quickly load data into a table.. Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to quickly load data into a table by switching a partition from a staging table into the target table. This operation is metadata-only and does not require data movement, making it highly efficient for incremental data loads.

Answer

Partitions are automatically aligned with distributions.

Answer

Partitioning is only supported on tables with clustered rowstore indexes.

Question 16

You are using Azure Stream Analytics to process real-time temperature data from IoT devices. The output must be written to Azure SQL Database. The job has been running successfully for weeks, but recently you notice that the output data has duplicate rows. The input events are unique. The job uses a windowed aggregation (TumblingWindow). What is the most likely cause of duplicates?

Accepted Answer

The job is being restarted and reprocessing data.. When an Azure Stream Analytics job is restarted, it may reprocess data from the last checkpoint or from the beginning of the input stream, depending on the configured start time and output policy. This reprocessing can cause duplicate rows in the output, especially when using windowed aggregations like TumblingWindow, because the same events are aggregated again and written to Azure SQL Database without deduplication logic.

Answer

The job is not handling late-arriving events.

Answer

The input event hub is receiving duplicate events.

Answer

The tumbling window size is too small.

Question 17

You need to assign permissions to a service principal so that it can write data to a specific container in Azure Data Lake Storage Gen2, but not delete blobs. The above JSON shows the built-in role 'Storage Blob Data Contributor'. The role includes delete permission in DataActions. What should you do?

Accepted Answer

Create a custom role that includes read and write DataActions but excludes the delete DataAction, then assign that custom role.. Azure RBAC roles are all-or-nothing at the permission level; you cannot selectively remove a single DataAction from a built-in role at assignment time. The only way to grant write access without delete is to create a custom role that explicitly includes the required read and write DataActions (e.g., Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write) and omits the delete DataAction (Microsoft.Storage/storageAccounts/blobServices/containers/blobs/delete). This custom role is then assigned to the service principal at the container scope, ensuring it can write data but never delete blobs.

Answer

Assign the Storage Blob Data Contributor role and create a deny assignment that denies delete.

Answer

Assign the Storage Blob Data Contributor role and use ACLs to deny delete on the container.

Answer

Assign the Storage Blob Data Contributor role and remove the delete permission at the role assignment scope.

Question 18

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Accepted Answer

Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.. Dynamic data masking obfuscates sensitive columns for unauthorized users at query time, providing column-level security without altering the underlying data. Option D is correct because column-level security in Azure Synapse dedicated SQL pools uses GRANT SELECT on specific columns to authorized users, restricting access to sensitive columns. Option B is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not at the column level. Option C is incorrect because security policies with filter predicates are used for row-level security, not column-level security. Option E is incorrect because Always Encrypted encrypts columns at the application level, which is separate from column-level security in Synapse.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a security policy with filter predicates to restrict access to specific columns.

Answer

Implement Always Encrypted to encrypt columns at the application level.

Question 19

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 20

Refer to the exhibit. A data engineer runs a pipeline copy activity and gets this error. What is the most likely cause of the failure?

Accepted Answer

The self-hosted integration runtime is not running or unreachable.. The error indicates the self-hosted integration runtime is not ready. This typically happens when the IR is not running or has network issues.

Answer

The destination storage account is inaccessible.

Answer

The pipeline parameters are missing.

Answer

The source database is offline.