DP-900 Practice Test 40 — 15 Questions

Question 1

Refer to the exhibit. This ARM template snippet is used to deploy which Azure resource?

Accepted Answer

Azure SQL Database server. The ARM template snippet contains the resource type 'Microsoft.Sql/servers', which is the Azure Resource Manager type for an Azure SQL Database logical server. This server acts as a central administrative point for one or more Azure SQL databases. The presence of properties like 'administratorLogin' and 'administratorLoginPassword' further confirms it's a SQL server, not a managed instance or MySQL server.

Answer

Azure Database for MySQL server

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics workspace

Question 2

A media company stores user profiles in Azure Cosmos DB using the Core (SQL) API. Each profile document contains a userId (unique), name, email, and a subscriptions array containing objects with a serviceName and startDate. The application needs to efficiently retrieve a single user by userId and also run a query to find all users who have a subscription to the service 'PremiumVideo'. Which partition key design is most appropriate for this workload?

Accepted Answer

Partition key on userId. Partitioning on userId ensures each document is evenly distributed across physical partitions, as userId is unique and used for point reads (the most efficient operation in Cosmos DB). The query for users with a 'PremiumVideo' subscription will be a cross-partition query regardless of partition key choice, but the primary workload—retrieving a single user by userId—is optimized with this design. Partitioning on userId also avoids hot partitions and adheres to the best practice of using a high-cardinality, frequently queried field as the partition key.

Answer

Partition key on email

Answer

Partition key on serviceName (extracted from subscriptions array)

Answer

Partition key on a composite key combining userId and serviceName

Question 3

You are designing a solution to store large binary files (up to 100 GB each) that are frequently read but rarely updated. The data must be accessible via HTTPS and support concurrent reads. Which Azure data store should you use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is the object store designed for massive unstructured/binary objects (block blobs up to ~190.7 TiB, single PUT up to 5 GiB, with 100 GB well within range), exposes HTTPS/REST endpoints via the blob service, and supports concurrent reads with high throughput and multiple access tiers. Its Hot tier fits the 'frequently read, rarely updated' pattern, and features like snapshots, soft delete, and RA-GRS cover durability.

Answer

Azure Files

Answer

Azure Cosmos DB

Answer

Azure NetApp Files

Question 4

A company is designing a data analytics solution. They need to store large volumes of raw data in its native format and support schema-on-read for data science exploration. Which storage technology should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, allowing raw data to be stored in its native format (e.g., CSV, JSON, Parquet) without transformation. It supports schema-on-read, meaning the schema is applied at query time (e.g., via Apache Spark or Azure Synapse SQL), which is ideal for data science exploration where the data structure may not be predefined.

Answer

Azure Blob Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 5

A healthcare organization stores patient medical records in a relational database with columns such as PatientID, Name, and DateOfBirth. They also store radiology images as DICOM files in Azure Blob Storage. Which statement correctly classifies these data types?

Accepted Answer

Patient records are structured, and radiology images are unstructured.. Patient records in a relational database with fixed columns like PatientID, Name, and DateOfBirth adhere to a predefined schema, making them structured data. Radiology images stored as DICOM files in Azure Blob Storage have no internal schema or tabular format and are therefore unstructured data. Option C correctly matches these classifications.

Answer

Both patient records and radiology images are structured data.

Answer

Patient records are semi-structured, and radiology images are unstructured.

Answer

Patient records are unstructured, and radiology images are semi-structured.

Question 6

A telecommunications company needs to analyze call detail records (CDRs) to detect fraud patterns and minimize revenue leakage. The data arrives as a continuous stream from network switches and must be queried within seconds of ingestion to flag suspicious activity. The analysts also need to run interactive ad-hoc queries over the last 90 days of CDR data using a Kusto query language. Which Azure service should they use as the primary data store and analytics engine?

Accepted Answer

Azure Data Explorer. Azure Data Explorer is optimized for high-velocity telemetry data like CDRs, supporting ingestion of continuous streams with sub-second query latency. Its native Kusto Query Language (KQL) enables both real-time fraud detection and interactive ad-hoc queries over large time windows (e.g., 90 days) without pre-aggregation or indexing overhead.

Answer

Azure Synapse Analytics

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 7

You need to store semi-structured JSON data from a web application and query it using SQL-like syntax. The solution must support high throughput with low latency. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents and offers SQL-like querying via its core (SQL) API. It is designed for high throughput and low latency with guaranteed single-digit millisecond response times at the 99th percentile, making it ideal for web applications with demanding performance requirements.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 8

A startup is migrating its on-premises SQL Server database to Azure. They want the least administrative overhead for patching and backups. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed PaaS database service that handles patching, backups, high availability, and monitoring automatically, minimizing administrative overhead. It is the natural migration target for a SQL Server database when the goal is least administrative effort.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Cosmos DB

Question 9

Which TWO Azure services can be used to perform real-time stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics (B) is a fully managed, real-time analytics service designed to ingest continuous streams from sources like Event Hubs, IoT Hub, or Blob Storage and run SQL-like queries with temporal windows (Tumbling, Hopping, Sliding, Session) to produce low-latency output to sinks such as Power BI, Cosmos DB, or SQL Database, making it a canonical real-time stream processing engine. Azure Databricks Structured Streaming (D) is built on Apache Spark and treats a live data stream as an unbounded table, supporting exactly-once semantics, event-time processing with watermarking, and continuous or micro-batch execution against sources like Kafka, Event Hubs, and Delta Lake, so it also performs real-time stream processing. Azure Data Factory (A) is a batch-oriented data integration and orchestration service using pipelines and activities, not a streaming engine. Azure Analysis Services (C) provides semantic tabular modeling and OLAP query capabilities over pre-processed data rather than processing live streams. Azure Synapse Pipelines (E) is the orchestration component of Synapse Analytics, used for scheduled batch ETL/ELT workflows, not real-time stream processing.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Synapse Pipelines

Question 10

Which TWO of the following are benefits of using Azure SQL Database over SQL Server on Azure Virtual Machines?

Accepted Answer

Built-in high availability with automatic failover. Option A is correct because Azure SQL Database is a fully managed PaaS offering that includes built-in high availability with automatic failover, typically using a 99.99% SLA and replicas managed by the service, whereas SQL Server on Azure VMs requires you to configure Always On availability groups or failover cluster instances yourself. Option B is correct because Azure SQL Database handles automated patching and updates of the database engine and underlying OS transparently, while SQL Server on Azure VMs leaves OS and SQL Server patching to the customer. Option C is not a benefit because Azure SQL Database does not let you choose any number of vCores arbitrarily; vCore counts are tied to specific hardware generations and service tiers, and cost depends on the chosen tier and compute size. Option D is incorrect because installing custom software on the database server is not possible in Azure SQL Database, but it is possible on SQL Server on Azure VMs. Option E is incorrect because full control over the operating system is a characteristic of SQL Server on Azure VMs (IaaS), not Azure SQL Database, which abstracts the OS away from the customer.

Answer

Lower cost because you can choose any number of vCores

Answer

Ability to install custom software on the database server

Answer

Full control over the operating system

Question 11

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 12

Your team is building a real-time dashboard for monitoring website traffic. The data source is streaming click events from Azure Event Hubs. The dashboard must update within seconds. Which Azure service should you use to process the stream?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is designed for real-time stream processing with low-latency output, making it ideal for processing click events from Event Hubs and updating a dashboard within seconds. It provides a SQL-like query language to define transformations and can output directly to Power BI or other visualization tools for near-instantaneous dashboard updates.

Answer

Azure Synapse Pipelines

Answer

Azure Data Factory

Answer

Azure Databricks Structured Streaming

Question 13

A data analyst needs to query a large dataset stored in Azure Blob Storage using serverless SQL pool in Azure Synapse Analytics. Which data format should they use to minimize storage costs while still supporting efficient querying?

Accepted Answer

Parquet. Parquet is a columnar storage format that compresses data efficiently and supports predicate pushdown, allowing serverless SQL pool in Azure Synapse to read only the necessary columns and rows. This minimizes storage costs while maintaining high query performance, unlike row-oriented formats such as CSV or JSON.

Answer

CSV

Answer

JSON

Answer

Avro

Question 14

A company runs a SQL Server database on an Azure virtual machine. They need to offload reporting queries to a read-only copy without modifying the application. Which Azure service should they use?

Accepted Answer

Azure SQL Database with read scale-out. Azure SQL Database with read scale-out (C) is correct because it creates a read-only replica of the database that can handle reporting queries without modifying the application. The application simply adds `ApplicationIntent=ReadOnly` to the connection string, and the Azure gateway automatically routes read-only queries to the secondary replica, offloading the primary from reporting workloads.

Answer

Azure Analysis Services

Answer

Azure SQL Managed Instance

Answer

Azure Synapse Analytics dedicated SQL pool

Question 15

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