DP-900 Describe an analytics workload on Azure Practice Test 11 — 15 Questions

Question 1

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?

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

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 2

A manufacturing company ingests real-time sensor data from assembly line machines into Azure Event Hubs. The company needs to calculate a 5-minute rolling average of temperature readings for each machine and compare it against a static threshold value stored in a CSV file in Azure Blob Storage. If the average exceeds the threshold, an alert must be triggered. Which Azure service should be used for this real-time data processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, including windowed aggregations like a 5-minute rolling average. It can directly ingest data from Azure Event Hubs, perform the calculation using a TumblingWindow or HoppingWindow function, and reference static data (the threshold CSV) from Azure Blob Storage via a reference data input. If the computed average exceeds the threshold, Stream Analytics can output the alert to a sink like Azure Functions or a notification service.

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Question 3

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 4

A retail company collects sales data from multiple stores. Data is ingested into Azure Data Lake Storage Gen2 as CSV files. The data team needs to run ad-hoc SQL queries on this data without moving it, and they want to pay only for the amount of data processed. They also need to integrate with Power BI for visualization. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (option D) is correct because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without moving the data, and it uses a pay-per-query model where you are billed only for the amount of data processed. It also integrates seamlessly with Power BI for visualization, making it ideal for ad-hoc SQL queries on CSV files.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure Data Lake Analytics

Question 5

A company uses Azure Synapse Analytics dedicated SQL pool for a large data warehouse. The fact table contains billions of rows and is hash-distributed on ProductID. Frequent queries join this fact table with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries aggregate sales by store and product for recent months, but run slowly due to data movement during joins. Which design change will most reduce data movement and improve query performance?

Accepted Answer

Replicate the Store dimension table. Replicating the small Store dimension table (10,000 rows) across all compute nodes eliminates the need to shuffle data during joins with the fact table. In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so queries that join a replicated table with a distributed fact table avoid costly data movement, significantly improving performance for frequent aggregation queries.

Answer

Change the distribution of the fact table to round-robin

Answer

Change the distribution key of the fact table to StoreID

Answer

Add a nonclustered index on the StoreID column in the fact table

Question 6

A data engineering team needs to transform large datasets stored in Azure Data Lake Storage Gen2 using Apache Spark with Python code. They want a fully managed service that provides serverless Spark pools, meaning no clusters to manage and automatic scaling. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics with serverless Spark pools. Azure Synapse Analytics with serverless Spark pools is the correct choice because it provides a fully managed, serverless Apache Spark environment that automatically scales and eliminates the need to manage clusters. This service directly supports transforming large datasets in Azure Data Lake Storage Gen2 using Python code with Spark, meeting the team's requirement for a no-cluster-management, auto-scaling solution.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure Machine Learning

Question 7

A financial services company stores transaction data in Azure Data Lake Storage Gen2 as Parquet files, partitioned by date. The data volume is 5 TB per day. The analytics team runs ad-hoc SQL queries to detect fraudulent patterns. Queries are highly selective (filtering on AccountID and date range). The team also needs to create external tables and views for use in Power BI. They want to pay only for the data processed by each query and avoid provisioning any compute resources. 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 querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, aligning with the requirement to pay only for data scanned by each query. It also supports creating external tables and views for Power BI, making it ideal for ad-hoc, selective queries on partitioned Parquet files.

Answer

Azure Databricks with interactive clusters

Answer

Azure Stream Analytics

Answer

Azure HDInsight with Spark

Question 8

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 9

A data analyst needs to run ad-hoc SQL queries on petabytes of data stored as Parquet files in Azure Data Lake Storage Gen2. The queries are infrequent but must return results within seconds. The analyst wants to pay only for the amount of data processed and does not want to manage any compute infrastructure. Additionally, they need to create views to simplify future reporting in Power BI. 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 querying petabytes of data in Azure Data Lake Storage Gen2 using standard T-SQL without provisioning any compute infrastructure. It charges only for the amount of data processed per query (pay-per-query model) and supports creating views for Power BI reporting, meeting all stated requirements.

Answer

Azure SQL Database

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure HDInsight with Spark

Question 10

A company needs to run complex analytical queries that aggregate terabytes of sales data across multiple years. The queries are used for monthly business reports and are not latency-sensitive. The data is stored in Azure Data Lake Storage Gen2. The company wants a fully managed, petabyte-scale data warehouse solution that supports SQL queries and integrates with Power BI for reporting. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is a fully managed, petabyte-scale analytics service that provides a dedicated SQL pool for running complex, high-performance T-SQL queries against massive datasets. It natively integrates with Azure Data Lake Storage Gen2 for reading data directly via PolyBase or external tables, and it offers built-in connectors to Power BI for reporting. This makes it the ideal choice for the described workload, which requires large-scale aggregation without low-latency demands.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure HDInsight

Question 11

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?

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 12

Drag and drop the steps to create an Azure Stream Analytics job in the correct order.

Accepted Answer

Create the Stream Analytics job, then configure inputs, then configure outputs, then define the transformation query.. Creating a Stream Analytics job involves setting up the job parameters, then configuring inputs, outputs, and the transformation query.

Answer

Create the Stream Analytics job, then define the transformation query, then configure inputs, then configure outputs.

Answer

Configure inputs, then configure outputs, then create the Stream Analytics job, then define the transformation query.

Answer

Define the transformation query, then create the Stream Analytics job, then configure inputs, then configure outputs.

Question 13

Match each Azure data tool to its purpose.

Accepted Answer

Azure Data Lake Storage: A scalable and secure data lake for high-performance analytics.. The correct matches are: Azure Data Lake Storage for scalable data lake storage, Azure Data Factory for data integration, and Azure Stream Analytics for real-time stream processing. Azure Synapse Analytics is a unified analytics platform, and Azure Databricks is an Apache Spark-based analytics platform. Common confusions include mixing up Synapse Analytics with Data Factory, and Databricks with Stream Analytics.

Answer

Azure Synapse Analytics: A cloud-based data integration service.

Answer

Azure Databricks: A real-time stream processing engine.

Question 14

Match each data type to its category in Azure.

Accepted Answer

Relational data: Stored in tables with predefined schema and relationships (Structured). Understanding data types helps choose the right Azure service. Structured data fits Azure SQL Database, semi-structured fits Cosmos DB, unstructured fits Blob Storage.

Answer

Relational data: Stored as key-value pairs with flexible schema (Semi-structured)

Answer

JSON data: Stored in tables with predefined schema and relationships (Structured)

Answer

Video files: Stored as key-value pairs (Semi-structured)

Question 15

A company runs a real-time dashboard in Power BI that displays sales data from Azure Synapse Analytics. The dashboard must show data with less than 5 seconds of latency. Which Azure service should be used to ingest streaming sales events into Azure Synapse Analytics?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, capable of ingesting high-velocity streaming sales events and outputting them to Azure Synapse Analytics with sub-second latency. This meets the requirement of less than 5 seconds of latency for the Power BI dashboard.

Answer

Azure SQL Database

Answer

Azure Data Factory

Answer

Azure Databricks