DP-203 Practice Test 19 — 15 Questions

Question 1

You execute the above T-SQL in a serverless SQL pool in Azure Synapse Analytics. The external table creation succeeds, but when you query the table, it returns zero rows. The folder 'sales/products/' exists in the container and contains multiple .parquet files. What is the most likely cause?

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

Accepted Answer

The LOCATION path in the external table does not match the actual file path.. The external table's LOCATION parameter specifies a path relative to the external data source's root. Even though the folder 'sales/products/' exists, the LOCATION must exactly match the subfolder path within the container. A mismatch (e.g., missing a trailing slash, case sensitivity, or an extra prefix) causes the serverless SQL pool to scan no files, returning zero rows.

Answer

The file format is incorrect; should be DELIMITEDTEXT instead of PARQUET.

Answer

The external data source location uses the wrong endpoint; should use .blob.core.windows.net instead.

Answer

The credential used in the external data source does not have read permissions.

Question 2

A company uses Azure Data Lake Storage Gen2 to store sensor data. They notice that queries on the data are slow. Which feature should they enable to optimize query performance without moving data?

Accepted Answer

Enable hierarchical namespace on the storage account.. Enabling hierarchical namespace on Azure Data Lake Storage Gen2 organizes blobs into a directory hierarchy, which allows query engines like Azure Synapse Analytics and Apache Spark to perform directory-level pruning and partition elimination. This reduces the amount of data scanned during queries, directly improving performance without requiring data movement or restructuring.

Answer

Implement Change Data Capture (CDC).

Answer

Enable Azure Search on the storage account.

Answer

Use PolyBase to query the data.

Question 3

Which TWO actions should you take to ensure that only authorized users can access sensitive data in an Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Implement row-level security. Row-level security (RLS) and column-level security are the two correct actions because they directly restrict data access at the row and column granularity within a dedicated SQL pool. RLS uses security predicates to filter which rows a user can query, while column-level security denies access to specific columns for unauthorized principals. Both are native features of Azure Synapse dedicated SQL pools that enforce authorization on the data plane.

Answer

Configure Azure Active Directory authentication

Answer

Enable dynamic data masking on all columns

Answer

Enable transparent data encryption

Question 4

You are analyzing the exhibit from an Azure Monitor metric query for a storage account. What is the primary purpose of this query?

Accepted Answer

To retrieve the average count of block blobs per hour.. The query uses the 'avg' aggregation on the 'BlobCount' metric, which calculates the average number of blobs over the specified time granularity (e.g., per hour). The result shows the average count of block blobs per hour, not the total count or the count in a specific tier. This aligns with option D, as the query is designed to retrieve the average count of block blobs per hour.

Answer

To calculate the average number of block blobs in the hot tier.

Answer

To identify the time period with the highest blob count.

Answer

To measure the total size of all block blobs in the account.

Question 5

Which THREE factors should be considered when choosing between Azure Stream Analytics and Azure Databricks for a real-time data processing solution?

Accepted Answer

Need for complex transformations and machine learning model integration. Azure Databricks provides native support for complex transformations (e.g., windowed aggregations, multi-step ETL) and seamless integration with machine learning libraries (e.g., MLflow, Spark MLlib), which are not natively available in Azure Stream Analytics. Stream Analytics uses a SQL-like query language and is optimized for simpler, declarative transformations, making Databricks the better choice when advanced analytics or ML model scoring is required in real-time pipelines.

Answer

Integration with Power BI for real-time dashboards

Answer

Maximum allowed latency for late-arriving data

Question 6

Refer to the exhibit. A custom RBAC role is defined as shown. A user is assigned this role at the resource group scope. Which operation can the user perform?

Accepted Answer

List containers in a storage account within DataRG. The custom RBAC role includes the 'Microsoft.Storage/storageAccounts/blobServices/containers/read' action, which allows listing containers. Since the user is assigned this role at the resource group scope (DataRG), they can list containers in any storage account within that resource group. The role does not include any data plane actions (e.g., read/write/delete blob data) or container deletion permissions, so only the list operation is permitted.

Answer

Delete containers

Answer

Write blob data to containers

Answer

Read blob data from containers

Question 7

You are designing a data processing solution for a retail company. The solution must ingest streaming sales data from point-of-sale (POS) systems and batch uploads from stores that are offline. The total data volume is 5 TB daily. The solution must allow real-time dashboards and periodic batch processing. Which combination of services and ingestion patterns is most cost-effective and scalable?

Accepted Answer

Use Azure Event Hubs with Kafka protocol for all incoming data. Use Stream Analytics for real-time dashboards and Event Hubs Capture to land data in ADLS for batch processing. Azure Event Hubs with Kafka protocol provides a unified ingestion endpoint for both streaming POS data and batch offline uploads, eliminating the need for separate services. Stream Analytics enables real-time dashboards, while Event Hubs Capture automatically lands data into Azure Data Lake Storage for cost-effective batch processing, making this the most scalable and cost-effective solution for 5 TB daily.

Answer

Use Azure IoT Hub for POS streaming and Azure Blob Storage for offline store uploads, then process with Stream Analytics and Data Factory

Answer

Use Azure Data Lake Storage for all data, then use Azure Databricks structured streaming for real-time and batch

Answer

Use Azure Stream Analytics directly on POS data and store offline uploads in Blob Storage, then batch process with U-SQL

Question 8

Refer to the exhibit. A data engineer notices that the target SQL table contains duplicate rows after a pipeline run. Which change to the pipeline configuration would prevent duplicates?

Accepted Answer

Add a 'Upsert' setting in SqlSink with a key column. The preCopyScript truncates the table before copy, but if the pipeline is run multiple times and truncation fails or is skipped, duplicates can occur. Using upsert semantics or adding a watermark could help, but the simplest fix is to ensure truncation is reliable. However, the best practice is to use a merge/upsert pattern. Among the options, adding a surrogate key and using upsert is most effective.

Answer

Remove the 'preCopyScript'

Answer

Change 'writeBatchSize' to 5000

Answer

Set 'recursive' to false

Question 9

Match each performance optimization technique to its description.

Accepted Answer

Columnstore Indexes: Improves query performance by storing data in columnar format, allowing better compression and faster scanning.. Correct matches: Columnstore indexes improve scan performance via columnar storage; partition switching enables fast data segment movement; materialized views pre-compute aggregations; result set caching caches query results. Common confusions include swapping partition switching with columnstore indexes or materialized views with result set caching.

Answer

Columnstore Indexes: Enables fast loading and removal of large data segments.

Answer

Materialized Views: Stores query results in cache to reuse for repeated queries.

Question 10

Which TWO actions help optimize data storage costs in Azure Data Lake Storage Gen2?

Accepted Answer

Configure lifecycle management policies to move data to cool or archive tiers.. Azure Data Lake Storage Gen2 supports lifecycle management policies that automatically transition data to cooler tiers (cool or archive) based on age or usage patterns. Moving infrequently accessed data to lower-cost tiers directly reduces storage costs without manual intervention.

Answer

Enable soft delete for blobs.

Answer

Enable geo-redundant storage (GRS) for the storage account.

Answer

Enable encryption at rest using customer-managed keys.

Question 11

You are designing data security for an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to ensure that only authorized users can access the data and that access can be audited. Which TWO actions should you implement?

Accepted Answer

Configure diagnostic settings to send logs to a Log Analytics workspace.. Configuring diagnostic settings to send logs to a Log Analytics workspace enables auditing of data access events, such as read, write, and delete operations, which is essential for compliance and security monitoring. Option E is correct because Azure AD authentication provides fine-grained access control using role-based access control (RBAC) and eliminates the need for shared keys, aligning with the principle of least privilege.

Answer

Use storage account access keys (Shared Key) for authentication.

Answer

Generate shared access signatures (SAS) with a long expiry time.

Answer

Configure a firewall to allow access from all Azure services.

Question 12

A company stores sensitive customer data in Azure Data Lake Storage Gen2. They need to implement a data retention policy where data older than 90 days is automatically moved to the 'cold' access tier, and data older than 365 days is deleted. Which Azure feature should be used to automate this?

Accepted Answer

Blob Storage lifecycle management. Azure Blob Storage lifecycle management is the correct feature because it allows you to define rules that automatically transition blobs to cooler access tiers (like the 'cold' tier) after a specified number of days and delete them after a further period. This directly meets the requirement to move data older than 90 days to the cold tier and delete data older than 365 days, all without custom code or manual intervention.

Answer

Azure Automation

Answer

Azure Data Factory

Answer

Azure Policy

Question 13

Match each data storage format to its characteristic.

Accepted Answer

Parquet: Columnar storage format, efficient for analytics and compression. In Azure data engineering, Parquet and ORC are columnar formats ideal for analytics, while Avro is row-based with embedded schema, and JSON is a semi-structured text format. Common confusions involve mixing columnar and row-oriented characteristics.

Answer

JSON: Columnar storage optimized for compression

Answer

ORC: Row-oriented format with schema in each file

Question 14

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a big data streaming platform and event ingestion service that integrates with Synapse Analytics for streaming data. Option A is incorrect because Azure Data Factory is primarily for batch data integration, not real-time streaming. Option B is incorrect because Azure Blob Storage is a storage service, not an ingestion service. Option D is incorrect because Azure Analysis Services is for semantic modeling, not streaming ingestion.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Analysis Services

Question 15

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

Accepted Answer

Run OPTIMIZE to compact small files.. The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Answer

Run CONVERT TO DELTA on the underlying Parquet files.

Answer

Run DESCRIBE HISTORY to analyze file sizes.

Answer

Run VACUUM to delete old files.