DP-203 Timed Practice Test 2 — 10 Questions

Question 1

Which TWO of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Use customer-managed keys in Azure Key Vault. Option C is correct because using customer-managed keys (CMK) in Azure Key Vault allows you to control and rotate the encryption keys used for Azure Storage Service Encryption (SSE), providing an additional layer of security for data at rest. This is a valid method to secure data at rest in Azure Data Lake Storage Gen2, as it ensures that only authorized parties with access to the key vault can decrypt the data.

Answer

Assign RBAC roles for data access

Answer

Configure storage firewall rules

Answer

Enable TLS 1.2 for all connections

Question 2

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 3

A company uses Azure Synapse Analytics with dedicated SQL pools. They notice that query performance degrades significantly during peak hours. They have already scaled up the Data Warehouse Units (DWU) to the maximum. Which action should they take next to improve performance?

Accepted Answer

Enable result-set caching.. When a dedicated SQL pool is already at maximum DWU, further scaling is not possible. Enabling result-set caching stores query results in the SSD-based cache of the SQL pool, allowing repeated queries to be served directly from cache without re-scanning data or re-computing aggregations. This reduces I/O and CPU pressure during peak hours, improving performance for recurring queries without requiring additional compute resources.

Answer

Rebuild all clustered columnstore indexes.

Answer

Increase the number of concurrency slots.

Answer

Move the data to Azure Data Lake Storage Gen2.

Question 4

You need to configure encryption for an Azure SQL Database to protect data at rest. Which Azure service or feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) is the correct choice because it performs real-time I/O encryption and decryption of the data and log files at rest, protecting against unauthorized access to the physical storage media. TDE uses an AES-256 encryption algorithm and is fully transparent to the application, requiring no changes to the database schema or queries.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 5

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 6

You are designing a data pipeline that ingests JSON files from Azure Blob Storage into Azure Synapse Analytics using PolyBase. The files contain nested JSON arrays. What should you do to ensure that the data is loaded correctly?

Accepted Answer

Flatten the JSON arrays into a tabular format using Azure Data Factory or Databricks before loading.. Option A is correct because PolyBase in Azure Synapse Analytics cannot directly handle nested JSON arrays; it requires a flat, tabular structure. Azure Data Factory or Databricks can flatten the nested arrays into rows and columns before loading, ensuring compatibility with PolyBase's external table format.

Answer

Create an external table with the JSON file type and use a schema definition.

Answer

Use the OPENJSON function in T-SQL to parse the JSON during the load.

Answer

Use PolyBase with a JSON format file specifying the schema.

Question 7

You are a data engineer for a financial services company. You have an Azure Data Lake Storage Gen2 account containing historical trade data organized by date in the format 'yyyy/MM/dd'. Each day's data is stored as a collection of Parquet files. The data is used by a team of analysts who run ad-hoc queries using Azure Synapse Serverless SQL. Recently, the analysts have reported that queries scanning multiple months of data are slow. The storage account uses LRS with a general-purpose v2 tier. You have enabled hierarchical namespace. The data is not partitioned in any other way. You need to improve query performance without moving data or changing the storage tier. What should you do?

Accepted Answer

Create external tables with partition definition using the directory structure and ensure queries filter on the date column.. Option A is correct because Azure Synapse Serverless SQL can leverage the directory structure of Azure Data Lake Storage Gen2 as virtual partitions. By creating external tables with a partition definition that maps to the 'yyyy/MM/dd' folder hierarchy and ensuring queries filter on the date column, the serverless SQL engine performs partition elimination. This reduces the amount of data scanned, directly addressing the slow performance when querying multiple months of data without moving data or changing the storage tier.

Answer

Increase the query timeout setting in Azure Synapse Studio.

Answer

Redistribute the data using hash distribution on the date column.

Answer

Increase the data warehouse units (DWU) for the serverless SQL endpoint.

Question 8

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 9

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 10

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