DP-900 Describe core data concepts Practice Test 14 — 15 Questions

Question 1

A company's data engineering team uses Azure Data Factory to orchestrate a pipeline that ingests data from Azure Blob Storage, transforms it using Azure Databricks, and loads it into Azure Synapse Dedicated SQL Pool. The pipeline fails intermittently due to transient errors. Which pattern should they implement to improve reliability?

Accepted Answer

Configure retry policy with exponential backoff on activities. Option D is correct because configuring a retry policy with exponential backoff on the Azure Data Factory activities directly addresses transient errors (e.g., network blips, throttling) by automatically retrying the failed activity after increasing delays. This pattern is specifically designed for intermittent failures and is a built-in feature of Azure Data Factory, improving pipeline reliability without architectural changes.

Answer

Replace Azure Databricks with Azure Functions

Answer

Increase the pipeline timeout to 24 hours

Answer

Split the pipeline into multiple smaller pipelines

Question 2

An organization uses Azure SQL Database and needs to maintain a copy of the database for read-only reporting without affecting the production workload. Which feature should they use?

Accepted Answer

Active geo-replication. Active geo-replication (Option C) creates a readable secondary replica of an Azure SQL Database in a different Azure region. This secondary replica is continuously updated asynchronously from the primary and can be used for read-only query workloads, offloading reporting traffic without impacting the production database's performance or transaction throughput.

Answer

Azure SQL Database read replica

Answer

Automated backups

Answer

Failover groups

Question 3

A data analyst needs to combine sales data from Azure SQL Database and inventory data from Azure Cosmos DB into a single Power BI report. Which Power BI feature should they use?

Accepted Answer

Power Query. Power Query is the correct feature because it is the data connection and transformation engine in Power BI that allows you to connect to multiple data sources—such as Azure SQL Database and Azure Cosmos DB—and combine them into a single dataset for reporting. It provides a graphical interface to merge, append, and shape data from disparate sources before loading it into the data model, which is exactly what the analyst needs to do.

Answer

Power BI Desktop

Answer

DAX formulas

Answer

Dataflows

Question 4

A company uses Azure Data Lake Storage Gen2 for a data lake. They implement a folder structure with access control lists (ACLs). A new data scientist needs to read data from a specific folder but not write to it. Which ACL permission should be assigned?

Accepted Answer

Execute. Execute (X) permission on a folder in Azure Data Lake Storage Gen2 is required to traverse the folder and access its contents. Without Execute, a user cannot list or read files inside the folder, even if Read permission is granted. Since the data scientist only needs to read data (not write), assigning Execute on the folder and Read on the files allows traversal and read access without write capability.

Answer

Modify

Answer

Write

Answer

Read

Question 5

A retail company captures real-time clickstream data from its website. They need to store this data for immediate analysis using KQL. Which Azure service should they use?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is optimized for interactive analytics on large volumes of streaming and high-velocity data, supporting Kusto Query Language (KQL) for real-time queries. It ingests clickstream data with low latency and provides immediate analysis capabilities, making it the correct choice for this scenario.

Answer

Azure Stream Analytics

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 6

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Choose two.)

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides an Apache Spark-based analytics platform that can perform complex data transformations, such as ETL (Extract, Transform, Load) operations, using notebooks and clusters. It allows you to write code in Python, Scala, or SQL to transform data at scale, making it a core compute service for data transformation in a pipeline.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Event Hubs

Question 7

Which THREE factors should you consider when choosing between Azure SQL Database and Azure Cosmos DB for a new application? (Choose three.)

Accepted Answer

Schema flexibility. Option A is correct because Azure SQL Database requires a fixed relational schema, whereas Azure Cosmos DB is schema-agnostic and supports flexible, document-based data models. This makes Cosmos DB ideal for applications with evolving or unstructured data, while SQL Database suits strictly relational workloads.

Answer

Cost per GB

Answer

Maximum data size

Question 8

Which TWO data storage types are classified as structured data in Azure? (Choose two.)

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is a fully managed SQL Server database engine in Azure, which stores data in a relational schema with predefined tables, columns, and data types. This structured format enforces a rigid schema, making it a classic example of structured data storage in Azure.

Answer

Azure Cosmos DB

Answer

Azure Data Lake Storage

Answer

Azure Blob Storage

Question 9

The exhibit shows an ARM template snippet for deploying an Azure storage account. What is the redundancy level of the storage account?

Accepted Answer

Locally redundant storage (LRS). The ARM template snippet does not specify a 'sku.tier' or 'sku.name' property that would indicate geo-replication or zone-redundancy. By default, when no redundancy option is explicitly configured, Azure Storage accounts deploy with Locally redundant storage (LRS), which replicates data three times within a single datacenter in the primary region.

Answer

Read-access geo-redundant storage (RA-GRS)

Answer

Zone-redundant storage (ZRS)

Answer

Geo-redundant storage (GRS)

Question 10

The exhibit shows a T-SQL query against an Azure SQL Database. What is the purpose of the HAVING clause in this query?

Accepted Answer

To filter groups after aggregation. The HAVING clause is used in T-SQL to filter groups after the GROUP BY clause has performed aggregation. In this query, it restricts the result set to only those product categories whose total sales (SUM(Amount)) exceed 1000, which is a condition on the aggregated value, not on individual rows.

Answer

To sort the result set by TotalSales descending

Answer

To join two tables

Answer

To filter rows before grouping

Question 11

The exhibit shows a KQL query in Azure Data Explorer. What is the output of this query?

Accepted Answer

Top 5 states by total property damage. The KQL query uses `summarize` to aggregate total property damage by state, then `top 5 by total_property_damage` to return the five states with the highest total damage. The `desc` argument (default) orders the results in descending order, making option B correct.

Answer

Bottom 5 states by total property damage

Answer

All states with total property damage

Answer

All storm events after 2024-01-01

Question 12

You need to design a data storage solution for a global e-commerce application that must support ACID transactions and require minimal latency for point lookups by a unique key. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides global distribution with multi-region writes, guarantees ACID transactions through its transactional batch API, and offers single-digit millisecond latency for point reads by a unique key (e.g., id and partition key). This makes it ideal for a global e-commerce application requiring both strong consistency and low-latency lookups.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 13

A data engineer needs to process streaming data from IoT devices in near real-time and store the results in Azure Cosmos DB. Which Azure service should they use for the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for low-latency, near-real-time analytics on streaming data. It can ingest data from IoT devices via Event Hubs or IoT Hub, apply SQL-based transformations, and directly output the results to Azure Cosmos DB with millisecond latency, making it ideal for this scenario.

Answer

Azure Synapse Analytics

Answer

Azure Databricks

Answer

Azure Data Factory

Question 14

Your company stores sensitive customer data in Azure SQL Database. You need to implement column-level encryption for the 'SSN' column using a customer-managed key stored in Azure Key Vault. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct feature because it allows client-side encryption of sensitive columns, such as 'SSN', using a customer-managed key stored in Azure Key Vault. The encryption keys are never exposed to the database engine, ensuring that even database administrators cannot view the plaintext data. This meets the requirement for column-level encryption with customer-managed keys.

Answer

Azure Policy

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 15

A data analyst needs to create interactive dashboards and reports from data stored in Azure Synapse Analytics. Which tool should they use?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports from a wide range of data sources, including Azure Synapse Analytics. Power BI connects directly to Synapse SQL pools or serverless SQL endpoints using DirectQuery or import mode, enabling real-time visualizations and cross-filtering. This aligns with the requirement for interactive analytics, which is Power BI's core strength.

Answer

SQL Server Reporting Services (SSRS)

Answer

Microsoft Excel

Answer

Azure Data Studio