DP-900 Describe an analytics workload on Azure — 20 Questions

Question 1

A logistics company receives real-time GPS tracking data from its delivery fleet via Azure Event Hubs. The data is a continuous stream of location updates (vehicle ID, latitude, longitude, timestamp). Additionally, the company has daily static route plan files in CSV format stored in Azure Data Lake Storage Gen2. The operations team needs to combine the live GPS stream with the route plans to create a near real-time dashboard showing if delivery vehicles are on schedule. They also want to run historical queries on both the stream data and route plans using T-SQL, without moving the data to another store. Which Azure service should they use as the primary analytics platform?

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

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can combine real-time streaming data from Azure Event Hubs (by first landing it in ADLS via Event Hubs Capture or a Synapse Pipeline) with static CSV route plans in Azure Data Lake Storage Gen2. It supports T-SQL queries directly against the files in ADLS using SQL Serverless, enabling near real-time dashboards and historical analysis without moving data to a separate store.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure Databricks

Question 2

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 offers both dedicated and serverless SQL pools. The serverless SQL pool supports T-SQL queries, scales compute independently, and does not require provisioning fixed resources, aligning perfectly with the requirement. While the dedicated pool is provisioned, the Synapse service as a whole includes the serverless option, making it the correct choice.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database (creating a secondary replica)

Question 3

A retail company needs to build an analytics pipeline on Azure. They ingest sales data from multiple store systems and an online e-commerce platform. The data must be cleaned, transformed, and loaded into a data warehouse for reporting. The company wants to use a modern ELT (Extract, Load, Transform) approach where raw data is stored first and then transformed. Order the following steps in the correct sequence for this pipeline. (Drag the steps into the correct order.)

Accepted Answer

Ingest data from sources using Azure Data Factory.. In an ELT (Extract, Load, Transform) approach, data is first extracted from source systems (A) and loaded as raw data into a scalable storage like a data lake (B). Once loaded, the raw data is transformed and cleaned (C). Finally, the transformed data is loaded into a data warehouse (D) for reporting and analysis (E).

Question 4

A company is building a modern data warehouse on Azure using a lakehouse approach. Arrange the following steps in the correct order to implement a typical pipeline that starts with raw data ingestion and ends with business reporting.

Accepted Answer

Ingest raw data from various sources into Azure Data Lake Storage Gen2. The explanation correctly describes the typical lakehouse pipeline order: Ingestion -> Transformation -> Loading -> Reporting. The steps A, B, C, D, as listed, are indeed in this correct logical sequence. However, the question format does not allow for selecting this sequence as an answer.

Question 5

A data engineering team wants to build a batch analytics pipeline. The raw data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The final output will be a set of tables in Azure Synapse Analytics (dedicated SQL pool) that will be used to create reports in Power BI. Arrange the following steps in the correct order for a typical ETL process.

Accepted Answer

Ingest raw data from ADLS Gen2 into Azure Databricks for transformation using Azure Data Factory pipelines. The explanation states 'extract raw data to ADLS', which contradicts the stem's premise that raw data is already stored in Azure Data Lake Storage Gen2. The first step (A) correctly describes ingesting/extracting data *from* ADLS Gen2. Furthermore, the explanation introduces an additional transformation step 'within Synapse' after loading into staging ('load into Synapse staging, then transform within Synapse to final tables'). This implies an ELT pattern or multi-stage transformation not explicitly detailed in the provided options A, B, C, which describe a simpler ETL flow where transformation is completed in Databricks (B) before loading the final transformed data into Synapse (C). A more accurate explanation aligning with the provided steps would be: 'A typical data pipeline for this scenario follows the order: Ingest raw data from ADLS Gen2 into a processing engine like Azure Databricks (A), transform the data (B), load the transformed data into Azure Synapse Analytics (C), and finally create Power BI reports (D).'

Question 6

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 7

Drag and drop the steps to perform a point-in-time restore of an Azure SQL Database in the correct order.

Accepted Answer

Navigate to the database, select Restore, choose Point-in-time and configure, then click OK.. To perform a point-in-time restore of an Azure SQL Database, you must first navigate to the specific database resource in the Azure portal. From the database's overview or menu, you then select the 'Restore' option. Within the restore blade, you choose 'Point-in-time' as the restore type, configure the desired restore point and target database details, and finally click 'OK' or 'Review + create' to initiate the restore operation. Option A accurately reflects this sequence of steps.

Answer

Select Restore, then navigate to database, then choose Point-in-time, then click OK.

Answer

Navigate to database, choose Point-in-time, then select Restore, then click OK.

Answer

Navigate to database, select Restore, click OK, then choose Point-in-time.

Question 8

A company uses Azure Synapse Analytics dedicated SQL pool to run large-scale analytics. The data engineering team notices that queries are slow due to excessive data movement between distributions. Which index type should be recommended to minimize data movement for fact tables that are frequently joined on a specific column?

Accepted Answer

Hash-distributed table. In Azure Synapse dedicated SQL pool, data movement occurs when rows from different distributions need to be combined for a join. By using a hash-distributed table with the same distribution column as the join key, matching rows co-locate on the same distribution, eliminating cross-distribution shuffling. Indexes like columnstore affect storage and scan performance but do not control data distribution. Therefore, D is the correct choice.

Answer

Ordered clustered columnstore index

Answer

Clustered columnstore index

Answer

Round-robin distributed table

Question 9

A company uses Azure Databricks for data engineering. The team wants to implement a medallion architecture (bronze, silver, gold) to organize data quality layers. In which layer should data be stored in a format optimized for analytics and reporting?

Accepted Answer

Gold layer. The gold layer in a medallion architecture contains data that has been refined, aggregated, and validated for business-level analytics and reporting. This layer stores data in a format optimized for query performance, such as Delta Lake with partitioning and Z-ordering, enabling efficient consumption by tools like Power BI or Azure Synapse.

Answer

Bronze layer

Answer

Silver layer

Answer

Lakehouse layer

Question 10

Which THREE components are required to implement a real-time analytics solution using Azure Stream Analytics? (Choose three.)

Accepted Answer

Stream input (e.g., Azure Event Hubs). A stream input like Azure Event Hubs is required because Azure Stream Analytics processes data in real time from a streaming source. Event Hubs ingests millions of events per second, providing the low-latency, ordered event stream that Stream Analytics consumes via its input binding. Without a streaming source, the service cannot perform continuous, real-time analytics.

A Stream Analytics query is essential as it defines the processing logic (e.g., filtering, aggregation, transformation) that Azure Stream Analytics applies to the incoming data. Without a query, the service would not know how to process the stream.

An output sink, such as Azure Synapse Analytics, is necessary to receive the results of the Stream Analytics job. After data is processed by the query, it must be sent to a destination for storage, visualization, or further action. Without an output, the processed data would have nowhere to go.

Answer

Batch input (e.g., Azure Blob Storage)

Answer

Machine learning model

Question 11

A company uses Azure Synapse Analytics for its data warehouse. They notice that queries against a large fact table are slow. The table is partitioned by month and uses clustered columnstore index. Which action would most likely improve query performance?

Accepted Answer

Rebuild the clustered columnstore index.. Rebuilding the clustered columnstore index reorganizes data, removes fragmentation, and improves compression and query performance, especially when there have been many updates or deletes. Option B is wrong: Changing to daily partitions may increase partition count and overhead without addressing fragmentation. Option C is wrong: Converting to a rowstore index would reduce performance for analytical queries. Option D is wrong: Increasing DWU may help but is not a targeted fix for index fragmentation.

Answer

Change partitioning to daily partitions.

Answer

Convert the table to a clustered rowstore index.

Answer

Increase the data warehouse performance level (DWU).

Question 12

An organization uses Azure Stream Analytics to process real-time IoT data from millions of devices. They need to ensure that the output is exactly once delivery semantics to a Power BI dataset. Which output configuration should they use?

Accepted Answer

Set the output to Power BI with 'At least once' delivery mode.. Azure Stream Analytics supports at-least-once delivery semantics for Power BI output, not exactly-once. To achieve exactly-once delivery, an intermediary service like Azure Data Explorer or Event Hubs with idempotent processing is required, but directly from Stream Analytics to Power BI, exactly-once is not supported. Therefore, the correct configuration is to set the output to Power BI with 'At least once' delivery mode, which is the default and only available mode.

Answer

Output to Azure Data Explorer and then use a Power BI dashboard.

Answer

Output to Azure Event Hubs and then use Azure Functions to write to Power BI.

Answer

Configure the Power BI output with 'Exactly once' delivery mode.

Question 13

Which TWO Azure services can be used to perform interactive data analytics on large datasets without managing infrastructure? (Choose two.)

Accepted Answer

Azure Synapse Analytics Serverless SQL pool. Azure Synapse Analytics Serverless SQL pool is correct because it enables interactive analytics on large datasets stored in Azure Data Lake Storage or other sources using T-SQL queries, without requiring any infrastructure management. It automatically scales compute resources based on query demand, allowing analysts to run ad-hoc queries on petabyte-scale data without provisioning or managing servers.

Azure Databricks is also correct as it provides a managed Apache Spark environment, which is excellent for interactive data analytics on large datasets using notebooks with languages like SQL, Python, R, and Scala. While users configure clusters, the underlying infrastructure (VMs, OS, patching) is managed by the service, significantly reducing infrastructure management overhead compared to self-hosting Spark. Databricks also offers serverless SQL warehouses for a fully serverless interactive analytics experience.

Answer

Azure SQL Database

Answer

Azure Data Factory

Answer

Azure Data Lake Storage Gen2

Question 14

You run the above Kusto query in Azure Data Explorer. What does the query return?

Accepted Answer

The top 5 states with the most tornado events.. The explanation describes a Kusto query that would lead to option B, but the actual query is not provided in the STEM. Therefore, it cannot be confirmed if the explanation accurately reflects the intended question.

Answer

The total number of tornado events across all states.

Answer

The top 5 years with the most tornadoes.

Answer

The number of events by type.

Question 15

A company uses Azure Data Lake Storage Gen2 to store raw data files. Data engineers need to transform this data using a serverless approach without managing infrastructure. Which Azure service should they use?

Accepted Answer

Azure Databricks with serverless SQL warehouses.. Azure Databricks with serverless SQL warehouses provides a fully serverless compute option for transforming data stored in Azure Data Lake Storage Gen2. It eliminates infrastructure management by automatically scaling compute resources based on workload demands, offering powerful SQL-based transformation capabilities suitable for complex data engineering pipelines without provisioning clusters.

Answer

Azure Data Factory with mapping data flows.

Answer

Azure HDInsight with Apache Spark clusters.

Answer

Azure Synapse Analytics with serverless SQL pool.

Question 16

A company uses Azure Data Factory to copy data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The transfer must be accelerated using WAN optimization. Which Data Factory feature should the company enable?

Accepted Answer

Deploy a self-hosted integration runtime on-premises.. A self-hosted integration runtime (IR) is required to connect to on-premises SQL Server and can be configured with data compression and other network optimizations to improve transfer performance over WAN. It is the foundational component for hybrid data movement, enabling secure and efficient data transfer. Options A and C are general performance features but do not specifically address WAN optimization. Option D is incorrect because PolyBase is designed for loading data into Azure Synapse Analytics, not Azure Data Lake Storage Gen2, and therefore staging with PolyBase does not apply to the target destination.

Answer

Use the Copy Activity with automatic partitioning.

Answer

Enable parallel copy within the Copy Activity.

Answer

Use staging with Azure Blob Storage and PolyBase.

Question 17

Which TWO Azure services can be used to build a real-time analytics solution that ingests streaming data and provides dashboards with low latency? (Choose two.)

Accepted Answer

Power BI. Azure Stream Analytics is correct because it is a real-time analytics service designed to ingest, process, and analyze high volumes of streaming data from various sources (like Event Hubs or IoT Hubs) with low latency. It can perform transformations, aggregations, and anomaly detection on the fly. Power BI is correct because it can connect to real-time data sources like Azure Stream Analytics or Event Hubs to create streaming datasets and dashboards that update automatically with low latency. It supports push datasets and real-time tiles, enabling live visualization of streaming data without batch processing delays.

Answer

Azure Data Factory

Answer

Azure Data Lake Storage Gen2

Answer

Azure HDInsight with Spark

Question 18

Which TWO Azure services can be used to perform data transformation in a serverless manner? (Choose two.)

Accepted Answer

Azure Synapse serverless SQL pool. Azure Synapse serverless SQL pool (Option B) is correct because it allows you to query and transform data directly from data lake files (e.g., Parquet, CSV) using T-SQL without provisioning any dedicated compute resources. It uses a pay-per-query model, making it inherently serverless for data transformation tasks.

Azure Data Factory mapping data flows (Option C) is also correct. Mapping data flows provide a visual, code-free way to transform data at scale. While they leverage Spark clusters under the hood, these clusters are fully managed by Azure Data Factory. Users define the transformations, and ADF provisions, scales, and terminates the compute resources automatically, abstracting away server management. This aligns with the serverless paradigm where you pay for execution time and data processed, not for provisioned servers.

Answer

Azure Databricks with Apache Spark

Answer

Azure SQL Database

Answer

Azure HDInsight with Hive

Question 19

A company uses Azure Synapse Analytics to run large-scale analytics on sales data. They need to ensure that the workload can automatically scale based on demand without manual intervention. What feature should they configure?

Accepted Answer

Auto-pause and auto-resume. Auto-pause and auto-resume is the correct feature because it automatically stops compute resources when the dedicated SQL pool is idle, scaling down to zero, and resumes on demand when a query or activity is detected. This effectively scales the workload based on demand without manual intervention, meeting the requirement for automatic scaling. While it does not dynamically adjust compute size during active workloads, it provides cost-effective scaling by eliminating compute costs during inactivity and automatically restoring resources when needed.

Answer

Read Scale-out replicas

Answer

Elastic Database Pools

Answer

Manual scale-up during peak hours

Question 20

Which TWO options are valid ways to ingest data into Azure Data Lake Storage Gen2?

Accepted Answer

AzCopy. AzCopy is a command-line utility designed specifically for copying data to and from Azure Blob Storage and Azure Data Lake Storage Gen2 using REST APIs. It supports high-performance, parallel transfers and can handle large datasets efficiently, making it a valid ingestion tool. Azure Data Factory (ADF) is a cloud-based data integration service that allows you to create, schedule, and orchestrate data workflows. It provides a wide range of connectors, including one for Azure Data Lake Storage Gen2, enabling robust and scalable data ingestion from various sources.

Answer

Power BI

Answer

Azure SQL Database

Answer

Azure DevOps