DP-203 Design and implement data storage — 10 Questions

Question 1

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

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

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 4

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?

Accepted Answer

/data/{source_system}/{date}/ (e.g., /data/SourceA/2023-01-01/). Option B is correct because 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 5

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

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

Question 7

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

You are reviewing a copy job configuration in Azure Data Factory that copies Parquet files from Azure Data Lake Storage Gen2 to Azure Synapse Analytics. The exhibit shows the job settings. If the source folder contains a file that is not in Parquet format (e.g., a CSV file), what will happen?

Accepted Answer

The copy job will skip the CSV file and continue copying other Parquet files.. When using Azure Data Factory's Copy Activity with a wildcard file path or a dataset that filters for Parquet files (e.g., *.parquet), the service evaluates the file pattern before attempting to read the file. If a CSV file is present in the same folder but does not match the Parquet filter, ADF simply ignores it and continues processing only the matching Parquet files. This behavior is by design to allow flexible file selection without causing failures.

Answer

The copy job will skip the CSV file and stop.

Answer

The copy job will fail with an error.

Answer

The copy job will attempt to read the CSV file as Parquet and may produce corrupt data.

Question 9

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

Accepted Answer

You can query the external table using standard T-SQL SELECT statements.. Option C is correct because an external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer

Querying the external table automatically imports data into a round-robin distribution.

Answer

The table cannot be queried until the data is imported into the dedicated SQL pool.

Answer

You must first create a PolyBase external table before querying.

Question 10

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage