DP-900 Practice Exam 54 — 20 Questions

Question 1

A data engineering team needs to analyze petabytes of historical sales data stored in Azure Data Lake Storage Gen2. They require the ability to run complex SQL queries that join multiple tables and need high performance. The solution must separate compute from storage to allow independent scaling of resources. Which Azure service should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is designed for petabyte-scale data warehousing, providing massively parallel processing (MPP) to run complex SQL queries across multiple tables with high performance. It separates compute from storage, allowing independent scaling of compute resources without moving data, which aligns with the requirement for decoupled scaling.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Question 2

A social media application uses Azure Cosmos DB to store user posts. When a user publishes a new post, they immediately refresh their feed and expect to see their own post right away. However, the application can tolerate temporary staleness for posts from other users. Which Azure Cosmos DB consistency level should the app use for the read operations that display the feed?

Accepted Answer

Session. Session consistency guarantees monotonic reads, writes, and read-your-writes within a single client session. Because the user expects to see their own post immediately after publishing, but can tolerate staleness for others' posts, Session consistency provides the exact guarantee needed: the user's own writes are immediately visible to them, while other users' posts may be slightly stale.

Answer

Strong

Answer

Bounded staleness

Answer

Eventual

Question 3

A financial services company runs critical end-of-day reports in an Azure Synapse Analytics dedicated SQL pool. These reports require guaranteed resource allocation and must complete within a fixed time window. However, ad-hoc analytical queries from data scientists often consume resources, causing contention and delaying the critical reports. Which feature should the company implement to ensure the critical reports always receive sufficient resources?

Accepted Answer

A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.. Workload groups in Azure Synapse Analytics dedicated SQL pool allow you to assign a minimum percentage of resources (e.g., CPU and memory) to a specific workload, ensuring guaranteed resource allocation. By setting high importance for the critical reports, the system prioritizes them over ad-hoc queries, preventing resource contention and ensuring they complete within the fixed time window.

Answer

B. Enable result set caching on all queries to reduce execution time.

Answer

C. Implement materialized views for the aggregations used in the critical reports.

Answer

D. Use hash distribution for the fact tables to improve query parallelism.

Question 4

An e-commerce application uses Azure SQL Database and stores user session data in a table called Sessions. The table contains millions of rows and queries often filter by UserID and LastActivityTime. The development team wants to improve query performance for these filters. What should they implement?

Accepted Answer

Create a nonclustered index on UserID and LastActivityTime. A nonclustered index on UserID and LastActivityTime allows the database engine to quickly locate rows matching the filter criteria without scanning the entire table. This index covers the two columns most frequently used in WHERE clauses, significantly reducing I/O and improving query performance for the e-commerce application's session data.

Answer

Create a clustered index on the SessionID column

Answer

Create a view that filters the data

Answer

Partition the table by month

Question 5

A manufacturing company ingests a continuous stream of sensor data from factory equipment into Azure Event Hubs. Additionally, historical maintenance data in CSV format is stored in Azure Data Lake Storage Gen2. The analytics team needs to join the streaming sensor data with the historical data in near real-time and enable analysts to query the combined dataset using standard T-SQL without moving the data. Which Azure service should they use as the primary analytics platform?

Accepted Answer

B) Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool. Azure Synapse Analytics with Synapse Pipelines and serverless SQL pool is the correct choice because it can ingest streaming data from Event Hubs via pipelines, query historical CSV data in Data Lake Storage Gen2 directly using T-SQL without moving it, and join both datasets in near real-time using the serverless SQL pool's ability to reference external data sources. This meets the requirement for standard T-SQL queries on combined streaming and historical data without data movement.

Answer

A) Azure Stream Analytics

Answer

C) Azure SQL Database

Answer

D) Azure Databricks

Question 6

A data analyst needs to create a real-time dashboard in Power BI that displays streaming data from Azure Event Hubs. The data must be refreshed every second. Which Power BI feature should they use?

Accepted Answer

Streaming dataset. A is correct because Power BI's streaming dataset feature is specifically designed to handle real-time data ingestion and visualization with sub-second latency. It supports direct integration with Azure Event Hubs, allowing the dashboard to refresh every second without the need for scheduled refresh or query-based retrieval.

Answer

Import mode with scheduled refresh

Answer

DirectQuery

Answer

Power BI Dataflows

Question 7

Refer to the exhibit. You are reviewing an Azure Resource Manager template for a Blob Storage container named 'sales'. The container has versioning enabled. A developer accidentally overwrites a blob. What is the simplest way to recover the previous version?

Accepted Answer

Access the previous version through the version list and restore it. Azure Blob Storage versioning automatically maintains a history of blob versions. When a blob is overwritten, the previous version is preserved and can be accessed via the version list. The simplest recovery method is to promote the previous version to the current version, which restores the blob to its prior state without needing additional services or configurations.

Answer

Use blob soft delete to recover the blob

Answer

Restore from a backup using Azure Backup

Answer

Perform a point-in-time restore of the container

Question 8

A data engineer needs to implement a solution that provides near real-time analytics on clickstream data. The data arrives as JSON events and must be queryable with sub-second latency using SQL-like queries. The solution should minimize operational overhead. Which Azure service should they use?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for interactive analytics on large volumes of streaming and historical data with sub-second query latency using Kusto Query Language (KQL), which supports SQL-like syntax. It natively ingests JSON events, provides near real-time analytics, and minimizes operational overhead as a fully managed, serverless service.

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Answer

Azure Synapse Analytics

Question 9

Your organization uses Azure SQL Database and needs to ensure that all customer data is encrypted at rest and in transit with minimal administrative overhead. Which solution should you recommend?

Accepted Answer

Enable Transparent Data Encryption (TDE) and enforce TLS 1.2 for connections.. Transparent Data Encryption (TDE) encrypts Azure SQL Database data files at rest without requiring any application changes, and enforcing TLS 1.2 ensures all data in transit is encrypted using a strong, industry-standard protocol. This combination meets the requirement for encryption at rest and in transit with minimal administrative overhead, as TDE is managed by the platform and TLS enforcement is a simple server-level setting.

Answer

Use Microsoft Purview Information Protection to label and encrypt the data.

Answer

Implement Dynamic Data Masking on the customer table.

Answer

Enable Always Encrypted for all sensitive columns and use client-side encryption.

Question 10

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 11

Match each Azure Cosmos DB API to its supported data model.

Accepted Answer

SQL API: Document. Azure Cosmos DB APIs map to specific data models: SQL and MongoDB for document, Cassandra for wide-column, Gremlin for graph, and Table for key-value. Common confusions involve misassigning Gremlin and Table.

Answer

Gremlin API: Key-value

Answer

Table API: Graph

Question 12

A data analyst needs to run ad-hoc SQL queries on large volumes of data stored as Parquet files in Azure Data Lake Storage Gen2. The queries are unpredictable, and the analyst wants to pay only for the compute resources consumed by each query. Which Azure Synapse Analytics compute model should be used?

Accepted Answer

Serverless SQL pool. Serverless SQL pool is the correct choice because it allows running ad-hoc SQL queries directly on data in Azure Data Lake Storage Gen2 without provisioning any fixed compute resources. It uses a pay-per-query billing model, charging only for the amount of data processed by each query, which aligns perfectly with the unpredictable query patterns described.

Answer

Dedicated SQL pool

Answer

Apache Spark pool

Answer

Azure Data Explorer pool

Question 13

Match each Azure data consistency model to its description.

Accepted Answer

Strong: Reads always return the most recent write.. Azure Cosmos DB offers five consistency levels: strong, bounded staleness, session, consistent prefix, and eventual. Strong returns the most recent write; bounded staleness allows a bounded lag; session ensures per-session guarantees.

Answer

Strong: Reads may lag behind writes by up to 5 seconds.

Answer

Session: Guarantees that all replicas have the same data at all times.

Question 14

A data engineering team needs to transform raw clickstream data stored as Parquet files in Azure Data Lake Storage Gen2. They want to use standard T-SQL queries to perform transformations and aggregations. The team prefers a serverless option to avoid provisioning and managing dedicated compute resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows querying Parquet files in Azure Data Lake Storage Gen2 using standard T-SQL without provisioning any dedicated compute resources. It automatically scales compute based on query demand, making it ideal for ad-hoc transformations and aggregations on raw data with a serverless, pay-per-query model.

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure Databricks

Answer

Azure HDInsight

Question 15

Which THREE factors should be considered when designing a relational database in Azure to minimize latency for globally distributed users?

Accepted Answer

Configure read replicas. Geo-replication provides read replicas in multiple regions. Read-replica configurations offload read traffic. Selecting a tier with higher IOPS ensures sufficient throughput. Horizontal partitioning (sharding) adds complexity and may increase latency for cross-shard queries. Columnstore indexes are for analytics, not latency reduction.

Answer

Implement horizontal partitioning (sharding)

Answer

Use columnstore indexes

Question 16

A company uses Azure Synapse Analytics to run complex queries against large datasets stored in Parquet files in Azure Data Lake Storage Gen2. They notice that queries scanning entire partitions are slow due to high I/O overhead on the compute nodes. Investigation shows each daily partition contains thousands of small files (under 1 MB each). Which optimization should be implemented first to improve query performance?

Accepted Answer

Compact small files into larger ones before querying. The high I/O overhead is caused by the thousands of small files per partition. When Synapse compute nodes read many small files, the overhead of opening, reading metadata, and closing each file dominates, even though the total data volume is small. Compacting these small files into fewer, larger files (e.g., 128 MB or more) reduces the number of file operations, improves read throughput, and allows more efficient predicate pushdown and parallelism.

Answer

Increase the number of compute nodes

Answer

Use columnstore indexes on external tables

Answer

Change the partition column to a different date granularity

Question 17

You are designing a solution that requires storing large binary files (up to 5 TB each) that are updated frequently by multiple processes. Which Azure storage feature allows concurrent writes to the same file?

Accepted Answer

Azure Files. Azure Files supports SMB protocol with leasing and oplocks, allowing multiple clients to read/write the same file with proper coordination. Blob Storage does not support concurrent writes to the same blob without custom logic. Azure Disks are for VM disks, not shared access. Azure NetApp Files provides shared file access but is more expensive and complex.

Answer

Azure Blob Storage with soft delete

Answer

Azure Disks

Answer

Azure NetApp Files

Question 18

You need to provide temporary access to a specific blob in Azure Blob Storage for a limited time. The access should be time-limited and require no authentication from the user. Which mechanism should you use?

Accepted Answer

Shared access signatures (SAS). Shared access signatures (SAS) provide time-limited, delegated access to storage resources without requiring the account key. Storage account keys provide full access and never expire. RBAC is for identity-based access, not anonymous. Access keys are long-lived secrets.

Answer

Storage account keys

Answer

Anonymous public access

Answer

Azure RBAC roles

Question 19

Which TWO of the following are features of Azure Cosmos DB that help ensure high availability?

Accepted Answer

Automatic failover. Cosmos DB supports multi-region writes and automatic failover for high availability. Point-in-time restore and change feed are features but not directly for high availability.

Answer

Data encryption at rest

Answer

Change feed

Answer

Point-in-time restore

Question 20

A financial institution needs to run complex queries against petabytes of historical trading data stored in Azure Data Lake Storage. The queries must be efficient and use columnar storage format. Which technology should they use to process this data?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a cloud-based analytics service designed for petabyte-scale data warehousing. It supports PolyBase to query data directly from Azure Data Lake Storage and uses a columnar storage format (via clustered columnstore indexes) to enable efficient, high-performance analytical queries on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage