DP-203 Practice Test 26 — 15 Questions

Question 1

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by date. As data grows, query performance on recent data degrades. You need to improve performance for queries filtering on the current month without affecting queries on older data. What should you do?

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

Accepted Answer

Implement partition switching to move older partitions to a different filegroup with slower storage. Partition switching allows you to efficiently move older partitions to a different filegroup (e.g., slower or compressed storage) without affecting queries on recent data. This reduces the active data volume in the fact table, improving scan performance for queries filtering on the current month. The operation is metadata-only and does not require data movement, so it does not impact ongoing queries on the remaining partitions.

Answer

Increase the service level objective (SLO) of the dedicated SQL pool

Answer

Change the table distribution to round-robin

Answer

Update statistics on the table for the current month partition

Question 2

You are implementing a data processing solution using Azure Data Factory. You have a pipeline that copies data from Azure Blob Storage to Azure Data Lake Storage Gen2. You need to ensure that the copy activity uses managed identity for authentication and that the data is transferred securely. Which TWO configurations should you apply?

Accepted Answer

Configure the source and sink linked services to use managed identity authentication.. Options C and D are correct. Option C is correct because managed identity authentication is configured in the linked service, not in the copy activity itself. Option D is correct because setting the integration runtime to 'AutoResolveIntegrationRuntime' uses Azure's network, ensuring data transfer remains within Azure and does not go over the public internet if using managed virtual network. Option A is wrong because 'enableDataIntegrityValidation' is for integrity checks, not authentication. Option B is wrong because the copy activity does not have a 'UseManagedIdentity' property; managed identity is configured in the linked service. Option E is wrong because staging is not required for secure transfer; it's used for performance or polybase.

Answer

Set the copy activity's 'enableDataIntegrityValidation' to true.

Answer

Set the copy activity's 'UseManagedIdentity' property to true.

Answer

Enable staged copy with a temporary blob store to improve security.

Question 3

You need to design a data storage solution for a batch processing pipeline that processes petabytes of data daily. The data is stored in Parquet format and must be accessible by both Azure Databricks and Azure Synapse Analytics. Which storage solution should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, providing native POSIX-like access control and high throughput for petabyte-scale batch processing. Both Azure Databricks and Azure Synapse Analytics have optimized connectors for ADLS Gen2 that leverage the hierarchical namespace for efficient partition pruning and file listing, which is critical for Parquet-based analytics at this scale.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 4

Refer to the exhibit. You created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files. Queries return no rows even though the files exist. What is the most likely issue?

Accepted Answer

The LOCATION path in the external table is relative to the data source, but the data source points to the wrong container or folder. The most likely issue is that the LOCATION path in the external table is relative to the data source. In the exhibit, the external data source points to the root container 'sales', and the external table LOCATION is 'parquet/sales/'. The combined path becomes 'sales/parquet/sales/', which is likely incorrect. The correct data source should point to the container containing the 'parquet' folder, or the LOCATION should be adjusted. Option A is incorrect because CREDENTIAL is already defined. Option B is incorrect because FILE_FORMAT is specified and valid for Parquet. Option C is incorrect because DATA_COMPRESSION is supported for Parquet.

Answer

The CREDENTIAL is missing

Answer

The FILE_FORMAT is incorrectly specified

Answer

The DATA_COMPRESSION is not supported for Parquet

Question 5

You are responsible for securing an Azure Synapse Analytics workspace. The workspace contains dedicated SQL pools and serverless SQL pools. You need to ensure that only users with specific Microsoft Entra ID roles can query serverless SQL pools, while dedicated SQL pools use SQL authentication. What should you do?

Accepted Answer

Configure serverless SQL pools to use Microsoft Entra ID authentication only, and dedicated SQL pools to allow SQL authentication.. Serverless SQL pools in Azure Synapse Analytics natively support Microsoft Entra ID authentication, allowing you to restrict access to specific Entra ID roles by disabling SQL authentication for those pools. Dedicated SQL pools can independently be configured to allow SQL authentication, enabling coexistence of both authentication methods as required. This separation ensures that only users with the designated Entra ID roles can query serverless SQL pools, while dedicated SQL pools remain accessible via SQL logins.

Answer

Create SQL logins for serverless SQL pools and assign permissions to Microsoft Entra ID groups.

Answer

Use a managed identity for serverless SQL pools and assign it to Microsoft Entra ID roles.

Answer

Disable SQL authentication for all pools and enforce Microsoft Entra ID authentication only.

Question 6

You have an Azure Databricks workspace that processes sensitive data. The security team requires that all access to the workspace be authenticated using Microsoft Entra ID and that all API calls be audited. Which configuration should you implement?

Accepted Answer

Configure workspace to use Microsoft Entra ID authentication and enable diagnostic settings for audit logs.. It directly addresses both requirements: Microsoft Entra ID authentication ensures all access is authenticated via Entra ID, and enabling diagnostic settings captures audit logs for all API calls. Option B (VNet injection) provides network isolation, not authentication or auditing. Option C (Private Link) ensures private connectivity, not authentication or auditing. Option D (personal access tokens) bypasses Entra ID, and cluster logs do not capture API audit trails.

Answer

Enable VNet injection and configure network security groups.

Answer

Deploy Azure Private Link and disable public access.

Answer

Configure personal access tokens for API access and enable cluster logs.

Question 7

Which THREE metrics should you monitor to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

DWU (Data Warehouse Unit) usage percentage. Options B, C, and E are correct. Option B: DWU usage percentage indicates whether the pool is under- or over-provisioned, helping optimize performance. Option C: Active queries count monitors concurrency and workload. Option E: Memory grant waiters count shows queries waiting for memory, which can indicate memory pressure and affect query performance. Option A: Storage used percentage is about capacity, not performance. Option D: Buffer cache hit ratio is more relevant for SQL Server, not directly for Synapse dedicated SQL pool performance optimization.

Answer

Storage used percentage

Answer

Buffer cache hit ratio

Question 8

Which TWO factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for migrating a legacy application? (Choose two.)

Accepted Answer

Need for SQL Server Agent jobs. SQL Managed Instance includes full support for SQL Server Agent, which is essential for legacy applications that rely on scheduled jobs, alerts, or automation. Azure SQL Database does not support SQL Server Agent, making Managed Instance the appropriate choice when this feature is required.

Answer

Support for active geo-replication

Answer

Authentication using Microsoft Entra ID

Answer

Integration with Azure VNet

Question 9

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 10

Your company uses Azure Purview for data governance. You need to ensure that sensitive data in Azure Data Lake Storage Gen2 is automatically detected and classified. What should you configure in Purview?

Accepted Answer

Create a scan rule set that includes built-in classification rules for sensitive data types.. In Microsoft Purview, you can create scan rule sets that include built-in classification rules to automatically detect sensitive data types during scanning. Option A is incorrect because sensitivity labels are applied after classification, not for detection. Option B is incorrect because Microsoft Defender for Cloud's data sensitivity discovery is a different feature; Purview itself handles classification. Option C is incorrect because Azure Policy enforces compliance rules, not data classification at the file level.

Answer

Apply sensitivity labels to the storage account using Microsoft Purview Information Protection.

Answer

Enable Microsoft Defender for Cloud's data sensitivity discovery.

Answer

Use Azure Policy to enforce tagging of resources containing sensitive data.

Question 11

Which THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Accepted Answer

A Lookup activity to retrieve the last processed watermark.. A Lookup activity is required to retrieve the last processed watermark (e.g., a timestamp or LSN) from a control table or source query. This watermark defines the incremental boundary for CDC, ensuring only new or changed rows are loaded in each pipeline run. Without it, the pipeline would have no way to track progress and would either reload all data or miss changes.

Answer

A Mapping Data Flow to transform the data before load.

Answer

Azure Functions to process change data.

Question 12

You are building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

Accepted Answer

Enable 'Incremental extraction' and specify the timestamp column for filtering.. Mapping data flows in Azure Synapse Analytics support incremental extraction by enabling the 'Incremental extraction' option in the source transformation and specifying a timestamp column (e.g., LastModified) to filter only new or updated rows. This allows the pipeline to load only changed data since the last run, which is essential for efficient incremental loads into a dedicated SQL pool.

Answer

Use a 'Column pattern' to detect new columns and filter rows.

Answer

Configure 'Row sampling' settings to take only new rows.

Answer

Set 'Source' to 'New rows only' in the sink transformation.

Question 13

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

Accepted Answer

Partition the data flow by a key column. Partitioning the data flow by a key column (Option D) enables parallel processing across multiple compute nodes, reducing data shuffling and improving throughput. In Azure Synapse data flows, partitioning distributes the workload evenly, which directly addresses the slow performance and high resource consumption observed in the pipeline.

Answer

Use a staging table to load data before transformation

Answer

Increase the batch size in the data flow

Answer

Use PolyBase to load data into the dedicated SQL pool

Question 14

You need to store data that is rarely accessed but must be retained for 10 years for compliance. The data will be accessed occasionally for audits. Which Azure storage tier is the most cost-effective?

Accepted Answer

Cool storage tier. The Cool storage tier is designed for data that is infrequently accessed and stored for at least 30 days, making it a cost-effective choice for compliance data accessed occasionally over a 10-year period. It offers lower storage costs than Hot tier while still providing low-latency access for audits, unlike Archive which requires hours to rehydrate.

Answer

Hot storage tier

Answer

Archive storage tier

Answer

Premium storage tier

Question 15

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, performance is improved by file pruning, which reduces the amount of data scanned. Adding a WHERE clause on the partition column allows the query engine to skip irrelevant partitions, thus reducing scan size. Option A is incorrect because MAXDOP controls parallelism, not data pruning. Option B is incorrect because materialized views are not supported in serverless SQL pools. Option C is incorrect because partitioning the underlying data helps, but the question asks for the best way to improve performance given the current query behavior; adding a WHERE clause on the partition column is the most direct and effective solution.

Answer

Increase the MAXDOP setting

Answer

Create materialized views on the external tables

Answer

Partition the underlying data by a frequently filtered column