DP-203 Develop data processing Practice Test 2 — 15 Questions

Question 1

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 as Parquet files partitioned by date. Which TWO of the following statements are true regarding querying this data? (Select TWO.)

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

Accepted Answer

You can use the filepath() function in the query to retrieve the partition column values.. Option A is correct because in a serverless SQL pool, the filepath() function (used with OPENROWSET) exposes the file path as a column, allowing you to extract partition values such as the date from the folder structure when querying Parquet files in ADLS Gen2. Option B is correct because serverless SQL pools perform partition elimination automatically when the WHERE clause filters on the partition column derived from the path, so only the relevant folders/files are read, reducing I/O and cost. Option C is incorrect because OPENROWSET is fully supported in serverless SQL pools and is in fact the primary way to query Parquet files without creating external tables. Option D is incorrect because serverless SQL pools do not support creating indexes; they are read-only over external data and rely on statistics and partition pruning instead. Option E is incorrect because OPENROWSET supports wildcards and multiple files via the BULK path, so you are not limited to a single file.

Answer

You must create an external table to query Parquet files; OPENROWSET is not supported.

Answer

You can create indexes on the serverless SQL pool to improve query performance.

Answer

You can only query a single file at a time; wildcards are not supported.

Question 2

You are developing a data processing pipeline in Azure Data Factory. The pipeline uses a mapping data flow to transform data. You need to ensure that the data flow can handle schema drift from the source. Which THREE of the following actions should you take? (Select THREE.)

Accepted Answer

Enable 'Allow schema drift' in the source settings.. Option B is correct because enabling 'Allow schema drift' in the source transformation settings lets the mapping data flow read and pass through columns that are not defined in the source schema, which is the foundational requirement for handling schema drift. Option C is correct because setting 'Ignore drift column types' in the sink settings allows the sink to accept drifted columns without failing on type mismatches, ensuring the pipeline can write new columns even when their types are not explicitly declared. Option D is correct because 'Column pattern' transformations (rule-based mappings) let you apply transformations to sets of columns matched by name patterns, so newly drifted columns are automatically processed without hardcoding each one. Option A is not correct because a 'Select' transformation that explicitly chooses only known columns would drop drifted columns, defeating the purpose of handling schema drift. Option E is not correct because adding a 'Derived Column' transformation for every possible column is impractical and impossible to maintain, since schema drift involves unknown columns that cannot be enumerated in advance.

Answer

Use a 'Select' transformation to explicitly choose only the known columns.

Answer

Add a 'Derived Column' transformation for every possible column that might be added.

Question 3

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 as Parquet files partitioned by date. Which TWO of the following statements are true regarding querying this data? (Select TWO.)

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

Accepted Answer

CREATE TABLE AS SELECT (CTAS) from external tables.. Option A (CTAS from external tables) is correct because a dedicated SQL pool can read data staged in Azure Data Lake Storage or Blob Storage via external tables and then materialize it into a new distributed table using CREATE TABLE AS SELECT, which is a fully supported, high-throughput ingestion pattern (often combined with PolyBase). Option B (PolyBase with T-SQL commands) is correct because PolyBase is the native T-SQL mechanism in dedicated SQL pools for querying and loading external data in Azure Storage (via CREATE EXTERNAL TABLE/EXTERNAL DATA SOURCE and CREATE TABLE AS SELECT or INSERT...SELECT), supporting both batch and near-real-time ingestion. Option C (COPY INTO) is not the right answer here because the COPY statement is the primary ingestion method for serverless SQL pools and for Azure Synapse's dedicated SQL pools only in specific preview/limited contexts; it is not the standard supported ingestion method for dedicated SQL pools in this scenario. Option D (Azure Logic Apps with SQL connector) is not appropriate because the SQL connector performs row-by-row transactional inserts/updates against Azure SQL/Synapse, which is not designed for bulk ingestion into a dedicated SQL pool. Option E (Azure Data Factory with a copy activity using native sink) is not correct because ADF copy into a dedicated SQL pool must use PolyBase or the COPY statement as the sink method, not a generic 'native sink', so it does not represent a distinct supported ingestion method for a dedicated SQL pool.

Answer

COPY INTO command.

Answer

Azure Logic Apps with SQL connector.

Answer

Azure Data Factory with a copy activity using native sink.

Question 4

Refer to the exhibit. You have an Azure Data Factory dataset definition for a Parquet file stored in Azure Data Lake Storage Gen2. You attempt to use this dataset as a source in a copy activity, but the copy activity fails with an error indicating that the file is not found. The file 'sales_orders.parquet' exists at the specified path. What is the most likely cause of the error?

Accepted Answer

The dataset is configured with a specific file name, but the data is partitioned across multiple folders; you should use a wildcard pattern in the folderPath.. The dataset definition specifies a single file name ('sales_orders.parquet') in the 'fileName' property, but the actual data is likely stored across multiple Parquet files in a partitioned folder structure (e.g., 'sales_orders/year=2024/month=01/'). When a copy activity uses this dataset as a source, Azure Data Factory looks for the exact file name at the specified path, not finding any file because the data is spread across subfolders. Using a wildcard pattern (e.g., '*.parquet') in the 'folderPath' or setting 'fileName' to '*' allows the copy activity to read all Parquet files in the folder, resolving the 'file not found' error.

Answer

The compression codec 'snappy' is not supported for Parquet files.

Answer

The dataset type should be 'AzureBlobStorageLocation' instead of 'AzureBlobFSLocation'.

Answer

The linked service 'ADLSGen2' is not properly configured with the storage account key.

Question 5

Refer to the exhibit. You have created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. When you query the external table, you get an error that the external table is not accessible. What should you check first?

Accepted Answer

Verify that the serverless SQL pool has been granted the 'Storage Blob Data Reader' role on the storage account.. The error 'external table is not accessible' in Azure Synapse serverless SQL pool typically indicates an authorization failure when the SQL pool attempts to read the underlying Parquet files in Azure Data Lake Storage Gen2. Serverless SQL pool uses its own service identity to access storage, and it must be granted the 'Storage Blob Data Reader' role on the storage account at the storage account scope to have read permissions. Without this role assignment, the SQL pool cannot authenticate to the storage, resulting in the access error.

Answer

Check that the external table's LOCATION path is relative to the container and does not start with a slash.

Answer

Ensure that the external file format is correctly referencing the Parquet format.

Answer

Confirm that the Snappy compression codec is supported by the serverless SQL pool.

Question 6

Your organization uses Azure Synapse Analytics to run large-scale ETL jobs. A pipeline that loads data from Azure Data Lake Storage Gen2 into a dedicated SQL pool is failing with 'Out of memory' errors during the staging step. The source data is 500 GB in size, and the SQL pool is currently set to DW500c. What should you do to resolve the issue without changing the pipeline logic?

Accepted Answer

Increase the DWU of the dedicated SQL pool to a higher tier (e.g., DW1000c).. The 'Out of memory' error during the staging step indicates that the dedicated SQL pool lacks sufficient memory resources to handle the 500 GB data load within the current DWU setting. Increasing the DWU (Data Warehouse Units) from DW500c to a higher tier, such as DW1000c, scales up the memory and compute resources available to the pool, resolving the memory pressure without altering the pipeline logic or source data.

Answer

Use PolyBase instead of the Copy activity.

Answer

Switch the source to Azure Data Lake Storage Gen1.

Answer

Partition the source data into smaller files.

Question 7

You are building a data processing solution in Azure Synapse Analytics. The solution requires creating a table that stores sales transactions. The table will be used for both point-of-sale lookups and large aggregation queries. The data is not updated frequently. Which table distribution should you recommend?

Accepted Answer

ROUND_ROBIN. ROUND_ROBIN is the correct choice because the table is used for both point-of-sale lookups (single-row queries) and large aggregation queries, and the data is not updated frequently. ROUND_ROBIN distributes data evenly across all distributions without a hash key, which provides the best overall performance for mixed workloads where no single distribution key optimizes both lookup and aggregation patterns. It avoids data skew and allows parallel processing for aggregations while still supporting efficient lookups when combined with appropriate indexes.

Answer

HASH on TransactionID

Answer

HASH on SalesDate

Answer

REPLICATE

Question 8

You are designing a batch processing solution in Azure Data Factory. The source is an Azure Blob Storage container with CSV files. The target is an Azure SQL Database. The pipeline must run daily and incrementally load only new or changed rows. Which Data Factory feature should you use?

Accepted Answer

Mapping Data Flow with a watermark column. Mapping Data Flows in Azure Data Factory can use a watermark column (e.g., LastModifiedDate or an incrementing ID) to perform incremental loads from Azure Blob Storage CSV files. This approach identifies only new or changed rows since the last pipeline run, avoiding full reloads. Option D (CDC resource) is designed for transactional databases with change tracking, not for flat files like CSV in Blob Storage, making it unsuitable for this scenario.

Answer

Stored Procedure activity to run a merge statement

Answer

Copy activity with a full load every time

Answer

Change data capture (CDC) resource

Question 9

Which THREE factors should you consider when choosing between Azure Data Factory Mapping Data Flows and Azure Synapse Spark pools for data transformation?

Accepted Answer

Ease of use: Mapping Data Flows provide a visual designer, while Spark requires code.. Option B is correct because Mapping Data Flows offer a code-free, visual drag-and-drop designer in Azure Data Factory or Synapse pipelines, whereas Spark pools require writing Scala, PySpark, Spark SQL, or .NET code, making ease of use a genuine decision factor. Option D is correct because Mapping Data Flows execute on Azure Integration Runtime (including self-hosted IR for on-premises connectivity) and can be orchestrated from Data Factory or Synapse pipelines, while Synapse Spark pools are tied to the Synapse workspace and cannot be invoked as an ADF integration runtime activity outside that context. Option E is correct because the Data Flow debug session has a configurable time-to-live with a maximum of 8 hours, whereas Spark pool debugging via notebooks or Synapse sessions does not impose that same 8-hour debug TTL, so debugging workflow is a real differentiator. Option A is wrong because both Mapping Data Flows and Synapse Spark notebooks/activities can be scheduled through triggers (ADF triggers or Synapse pipeline triggers), so scheduling is not exclusive to Data Flows. Option C is wrong because Mapping Data Flows are not hard-limited to 100 GB; they scale with the integration runtime and can process large datasets, and Spark's petabyte capability is not a clean 100 GB cutoff distinction.

Answer

Scheduling: Only Data Flows can be scheduled via triggers.

Answer

Data volume: Data Flows are limited to 100 GB, while Spark can handle petabytes.

Question 10

Which TWO configuration options are required to enable change data capture (CDC) in Azure Data Factory for incremental loading from Azure SQL Database?

Accepted Answer

Enable CDC on the source Azure SQL Database.. Option B is correct because Azure SQL Database CDC must be enabled at the source database level (e.g., via sys.sp_cdc_enable_db and per-table sys.sp_cdc_enable_table) before ADF can read the change tracking tables (cdc.<capture_instance>_CT) for incremental extraction. Option C is correct because Azure Data Factory requires a dedicated 'Change data capture' resource (the CDC resource/artifact) that references the source and target stores and defines the change-tracking configuration used by the CDC activity in a pipeline. Option A is not required because ADF CDC reads directly from the SQL change tables and does not need a Blob Storage folder for CDC logs. Option D is not required because a staging Azure SQL Database is not a prerequisite for CDC-based incremental loading. Option E is not required because an initial full load is optional and not a configuration requirement to enable CDC.

Answer

Create a dedicated folder in Azure Blob Storage for CDC logs.

Answer

Provision a staging Azure SQL Database.

Answer

Configure a full load of the entire table first.

Question 11

You are reviewing a Mapping Data Flow in Azure Data Factory that copies data from a CSV file to an Azure Synapse table. The data flow uses 'allowSchemaDrift: true' and 'validateSchema: false'. After running the pipeline, you notice that the target table does not have the expected columns. The CSV file sometimes has extra columns. What is the most likely reason?

Accepted Answer

The sink mapping is explicitly defined and does not include auto-mapping for drifted columns.. When 'allowSchemaDrift' is enabled on the source, drifted columns are detected but will only be written to the sink if auto-mapping is used. If the sink mapping is explicitly defined (e.g., column-by-column mappings), it overrides auto-mapping and drifted columns are ignored. Since the target table is missing expected columns, the explicit mapping likely excludes the drifted columns.

Answer

Schema drift is not enabled on the source.

Answer

The sink dataset has a fixed schema that does not allow drift.

Answer

The source dataset has a fixed schema that does not include extra columns.

Question 12

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

Accepted Answer

The TTL setting is too low, causing the cluster to be recreated frequently.. The first data flow execution takes a long time because the TTL (time-to-live) setting of 10 minutes causes the cluster to be deallocated shortly after the previous run. When a new data flow starts after the TTL expires, a new cluster must be provisioned from scratch, which adds significant startup latency. A higher TTL (e.g., 60 minutes) would keep the cluster warm for subsequent executions, reducing cold-start delays.

Answer

The core count of 8 is insufficient for the data flow.

Answer

The AutoResolve location cannot be used for Managed IR.

Answer

The integration runtime type should be 'Self-Hosted' for data flows.

Question 13

You are using Azure Synapse Pipelines to perform an incremental load from Azure SQL Database to Azure Synapse Analytics. You need to identify rows that have changed since the last load. Which approach should you use?

Accepted Answer

Enable Change Data Capture (CDC) on the source table.. Change Data Capture (CDC) on the source Azure SQL Database captures insert, update, and delete operations in change tables, enabling Azure Synapse Pipelines to efficiently identify only the changed rows since the last load. This approach minimizes data movement and processing overhead compared to full or snapshot-based comparisons, making it the recommended pattern for incremental loads in Synapse Pipelines.

Answer

Compare the current data with a snapshot using T-SQL MERGE.

Answer

Truncate and reload the entire table daily.

Answer

Use a watermark column such as LastModifiedDate.

Question 14

You have an Azure Stream Analytics job that reads from an Event Hub and writes to Azure SQL Database. The job processes high-velocity IoT sensor data. You notice that the output to SQL Database is slower than expected and the job's watermark delay is increasing. What should you do to improve throughput?

Accepted Answer

Partition the output by a column like DeviceId.. Partitioning the output by a column like DeviceId allows Azure Stream Analytics to write to multiple SQL Database tables or use partitioned tables, enabling parallel writes. This reduces contention and improves throughput because the job can distribute the load across multiple write operations, directly addressing the bottleneck caused by high-velocity IoT sensor data overwhelming a single output stream.

Answer

Disable late arrival and out-of-order event handling.

Answer

Increase the Streaming Units (SU) of the job.

Answer

Decrease the window size in the query.

Question 15

Your team is building a real-time dashboard in Power BI that displays sales data from Azure Stream Analytics. The data must be updated every 5 seconds with low latency. Which output type should you configure in Stream Analytics to achieve this?

Accepted Answer

Power BI. Power BI is the correct output type because Azure Stream Analytics has a native Power BI output connector that supports real-time streaming datasets, enabling sub-second latency for dashboards. This connector pushes data directly to Power BI's streaming API, which refreshes visuals automatically every 5 seconds as required.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Event Hubs