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

Question 1

Drag and drop the steps to configure Azure Databricks auto-scaling cluster for ETL workloads into the correct order.

Accepted Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then select runtime version, then attach libraries, then set cluster policies.. First create the workspace, then the cluster with auto-scaling settings, choose runtime, attach libraries, and set policies.

Answer

Create Azure Databricks workspace, then select runtime version, then configure cluster auto-scaling settings, then attach libraries, then set cluster policies.

Answer

Configure cluster auto-scaling settings, then create Azure Databricks workspace, then select runtime version, then attach libraries, then set cluster policies.

Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then attach libraries, then select runtime version, then set cluster policies.

Question 2

Drag and drop the steps to implement Azure Data Lake Storage Gen2 lifecycle management to move data to cool and archive tiers into the correct order.

Accepted Answer

Create Azure Data Lake Storage Gen2 account, then define a lifecycle management rule with conditions and actions, then apply the rule to the account, then verify data movement.. After creating the account, define a lifecycle rule with conditions and actions, then apply it.

Answer

Define a lifecycle management rule, then create an Azure Data Lake Storage Gen2 account, then apply the rule, then verify data movement.

Answer

Create Azure Data Lake Storage Gen2 account, then apply a lifecycle management rule, then define the rule conditions and actions, then verify data movement.

Answer

Define a lifecycle management rule, then apply it to the account, then create the Azure Data Lake Storage Gen2 account, then verify data movement.

Question 3

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.

Question 4

Match each Azure security feature to its description.

Accepted Answer

Azure Active Directory: A cloud-based identity and access management service that provides authentication and authorization.. The correct matches are: Azure AD for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Azure AD with Managed Identity and Key Vault with RBAC.

Answer

Azure Active Directory: An automatically managed identity in Azure AD for applications to connect to resources without managing credentials.

Answer

Azure Key Vault: A service that provides role-based access control for Azure resources.

Question 5

Match each data storage format to its characteristic.

Accepted Answer

Parquet: Columnar storage format, efficient for analytics and compression. In Azure data engineering, Parquet and ORC are columnar formats ideal for analytics, while Avro is row-based with embedded schema, and JSON is a semi-structured text format. Common confusions involve mixing columnar and row-oriented characteristics.

Answer

JSON: Columnar storage optimized for compression

Answer

ORC: Row-oriented format with schema in each file

Question 6

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 7

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

Accepted Answer

Cool tier. The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Answer

Hot tier

Answer

Archive tier

Answer

Premium tier

Question 8

A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?

Accepted Answer

Change the distribution to hash on OrderDate. Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.

Answer

Change the distribution to round-robin

Answer

Replicate the table to all distributions

Answer

Create a columnstore index on OrderDate

Question 9

Which TWO Azure services can be used to implement a data lake architecture for storing structured, semi-structured, and unstructured data?

Accepted Answer

Azure Blob Storage with hierarchical namespace. Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Azure Blob Storage and adds a hierarchical namespace, enabling a true data lake architecture. It supports storing structured, semi-structured, and unstructured data at scale, with POSIX-like access control lists and directory-level operations. Azure Blob Storage with hierarchical namespace enabled is essentially the same service as ADLS Gen2, providing the same data lake capabilities.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB

Question 10

Which THREE factors should be considered when designing a partitioning strategy for a large fact table in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Data compression. Data compression is a key factor in partitioning strategy because it directly impacts storage efficiency and query performance. In Azure Synapse Analytics dedicated SQL pool, columnstore compression is applied per partition, and choosing the right compression level (e.g., COLUMNSTORE vs. COLUMNSTORE_ARCHIVE) can significantly reduce I/O and improve scan speeds for large fact tables.

Answer

Index type

Answer

Distribution key

Question 11

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

Accepted Answer

Parquet. Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Answer

Avro

Answer

CSV

Answer

JSON

Question 12

A company is migrating its on-premises SQL Server data warehouse to Azure Synapse Analytics. They have a fact table with 2 billion rows and 30 columns. The table is frequently joined on CustomerID and filtered on OrderDate. What is the recommended table design?

Accepted Answer

Hash-distribute on CustomerID and partition on OrderDate. Hash-distributing the fact table on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution node, which makes joins on CustomerID efficient by avoiding data movement. Partitioning on OrderDate enables partition elimination when filtering by date, reducing the amount of data scanned. This combination optimizes both the join and filter operations for a large fact table in Azure Synapse Analytics.

Answer

Replicate the table to all nodes

Answer

Round-robin distribution with partitions on OrderDate

Answer

Hash-distribute on OrderDate and partition on CustomerID

Question 13

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

Accepted Answer

Firewall and virtual network service endpoints. Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Answer

Azure Private Endpoint

Answer

Shared access signature (SAS)

Answer

Azure Defender for Storage

Question 14

A data engineer is designing a solution that uses Azure Data Factory to copy data from an on-premises SQL Server to Azure Synapse Analytics. The data transfer must be encrypted in transit. Which property should be configured in the linked service?

Accepted Answer

EncryptedConnection. The EncryptedConnection property in an Azure Data Factory linked service enforces encryption for data in transit between the on-premises SQL Server and Azure Synapse Analytics. When set to true, it uses TLS/SSL to encrypt the connection, ensuring that data transferred over the network is protected from interception or tampering.

Answer

ConnectionString with Integrated Security

Answer

AuthenticateVia

Answer

UseSystemTrustStore

Question 15

Which TWO are valid methods to load data into Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

PolyBase. PolyBase is a valid method for loading data into Azure Synapse Analytics dedicated SQL pool because it uses the T-SQL language to access and combine data from external sources like Azure Blob Storage or Azure Data Lake Store without needing to move the data first. The COPY statement is also valid as it provides a high-throughput, flexible ingestion mechanism that supports various file formats and error handling options directly via T-SQL.

Answer

BCp utility

Answer

Azure Data Factory Copy Activity

Answer

BULK INSERT