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

Question 1

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The sales table is partitioned by month and has a clustered columnstore index. Over time, the performance of queries filtering on a specific month has degraded. The data engineer suspects high rowgroup elimination. Which action should be taken to improve performance?

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

Accepted Answer

Reorganize or rebuild the columnstore index on the table.. Reorganizing or rebuilding the columnstore index compresses fragmented rowgroups and merges small rowgroups into optimal sizes (typically 102,400 rows per rowgroup). This directly addresses the degraded rowgroup elimination: when rowgroups are too small or fragmented, the engine cannot efficiently skip entire rowgroups during partition-level scans, causing more I/O and slower performance.

Answer

Change the table distribution to hash-distributed on the partition key.

Answer

Drop and recreate the affected partitions.

Answer

Update statistics on the partitioned column.

Question 2

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

Accepted Answer

Azure Storage Service Encryption (SSE) with customer-managed keys. Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Answer

Azure Disk Encryption

Answer

Azure Storage Firewall

Answer

Azure Information Protection

Question 3

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using the COPY statement. For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Answer

Using INSERT INTO VALUES

Answer

Using BCP utility

Answer

Using SQL Server Integration Services (SSIS)

Question 4

A company is designing a data storage solution for streaming IoT telemetry data. The data is JSON-formatted, arrives at up to 10,000 events per second, and must be stored for at least 30 days for real-time dashboards and ad-hoc querying. The solution must minimize operational overhead and query latency. Which Azure service should they use?

Accepted Answer

Azure Data Explorer (ADX). Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and time-series data, ingesting up to 10,000 events per second with low latency. Its columnar storage and indexing enable sub-second queries on JSON data for real-time dashboards, while the 30-day retention is natively configurable via caching and soft-delete policies. This minimizes operational overhead by eliminating the need for manual partitioning or index tuning.

Answer

Azure Blob Storage with Azure Data Lake Storage Gen2

Answer

Azure Cosmos DB with analytical store

Answer

Azure SQL Database with elastic query

Question 5

A company is designing a data storage solution for IoT device telemetry data. The data is append-only, needs to be stored cost-effectively for long-term analytics, and must support querying by device ID and timestamp. Which Azure storage solution should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines the cost-effective, append-only blob storage of Azure Blob Storage with a hierarchical namespace that enables directory-level operations and POSIX-like access control. This makes it ideal for storing large volumes of IoT telemetry data at low cost while supporting efficient querying by device ID and timestamp through partition pruning in tools like Azure Synapse Analytics or Apache Spark.

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Answer

Azure Blob Storage with hot access tier

Question 6

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

Accepted Answer

Store processed file names in a Delta table and compare with source folder listing. Storing processed file names in a Delta table allows the pipeline to track which files have already been ingested, supporting incremental processing and handling late-arriving data up to 2 hours. By comparing the current source folder listing against the Delta table, the pipeline can identify only new or late-arriving files, avoiding reprocessing and ensuring exactly-once semantics. This approach integrates seamlessly with Azure Databricks and Delta Lake's ACID transactions, providing reliable state management for batch pipelines.

Answer

Use Blob Storage event triggers to invoke Azure Functions

Answer

Use Azure Synapse Pipelines with a schedule and full load each time

Answer

Use Azure Data Factory with watermark columns in the source

Question 7

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed, serverless relational database service designed for transactional workloads. It provides built-in high availability, automatic scaling, and pay-per-use billing, making it ideal for OLTP scenarios without the need to manage underlying infrastructure.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Data Lake Storage Gen2

Question 8

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

Accepted Answer

Azure Cosmos DB with multi-region writes and eventual consistency. Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Answer

Azure Cosmos DB with single-region writes and strong consistency

Answer

Azure Data Lake Storage Gen2 with hierarchical namespace

Answer

Azure Blob Storage with hot tier and append blobs

Question 9

Which of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Azure Storage Service Encryption (SSE) with Microsoft-managed keys. Azure Storage Service Encryption (SSE) with Microsoft-managed keys encrypts data at rest automatically for Azure Data Lake Storage Gen2 using 256-bit AES encryption. This is enabled by default for all storage accounts, ensuring data written to disk is encrypted before being persisted, with no additional configuration required.

Answer

Azure Active Directory (Azure AD) authentication for storage accounts

Answer

Configure firewall rules to restrict IP access

Question 10

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Accepted Answer

Store data in Parquet format. Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Answer

Use many small files (under 64 MB) to increase parallelism

Answer

Store data in nested folder structures for better organization

Question 11

You are designing a data storage solution for IoT sensor data. The data is written thousands of times per second and requires low-latency reads for real-time dashboards. Which Azure storage solution should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides single-digit millisecond read and write latency at any scale, with automatic indexing and multi-region distribution. Its support for multiple APIs (SQL, MongoDB, Cassandra, etc.) and configurable consistency levels makes it ideal for IoT sensor data requiring high-throughput writes and low-latency reads for real-time dashboards.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 12

Your company stores sensitive customer data in Azure SQL Database. You need to encrypt the data at rest and ensure that only your application can decrypt it, even from database administrators. What should you implement?

Accepted Answer

Always Encrypted. Always Encrypted is correct because it ensures that sensitive data is encrypted at rest and in use, and the encryption keys are stored client-side, so only the application can decrypt the data. Database administrators (DBAs) cannot access the plaintext data because they lack the column encryption keys, even though they have full administrative access to the database.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Azure Storage Service Encryption

Question 13

Which Azure storage solution is best suited for storing large volumes of unstructured data, such as log files and media files, and supports both hierarchical namespace and POSIX-like access control lists?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with POSIX-like access control lists (ACLs) on top of Azure Blob Storage. This makes it ideal for storing large volumes of unstructured data (e.g., log files, media files) while supporting fine-grained, POSIX-compliant permissions and directory-level operations that are essential for big data analytics workloads.

Answer

Azure Blob Storage

Answer

Azure Files

Answer

Azure SQL Database

Question 14

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Table Storage

Question 15

Match each Azure service to its primary purpose in a data engineering pipeline.

Accepted Answer

Azure Data Lake Storage Gen2: Scalable data lake for big data analytics.. The correct matches are: Azure Data Lake Storage Gen2 as a scalable data lake, Azure Synapse Analytics as a unified data warehousing platform, Azure Data Factory for data integration and orchestration, and Azure Databricks for Spark-based analytics. Common confusions include swapping storage and integration services, or mistaking data lakes for data warehousing.

Answer

Azure Data Lake Storage Gen2: Cloud data integration service for orchestrating data movement.

Answer

Azure Synapse Analytics: Scalable data lake for big data analytics.