DP-203 Multiple Select — 40 Questions

Question 1

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using the COPY statement. For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Answer

Using INSERT INTO VALUES

Answer

Using BCP utility

Answer

Using SQL Server Integration Services (SSIS)

Question 2

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

Accepted Answer

Copy activity. The Copy activity (B) is correct because it is the native Azure Synapse Analytics/Data Factory pipeline activity designed to ingest data from disparate sources such as Azure Blob Storage, Azure SQL Database, and REST API connectors into a dedicated SQL pool sink, handling schema mapping and parallel loading. The Stored procedure activity (D) is correct because it invokes a stored procedure in the dedicated SQL pool after the load completes, which is exactly what is needed to run the aggregation logic on the loaded data. The Execute Pipeline activity (A) only calls another pipeline and does not itself move or transform data, so it does not satisfy the load or aggregation requirement. The Azure Function activity (C) runs custom code in an Azure Function, which is unnecessary here since built-in Copy and Stored procedure activities cover the scenario. The Data Flow activity (E) performs code-free transformations in a Spark-based execution engine, but the requirement is to load data and then aggregate via a stored procedure, not to transform data in a mapping data flow.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity

Question 3

Which TWO are valid ways to process data in Azure Synapse Analytics?

Accepted Answer

Use Synapse SQL pool to run T-SQL queries.. Option C is correct because a Synapse SQL pool (dedicated or serverless) is a core Synapse Analytics engine that executes T-SQL queries directly against data in the workspace, making it a valid data-processing method. Option E is correct because Synapse Spark notebooks run Apache Spark code, including Scala, Python, SQL, and R, and are a first-class way to transform and process data in Azure Synapse Analytics. Option A is not appropriate because Logic Apps is a workflow/automation service for orchestrating triggers and connectors, not a data transformation engine. Option B is not the intended Synapse processing method because Azure Functions is a separate serverless compute service outside Synapse's built-in SQL and Spark engines. Option D is not correct because Power BI is a visualization and reporting tool; its data transformation (Power Query) is for modeling/reporting, not a Synapse data-processing workload.

Answer

Use Logic Apps to run data transformations.

Answer

Use Azure Functions to process data in a serverless manner.

Answer

Use Power BI to transform data.

Question 4

Which TWO actions should you take to secure sensitive data in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Use access control lists (ACLs) to restrict access to specific directories. Option B is correct because Azure Data Lake Storage Gen2 supports POSIX-style access control lists (ACLs) that let you grant or deny read/write/execute permissions at the directory and file level, which is essential for least-privilege access to sensitive data. Option E is correct because enabling encryption at rest with customer-managed keys stored in Azure Key Vault gives you control over the key lifecycle and satisfies compliance requirements for protecting sensitive data at rest. Option A is wrong because enabling public network access from all networks exposes the storage account to the internet and increases attack surface. Option C is wrong because allowing anonymous access permits unauthenticated reads and is a security risk, not a protection measure. Option D is wrong because disabling soft delete removes a recovery safeguard and does nothing to secure sensitive data.

Answer

Enable public network access from all networks for ease of use

Answer

Allow anonymous access to enable sharing

Answer

Disable soft delete to prevent accidental retention of deleted data

Question 5

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Avoid data skew by choosing a good distribution key.. Option B is correct because choosing a good distribution key prevents data skew, which otherwise causes some distributions to hold disproportionate rows and forces the query engine to move data across nodes, degrading performance in a dedicated SQL pool. Option D is correct because replicated tables copy the full table to every compute node, eliminating data movement for joins; this is recommended for small dimension tables under roughly 1 GB (or up to 2 GB compressed). Option E is correct because hash distribution on a high-cardinality column spreads rows evenly across the 60 distributions, which is ideal for large fact tables and minimizes skew. Option A is wrong because clustered columnstore indexes are the default and preferred storage for large tables in dedicated SQL pools, delivering high compression and fast analytical scans. Option C is wrong because round-robin distribution is a fallback for staging or tables with no clear join key, not a blanket best practice for all large fact tables, since it can cause costly data movement during joins.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.

Question 6

Which TWO Azure services can be used to audit data access and changes in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Storage account diagnostic settings.. Storage account diagnostic settings (C) are correct because they can stream Data Lake Storage Gen2 resource logs—such as Read, Write, and Delete operations on blobs and ADLS Gen2 filesystems—to a Log Analytics workspace, storage account, or Event Hub, providing the raw audit trail of data access and changes. Azure Monitor and Microsoft Sentinel (D) are correct because Azure Monitor collects and queries those diagnostic logs (via Log Analytics/KQL) for auditing, while Microsoft Sentinel ingests the same storage/ADLS logs to detect, alert on, and investigate suspicious data-access activity. Microsoft Entra ID sign-in logs (A) only record authentication and token issuance events, not per-file data-plane operations, so they cannot audit data access or changes. Azure Backup reports (B) cover backup job status and protected-item health, not data-plane read/write/delete auditing. Azure Policy (E) evaluates and enforces resource configuration compliance (control plane), not individual data access or modification events.

Answer

Microsoft Entra ID sign-in logs.

Answer

Azure Backup reports.

Answer

Azure Policy.

Question 7

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Accepted Answer

Delta Lake provides time travel capabilities for accessing historical data versions.. Option A is correct because Delta Lake stores a transaction log (_delta_log) that records every commit, enabling time travel queries such as SELECT * FROM table VERSION AS OF n or TIMESTAMP AS OF, which is valuable for auditing, reproducing past results, and recovering from bad writes in a daily batch pipeline. Option D is correct because Delta Lake provides ACID transactions on top of ADLS Gen2, so concurrent writers and readers see consistent snapshots and partial or failed writes are not exposed, which matters when multiple jobs or retries touch the same output. Option B is wrong because schema evolution is actually a Delta Lake strength (mergeSchema and automatic schema evolution in MERGE/append), while Parquet requires manual rewrites or workarounds. Option C is wrong because Delta Lake does not automatically compress data to reduce storage cost; it relies on Parquet compression plus OPTIMIZE/Z-ORDER and VACUUM for layout and cleanup, and it adds transaction-log overhead. Option E is wrong because Azure Databricks natively reads and writes Parquet files, including via Spark and external tables.

Answer

Parquet is easier to implement for schema evolution than Delta Lake.

Answer

Delta Lake reduces storage costs by automatically compressing data.

Answer

Parquet files are not natively supported by Azure Databricks.

Question 8

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Accepted Answer

Enable Azure Storage Service Encryption (SSE) for data at rest. Option B is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting stored files and blobs even if the underlying media is compromised. Option D is correct because Data Lake Storage Gen2 supports a hierarchical namespace where Azure RBAC controls management-plane and broad data access while POSIX-style ACLs provide fine-grained file and directory permissions, ensuring only authorized identities can read or modify data. Option E is correct because requiring secure transfer (HTTPS) enforces TLS encryption of data in transit between clients and the storage account, preventing eavesdropping or tampering on the network. Option A is not correct because TLS 1.0 is deprecated and insecure; enabling it weakens transport security rather than strengthening it, and Azure recommends TLS 1.2 or higher. Option C is not correct because firewall IP restrictions are a network-perimeter control, not a mechanism for securing data at rest or in transit, and they do not encrypt or authorize data access by themselves.

Answer

Enable TLS 1.0 for compatibility with legacy clients

Answer

Configure firewall rules to allow only trusted IPs

Question 9

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a monitoring strategy to detect and alert on unusual access patterns that could indicate a security breach. Which THREE services or features should you use? (Choose three.)

Accepted Answer

Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.. Option A is correct because Microsoft Defender for Storage analyzes data-plane telemetry on the storage account and raises security alerts for suspicious activity such as unusual access patterns, anomalous data exfiltration, or access from unusual locations, which directly addresses breach detection. Option C is correct because Microsoft Sentinel can ingest storage logs and use analytics rules (including built-in anomaly and threat-detection templates) to correlate events and alert on anomalous access patterns across the environment. Option D is correct because enabling diagnostic settings on the storage account is the prerequisite that exports read, write, and delete data-plane logs (the StorageRead/StorageWrite/StorageDelete categories) to a Log Analytics workspace, Event Hub, or storage account, providing the raw telemetry that Sentinel and Defender for Storage rely on for anomaly detection. Option B does not belong because Azure Policy enforces configuration and compliance (for example, requiring encryption or HTTPS), but it does not detect or alert on unusual access patterns. Option E does not belong because Azure Monitor Metrics provides aggregate numeric time-series data such as transaction counts, latency, and availability, not per-request access detail needed to identify anomalous access patterns.

Answer

Apply Azure Policy to enforce encryption and access policies.

Answer

Use Azure Monitor Metrics to track storage account transactions and latency.

Question 10

You are securing an Azure Data Lake Storage Gen2 account that contains sensitive data. Which TWO of the following should you implement to protect data from unauthorized access?

Accepted Answer

Configure ACLs to grant least privilege to users and groups. Option A is correct because Azure Data Lake Storage Gen2 uses POSIX-style access control lists (ACLs) at both the directory and file level, and configuring ACLs to grant least privilege ensures users and groups only receive the specific read/write/execute permissions they require, directly preventing unauthorized access to sensitive data. Option B is correct because private endpoints assign a private IP address from your virtual network to the storage account, removing exposure to the public internet and restricting access to only clients within the approved virtual network or connected networks. Option C is incorrect because setting a default ACL that allows read access to all authenticated users violates least privilege and would broaden, not restrict, access to sensitive data. Option D is incorrect because CORS rules only control which web origins can make cross-origin browser requests to the service; they do not authenticate users or prevent unauthorized direct access. Option E is incorrect because enabling large file shares only increases the maximum capacity and file size limits of the file share, and has no effect on access control or authorization.

Answer

Set the default ACL to allow read access for all authenticated users

Answer

Enable CORS rules to allow only specific origins

Answer

Enable large file shares on the storage account

Question 11

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Accepted Answer

SQL Server Integration Services (SSIS) package. Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion. PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Answer

PolyBase from external table

Question 12

Which THREE options are valid ways to transform data in Azure Synapse Analytics?

Accepted Answer

Use T-SQL scripts in a dedicated SQL pool.. T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Answer

Use Power Query online in Synapse pipelines.

Answer

Use Azure Machine Learning pipelines for data wrangling.

Question 13

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Accepted Answer

Pivot. In Azure Data Factory Mapping Data Flows, the Pivot transformation (A) is a built-in transformation that reshapes data by turning unique row values into columns, which is why it is correct. The Derived Column transformation (B) is also available and is used to create new columns or modify existing ones using expression builder logic, making it correct. The Aggregate transformation (D) is likewise a native Mapping Data Flows transformation that performs group-by operations and aggregations such as SUM, AVG, COUNT, and MIN/MAX, so it is correct. Union All (C) is not a Mapping Data Flows transformation; combining multiple streams is done with the Union transformation, which behaves like a union (not specifically 'Union All' as a named transformation). Merge Join (E) is not a Mapping Data Flows transformation either; joins are performed with the Join transformation, and the term 'Merge Join' refers to a SQL Server/SSIS-style operator rather than an ADF Mapping Data Flows transformation.

Answer

Union All

Answer

Merge Join

Question 14

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Accepted Answer

Amazon S3. Amazon S3 is correct (C) because Azure Data Factory provides a native Amazon S3 connector that the Copy activity can use as a source, reading objects via the S3 API with linked-service credentials. Azure Blob Storage is correct (D) because it is one of ADF's core supported stores, and the Copy activity can read blobs through the Azure Blob Storage connector. Power BI Dataset (A) is not a Copy activity source; ADF interacts with Power BI only for certain dataset-based scenarios, not as a copy source. Azure DevOps (B) is not a data store connector for Copy activity sources. Azure Analysis Services (E) is an analytical model, not a supported Copy activity source store.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 15

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Option A is correct because materialized views in a dedicated SQL pool precompute and persist the results of complex aggregations (such as SUM, COUNT, AVG over large fact tables), letting the optimizer rewrite queries to read the smaller materialized result instead of rescanning and re-aggregating the base tables, which dramatically reduces I/O and CPU. Option C is correct because clustered columnstore indexes are the default and recommended storage format for dedicated SQL pools, providing high compression and batch-mode vectorized execution that speeds up large-scale analytical scans and aggregations on fact tables. Option D is correct because hash distribution on columns frequently used in JOINs co-locates matching rows on the same distribution, enabling collocated joins that avoid costly data movement (shuffle) across the 60 distributions. Option B is not a best practice because assigning the largest resource class to every query consumes excessive memory and concurrency slots, reducing overall workload throughput; resource classes should be sized to each query's needs. Option E is not a best practice because round-robin distribution is suited to staging or temporary tables, whereas large fact tables benefit from hash distribution on a join/filter column to minimize data movement during queries.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 16

Which THREE of the following are best practices for optimizing performance of Delta Lake tables in Azure Synapse Analytics? (Choose three.)

Accepted Answer

Run the OPTIMIZE command to compact small files and improve read performance.. Option A is correct because the OPTIMIZE command compacts many small Parquet files into fewer, larger files, reducing per-file overhead and metadata work so that Delta Lake scans and reads run faster. Option C is correct because partitioning on columns commonly used in WHERE clauses lets the engine perform partition pruning, skipping entire directories of data that cannot match the filter and cutting I/O. Option E is correct because Z-ordering (for example, OPTIMIZE ... ZORDER BY (col)) co-locates related data within files, so data-skipping statistics let queries with frequent filter predicates read far fewer rows. Option B is not a performance optimization: VACUUM deletes unreferenced old files to reclaim storage and control cost, and running it too aggressively can break time travel and concurrent readers. Option D is wrong because partitioning on high-cardinality columns such as UserID creates a huge number of tiny partitions and files, which increases metadata overhead and degrades performance rather than maximizing parallelism.

Answer

Periodically run VACUUM to remove old versions of files that are no longer needed.

Answer

Partition on high-cardinality columns like UserID to maximize parallelism.

Question 17

Which of the following are valid activities in an Azure Data Factory pipeline? (Choose three.)

Accepted Answer

Copy. Option A, Copy, is a valid Azure Data Factory pipeline activity that moves data from a source dataset to a sink dataset, supporting dozens of connectors and optional staging, so it is correct. Option B, Databricks Notebook, is a valid ADF activity that runs an Azure Databricks notebook against a linked Databricks workspace, passing parameters and returning output, so it is correct. Option D, Execute Pipeline, is a valid ADF activity that invokes another pipeline as a child pipeline, enabling modular and reusable orchestration, so it is correct. Option C, Assign, is not an ADF pipeline activity; variable assignment in ADF is done with the Set Variable activity (and Append Variable), while Assign is a control-flow activity in Azure Logic Apps, so it does not belong here.

Answer

Assign

Question 18

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

Align distribution keys on tables that are frequently joined together. Option A is correct because co-locating the distribution key on tables that are frequently joined ensures matching rows land on the same distribution, letting joins execute locally and eliminating costly shuffle (data movement) operations. Option B is correct because a distribution key with many unique values spreads rows evenly across the 60 distributions, minimizing data skew that would otherwise create hot distributions and slow query performance. Option E is correct because table size and join frequency are the core drivers of the distribution decision: small tables may be replicated, while large frequently-joined tables should be hash-distributed on a shared key. Option C is not correct because round-robin is generally recommended for staging or temporary tables, not large fact tables, which benefit from hash distribution on a join/filter column. Option D is not correct because replicated tables are intended for small dimension tables (roughly under 2 GB compressed); replicating large tables consumes excessive storage and rebuild time on every write.

Answer

Use round-robin distribution for large fact tables to distribute data evenly

Answer

Use replicated tables for large tables to avoid data movement

Question 19

You are optimizing the performance of a large-scale batch processing job in Azure Databricks. The job reads data from Azure Data Lake Storage Gen2, performs transformations, and writes results back. You notice that the job is I/O bound. Which THREE strategies can improve performance? (Choose three.)

Accepted Answer

Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.. Option A is correct because Delta Lake with Z-ordering co-locates related data in the same set of files, enabling data-skipping so the I/O-bound job reads far fewer bytes when filtering on those columns. Option B is correct because increasing DataFrame partitions spreads the read/transform workload across more concurrent tasks, raising parallelism and better saturating the storage throughput available to the cluster. Option C is correct because caching the DataFrame in memory (or Delta cache) prevents repeated reads from ADLS Gen2, directly cutting the disk I/O that is the bottleneck. Option D is not correct because reducing shuffle partitions lowers parallelism for wide transformations and typically increases per-task data volume, which does not relieve I/O-bound reads. Option E is not correct because autoscaling adds compute nodes but does not by itself reduce the I/O volume or improve data layout, so it is not a targeted fix for an I/O-bound workload.

Answer

Reduce the number of shuffle partitions to minimize data movement.

Answer

Enable autoscaling on the cluster to add more nodes during processing.

Question 20

Which THREE metrics should you monitor for an Azure Synapse Analytics dedicated SQL pool to ensure optimal performance?

Accepted Answer

tempdb usage. Monitoring tempdb usage (A) is essential because dedicated SQL pool queries spill to tempdb for sorts, hash joins, and other operations, and high tempdb utilization can indicate insufficient memory or poorly designed queries that degrade performance. DWU usage (B) is a key metric because it reflects how much of the provisioned Data Warehouse Unit capacity is being consumed; consistently high DWU usage signals that the pool may need scaling or workload tuning. Queued queries (C) should be monitored because a growing queue indicates that concurrency limits or resource contention are delaying query execution, directly affecting performance. The unmarked options do not belong: login failures (D) is a security/connectivity metric rather than a performance indicator, and total storage size (E) relates to capacity planning and cost, not to query performance optimization.

Answer

Login failures

Answer

Total storage size