DP-900 Practice Test 44 — 15 Questions

Question 1

A financial services company has raw transaction data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files, partitioned by date. The analytics team needs to run complex SQL queries that join multiple datasets, including reference data from an Azure SQL Database, to generate risk reports. They require enterprise-grade security features such as row-level security (RLS) and column-level security. They also want to use the same service for data transformation and loading (ETL) into a curated layer. Which Azure service should they choose?

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

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that combines enterprise data warehousing with big data analytics. It directly supports complex SQL queries across multiple datasets (including Parquet files in ADLS Gen2 and Azure SQL Database), offers built-in row-level security (RLS) and column-level security for enterprise-grade access control, and includes a built-in pipeline orchestration engine (via Synapse Pipelines) for ETL/ELT transformations into a curated layer. This single service eliminates the need to stitch together separate tools for querying, security, and data transformation.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 2

A hospital collects patient data from multiple sources. Source A stores patient vitals as a continuous stream of readings from wearable devices. Source B stores historical medical records in a relational database with fixed columns (PatientID, Diagnosis, AdmissionDate). Source C stores doctor's notes as unstructured text files. Which statement correctly describes the structure of data from these sources?

Accepted Answer

Source A is structured, Source B is structured, Source C is unstructured.. Source A stores patient vitals as a continuous stream from wearable devices, which is structured data because it typically consists of time-stamped numeric readings with a fixed schema (e.g., timestamp, heart rate, blood pressure). Source B uses a relational database with fixed columns (PatientID, Diagnosis, AdmissionDate), which is classic structured data. Source C contains unstructured text files (doctor's notes) with no predefined schema. Therefore, Option B correctly identifies all three sources.

Answer

Source A is semi-structured, Source B is structured, Source C is unstructured.

Answer

Source A is structured, Source B is unstructured, Source C is semi-structured.

Answer

Source A is semi-structured, Source B is semi-structured, Source C is unstructured.

Question 3

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate are slow. The table currently has a clustered index on OrderID (the primary key). Which index strategy will best improve these queries?

Accepted Answer

C. Create a nonclustered index on (CustomerID, OrderDate).. Creating a nonclustered index on (CustomerID, OrderDate) directly supports the query's filter (WHERE CustomerID = ?) and sort (ORDER BY OrderDate) operations. This composite index allows SQL Server to seek on CustomerID and then retrieve rows in OrderDate order without a separate sort, eliminating the need for a full clustered index scan on OrderID. It is a covering index for this query pattern, significantly reducing I/O and CPU overhead.

Answer

A. Create a nonclustered index on OrderDate only.

Answer

B. Create a filtered index on CustomerID where Status = 'Active'.

Answer

D. Create a nonclustered index on OrderID and OrderDate.

Question 4

A data engineer needs to transform large datasets stored in Azure Data Lake Storage Gen2 using Python and Apache Spark. They want a serverless compute option that automatically scales and requires no cluster management. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless Spark pool. Azure Synapse Analytics serverless Spark pool is correct because it provides a serverless Apache Spark compute environment that automatically scales based on workload demand and requires no cluster management. This aligns perfectly with the requirement to transform large datasets in Azure Data Lake Storage Gen2 using Python and Spark without provisioning or managing infrastructure.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks with interactive clusters

Answer

Azure Data Factory with MapReduce

Question 5

A retail company runs its legacy order management application on an on-premises SQL Server. They plan to migrate to Azure with minimal application changes and need high availability with automatic failover to a secondary Azure region. They also require full database-level isolation and the ability to use SQL Server Agent jobs. Which Azure deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (C) is correct because it provides near-100% compatibility with on-premises SQL Server, including full database-level isolation (a dedicated instance) and full support for SQL Server Agent jobs. It also supports auto-failover groups for high availability with automatic failover to a secondary Azure region, meeting the migration requirement with minimal application changes.

Answer

Azure SQL Database single database

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

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

A global online gaming company needs a data store for player game session logs. Each log record has a SessionID (unique), PlayerID, GameID, StartTime, EndTime, and a JSON payload containing variable game state details. The company requires low-latency writes for millions of concurrent sessions and wants to query by PlayerID and time range. Schema flexibility is important because game state details change frequently. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB with the NoSQL API. Azure Cosmos DB with the NoSQL API is the correct choice because it provides low-latency writes (single-digit milliseconds at the 99th percentile) for millions of concurrent sessions, supports schema-flexible JSON documents that can accommodate frequently changing game state payloads, and enables efficient queries by PlayerID and time range using a composite index or a partition key like PlayerID combined with a time-based sort order.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 8

A media company stores raw video footage as blobs in Azure Blob Storage. After processing, the raw footage is kept for compliance purposes and is accessed only a few times per year. The company wants to minimize storage costs while ensuring the data is durable and can be restored within 24 hours if needed. Which Azure Blob Storage access tier should they use?

Accepted Answer

Archive. The Archive tier is the correct choice because it offers the lowest storage cost for data that is rarely accessed (a few times per year) and can tolerate a retrieval latency of up to 15 hours, which is well within the 24-hour restoration requirement. Azure Blob Storage's Archive tier is designed for long-term retention, compliance, and backup scenarios where durability is maintained through geo-redundant replication options, and data can be rehydrated to an online tier (e.g., Hot or Cool) within the specified time frame.

Answer

Hot

Answer

Cool

Answer

Cold

Question 9

A manufacturing company ingests a continuous stream of sensor data from thousands of IoT devices into Azure Event Hubs. The company also stores historical equipment maintenance records in Azure SQL Database. The operations team needs to join the streaming sensor data with the historical maintenance records in near real-time to detect anomalies, and data scientists need to run ad-hoc T-SQL queries on the combined dataset for analysis. Which Azure service should they use as the primary analytics platform to meet both requirements?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest streaming data from Azure Event Hubs via its built-in Spark pools or pipelines, and simultaneously query historical data in Azure SQL Database using T-SQL. This enables near real-time anomaly detection through streaming joins and ad-hoc T-SQL queries for data scientists, all within a single service without needing separate tools.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 10

A data engineer needs to load data from an on-premises SQL Server database to Azure Synapse Analytics. The data volume is approximately 2 TB and the network bandwidth is limited. Which approach minimizes data transfer time?

Accepted Answer

Use Azure Data Box to physically ship the data.. Azure Data Box is the correct approach because it physically ships the 2 TB of data on a secure storage device, bypassing the limited network bandwidth entirely. For large data volumes (multiple TB) with constrained connectivity, offline data transfer is significantly faster than any online method, as it avoids network latency and bandwidth bottlenecks.

Answer

Use SQL Server Integration Services (SSIS) to transfer data over the internet.

Answer

Establish a site-to-site VPN and use Azure Data Factory.

Answer

Use Azure Data Factory with a self-hosted integration runtime over the internet.

Question 11

A manufacturing company installs IoT sensors on equipment in a factory. Each sensor sends a reading (device ID, timestamp, temperature, vibration) every second. The application must store these readings with extremely low write latency, support queries for the latest reading per device, and allow range queries over the last hour for a specific device. The development team expects high throughput writes (millions per day) and does not require complex joins. Which Azure data store is most appropriate for this workload?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the most appropriate because it offers single-digit millisecond write and read latencies at any scale, which is critical for the high-throughput, low-latency IoT sensor ingestion described. Its support for automatic indexing and efficient point reads (by device ID and timestamp) enables fast retrieval of the latest reading per device, while its native time-to-live (TTL) and range query capabilities on the timestamp field allow efficient queries over the last hour for a specific device. Additionally, Cosmos DB's schema-agnostic, non-relational model fits the simple key-value structure of sensor readings without requiring complex joins.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 12

A retail company uses Power BI to create sales reports. The data source is an Azure SQL Database that updates every 15 minutes. The reports must reflect near real-time data without manual refresh. Which Power BI feature should the company use?

Accepted Answer

Use DirectQuery mode to connect to the Azure SQL Database.. DirectQuery mode allows Power BI to query the Azure SQL Database directly without importing data, ensuring that reports reflect the current state of the database each time a report is viewed. Since the database updates every 15 minutes, DirectQuery provides near real-time data without requiring manual or scheduled refresh operations.

Answer

Use the on-premises data gateway to connect to Azure SQL Database.

Answer

Import data with scheduled refresh every 15 minutes.

Answer

Create a Power BI dataflow to transform the data.

Question 13

A manufacturing company stores IoT sensor data as blobs in Azure Blob Storage. Each blob is named with a device ID and a timestamp, and they need to quickly find all blobs for a specific device within a date range. Which Azure Blob Storage feature should they use to query blobs based on custom metadata?

Accepted Answer

Blob index tags. Blob index tags allow you to apply custom key-value metadata to blobs and then query them using a filtered query across containers or storage accounts. This enables efficient retrieval of blobs by device ID and timestamp without scanning all blob names or maintaining a separate index.

Answer

Blob snapshots

Answer

Blob soft delete

Answer

Blob lifecycle management

Question 14

A company needs to store large amounts of unstructured data (videos and images) for a media streaming application. The data must be accessible via HTTP/HTTPS and support tiered storage for cost optimization. Which Azure storage solution should they choose?

Accepted Answer

Azure Blob Storage. Azure Blob Storage (option B) is correct because it is Azure's object storage service designed for massive amounts of unstructured data such as videos and images, and it natively supports HTTP/HTTPS access as well as access tiers (Hot, Cool, Cold, Archive) for cost optimization. Azure Disk Storage (option A) provides block-level virtual hard disks for VMs and is not accessed via HTTP/HTTPS nor suited to tiered object storage. Azure Table Storage (option C) is a NoSQL key-attribute store for structured data, not large media files. Azure Files (option D) offers SMB/NFS file shares rather than HTTP/HTTPS-accessible object storage with tiering.

Answer

Azure Disk Storage

Answer

Azure Table Storage

Answer

Azure Files

Question 15

A data analyst is using Azure Databricks to transform streaming data from Event Hubs. They need to ensure that if a failure occurs, the streaming job can resume processing from the last committed offset. Which checkpointing mechanism should they configure?

Accepted Answer

Use Structured Streaming with checkpointing to Azure Data Lake Storage Gen2.. Structured Streaming in Azure Databricks uses checkpointing to store the current state and offsets of the streaming query in a reliable, external storage system. By configuring checkpointing to Azure Data Lake Storage Gen2, the job can recover from failures and resume processing exactly from the last committed offset, ensuring exactly-once semantics. This is the recommended approach for production streaming workloads on Azure.

Answer

Enable write-ahead logs on the Event Hubs namespace.

Answer

Use checkpointing to Hive metastore.

Answer

Use checkpointing to DBFS (Databricks File System).