DP-900 Practice Test 25 — 15 Questions

Question 1

Your team is migrating a data warehouse to Azure Synapse Analytics. You need to ensure that the data model supports both historical trend analysis and current-day reporting with minimal storage redundancy. Which table design pattern should you use?

Accepted Answer

Star schema with dimension and fact tables. The star schema is the correct choice because it separates business processes into fact tables (for measures like sales quantities) and dimension tables (for descriptive attributes like customer or date). This design directly supports both historical trend analysis (by joining facts with the date dimension) and current-day reporting (by filtering on the latest date) while minimizing storage redundancy through normalized dimensions. Azure Synapse Analytics is optimized for star schemas, leveraging columnstore indexes and distributed tables to accelerate such queries.

Answer

Single flat table containing all attributes

Answer

Wide table with repeated customer attributes per order

Answer

Highly normalized design with many tables

Question 2

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 3

Your organization uses Microsoft Fabric to build a data lakehouse. Data engineers need to transform data using Spark and store results in Delta Lake format. Which Fabric component should they use?

Accepted Answer

Notebooks. Notebooks in Microsoft Fabric provide an interactive environment for writing and executing Spark code, which is required for transforming data using Spark. The results can be directly written to Delta Lake format, making Notebooks the correct component for this task.

Answer

Dataflows Gen2

Answer

Pipelines

Answer

Semantic models

Question 4

Which TWO of the following Azure services are categorized as Platform as a Service (PaaS) for data storage?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a fully managed NoSQL database service that provides turnkey global distribution, elastic scaling, and multi-model support (document, key-value, graph, column-family). As a PaaS offering, it abstracts infrastructure management—such as hardware provisioning, patching, and replication—allowing developers to focus on data modeling and application logic.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Lake Storage Gen2

Answer

Azure Virtual Machines with SQL Server

Question 5

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 6

A company wants to run SQL queries on data stored in Azure Cosmos DB for NoSQL. Which API should they use?

Accepted Answer

Core (SQL) API. The Core (SQL) API is the native API for Azure Cosmos DB for NoSQL, designed to query JSON documents using a SQL-like syntax. Since the requirement is to run SQL queries on data stored in Azure Cosmos DB for NoSQL, this API directly supports that need without requiring any protocol translation or schema mapping.

Answer

Gremlin API

Answer

Cassandra API

Answer

MongoDB API

Question 7

Refer to the exhibit. You are reviewing an ARM template for a new storage account. The storage account will store data that must be accessible from any Azure region and must be highly durable. Which change should you make to the template?

Accepted Answer

Change the SKU name to Standard_GRS. Standard_GRS (Geo-Redundant Storage) is the correct SKU because it replicates data synchronously three times within a primary region and asynchronously to a secondary region hundreds of miles away, ensuring high durability (11 nines) and accessibility from any Azure region via read-access (RA-GRS). The requirement for data to be accessible from any Azure region and highly durable aligns with GRS's geo-replication, whereas LRS only replicates within a single datacenter and Premium_LRS is for low-latency workloads, not geo-accessibility.

Answer

Set supportsHttpsTrafficOnly to false

Answer

Change the SKU name to Premium_LRS

Answer

Change the kind to BlobStorage

Question 8

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 9

You are reviewing an ARM template for an Azure SQL Database deployment. What is the maximum size of the database?

Accepted Answer

5 GB. The ARM template for an Azure SQL Database deployment specifies the database size based on the selected service tier and performance level. For the Basic tier, the maximum database size is 5 GB, which is the correct answer. This is a fixed limit for Basic tier databases, while higher tiers like Standard or Premium support larger sizes.

Answer

50 GB

Answer

5 MB

Answer

500 GB

Question 10

Refer to the exhibit. You are analyzing storm event data in Azure Data Explorer. The KQL query returns the top 5 event types by count in Texas. However, the results show event types with very low counts (e.g., 'Volcanic Ash' with 2 events). What is the most likely reason for this?

Accepted Answer

The state filter is case-sensitive and the actual state value differs.. Option B is correct because the StormEvents sample table contains data from multiple states, but the filter for 'TEXAS' may be case-sensitive or the state might be stored as 'Texas' or 'tx'. If the case does not match exactly, the filter returns few or no rows, leading to unexpected results. Option A is wrong because the 'take' operator limits rows, not aggregates. Option C is wrong because summarizing by EventType should work regardless of state. Option D is wrong because the query is syntactically correct.

Answer

The query has a syntax error.

Answer

The 'take' operator limits the number of events, not the number of types.

Answer

The 'summarize' operator cannot be used with 'where'.

Question 11

Refer to the exhibit. You are analyzing a Kusto query in Azure Data Explorer. The query is intended to return the top 5 event types that caused the most property damage in Florida. However, the query returns an error. What is the most likely cause?

Accepted Answer

The table or column names are incorrect.. The query returns an error because the table or column names referenced in the query do not match the actual schema in Azure Data Explorer. In Kusto Query Language (KQL), if a table name like 'Events' or a column like 'PropertyDamage' does not exist in the database, the query will fail with a 'semantic error' indicating an unknown table or column. This is the most likely cause given that the query logic (where, summarize, top) is syntactically correct.

Answer

The where clause must specify a numeric value.

Answer

The summarize operator cannot use sum aggregation.

Answer

The top operator requires an order by clause.

Question 12

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 13

An e-commerce company uses Azure Cosmos DB for its product catalog. They need to ensure that read requests are served from the nearest Azure region to reduce latency. Which feature should they use?

Accepted Answer

Cosmos DB multi-region writes. Cosmos DB multi-region writes (correctly referred to as multi-region reads in this context) allows you to configure your database account to be read from multiple Azure regions, enabling the SDK to automatically route read requests to the nearest region based on the client's location. This reduces latency by serving reads from a local replica without requiring a separate global load-balancing service.

Answer

Azure Front Door

Answer

Azure Content Delivery Network

Answer

Microsoft Traffic Manager

Question 14

A retail company uses Azure SQL Database to store transactional data. They need to ensure that reporting queries do not impact the performance of the transactional workload. Which solution should you recommend?

Accepted Answer

Configure a read replica in Azure SQL Database. A read replica in Azure SQL Database allows reporting queries to be offloaded to a read-only copy of the database, isolating them from the primary transactional workload. This ensures that reporting activities do not consume resources (CPU, IO, memory) on the primary instance, preventing performance degradation for transactional operations.

Answer

Increase the DTU or vCore limit of the database

Answer

Add indexes to the reporting tables

Answer

Partition the largest tables by date

Question 15

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