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

Question 1

Which THREE factors should be considered when designing a partitioning strategy for a large fact table in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Data compression. In a dedicated SQL pool, partitioning a large fact table interacts directly with data compression, so option B is correct: choosing the right compression (e.g., columnstore vs. rowstore, and COLUMNSTORE_ARCHIVE for cold data) affects both storage and query performance, and partition switching requires matching compression settings. Option D is correct because the partition column determines pruning effectiveness; it should be a low-cardinality, frequently filtered column such as a date key, since partitioning on a high-cardinality column creates excessive partitions and metadata overhead. Option E is correct because the number of partitions must be balanced: too many partitions (e.g., over 1,000–2,000 per distribution) causes poor query plans and slow partition elimination, while too few limits pruning and partition-switching benefits. Options A and C are not among the three factors asked for here: index type and distribution key are important design decisions for dedicated SQL pools, but the question specifically targets the partitioning strategy, where compression, partition column, and partition count are the defining considerations.

Answer

Index type

Answer

Distribution key

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. 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 (C) is correct because it is a native T-SQL technology in dedicated SQL pools that uses external tables and external data sources to query and load data directly from Azure Data Lake Storage Gen2 or Blob Storage with massively parallel processing. The COPY statement (E) is correct because it is the modern, flexible T-SQL bulk-load command in dedicated SQL pools that reads from Azure Data Lake Storage Gen2 or Blob Storage with high throughput and supports CSV, Parquet, and ORC. The bcp utility (A) is not a valid method for loading a dedicated SQL pool because it targets SQL Server and Azure SQL Database, not Synapse dedicated pools. Azure Data Factory Copy Activity (B) is a valid orchestration tool for moving data, but the question asks for methods that load directly into the dedicated SQL pool, and ADF ultimately relies on PolyBase or COPY under the hood rather than being a native load method itself. BULK INSERT (D) is not supported in dedicated SQL pools; it is a SQL Server and Azure SQL Database command.

Answer

BCp utility

Answer

Azure Data Factory Copy Activity

Answer

BULK INSERT

Question 4

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. 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 5

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 6

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. 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 7

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 8

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 9

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 10

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 11

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. 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 12

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 Event Hubs (E) is correct because it is a highly scalable event-ingestion service capable of ingesting millions of events per second and handling throughput well beyond 100 MB/s, making it the right front door for streaming machine sensor data. Azure Stream Analytics (D) is correct because it provides real-time stream processing with sub-second latency, can read directly from Event Hubs, and can output to Power BI for real-time dashboards while also writing results to Azure Data Lake Storage Gen2. Together they form the standard ingestion-plus-real-time-processing pattern for high-volume streaming telemetry. Azure Analysis Services (A) is not appropriate because it is an OLAP semantic-model engine for batch/analytical queries, not real-time streaming ingestion or processing. Azure Data Factory (B) is a batch-oriented data integration and orchestration service, not designed for continuous 100 MB/s streaming. Azure Databricks (C) can process streams but is a general-purpose analytics platform with higher latency and operational overhead, so it is not the best fit for the required real-time dashboards.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure Databricks

Question 13

You are designing a data storage solution for a financial services company. The solution must meet the following requirements: store transaction data for 7 years for regulatory compliance, support point-in-time restore (PITR) for the last 30 days, and minimize storage costs for historical data. Which THREE actions should you take?

Accepted Answer

Use Azure Blob Storage with cool access tier for data older than 30 days. Option B is correct because moving data older than 30 days to the Blob Storage cool access tier lowers storage cost for infrequently accessed historical records while still keeping them online and durable for the 7-year retention period. Option C is correct because Azure SQL Database automated backups support point-in-time restore (PITR), and configuring the PITR retention to 30 days directly satisfies the requirement to restore transaction data to any point within the last 30 days. Option D is correct because Azure Data Factory can orchestrate the export of data older than 30 days from Azure SQL Database into Blob Storage, which is the mechanism that separates recent, PITR-protected data from low-cost historical archives. Option A is not selected because Data Lake Storage Gen2 is optimized for big-data analytics workloads rather than being the specified low-cost archival target here. Option E is not selected because Azure Table Storage is a NoSQL key-attribute store, not the appropriate archival tier for long-term transaction retention at minimal cost.

Answer

Store historical data in Azure Data Lake Storage Gen2

Answer

Use Azure Table Storage for archival

Question 14

You need to design a storage solution for a data lake that will be used by multiple teams for analytics. The solution must support fine-grained access control, versioning of files, and integration with Azure Purview for data cataloging. Which THREE features should you enable in Azure Data Lake Storage Gen2?

Accepted Answer

Integration with Microsoft Purview. Option A (Integration with Microsoft Purview) is correct because Azure Data Lake Storage Gen2 natively integrates with Microsoft Purview, allowing automatic scanning, classification, and cataloging of data assets across the data lake so multiple teams can discover and govern data. Option C (Blob versioning) is correct because enabling blob versioning in ADLS Gen2 preserves previous versions of files whenever they are overwritten or deleted, satisfying the requirement for file versioning and enabling recovery of prior data states. Option D (Access control lists (ACLs)) is correct because ADLS Gen2 supports POSIX-style ACLs at the directory and file level, which provide the fine-grained, per-user and per-group access control needed when multiple teams share the same data lake. Option B (Managed identity authentication) is not the right fit here because it is an authentication mechanism for service-to-service access rather than a storage feature that delivers fine-grained access control, versioning, or cataloging. Option E (Soft delete) is not required because it only protects against accidental deletion by retaining deleted blobs for a retention period; it does not provide versioning of file contents, fine-grained ACL-based access control, or Purview catalog integration.

Answer

Managed identity authentication

Answer

Soft delete

Question 15

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to implement a security solution that prevents unauthorized access from the public internet while allowing access from specific Azure services and on-premises networks. Which feature should you configure?

Accepted Answer

Configure firewall rules and virtual network service endpoints.. Configuring firewall rules and virtual network service endpoints allows you to restrict access to your Azure Data Lake Storage Gen2 account to only traffic originating from specific virtual networks and trusted Azure services, while blocking all public internet traffic. This provides a network-level security boundary that meets the requirement of preventing unauthorized public access while permitting access from on-premises networks (via VPN/ExpressRoute) and specific Azure services.

Answer

Use private endpoints for all storage accounts.

Answer

Enable Microsoft Entra ID authentication only.

Answer

Use shared access signatures (SAS) with IP restrictions.