DP-203 Design and implement data storage Practice Test 10 — 15 Questions

Question 1

Which TWO actions should be taken to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Configure firewall and virtual network rules. Configuring firewall and virtual network rules restricts network-level access to the storage account, preventing unauthorized traffic from reaching the data at rest. Option C is correct because enabling customer-managed keys (CMK) in Azure Key Vault provides an additional encryption layer for data at rest, allowing you to control and rotate encryption keys independently of Azure-managed keys.

Answer

Enable soft delete for blobs

Answer

Assign storage blob data contributor roles to users

Answer

Use client-side encryption with Azure Key Vault

Question 2

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

Accepted Answer

Denies storage accounts that allow public network access from all networks. The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Answer

Denies storage accounts that do not have any IP rules defined

Answer

Denies storage accounts that have firewall rules configured

Answer

Denies storage accounts that have public network access disabled

Question 3

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Serverless SQL. Which statement about this table is correct?

Accepted Answer

The table is read-only. External tables in Azure Synapse Serverless SQL are read-only because they provide a relational abstraction over data stored externally (e.g., in Azure Data Lake Storage Gen2 or Blob Storage). You cannot perform INSERT, UPDATE, DELETE, or DDL modifications on the underlying data through the external table; it is designed solely for querying with T-SQL. This is a fundamental constraint of the serverless SQL pool architecture, which uses the OPENROWSET or CREATE EXTERNAL TABLE syntax to read files in place without a storage engine.

Answer

The table supports indexing for performance

Answer

The external data source TYPE must be 'HADOOP' for Azure Data Lake Storage Gen2

Answer

The table references a single Parquet file named 'Sales.parquet'

Question 4

Refer to the exhibit. A Bicep file is used to deploy an Azure Synapse Analytics workspace. What is the purpose of the 'purviewConfiguration' property?

Accepted Answer

It links the workspace to a Microsoft Purview account for data lineage and cataloging. The 'purviewConfiguration' property in a Bicep file for Azure Synapse Analytics links the workspace to a Microsoft Purview account. This integration enables automated data lineage tracking, cataloging, and discovery across the Synapse environment, allowing users to search for and govern data assets directly from Purview. Without this property, the Synapse workspace operates independently of Purview's unified data governance capabilities.

Answer

It configures automated backups of the Synapse workspace

Answer

It enables monitoring of data movement by Azure Monitor

Answer

It connects the workspace to a data catalog for pipeline sources

Question 5

A data engineer needs to store log data from multiple applications in Azure. The data is append-only, heavily compressed, and queried infrequently. Cost minimization is critical. Which storage solution is best?

Accepted Answer

Azure Blob Storage with cool or archive access tier. Azure Blob Storage with cool or archive access tier is the best choice because the data is append-only, heavily compressed, and infrequently queried, making cost minimization the top priority. The cool tier offers low storage costs with higher access charges, while the archive tier provides the lowest storage cost for data that is rarely accessed and can tolerate hours of retrieval latency. This aligns perfectly with the append-only, infrequently queried nature of the log data.

Answer

Azure Table Storage

Answer

Azure Cosmos DB with analytical store

Answer

Azure Data Lake Storage Gen2 with hot tier

Question 6

A financial services company is migrating its data warehouse to Azure Synapse Analytics. They have a star schema with a 10-billion-row fact table and 50 dimension tables. Query performance is critical, and they need to minimize data movement during joins. Which distribution strategy should they use for the fact table?

Accepted Answer

Hash distribution on the most frequently joined dimension key. Hash distribution on the most frequently joined dimension key is correct because it co-locates matching rows from the fact and dimension tables on the same compute node, minimizing data movement during joins. For a 10-billion-row fact table, this distribution ensures that the most common join operation is performed locally without shuffling data across nodes, which is critical for query performance in Azure Synapse Analytics.

Answer

Replicated distribution

Answer

Partitioned distribution

Answer

Round-robin distribution

Question 7

A data engineering team uses Azure Data Factory to load data from Azure SQL Database to Azure Data Lake Storage Gen2. They notice that the pipeline runs fail intermittently due to transient errors. They need to implement a retry policy with exponential backoff. What is the most efficient way to achieve this?

Accepted Answer

Configure the 'Retry' property on the copy activity with a count and exponential backoff interval. Azure Data Factory natively supports configuring a 'Retry' property on activities, including Copy activities, with an exponential backoff interval. This built-in mechanism automatically retries the activity upon transient failures without requiring custom logic, making it the most efficient and maintainable approach for handling intermittent errors.

Answer

Use a 'Validation' activity before the copy to check source availability

Answer

Create a custom .NET activity to handle retries

Answer

Add a 'Until' loop with a wait activity in the pipeline

Question 8

Which THREE factors should be considered when choosing between Azure Synapse Dedicated SQL Pool and Azure SQL Database for a data warehouse workload?

Accepted Answer

Concurrency and workload management. Azure Synapse Dedicated SQL Pool provides built-in workload management features like workload classification, importance, and concurrency slots, which are essential for managing mixed analytical workloads with varying resource demands. Azure SQL Database, while supporting resource governance, lacks the granular workload management capabilities needed for complex data warehouse scenarios with concurrent queries.

Answer

Need for geo-replication

Answer

Integration with Azure Data Lake Storage

Question 9

Which TWO strategies can be used to optimize storage costs for historical data in Azure Data Lake Storage Gen2?

Accepted Answer

Implement lifecycle management policies to move data to archive tier. Azure Blob Storage lifecycle management policies allow you to automatically transition data from hot to cool to archive tiers based on age or last modification time. Moving historical data to the archive tier significantly reduces storage costs, as archive is the lowest-cost storage tier, though it incurs higher retrieval latency and costs.

Answer

Enable soft delete to recover data

Answer

Use geo-redundant storage (GRS) for durability

Answer

Encrypt data with Azure Storage Service Encryption

Question 10

Refer to the exhibit. An ARM template deploys an Azure Synapse Analytics workspace. What is the purpose of the 'managedVirtualNetwork' property set to 'default'?

Accepted Answer

It enables the workspace to use a managed virtual network for network isolation. Setting 'managedVirtualNetwork' to 'default' in an ARM template for Azure Synapse Analytics enables a managed virtual network that provides network isolation for the workspace. This allows the workspace to use private endpoints and managed private endpoints for secure data integration without exposing traffic to the public internet. It is a foundational setting for implementing a secure, network-isolated Synapse environment.

Answer

It configures the workspace to use a user-assigned managed identity

Answer

It disables public network access to the workspace

Answer

It creates the workspace in a private endpoint configuration

Question 11

A company uses Azure Synapse Analytics dedicated SQL pool. They need to load data from Azure Data Lake Storage Gen2 (ADLS Gen2) incrementally. Which PolyBase external table configuration supports incremental loading without reprocessing historical data?

Accepted Answer

Create an external table partitioned by date column with a partition location pattern.. Partitioning an external table by a date column with a partition location pattern allows PolyBase to perform partition elimination during queries. This means only the partitions containing new or modified data are read from ADLS Gen2, enabling incremental loading without scanning historical data.

Answer

Create an external table with a clustered index on the date column.

Answer

Create an external table on ORC files with a clustered columnstore index.

Answer

Create an external table with round-robin distribution on the date column.

Question 12

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

Accepted Answer

Azure Data Explorer (ADX).. Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and log analytics, ingesting 10,000+ events per second with sub-second query latency via its columnar storage and distributed query engine. It supports real-time analytics on streaming IoT data without requiring pre-defined schemas or indexing, making it the optimal choice for this scenario.

Answer

Azure Table Storage.

Answer

Azure SQL Database with in-memory OLTP.

Answer

Azure Cosmos DB with analytical store.

Question 13

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

Accepted Answer

Create external tables with statistics on relevant columns.. In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Answer

Create clustered columnstore indexes on the external tables.

Answer

Convert the Parquet files to CSV format for faster reads.

Answer

Partition the data into many small files.

Question 14

You are migrating an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The database includes a table with 500 million rows that is frequently queried by date range. Which distribution strategy should you use for this table?

Accepted Answer

Hash distribution on the date column.. Hash distribution on the date column is correct because it distributes rows evenly across distributions based on a hash of the date value, enabling partition elimination and collocated joins when queries filter by date range. This strategy optimizes performance for the frequent date-range queries by ensuring that data for a specific date range is likely stored on the same distribution, minimizing data movement.

Answer

Hash distribution on an identity column.

Answer

Replicated distribution.

Answer

Round-robin distribution.

Question 15

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

Accepted Answer

Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.. Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Answer

Azure Data Factory mapping data flows.

Answer

Azure Cosmos DB Core (SQL) API.

Answer

Azure SQL Database with JSON columns.