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

Question 1: Which feature in Azure Synapse Analytics allows querying CSV or Parquet files in ADLS Gen2 without creating a dedicated SQL pool?

Spark pool
Synapse Link
Dedicated SQL pool
Serverless SQL pool (Correct answer)

Correct answer: Serverless SQL pool

Synapse serverless SQL pool enables on-demand T-SQL queries over files in ADLS Gen2 with no infrastructure to provision.

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

OPTIMIZE (Correct answer)
VACUUM
ANALYZE
COMPACT

Correct answer: OPTIMIZE

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

Question 3: You're utilizing Azure metrics to keep track of an Azure Stream Analytics task. You notice that the average watermark delay over the last 12 hours has consistently exceeded the configured late arrival tolerance. What could be the source of this behavior?

Events with an application timestamp that is more than five minutes earlier than their arrival time are received as inputs.
The late arrival policy causes events to be dropped
The job's resources are insufficient to handle the volume of incoming data. (Correct answer)
The input data contains errors.

Correct answer: The job's resources are insufficient to handle the volume of incoming data.

A consistently high average watermark delay exceeding the late arrival tolerance indicates that the Azure Stream Analytics job is struggling to process incoming data at the rate it's arriving. This usually points to insufficient resources allocated to the job. Increasing the job's streaming units (SUs) would provide more processing power, allowing it to catch up with the incoming event stream and reduce the watermark delay.

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

To provide the query optimizer with data distribution information for generating efficient execution plans (Correct answer)
To compress data at the column level
To enforce data integrity constraints
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: Which tool should you run before migrating an on-premises SQL Server database to Azure SQL Database to identify compatibility issues?

Azure Advisor
Azure Cost Management
Data Migration Assistant (DMA) (Correct answer)
Database Experimentation Assistant

Correct answer: Data Migration Assistant (DMA)

Data Migration Assistant (DMA) assesses SQL Server databases for compatibility issues and provides actionable recommendations before migration to Azure SQL Database.

Question 6: What does the VACUUM command do on a Delta Lake table?

Compacts small files into larger Parquet files
Applies schema changes to all partitions
Rebuilds statistics for query optimization
Removes old data files no longer referenced by the transaction log (Correct answer)

Correct answer: Removes old data files no longer referenced by the transaction log

VACUUM deletes data files older than the retention threshold that are no longer referenced by the Delta transaction log, reclaiming storage.

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

Applies automatic performance tuning recommendations
Configures high availability for the service
Enables automatic backups 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 8: Which Azure Key Vault object type is used to store and manage symmetric or asymmetric cryptographic keys for data encryption?

Certificates
Policies
Secrets
Keys (Correct answer)

Correct answer: Keys

Azure Key Vault Keys store cryptographic key material and support operations like encrypt, decrypt, sign, and verify for data protection scenarios.

Question 9: Which Azure Data Factory Integration Runtime type enables lift-and-shift of on-premises SSIS packages to run in the cloud?

SSIS Migration Assistant
Self-hosted Integration Runtime
Azure-SSIS Integration Runtime (Correct answer)
Azure IR with SSIS trigger

Correct answer: Azure-SSIS Integration Runtime

Azure-SSIS Integration Runtime is a fully managed cluster that allows existing SSIS packages to be executed natively in Azure without modification.

Question 10: 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 Service Bus
Azure Notification Hubs
Azure Event Hubs (Correct answer)
Azure Relay

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.

Question 11: Which Azure Data Factory integration runtime is used to execute activities within a private virtual network without exposing data to the public internet?

Azure Integration Runtime
Self-hosted Integration Runtime
Managed Virtual Network Integration Runtime (Correct answer)
SSIS Integration Runtime

Correct answer: Managed Virtual Network Integration Runtime

The Managed Virtual Network Integration Runtime runs within a managed VNet, providing secure connectivity to private data sources without public exposure.

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

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

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 13: Which Azure Data Factory Mapping Data Flow feature automatically handles new or changed columns in the source without requiring pipeline changes?

Manual schema mapping
Schema enforcement
Dataset versioning
Schema drift (Correct answer)

Correct answer: Schema drift

Schema drift in Mapping Data Flow allows the data flow to dynamically adapt to changes in the source schema—such as added or renamed columns—without failing or requiring manual updates.

Question 14: In Azure Data Lake Storage Gen2, what is the recommended file size range for optimal analytics performance?

1–10 KB
10–50 GB
256 KB–1 MB
256 MB–1 GB (Correct answer)

Correct answer: 256 MB–1 GB

Analytics frameworks like Spark perform best with files in the 256 MB–1 GB range, as very small files create excessive metadata overhead.

Question 15: In Azure SQL Database, what is the purpose of an Elastic Pool?

To share a pool of compute and storage resources (eDTUs or vCores) across multiple databases cost-efficiently (Correct answer)
To enable horizontal sharding of a single large database
To replicate databases across regions automatically
To cache database results in memory across instances

Correct answer: To share a pool of compute and storage resources (eDTUs or vCores) across multiple databases cost-efficiently

Elastic Pools allow multiple databases with variable workloads to share a common resource pool, reducing cost compared to provisioning each database individually.

Question 16: What is the primary advantage of using Azure Data Lake Storage Gen2 over Azure Blob Storage for analytics workloads?

Built-in machine learning capabilities
Lower cost per GB
Hierarchical namespace enabling faster analytics operations (Correct answer)
Automatic schema detection

Correct answer: Hierarchical namespace enabling faster analytics operations

ADLS Gen2's hierarchical namespace makes directory-level operations atomic and significantly faster, improving analytics throughput.

Question 17: What does Azure SQL Dynamic Data Masking do?

Limits exposure of sensitive data by masking it for non-privileged users in query results (Correct answer)
Audits all data access to a log
Encrypts data at rest
Prevents unauthorized schema changes

Correct answer: Limits exposure of sensitive data by masking it for non-privileged users in query results

Dynamic Data Masking obscures sensitive column values (e.g., showing XXXX-1234 for credit card numbers) in query results for non-privileged users.

Question 18: 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 19: In Azure Databricks, what is the purpose of Z-ordering a Delta table?

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)
To compress data using Z-standard compression
To partition data by date automatically

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 20: What does the Azure SQL Database DTU (Database Transaction Unit) represent?

The amount of storage allocated to the database
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 number of concurrent database connections allowed

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 21: Which columnar file format is natively optimized for Azure Data Lake Storage Gen2 and Azure Synapse Analytics analytics workloads?

JSON
CSV
Parquet (Correct answer)
XML

Correct answer: Parquet

Parquet is a columnar storage format that provides efficient compression and fast analytical query performance, and is natively supported across Azure analytics services including Synapse and Databricks.

Question 22: Which mechanism in Azure Data Lake Storage Gen2 allows you to grant a service principal read access to a specific directory without granting access to the entire storage account?

Storage account key rotation
POSIX ACLs (Correct answer)
Shared Access Signature (SAS) token
Azure Policy

Correct answer: POSIX ACLs

POSIX ACLs in ADLS Gen2 allow granular, directory- and file-level permissions to be assigned to service principals and users.

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

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

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 24: Which Azure Data Factory activity is used to copy data from a source dataset to a sink dataset?

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

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 25: 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
Copy Data wizard
Mapping Data Flow (Correct answer)

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 26: What is the role of a reference input in Azure Stream Analytics?

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
To output processed results to a sink

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 27: Which Azure Monitor feature allows you to define automated actions—such as sending an email or triggering a runbook—when a metric crosses a threshold?

Diagnostic settings
Action groups with alert rules (Correct answer)
Log Analytics workspaces
Azure Advisor recommendations

Correct answer: Action groups with alert rules

Alert rules evaluate metric or log conditions and trigger action groups that can send emails, call webhooks, or execute automation runbooks.

Question 28: Which Azure service is best suited for orchestrating complex data workflows that include both batch and real-time components with dependencies between steps?

Azure Scheduler
Azure Data Factory (Correct answer)
Azure Functions
Azure Logic Apps

Correct answer: Azure Data Factory

Azure Data Factory is a cloud-scale data integration service that orchestrates both batch ETL and hybrid data pipelines with rich dependency and scheduling capabilities.

Question 29: In Azure Synapse Analytics, what feature allows row-level access control so different users see only the rows they are authorized to view?

Dynamic Data Masking
Row-level security (RLS) (Correct answer)
Always Encrypted
Column-level security

Correct answer: Row-level security (RLS)

Row-Level Security in Synapse Analytics uses security policies and predicate functions to filter rows based on the executing user's identity.

Question 30: Which Azure SQL Database feature allows encrypting specific sensitive columns so that data is only decrypted by authorized client applications?

Always Encrypted (Correct answer)
Transparent Data Encryption (TDE)
Dynamic Data Masking
Row-Level Security (RLS)

Correct answer: Always Encrypted

Always Encrypted ensures that sensitive column data is encrypted at the client and never exposed in plaintext to the database engine or DBAs.

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

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

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 32: What is the primary benefit of using Azure Data Factory's data flow (mapping data flow) over a custom Spark script?

Lower memory usage
Support for real-time streaming only
Codeless, visually designed transformations that run on Spark clusters (Correct answer)
Faster execution than hand-written Spark code

Correct answer: Codeless, visually designed transformations that run on Spark clusters

Mapping data flows provide a visual, codeless interface for building Spark transformations, making complex ETL accessible without Spark expertise.

Question 33: What does Transparent Data Encryption (TDE) protect in Azure SQL Database?

Data in transit between client and server
Row-level data based on user identity
Column-level data from unauthorized queries
Data at rest by encrypting database files, backups, and transaction logs (Correct answer)

Correct answer: Data at rest by encrypting database files, backups, and transaction logs

TDE encrypts the entire database at the storage level, protecting data files, backups, and transaction logs at rest.

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

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

Correct answer: Horizontal partitioning

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

Question 35: 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 Hopping window that has one-minute hop
A two-minute Sliding window
A two-minute Tumbling window (Correct answer)
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 36: Which Azure Cosmos DB metric should you monitor to detect when your provisioned throughput is being throttled?

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

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 37: What is the purpose of partitioning in Azure Synapse Analytics dedicated SQL pool tables?

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

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 38: Which replication option provides the highest durability for Azure Storage by maintaining six copies across two geographically separated data centers?

Geo-Redundant Storage (GRS)
Zone-Redundant Storage (ZRS)
Locally Redundant Storage (LRS)
Geo-Zone-Redundant Storage (GZRS) (Correct answer)

Correct answer: Geo-Zone-Redundant Storage (GZRS)

GZRS combines zone redundancy within the primary region and geo-replication to a secondary region, providing the highest overall durability.

Question 39: A business has a SaaS solution that makes use of Azure SQL Database and elastic pools. Each customer organization has its own database in the solution. Peak consumption periods for customer organizations occur at various times throughout the year. To save money, you should use the Azure SQL Database elastic pool. Which option, if any, should you set up?

Number of transactions only
eDTUs and max data size (Correct answer)
Number of databases only
CPU usage only

Correct answer: eDTUs and max data size

To save money with Azure SQL Database elastic pools, especially when customer peak consumption periods vary, you should configure the eDTUs (elastic Database Transaction Units) and the max data size. eDTUs represent the combined measure of CPU, memory, and I/O resources, allowing databases within the pool to share resources and burst when needed. By setting appropriate eDTU limits and maximum storage, you can optimize costs by provisioning for the aggregate demand rather than individual peaks.

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

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

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 41: In Azure Synapse Analytics dedicated SQL pool, which distribution type should be used for small dimension tables to avoid data movement during joins?

Hash distribution
Round-robin distribution
Replicated distribution (Correct answer)
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 42: What Azure Databricks feature allows you to query the state of a Delta table at a previous point in time?

Auto Loader
Z-ordering
Time travel (Correct answer)
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 Synapse Analytics, what is the purpose of column-level security?

To encrypt specific columns using Always Encrypted
To restrict access to specific table columns based on user permissions using GRANT/DENY on columns (Correct answer)
To mask column values for non-privileged users
To index columns for faster query performance

Correct answer: To restrict access to specific table columns based on user permissions using GRANT/DENY on columns

Column-level security uses T-SQL GRANT/DENY statements on individual columns to restrict which users or roles can SELECT specific fields.

Question 44: Which Azure tool provides a unified monitoring experience for metrics, logs, and alerts across Azure data services?

Azure Service Health
Azure Monitor (Correct answer)
Azure Advisor
Azure Cost Management

Correct answer: Azure Monitor

Azure Monitor collects, analyzes, and acts on telemetry from Azure resources, providing metrics, logs, alerts, and dashboards in a single platform.

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

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

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 46: Which Azure storage option is most appropriate for storing semi-structured IoT telemetry data that requires millisecond reads by device ID?

Azure SQL Database
Azure Table Storage (Correct answer)
Azure File Storage
Azure Queue Storage

Correct answer: Azure Table Storage

Azure Table Storage is a key-value NoSQL store optimized for fast point lookups by partition key (e.g., device ID) at low cost.

Question 47: You're working on an Azure Data Lake Storage Gen 2 application. You must propose a method for granting permissions to a certain application for a set length of time. What should your advice include?

role assignments
Azure Active Directory (Azure AD) identities
account keys
shared access signatures (SAS) (Correct answer)

Correct answer: shared access signatures (SAS)

Shared Access Signatures (SAS) are the recommended method for granting time-limited and granular permissions to specific resources in Azure Data Lake Storage Gen2. A SAS token provides delegated access to storage resources for a specified period, with defined permissions and an optional IP address restriction. This ensures that the application only has the necessary access for a controlled duration, enhancing security.

Question 48: 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?

Dedicated SQL pool
Spark pool
Serverless SQL pool (Correct answer)
Data Explorer 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 49: What Azure Data Factory monitoring feature allows you to view the detailed execution status and duration of each activity within a pipeline run?

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

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 50: In Azure Databricks, what is a Delta Lake table?

A real-time streaming sink only
A proprietary binary format incompatible with Spark
A NoSQL document store
An ACID-compliant storage layer built on Parquet files with a transaction log (Correct answer)

Correct answer: An ACID-compliant storage layer built on Parquet files with a transaction log

Delta Lake adds ACID transactions, schema enforcement, and time travel to Parquet files by maintaining a JSON transaction log alongside the data.

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

These practice questions were written with AI tools. They are not real exam questions. Exam facts are checked against the official sources listed below. Not reviewed by a subject-matter expert. Editorial policy

Sources & references

Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy

learn.microsoft.com — Eligibility, renewal, score validity, retake policy, passing score, score release, exam information · verified Nov 2026
pearsonvue.com — Test delivery · verified Nov 2026