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

Question 1

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?

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

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 2

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. Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption, which is transparent to applications and meets compliance requirements. This is a fundamental security control for protecting data stored in Azure Data Lake Storage Gen2.

Answer

Enable TLS 1.0 for compatibility with legacy clients

Answer

Configure firewall rules to allow only trusted IPs

Question 3

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

Accepted Answer

The dimension table is not actually replicated due to its size exceeding the replication threshold. The 'ShuffleMove' operation in a Synapse dedicated SQL pool query plan indicates that data is being moved between distributions to complete the join. If the small dimension table is supposed to be replicated but exceeds the replication threshold (default 60 GB compressed), it will not be replicated and instead remains hash-distributed. This forces a shuffle of the large fact table's data to align with the dimension table's distribution, causing significant data movement and slow performance.

Answer

The query is running with a low resource class

Answer

Result set caching is enabled

Answer

Statistics are outdated on the dimension table

Question 4

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 5

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 6

You are designing a data storage solution that must support both operational and analytical workloads using a single copy of data. Which TWO technologies should you consider?

Accepted Answer

Azure Synapse Link for Cosmos DB. The requirement is to support both operational and analytical workloads on a single copy of data, which is characteristic of HTAP (Hybrid Transactional/Analytical Processing) solutions. Among the options, Azure Synapse Link for Cosmos DB (D) and Azure Cosmos DB with analytical store (HTAP) (E) are specifically designed for HTAP, synchronizing operational data to an analytical store without separate copies. Azure SQL Database with materialized views (A) improves query performance but does not enable HTAP on a single copy; change data capture (B) tracks changes but is not HTAP; and PolyBase (C) is a data virtualization tool that queries external data sources, not an in-database HTAP solution. Therefore, D and E are correct.

Answer

Azure SQL Database with materialized views

Answer

Azure SQL Database with change data capture (CDC)

Answer

Azure SQL Database with PolyBase

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

You are designing a data storage solution for a real-time dashboard that displays streaming data from Azure Event Hubs. The data must be stored in a format that supports both real-time and batch analytics with minimal latency. Which TWO technologies should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is correct because it provides real-time stream processing with low latency, directly ingesting data from Event Hubs and outputting to storage or analytics services. It enables both real-time dashboard queries and batch analytics by writing to a staging store like Azure Data Lake Storage, which can then be queried by Azure Synapse Analytics for historical analysis.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure SQL Database

Question 9

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query is slow and you suspect that the file layout is not optimized. You examine the files and find that each file is 50 MB. What should you do to improve query performance?

Accepted Answer

Merge the small files into larger files of at least 100 MB each. Azure Synapse serverless SQL pool performs best when reading files of at least 100 MB each. Small files (50 MB) cause excessive metadata operations, partition discovery, and I/O overhead, leading to slower queries. Merging them into larger files reduces the number of file open/close operations and improves parallelism efficiency.

Answer

Partition the data into folders by date

Answer

Compress the files with Gzip

Answer

Convert the files to CSV format to reduce overhead

Question 10

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by date. As data grows, query performance on recent data degrades. You need to improve performance for queries filtering on the current month without affecting queries on older data. What should you do?

Accepted Answer

Implement partition switching to move older partitions to a different filegroup with slower storage. Partition switching allows you to efficiently move older partitions to a different filegroup (e.g., slower or compressed storage) without affecting queries on recent data. This reduces the active data volume in the fact table, improving scan performance for queries filtering on the current month. The operation is metadata-only and does not require data movement, so it does not impact ongoing queries on the remaining partitions.

Answer

Increase the service level objective (SLO) of the dedicated SQL pool

Answer

Change the table distribution to round-robin

Answer

Update statistics on the table for the current month partition

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

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a folder structure that separates data by sensitivity level. Which access control method should you use?

Accepted Answer

Use Azure RBAC at the resource group level and ACLs on directories. Azure Data Lake Storage Gen2 supports both Azure RBAC at the resource group level for coarse-grained control and POSIX-like ACLs on directories for fine-grained, sensitivity-based access. This combination allows you to assign read/write/execute permissions per directory, enabling a folder structure that separates data by sensitivity level without compromising security.

Answer

Use a storage account firewall and virtual network service endpoints

Answer

Use storage account keys for all access

Answer

Use shared access signatures (SAS) for each folder

Question 13

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?

Accepted Answer

Increase the number of files to match the number of distributions. COPY INTO in Azure Synapse dedicated SQL pool distributes data across 60 distributions. To maximize parallelism, the number of input files should match or exceed the number of distributions. With 1 GB files, you have too few files to fully utilize all distributions, causing some distributions to remain idle. Increasing the number of files to at least 60 ensures each distribution gets work, improving throughput.

Answer

Reduce the file size to 100 MB to increase parallelism

Answer

Use PolyBase instead of COPY INTO

Answer

Disable parallel processing in the COPY command

Question 14

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. SQL Managed Instance includes full support for SQL Server Agent, which is essential for legacy applications that rely on scheduled jobs, alerts, or automation. Azure SQL Database does not support SQL Server Agent, making Managed Instance the appropriate choice when this feature is required.

Answer

Support for active geo-replication

Answer

Authentication using Microsoft Entra ID

Answer

Integration with Azure VNet

Question 15

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. Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance in Azure Synapse Analytics dedicated SQL pools.

Answer

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

Answer

Use replicated tables for large tables to avoid data movement