DP-900 Describe core data concepts Practice Test 13 — 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

A healthcare organization stores patient records in Azure Blob Storage and must comply with data retention policies that require deleting records after 7 years. They also need to prevent any modification or deletion of records before the retention period ends. Which Azure feature should they use?

Accepted Answer

Immutable storage with time-based retention policy. Immutable storage with a time-based retention policy (WORM – Write Once, Read Many) ensures that blobs cannot be modified or deleted until the retention period expires. This directly meets the dual requirement of preventing premature deletion while enforcing a 7-year retention, as the policy locks the data for the specified duration.

Answer

Azure Backup for Blob Storage

Answer

Soft delete for Blob Storage

Answer

Azure Blob Storage lifecycle management

Question 3

You need to design a real-time dashboard that displays the number of orders placed in the last hour from an e-commerce application. The application writes orders to Azure Event Hubs. Which Azure service should you use to aggregate the data and serve the dashboard with minimal latency?

Accepted Answer

Azure Stream Analytics with Power BI output. Azure Stream Analytics is purpose-built for real-time data processing from sources like Event Hubs, and its native integration with Power BI enables direct output to a dashboard with sub-second latency. This combination provides the minimal-latency aggregation and serving required for a real-time orders dashboard without additional infrastructure.

Answer

Azure Databricks Structured Streaming

Answer

Azure Analysis Services

Answer

Azure Data Factory with tumbling window

Question 4

A company wants to provide self-service analytics to business users who need to create reports and dashboards from data in Azure Synapse Analytics. Which tool should you recommend?

Accepted Answer

Power BI. Power BI is the correct tool because it is designed specifically for self-service analytics, enabling business users to create interactive reports and dashboards from data stored in Azure Synapse Analytics. Power BI connects directly to Synapse via its built-in connector, allowing users to build visualizations without writing code or relying on IT. This aligns with the requirement for business users to perform ad-hoc analysis and reporting.

Answer

Microsoft Excel

Answer

Azure Synapse Studio

Answer

Azure Data Studio

Question 5

Your organization stores sensitive financial data in Azure SQL Database. You need to audit all SELECT operations on the 'Transactions' table and alert the security team when a user outside the finance department queries the table. Which feature should you use?

Accepted Answer

SQL Server Auditing. SQL Server Auditing is the correct choice because it tracks database events, including SELECT operations, and writes them to an audit log. You can configure an audit policy to capture all SELECT statements on the 'Transactions' table and then set up an alert (e.g., via Azure Monitor or Logic Apps) that triggers when a user from outside the finance department executes such a query. This directly meets the requirement to both audit and alert on specific user actions.

Answer

Microsoft Defender for SQL

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Question 6

Which TWO of the following are common characteristics of a NoSQL database?

Accepted Answer

Flexible schema. Option A is correct because NoSQL databases, such as MongoDB or Cassandra, use a flexible schema that allows documents or records to have varying fields without requiring predefined table structures. This enables developers to iterate quickly and store semi-structured or unstructured data, such as JSON documents, without costly schema migrations.

Answer

Normalized data storage

Answer

Strong ACID transaction support

Answer

Relational data model

Question 7

Which THREE of the following are benefits of using a columnar storage format like Parquet for analytical workloads?

Accepted Answer

Reduced I/O when querying a subset of columns. Option B is correct because columnar storage formats like Parquet store data by column rather than by row. When a query only needs a subset of columns (e.g., SELECT SUM(sales) FROM table), the storage engine reads only the relevant column chunks from disk, dramatically reducing I/O compared to reading entire rows. This is a key performance advantage for analytical workloads that aggregate or filter on specific columns.

Answer

Enforced referential integrity constraints

Answer

Optimized for frequent row updates

Question 8

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 9

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 10

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 11

Refer to the exhibit. You are designing a fact table for a data warehouse. The table will store sales transactions with daily granularity. Which column would be most appropriate as the distribution column in a hash-distributed table in Azure Synapse Analytics?

Accepted Answer

CustomerKey. CustomerKey (B) is the most appropriate distribution column because it has high cardinality and is frequently used in joins with dimension tables, ensuring data is evenly distributed across distributions in Azure Synapse Analytics. A hash-distributed table requires a column with many unique values to avoid data skew, and CustomerKey is a natural key for sales transactions that meets this requirement.

Answer

SalesAmount

Answer

ProductKey

Answer

OrderDate

Question 12

A company stores IoT sensor data in Azure Blob Storage. Data scientists need to query the data using SQL without moving it to another store. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool allows you to query data directly from Azure Blob Storage using T-SQL without moving or copying the data. It uses a distributed query engine that reads files (Parquet, CSV, JSON) in place, making it ideal for ad-hoc analytics over IoT sensor data stored in Blob Storage.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Answer

Azure SQL Database

Question 13

A healthcare organization must store patient health records for 7 years to meet regulatory requirements. After 7 years, data must be deleted immediately. They use Azure Blob Storage. Which policy should they implement?

Accepted Answer

Time-based retention policy. A time-based retention policy (immutability policy) in Azure Blob Storage ensures that blobs are stored in a WORM (Write Once, Read Many) state for a specified period, preventing modification or deletion. After the retention period expires, the data can be deleted immediately, meeting the 7-year regulatory requirement. This policy is designed specifically for compliance scenarios where data must be preserved for a fixed duration and then removed.

Answer

Soft delete policy

Answer

Legal hold policy

Answer

Lifecycle management policy with deletion after 7 years

Question 14

A company uses Azure Cosmos DB for a globally distributed application. They need to ensure low-latency reads and writes for users in multiple regions. Which consistency level provides the strongest guarantees without sacrificing availability?

Accepted Answer

Bounded staleness. Bounded staleness provides the strongest consistency guarantee that still maintains availability during a partition. It ensures that reads are guaranteed to be within a configurable staleness window (either K versions or a time interval) from the latest write, which is stronger than consistent prefix or eventual consistency, while avoiding the availability trade-offs of strong consistency in a globally distributed Azure Cosmos DB account.

Answer

Consistent prefix

Answer

Strong

Answer

Eventual

Question 15

A marketing team needs to analyze customer purchase history data stored in Azure SQL Database. They want to create interactive dashboards with drill-down capabilities. Which Microsoft tool should they use?

Accepted Answer

Power BI. Power BI is the correct tool because it is designed specifically for creating interactive dashboards with drill-down capabilities using data from Azure SQL Database. It connects directly to Azure SQL Database via built-in connectors, allowing users to build visualizations that support hierarchical navigation and real-time filtering.

Answer

Azure Data Studio

Answer

Microsoft Excel

Answer

Azure Analysis Services