DP-200: Implementing an Azure Data Solution — Questions and Answers

Question 1: What Azure SQL Database feature automatically identifies performance recommendations such as missing indexes or plan regression?

Query Store
Extended Events
Dynamic Management Views (DMVs)
Automatic Tuning (Correct answer)

Correct answer: Automatic Tuning

Automatic Tuning in Azure SQL Database monitors workloads and can automatically create missing indexes, drop unused indexes, and correct plan regressions.

Question 2: What is the purpose of the compatibility level setting in Azure Stream Analytics?

It controls the encryption strength of output data
It defines which version of Stream Analytics query language features and behaviors are used (Correct answer)
It specifies the SLA tier for the job
It sets the maximum parallelism for the job

Correct answer: It defines which version of Stream Analytics query language features and behaviors are used

The compatibility level governs which version of the Stream Analytics query engine features are applied, ensuring consistent behavior across updates.

Question 3: In Azure Synapse Analytics dedicated SQL pool, which distribution type should be used for small dimension tables to avoid data movement during joins?

Round-robin distribution
Replicated distribution (Correct answer)
Hash distribution
Partitioned distribution

Correct answer: Replicated distribution

Replicated tables store a full copy on every compute node, eliminating data movement when joining small dimension tables with large fact tables.

Question 4: What is the purpose of statistics in Azure Synapse Analytics dedicated SQL pool?

To enforce data integrity constraints
To provide the query optimizer with data distribution information for generating efficient execution plans (Correct answer)
To compress data at the column level
To monitor real-time query throughput

Correct answer: To provide the query optimizer with data distribution information for generating efficient execution plans

Statistics describe column value distribution and help the Synapse query optimizer choose optimal join strategies, aggregation methods, and data movement operations.

Question 5: In Azure Data Factory, what is the purpose of the Copy activity's parallelism setting?

To run multiple pipeline instances simultaneously
To enable schema mapping between source and destination
To enable retry logic on transient failures
To control the number of concurrent read/write threads used during data copy to improve throughput (Correct answer)

Correct answer: To control the number of concurrent read/write threads used during data copy to improve throughput

The parallelism (degree of copy parallelism) setting controls how many threads read from source and write to sink concurrently, improving copy throughput for large datasets.

Question 6: What is the purpose of Azure Private Endpoint for Azure Storage?

To provide a private IP within your VNet for secure access to storage without traversing the public internet (Correct answer)
To accelerate CDN delivery of static assets
To enable public internet access to storage
To replicate storage across regions

Correct answer: To provide a private IP within your VNet for secure access to storage without traversing the public internet

A Private Endpoint assigns a private IP address within your VNet to a storage account, ensuring traffic never leaves the Microsoft backbone network.

Question 7: In Azure Stream Analytics, what does a tumbling window function do?

Overlaps with adjacent windows
Slides forward by a defined step size
Aggregates all data since the stream started
Segments a stream into non-overlapping, fixed-duration time buckets (Correct answer)

Correct answer: Segments a stream into non-overlapping, fixed-duration time buckets

Tumbling windows divide a stream into fixed, non-overlapping intervals, with each event belonging to exactly one window.

Question 8: Which Azure Data Factory feature provides a visually designed, code-free interface for building complex data transformation logic that runs on Spark?

Pipeline Editor
Data Flow
Mapping Data Flow (Correct answer)
Copy Data wizard

Correct answer: Mapping Data Flow

Mapping Data Flow lets you visually design data transformations using a graph-based UI; ADF compiles these flows into Spark jobs that run on an auto-managed Spark cluster.

Question 9: What does a tumbling window trigger in Azure Data Factory guarantee that a schedule trigger does not?

Automatic schema mapping
Parallel execution of all windows simultaneously
Non-overlapping, contiguous time windows with retry on failure (Correct answer)
Lower cost per execution

Correct answer: Non-overlapping, contiguous time windows with retry on failure

Tumbling window triggers process non-overlapping fixed-size time windows and automatically handle late arrivals and retries for each window.

Question 10: What does enabling diagnostic settings on an Azure data service do?

Applies automatic performance tuning recommendations
Enables automatic backups for the service
Configures high availability for the service
Routes resource logs and metrics to a destination like Log Analytics, Storage, or Event Hubs (Correct answer)

Correct answer: Routes resource logs and metrics to a destination like Log Analytics, Storage, or Event Hubs

Diagnostic settings route a resource's platform logs and metrics to one or more destinations (Log Analytics, Storage, Event Hubs) for analysis and archiving.

Question 11: An Azure Data Lake Gen 2 storage account has been created by a corporation. They wish to ingest data from numerous data sources into the storage account. Which of the following methods may they utilize to consume data from web server log files?

AzCopy Tool
Azure Event Grid
Azure Data Factory (Correct answer)
Azure Event Hubs

Correct answer: Azure Data Factory

Azure Data Factory (ADF) is the recommended method for consuming and ingesting web server log files into an Azure Data Lake Storage Gen2 account. ADF is a cloud-based ETL service that can orchestrate and automate data movement from various sources, including log files (e.g., from an HTTP endpoint or a staging blob storage), to ADLS Gen2. It provides robust scheduling, monitoring, and transformation capabilities for such recurring ingestion pipelines.

Question 12: Azure Stream Analytics is used to receive Twitter data from Azure Event Hubs and send it to an Azure Blob storage account. Every minute, you must output the number of tweets from the previous five minutes. Which windowing mode should you employ?

Sliding
Session
Tumbling
Hopping (Correct answer)

Correct answer: Hopping

A hopping window is the correct choice because it allows you to define a fixed-size window (five minutes) that moves forward by a specified 'hop' interval (every minute). This enables the system to calculate the number of tweets in the preceding five minutes, and then perform the same calculation one minute later, effectively providing a rolling five-minute count every minute. Tumbling windows are non-overlapping, and sliding windows output for every event, neither of which fits the requirement precisely.

Question 13: Which Azure HDInsight cluster type is optimized for real-time stream processing and message queuing?

Hadoop
Kafka (Correct answer)
HBase
Spark

Correct answer: Kafka

HDInsight Kafka provides a managed Apache Kafka cluster optimized for high-throughput, fault-tolerant real-time message streaming.

Question 14: Which Azure Databricks feature continuously monitors a Delta table for new files and processes them automatically in a streaming fashion?

Auto Loader (Correct answer)
VACUUM
Delta Live Tables only
OPTIMIZE

Correct answer: Auto Loader

Auto Loader in Databricks incrementally and efficiently processes new files arriving in cloud storage by tracking which files have been ingested using checkpoint files.

Question 15: In Azure Databricks, what is the purpose of Z-ordering a Delta table?

To compress data using Z-standard compression
To partition data by date automatically
To sort data alphabetically within each file
To co-locate related data in the same set of files to improve filter predicate performance (Correct answer)

Correct answer: To co-locate related data in the same set of files to improve filter predicate performance

Z-ordering organizes data in files so that rows with similar values in the Z-ordered columns are physically co-located, improving data skipping for filter queries.

Question 16: Which approach should you use in Azure Data Factory to implement incremental data loading from a relational source database?

Read-only replica with full export
Watermark column tracking or Change Data Capture (Correct answer)
Full load with truncate-and-reload on each run
Snapshot isolation on the source database

Correct answer: Watermark column tracking or Change Data Capture

Incremental loading requires a way to identify new or changed rows; ADF supports watermark columns (last-modified timestamps) and Change Data Capture for this purpose.

Question 17: In Azure Synapse Analytics, what is a PolyBase external table used for?

Encrypting data at rest
Querying data stored outside Synapse such as ADLS Gen2 without importing it (Correct answer)
Creating in-memory indexes
Storing relational data inside Synapse

Correct answer: Querying data stored outside Synapse such as ADLS Gen2 without importing it

PolyBase external tables allow Synapse Analytics to query data stored in external sources like ADLS Gen2 or Azure Blob Storage without moving it.

Question 18: In Azure Synapse Analytics, which pool type should you use for interactive, ad-hoc SQL queries over data in ADLS Gen2 without pre-provisioning compute?

Spark pool
Serverless SQL pool (Correct answer)
Data Explorer pool
Dedicated SQL pool

Correct answer: Serverless SQL pool

The Synapse serverless SQL pool enables pay-per-query T-SQL analytics over ADLS Gen2 files without provisioning or managing dedicated compute resources.

Question 19: In Azure Synapse Analytics, what does Workload Management allow you to control?

Resource allocation, query prioritization, and concurrency limits across different workload groups (Correct answer)
Automatic schema evolution in tables
Automatic index creation for slow queries
Data replication to secondary regions

Correct answer: Resource allocation, query prioritization, and concurrency limits across different workload groups

Workload Management in Synapse lets you define workload groups with guaranteed resource allocations, caps, and priorities to manage competing workloads.

Question 20: What Azure Data Factory monitoring feature allows you to view the detailed execution status and duration of each activity within a pipeline run?

Linked service diagnostics
Activity run history in Monitor tab (Correct answer)
Trigger history
Pipeline templates

Correct answer: Activity run history in Monitor tab

The Monitor tab in ADF shows pipeline and activity run history, including status, duration, input/output, and error details for each activity execution.

Question 21: Which output sink in Azure Stream Analytics is best suited for storing aggregated results in a relational format for BI reporting?

Azure SQL Database (Correct answer)
Azure Event Hubs
Azure Service Bus
Azure Blob Storage

Correct answer: Azure SQL Database

Azure SQL Database is the ideal relational output for Stream Analytics aggregations that will be consumed by BI tools like Power BI.

Question 22: Which Azure Cosmos DB metric should you monitor to detect when your provisioned throughput is being throttled?

Total Request Units consumed
HTTP 429 (Request Rate Too Large) error rate (Correct answer)
Average document size in bytes
Number of physical partitions

Correct answer: HTTP 429 (Request Rate Too Large) error rate

HTTP 429 errors in Cosmos DB indicate that requests are exceeding provisioned RU/s, causing the service to throttle and retry operations.

Question 23: What is the purpose of a linked service in Azure Data Factory?

To store connection information for a data store or compute resource (Correct answer)
To monitor data quality
To schedule pipeline triggers
To define transformation logic

Correct answer: To store connection information for a data store or compute resource

A linked service in ADF defines the connection string and credentials needed to connect to an external data store or compute target.

Question 24: In Azure Data Factory, which construct is a logical grouping of activities that together perform a unit of work?

Dataset
Pipeline (Correct answer)
Linked Service
Trigger

Correct answer: Pipeline

A Pipeline in Azure Data Factory is a logical grouping of activities—such as Copy, Data Flow, and stored procedure activities—that together accomplish a data integration task.

Question 25: What is the purpose of partitioning in Azure Synapse Analytics dedicated SQL pool tables?

To improve query performance and data management by dividing large tables into smaller segments based on a column value (Correct answer)
To enable cross-database queries
To distribute data across compute nodes
To enforce foreign key relationships

Correct answer: To improve query performance and data management by dividing large tables into smaller segments based on a column value

Partitioning divides a large table into smaller, manageable segments (typically by date), enabling partition elimination to scan only relevant data.

Question 26: In Azure Stream Analytics, what does setting a late arrival tolerance policy accomplish?

It speeds up event processing by batching late events
It drops all events that arrive after the watermark
It allows events arriving within the tolerance window to be included in the correct time-based computation (Correct answer)
It converts late events to reference data

Correct answer: It allows events arriving within the tolerance window to be included in the correct time-based computation

Late arrival tolerance allows Stream Analytics to wait a specified duration for out-of-order events before finalizing window results.

Question 27: In Azure Data Factory, what is a pipeline?

A real-time stream processor
A machine learning model deployment
A cloud storage container
A logical grouping of activities that perform a unit of work (Correct answer)

Correct answer: A logical grouping of activities that perform a unit of work

An ADF pipeline is a logical grouping of activities—such as copy, transformation, or control flow—that together perform a data integration task.

Question 28: Which Azure Stream Analytics window type allows events to be included in multiple overlapping windows?

Session window
Hopping window (Correct answer)
Tumbling window
Snapshot window

Correct answer: Hopping window

Hopping windows have a fixed size but advance by a hop interval smaller than the window size, causing windows to overlap and events to appear in multiple windows.

Question 29: What is Microsoft Purview (formerly Azure Purview) primarily used for in a data platform context?

Real-time stream processing
ETL pipeline orchestration
Database backup and restore
Unified data governance, cataloging, and lineage tracking across hybrid data sources (Correct answer)

Correct answer: Unified data governance, cataloging, and lineage tracking across hybrid data sources

Microsoft Purview provides a unified data governance solution with automated data discovery, cataloging, classification, and lineage visualization.

Question 30: Andy is employed by TPT Ltd. He receives Twitter data through Azure Event and outputs it to an Azure Blob storage account using Azure Stream Analytics. Every two minutes, Andy is to output the total number of tweets received in the previous two minutes; each tweet is tallied just once. Andy should utilize which of the provided windowing functions to achieve the requirement?

A two-minute Tumbling window (Correct answer)
A two-minute Sliding window
A two-minute Hopping window that has one-minute hop
A two-minute Session window

Correct answer: A two-minute Tumbling window

A two-minute Tumbling window is the correct windowing function for this requirement. A tumbling window divides the data stream into distinct, non-overlapping, and contiguous time segments. By using a two-minute tumbling window, each tweet will fall into exactly one window and be counted once, providing the total number of tweets received in each successive two-minute interval.

Question 31: Which migration mode in Azure Database Migration Service keeps the source database operational throughout the migration process?

Offline migration
Snapshot migration
Bulk copy migration
Online migration (Correct answer)

Correct answer: Online migration

Online migration uses continuous data synchronization so the source database remains available, minimizing downtime to a brief cutover window at the end.

Question 32: In a lambda architecture built on Azure, what is the role of the batch layer?

To route events to multiple downstream consumers
To cache query results for the serving layer
To reprocess all historical data periodically to produce accurate, comprehensive batch views (Correct answer)
To process streaming data in milliseconds

Correct answer: To reprocess all historical data periodically to produce accurate, comprehensive batch views

The batch layer processes the complete historical dataset to produce accurate batch views, compensating for any errors in the speed layer.

Question 33: Which Azure Data Factory activity is used to copy data from a source dataset to a sink dataset?

Execute Pipeline activity
Web activity
Lookup activity
Copy activity (Correct answer)

Correct answer: Copy activity

The Copy activity is ADF's primary data ingestion mechanism, transferring data from a supported source to a supported sink.

Question 34: What does the Azure SQL Database DTU (Database Transaction Unit) represent?

The number of concurrent database connections allowed
A blended measure of CPU, memory, reads, and writes that represents database performance capacity (Correct answer)
The number of transactions per second the database can handle
The amount of storage allocated to the database

Correct answer: A blended measure of CPU, memory, reads, and writes that represents database performance capacity

A DTU is a bundled measure of CPU, memory, and I/O capacity, providing a simplified way to select a performance tier without tuning individual resource parameters.

Question 35: What Azure service provides a fully managed, serverless Apache Spark environment integrated with Azure Synapse Analytics?

Azure Machine Learning Compute
Azure Synapse Spark pool (Correct answer)
Azure Databricks
Azure HDInsight

Correct answer: Azure Synapse Spark pool

Azure Synapse Spark pools provide on-demand, auto-scaling Apache Spark clusters within the Synapse workspace, billed per second of use.

Question 36: Which Azure Blob Storage access tier is most cost-effective for data that is rarely accessed and stored for at least 180 days?

Archive tier (Correct answer)
Hot tier
Cool tier
Premium tier

Correct answer: Archive tier

The Archive tier offers the lowest storage cost for data accessed infrequently, with a recommended minimum retention of 180 days.

Question 37: What Azure Synapse Analytics feature enables querying operational Azure Cosmos DB data using Spark or SQL without impacting the transactional workload?

Synapse Link for Azure Cosmos DB (Correct answer)
PolyBase external tables
Azure Data Factory Copy
Cosmos DB Change Feed

Correct answer: Synapse Link for Azure Cosmos DB

Azure Synapse Link for Cosmos DB automatically syncs data to the Synapse analytical store, allowing Spark and SQL queries without any ETL or impact on Cosmos DB throughput.

Question 38: What is the role of a reference input in Azure Stream Analytics?

To output processed results to a sink
To provide a static or slowly changing dataset for lookups and enrichment (Correct answer)
To receive high-velocity streaming events
To define watermark thresholds for late arrivals

Correct answer: To provide a static or slowly changing dataset for lookups and enrichment

A reference input in Stream Analytics is a slow-changing dataset (e.g., a CSV in Blob Storage) joined against the streaming input for data enrichment.

Question 39: What Azure service would you use to apply machine learning models to streaming data in real time without writing custom code?

Azure Stream Analytics with Azure Machine Learning integration (Correct answer)
Azure Machine Learning batch inference
Azure Databricks batch scoring
Azure Data Factory ML activity

Correct answer: Azure Stream Analytics with Azure Machine Learning integration

Azure Stream Analytics supports calling Azure Machine Learning web service endpoints directly within a streaming query to score events in real time.

Question 40: Which type of Azure Data Factory Integration Runtime must be installed on an on-premises machine to access private network data sources?

Self-hosted Integration Runtime (Correct answer)
Azure Integration Runtime
Hybrid Integration Runtime
Azure-SSIS Integration Runtime

Correct answer: Self-hosted Integration Runtime

Self-hosted Integration Runtime is installed on on-premises or cloud VMs in a private network, allowing Azure Data Factory to reach data sources not exposed to the public internet.

Question 41: What is the key difference between Azure Data Factory's Copy Activity and Mapping Data Flow?

Data Flow is only for real-time streaming; Copy Activity is for batch only
Copy Activity uses Python scripts; Data Flow uses SQL only
Copy Activity supports transformation while Data Flow only copies data
Copy Activity moves data without transformation; Data Flow performs complex transformations on Spark (Correct answer)

Correct answer: Copy Activity moves data without transformation; Data Flow performs complex transformations on Spark

Copy Activity is optimized for high-throughput data movement without transformation logic, while Mapping Data Flow provides a visual, code-free transformation environment backed by Spark.

Question 42: What Azure Databricks feature allows you to query the state of a Delta table at a previous point in time?

Auto Loader
Time travel (Correct answer)
Z-ordering
Schema evolution

Correct answer: Time travel

Delta Lake time travel allows querying historical table versions using VERSION AS OF or TIMESTAMP AS OF syntax.

Question 43: In Azure Data Factory, which trigger type executes a pipeline based on a defined schedule (e.g., daily at midnight)?

Schedule trigger (Correct answer)
Tumbling window trigger
Manual trigger
Event-based trigger

Correct answer: Schedule trigger

A schedule trigger fires a pipeline at a fixed wall-clock time or on a recurring schedule such as daily, weekly, or hourly.

Question 44: Which Azure Cosmos DB feature automatically distributes data and throughput across multiple partitions?

Horizontal partitioning (Correct answer)
Time-to-Live (TTL)
Change feed
Automatic indexing

Correct answer: Horizontal partitioning

Horizontal partitioning in Cosmos DB automatically splits data across logical and physical partitions to scale throughput and storage.

Question 45: In a lambda architecture on Azure, which component serves the speed layer for low-latency real-time queries?

Azure Data Lake Analytics
Azure Stream Analytics or Event Hubs with real-time consumers (Correct answer)
Azure Blob Storage
Azure Data Factory

Correct answer: Azure Stream Analytics or Event Hubs with real-time consumers

The speed layer processes streaming data in near real-time using services like Azure Stream Analytics to answer latency-sensitive queries.

Question 46: What Azure Cosmos DB feature can be used to trigger downstream processing whenever data in a container is inserted or updated?

Triggers
Stored procedures
UDFs
Change feed (Correct answer)

Correct answer: Change feed

The change feed feature in Cosmos DB provides a persistent log of inserts and updates that downstream services can consume for event-driven processing.

Question 47: In Azure SQL Database, what is the purpose of Query Store?

To store parameterized queries as stored procedures
To cache frequently executed query results
To replicate queries to a secondary replica
To capture query execution plans and runtime statistics for performance troubleshooting (Correct answer)

Correct answer: To capture query execution plans and runtime statistics for performance troubleshooting

Query Store persists query execution plans, runtime statistics, and wait statistics over time, enabling analysis of query performance regressions.

Question 48: In Azure Databricks, which command is used to optimize a Delta table by compacting small files into larger ones?

COMPACT
ANALYZE
VACUUM
OPTIMIZE (Correct answer)

Correct answer: OPTIMIZE

The OPTIMIZE command in Delta Lake compacts small Parquet files into larger ones, improving query performance by reducing file overhead.

Question 49: What is the purpose of Streaming Units (SUs) in Azure Stream Analytics?

They represent the compute resources (CPU and memory) allocated to a Stream Analytics job (Correct answer)
They represent the number of event hubs partitions allocated
They control the event retention period
They define the number of output sinks

Correct answer: They represent the compute resources (CPU and memory) allocated to a Stream Analytics job

Streaming Units represent the combined CPU and memory resources allocated to a Stream Analytics job, and scaling SUs increases processing throughput.

Question 50: Which Azure service is used to ingest high-velocity streaming data such as telemetry from millions of IoT devices before processing in Azure Databricks?

Azure Relay
Azure Service Bus
Azure Event Hubs (Correct answer)
Azure Notification Hubs

Correct answer: Azure Event Hubs

Azure Event Hubs is a massively scalable event streaming platform that can ingest millions of events per second for downstream processing.

DP-200: Implementing an Azure Data Solution

DP-200 validates skills in implementing Azure data storage solutions, developing data processing pipelines, and monitoring and optimizing Azure data solutions. Note: This exam was retired and replaced by DP-203.

Questions

180 min

Time Limit

70%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds