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

Question 1

Match each Azure security feature to its description.

Accepted Answer

Azure Active Directory: A cloud-based identity and access management service that provides authentication and authorization.. The correct matches are: Microsoft Entra ID for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Microsoft Entra ID with Managed Identity and Key Vault with RBAC.

Answer

Azure Active Directory: An automatically managed identity in Azure AD for applications to connect to resources without managing credentials.

Answer

Azure Key Vault: A service that provides role-based access control for Azure resources.

Question 2

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 3

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

Accepted Answer

Cool tier. The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Answer

Hot tier

Answer

Archive tier

Answer

Premium tier

Question 4

A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?

Accepted Answer

Change the distribution to hash on OrderDate. Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.

Answer

Change the distribution to round-robin

Answer

Replicate the table to all distributions

Answer

Create a columnstore index on OrderDate

Question 5

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

Accepted Answer

Parquet. Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Answer

Avro

Answer

CSV

Answer

JSON

Question 6

A company is migrating its on-premises SQL Server data warehouse to Azure Synapse Analytics. They have a fact table with 2 billion rows and 30 columns. The table is frequently joined on CustomerID and filtered on OrderDate. What is the recommended table design?

Accepted Answer

Hash-distribute on CustomerID and partition on OrderDate. Hash-distributing the fact table on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution node, which makes joins on CustomerID efficient by avoiding data movement. Partitioning on OrderDate enables partition elimination when filtering by date, reducing the amount of data scanned. This combination optimizes both the join and filter operations for a large fact table in Azure Synapse Analytics.

Answer

Replicate the table to all nodes

Answer

Round-robin distribution with partitions on OrderDate

Answer

Hash-distribute on OrderDate and partition on CustomerID

Question 7

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

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

Accepted Answer

Use materialized views for complex aggregations. Option A is correct because materialized views in a dedicated SQL pool precompute and persist the results of complex aggregations (such as SUM, COUNT, AVG over large fact tables), letting the optimizer rewrite queries to read the smaller materialized result instead of rescanning and re-aggregating the base tables, which dramatically reduces I/O and CPU. Option C is correct because clustered columnstore indexes are the default and recommended storage format for dedicated SQL pools, providing high compression and batch-mode vectorized execution that speeds up large-scale analytical scans and aggregations on fact tables. Option D is correct because hash distribution on columns frequently used in JOINs co-locates matching rows on the same distribution, enabling collocated joins that avoid costly data movement (shuffle) across the 60 distributions. Option B is not a best practice because assigning the largest resource class to every query consumes excessive memory and concurrency slots, reducing overall workload throughput; resource classes should be sized to each query's needs. Option E is not a best practice because round-robin distribution is suited to staging or temporary tables, whereas large fact tables benefit from hash distribution on a join/filter column to minimize data movement during queries.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 9

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 10

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 11

You are designing a data storage solution for a global e-commerce company. The company needs to store clickstream data from millions of users with high write throughput and low-latency reads for real-time analytics. The data is semi-structured and includes nested JSON objects. Which Azure data store should you recommend?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides a multi-model, globally distributed database service with guaranteed single-digit-millisecond read and write latencies at the 99th percentile, making it ideal for high-throughput clickstream ingestion and real-time analytics. Its native support for semi-structured data and nested JSON objects via the SQL API (or MongoDB API) allows direct storage and querying of complex event payloads without schema flattening. Additionally, Cosmos DB offers automatic indexing and tunable consistency levels to balance performance and data freshness for global e-commerce scenarios.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 12

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

Accepted Answer

Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.. It uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Answer

Use hash-distributed tables for all tables and clustered columnstore indexes.

Answer

Use replicated tables for all fact tables and hash-distributed tables for dimension tables.

Answer

Use round-robin tables for all tables to simplify the migration.

Question 13

You are designing a data lake architecture using Azure Data Lake Storage Gen2. The data will be ingested from multiple sources with varying schemas. You need to organize the data in a way that supports both batch and streaming analytics while maintaining data lineage. Which folder structure convention should you use?

Accepted Answer

Use a medallion architecture with three layers: bronze (raw), silver (cleaned), gold (aggregated).. The medallion architecture (bronze, silver, gold) is the recommended pattern for Azure Data Lake Storage Gen2 when handling multiple sources with varying schemas. It supports both batch and streaming by storing raw data in bronze, applying incremental transformations in silver, and serving aggregated views in gold, while maintaining data lineage through clear layer boundaries and audit columns.

Answer

Organize by ingestion date only, with subfolders for each source.

Answer

Organize by source system, then by date.

Answer

Organize by file format (CSV, Parquet, JSON) and date.

Question 14

You are tuning a dedicated SQL pool in Azure Synapse Analytics. A query that joins two large tables (fact_sales and dim_product) is slow. The fact_sales table is hash-distributed on product_id, and dim_product is replicated. You notice that the query plan shows a shuffle move. What is the most likely cause?

Accepted Answer

The join condition does not include the distribution key for fact_sales.. When the join condition does not include the distribution key (product_id) of the hash-distributed fact_sales table, the SQL engine cannot perform a collocated join. Instead, it must shuffle data across distributions to satisfy the join, which introduces expensive data movement. The shuffle move in the query plan directly indicates this redistribution.

Answer

The fact_sales table uses clustered columnstore index.

Answer

The dim_product table is replicated, causing a broadcast join.

Answer

Statistics are out of date on both tables.

Question 15

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Avoid data skew by choosing a good distribution key.. Option B is correct because choosing a good distribution key prevents data skew, which otherwise causes some distributions to hold disproportionate rows and forces the query engine to move data across nodes, degrading performance in a dedicated SQL pool. Option D is correct because replicated tables copy the full table to every compute node, eliminating data movement for joins; this is recommended for small dimension tables under roughly 1 GB (or up to 2 GB compressed). Option E is correct because hash distribution on a high-cardinality column spreads rows evenly across the 60 distributions, which is ideal for large fact tables and minimizes skew. Option A is wrong because clustered columnstore indexes are the default and preferred storage for large tables in dedicated SQL pools, delivering high compression and fast analytical scans. Option C is wrong because round-robin distribution is a fallback for staging or tables with no clear join key, not a blanket best practice for all large fact tables, since it can cause costly data movement during joins.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.