DP-900 Describe an analytics workload on Azure — Timed 15 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?

15 minute time limit — choose an answer to begin.

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.