DP-900 Practice Test 42 — 15 Questions

Question 1

A financial services company stores petabytes of transaction data in Parquet format in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and aggregate billions of rows, with results expected within seconds. The company wants to use a massively parallel processing (MPP) engine that supports T-SQL and can be paused to reduce costs during off-hours. They also need native integration with Azure Data Factory and Power BI. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL DW) is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling complex T-SQL queries on petabyte-scale Parquet data with results in seconds. It supports native T-SQL, can be paused to reduce costs during off-hours, and offers built-in integration with Azure Data Factory and Power BI through its SQL endpoints and linked service connectors.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure SQL Database

Question 2

A retail company needs to run complex SQL queries on petabytes of historical sales data stored in Parquet files in Azure Data Lake Storage Gen2. They want a solution that provides fast query performance without managing infrastructure, and they prefer a pay-per-query pricing model. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows querying petabytes of Parquet files in Azure Data Lake Storage Gen2 using T-SQL without provisioning any infrastructure, and it charges per terabyte of data processed (pay-per-query). This matches the requirements for fast query performance on historical sales data with a serverless, consumption-based pricing model.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight with Hive

Question 3

A logistics company ingests real-time GPS data from delivery vehicles via Azure Event Hubs. The data includes vehicle ID, latitude, longitude, and timestamp. The company also has historical route plan data stored as CSV files in Azure Data Lake Storage Gen2. Data analysts need to combine the live stream with the historical data in near real-time to create a dashboard showing if vehicles are on schedule. They also need to run complex T-SQL queries on the combined dataset for ad-hoc reporting. Which Azure service should they use as the primary analytics platform?

Accepted Answer

C: Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest real-time data from Azure Event Hubs via its built-in streaming capabilities (e.g., using Synapse Pipelines or Spark Structured Streaming) and combine it with historical data stored in Azure Data Lake Storage Gen2. It supports complex T-SQL queries through its dedicated SQL pool (formerly SQL Data Warehouse) for ad-hoc reporting, enabling near real-time dashboards and interactive analytics on the combined dataset.

Answer

A: Azure Stream Analytics

Answer

B: Azure Data Lake Analytics

Answer

D: Azure Analysis Services

Question 4

A manufacturing company deploys IoT sensors on equipment in a factory. They need to monitor sensor data in real time to detect anomalies and trigger immediate alerts. They also need to store years of historical sensor data for monthly capacity planning reports that involve complex aggregations. The company wants a cost-effective solution that minimizes data movement between storage and compute. Which combination of Azure services should they use for real-time processing and historical batch analytics?

Accepted Answer

A. Azure Stream Analytics for real-time processing, Azure Data Lake Storage Gen2 for historical storage, and Azure Synapse Analytics for batch queries.. Azure Stream Analytics is purpose-built for real-time processing of streaming data from IoT sensors, enabling immediate anomaly detection and alerting. Azure Data Lake Storage Gen2 provides cost-effective, scalable storage for years of historical sensor data, while Azure Synapse Analytics (formerly SQL Data Warehouse) can run complex aggregations directly against that data without moving it, minimizing data movement and cost.

Answer

B. Azure Data Factory for real-time processing, Azure Cosmos DB for historical storage, and Power BI for batch queries.

Answer

C. Azure Functions for real-time processing, Azure Table Storage for historical storage, and Azure Analysis Services for batch queries.

Answer

D. Azure Event Hubs for real-time processing, Azure SQL Database for historical storage, and Azure Machine Learning for batch queries.

Question 5

A retail company uses Azure SQL Database for its inventory system. The database stores sensitive customer information that must be encrypted at rest to comply with data protection regulations. Which feature should they enable?

Accepted Answer

Transparent Data Encryption (TDE). Option B is correct because Transparent Data Encryption (TDE) encrypts data at rest in Azure SQL Database. Option A (Always Encrypted) protects sensitive data in transit and at rest but is more for column-level encryption. Option C (Dynamic Data Masking) obfuscates data from non-privileged users. Option D (Azure Information Protection) is for file-level protection.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 6

A large e-commerce company needs to build an analytics solution. They have streaming clickstream data from their website (JSON) and daily sales data from their transactional database (CSV). They need to perform real-time dashboards on clickstream for the current hour, and also run complex historical queries that join sales data with aggregated clickstream data over the past year. They want a single Azure service that can handle both stream processing and batch processing using a unified experience, without moving data between separate systems. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified experience for both stream processing (via Synapse Pipelines or Spark Structured Streaming) and batch processing (via dedicated SQL pools or serverless SQL), enabling real-time dashboards on clickstream data and complex historical queries joining sales data with aggregated clickstream data without moving data between separate systems.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure HDInsight

Question 7

A manufacturing company needs to build an analytics solution for IoT sensor data. Thousands of devices send real-time temperature and vibration readings. The solution must: (1) ingest the streaming data reliably, (2) perform real-time aggregations (e.g., average temperature per device every minute), and (3) store the aggregated results in Azure Synapse Analytics for historical reporting and dashboards. Which combination of Azure services should be used?

Accepted Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Synapse Analytics. Azure Event Hubs is designed for high-throughput, reliable ingestion of streaming data from millions of IoT devices. Azure Stream Analytics can then perform real-time aggregations (like average temperature per device per minute) using a SQL-like query language. Finally, Azure Synapse Analytics provides a dedicated SQL pool or serverless SQL endpoint for storing and querying the aggregated results, enabling historical reporting and dashboards.

Answer

Azure IoT Hub -> Azure Data Factory -> Azure Cosmos DB

Answer

Azure Blob Storage -> Azure Databricks -> Azure SQL Database

Answer

Azure Service Bus -> Azure Functions -> Azure Table Storage

Question 8

A manufacturing company has a streaming data pipeline that ingests sensor data from factory equipment into Azure Event Hubs. The data must be prepared for reporting by cleaning invalid records, removing duplicates, and aggregating readings into 5-minute windows. The transformed data needs to be stored in a columnar format in a data lake to support efficient querying by data analysts using SQL. Which Azure service should perform the data transformation and loading?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, directly consuming data from Azure Event Hubs, performing transformations like cleaning invalid records, removing duplicates, and aggregating over tumbling windows (e.g., 5-minute windows), and outputting the results in a columnar format (e.g., Parquet) to Azure Data Lake Storage. This aligns perfectly with the requirement for a low-latency, continuous transformation pipeline without needing additional orchestration or compute clusters.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines

Question 9

A marketing team wants to analyze social media sentiment in near real-time. They will use Azure Event Hubs to capture tweets and need to aggregate sentiment scores over 5-minute windows. The aggregated results must be stored in Azure Blob Storage for later analysis. Which Azure service should they use to perform the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a real-time stream processing engine designed to ingest data from sources like Azure Event Hubs, apply temporal aggregations (e.g., 5-minute tumbling windows), and output results directly to Azure Blob Storage. It provides built-in support for windowed functions and exactly-once delivery semantics, making it ideal for near-real-time sentiment analysis without requiring custom code.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Analytics

Question 10

A retail company has an Azure SQL Database that handles OLTP transactions for its e-commerce platform. The analytics team needs to run complex reporting queries that join multiple tables (e.g., orders, products, customers) and aggregate millions of rows. These queries are long-running and would negatively impact the performance of the OLTP database if run directly. The company wants to use a separate analytics service that supports T-SQL queries, can scale compute independently, and provides a serverless option to avoid provisioning fixed resources. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics (dedicated SQL pool). Azure Synapse Analytics (dedicated SQL pool) is the correct choice because it is a cloud-based analytics service that supports T-SQL queries, can scale compute independently from storage, and offers a serverless option (Synapse Serverless SQL pool) that eliminates the need to provision fixed resources. This allows the analytics team to run complex, long-running reporting queries against large datasets without impacting the performance of the OLTP Azure SQL Database.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database (creating a secondary replica)

Question 11

A retail company stores years of historical sales data in Azure Data Lake Storage Gen2 as Parquet files. Business analysts need to run complex SQL queries over this data to identify sales trends, and they want to visualize the results in Power BI dashboards. They prefer to avoid moving data into a separate database to minimize storage costs and latency. Which Azure service should they use to query the data directly in the lake?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics provides the serverless SQL pool capability that allows you to query data directly in Azure Data Lake Storage Gen2 using T-SQL without moving or copying the data. This enables business analysts to run complex SQL queries over Parquet files in the lake and connect the results to Power BI for visualization, minimizing storage costs and latency by avoiding a separate database.

Answer

Azure SQL Database

Answer

Azure Data Factory

Answer

Azure Analysis Services

Question 12

A manufacturing company connects thousands of IoT sensors on an assembly line, each sending telemetry data every second. The data volume is terabyte-scale per day. The company needs to analyze the sensor data in near real-time to detect anomalies (e.g., temperature spikes) and also allow data scientists to run interactive ad-hoc queries on the historical data to find patterns. They prefer using a query language similar to SQL. Which Azure service should they choose?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for high-velocity telemetry data, ingesting terabytes per day from IoT sensors with sub-second latency. It supports Kusto Query Language (KQL), which is SQL-like and optimized for time-series analysis, anomaly detection, and interactive ad-hoc queries on both real-time and historical data. This makes it the ideal choice for the described scenario.

Answer

Azure Stream Analytics

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks with Structured Streaming

Question 13

A logistics company uses IoT sensors on delivery trucks to transmit GPS location, speed, and engine diagnostics every 10 seconds. The data is ingested into Azure Event Hubs. The company needs to analyze the data in real time to identify speeding trucks and send alerts. The analysis requires joining the live sensor data with a reference table of truck details (e.g., driver name, route number) stored in Azure SQL Database. Which Azure service should they use for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a real-time event processing engine designed to handle streaming data from sources like Azure Event Hubs. It can perform temporal joins between the live IoT sensor stream and a static reference table (e.g., truck details from Azure SQL Database) to enrich the data and trigger alerts when speeding is detected, all with sub-second latency.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory

Answer

Azure Databricks

Question 14

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 15

A global e-commerce company uses Azure Cosmos DB for its product catalog. The database is replicated across multiple regions. During a regional outage, customers in the affected region report that they cannot update product prices. The application team confirms that read requests succeed. What is the most likely cause of this issue?

Accepted Answer

The Cosmos DB account is configured with a single write region and automatic failover is not enabled.. In a multi-region write configuration, Cosmos DB can handle writes in any region. However, if the account is configured with a single write region, only that region can accept writes. During an outage of the write region, writes fail. Option A is wrong because read replicas are for read-only scenarios. Option B is wrong because consistency level does not affect write availability. Option D is wrong because failover is automatic if configured.

Answer

The application is using the wrong connection string for the region.

Answer

The consistency level is set to eventual, causing write conflicts.

Answer

The account is using read-only replicas in the affected region.