DP-203 Hard Questions — 25 Difficult Questions

Question 1

A multinational corporation uses Azure Data Lake Storage Gen2 to store petabytes of parquet files partitioned by date and hour. Data scientists report that queries on the last 7 days of data take over 30 minutes, while queries on older data are fast. The storage account uses the default Azure Blob Storage hierarchical namespace. Which action will MOST improve query performance on recent data?

Accepted Answer

Optimize the partition layout by partitioning by date first, then by hour, to reduce the number of partitions scanned for recent data. Option C is correct because partitioning by date first, then by hour, ensures that queries filtering on the last 7 days scan only the relevant date partitions, drastically reducing the amount of data read. In Azure Data Lake Storage Gen2, the hierarchical namespace allows partition pruning at the directory level, so a date-first layout minimizes the number of partitions scanned for recent data, directly addressing the performance bottleneck.

Answer

Convert the parquet files to CSV format to reduce metadata overhead

Answer

Enable soft delete on the storage account to reduce read latency

Answer

Apply Z-order clustering on the parquet files using Azure Databricks

Question 2

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. Option B is correct because 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 3

You are designing a data lake on Azure Data Lake Storage Gen2. The data will be used by both batch processing (Spark) and interactive querying (Azure Synapse Serverless SQL). The data is partitioned by date and stored as Parquet. What is the optimal folder structure to minimize cross-partition scans for both workloads?

Accepted Answer

/year/month/day/ (e.g., /2023/12/25/). Option B (/year/month/day/) is optimal because it aligns with Hive-style partitioning, which both Spark and Azure Synapse Serverless SQL can leverage for partition pruning. Spark uses partition discovery to read only relevant directories, and Synapse Serverless SQL uses the file path metadata to filter partitions, minimizing cross-partition scans and reducing data read overhead.

Answer

All files in a single folder

Answer

/yyyy-mm-dd/ (e.g., /2023-12-25/)

Answer

Files named by date (e.g., data_20231225.parquet)

Question 4

A company uses Azure Data Factory to copy sensitive data from on-premises SQL Server to Azure Blob Storage. They must ensure that data is encrypted in transit and at rest. Which combination of features should they use?

Accepted Answer

Configure the copy activity to use TLS and enable Azure Storage Service Encryption.. Option C is correct because Azure Data Factory's copy activity uses TLS (Transport Layer Security) to encrypt data in transit between the on-premises SQL Server and Azure Blob Storage, and Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption. This combination satisfies both encryption requirements without additional complexity.

Answer

Use Always Encrypted in SQL Server and customer-managed keys in Blob Storage.

Answer

Set up a VPN between on-premises and Azure, and use Azure Disk Encryption.

Answer

Use HTTPS for the copy activity and enable Azure Storage Service Encryption.

Question 5

Which THREE of the following are required to implement column-level security in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

A GRANT statement on specific columns to users or roles. Option A is correct because column-level security in Azure Synapse Analytics dedicated SQL pool is implemented using GRANT statements on specific columns. By granting SELECT on only certain columns to a user or role, you restrict access to sensitive data at the column level without needing to create views or modify schemas. This is the native mechanism provided by SQL Server and Azure Synapse for column-level security.

Answer

A VIEW that selects only the allowed columns

Answer

A DENY statement on specific columns to users or roles

Answer

A row-level security policy must be in place

Question 6

Which THREE factors should you consider when choosing between rowstore and columnstore indexes in Azure Synapse Analytics?

Accepted Answer

The table size is expected to be over 1 TB.. Option C is correct because columnstore indexes in Azure Synapse Analytics are optimized for large-scale data warehousing workloads, where table sizes exceeding 1 TB benefit from high compression and columnar storage, significantly improving scan and aggregation performance. Rowstore indexes, in contrast, are less efficient for such large datasets due to higher I/O and storage overhead.

Answer

The table contains many NULL values in indexed columns.

Answer

The table will be partitioned frequently.

Question 7

A company has an Azure Data Lake Storage Gen2 account. They want to ensure that only users with the 'Data Reader' role can access files in a specific container, while other users cannot list or read files. The storage account has hierarchical namespace enabled. What is the most secure and manageable approach?

Accepted Answer

Set POSIX-like access control lists (ACLs) on the container folder for the Data Reader role. Option D is correct because Azure Data Lake Storage Gen2 with hierarchical namespace enabled supports POSIX-like access control lists (ACLs) at the container and folder level. By setting ACLs on the specific container folder to grant 'Read' and 'Execute' permissions only to the 'Data Reader' role (or its associated security group), you enforce least-privilege access without affecting other containers. This approach is both secure and manageable, as ACLs are inherited by default and can be centrally managed via Azure RBAC integration.

Answer

Assign the Storage Blob Data Reader role at the storage account level and use row-level security

Answer

Generate a shared access signature (SAS) token for each user

Answer

Configure a storage firewall to allow only the Data Reader role's IP addresses

Question 8

Which THREE components are part of a defense-in-depth strategy for data security in Azure?

Accepted Answer

Network security groups (NSGs) on subnets. Network security groups (NSGs) are a fundamental component of a defense-in-depth strategy because they provide network-layer segmentation and filtering. By applying NSGs to subnets, you can control inbound and outbound traffic based on source/destination IP addresses, ports, and protocols, creating a perimeter defense that limits lateral movement in case of a breach.

Answer

Azure Policy to enforce tagging

Answer

Dynamic data masking for all databases

Question 9

A company is using Azure Data Factory to copy data from an on-premises SQL Server to Azure Blob Storage. The data must be encrypted in transit using TLS 1.2. The on-premises SQL Server is configured to support TLS 1.2. Which Data Factory property should be configured?

Accepted Answer

The typeProperties property in the linked service to include 'Encrypt=True' in the connection string. Option B is correct because TLS 1.2 encryption for data in transit between Azure Data Factory and an on-premises SQL Server is enforced by adding 'Encrypt=True' and 'TrustServerCertificate=False' to the connection string within the typeProperties of the linked service. This ensures the SQL Server Native Client uses TLS 1.2 for the connection, aligning with the requirement.

Answer

The encryptedCredential property in the linked service

Answer

The connectVia property in the linked service

Answer

The integrationRuntime property in the dataset

Question 10

A data engineer is monitoring Azure Data Lake Storage Gen2 costs and notices high transaction costs for a specific container. The container stores Parquet files used by Azure Databricks for read-heavy analytics. The files are accessed frequently by multiple jobs. What is the most cost-effective way to reduce transaction costs?

Accepted Answer

Enable Azure CDN to cache the files.. Option D is correct because enabling Azure CDN caches the frequently accessed Parquet files at edge locations, reducing the number of direct read requests to Azure Data Lake Storage Gen2. This lowers transaction costs (both read and list operations) while maintaining low-latency access for read-heavy analytics workloads. The CDN serves cached content, so the storage account incurs fewer billable transactions.

Answer

Move the data to Azure Blob Storage cool tier.

Answer

Increase the Parquet file size to maximize block size.

Answer

Convert the container to Azure Files.

Question 11

A data engineer is optimizing an Azure Data Lake Storage Gen2 account used for big data analytics. The account contains billions of small files (under 1 MB). The analytics jobs are slow and cost more than expected. Which THREE actions should the engineer take to improve performance and reduce costs?

Accepted Answer

Convert data to columnar file formats such as Parquet.. Option A is correct because converting data to columnar formats like Parquet reduces the amount of data read during analytics queries, as only the necessary columns are scanned. This significantly improves query performance and lowers I/O costs, especially for big data workloads on Azure Data Lake Storage Gen2.

Answer

Move data to the cool tier to reduce storage costs.

Answer

Enable soft delete to protect against accidental deletion.

Question 12

You are monitoring an Azure Data Lake Storage Gen2 account that stores streaming data from IoT devices. You notice that query performance on the data in Parquet format is degrading over time. You need to improve query performance for both current and future data. Which TWO actions should you take?

Accepted Answer

Partition the data by a column commonly used in filter conditions.. Partitioning the data by a column commonly used in filter conditions (e.g., date, device ID) enables predicate pushdown in query engines like Azure Synapse or Spark, allowing them to skip irrelevant partitions and scan only the necessary files. This directly addresses the performance degradation by reducing the amount of data read during queries, and it benefits both current and future data when applied consistently.

Answer

Move frequently accessed data to Azure SQL Database.

Answer

Enable soft delete on the storage account to optimize read performance.

Answer

Migrate the data to Azure NetApp Files for lower latency.

Question 13

You are designing a stream processing solution using Azure Stream Analytics. The job must reference a static lookup table (product catalog) stored in Azure Blob Storage. The catalog is updated once daily. The job should automatically pick up the latest version without restarting. Which two configurations are required? (Choose two.)

Accepted Answer

Set the reference input's 'Path pattern' to include date and time placeholders. Option B is correct because Azure Stream Analytics reference data inputs support path pattern placeholders like {date} and {time} to dynamically resolve the latest blob file. This allows the job to automatically load a new version of the static lookup table when the blob is updated, without requiring a job restart. The path pattern must be structured to match the naming convention of the uploaded file, such as 'catalog/{date}/{time}/products.csv'.

Answer

Configure the reference input with a static blob path

Answer

Use Azure Event Grid to trigger job restart on blob update

Answer

Store the reference data in Azure SQL Database instead of Blob Storage

Question 14

Which THREE factors should you consider when designing a monitoring strategy for Azure Synapse Analytics dedicated SQL pool performance?

Accepted Answer

Use dynamic management views (DMVs) to identify long-running queries.. Option A is correct because dynamic management views (DMVs) in Azure Synapse Analytics dedicated SQL pool, such as sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps, provide real-time insight into query execution, allowing you to identify long-running queries, monitor resource consumption, and detect performance bottlenecks. This is a foundational monitoring practice for tuning workload performance.

Answer

Ensure data is evenly distributed across distributions.

Answer

Configure automatic index rebuild for columnstore indexes.

Question 15

You are reviewing an Azure Policy assignment that uses the above JSON to define a role-based access control (RBAC) action. What is the primary purpose of this policy?

Accepted Answer

To authorize generation of a shared access signature (SAS) token for the storage account.. The policy JSON defines a role-based access control (RBAC) action that grants the 'Microsoft.Storage/storageAccounts/listAccountSas/action' permission. This specific action authorizes the generation of a shared access signature (SAS) token at the storage account level, not at the container or blob level. Therefore, the primary purpose is to allow the generation of an account SAS token, which provides delegated access to storage services.

Answer

To assign RBAC roles to users for the storage account.

Answer

To enable delegation of access to a specific blob.

Answer

To allow users to set permissions on storage account containers.

Question 16

A company uses Azure Databricks to process streaming data from Event Hubs. The data is written to a Delta table. The job occasionally fails due to checkpoint corruption. Which THREE measures should you implement to improve reliability?

Accepted Answer

Configure checkpointing to a durable storage like Azure Data Lake Storage.. Option A is correct because checkpointing to durable storage like Azure Data Lake Storage (ADLS) ensures that streaming progress metadata is persisted across cluster restarts and failures. ADLS provides high durability and availability, preventing checkpoint corruption that can occur with local or ephemeral storage, thereby enabling exactly-once processing guarantees in Structured Streaming.

Answer

Increase the batch interval to reduce load.

Answer

Increase the cluster size to handle spikes.

Question 17

Refer to the exhibit. An Azure Data Factory instance uses a self-hosted integration runtime. The exhibit shows the properties of the integration runtime. The data engineer notices that copy activities are failing with errors indicating that the integration runtime is not available. What is the most likely cause?

Accepted Answer

The integration runtime version is outdated and needs to be manually updated. Option C is correct because the exhibit shows the integration runtime version as '5.24.8345.1' and the status as 'Online', but copy activities are failing. The most likely cause is that the self-hosted IR version is outdated and no longer compatible with the Azure Data Factory service endpoints, leading to connectivity failures. Auto-update being disabled (Option B) would prevent automatic updates, but the core issue is the outdated version itself, which requires manual intervention to update.

Answer

The integration runtime status is "Offline"

Answer

Auto-update is disabled, preventing the IR from updating

Answer

Self-contained interactive authoring is disabled, causing connectivity issues

Question 18

A company uses Azure Synapse Analytics with a dedicated SQL pool. Data engineers need to implement column-level security so that only users with the 'Manager' role can see salary columns. Which TWO actions should they take?

Accepted Answer

Create a security policy with a filter predicate on the salary column using the function, and set the state to ON with BLOCK predicate.. Option C is correct because column-level security in Azure Synapse Analytics dedicated SQL pools is implemented via a security policy that uses a filter predicate (a function) to restrict access to specific columns. Setting the state to ON with a BLOCK predicate ensures that unauthorized users cannot see the salary column. Option D is correct because the inline table-valued function is the predicate function that checks role membership (e.g., using IS_MEMBER('Manager')) and returns 1 or 0, which the security policy uses to filter rows.

Answer

Create a stored procedure that checks the user's role and returns the appropriate columns.

Answer

Grant the 'Manager' role SELECT permission on the security policy.

Answer

Use GRANT SELECT ON OBJECT::[dbo].[Employee](Salary) TO [Manager] to grant access to the salary column.

Question 19

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.. Option A is correct because 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 20

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. Option B is correct because 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