DP-203 Practice Test 49 — 15 Questions

Question 1

You are designing a data processing solution in Azure using Azure Data Lake Storage Gen2 as the storage layer. You need to ensure that data ingested from various sources is immutable and can be used for both batch and streaming workloads. Which storage design pattern should you implement?

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

Accepted Answer

Implement a medallion architecture with bronze, silver, and gold layers.. The medallion architecture (bronze, silver, gold) is the correct pattern because it enforces immutability at the bronze layer (raw ingested data is never modified), while providing progressively refined, query-optimized views for both batch and streaming workloads in Azure Data Lake Storage Gen2. This design supports schema-on-read, enables reprocessing from raw data, and aligns with lakehouse principles for unified analytics.

Answer

Store data in a normalized relational database structure.

Answer

Use a data vault model with hubs, links, and satellites.

Answer

Design a star schema with fact and dimension tables.

Question 2

Your company uses Azure Data Lake Storage Gen2 and Azure Databricks for data processing. Some Parquet files in the lake are written with a schema that includes a column 'address' of struct type. A downstream process expects 'address' to be a string. You need to transform the data in a way that minimizes read overhead and does not rewrite the entire dataset. Which approach should you use?

Accepted Answer

Apply a schema-on-read transformation that casts the column to string when reading.. Schema-on-read allows you to cast the 'address' column from struct to string at query time without modifying the underlying Parquet files. This approach minimizes read overhead by avoiding a full data rewrite and leverages Spark's ability to apply transformations during the read path, which is efficient for downstream processes that expect a string type.

Answer

Use schema evolution in Delta Lake to change the column type.

Answer

Create a view that selects the column as a string and save it as a new table.

Answer

Rewrite the Parquet files with the correct schema using a Spark job.

Question 3

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

Accepted Answer

Azure Synapse Analytics dynamic data masking. Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs. Azure Data Lake Storage does not provide masking.

Answer

Azure API Management policies

Answer

Azure Data Lake Storage access control lists (ACLs)

Answer

Azure Purview classification and labeling

Question 4

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

Accepted Answer

Create statistics on the columns used in joins and filters. (Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance. Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Answer

Increase the MAXDOP setting in the query

Answer

Move the data to a dedicated SQL pool

Answer

Convert the Parquet files to CSV format

Question 5

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slower than expected. The files are large (500 MB each) and not partitioned. You need to improve query performance without moving data. What should you do?

Accepted Answer

Create external tables with explicit file format and partition elimination hints.. Creating external tables with file format options allows the serverless SQL pool to use metadata for optimization. Changing the file format to CSV or using OPENROWSET without external tables does not improve performance as much. Converting to Delta format requires data movement. Partitioning the files would be best, but that requires reorganizing files.

Answer

Convert the Parquet files to Delta format using Azure Databricks.

Answer

Change the files to CSV format with a header row.

Answer

Use OPENROWSET with BULK and specify ROWSET_OPTIONS for better performance.

Question 6

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Answer

Azure Synapse Spark with Structured Streaming

Answer

Azure Data Factory mapping data flows

Answer

Azure Synapse dedicated SQL pool with T-SQL queries

Question 7

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Answer

Azure HDInsight with Hive

Answer

Azure Analysis Services

Answer

Azure Databricks with PySpark

Question 8

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation.. Enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Answer

Use a conditional split to route rows with different schemas to separate sinks.

Answer

Define a rigid schema in the source dataset and reject rows that don't match.

Answer

Disable schema drift to improve performance.

Question 9

You have an Azure Data Factory pipeline that executes a stored procedure in Azure SQL Database. The pipeline fails with an error indicating that the stored procedure ran out of memory. What change should you make to the pipeline to resolve this?

Accepted Answer

Scale up the Azure SQL Database to a higher service tier.. The error indicates that the stored procedure ran out of memory, which is a resource limitation at the database level, not a transient failure or timeout issue. Scaling up the Azure SQL Database to a higher service tier (e.g., from Standard to Premium or increasing DTU/vCore count) provides more memory and compute resources, directly resolving the out-of-memory condition.

Answer

Add a retry policy to the stored procedure activity.

Answer

Increase the pipeline activity timeout.

Answer

Use a Self-Hosted Integration Runtime instead of Azure IR.

Question 10

A company is designing a data storage solution for a global application that requires low-latency reads and writes for user session data. The solution must support automatic failover across multiple Azure regions. Which TWO Azure services meet these requirements?

Accepted Answer

Azure Cache for Redis. Azure Cache for Redis is correct because it provides an in-memory data store with sub-millisecond latency for both reads and writes, making it ideal for user session data. It supports automatic failover across Azure regions through geo-replication, where data from a primary cache is asynchronously replicated to a secondary cache in a paired region, ensuring high availability and disaster recovery.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 11

You are running a batch processing job using Azure Data Factory. The job reads from Azure Blob Storage, transforms data with a Data Flow, and writes to Azure Synapse Analytics. The job fails intermittently with the error: 'Operation on target WriteToSynapse failed: Cannot bulk load because the file could not be opened.' You need to resolve the issue with minimal downtime. What should you do?

Accepted Answer

Enable staging blob file deletion in the copy activity settings.. The error indicates a transient lock on the staging blob file. Enabling staging blob file deletion in the copy activity settings allows the pipeline to clean up and retry. Increasing the degree of copy parallelism does not address file locking. Using PolyBase with external tables requires schema changes. Switching to staging via SQL authentication does not solve the file lock issue.

Answer

Increase the degree of copy parallelism in the Data Flow.

Answer

Use PolyBase to load data directly from Blob Storage without staging.

Answer

Switch to staging via SQL authentication instead of Managed Identity.

Question 12

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

Accepted Answer

Yes, because the integration runtime is self-hosted and can access on-premises data sources.. The ARM template snippet defines a self-hosted integration runtime, which is designed to be installed on a local machine inside the corporate network. This allows it to directly connect to on-premises SQL Server databases using native drivers (e.g., ADO.NET) and then copy data to Azure Blob Storage via the Azure cloud. Self-hosted IRs are the correct choice for hybrid data movement scenarios where the source is on-premises and the destination is cloud-based.

Answer

No, you need an Azure integration runtime that is configured for on-premises access.

Answer

No, you need a managed virtual network integration runtime for on-premises sources.

Answer

Yes, but only if the integration runtime is installed on a domain-joined machine.

Question 13

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

Accepted Answer

Create new columns or modify existing columns using expressions.. The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Answer

Aggregate data by grouping rows.

Answer

Sort data in ascending or descending order.

Answer

Rename or drop columns.

Question 14

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to process CSV files that arrive in a 'raw' container, transform them into Parquet format, and write them to a 'curated' container. The transformation includes filtering out rows with null values in the 'customer_id' column and adding a partition column 'year' based on the 'order_date'. You use Azure Synapse Pipelines. Which activity should you use for the transformation?

Accepted Answer

Data flow activity. The Data Flow activity in Azure Synapse Pipelines is designed for code-free, visual data transformations at scale. It can directly read CSV files from the 'raw' container, filter out rows with null 'customer_id' values using a conditional split or filter transformation, derive a 'year' column from 'order_date' using a derived column transformation, and write the results as Parquet files to the 'curated' container—all without writing code. This makes it the optimal choice for this ETL scenario.

Answer

Stored procedure activity

Answer

Notebook activity with PySpark

Answer

Copy data activity

Question 15

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

Accepted Answer

Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.. Enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Answer

Increase the provisioned RU/s on the container to handle both transactional and analytical queries.

Answer

Change the partition key to /date to optimize for analytical queries.

Answer

Create a materialized view using the change feed and store aggregated data in a separate container.