DP-203 Practice Test 45 — 15 Questions

Question 1

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

Accepted Answer

Parquet with Snappy compression. Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Answer

Avro with Deflate compression

Answer

CSV with Gzip compression

Answer

ORC with Zlib compression

Question 2

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is ingested from multiple sources and needs to be deduplicated based on a composite key (source_id, record_id). New data may have duplicates within the same batch. Which write mode and table property should you use to handle this efficiently?

Accepted Answer

Use 'append' mode and perform a MERGE operation after write to deduplicate.. Option A is correct because using 'append' mode writes all incoming data as new files, and then performing a MERGE operation (upsert) based on the composite key (source_id, record_id) allows you to efficiently deduplicate both within the batch and against existing data. This approach leverages Delta Lake's ACID transactions and avoids the cost of rewriting entire partitions, making it suitable for handling duplicates from multiple sources.

Answer

Use 'overwrite' mode and enable 'delta.autoOptimize.optimizeWrite' = true.

Answer

Use 'ignore' mode and set 'delta.autoCompact' = true.

Answer

Use 'error' mode and enable 'delta.merge.onSchemaMismatch' = true.

Question 3

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

Accepted Answer

Delta Lake format. Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Answer

Auto Loader

Answer

Photon engine

Answer

Unity Catalog

Question 4

Refer to the exhibit. You are running the KQL query in Azure Data Explorer. The query returns no results, but you know there is data in the table T. What is the most likely issue?

Accepted Answer

The between operator syntax is incorrect; should be 'between (startTime..endTime)' without spaces around the dots. Option B is correct because the KQL `between` operator requires the range syntax `between(datetime1..datetime2)` with no spaces around the two dots. The query uses `between (startTime .. endTime)` with spaces, which is invalid syntax and causes the query to return no results even though data exists in table T.

Answer

The bin function is used incorrectly; should be bin(Timestamp, 1h) but placed in the wrong clause

Answer

The datetime format is incorrect; use ISO 8601

Answer

The table T does not exist in the database

Question 5

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

Accepted Answer

The table is not registered in the Spark metastore; it is only in the lakehouse.. In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Answer

The user does not have read permission on the table.

Answer

The Parquet file format is incompatible with the Delta source.

Answer

The Delta table is corrupted.

Question 6

You are developing a data processing pipeline in Azure Data Factory. The pipeline must copy data from an on-premises SQL Server to Azure Blob Storage. The data volumes are large, and the network connection is unreliable. Which configuration should you use to ensure resilience and resume capability?

Accepted Answer

Configure the copy activity with fault tolerance and enable 'Skip incompatible rows'. Use a self-hosted integration runtime for the source.. Option B is correct because using a self-hosted integration runtime enables connectivity to on-premises SQL Server, and enabling fault tolerance with 'Skip incompatible rows' allows the copy activity to continue despite transient failures or data inconsistencies. This configuration ensures resilience by automatically retrying failed operations and skipping rows that cause errors, which is critical for large data volumes over an unreliable network.

Answer

Use a staging copy with Azure Blob Storage as an intermediate store.

Answer

Increase the 'parallelCopies' setting to 10 and use a staging table.

Answer

Use PolyBase to load data directly into Azure Synapse Analytics.

Question 7

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

Accepted Answer

Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*). Option D is correct because partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Answer

Increase the maximum query concurrency limit

Answer

Provision a dedicated SQL pool with more DTUs

Answer

Create a clustered columnstore index on the date column

Question 8

You are optimizing a pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure Synapse Analytics. The pipeline uses a copy activity with PolyBase. The data is partitioned by date in Blob Storage. You notice that the load is slow. What is the most likely cause?

Accepted Answer

The source files are too many and too small (e.g., thousands of 1 MB files). PolyBase in Azure Synapse Analytics performs best when reading large, contiguous files. When the source contains thousands of small files (e.g., 1 MB each), PolyBase must initiate a separate read operation for each file, causing excessive overhead from file open/close operations and metadata requests. This dramatically reduces throughput compared to reading fewer, larger files.

Answer

The source files are stored in Azure Blob Storage instead of Data Lake Storage Gen2

Answer

The source files are in CSV format instead of Parquet

Answer

The sink table has a clustered columnstore index

Question 9

You need to execute a T-SQL stored procedure in Azure Synapse dedicated SQL pool that performs a large data load. The stored procedure takes approximately 45 minutes to run. You want to monitor the progress and see the statement text currently being executed. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests (with status='running'). B is correct because `sys.dm_pdw_exec_requests` with the filter `WHERE status = 'running'` returns the currently executing requests in an Azure Synapse dedicated SQL pool, including the full statement text in the `command` column. This allows you to see the exact T-SQL being executed during the 45-minute data load, enabling progress monitoring.

Answer

sys.dm_workload_management_workload_groups_stats

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_exec_requests

Question 10

Which TWO are valid ways to process data in Azure Synapse Analytics?

Accepted Answer

Use Synapse SQL pool to run T-SQL queries.. Option C is correct because Synapse SQL pool (formerly SQL DW) is a dedicated or serverless SQL engine within Azure Synapse Analytics that allows you to run T-SQL queries for data transformation, loading, and querying. It is a first-class compute resource designed for large-scale data warehousing workloads, making T-SQL queries a valid and primary method for processing data in Synapse.

Answer

Use Logic Apps to run data transformations.

Answer

Use Azure Functions to process data in a serverless manner.

Answer

Use Power BI to transform data.

Question 11

You are designing a streaming job in Azure Stream Analytics. The job needs to count the number of events per device type every 10 seconds. The input is from Event Hubs. Which query should you use?

Accepted Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10). Option B is correct because a TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)

Question 12

You are processing CSV files in Azure Synapse Analytics serverless SQL pool. The files contain newline characters within quoted fields, causing parsing errors. Which file format option should you specify to handle this correctly?

Accepted Answer

Use PARSER_VERSION = '2.0'. Option D is correct because PARSER_VERSION = '2.0' in Azure Synapse serverless SQL pool supports quoted fields that contain embedded newline characters (CR/LF). The parser version 2.0 is designed to handle CSV files with complex quoting rules, including newlines within quoted fields, which the older parser version 1.0 cannot parse correctly.

Answer

Use ROWTERMINATOR = '0x0a'

Answer

Use FORMAT = 'CSV'

Answer

Use FIELDQUOTE = '"'

Question 13

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Accepted Answer

Pivot. Option A is correct because the Pivot transformation in Mapping Data Flows allows you to rotate data from rows into columns, enabling aggregation and restructuring of datasets. It is a native transformation in Azure Data Factory's Mapping Data Flows, supporting group-by columns, pivot keys, and aggregated column values.

Answer

Union All

Answer

Merge Join

Question 14

You are monitoring an Azure Data Factory pipeline that runs every hour. The pipeline uses a Copy activity to copy data from Azure SQL Database to Azure Blob Storage. Recently, the pipeline has been failing with a 'Timeout' error. The source SQL database has a large number of records. What should you do to resolve the timeout?

Accepted Answer

Enable staging and use PolyBase or COPY statement for the copy activity.. Option A is correct because enabling staging with PolyBase or the COPY statement offloads the data transfer to Azure Data Lake or Blob Storage, bypassing the bottleneck of the Copy activity's default data movement. This approach is specifically designed for large-scale data loads from Azure SQL Database, as it uses the database's bulk export capabilities and avoids the timeout by not relying on the Copy activity's internal query execution.

Answer

Decrease the 'writeBatchSize' to 1000.

Answer

Increase the 'timeout' value in the copy activity settings.

Answer

Use a query with 'queryTimeout' set to 7200 seconds.

Question 15

You are working with Azure Synapse Analytics serverless SQL pool. You need to query a set of Parquet files located in ADLS Gen2. The files have nested columns (structs and arrays). Which function should you use to flatten the nested data?

Accepted Answer

OPENJSON. OPENJSON is the correct function because it parses JSON text and returns objects and properties from JSON input as rows and columns. In Azure Synapse serverless SQL pool, when Parquet files contain nested columns (structs and arrays), they are exposed as JSON strings, and OPENJSON can flatten these nested structures into a relational format. This allows you to query complex nested data directly without needing to pre-process the files.

Answer

PIVOT

Answer

UNNEST

Answer

CROSS APPLY