DP-900 Practice Test 43 — 15 Questions

Question 1

Which TWO Azure services can be used to host a relational database that requires native support for JSON data and high availability with automatic failover?

Accepted Answer

Azure SQL Database. Azure SQL Database and Azure SQL Managed Instance both support native JSON functions (e.g., JSON_VALUE, JSON_QUERY) and provide high availability with automatic failover through built-in mechanisms. Azure Database for PostgreSQL supports JSON via the jsonb data type, but the exam emphasizes Azure SQL services for relational JSON capabilities. Azure Cosmos DB is NoSQL, and Azure Database for MariaDB lacks native JSON support.

Answer

Azure Database for MariaDB

Answer

Azure Cosmos DB

Answer

Azure Database for PostgreSQL

Question 2

A company receives real-time clickstream data from its website via Azure Event Hubs. They need to detect fraudulent clicks within seconds and also produce daily aggregate reports of visitor statistics for historical analysis. Which combination of Azure services should they use for the real-time detection and the daily aggregation, respectively?

Accepted Answer

Azure Stream Analytics for real-time detection; Azure Data Factory for daily aggregation. Azure Stream Analytics is purpose-built for real-time stream processing, making it ideal for detecting fraudulent clicks within seconds from Event Hubs. Azure Data Factory is a cloud-based ETL service that can orchestrate and execute daily aggregation jobs on historical data, such as producing visitor statistics reports from stored clickstream data.

Answer

Azure Databricks for both real-time detection and daily aggregation

Answer

Azure Synapse Analytics for real-time detection; Azure Blob Storage for daily aggregation

Answer

Azure Functions for real-time detection; Azure SQL Database for daily aggregation

Question 3

Refer to the exhibit. An administrator deploys this Azure Policy assignment. What is the most likely effect on storage account 'storage1'?

Accepted Answer

Public network access will be denied.. The Azure Policy assignment shown in the exhibit denies the creation or update of storage accounts that do not have public network access disabled. Since 'storage1' is subject to this policy, the policy will enforce the 'Deny' effect, preventing any configuration that allows public network access. If 'storage1' already exists and is compliant, it remains; if it is non-compliant, the policy will block changes that would enable public access, effectively denying public network access.

Answer

The storage account will be deleted.

Answer

Firewall rules will be added.

Answer

Soft Delete will be enabled.

Question 4

You are a data architect for a logistics company. The company uses Azure Data Lake Storage Gen2 to store shipment tracking data. The data is ingested from IoT devices on trucks. Each record contains truck ID, timestamp, GPS coordinates, speed, and fuel level. The volume is 5 TB per day. The company wants to build a near-real-time dashboard to monitor truck locations and speeds. They also need to run daily batch analytics to compute fuel efficiency trends. You need to design a solution that minimizes latency for the dashboard and maximizes cost efficiency for batch processing. You plan to use Azure Event Hubs for ingestion. Which approach should you take?

Accepted Answer

Use Azure Stream Analytics to process the stream, output to Power BI for real-time dashboard, and simultaneously output raw data to Azure Data Lake Storage. Use Azure Databricks to process the data lake for batch analytics.. It separates the real-time and batch processing paths to minimize latency and maximize cost efficiency. Azure Stream Analytics outputs directly to Power BI for near-real-time dashboard updates, while simultaneously writing raw data to Azure Data Lake Storage for cost-effective storage. Azure Databricks then processes the data lake for daily batch analytics, avoiding expensive real-time compute for historical analysis.

Answer

Use Azure Stream Analytics to process the stream and output directly to Azure SQL Database. Use Power BI to query SQL Database for both real-time dashboard and historical analytics.

Answer

Use Azure Event Hubs Capture to store data in Azure Blob Storage, then use Azure Data Factory to transform and load into Azure Synapse Analytics for both dashboard and batch.

Answer

Use Azure Databricks with Structured Streaming to process the stream, write to Delta Lake, and use Delta Lake to serve both real-time and batch queries.

Question 5

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 6

A company has 12 SQL Server databases, each about 30 GB. The databases experience unpredictable load spikes during the day. The company wants to migrate to Azure SQL Database to reduce administrative overhead and optimize costs by sharing resources among the databases. Which deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed to share resources (eDTUs or eVCores) among multiple databases with unpredictable, overlapping load spikes. By pooling resources, the company can optimize costs because databases do not all peak simultaneously, and the pool’s total resource allocation is lower than the sum of individual peak requirements. This reduces administrative overhead by providing a single management point for scaling and monitoring all databases in the pool.

Answer

Single database with provisioned DTU

Answer

Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 7

A retail company stores customer data in three formats: a relational database table with fixed columns for CustomerID, Name, and Email; customer feedback as JSON documents with varying fields such as rating and comment; and product images as JPEG files. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Relational table, JSON documents, image files. Relational tables enforce a fixed schema with defined columns and data types, making them the most structured. JSON documents are semi-structured, allowing varying fields and flexible schemas, while image files are unstructured binary data with no inherent schema. This ordering from most to least structured aligns with the core data classification concept in the DP-900 exam.

Answer

JSON documents, relational table, image files

Answer

Image files, JSON documents, relational table

Answer

Relational table, image files, JSON documents

Question 8

A company receives data from a point-of-sale system. Each row contains TransactionID, ProductID, Quantity, and Price. The data has a fixed schema and is stored in a table. How should this data be classified?

Accepted Answer

Structured data. The data has a fixed schema with clearly defined columns (TransactionID, ProductID, Quantity, Price) and each row follows the same structure, which is the definition of structured data. In Azure, this would map directly to a table in Azure SQL Database or a fixed-schema table in Azure Synapse Analytics. The rigid schema and consistent data types make it ideal for relational storage and querying.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Transactional data

Question 9

A transportation company collects real-time GPS data from thousands of delivery vehicles. They need to process this streaming data to detect delays and generate alerts when a vehicle is behind schedule. Which Azure service should they use for the stream processing?

Accepted Answer

B. Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed to handle high-velocity data from sources like IoT devices and GPS sensors. It can ingest streaming data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect patterns such as delays, and output alerts to sinks like Azure Functions or Power BI in near real-time.

Answer

A. Azure Data Factory

Answer

C. Azure Data Lake Analytics

Answer

D. Azure Analysis Services

Question 10

A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.

Answer

Single database with DTU purchasing model

Answer

Single database with vCore purchasing model

Answer

Azure SQL Managed Instance

Question 11

A data engineer needs to build a data pipeline that runs daily to copy sales data from an on-premises SQL Server to Azure Synapse Analytics. Which Azure service should they use to orchestrate the pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based data integration service specifically designed to orchestrate and automate data pipelines. It supports scheduled triggers (e.g., daily runs) and provides native connectors to copy data from on-premises SQL Server (via Self-Hosted Integration Runtime) to Azure Synapse Analytics, making it the ideal tool for this ETL/ELT workload.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure HDInsight

Question 12

You need to store event data from multiple sources in a schema-less format for later analysis. The data arrives as JSON and must be durable and highly available. Which Azure service should you use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage provides durable, highly available storage for JSON blobs with schema-less format. Option B is wrong because Azure SQL Database requires a schema. Option C is wrong because Azure Event Hubs is for ingestion, not long-term storage. Option D is wrong because Azure Data Factory is an orchestration service.

Answer

Azure SQL Database

Answer

Azure Event Hubs

Answer

Azure Data Factory

Question 13

A company is evaluating Azure database services for two different workloads. Workload A processes high-volume, low-latency transactions such as order entry and payment processing, where each transaction updates a few rows. Workload B involves running complex aggregations on terabytes of historical sales data to generate monthly business intelligence reports. Which Azure service is best suited for each workload?

Accepted Answer

B. Workload A: Azure Cosmos DB; Workload B: Azure Synapse Analytics. Workload A requires a low-latency, high-throughput transactional database capable of handling many small, row-level updates. Azure Cosmos DB is a NoSQL database designed for single-digit millisecond latency and horizontal scaling, making it ideal for order entry and payment processing. Workload B involves complex aggregations on terabytes of historical data, which is best handled by Azure Synapse Analytics, a distributed analytics service that uses massively parallel processing (MPP) to run large-scale queries efficiently.

Answer

A. Workload A: Azure SQL Database; Workload B: Azure Cosmos DB

Answer

C. Workload A: Azure Synapse Analytics; Workload B: Azure SQL Database

Answer

D. Workload A: Azure Cosmos DB; Workload B: Azure Cosmos DB

Question 14

A bank processes a fund transfer transaction. The system debits $100 from Account A and then credits $100 to Account B. If the system crashes after debiting Account A but before crediting Account B, the database automatically reverts the debit. Which ACID property ensures this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails (e.g., a crash after debiting Account A but before crediting Account B), the entire transaction is rolled back, reverting any partial changes like the debit. This all-or-nothing behavior is the core of atomicity in database systems.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 15

A healthcare organization is planning a data analytics platform. They will ingest data from various sources: structured patient records from a relational database, semi-structured JSON logs from medical devices, and unstructured physician notes as plain text files. Which characteristic of big data describes the different formats of data being ingested?

Accepted Answer

Variety. The question describes data in three distinct formats: structured (relational database), semi-structured (JSON logs), and unstructured (plain text). In big data terminology, 'Variety' specifically refers to the different types and formats of data being processed. This is a core concept in the 4 V's of big data, where Variety captures the heterogeneity of data sources and structures.

Answer

Volume

Answer

Velocity

Answer

Veracity