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

Question 1

You are designing a data storage solution for a financial services company. They need to store large volumes of structured transaction data in Azure Data Lake Storage Gen2 (ADLS Gen2). The data will be accessed by Azure Synapse Analytics and Azure Databricks. The company requires that data be organized into a hierarchical folder structure and that access permissions be enforced at the folder level. They also need to minimize storage costs for data that is infrequently accessed after 90 days. What should you recommend?

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

Accepted Answer

Enable hierarchical namespace on the storage account and use Azure Blob Storage access tiers with lifecycle management policies to move data to Cool tier after 90 days.. ADLS Gen2 with hierarchical namespace enables folder-level ACLs, which is essential for granular access control in a hierarchical structure. Lifecycle management policies automatically transition data to cooler tiers based on access patterns, reducing storage costs for infrequently accessed data. Together, these features meet the financial company's requirements for organization, security, and cost efficiency.

Answer

Use Azure Blob Storage with flat namespace and configure Azure RBAC roles for folder-level access.

Answer

Use Azure Data Lake Storage Gen1 with folder-level ACLs and manually move data to Cool tier after 90 days.

Answer

Enable hierarchical namespace on the storage account and use Azure Files shares with quotas to manage access.

Question 2

A healthcare organization stores patient records in Azure SQL Database. They need to implement a solution that automatically archives records older than five years to Azure Blob Storage while ensuring that the archived data remains queryable using T-SQL without restoring the database. The solution must minimize costs and administrative effort. What should you implement?

Accepted Answer

Use Azure Synapse Analytics serverless SQL pool to query Parquet files in Blob Storage and automate archiving with Azure Data Factory.. Azure Synapse Analytics serverless SQL pool enables querying of external data in Blob Storage using T-SQL, which satisfies the requirement for queryable archived data without restoration. Azure Data Factory automates the archival process, reducing manual effort. This approach is cost-effective because serverless SQL pool charges only for queries executed, and Blob Storage is inexpensive for long-term retention.

Answer

Configure Azure SQL Database elastic queries to access the archived data in Blob Storage.

Answer

Use Azure SQL Database external tables with PolyBase to query data in Blob Storage.

Answer

Implement Azure SQL Database external tables with OPENROWSET to query Parquet files in Blob Storage.

Question 3

You are designing an Azure Data Lake Storage Gen2 solution to store customer transaction files. The files are written once and never modified, and they must be retained for seven years for regulatory compliance. Cost is a primary concern, and the data is rarely accessed after the first 30 days. What should you do to minimize storage costs while meeting the retention requirement?

Accepted Answer

Store files in the cool tier for 30 days, then move them to the archive tier for the remaining retention period.. A lifecycle management policy that transitions data from cool to archive tier after 30 days balances the need for short-term accessibility with long-term cost efficiency. The cool tier handles the initial period when data may still be read, and the archive tier provides the lowest storage cost for the remaining years, satisfying both cost and compliance requirements.

Answer

Store files in the hot tier for 30 days, then delete them and rely on backups for compliance.

Answer

Store all files in the archive tier immediately upon creation.

Answer

Store all files in the hot access tier for the entire seven years.

Question 4

You are designing a storage layer in Azure Data Lake Storage Gen2 for a media company that ingests 200 GB of video files daily. Analysts query only the most recent 30 days frequently, while older files are accessed about twice a year for compliance audits. You need to minimize storage costs while keeping the historical files immediately available without rehydration delays. What should you implement?

Accepted Answer

Configure an Azure Blob Storage lifecycle management policy that moves blobs older than 30 days to the Cool tier and older than 180 days to the Cold tier.. The Cold access tier provides lower storage cost than Hot or Cool while keeping data online and immediately readable, which matches the requirement that historical video files remain instantly available for occasional compliance audits. Lifecycle management policies can automatically transition blobs based on last-modified or last-accessed time, so no manual movement is needed. Archive would be cheaper but introduces rehydration delay, and soft delete or folder organization does not change billing tiers.

Answer

Enable soft delete on the storage account and set the retention period to 180 days for all containers holding the video files.

Answer

Configure an Azure Blob Storage lifecycle management policy that moves blobs older than 30 days to the Cool tier and older than 180 days to the Archive tier.

Answer

Enable hierarchical namespace on the storage account and move older files into a separate directory named archive.

Question 5

You are designing a data storage solution for a financial services company. They need to store large volumes of structured transaction data in Azure Data Lake Storage Gen2 (ADLS Gen2). The data will be accessed by Azure Synapse Analytics and Azure Databricks. The company requires that data be organized into a hierarchical folder structure and that access permissions be enforced at the folder level. They also need to minimize storage costs for data that is infrequently accessed after 90 days. What should you recommend?

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

Accepted Answer

Use the filepath() function in the OPENROWSET query to filter on the date partition columns.. Serverless SQL pools in Azure Synapse Analytics can query partitioned Parquet data efficiently by using the filepath() function to expose partition columns and filter on them. This causes the engine to skip irrelevant files, reducing data scanned and query cost. Partitioned views and hints are not supported in serverless SQL pools, and converting to CSV would increase I/O and cost.

Answer

Create an external table with a partition elimination hint in the serverless SQL pool.

Answer

Create a partitioned view in the serverless SQL pool that references the Parquet folder structure.

Answer

Convert the Parquet files to CSV format to enable predicate pushdown.

Question 6

A logistics company stores shipment tracking events in Azure Data Lake Storage Gen2. The data is used by an Azure Databricks job that reads the files and performs transformations. The company wants to ensure that the data can be accessed with the highest possible throughput and lowest latency. Which storage tier should they use?

Accepted Answer

Hot tier. The hot tier is designed for data that is accessed frequently, offering the lowest latency and highest throughput. Since the Databricks job actively reads and transforms the shipment events, the data is frequently accessed and requires fast performance. Cool and archive tiers are for infrequent access and would add latency and access costs. Premium performance tier is not a valid tier for Data Lake Storage Gen2.

Answer

Premium performance tier

Answer

Archive tier

Answer

Cool tier

Question 7

You are designing a data storage solution for a financial services company. They need to store large volumes of structured transaction data in Azure Data Lake Storage Gen2 (ADLS Gen2). The data will be accessed by Azure Synapse Analytics and Azure Databricks. The company requires that data be organized into a hierarchical folder structure and that access permissions be enforced at the folder level. They also need to minimize storage costs for data that is infrequently accessed after 90 days. What should you recommend?

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

Accepted Answer

Use a hash-distributed table with ProductCategory as the distribution column.. Using a hash-distributed table with ProductCategory as the distribution column colocates rows with the same value, reducing data movement for joins and aggregations on that column. A clustered columnstore index provides efficient storage and query performance for analytical workloads. Round-robin and replicated tables do not minimize data movement for operations on a specific column, and partitioning on a high-cardinality column is not recommended.

Answer

Use a replicated table.

Answer

Partition the table on ProductCategory.

Answer

Use a round-robin distributed table.

Question 8

You are designing a storage solution for a global retail company that uses Azure Cosmos DB for its product catalog. The catalog contains millions of items, and queries are performed using multiple attributes such as category, price range, and brand. The workload is read-heavy, and you need to minimize RU consumption for these queries while ensuring low latency. Which indexing policy should you implement?

Accepted Answer

Create composite indexes on category, price, and brand, and include them in the indexing policy.. Composite indexes on category, price, and brand allow Azure Cosmos DB to efficiently serve queries that filter or sort on these attributes, reducing RU consumption and latency. This is optimal for read-heavy workloads with multi-attribute queries, as it avoids full scans and leverages index seeks.

Answer

Include only the partition key in the index and exclude all other paths.

Answer

Use the default indexing policy that indexes all properties.

Answer

Exclude all paths from the index and rely on full scans.

Question 9

You are designing a data storage solution for a financial services company. They need to store large volumes of structured transaction data in Azure Data Lake Storage Gen2 (ADLS Gen2). The data will be accessed by Azure Synapse Analytics and Azure Databricks. The company requires that data be organized into a hierarchical folder structure and that access permissions be enforced at the folder level. They also need to minimize storage costs for data that is infrequently accessed after 90 days. What should you recommend?

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

Accepted Answer

Move blobs to Cool after 30 days, then to Archive after 90 days.. The access pattern shows frequent access for 30 days, infrequent for the next 60, and rare after 90 days. Moving to Cool after 30 days reduces storage costs for infrequent access, and moving to Archive after 90 days provides the lowest storage cost for long-term retention. This policy balances cost and availability while meeting compliance needs.

Answer

Move blobs to Cool after 30 days, then to Cold after 90 days.

Answer

Keep blobs in Hot tier for 90 days, then move to Archive.

Answer

Move blobs to Archive after 30 days, then delete after 90 days.

Question 10

You are designing a storage solution in Azure Synapse Analytics dedicated SQL pool for a sales data warehouse. You need to choose distribution and indexing strategies that minimize data movement for large fact tables joined to smaller dimension tables. Which two actions should you take? (Choose two.)

Accepted Answer

Replicate small dimension tables to all distributions.. Hash distributing the fact table on the most common large-dimension join key colocates matching rows, enabling collocated joins without shuffling the fact table. Replicating small dimensions puts copies on every distribution so those joins happen locally. Together these strategies minimize data movement for the fact-to-dimension joins that dominate the workload.

Answer

Partition the fact table by date and use a nonclustered index on the join key.

Answer

Create a clustered columnstore index on the fact table and a heap on all dimension tables.

Answer

Use a round-robin distribution for the fact table to ensure even data spread.

Question 11

A financial services firm stores trade records in an Azure Data Lake Storage Gen2 account. Regulatory policy requires that records be immutable for seven years, and the security team must be able to prove that no record was altered or deleted during that period, even by a storage account owner. You need to implement a compliance control that satisfies the auditors. What should you configure?

Accepted Answer

Create a time-based retention policy on the container and lock the policy so that it cannot be deleted or shortened.. Immutable storage for Azure Blob Storage provides WORM protection through time-based retention policies and legal holds. Only a locked time-based retention policy guarantees that blobs cannot be deleted or modified for the specified interval and that the policy itself cannot be weakened, which is what regulators require as proof of non-alteration. Soft delete, versioning, and customer-managed keys address recovery and confidentiality but do not establish immutability.

Answer

Enable soft delete on the storage account with a seven-year retention period and enable blob versioning.

Answer

Configure a customer-managed key in Azure Key Vault and rotate the key annually for the storage account.

Answer

Create a time-based retention policy on the container and lock the policy with a legal hold flag set to unlocked.

Question 12

A logistics company uses Azure Data Lake Storage Gen2 to store delivery route data. The data is organized in a hierarchical structure by year/month/day. The company needs to grant a team of analysts read-only access to only the data for the current year, while ensuring they cannot access historical data. The analysts use Azure Synapse Analytics serverless SQL pool to query the data. What should you implement?

Accepted Answer

Use POSIX ACLs on the directory structure to grant read permissions to the current year folder only.. POSIX ACLs in ADLS Gen2 provide directory-level access control, allowing you to grant read permissions on the current year's folder while denying access to historical data. This satisfies the requirement for fine-grained authorization that integrates with Synapse serverless SQL pool's ability to read data using the caller's identity.

Answer

Assign the Storage Blob Data Reader role to the analysts at the storage account level.

Answer

Configure a firewall rule to allow access only from the analysts' IP addresses.

Answer

Create a shared access signature (SAS) token scoped to the current year's container.

Question 13

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be encrypted at rest and in transit, and access must be audited. The company also requires that specific columns containing personally identifiable information (PII) be protected even from users with read access to the files. Which feature should they implement?

Accepted Answer

Azure Synapse Analytics column-level encryption. Column-level encryption in Azure Synapse Analytics encrypts individual columns with a separate key, so users with read access to the table cannot view the encrypted data without the key. This provides protection beyond file-level access controls. Storage Service Encryption and POSIX ACLs operate at the file or account level, and Dynamic Data Masking is not applicable to Data Lake Storage Gen2 files. Column-level encryption also supports auditing.

Answer

Azure SQL Database Dynamic Data Masking

Answer

Azure Storage Service Encryption with customer-managed keys

Answer

Azure Data Lake Storage Gen2 POSIX ACLs

Question 14

A logistics company stores delivery route data in Azure Data Lake Storage Gen2. The data is organized in a hierarchical folder structure by year, month, and day. Analysts need to query only the last 7 days of data frequently, while older data is queried occasionally. You need to optimize query performance and minimize cost for queries against the last 7 days. What should you do?

Accepted Answer

Create a partitioned external table in a serverless SQL pool that includes only the last 7 days of data.. Creating a partitioned external table that includes only the last 7 days of data ensures that queries scan only the relevant partition, minimizing data processed and cost in a serverless SQL pool. This leverages partition elimination and aligns with the requirement to optimize performance and cost for frequent queries on recent data.

Answer

Move the last 7 days of data to a separate storage account and query it with a serverless SQL pool.

Answer

Create a view that filters the last 7 days of data from a full external table.

Answer

Use a dedicated SQL pool and load only the last 7 days of data into a table.

Question 15

You are designing a storage solution for a company that requires a hierarchical namespace in Azure Data Lake Storage Gen2. The company needs to store and analyze petabytes of data using Azure Synapse Analytics. You need to ensure that the storage account supports the required features. Which storage account type should you use?

Accepted Answer

General-purpose v2 with premium performance tier. Azure Data Lake Storage Gen2 requires a storage account that supports hierarchical namespace. General-purpose v2 accounts with premium performance tier support this feature and provide the performance needed for large-scale analytics with Azure Synapse Analytics. Premium block blob and standard Blob Storage accounts do not support hierarchical namespace.

Answer

General-purpose v2 with standard performance tier

Answer

Azure Blob Storage with standard performance tier

Answer

Premium block blob storage account