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

Question 1

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

Accepted Answer

Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.. Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Answer

Azure Data Factory mapping data flows.

Answer

Azure Cosmos DB Core (SQL) API.

Answer

Azure SQL Database with JSON columns.

Question 2

You are designing a data lake architecture for a healthcare company. The solution must support fine-grained access control at the file level, encryption at rest and in transit, and integration with Microsoft Purview for data lineage. Which storage solution should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2 (ADLS Gen2).. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with POSIX-like access control lists (ACLs) for fine-grained file-level permissions, supports encryption at rest (Azure Storage Service Encryption) and in transit (TLS 1.2+), and natively integrates with Microsoft Purview for automated data lineage and cataloging. This makes it ideal for healthcare scenarios requiring strict compliance and auditability.

Answer

Azure NetApp Files.

Answer

Azure Files.

Answer

Azure Blob Storage.

Question 3

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that some queries are slow due to high data movement. What should you do to minimize data movement for queries that join large fact tables?

Accepted Answer

Hash-distribute the fact tables on the join keys.. Hash-distributing the fact tables on the join keys ensures that rows with the same join key value are placed on the same distribution node. This eliminates the need to shuffle data across nodes during the join, minimizing data movement and improving query performance in Azure Synapse dedicated SQL pool.

Answer

Use round-robin distribution for all tables.

Answer

Partition both tables on the join keys.

Answer

Use replicated tables for all large fact tables.

Question 4

You need to store historical sales data for 10 years with infrequent queries. The storage cost must be minimized while retaining the ability to query using Azure Synapse serverless SQL pool. Which storage tier should you use?

Accepted Answer

Azure Storage Cool tier.. The Cool tier is the correct choice because it provides low-cost storage for data that is infrequently accessed (e.g., historical sales data spanning 10 years) while still supporting immediate read access via Azure Synapse serverless SQL pool. Unlike the Archive tier, Cool tier data is online and can be queried without the need for time-consuming rehydration, making it suitable for infrequent but on-demand analytical queries.

Answer

Azure Storage Archive tier.

Answer

Azure Storage Premium tier.

Answer

Azure Storage Hot tier.

Question 5

A financial services company needs to store transaction data for audit purposes. The data must be immutable and cannot be modified or deleted for 7 years. Which Azure storage feature should be used?

Accepted Answer

Azure Blob Storage immutability policy (time-based retention).. Azure Blob Storage immutability policy with time-based retention ensures that blobs cannot be modified or deleted for a specified retention period (e.g., 7 years). This meets the audit requirement for immutable storage by locking the data at the storage level, preventing any writes or deletes until the retention interval expires. The policy is enforced at the container level and applies to all blobs within, making it the correct choice for regulatory compliance.

Answer

Azure Blob Storage soft delete.

Answer

Azure Blob Storage versioning.

Answer

Azure Files share snapshots.

Question 6

Which TWO actions should you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool when working with large fact tables?

Accepted Answer

Create statistics on columns used in WHERE clauses.. Option C is correct because creating statistics on columns used in WHERE clauses gives the dedicated SQL pool's query optimizer accurate cardinality estimates, enabling better join orders and scan strategies for large fact tables. Option E is correct because partitioning a large fact table on a date column enables partition elimination, so queries filtering on that date range scan only relevant partitions instead of the entire table. Options A and B are incorrect: replicated distribution copies the full fact table to every compute node, which is meant for small dimension tables and would be prohibitively expensive for large fact tables, while round-robin distribution is a generic fallback that does not align data for joins and causes costly data movement. Option D is incorrect because clustered columnstore indexes are the recommended default for large fact tables in dedicated SQL pools, delivering high compression and fast analytical scans, whereas a clustered (rowstore) index is generally inferior for this workload.

Answer

Use replicated distribution for the fact table.

Answer

Use round-robin distribution to evenly distribute data.

Answer

Use clustered index instead of columnstore index.

Question 7

You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?

Accepted Answer

The credential used to access ADLS Gen2 does not have sufficient permissions.. The most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.

Answer

The serverless SQL pool does not support reading Parquet files.

Answer

The external table definition is missing the SCHEMA_NAME parameter.

Answer

The DATA_COMPRESSION setting is incompatible with Parquet files.

Question 8

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

Accepted Answer

Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).. Partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Answer

Partition by device ID first, then by date.

Answer

Partition by device ID only, with a separate directory for each device.

Answer

Partition by device type only, with a directory for each type.

Question 9

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

Accepted Answer

Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).. It aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Answer

Transition to cool tier after 30 days; delete after 7 years.

Answer

Transition to archive tier after 30 days; delete after 7 years.

Answer

Transition to cool tier after 30 days; transition to cool tier again after 365 days.

Question 10

Your company uses Azure Cosmos DB for NoSQL to store user profiles. The application frequently reads profiles by user ID (the partition key). Occasionally, the application needs to query by email address, which is not part of the partition key. What should you do to optimize the occasional queries by email?

Accepted Answer

Create a secondary (composite) index on the email field.. Creating a secondary index on the email field allows Azure Cosmos DB for NoSQL to efficiently serve queries filtering by email without scanning all partitions. Since email is not the partition key, a secondary index (specifically a composite index if needed for multi-field queries, or a single-field index) enables index-based lookup across all physical partitions, optimizing the occasional query without redesigning the data model.

Answer

Change the partition key to the email field.

Answer

Denormalize the data by storing a copy of the email in the partition key.

Answer

Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.

Question 11

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Partition switching can be used to quickly load data into a table.. Option A is correct because partition switching (ALTER TABLE ... SWITCH PARTITION) is a metadata-only operation that instantly moves a fully prepared staging table's partition into the target table, making it a fast way to load data. Option C is correct because in a clustered columnstore index each partition is stored as its own set of rowgroups, so partition boundaries also define rowgroup boundaries. Option E is correct because creating too many partitions (especially small ones) increases metadata overhead, causes rowgroup fragmentation, and degrades query performance due to reduced segment elimination efficiency. Option B is not correct because partitions and distributions are independent constructs; partitioning does not automatically align with the 60 distributions, and alignment must be managed explicitly. Option D is not correct because partitioning is supported on clustered columnstore, clustered rowstore, and heap tables in dedicated SQL pools, not only on clustered rowstore indexes.

Answer

Partitions are automatically aligned with distributions.

Answer

Partitioning is only supported on tables with clustered rowstore indexes.

Question 12

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

Accepted Answer

Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.. Enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Answer

Increase the provisioned RU/s on the container to handle both transactional and analytical queries.

Answer

Change the partition key to /date to optimize for analytical queries.

Answer

Create a materialized view using the change feed and store aggregated data in a separate container.

Question 13

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

Accepted Answer

Hash distribution on the most joined dimension key with clustered columnstore index. Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Answer

Round-robin distribution with clustered columnstore index

Answer

Replicated distribution with clustered columnstore index

Answer

Hash distribution on a dimension key with heap index

Question 14

You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?

Accepted Answer

Store data in columnar format (e.g., Parquet). Option A is correct because storing append-only IoT telemetry in a columnar format such as Parquet enables column pruning and compression, so time-range queries scan only the needed timestamp/value columns and consume far less I/O and storage, reducing cost. Option D is correct because partitioning the data by date (e.g., a partition per day or month) lets the query engine perform partition pruning, skipping entire partitions outside the requested time range, which dramatically improves time-range query performance and lowers scanned data volume. Option B is not appropriate because indexing every column on high-velocity append-only data adds heavy write and storage overhead while providing little benefit for range scans on time-series data. Option C does not belong because row-level security is an access-control feature, not a query-performance or cost optimization. Option E is not relevant because geo-redundant storage improves durability and disaster recovery across regions but does not optimize time-range query performance and typically increases cost.

Answer

Create indexes on all columns

Answer

Enable row-level security

Answer

Enable geo-redundant storage

Question 15

You are a data engineer for a multinational e-commerce company. The company uses Azure Synapse Analytics as its data warehouse. The current fact table, SalesFact, is distributed using hash distribution on the CustomerID column. It has 2 billion rows and is 2 TB in size. Recently, the business team has been running many queries that aggregate sales by product category and date, and these queries are experiencing high data movement and long execution times. The product dimension table (ProductDim) has 100,000 rows and is 100 MB. The date dimension table (DateDim) has 5,000 rows and is 5 MB. You need to redesign the storage to minimize data movement for these aggregation queries. You cannot change the fact table distribution key to ProductID because of other critical queries that rely on CustomerID. What should you do?

Accepted Answer

Replicate the ProductDim and DateDim tables to all compute nodes. Replicating small dimension tables (ProductDim at 100 MB and DateDim at 5 MB) to all compute nodes eliminates the need to shuffle these tables across nodes during joins. In Azure Synapse Analytics, replicated tables are copied to each distribution, so when the fact table (hash-distributed on CustomerID) joins with ProductDim and DateDim on ProductID and DateID, no data movement occurs for the dimension tables. This directly reduces the high data movement and long execution times for aggregation queries by product category and date.

Answer

Create materialized views on the fact table that aggregate by product category and date

Answer

Partition the fact table by date and keep the same distribution

Answer

Change the fact table distribution to round-robin and create non-clustered indexes on ProductID and DateID