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

Question 1

A financial services company is migrating its data warehouse to Azure Synapse Analytics. They have a star schema with a 10-billion-row fact table and 50 dimension tables. Query performance is critical, and they need to minimize data movement during joins. Which distribution strategy should they use for the fact table?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Hash distribution on the most frequently joined dimension key. Hash distribution on the most frequently joined dimension key is correct because it co-locates matching rows from the fact and dimension tables on the same compute node, minimizing data movement during joins. For a 10-billion-row fact table, this distribution ensures that the most common join operation is performed locally without shuffling data across nodes, which is critical for query performance in Azure Synapse Analytics.

Answer

Replicated distribution

Answer

Partitioned distribution

Answer

Round-robin distribution

Question 2

Which THREE factors should be considered when choosing between Azure Synapse Dedicated SQL Pool and Azure SQL Database for a data warehouse workload?

Accepted Answer

Concurrency and workload management. Option B (Concurrency and workload management) is correct because Synapse Dedicated SQL Pool provides workload isolation, resource classes, and workload groups that let you manage many concurrent queries and mixed workloads, whereas Azure SQL Database has more limited concurrency controls for large-scale DW scenarios. Option D (Volume of data to be stored) is correct because Synapse Dedicated SQL Pool uses MPP architecture with distributions and can scale to petabytes via DWU/cDWU scaling, while Azure SQL Database is typically limited to smaller data volumes (up to 4 TB with Hyperscale, but not designed for petabyte-scale warehousing). Option E (Complexity of analytical queries) is correct because Synapse Dedicated SQL Pool is optimized for complex analytical queries over large datasets using columnstore indexes and MPP, while Azure SQL Database is better suited for OLTP and smaller analytical workloads. Option A (Need for geo-replication) is not a primary differentiator for choosing between these two for a data warehouse workload, since both offer geo-replication features but it is not a core DW selection factor. Option C (Integration with Azure Data Lake Storage) is not a distinguishing factor because both Azure Synapse and Azure SQL Database can integrate with Azure Data Lake Storage, so it does not drive the choice between them.

Answer

Need for geo-replication

Answer

Integration with Azure Data Lake Storage

Question 3

A company uses Azure Synapse Analytics dedicated SQL pool. They need to load data from Azure Data Lake Storage Gen2 (ADLS Gen2) incrementally. Which PolyBase external table configuration supports incremental loading without reprocessing historical data?

Accepted Answer

Create an external table partitioned by date column with a partition location pattern.. Partitioning an external table by a date column with a partition location pattern allows PolyBase to perform partition elimination during queries. This means only the partitions containing new or modified data are read from ADLS Gen2, enabling incremental loading without scanning historical data.

Answer

Create an external table with a clustered index on the date column.

Answer

Create an external table on ORC files with a clustered columnstore index.

Answer

Create an external table with round-robin distribution on the date column.

Question 4

You are migrating an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The database includes a table with 500 million rows that is frequently queried by date range. Which distribution strategy should you use for this table?

Accepted Answer

Hash distribution on the date column.. Hash distribution on the date column is correct because it distributes rows evenly across distributions based on a hash of the date value, enabling partition elimination and collocated joins when queries filter by date range. This strategy optimizes performance for the frequent date-range queries by ensuring that data for a specific date range is likely stored on the same distribution, minimizing data movement.

Answer

Hash distribution on an identity column.

Answer

Replicated distribution.

Answer

Round-robin distribution.

Question 5

Which THREE components are required to implement a modern data warehouse architecture on Microsoft Azure using Azure Synapse Analytics?

Accepted Answer

Microsoft Purview for data governance and lineage.. Azure Synapse Analytics is the core of a modern Azure data warehouse, and a dedicated SQL pool (option E) provides the provisioned, massively parallel processing (MPP) engine that stores and queries the warehoused relational data. Azure Data Lake Storage Gen2 (option C) is required as the underlying data lake because Synapse uses its hierarchical namespace and integrates with PolyBase/OPENROWSET to ingest and query external data at scale. Microsoft Purview (option A) is required to deliver the governance, cataloging, and end-to-end data lineage that a modern data warehouse needs across the lake and warehouse assets. Power BI (option B) is a visualization tool that consumes the warehouse but is not a required architectural component, and Azure Analysis Services (option D) is a legacy semantic-modeling service that is optional and not needed to implement the warehouse itself.

Answer

Power BI as the data visualization layer.

Answer

Azure Analysis Services for semantic modeling.

Question 6

Which TWO Azure services can be used to ingest streaming data into Azure Synapse Analytics?

Accepted Answer

Azure Stream Analytics.. Azure Stream Analytics (B) is correct because it is a fully managed, real-time stream processing engine that can read from streaming sources such as Event Hubs or IoT Hub and write its output directly into Azure Synapse Analytics (dedicated SQL pool) as a sink, making it a native streaming ingestion path. Azure Event Hubs (D) is correct because it is a big-data streaming platform and event ingestion service that captures high-throughput event streams and can be used as a streaming source for Synapse, including via its native Synapse connector or Event Hubs Capture into ADLS Gen2 that Synapse reads. Azure Databricks Auto Loader (A) is not the intended answer here because it is a Databricks feature for incrementally ingesting files from cloud storage, not an Azure service for streaming ingestion into Synapse. Azure Data Factory (C) is primarily an orchestration and batch/ETL service (with limited streaming via data flows), so it is not the streaming ingestion service being asked for. Azure Logic Apps (E) is a workflow automation service for app integration, not a streaming data ingestion engine for Synapse.

Answer

Azure Databricks Auto Loader.

Answer

Azure Data Factory.

Answer

Azure Logic Apps.

Question 7

You are reviewing an ARM template snippet for an Azure Blob Storage container. What is the effect of this configuration?

Accepted Answer

It enforces a 7-year immutable retention policy allowing appends.. The ARM template snippet configures a time-based retention policy on the container with a retention period of 2555 days (7 years) and sets `allowProtectedAppendWrites` to true. This enables an immutable storage policy that prevents deletion or modification of existing blobs but allows new append blocks to be added to append blobs, making option C correct.

Answer

It enables legal hold on the container for 2555 days.

Answer

It blocks append writes to blobs in the container.

Answer

It sets a 3-year retention policy blocking any modifications.

Question 8

You are a data engineer for a large e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool as its enterprise data warehouse. A new business requirement mandates that the Sales fact table, which contains 2 billion rows, must support real-time analytics with a maximum query latency of 1 second for aggregations on the most recent 24 hours of data. The table is currently hash-distributed on CustomerID and partitioned monthly by SaleDate. The current query performance for recent data is slow due to full partition scans. The data is ingested via Azure Event Hubs and processed by Azure Stream Analytics, which writes to staging tables every minute. You need to redesign the storage to meet the latency requirement while minimizing cost and maintaining data integrity. Which approach should you take?

Accepted Answer

Create a separate staging table for the last 24 hours of data with round-robin distribution and a clustered columnstore index. After each batch load, merge the staging table into the main partitioned Sales table. Queries for recent data should target the staging table.. It isolates the hot (recent 24-hour) data into a separate staging table with a clustered columnstore index, which is optimized for fast aggregations and high compression. This avoids full partition scans on the 2-billion-row main table, and merging into the partitioned table after each batch load maintains data integrity without requiring expensive repartitioning or additional dedicated SQL pools.

Answer

Increase partition granularity to hourly partitions for the Sales table.

Answer

Change the Sales table to use a clustered index on SaleDate and a round-robin distribution.

Answer

Provision a second dedicated SQL pool optimized for real-time queries and replicate the recent data there.

Question 9

You are designing a solution to store semi-structured JSON logs from a web application in Azure Cosmos DB. The logs are written once and rarely read. The application writes up to 10,000 documents per second, and each document is about 2 KB. You need to minimize RU/s cost. Which API and indexing policy should you choose?

Accepted Answer

Azure Cosmos DB for MongoDB with a custom indexing policy that only includes a wildcard index on _id and disables automatic indexing.. Azure Cosmos DB for MongoDB with a custom indexing policy that disables automatic indexing and only includes a wildcard index on _id minimizes RU/s cost for write-heavy, rarely read workloads. Each write operation consumes RUs proportional to the number of indexed paths; by eliminating automatic indexing of all fields, you drastically reduce the per-document write RU charge. The MongoDB API supports this fine-grained indexing control, making it ideal for high-throughput ingestion of semi-structured JSON logs.

Answer

Azure Cosmos DB for Cassandra with default indexing policy.

Answer

Azure Cosmos DB for Table with default indexing policy.

Answer

Azure Cosmos DB for NoSQL with default indexing policy (automatic indexing of all fields).

Question 10

Your company uses Azure Synapse Analytics dedicated SQL pool to store a fact table with 2 billion rows. You need to improve query performance for a workload that frequently aggregates sales by date and product category. Which distribution and index type should you use?

Accepted Answer

Hash-distribute on product_category and use a clustered columnstore index.. Hash-distributing on product_category ensures that rows with the same product category are co-located on the same distribution, enabling local aggregation without data movement. A clustered columnstore index provides high compression and batch-mode processing, which is ideal for large fact tables and analytical workloads that aggregate millions of rows by columns like date and product_category.

Answer

Replicate the table and use a clustered index.

Answer

Round-robin distribution and a clustered columnstore index.

Answer

Hash-distribute on date and use a clustered index.

Question 11

You are designing a change data capture (CDC) pipeline to ingest incremental changes from an on-premises SQL Server database into Azure Data Lake Storage Gen2. The pipeline must run every 5 minutes and handle high-volume DML changes. Which Azure service should you use to capture the changes with low latency?

Accepted Answer

Azure Data Factory with a change data capture (CDC) source in the mapping data flow.. Azure Data Factory's mapping data flow includes a native CDC source that can connect to SQL Server and capture incremental DML changes (inserts, updates, deletes) with low latency. This approach uses change tracking or change data capture features in SQL Server to identify changes, and the pipeline can run every 5 minutes to meet the high-volume requirement without custom coding.

Answer

Azure Data Share to share the SQL Server data and capture changes.

Answer

Azure Synapse Pipelines with a copy activity that uses a query to capture changes.

Answer

Azure Databricks with Auto Loader and Delta Live Tables to capture changes.

Question 12

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by month. You notice that queries filtering on a specific month still scan all partitions. The table has a clustered columnstore index. What is the most likely cause?

Accepted Answer

The columnstore index has a large number of open rowgroups that are not compressed.. When a clustered columnstore index has a large number of open rowgroups (i.e., rowgroups that have not yet been compressed into columnstore segments), queries may fall back to the delta store, which stores data in a B-tree-like structure. This bypasses partition elimination because the delta store does not maintain the same metadata for partition pruning, causing all partitions to be scanned. Compressing open rowgroups into closed, compressed rowgroups restores proper partition elimination behavior.

Answer

The table is hash-distributed on a column other than date, preventing partition elimination.

Answer

The statistics on the date column are outdated or missing.

Answer

The table is replicated, so partition elimination does not apply.

Question 13

You need to store streaming data from Azure Event Hubs into Azure Data Lake Storage Gen2 in near real-time. The data should be stored in Avro format with a folder structure: /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. Which Azure service should you use to ingest the data?

Accepted Answer

Azure Stream Analytics with a job that reads from Event Hubs and writes to ADLS Gen2.. Azure Stream Analytics is the correct choice because it natively supports reading from Event Hubs and writing to ADLS Gen2 with built-in time-based partitioning into the exact folder structure /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. It provides near real-time processing with sub-minute latency and can output data in Avro format directly, meeting all requirements without additional code or orchestration.

Answer

Event Hubs Capture feature to automatically capture events to ADLS Gen2.

Answer

Azure Data Factory with a tumbling window trigger to copy data from Event Hubs every 5 minutes.

Answer

Azure Databricks with Auto Loader to read from Event Hubs and write to ADLS Gen2.

Question 14

You are designing a data storage solution for a global e-commerce company. The company's analytics team uses Azure Synapse Serverless SQL to query Parquet files in ADLS Gen2. The data is partitioned by year, month, and day. The team frequently runs queries that aggregate sales by product category across the last 30 days. However, the queries are slow and scanning too much data. What should you do to improve performance?

Accepted Answer

Create a view that uses explicit partition pruning by filtering on year, month, and day in the WHERE clause.. Creating a view with explicit partition pruning by filtering on year, month, and day in the WHERE clause forces Synapse Serverless SQL to read only the relevant partitions (the last 30 days) instead of scanning the entire dataset. This reduces the amount of data scanned, directly improving query performance and lowering cost.

Answer

Increase the number of files per partition to improve parallelism.

Answer

Convert the Parquet files to CSV format to reduce file size.

Answer

Add a new partition by product category to the folder structure.

Question 15

Which TWO factors should you consider when choosing between Azure SQL Database and Azure Cosmos DB for a transactional workload that requires low-latency reads and writes globally?

Accepted Answer

Consistency models (strong, bounded staleness, session, eventual, consistent prefix).. Option C is correct because Azure Cosmos DB lets you choose among five well-defined consistency levels (strong, bounded staleness, session, consistent prefix, and eventual), and that choice directly affects read latency, availability, and data freshness for a globally distributed transactional workload; Azure SQL Database does not expose this tunable spectrum. Option E is correct because Azure Cosmos DB provides turnkey global distribution with multi-region writes (multi-master) so any region can accept writes and serve low-latency reads/writes locally, which is central to the scenario; Azure SQL Database's geo-replication is primarily read-scale with a single writable primary. Option A is not a distinguishing factor here because multi-document ACID transactions are a Cosmos DB capability but the question asks for factors to weigh between the two services, and Azure SQL Database already provides full ACID transactions relationally. Option B is not decisive because both services support secondary indexes. Option D is not decisive because both platforms support stored procedures (T-SQL in Azure SQL Database, JavaScript in Cosmos DB).

Answer

ACID transaction support across multiple documents.

Answer

Support for secondary indexes.

Answer

Ability to run stored procedures.