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

Question 1

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 2

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 3

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

Accepted Answer

Flexible schema. Option A (Flexible schema) is correct because NoSQL databases such as document stores (MongoDB), key-value stores (Redis), and wide-column stores (Cassandra) do not enforce a fixed table schema, allowing each record to have different fields and structures. Option E (Horizontal scaling) is correct because NoSQL systems are designed to scale out by distributing data across many commodity nodes via sharding and partitioning, rather than scaling up on a single server. Option B (Normalized data storage) is incorrect because NoSQL databases typically denormalize data, embedding related data in a single document or row to avoid joins. Option C (Strong ACID transaction support) is incorrect as a general characteristic because many NoSQL databases follow BASE (Basically Available, Soft state, Eventual consistency) and offer only limited or eventual consistency, though some now add ACID features. Option D (Relational data model) is incorrect because NoSQL databases use non-relational models such as document, key-value, graph, or column-family, not tables with foreign-key relationships.

Answer

Normalized data storage

Answer

Strong ACID transaction support

Answer

Relational data model

Question 4

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 formats like Parquet store each column contiguously, so a query that references only a subset of columns reads only those column chunks rather than full rows, dramatically reducing I/O. Option D is correct because values within a single column share the same data type and tend to be similar, which allows efficient encoding schemes (e.g., dictionary, run-length, delta encoding) and yields much better compression ratios than row-oriented storage. Option E is correct because Parquet stores per-row-group and per-column-chunk statistics (min/max, null counts), enabling predicate pushdown so the engine can skip row groups and column chunks that cannot satisfy the filter. Option A is not a benefit of Parquet: it is a file format, not a database, and it does not enforce referential integrity constraints such as foreign keys. Option C is also wrong because columnar layouts are optimized for bulk scans and aggregations, not frequent single-row updates; row-oriented stores handle point updates far better.

Answer

Enforced referential integrity constraints

Answer

Optimized for frequent row updates

Question 5

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 6

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 7

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 8

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 9

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 10

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 11

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

Question 12

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. 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 13

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 14

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 15

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