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

Question 1

A retail company ingests daily sales data from multiple stores as CSV files stored in Azure Blob Storage. The data must be cleaned and transformed using Spark, then loaded into Azure Synapse Analytics for large-scale reporting. The pipeline must run on a schedule, handle failures with retries, and minimize manual intervention. Which combination of Azure services should they use to orchestrate and execute this pipeline?

Accepted Answer

Azure Data Factory, Azure Databricks, and Azure Synapse Analytics.. Option A is correct because Azure Data Factory provides the orchestration and scheduling layer, Azure Databricks executes the Spark-based cleaning and transformation, and Azure Synapse Analytics serves as the target data warehouse for large-scale reporting. This combination supports retry policies for failure handling and minimizes manual intervention through automated pipeline execution.

Answer

Azure Stream Analytics, Azure Data Lake Storage, and Power BI.

Answer

Azure Functions, Azure SQL Database, and Azure Analysis Services.

Answer

Azure Logic Apps, Azure HDInsight, and Azure Cosmos DB.

Question 2

A retail company needs to analyze clickstream data from their website in real time to detect fraudulent activity and also run complex historical queries on months of data to identify shopping trends. They want a single service that can handle both streaming and batch analytics using a unified query language, minimizing data movement. Which Azure service should they use?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for real-time analytics on streaming data and can also handle complex historical queries over large volumes of data using the Kusto Query Language (KQL). It minimizes data movement by ingesting streaming data directly and storing it in a columnar format optimized for both real-time and batch queries, making it the ideal single service for this scenario.

Answer

Azure Stream Analytics

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Question 3

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 4

A manufacturing company uses IoT sensors to collect temperature and vibration data from machinery. They need to analyze the streaming data in real time to detect anomalies and trigger alerts. Additionally, they need to run complex historical queries on months of sensor data to identify equipment failure patterns. They want a single Azure service that can handle both real-time stream processing and large-scale batch analytics using a unified query language, minimizing the need for separate technologies. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified platform that combines both real-time stream processing (via Synapse Pipelines and Spark Streaming) and large-scale batch analytics (via Synapse SQL and Spark) using a single query language (T-SQL or Spark SQL). This eliminates the need for separate technologies, directly addressing the requirement for a single service to handle both streaming and historical batch analysis.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage Gen2

Answer

Azure HDInsight

Question 5

A company wants to build a modern data warehouse using a lakehouse architecture. They need to store raw data in its native format (e.g., CSV, JSON, Parquet) and also support BI reporting on curated, transformed data. They want to use a single storage layer for both raw and curated data. Which Azure service should they use as the core storage layer?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a single, unified storage layer that can store raw data in its native format (CSV, JSON, Parquet) in a hierarchical namespace, while also serving as the foundation for curated, transformed data used in BI reporting. It combines the scalability and cost-effectiveness of Azure Blob Storage with the file system semantics and ACLs needed for analytics workloads, making it the ideal core storage layer for a lakehouse architecture.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB

Question 6

A retail company processes petabytes of sales transaction data stored in Azure Data Lake Storage Gen2. They need to run recurring complex queries that involve large joins and aggregations. The queries must consistently complete within a fixed time window overnight. The company wants predictable performance and costs. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics Dedicated SQL pool. Azure Synapse Analytics Dedicated SQL pool provides reserved, fixed compute resources that ensure predictable performance and cost for recurring complex queries involving large joins and aggregations. It is designed for petabyte-scale data warehousing workloads with consistent SLAs, making it ideal for overnight batch processing within a fixed time window.

Answer

Azure Synapse Analytics Serverless SQL pool

Answer

Azure SQL Database

Answer

Azure Analysis Services

Question 7

A data engineering team is designing a modern data warehouse using Azure Synapse Analytics. They want to follow a lakehouse architecture where raw data is stored in its native format and then processed and curated for reporting. Which component in Azure Synapse Analytics is primarily used to store raw data in its original format without requiring a schema?

Accepted Answer

Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct component because it provides a scalable, hierarchical file system that can store raw data in any native format (e.g., CSV, JSON, Parquet) without requiring a predefined schema. This aligns with the lakehouse architecture's requirement to ingest and persist raw data as-is before any transformation or curation.

Answer

Dedicated SQL pool

Answer

Serverless SQL pool

Answer

Synapse Pipeline

Question 8

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 9

A financial services company uses a dedicated SQL pool in Azure Synapse Analytics to run large-scale analytical queries. During peak hours, complex aggregations consume excessive resources, causing slower performance for other users. The company needs to ensure that critical scheduled management reports always receive guaranteed resources and complete within a predictable timeframe, while less important ad-hoc queries do not interfere. Which feature should they implement to manage query resource allocation?

Accepted Answer

Workload management. Workload management in Azure Synapse Analytics allows you to classify, assign, and govern resources for queries by using workload groups and importance levels. By configuring workload groups, you can guarantee resources for critical scheduled management reports (e.g., assigning high importance) while limiting or deprioritizing less important ad-hoc queries, ensuring predictable completion times during peak hours.

Answer

Result set caching

Answer

Columnstore indexes

Answer

Table distribution

Question 10

A financial services company runs critical end-of-day reports in an Azure Synapse Analytics dedicated SQL pool. These reports require guaranteed resource allocation and must complete within a fixed time window. However, ad-hoc analytical queries from data scientists often consume resources, causing contention and delaying the critical reports. Which feature should the company implement to ensure the critical reports always receive sufficient resources?

Accepted Answer

A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.. Option A is correct because workload groups in Azure Synapse Analytics dedicated SQL pool allow you to assign a minimum percentage of resources (e.g., CPU and memory) to a specific workload, ensuring guaranteed resource allocation. By setting high importance for the critical reports, the system prioritizes them over ad-hoc queries, preventing resource contention and ensuring they complete within the fixed time window.

Answer

B. Enable result set caching on all queries to reduce execution time.

Answer

C. Implement materialized views for the aggregations used in the critical reports.

Answer

D. Use hash distribution for the fact tables to improve query parallelism.

Question 11

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 12

A company stores weather sensor data in Azure Data Lake Storage Gen2. Data scientists need to run large-scale transformations and machine learning experiments on this data using Python and Apache Spark. They want to collaborate using shared Jupyter notebooks. Which Azure service should they use for this analytical workload?

Accepted Answer

Azure Synapse Analytics (with Apache Spark pools). Azure Synapse Analytics with Apache Spark pools provides a fully managed Spark environment that supports Python and allows data scientists to run large-scale transformations and machine learning experiments. It integrates directly with Azure Data Lake Storage Gen2 for reading and writing data, and supports collaborative Jupyter notebooks for shared development. This makes it the correct choice for the described analytical workload.

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Answer

Azure SQL Database

Question 13

A retail company wants to analyze years of historical sales data stored as CSV files in Azure Blob Storage. The analytics solution must be serverless, allow T-SQL queries without managing infrastructure, and integrate directly with Power BI. Which Azure service should the company use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a serverless, on-demand query service that can directly query CSV files stored in Azure Blob Storage using T-SQL without requiring any infrastructure management. It integrates natively with Power BI via the T-SQL endpoint, enabling direct data visualization from the queried files.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Analysis Services

Question 14

A business analyst needs to explore and create interactive visualizations of sales data stored in Azure Data Lake Storage Gen2 without writing SQL code. Which Azure service is best suited for this drag-and-drop data exploration?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct choice because it provides a drag-and-drop interface for creating interactive visualizations and exploring data without requiring SQL code. It can directly connect to Azure Data Lake Storage Gen2 using Power Query or the ADLS connector, enabling business analysts to build reports and dashboards through intuitive visual interactions.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure Databricks

Question 15

A financial institution needs to run complex queries against petabytes of historical trading data stored in Azure Data Lake Storage. The queries must be efficient and use columnar storage format. Which technology should they use to process this data?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a cloud-based analytics service designed for petabyte-scale data warehousing. It supports PolyBase to query data directly from Azure Data Lake Storage and uses a columnar storage format (via clustered columnstore indexes) to enable efficient, high-performance analytical queries on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage