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

Question 1

You need to store data that is rarely accessed but must be retained for 10 years for compliance. The data will be accessed occasionally for audits. Which Azure storage tier is the most cost-effective?

Accepted Answer

Cool storage tier. The Cool storage tier is designed for data that is infrequently accessed and stored for at least 30 days, making it a cost-effective choice for compliance data accessed occasionally over a 10-year period. It offers lower storage costs than Hot tier while still providing low-latency access for audits, unlike Archive which requires hours to rehydrate.

Answer

Hot storage tier

Answer

Archive storage tier

Answer

Premium storage tier

Question 2

You are designing a change data capture (CDC) solution to incrementally load data from an on-premises SQL Server database to Azure Synapse Analytics. The source tables have no timestamp columns and you cannot modify the schema. Which Azure service should you use?

Accepted Answer

Azure Data Factory with change tracking. Azure Data Factory's change tracking capability is the correct choice because it can capture row-level inserts, updates, and deletes from SQL Server without requiring timestamp columns or schema modifications. It uses SQL Server's built-in change tracking feature, which tracks changes at the table level and provides a reliable incremental load mechanism to Azure Synapse Analytics.

Answer

Azure Synapse Pipelines with mapping data flows

Answer

Azure Databricks with Auto Loader

Answer

Azure Stream Analytics

Question 3

Your team needs to provide near-real-time analytics on IoT sensor data streaming into Azure Event Hubs. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and device ID. Which architecture should you implement?

Accepted Answer

Use Azure Stream Analytics with output to Data Lake Storage Gen2, using partitioning by date and device ID.. Azure Stream Analytics provides native, low-latency processing of streaming data from Event Hubs with direct output to Azure Data Lake Storage Gen2. It supports automatic partitioning by specifying a partition key (e.g., date and device ID) in the output configuration, enabling efficient, near-real-time writes in Parquet format without additional orchestration.

Answer

Use Azure Data Factory with a tumbling window trigger to copy data from Event Hubs to Data Lake Storage.

Answer

Use Azure Functions to read from Event Hubs and write to Data Lake Storage.

Answer

Use Azure Databricks with Structured Streaming to read from Event Hubs and write to Data Lake Storage.

Question 4

You are responsible for securing an Azure Synapse Analytics workspace. The workspace contains dedicated SQL pools and serverless SQL pools. You need to ensure that only users with specific Microsoft Entra ID roles can query serverless SQL pools, while dedicated SQL pools use SQL authentication. What should you do?

Accepted Answer

Configure serverless SQL pools to use Microsoft Entra ID authentication only, and dedicated SQL pools to allow SQL authentication.. Serverless SQL pools in Azure Synapse Analytics natively support Microsoft Entra ID authentication, allowing you to restrict access to specific Entra ID roles by disabling SQL authentication for those pools. Dedicated SQL pools can independently be configured to allow SQL authentication, enabling coexistence of both authentication methods as required. This separation ensures that only users with the designated Entra ID roles can query serverless SQL pools, while dedicated SQL pools remain accessible via SQL logins.

Answer

Create SQL logins for serverless SQL pools and assign permissions to Microsoft Entra ID groups.

Answer

Use a managed identity for serverless SQL pools and assign it to Microsoft Entra ID roles.

Answer

Disable SQL authentication for all pools and enforce Microsoft Entra ID authentication only.

Question 5

Which TWO of the following are valid methods to load data into a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

COPY statement. The COPY statement (option C) is a valid, first-class T-SQL ingestion method in dedicated SQL pools that reads data directly from Azure Data Lake Storage (Gen2) or Azure Blob Storage with high throughput and flexible options like file format, compression, and error handling. PolyBase (option E) is also a supported method: it uses external tables and external data sources (CREATE EXTERNAL TABLE ... WITH (LOCATION=...)) to query and load data from Azure Blob Storage or ADLS Gen2 into a dedicated SQL pool. BULK INSERT (A) is not supported for dedicated SQL pools (it targets SQL Server/Azure SQL), Azure Data Factory Copy Activity (B) is a pipeline orchestration service rather than an in-pool load method, and Azure Import/Export (D) is a physical disk shipping service for moving large datasets into Blob Storage, not a direct load mechanism into a dedicated SQL pool.

Answer

BULK INSERT

Answer

Azure Data Factory Copy Activity

Answer

Azure Import/Export service

Question 6

You execute the above T-SQL in a serverless SQL pool in Azure Synapse Analytics. The external table creation succeeds, but when you query the table, it returns zero rows. The folder 'sales/products/' exists in the container and contains multiple .parquet files. What is the most likely cause?

Accepted Answer

The LOCATION path in the external table does not match the actual file path.. The external table's LOCATION parameter specifies a path relative to the external data source's root. Even though the folder 'sales/products/' exists, the LOCATION must exactly match the subfolder path within the container. A mismatch (e.g., missing a trailing slash, case sensitivity, or an extra prefix) causes the serverless SQL pool to scan no files, returning zero rows.

Answer

The file format is incorrect; should be DELIMITEDTEXT instead of PARQUET.

Answer

The external data source location uses the wrong endpoint; should use .blob.core.windows.net instead.

Answer

The credential used in the external data source does not have read permissions.

Question 7

You run the above query on a table named 'visits' in a dedicated SQL pool. The table has 1 billion rows and is hash-distributed on user_id. The query takes a long time. What is the most likely reason?

Accepted Answer

COUNT(DISTINCT) operations are expensive because they require data movement across distributions.. In a dedicated SQL pool, COUNT(DISTINCT) is inherently expensive because it requires all distinct values to be gathered across distributions before counting. Since the table is hash-distributed on user_id, the distinct count on a different column (likely visit_date or another attribute) forces data shuffling across all distributions to ensure uniqueness, causing significant performance degradation.

Answer

The query uses a date filter which cannot be pushed down to the distribution.

Answer

The table is hash-distributed on user_id, but the query uses a different column for aggregation.

Answer

The table should use a replicated distribution instead of hash distribution.

Question 8

You are designing a data storage solution for a real-time analytics application that ingests IoT sensor data. The data must be stored in a format that supports both streaming ingestion and batch processing with low latency for queries. Which Azure storage solution should you use?

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, enabling both streaming ingestion (via Event Hubs, Kafka, or Spark Structured Streaming) and batch processing (via PolyBase, Azure Synapse, or Databricks) while supporting low-latency queries through its POSIX-like file system and optimized columnar formats like Parquet. It directly addresses the requirement for a unified storage layer that handles real-time and batch workloads without data movement.

Answer

Azure Blob Storage with hot access tier

Answer

Azure SQL Database with change data capture

Answer

Azure Cosmos DB with analytical store

Question 9

You are designing a delta lake architecture in Azure Synapse Analytics. Which TWO practices should you follow to ensure ACID transactions and data consistency?

Accepted Answer

Use the Delta Lake transaction log for all write operations. Option C is correct because Delta Lake's ACID guarantees are built on the Delta transaction log (_delta_log), which records every write as an atomic, ordered commit so readers see a consistent snapshot and concurrent writers can be serialized. Option E is correct because write-ahead logging ensures that changes are durably recorded before being applied, which underpins atomicity and lets concurrent reads and writes proceed without corrupting data consistency. Option A is wrong because disabling schema evolution does not provide ACID transactions and can actually hinder managed schema changes rather than ensure consistency. Option B is wrong because OPTIMIZE compacts small files for query performance, not for ACID transaction or consistency guarantees. Option D is wrong because CSV is a non-transactional, schema-on-read format that lacks ACID support and schema enforcement, making it unsuitable for Delta Lake consistency requirements.

Answer

Disable schema evolution to prevent accidental schema changes

Answer

Run OPTIMIZE commands frequently to compact small files

Answer

Store data in CSV format to simplify schema enforcement

Question 10

Your company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. You notice that queries on a large fact table are slow. The table is hash-distributed on CustomerID and has 60 distributions. After reviewing the query plan, you see that many queries filter on OrderDate. You want to improve query performance without redesigning the table. What should you do?

Accepted Answer

Create a clustered columnstore index on the table and order by OrderDate. Creating a clustered columnstore index with ORDER BY OrderDate on the fact table improves query performance by physically ordering the data by OrderDate within each columnstore segment. This allows segment elimination during queries that filter on OrderDate, reducing the amount of data scanned. Since the table is already hash-distributed on CustomerID, this approach does not require redesigning the table and directly addresses the slow queries without changing the distribution strategy.

Answer

Change the distribution key to OrderDate

Answer

Recreate the table with round-robin distribution

Answer

Update statistics only on the OrderDate column

Question 11

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 12

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. Option D, Azure Synapse Link for Cosmos DB, is correct because it enables near-real-time HTAP analytics directly against the Cosmos DB transactional data without extracting, transforming, and loading it into a separate store, so a single copy of data serves both operational and analytical workloads. Option E, Azure Cosmos DB with analytical store (HTAP), is correct because the analytical store is a column-oriented, fully isolated store that is automatically kept in sync with the transactional row store, letting analytical queries run without impacting operational performance on the same data. Options A, B, and C are not the intended answers: materialized views, change data capture, and PolyBase each add analytical or data-movement capability to Azure SQL Database, but they typically involve copying, staging, or transforming data into another store rather than providing a native single-copy HTAP solution as Cosmos DB's analytical store and Synapse Link do.

Answer

Azure SQL Database with materialized views

Answer

Azure SQL Database with change data capture (CDC)

Answer

Azure SQL Database with PolyBase

Question 13

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 (A) is correct because it is a fully managed, real-time stream processing engine that can ingest Event Hubs data continuously and write results with sub-second latency, enabling the real-time portion of the dashboard. Azure Synapse Analytics (C) is correct because it unifies big-data (Spark) and data-warehouse (SQL pool) analytics over the same stored data, letting the same dataset serve both real-time and batch analytical workloads. Together they satisfy the requirement of storing data in a format that supports both real-time and batch analytics with minimal latency. Azure Data Factory (B) is a batch-oriented orchestration/ETL service, not a low-latency streaming processor. Azure Analysis Services (D) is a semantic/tabular modeling layer for BI, not a streaming or big-data storage engine. Azure SQL Database (E) is a relational OLTP database that is not designed for high-throughput streaming ingestion or large-scale batch analytics.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure SQL Database

Question 14

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 15

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