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

Question 1

A retail company needs to analyze sales transactions as they occur to detect fraud patterns and immediately block suspicious orders. They also need to run daily batch reports on historical sales data. Which combination of Azure services should they use to meet both real-time and batch processing requirements?

Accepted Answer

Azure Stream Analytics for real-time processing and Azure Synapse Analytics for batch analytics. Azure Stream Analytics is purpose-built for real-time data streaming and can process sales transactions as they occur to detect fraud patterns and block suspicious orders immediately. Azure Synapse Analytics provides a unified analytics platform that can run large-scale batch queries on historical sales data for daily reports, making this combination ideal for both real-time and batch processing needs.

Answer

Azure Data Factory for both real-time and batch processing

Answer

Azure Logic Apps for real-time processing and Azure Synapse Analytics for batch analytics

Answer

Azure Stream Analytics for both real-time and batch processing

Question 2

A data engineering team is designing a modern data warehouse on Azure. They have raw data landing in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files. They need to perform transformations using Apache Spark, and then load the transformed data into Azure Synapse Analytics for high-performance analytical queries. The team wants to use a single orchestration service to schedule, monitor, and manage the entire pipeline. Which Azure service should they choose for orchestration?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and orchestration service designed to schedule, monitor, and manage data pipelines at scale. It natively supports triggers (e.g., time-based, event-based) and can orchestrate Apache Spark transformations via Azure Databricks or HDInsight, then load the transformed data into Azure Synapse Analytics using built-in copy activities or pipelines. ADF provides a single pane of glass for end-to-end pipeline management, including dependency handling and error monitoring.

Answer

Azure Databricks

Answer

Azure Logic Apps

Answer

Azure Data Lake Analytics

Question 3

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. They frequently run queries that aggregate sales by product and region over the past month. The queries are slow because they scan the entire table. Which index type should they implement on the fact table to improve query performance for these aggregations?

Accepted Answer

Clustered columnstore index. A clustered columnstore index is ideal for large fact tables in Azure Synapse Analytics dedicated SQL pool because it stores data column-wise, enabling high compression and eliminating the need to scan irrelevant columns. For aggregation queries that sum sales by product and region over the past month, the columnstore index significantly reduces I/O by reading only the necessary columns and applying batch-mode processing, which accelerates scan and aggregation operations.

Answer

Clustered index on the primary key

Answer

Hash-distributed table on SalesID

Answer

Non-clustered index on (ProductID, Region)

Question 4

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The fact table contains billions of rows and is hash-distributed on ProductID. Queries aggregate sales by store and product for the current month and join with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries are slow 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 and the Product dimension table.. Replicating the Store and Product dimension tables across all compute nodes eliminates the need to shuffle data during joins with the large fact table. Since both dimension tables are small enough to fit in memory on each node (10,000 and 500,000 rows), replication avoids costly data movement and significantly improves query performance for aggregations that join on multiple dimensions.

Answer

Change the fact table to round-robin distribution.

Answer

Change the hash distribution key of the fact table to StoreID.

Answer

Implement a clustered columnstore index on the fact table.

Question 5

A data engineering team is building a batch analytics pipeline. Raw clickstream data is stored as Parquet files in Azure Data Lake Storage Gen2. The team needs to transform the data using Apache Spark (Python code) and then load the results into Azure Synapse Analytics for high-performance reporting. They want to use a serverless compute option for Spark to avoid managing clusters. Which combination of Azure services should they use for the transformation and loading?

Accepted Answer

Use Azure Synapse Analytics serverless Spark pools for transformations and load into the Synapse dedicated SQL pool.. Option B is correct because Azure Synapse Analytics serverless Spark pools provide a serverless compute option for running Apache Spark transformations without managing clusters, and the transformed data can be directly loaded into the Synapse dedicated SQL pool for high-performance reporting. This combination meets all requirements: serverless Spark for transformations, and Synapse dedicated SQL pool for optimized analytics workloads.

Answer

Use Azure Databricks with a serverless cluster for transformations and load into Azure SQL Database.

Answer

Use Azure Data Factory with a Spark activity to run transformations and load into Azure Synapse Analytics.

Answer

Use Azure HDInsight with Apache Spark for transformations and load into Azure Blob Storage.

Question 6

A data analyst needs to query large datasets stored as Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and infrequent. The analyst wants to run SQL queries directly on the data without creating any storage or compute infrastructure, and only pay for the amount of data processed. They also need to create T-SQL views 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 querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute or storage infrastructure. It supports ad-hoc, infrequent queries with a pay-per-query pricing model based on the amount of data processed, and it enables the creation of T-SQL views that can be used directly by Power BI for reporting.

Answer

Azure SQL Database

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 7

A logistics company needs to analyze GPS data from delivery trucks in real time to detect delays and reroute deliveries. The GPS data is streamed into Azure Event Hubs. They also need to combine this live data with static route information 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 designed for real-time stream processing, capable of ingesting data from Azure Event Hubs, applying SQL-like queries to detect delays, and joining live streams with static reference data from Azure SQL Database. This enables the logistics company to analyze GPS data as it arrives and trigger rerouting actions with minimal latency.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Synapse Pipelines

Question 8

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?

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 9

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 10

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 11

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 12

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 13

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 14

A data analyst needs to run ad-hoc SQL queries on petabytes of Parquet files stored in Azure Data Lake Storage Gen2. The queries are infrequent and highly selective. The analyst wants to pay only for the data scanned by each query and does not want to provision any compute resources. They also need to create views to simplify future queries for other analysts. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (correct answer) is a pay-per-query service that charges only for the data scanned, requires no provisioning of compute resources, and supports creating views over external data in Azure Data Lake Storage Gen2. It uses T-SQL to query Parquet files directly, making it ideal for infrequent, highly selective ad-hoc queries without managing infrastructure.

Answer

Azure Data Factory

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure Databricks

Question 15

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