DP-203 Design and implement data storage — Timed 40 Questions

Question 1

You are designing a solution to store streaming data from multiple sources into Azure Data Lake Storage Gen2. The data must be organized by ingestion time and source system. Each source system produces data in a different format: CSV, JSON, and Parquet. The solution must allow efficient querying using Azure Synapse Serverless SQL and must support partitioning on ingestion date. What is the recommended folder structure?

40 minute time limit — choose an answer to begin.

Accepted Answer

/data/{source_system}/{date}/ (e.g., /data/SourceA/2023-01-01/). It places the source system partition first, which aligns with Azure Synapse Serverless SQL's partition elimination behavior when querying by source system. The date partition at the end allows efficient pruning on ingestion date, and the hierarchical folder structure maps directly to Hive-style partitioning, which Synapse Serverless SQL natively supports for CSV, JSON, and Parquet formats.

Answer

/data/{date}/{source_system}/ (e.g., /data/2023-01-01/SourceA/)

Answer

/data/{source_system}/ with files named {timestamp}.csv/.json/.parquet

Answer

/data/{source_system}/{year}/{month}/{day}/ (e.g., /data/SourceA/2023/01/01/)

Question 2

Which THREE of the following are required to configure a managed private endpoint for Azure Data Factory when connecting to an Azure SQL Database that has a private endpoint?

Accepted Answer

A private DNS zone for Azure SQL Database must be linked to the virtual network.. When using a managed private endpoint for Azure Data Factory to connect to an Azure SQL Database that has a private endpoint, a private DNS zone (privatelink.database.windows.net) must be linked to the virtual network. This ensures that DNS resolution for the Azure SQL Database private endpoint resolves to the private IP address within the virtual network, enabling connectivity over the private link without relying on public endpoints.

Answer

The managed identity of Azure Data Factory must be granted access to the private endpoint.

Answer

The private endpoint must be in the same subscription as the Azure Data Factory.

Question 3

Which TWO options are valid ways to load data into Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using BCP utility. Both BCP (Bulk Copy Program) and SQL Server Integration Services (SSIS) are fully supported methods for loading data into Azure Synapse SQL Pool. BCP is a command-line utility for bulk data import/export, while SSIS provides a graphical workflow for ETL operations. INSERT INTO VALUES (Option A) is not suitable for bulk loading in an MPP environment due to row-by-row processing. The COPY statement (Option C) and PolyBase (Option E) are also valid methods, but the question asks to choose exactly two options, and BCP and SSIS are correct choices.

Answer

Using INSERT INTO VALUES

Answer

Using the COPY statement

Answer

Using PolyBase to load from Azure Blob Storage

Question 4

Which TWO of the following are valid methods to load data into a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

COPY statement. Both Azure Data Factory Copy Activity and the COPY statement are valid methods for loading data into a dedicated SQL pool. Azure Data Factory Copy Activity provides a scalable, serverless ETL capability to ingest data from various sources. The COPY statement offers high-throughput, built-in error handling, and automatic schema inference for loading from Azure Data Lake Storage Gen2 or Azure Blob Storage. PolyBase is also a valid method, but the question asks for two specific methods; while PolyBase can load data, Azure Data Factory Copy Activity is the more modern and recommended ETL approach. BULK INSERT is not supported in dedicated SQL pools, and Azure Import/Export service is used for physical data transfers, not direct loading.

Answer

BULK INSERT

Answer

Azure Data Factory Copy Activity

Answer

Azure Import/Export service

Question 5

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

Accepted Answer

Azure Synapse Pipelines. Both Azure Synapse Pipelines (option A) and Azure Data Factory (option B) are correct because they serve as native orchestration services for building, scheduling, and monitoring data pipelines that include data transformation activities. Azure Synapse Pipelines, built on the same technology as Azure Data Factory, provides integrated orchestration within Synapse Analytics, supporting Data Flows and other transformations. Azure Data Factory is a standalone cloud-based ETL and data integration service that can orchestrate complex pipelines involving data transformation, such as mapping data flows, stored procedures, or custom activities. In contrast, Azure Logic Apps (C) is designed for workflow automation and integration across SaaS applications, not for heavy data transformation or pipeline orchestration. Azure Databricks (D) is an analytics platform used primarily for big data processing and machine learning, often serving as a compute target within a pipeline rather than an orchestrator. Azure Functions (E) is a serverless compute service for event-driven code execution, typically used as a custom activity within a pipeline, not as the orchestrator itself. Therefore, the two services that can orchestrate data pipelines with transformation are Azure Synapse Pipelines and Azure Data Factory.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions

Question 6

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 7

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 8

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 9

Your team uses Azure Databricks to process data stored in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data and that access is audited. What should you implement?

Accepted Answer

Use Azure RBAC with Microsoft Entra ID and enable diagnostic settings for Azure Storage. Azure RBAC with Microsoft Entra ID (formerly Azure AD) provides fine-grained access control to Azure Data Lake Storage Gen2, ensuring only authorized users can access data. Enabling diagnostic settings for Azure Storage captures read, write, and delete operations, which are sent to a Log Analytics workspace, storage account, or event hub for auditing. This combination meets both the authorization and auditing requirements specified in the question. Option A is correct.

Answer

Use Azure managed identities to access the storage account

Answer

Use shared access signatures (SAS) with stored access policies

Answer

Configure the storage account firewall to allow only Azure Databricks IP addresses

Question 10

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 11

Which THREE factors should you consider when choosing a shard key for Azure Cosmos DB to ensure even distribution and optimal performance?

Accepted Answer

Stable key values that do not change. When choosing a shard key for Azure Cosmos DB, three key factors ensure even distribution and optimal performance. First, a stable key (Option A) is crucial because the shard key is immutable after document creation; changing it would require moving the document to a different partition, which Cosmos DB does not support, leading to cross-partition queries. Second, high cardinality (Option C) provides many distinct values, enabling the data to be spread evenly across physical partitions, preventing hot spots. Third, even distribution of request volume (Option E) ensures that the workload is balanced across partitions, avoiding throttling and maximizing throughput. Together, these factors maintain partition balance and efficient query routing.

Answer

Frequent updates to the key value

Answer

Low cardinality of the key

Question 12

Which TWO Azure Blob Storage access tiers are suitable for data that must be available within milliseconds but is accessed infrequently (e.g., once per month)?

Accepted Answer

Cool. Both the Hot and Cool access tiers provide millisecond latency, making them suitable for data that must be available quickly. While Hot is designed for frequently accessed data, it still meets the latency requirement for infrequently accessed data. Cool is optimized for infrequently accessed data (e.g., once per month) with a lower storage cost and higher access cost, making it the more cost-effective choice for such usage. Therefore, both tiers are appropriate for the described scenario.

Answer

Premium

Answer

Cold

Answer

Archive

Question 13

A company is designing a data lake on Azure Data Lake Storage Gen2. They need to enforce row-level security on the data for different departments. Which approach should they use?

Accepted Answer

Create views in Azure Synapse Serverless SQL with security predicates. Azure Synapse Serverless SQL supports row-level security through views that leverage the WHERE clause with user identity functions like SUSER_SNAME() to filter rows based on the user's department membership. This allows dynamic row filtering without duplicating data or managing separate files, and it is the only native Azure service that provides declarative row-level filtering directly on data stored in Azure Data Lake Storage Gen2.

Answer

Assign Azure RBAC roles for each department on the storage account

Answer

Implement Azure Purview data policies for row-level security

Answer

Use Azure Data Lake Storage Gen2 access control lists (ACLs) at the file level

Question 14

Your company uses Azure Synapse Analytics for a data warehouse. The fact table is 500 GB and distributed by hash on CustomerID. You notice that queries joining the fact table with the Customer dimension table are slow due to data movement. The Customer dimension table is 10 GB. Which TWO actions should you take to improve query performance?

Accepted Answer

Implement partitioning on the fact table by date. The fact table is already hash-distributed on CustomerID, which is the correct distribution key for joining with the Customer dimension. Changing it to the same distribution is not an actionable improvement. To reduce data movement, replicate the Customer dimension table (E) so each compute node has a local copy, and implement partitioning on the fact table by date (D) to enable partition elimination for queries filtered by date. Options A and B are incorrect: heap indexes are not suitable for large fact tables, and round-robin distribution would worsen join performance.

Answer

Use heap index on the fact table

Answer

Change the fact table distribution to round-robin

Answer

Change the fact table distribution to hash on CustomerID

Question 15

You are reviewing an Azure Data Factory dataset JSON definition for a data lake. The dataset is used in a copy activity that loads sales data into Azure Data Lake Storage Gen2. The pipeline runs successfully, but you notice that the output file always overwrites the previous file with the name 'sales.parquet' regardless of the folderPath parameter. What is the most likely cause?

Accepted Answer

The fileName property is hardcoded and not parameterized. The dataset's fileName property is hardcoded to 'sales.parquet', which means every pipeline run writes to the same file, overwriting it regardless of the folderPath parameter. In Azure Data Factory, if fileName is static and not parameterized, the copy activity will always target that exact file name, even if folderPath changes dynamically.

Answer

The folderPath parameter is not being evaluated correctly

Answer

The linkedServiceName is not correctly configured

Answer

The format type is incorrect; it should be 'Parquet' instead of 'ParquetFormat'

Question 16

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

Accepted Answer

Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.. ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Answer

Use Azure SQL Database with clustered columnstore index on date and device ID.

Answer

Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.

Answer

Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.

Question 17

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

Accepted Answer

Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.. Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Answer

Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.

Answer

Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.

Answer

Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.

Question 18

A data engineer needs to store semi-structured JSON log files from a web application. Each log entry is about 1 KB. The logs are rarely queried (once a month) and must be retained for 7 years for compliance. The solution must minimize storage cost. Which storage option should be used?

Accepted Answer

Store the logs in Azure Blob Storage with cool access tier.. Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Answer

Store the logs in Azure SQL Database as a table.

Answer

Store the logs in Azure Files share.

Answer

Store the logs in Azure Cosmos DB with a JSON container.

Question 19

A healthcare company stores sensitive patient data in Azure Data Lake Storage Gen2. They need to ensure that only authorized users can access data and that all access is audited. They also need to prevent data from being accessed by unauthorized Azure services. Which combination of security features should be used?

Accepted Answer

Use Azure RBAC and ACLs for authorization, enable firewall and virtual network service endpoints, and enable diagnostic settings for auditing.. It combines Azure RBAC and ACLs for fine-grained authorization, a firewall with virtual network service endpoints to restrict access to authorized networks, and diagnostic settings to capture audit logs. This layered approach ensures that only authorized users can access the data, all access is audited, and unauthorized Azure services are blocked by the firewall and service endpoints.

Answer

Use a private endpoint and Azure AD authentication, disable public access.

Answer

Use managed identity for service access and disable public access.

Answer

Use Azure AD authentication and SAS tokens for access, enabling diagnostic logs for auditing.

Question 20

Which TWO of the following are supported storage options for use as a source in Azure Synapse Pipeline Copy Activity?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is a supported source for Azure Synapse Pipeline Copy Activity because it combines a hierarchical file system with Azure Blob Storage APIs, enabling efficient data ingestion. The Copy Activity can read data from ADLS Gen2 using the AzureBlobFS linked service, which supports both file and folder-level reads for structured and unstructured data.

Answer

Azure Analysis Services

Answer

Azure Cognitive Search

Answer

Azure Purview