DP-203 Practice Exam 53 — 20 Questions

Question 1

A company is migrating its on-premises SQL Server data warehouse to Azure Synapse Analytics. They have a fact table with 2 billion rows and 30 columns. The table is frequently joined on CustomerID and filtered on OrderDate. What is the recommended table design?

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

Accepted Answer

Hash-distribute on CustomerID and partition on OrderDate. Hash-distributing the fact table on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution node, which makes joins on CustomerID efficient by avoiding data movement. Partitioning on OrderDate enables partition elimination when filtering by date, reducing the amount of data scanned. This combination optimizes both the join and filter operations for a large fact table in Azure Synapse Analytics.

Answer

Replicate the table to all nodes

Answer

Round-robin distribution with partitions on OrderDate

Answer

Hash-distribute on OrderDate and partition on CustomerID

Question 2

Drag and drop the steps to configure Azure Stream Analytics job with event input and Power BI output into the correct order.

Accepted Answer

Step 1: Set up Event Hub as data source, Step 2: Create Stream Analytics job, Step 3: Configure input (Event Hub), Step 4: Configure output (Power BI), Step 5: Write the query, Step 6: Start the job. First, set up the event hub as the data source. Then create the Stream Analytics job, configure input and output, write the query, and start it.

Answer

Step 1: Set up Event Hub as data source, Step 2: Configure input (Event Hub), Step 3: Create Stream Analytics job, Step 4: Configure output (Power BI), Step 5: Write the query, Step 6: Start the job

Answer

Step 1: Set up Event Hub as data source, Step 2: Create Stream Analytics job, Step 3: Configure output (Power BI), Step 4: Configure input (Event Hub), Step 5: Write the query, Step 6: Start the job

Answer

Step 1: Set up Event Hub as data source, Step 2: Create Stream Analytics job, Step 3: Configure input (Event Hub), Step 4: Write the query, Step 5: Configure output (Power BI), Step 6: Start the job

Question 3

Drag and drop the steps to configure Azure Databricks auto-scaling cluster for ETL workloads into the correct order.

Accepted Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then select runtime version, then attach libraries, then set cluster policies.. First create the workspace, then the cluster with auto-scaling settings, choose runtime, attach libraries, and set policies.

Answer

Create Azure Databricks workspace, then select runtime version, then configure cluster auto-scaling settings, then attach libraries, then set cluster policies.

Answer

Configure cluster auto-scaling settings, then create Azure Databricks workspace, then select runtime version, then attach libraries, then set cluster policies.

Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then attach libraries, then select runtime version, then set cluster policies.

Question 4

Drag and drop the steps to configure Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2 into the correct order.

Accepted Answer

Set up the storage account and data, then create the serverless SQL pool, then create a database, then create an external data source referencing the storage, and finally create an external file format and external table.. First, set up the storage account and data. Then create the serverless SQL pool, a database, external data source referencing the storage, and finally external file format and external table to query.

Answer

Create the serverless SQL pool, then set up the storage account and data, then create a database, then create an external data source, and finally create an external table.

Answer

Create a database, then create an external data source, then create an external file format, then create an external table, and finally set up the storage account and data.

Answer

Set up the storage account and data, then create a database, then create an external data source, then create an external file format, then create an external table, and finally create the serverless SQL pool.

Question 5

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

Accepted Answer

Use Delta Lake's OPTIMIZE command to compact small files.. The OPTIMIZE command in Delta Lake compacts small files into larger ones, reducing the number of files that Spark must read during subsequent queries and writes. This is critical for large datasets where many small files can cause significant overhead in file listing and task scheduling. Option C is correct because Z-order optimization on frequently filtered columns improves data skipping, allowing Delta Lake to prune irrelevant files during scans, which dramatically reduces I/O and speeds up query performance.

Answer

Disable automatic schema detection to reduce overhead.

Answer

Cache the entire DataFrame in memory after reading.

Answer

Enable auto-compaction in Spark configuration.

Question 6

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised. This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Answer

The Parquet files are not using the .parquet extension.

Answer

The derivedModel option is set to false, which disables schema inference.

Answer

The recursive option is causing the table to include files from subfolders that have different schemas.

Question 7

Which TWO actions should you take to reduce costs associated with an Azure Synapse Dedicated SQL Pool that is used for reporting during business hours only?

Accepted Answer

Pause the pool during non-business hours.. Pausing a Dedicated SQL Pool stops billing for compute resources (DWU) while retaining storage costs. Since the pool is only needed for reporting during business hours, pausing it during non-business hours directly eliminates compute charges for that period, which is the most significant cost driver.

Answer

Enable advanced data compression on all tables.

Answer

Scale down the pool during business hours.

Answer

Change the distribution of large tables to ROUND_ROBIN.

Question 8

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Accepted Answer

Delta Lake. Delta Lake is correct because it provides ACID transactions, schema enforcement, and time travel capabilities, which are essential for handling late-arriving data and ensuring exactly-once semantics when combined with Structured Streaming. It allows you to merge late records into existing Delta tables using merge operations (upserts) without corrupting the data state.

Answer

PolyBase

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 9

You are designing a data storage solution for a global e-commerce company. The company needs to store clickstream data from millions of users with high write throughput and low-latency reads for real-time analytics. The data is semi-structured and includes nested JSON objects. Which Azure data store should you recommend?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides a multi-model, globally distributed database service with guaranteed single-digit-millisecond read and write latencies at the 99th percentile, making it ideal for high-throughput clickstream ingestion and real-time analytics. Its native support for semi-structured data and nested JSON objects via the SQL API (or MongoDB API) allows direct storage and querying of complex event payloads without schema flattening. Additionally, Cosmos DB offers automatic indexing and tunable consistency levels to balance performance and data freshness for global e-commerce scenarios.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 10

Which TWO of the following are supported storage options for use as a source in Azure Synapse Pipeline Copy Activity?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is a supported source for Azure Synapse Pipeline Copy Activity because it combines a hierarchical file system with Azure Blob Storage APIs, enabling efficient data ingestion. The Copy Activity can read data from ADLS Gen2 using the AzureBlobFS linked service, which supports both file and folder-level reads for structured and unstructured data.

Answer

Azure Analysis Services

Answer

Azure Cognitive Search

Answer

Azure Purview

Question 11

A data engineer needs to store log data from multiple applications in Azure. The data is append-only, heavily compressed, and queried infrequently. Cost minimization is critical. Which storage solution is best?

Accepted Answer

Azure Blob Storage with cool or archive access tier. Azure Blob Storage with cool or archive access tier is the best choice because the data is append-only, heavily compressed, and infrequently queried, making cost minimization the top priority. The cool tier offers low storage costs with higher access charges, while the archive tier provides the lowest storage cost for data that is rarely accessed and can tolerate hours of retrieval latency. This aligns perfectly with the append-only, infrequently queried nature of the log data.

Answer

Azure Table Storage

Answer

Azure Cosmos DB with analytical store

Answer

Azure Data Lake Storage Gen2 with hot tier

Question 12

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.. The error 'Could not create SSL/TLS secure channel' indicates a TLS version mismatch. Azure SQL Database and Azure Blob Storage require at least TLS 1.2 for secure connections. If the self-hosted integration runtime (SHIR) is configured to use TLS 1.0, the handshake fails because the services reject the older protocol. This is the most likely cause given the intermittent nature and the specific error message.

Answer

Azure SQL Database firewall rules blocking the IP address of the integration runtime.

Answer

Transient network connectivity issues between the services.

Answer

The Azure SQL Database DTU limit has been exceeded, causing throttling.

Question 13

You need to store semi-structured JSON data from a web application that requires low-latency reads and writes at a global scale. The data must be indexed automatically and support SQL-like queries. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB (NoSQL API). Azure Cosmos DB with the NoSQL API is the correct choice because it natively stores semi-structured JSON documents, provides automatic indexing of all properties, supports SQL-like queries via its query engine, and offers low-latency reads and writes at global scale through multi-region replication and configurable consistency levels. This combination directly matches the requirements for a globally distributed web application needing fast, queryable JSON storage.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 14

You are designing data security for Azure Data Lake Storage Gen2. The requirement is to prevent data from being accessed by anyone outside the corporate network. Which feature should you enable?

Accepted Answer

Use Azure Private Endpoint or service endpoint with a VNet.. Azure Private Endpoint or service endpoint with a VNet ensures that all traffic to the storage account stays within the corporate network and never traverses the public internet. Private Endpoint assigns a private IP from the VNet to the storage account, effectively isolating it from public access. This meets the requirement to prevent access from outside the corporate network by enforcing network-level isolation.

Answer

Assign RBAC roles to deny access to all except corporate users.

Answer

Configure IP firewall rules to allow only corporate IP ranges.

Answer

Enable encryption at rest using customer-managed keys.

Question 15

You are designing a data pipeline in Azure Synapse Analytics that ingests streaming taxi trip data from Azure Event Hubs. The data must be processed in near real-time and stored in a dedicated SQL pool. The pipeline should handle late-arriving data (up to 30 minutes late) without reprocessing the entire stream. Which Azure service should you use to process the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing with native support for Event Hubs as an input and dedicated SQL pool as an output. It can handle late-arriving data via its built-in 'late arrival' window (configurable up to 30 minutes) using event time processing, without requiring reprocessing of the entire stream.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hubs trigger

Question 16

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

Accepted Answer

Notebook activity configured to use a Python kernel. A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Answer

Web activity

Answer

Stored Procedure activity

Answer

HDInsight Hive activity

Question 17

Which TWO actions should you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool when working with large fact tables?

Accepted Answer

Create statistics on columns used in WHERE clauses.. Creating statistics on columns used in WHERE clauses enables the Azure Synapse Analytics dedicated SQL pool query optimizer to generate more accurate cardinality estimates, leading to better join strategies and index selections. Without up-to-date statistics, the optimizer may choose suboptimal plans, especially for large fact tables where data distribution skew is common.

Answer

Use replicated distribution for the fact table.

Answer

Use round-robin distribution to evenly distribute data.

Answer

Use clustered index instead of columnstore index.

Question 18

Which THREE of the following are valid methods to load data into Azure Synapse Analytics?

Accepted Answer

Azure Data Factory pipeline. Azure Data Factory (ADF) is a fully managed cloud-based ETL service that provides native connectors to Azure Synapse Analytics. It supports high-throughput data movement using the PolyBase engine or staged copy, making it a valid and recommended method for loading data at scale into Synapse dedicated SQL pools.

Answer

BULK INSERT with a data source from Azure Blob Storage

Answer

SQL Server Integration Services (SSIS) package

Question 19

You are using Azure Stream Analytics to process real-time data from an Event Hub. Which TWO of the following are valid output sinks?

Accepted Answer

Azure Synapse Analytics. Azure Stream Analytics supports Azure Synapse Analytics as a native output sink, allowing you to write real-time streaming results directly into dedicated SQL pools for high-performance analytics. This is achieved via the built-in Azure Synapse Analytics output adapter, which uses PolyBase or COPY INTO for efficient bulk ingestion.

Answer

Azure Queue Storage

Answer

Azure Files

Answer

Azure Table Storage

Question 20

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). 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)