DP-900 Practice Test 40 — 15 Questions

Question 1

A company stores terabytes of historical sales data as Parquet files in Azure Data Lake Storage Gen2. Business analysts need to run ad-hoc SQL queries that involve complex joins and aggregations over this data. They want to avoid provisioning a dedicated cluster or moving data into a separate database. The queries must be executed using standard T-SQL syntax. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool (B) is the correct choice because it allows you to query Parquet files directly in Azure Data Lake Storage Gen2 using standard T-SQL syntax without provisioning any dedicated cluster or moving data. It automatically scales compute resources to handle complex joins and aggregations on terabytes of data, charging only for the data processed. This matches the requirement for ad-hoc, serverless querying with familiar T-SQL.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks

Answer

Azure HDInsight

Question 2

A company uses Azure Data Factory to run a pipeline that copies new orders from an on-premises SQL Server database to Azure Data Lake Storage every hour. After the data is in the data lake, an Azure Databricks notebook transforms it and loads it into Azure Synapse Analytics for reporting. Which type of data processing does the hourly copy operation represent?

Accepted Answer

Batch processing. The hourly copy operation from on-premises SQL Server to Azure Data Lake Storage is a classic batch processing pattern: data is collected over a fixed time interval (1 hour) and processed as a single unit. Azure Data Factory orchestrates this scheduled, non-continuous transfer, which aligns with batch processing's definition of handling data in discrete, periodic chunks rather than in real-time.

Answer

Real-time streaming

Answer

Interactive query

Answer

Transactional processing

Question 3

A data engineering team needs to build a pipeline that ingests streaming data from IoT devices into Azure Data Lake Storage Gen2. The data arrives as JSON messages. They want to use a service that can capture the streaming data in near real-time and store it as files in the data lake without writing custom code for the ingestion. Which Azure service should they use?

Accepted Answer

Azure Event Hubs with Capture. Azure Event Hubs with Capture is the correct choice because it natively ingests streaming JSON data from IoT devices in near real-time and automatically writes the data to Azure Data Lake Storage Gen2 as files without requiring any custom code. The Capture feature automatically persists the event stream to the specified storage destination at defined time or size intervals, making it ideal for serverless, code-free ingestion.

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Answer

Azure Synapse Pipelines

Question 4

A company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. Every night, they need to load 500 GB of new sales data from CSV files stored in Azure Data Lake Storage Gen2. The loading process must be automated, scheduled, and include error handling (e.g., skip corrupt rows and log them). Which Azure service should be used to orchestrate this load pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data orchestration service that supports scheduled execution, error handling (e.g., skipping corrupt rows via fault tolerance settings in the Copy activity), and native integration with Azure Data Lake Storage Gen2 and Azure Synapse dedicated SQL pool. ADF can automate the nightly 500 GB load using a trigger, and its mapping data flows or Copy activity can log errors to a separate file or table, meeting the requirement for automated, scheduled, and error-tolerant ingestion.

Answer

Azure Stream Analytics

Answer

Azure HDInsight

Answer

Azure Logic Apps

Question 5

A company needs to migrate a large on-premises SQL Server database to Azure with minimal downtime. Which Azure service should they use for the migration?

Accepted Answer

Azure Database Migration Service. Option B is correct because Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting continuous data sync. Option A is wrong because Azure SQL Database is the target, not a migration tool. Option C is wrong because Azure Data Factory is for data integration, not database migration with minimal downtime. Option D is wrong because Azure Backup is for backup, not migration.

Answer

Azure SQL Database

Answer

Azure Backup

Answer

Azure Data Factory

Question 6

A data analyst needs to run ad-hoc SQL queries on large datasets stored as Parquet files in Azure Data Lake Storage Gen2. The queries are infrequent and the data volume varies. The analyst wants to pay only for the amount of data processed per query and does not want to manage any infrastructure. They also need to create views in T-SQL to simplify queries for Power BI reports. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query model, charging only for the amount of data processed, and supports creating T-SQL views that can be used directly by Power BI for simplified reporting.

Answer

Azure Data Lake Analytics

Answer

Azure HDInsight

Answer

Azure Databricks

Question 7

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 8

A data analyst needs to run ad-hoc SQL queries on terabytes of CSV files stored in Azure Data Lake Storage Gen2. The queries are infrequent and unpredictable. The analyst wants to pay only for the amount of data processed by each query, and does not want to manage any compute or storage infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (C) is the correct choice because it allows querying data in Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, making it ideal for infrequent, unpredictable ad-hoc queries, and it eliminates infrastructure management.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory

Answer

Azure Analysis Services

Question 9

A data engineering team needs to build a batch processing pipeline that transforms large volumes of sales data stored in Azure Data Lake Storage Gen2. The transformations include aggregations and joins, and the output should be stored back in the data lake as Parquet files. The team wants a serverless compute option that automatically scales and charges per second. Which Azure service should they use?

Accepted Answer

Azure Databricks with auto-scaling clusters. Azure Databricks with auto-scaling clusters is the correct choice because it provides a serverless, Apache Spark-based compute platform that automatically scales resources based on workload demand and charges per second (via serverless or low-concurrency modes). It is ideal for batch processing large volumes of data in Azure Data Lake Storage Gen2, supporting complex transformations like aggregations and joins, and can write output directly as Parquet files.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory with mapping data flows

Answer

Azure Stream Analytics

Question 10

A company is migrating an on-premises SQL Server database to Azure SQL Database. The database currently uses SQL Server Agent jobs for nightly ETL processes. Which Azure service should the company use to replace these jobs?

Accepted Answer

Elastic Database Jobs. Option A is correct because Azure SQL Database does not support SQL Server Agent; Elastic Database Jobs can be used to schedule T-SQL jobs across multiple databases. Option B is wrong because Azure Data Factory is for data integration, not scheduling T-SQL jobs. Option C is wrong because Azure Logic Apps is for workflow automation, not T-SQL job scheduling. Option D is wrong because Azure Automation can run PowerShell scripts but not T-SQL directly.

Answer

Azure Automation

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 11

A financial services company processes real-time stock trade data from multiple exchanges. Trades are ingested into Azure Event Hubs. The company needs to compute a 5-minute sliding window average of trade prices per stock symbol and ensure that each trade is processed exactly once within the window. The aggregated results must be stored in Azure SQL Database for historical reporting and also sent to a Power BI dashboard for near real-time visualization. Which Azure service should be used for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for real-time stream processing with native support for time-based windowing (e.g., 5-minute sliding window) and exactly-once semantics when used with Azure Event Hubs as input and Azure SQL Database as output. It can directly compute the sliding window average of trade prices per stock symbol and output results to both Azure SQL Database for historical storage and Power BI for near real-time visualization, all without requiring additional code or infrastructure management.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

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

A financial services company runs large-scale analytical queries on a dedicated SQL pool in Azure Synapse Analytics. They notice that during peak hours, complex aggregations consume excessive resources, causing slower queries from other users. They need to ensure that critical management reports always get enough resources and complete within a guaranteed time, while other less important queries do not starve them. Which feature should they implement?

Accepted Answer

Workload management. Workload management in Azure Synapse Analytics allows you to classify, assign resources, and prioritize queries by creating workload groups and classifiers. By configuring a workload group for critical management reports with a higher importance and a guaranteed minimum resource percentage, you ensure those queries always get sufficient resources and complete within a guaranteed time, while less important queries are throttled and cannot starve the critical ones.

Answer

Result-set caching

Answer

Materialized views

Answer

Columnstore index

Question 14

A data engineering team needs to build a real-time dashboard showing sales totals by region. Sales transactions are streamed from point-of-sale systems into Azure Event Hubs. The team wants to aggregate the data in near real-time (e.g., every minute) and store the results in Azure SQL Database for visualization in Power BI. Which Azure service should they use for the aggregation step?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, allowing you to define a query that aggregates sales data from Event Hubs over a one-minute tumbling window and output the results directly to Azure SQL Database. This meets the requirement for near real-time aggregation without needing to write custom code or manage infrastructure.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Logic Apps

Question 15

Which THREE are valid deployment options for Azure SQL Database?

Accepted Answer

Managed instance. Options B, C, and E are correct. Single database, elastic pool, and managed instance are all deployment options for Azure SQL Database. Option A is wrong because virtual machine is not a deployment option for Azure SQL Database; it's SQL Server on VM. Option D is wrong because SQL Data Warehouse is a separate service (now Synapse).

Answer

SQL Server on Azure Virtual Machine

Answer

SQL Data Warehouse