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

Question 1

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 2

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 3

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 4

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 5

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

Question 6

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

Question 7

Match each Azure data tool to its purpose.

Question 8

Match each data type to its category in Azure.

Question 9

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

Question 10

A data engineer needs to design a solution for a healthcare organization that must store patient records for 7 years to comply with regulatory requirements. The data will be accessed infrequently after the first year. Which Azure storage tier should be used for data older than one year?

Accepted Answer

Cool tier. The Cool tier is designed for data that is accessed infrequently but must be available immediately when needed, with a minimum storage duration of 30 days. Since patient records older than one year are accessed rarely but may still require low-latency retrieval for compliance audits, Cool tier balances cost and accessibility. Hot tier is for frequent access, Archive tier has a retrieval latency of hours (not suitable for occasional access), and Transaction-optimized is not a standard Azure Blob Storage tier.

Answer

Transaction-optimized tier

Answer

Archive tier

Answer

Hot tier

Question 11

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. Hash-distributed tables distribute rows across distributions based on a hash of a chosen column. When fact tables are frequently joined on that specific column, using the same distribution column ensures that matching rows from both tables reside on the same distribution, eliminating the need to shuffle data between distributions during the join. This minimizes data movement and significantly improves query performance in Azure Synapse dedicated SQL pools.

Answer

Ordered clustered columnstore index

Answer

Clustered columnstore index

Answer

Round-robin distributed table

Question 12

A data analyst needs to create a report in Power BI that combines sales data from Azure SQL Database and inventory data from Azure Cosmos DB. The report should refresh daily. Which Power BI feature should be used to combine these data sources?

Accepted Answer

Power Query. Power Query allows connecting to multiple data sources and combining them through queries and merges. Option A is wrong because Quick Measures are for creating calculations within a single table. Option B is wrong because Power BI Desktop is the tool, not a feature for combining sources. Option D is wrong because DAX is a formula language for creating calculations, not for data ingestion.

Answer

Quick Measures

Answer

Data Analysis Expressions (DAX)

Answer

Power BI Desktop

Question 13

A company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data is partitioned by date and sensor ID. A data scientist needs to efficiently query only the last 7 days of data for a specific sensor. Which strategy minimizes the amount of data scanned?

Accepted Answer

Use a directory structure that enables partition elimination. Option A is correct because Azure Data Lake Storage Gen2 supports hierarchical directory structures that enable partition elimination at the storage layer. By organizing data under a path like `/sensorID=123/date=2025-03-20/`, a query engine (e.g., Azure Synapse Serverless SQL or Spark) can skip entire directories that do not match the filter, drastically reducing the amount of data scanned.

Answer

Create a view that filters on date and sensor ID

Answer

Read all Parquet files and filter using a WHERE clause

Answer

Create an index on the date and sensor ID columns

Question 14

A multinational corporation uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage. The security team requires that access to specific columns containing personally identifiable information (PII) be restricted based on the user's role. Which feature should be implemented?

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics serverless SQL pool allows you to restrict access to specific columns containing PII based on the user's role or identity. By granting or denying SELECT permissions on individual columns, you can ensure that only authorized users see sensitive data while others see NULL or an error. This directly meets the requirement to restrict column access by role.

Answer

Row-level security (RLS)

Answer

Azure Purview data classification

Answer

Dynamic data masking

Question 15

A company wants to build a near-real-time analytics solution on Azure. IoT devices send telemetry data to Azure Event Hubs. The data must be processed and stored in Azure Cosmos DB for low-latency queries. Which Azure service should be used to process the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed specifically for real-time analytics on high-throughput data streams from sources like Azure Event Hubs. It can run SQL-like queries to filter, aggregate, and join streaming data, and output results directly to Azure Cosmos DB for low-latency queries, making it ideal for near-real-time IoT analytics.

Answer

Azure Logic Apps

Answer

Azure Functions

Answer

Azure Data Factory