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

Question 1

Which TWO of the following Azure services can be used to orchestrate data pipelines that include data transformation?

Accepted Answer

Azure Synapse Pipelines. Azure Synapse Pipelines (A) is correct because it is the built-in pipeline orchestration engine in Azure Synapse Analytics, providing the same Data Factory-style activities (Copy, Data Flow, Stored Procedure, Notebook) that let you schedule and orchestrate data movement with transformation steps such as Mapping Data Flows. Azure Data Factory (B) is correct because it is Azure's dedicated cloud ETL/ELT orchestration service, where pipelines chain activities like Copy, Mapping Data Flow, Databricks notebook, and stored procedure activities to move and transform data. Azure Logic Apps (C) is a workflow/automation service for app and system integration, not a data pipeline orchestrator with transformation activities. Azure Databricks (D) is an Apache Spark analytics/compute platform that performs transformations but is typically invoked as an activity within a pipeline rather than being the orchestrator itself. Azure Functions (E) is a serverless compute service for running event-driven code, not a data pipeline orchestration service.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions

Question 2

You need to assign permissions to a service principal so that it can write data to a specific container in Azure Data Lake Storage Gen2, but not delete blobs. The above JSON shows the built-in role 'Storage Blob Data Contributor'. The role includes delete permission in DataActions. What should you do?

Accepted Answer

Create a custom role that includes read and write DataActions but excludes the delete DataAction, then assign that custom role.. Azure RBAC roles are all-or-nothing at the permission level; you cannot selectively remove a single DataAction from a built-in role at assignment time. The only way to grant write access without delete is to create a custom role that explicitly includes the required read and write DataActions (e.g., Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write) and omits the delete DataAction (Microsoft.Storage/storageAccounts/blobServices/containers/blobs/delete). This custom role is then assigned to the service principal at the container scope, ensuring it can write data but never delete blobs.

Answer

Assign the Storage Blob Data Contributor role and create a deny assignment that denies delete.

Answer

Assign the Storage Blob Data Contributor role and use ACLs to deny delete on the container.

Answer

Assign the Storage Blob Data Contributor role and remove the delete permission at the role assignment scope.

Question 3

You need to store semi-structured JSON data from a web application that requires low-latency reads and writes at a global scale. The data must be indexed automatically and support SQL-like queries. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB (NoSQL API). Azure Cosmos DB with the NoSQL API is the correct choice because it natively stores semi-structured JSON documents, provides automatic indexing of all properties, supports SQL-like queries via its query engine, and offers low-latency reads and writes at global scale through multi-region replication and configurable consistency levels. This combination directly matches the requirements for a globally distributed web application needing fast, queryable JSON storage.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 4

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Accepted Answer

Enable Azure Storage Service Encryption (SSE) for data at rest. Option B is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting stored files and blobs even if the underlying media is compromised. Option D is correct because Data Lake Storage Gen2 supports a hierarchical namespace where Azure RBAC controls management-plane and broad data access while POSIX-style ACLs provide fine-grained file and directory permissions, ensuring only authorized identities can read or modify data. Option E is correct because requiring secure transfer (HTTPS) enforces TLS encryption of data in transit between clients and the storage account, preventing eavesdropping or tampering on the network. Option A is not correct because TLS 1.0 is deprecated and insecure; enabling it weakens transport security rather than strengthening it, and Azure recommends TLS 1.2 or higher. Option C is not correct because firewall IP restrictions are a network-perimeter control, not a mechanism for securing data at rest or in transit, and they do not encrypt or authorize data access by themselves.

Answer

Enable TLS 1.0 for compatibility with legacy clients

Answer

Configure firewall rules to allow only trusted IPs

Question 5

You need to design a data storage solution for a batch processing pipeline that processes petabytes of data daily. The data is stored in Parquet format and must be accessible by both Azure Databricks and Azure Synapse Analytics. Which storage solution should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, providing native POSIX-like access control and high throughput for petabyte-scale batch processing. Both Azure Databricks and Azure Synapse Analytics have optimized connectors for ADLS Gen2 that leverage the hierarchical namespace for efficient partition pruning and file listing, which is critical for Parquet-based analytics at this scale.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 6

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source has a fact table with 500 million rows and several dimension tables. You need to choose the best distribution strategy for the fact table to minimize data movement during joins. Which distribution type should you use?

Accepted Answer

Hash distribution on the foreign key column used in joins. Hash distribution on the foreign key column used in joins ensures that rows with the same join key are co-located on the same distribution node. This minimizes data movement because the join can be performed locally on each node without shuffling data across the compute nodes, which is critical for a 500-million-row fact table.

Answer

No distribution (single distribution)

Answer

Replicated distribution

Answer

Round-robin distribution

Question 7

You need to store log files from multiple applications in a central location for long-term retention and occasional analysis. The data is rarely accessed after 30 days. Which storage solution should you use to minimize cost?

Accepted Answer

Azure Blob Storage (cool or archive tier). Azure Blob Storage with cool or archive tier is the most cost-effective solution for storing log files that are rarely accessed after 30 days. The cool tier offers low storage costs with higher access costs, while the archive tier provides the lowest storage cost but requires rehydration for access, making both ideal for long-term retention and occasional analysis.

Answer

Azure Files

Answer

Azure Data Lake Storage Gen2

Answer

Azure Cosmos DB

Question 8

Your company is migrating an on-premises SQL Server database to Azure SQL Database. The database includes a large fact table with hourly updates. You need to minimize downtime during migration. Which Azure service should you use to replicate data continuously?

Accepted Answer

Use Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode uses continuous change data capture (CDC) to replicate ongoing changes from the source SQL Server to Azure SQL Database with minimal downtime. This is the only option that supports near-zero downtime migration by synchronizing data continuously until the final cutover, which is critical for a large fact table with hourly updates.

Answer

Use Azure SQL Managed Instance as a target

Answer

Use Azure Data Factory with a copy activity

Answer

Use Azure Synapse Link for SQL Server

Question 9

You are designing a data lake on Azure Data Lake Storage Gen2. The data includes customer PII that must be encrypted at rest using customer-managed keys. Which feature should you enable?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault. Azure Storage encryption with customer-managed keys (CMK) in Azure Key Vault allows you to control the encryption keys used to encrypt data at rest in Azure Data Lake Storage Gen2. This meets the requirement for encrypting PII with customer-managed keys, as CMK provides an extra layer of security by letting you manage key rotation, revocation, and access policies.

Answer

Enable double encryption for Azure Storage

Answer

Enable infrastructure encryption

Answer

Use Azure Customer Lockbox for access control

Question 10

You are designing a data storage solution for real-time streaming data from IoT devices. The data must be stored in its original format for immediate processing and later transformed for analytics. Which Azure service should you use for raw data ingestion?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for up to 7 days (or longer with Event Hubs Capture) for later transformation and analytics. This makes it the correct choice for raw data ingestion before any transformation occurs.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 11

You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?

Accepted Answer

The join columns are not aligned; data must be shuffled across distributions. D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.

Answer

The table is not partitioned by OrderDate

Answer

Statistics on the join columns are outdated

Answer

The table should use round-robin distribution instead

Question 12

You need to store JSON files from an external partner in Azure Blob Storage. The files contain sensitive financial data. Which access method provides the highest security while allowing the partner to upload files?

Accepted Answer

Generate a shared access signature (SAS) with write-only permission and expiry time. A Shared Access Signature (SAS) with write-only permission and an expiry time provides delegated, time-limited access to Azure Blob Storage without exposing the storage account key. This ensures the partner can upload JSON files but cannot read, modify, or list existing blobs, and access automatically revokes after the expiry, meeting the highest security requirement for sensitive financial data.

Answer

Share the storage account access key with the partner

Answer

Configure a firewall to allow only the partner's IP address

Answer

Enable anonymous public access to a container

Question 13

Which TWO factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for migrating a legacy application? (Choose two.)

Accepted Answer

Need for SQL Server Agent jobs. Option C (Need for SQL Server Agent jobs) is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance, whereas Azure SQL Database does not include SQL Server Agent and instead requires alternatives such as Elastic Database Jobs or Azure Automation for scheduled task execution. Option D (Requirement for cross-database queries) is correct because Azure SQL Managed Instance supports three-part and cross-database queries within the same instance, while Azure SQL Database generally restricts cross-database queries unless you use elastic query features, making this a key differentiator for legacy applications. Option A is not a deciding factor because active geo-replication is available in both Azure SQL Database and Azure SQL Managed Instance. Option B is not a deciding factor because Microsoft Entra ID authentication is supported by both services. Option E is not a deciding factor because both Azure SQL Database (via Private Link or VNet service endpoints) and Azure SQL Managed Instance can integrate with an Azure VNet, though the mechanisms differ.

Answer

Support for active geo-replication

Answer

Authentication using Microsoft Entra ID

Answer

Integration with Azure VNet

Question 14

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

Align distribution keys on tables that are frequently joined together. Option A is correct because co-locating the distribution key on tables that are frequently joined ensures matching rows land on the same distribution, letting joins execute locally and eliminating costly shuffle (data movement) operations. Option B is correct because a distribution key with many unique values spreads rows evenly across the 60 distributions, minimizing data skew that would otherwise create hot distributions and slow query performance. Option E is correct because table size and join frequency are the core drivers of the distribution decision: small tables may be replicated, while large frequently-joined tables should be hash-distributed on a shared key. Option C is not correct because round-robin is generally recommended for staging or temporary tables, not large fact tables, which benefit from hash distribution on a join/filter column. Option D is not correct because replicated tables are intended for small dimension tables (roughly under 2 GB compressed); replicating large tables consumes excessive storage and rebuild time on every write.

Answer

Use round-robin distribution for large fact tables to distribute data evenly

Answer

Use replicated tables for large tables to avoid data movement

Question 15

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Accepted Answer

Hierarchical namespace. Azure Data Lake Storage Gen2 is built on top of Blob Storage but adds a hierarchical namespace (option A), which organizes objects into true directories and enables atomic directory operations and efficient rename/delete semantics that flat Blob Storage cannot provide. It also supports POSIX-compliant access control lists (option E), allowing fine-grained, file- and directory-level permissions (read/write/execute) that map to Hadoop and POSIX-style security models, which plain Blob Storage does not offer. The other options are not unique to ADLS Gen2: immutable storage (option B) is a Blob Storage feature (time-based retention and legal holds), soft delete for blobs (option C) is a Blob Storage data-protection feature, and lifecycle management policies (option D) are available for Blob Storage to transition or expire data across hot/cool/archive tiers.

Answer

Immutable storage

Answer

Soft delete for blobs

Answer

Lifecycle management policies