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

Question 1

You are designing a data storage solution for a financial services company. The solution must meet the following requirements: store transaction data for 7 years for regulatory compliance, support point-in-time restore (PITR) for the last 30 days, and minimize storage costs for historical data. Which THREE actions should you take?

Accepted Answer

Use Azure Blob Storage with cool access tier for data older than 30 days. Option B is correct because Azure Blob Storage's cool access tier is designed for infrequently accessed data with lower storage costs, making it ideal for historical transaction data older than 30 days. This tier provides cost-effective storage while still allowing retrieval when needed, aligning with the requirement to minimize storage costs for historical data.

Answer

Store historical data in Azure Data Lake Storage Gen2

Answer

Use Azure Table Storage for archival

Question 2

You need to design a storage solution for a data lake that will be used by multiple teams for analytics. The solution must support fine-grained access control, versioning of files, and integration with Azure Purview for data cataloging. Which THREE features should you enable in Azure Data Lake Storage Gen2?

Accepted Answer

Integration with Microsoft Purview. Option A is correct because Microsoft Purview integration is required for data cataloging, enabling automated scanning, classification, and lineage tracking of data stored in Azure Data Lake Storage Gen2. This directly supports the requirement for data cataloging in the scenario.

Answer

Managed identity authentication

Answer

Soft delete

Question 3

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 4

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.. Option D is correct because 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 5

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to implement a security solution that prevents unauthorized access from the public internet while allowing access from specific Azure services and on-premises networks. Which feature should you configure?

Accepted Answer

Configure firewall rules and virtual network service endpoints.. Option C is correct because configuring firewall rules and virtual network service endpoints allows you to restrict access to your Azure Data Lake Storage Gen2 account to only traffic originating from specific virtual networks and trusted Azure services, while blocking all public internet traffic. This provides a network-level security boundary that meets the requirement of preventing unauthorized public access while permitting access from on-premises networks (via VPN/ExpressRoute) and specific Azure services.

Answer

Use private endpoints for all storage accounts.

Answer

Enable Microsoft Entra ID authentication only.

Answer

Use shared access signatures (SAS) with IP restrictions.

Question 6

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).. Option C is correct because 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 7

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.. Option D is correct because 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 8

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 9

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 10

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 11

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.. Option B is correct because 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 12

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 is a first-class, high-throughput ingestion method for dedicated SQL pools in Azure Synapse Analytics. It provides a simple and flexible way to load data from Azure Data Lake Storage Gen2 or Azure Blob Storage with built-in error handling, automatic schema inference, and support for various file formats (CSV, Parquet, ORC). Unlike PolyBase, the COPY statement does not require external tables and is optimized for performance with parallel loading.

Answer

BULK INSERT

Answer

Azure Data Factory Copy Activity

Answer

Azure Import/Export service

Question 13

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 a good distribution key minimizes data skew, ensuring that data is evenly distributed across all distributions. This prevents performance bottlenecks where some distributions handle a disproportionate amount of data or queries, which is critical for parallel processing in a dedicated SQL pool.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.

Question 14

Which TWO of the following Azure services can be used to orchestrate data pipelines that include data transformation?

Accepted Answer

Azure Synapse Pipelines. Azure Synapse Pipelines (option A) is correct because it provides a native orchestration service within Azure Synapse Analytics for building, scheduling, and monitoring data pipelines that include data transformation activities, such as Data Flow or Stored Procedure tasks. It leverages the same integration runtime as Azure Data Factory and supports both code-free and code-based transformation steps.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions

Question 15

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.. Option B is correct because 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.