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

Question 1

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

Accepted Answer

Firewall and virtual network service endpoints. Option D is correct because Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Answer

Azure Private Endpoint

Answer

Shared access signature (SAS)

Answer

Azure Defender for Storage

Question 2

A data engineer is designing a solution that uses Azure Data Factory to copy data from an on-premises SQL Server to Azure Synapse Analytics. The data transfer must be encrypted in transit. Which property should be configured in the linked service?

Accepted Answer

EncryptedConnection. Option C is correct because the EncryptedConnection property in an Azure Data Factory linked service enforces encryption for data in transit between the on-premises SQL Server and Azure Synapse Analytics. When set to true, it uses TLS/SSL to encrypt the connection, ensuring that data transferred over the network is protected from interception or tampering.

Answer

ConnectionString with Integrated Security

Answer

AuthenticateVia

Answer

UseSystemTrustStore

Question 3

Which TWO are valid methods to load data into Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

PolyBase. PolyBase is a valid method for loading data into Azure Synapse Analytics dedicated SQL pool because it uses the T-SQL language to access and combine data from external sources like Azure Blob Storage or Azure Data Lake Store without needing to move the data first. The COPY statement is also valid as it provides a high-throughput, flexible ingestion mechanism that supports various file formats and error handling options directly via T-SQL.

Answer

BCp utility

Answer

Azure Data Factory Copy Activity

Answer

BULK INSERT

Question 4

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 5

A company uses Azure Data Lake Storage Gen2 with a hierarchical namespace. They need to secure access to specific directories using RBAC roles. Which RBAC role should be assigned to a user to grant read and write access to a specific folder without giving access to other folders in the same container?

Accepted Answer

Storage Blob Data Contributor with ACLs at folder level. Option B is correct because Azure Data Lake Storage Gen2 supports POSIX-like access control lists (ACLs) at the directory and file level. Assigning the Storage Blob Data Contributor role at the storage account level grants broad data-plane access, but when combined with ACL entries on a specific folder, you can restrict read and write permissions to only that folder. This allows granular security without affecting other folders in the same container.

Answer

Storage Account Contributor

Answer

Storage Blob Data Reader

Answer

Storage Blob Data Owner

Question 6

Your company has an Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that automatically moves data between the 'PRIMARY' filegroup and a secondary filegroup based on data age, without manual intervention. Which feature should you use?

Accepted Answer

Partition switching. Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to efficiently move data between filegroups by switching partitions between tables. By aligning partitions with data age, you can automatically transfer older data to a secondary filegroup without manual intervention, using a scheduled process like a stored procedure or Azure Data Factory.

Answer

Materialized views

Answer

Azure Data Lake Storage tiering

Answer

PolyBase

Question 7

You are designing a data storage solution for an Azure Data Lake Storage Gen2 account that will store sensitive customer data. The solution must enforce that all data is encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, you need to prevent data from being accessed by any Azure service except Azure Synapse Analytics. Which combination of configurations should you implement?

Accepted Answer

Enable Azure Storage encryption with customer-managed keys stored in Azure Key Vault and configure a firewall with a service endpoint for Azure Synapse Analytics. Option A is correct because it combines two essential controls: Azure Storage encryption with customer-managed keys (CMK) stored in Azure Key Vault ensures data is encrypted at rest using keys you control, and a firewall with a service endpoint for Azure Synapse Analytics restricts network access so that only traffic from Azure Synapse Analytics can reach the storage account. This meets both the encryption and access restriction requirements.

Answer

Enable Azure Storage encryption with customer-managed keys and use a shared access signature (SAS) token for Azure Synapse Analytics

Answer

Enable Azure Storage encryption with customer-managed keys and assign an Azure Policy denying public network access

Answer

Enable Azure Storage encryption with Microsoft-managed keys and configure a private endpoint

Question 8

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

Accepted Answer

Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data. Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Answer

Use Azure Data Lake Storage Gen2 for both transactional and historical data

Answer

Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data

Answer

Use Azure SQL Database with Hyperscale tier for both transactional and historical data

Question 9

You are using Azure Synapse Analytics serverless SQL pool to query data in Parquet files stored in Azure Data Lake Storage Gen2. The queries are slow when filtering on a date column. You need to improve query performance without changing the data structure. What should you do?

Accepted Answer

Partition the Parquet files into folders by date. D is correct because partitioning Parquet files into folders by date enables partition elimination in Azure Synapse serverless SQL pool. When queries filter on the date column, the engine can prune entire folders from the scan, reading only the relevant Parquet files. This reduces I/O and improves performance without altering the data structure or format.

Answer

Create materialized views in the serverless SQL pool

Answer

Increase the service level objective (SLO) of the serverless SQL pool

Answer

Convert the Parquet files to CSV format

Question 10

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to an Azure Synapse Analytics dedicated SQL pool. The pipeline uses a staging Azure Blob Storage account. Recently, the pipeline has been failing with timeout errors. You need to ensure the pipeline completes successfully within the scheduled window. What should you do?

Accepted Answer

Increase the staging blob's copy timeout and enable parallel copy in the copy activity. The timeout errors occur because the default copy timeout for staging blob storage is insufficient for large data volumes or slow network conditions. Increasing the staging blob's copy timeout and enabling parallel copy in the copy activity directly addresses this by allowing more time for data transfer and leveraging multiple concurrent connections to improve throughput, ensuring the pipeline completes within the scheduled window.

Answer

Move the staging storage to Azure Data Lake Storage Gen2

Answer

Increase the dedicated SQL pool's data warehouse units (DWU)

Answer

Use PolyBase instead of staging for data loading

Question 11

You need to design a storage solution for streaming data from IoT devices. The solution must support real-time analytics and long-term storage for historical analysis. Which combination of Azure services should you use?

Accepted Answer

Azure Event Hubs and Azure Data Lake Storage Gen2. Azure Event Hubs is designed for high-throughput, low-latency ingestion of streaming data from IoT devices, supporting real-time analytics via integration with Azure Stream Analytics. Azure Data Lake Storage Gen2 provides hierarchical namespace and POSIX-compliant access for long-term storage, enabling efficient historical analysis with tools like Azure Synapse Analytics or Spark. This combination meets both real-time and historical requirements without the limitations of other options.

Answer

Azure Queue Storage and Azure Cosmos DB

Answer

Azure Event Hubs and Azure Blob Storage

Answer

Azure IoT Hub and Azure SQL Database

Question 12

You are designing a data storage solution for a healthcare organization that stores patient records. The solution must comply with HIPAA and support point-in-time restore (PITR) for the last 35 days. The data is frequently accessed for reporting. Which Azure data service should you use?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports point-in-time restore (PITR) for up to 35 days (configurable from 7 to 35 days) and is a HIPAA-eligible service when configured with encryption, auditing, and network security. It provides transactional consistency required for patient records and supports frequent reporting workloads with features like columnstore indexes and read replicas.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage with point-in-time restore

Answer

Azure Data Lake Storage Gen2

Question 13

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that reduces storage costs for historical data that is rarely accessed but must be available for querying within minutes. The solution should not require application changes. What should you do?

Accepted Answer

Create external tables pointing to data stored in Azure Data Lake Storage Gen2 with appropriate tiering. Option A is correct because creating external tables in Azure Synapse Analytics dedicated SQL pool that point to data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) allows you to query historical data directly from low-cost storage tiers (e.g., cool or archive) without moving it into the pool. This reduces storage costs for rarely accessed data while keeping it queryable within minutes, and requires no application changes since the external tables are accessed via standard T-SQL queries.

Answer

Use Azure Blob Storage with PolyBase and configure lifecycle management to archive data

Answer

Drop older partitions and reload data when needed

Answer

Change the distribution to round-robin for the fact table

Question 14

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Question 15

You are designing a data storage solution for a manufacturing company that collects sensor data from machines. The data is stored in Azure Data Lake Storage Gen2. You need to ensure that the solution can handle large volumes of streaming data (up to 100 MB/s) and provide real-time dashboards. Which TWO services should you include?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is correct because it is a real-time analytics service designed to process high-velocity streaming data (up to 100 MB/s) from sources like Event Hubs and output to dashboards and storage. It provides low-latency, SQL-based querying for real-time dashboards, making it ideal for manufacturing sensor data scenarios.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure Databricks