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

Question 1

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 2

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 3

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 4

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 5

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

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

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

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 9

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

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 11

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 12

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 13

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.

Question 14

You are designing a data lake architecture for a healthcare company. The solution must support fine-grained access control at the file level, encryption at rest and in transit, and integration with Microsoft Purview for data lineage. Which storage solution should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2 (ADLS Gen2).. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with POSIX-like access control lists (ACLs) for fine-grained file-level permissions, supports encryption at rest (Azure Storage Service Encryption) and in transit (TLS 1.2+), and natively integrates with Microsoft Purview for automated data lineage and cataloging. This makes it ideal for healthcare scenarios requiring strict compliance and auditability.

Answer

Azure NetApp Files.

Answer

Azure Files.

Answer

Azure Blob Storage.

Question 15

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that some queries are slow due to high data movement. What should you do to minimize data movement for queries that join large fact tables?

Accepted Answer

Hash-distribute the fact tables on the join keys.. Hash-distributing the fact tables on the join keys ensures that rows with the same join key value are placed on the same distribution node. This eliminates the need to shuffle data across nodes during the join, minimizing data movement and improving query performance in Azure Synapse dedicated SQL pool.

Answer

Use round-robin distribution for all tables.

Answer

Partition both tables on the join keys.

Answer

Use replicated tables for all large fact tables.

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

Exhibit

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

Exhibit