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

Question 1

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table containing 5 TB of sales transactions. New data arrives continuously and is loaded daily. The company needs to load 500 GB of new data each day while allowing concurrent read queries on the most recent data without performance degradation. Which loading strategy optimizes both load speed and query performance?

Accepted Answer

Load data into a staging table, then use partition switching to swap the latest partition. Option C is correct because partition switching allows you to load new data into a staging table, then instantly swap the staging partition with the target table's latest partition using ALTER TABLE SWITCH. This minimizes metadata changes and avoids data movement, enabling fast loads while keeping the fact table online for concurrent read queries without blocking or performance degradation.

Answer

Use INSERT statements to add rows incrementally

Answer

Use CREATE TABLE AS SELECT (CTAS) to build a new table and rename it

Answer

Use PolyBase to load data directly into the fact table

Question 2

A business analyst needs to create interactive visualizations and share dashboards with colleagues using data stored in an Azure Synapse Analytics dedicated SQL pool. Which tool should the analyst use?

Accepted Answer

Power BI Desktop. Power BI Desktop is the correct tool because it is designed for creating interactive visualizations and dashboards, and it can connect directly to Azure Synapse Analytics dedicated SQL pools via the built-in Azure Synapse Analytics connector. This allows the business analyst to build reports and share them with colleagues through the Power BI service, meeting the requirement for interactive dashboards and collaboration.

Answer

Azure Data Studio

Answer

SQL Server Management Studio (SSMS)

Answer

Azure Machine Learning Studio

Question 3

A data analyst needs to run ad-hoc SQL queries on petabytes of log data stored as Parquet files in Azure Data Lake Storage Gen2. The queries join multiple tables and require high concurrency from multiple analysts. The solution should minimize cost by only paying for queries executed. 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 running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, making it cost-effective for sporadic, high-concurrency workloads where you only want to pay for the compute resources consumed during query execution.

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 4

A company has a data warehouse in Azure Synapse Analytics dedicated SQL pool. They need to load new sales data every night from a CSV file stored in Azure Data Lake Storage Gen2. The load process must be automated, scheduled, and have error handling for failed loads. Which Azure service should they use to orchestrate this process?

Accepted Answer

Azure Data Factory. Azure Data Factory is the correct choice because it is a cloud-based ETL service designed specifically for orchestrating and automating data movement and transformation at scale. It supports scheduled triggers, native connectors to Azure Data Lake Storage Gen2 and Azure Synapse Analytics, and built-in error handling via retry policies and failure activities, making it ideal for nightly CSV file loads.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 5

A manufacturing company collects sensor data from thousands of IoT devices. The data arrives as a stream of time-stamped readings with a fixed schema (DeviceID, Timestamp, Temperature, Pressure, Vibration). They need to store this data and support both real-time dashboards showing the last hour of data and complex analytical queries over years of historical data. The solution must minimize storage costs and provide sub-second response for real-time queries. Which Azure service is best suited for this workload?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is purpose-built for high-performance analysis of large volumes of streaming telemetry data. It supports ingestion from IoT hubs, automatic indexing for sub-second queries on recent data (e.g., last hour), and cost-effective long-term storage via hot/cold tiering for years of historical analytics. Its columnar storage and Kusto Query Language (KQL) are optimized for time-series and aggregation queries, making it ideal for this mixed real-time and historical workload.

Answer

Azure Cosmos DB with SQL API

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 6

A retail company needs to analyze streaming clickstream data from their website to detect shopping cart abandonment in real-time. They want to use Azure Stream Analytics to output results that can be visualized on a live dashboard. Which output sink allows the fastest data visualization for a real-time dashboard in Power BI?

Accepted Answer

Power BI dataset. Power BI dataset is the correct output sink because Azure Stream Analytics can directly stream data into a Power BI dataset via the Power BI output adapter, enabling real-time dashboard updates with sub-second latency. This integration uses the Power BI REST API to push streaming data events, which Power BI then visualizes immediately without requiring intermediate storage or batch processing.

Answer

Azure Blob Storage

Answer

Azure Event Hubs

Answer

Azure SQL Database

Question 7

A data engineer is designing a data lake architecture in Azure. They plan to first ingest raw data from various sources into a landing zone in Azure Data Lake Storage Gen2. Then they will clean, validate, and deduplicate that data in a second zone. Finally, they will create aggregated, business-ready datasets in a third zone for analysts. This layered approach is known as which architecture?

Accepted Answer

Medallion architecture. The medallion architecture (bronze, silver, gold) is a layered data lake design pattern where raw data lands in the bronze zone, is cleaned and deduplicated in the silver zone, and aggregated into business-ready datasets in the gold zone. This directly matches the described three-zone ingestion, transformation, and aggregation pipeline in Azure Data Lake Storage Gen2.

Answer

Star schema

Answer

Snowflake schema

Answer

Lambda architecture

Question 8

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 lakehouse pipeline starts with raw data ingestion, followed by transformation and loading into a curated layer, then creating tables for querying, and finally reporting with Power BI.

Question 9

A company needs to run complex SQL queries on petabytes of data stored in Azure Data Lake Storage Gen2. They want to pay only for the queries they run and do not want to manage any infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it enables running complex SQL queries directly against data in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, charging only for the amount of data processed, which aligns with the requirement to pay only for queries run and avoid infrastructure management.

Answer

Azure Synapse dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight

Question 10

A retail company receives daily sales data as CSV files in Azure Data Lake Storage Gen2. They need to load this data into an Azure Synapse Analytics dedicated SQL pool every night. The process must be automated, scheduled, and include error handling for failed loads. Which Azure service should they use to orchestrate this pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data integration service designed specifically for orchestrating and automating data pipelines. It supports scheduled triggers, can copy CSV files from Azure Data Lake Storage Gen2 into an Azure Synapse dedicated SQL pool, and provides built-in error handling via retry policies, activity-level error outputs, and pipeline failure notifications.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 11

A company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. Every night, they need to load 500 GB of new sales data from CSV files stored in Azure Data Lake Storage Gen2. The loading process must be automated, scheduled, and include error handling (e.g., skip corrupt rows and log them). Which Azure service should be used to orchestrate this load pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data orchestration service that supports scheduled execution, error handling (e.g., skipping corrupt rows via fault tolerance settings in the Copy activity), and native integration with Azure Data Lake Storage Gen2 and Azure Synapse dedicated SQL pool. ADF can automate the nightly 500 GB load using a trigger, and its mapping data flows or Copy activity can log errors to a separate file or table, meeting the requirement for automated, scheduled, and error-tolerant ingestion.

Answer

Azure Stream Analytics

Answer

Azure HDInsight

Answer

Azure Logic Apps

Question 12

A data warehouse team uses Azure Synapse Analytics dedicated SQL pool to serve both business executives running weekly reports and data scientists running complex ad-hoc queries on large fact tables. The ad-hoc queries often consume excessive resources and degrade performance for the weekly reports. The team needs to ensure that the weekly reports always get guaranteed resources regardless of other concurrent queries. Which Synapse feature should they use?

Accepted Answer

Workload classification. Workload classification in Azure Synapse Analytics dedicated SQL pool allows the team to assign incoming queries to specific workload groups with predefined resource allocations. By classifying the weekly report queries into a group with guaranteed minimum resources (e.g., using `CREATE WORKLOAD CLASSIFIER` with `IMPORTANCE` and `REQUEST_MIN_RESOURCE_PERCENT`), the team ensures those queries always receive the necessary resources, even when ad-hoc data scientist queries are running concurrently. This directly addresses the need for predictable performance for critical reports.

Answer

Result set caching

Answer

Materialized views

Answer

Columnstore indexes

Question 13

A manufacturing company needs to build an analytics solution for IoT sensor data. Thousands of devices send real-time temperature and vibration readings. The solution must: (1) ingest the streaming data reliably, (2) perform real-time aggregations (e.g., average temperature per device every minute), and (3) store the aggregated results in Azure Synapse Analytics for historical reporting and dashboards. Which combination of Azure services should be used?

Accepted Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Synapse Analytics. Azure Event Hubs is designed for high-throughput, reliable ingestion of streaming data from millions of IoT devices. Azure Stream Analytics can then perform real-time aggregations (like average temperature per device per minute) using a SQL-like query language. Finally, Azure Synapse Analytics provides a dedicated SQL pool or serverless SQL endpoint for storing and querying the aggregated results, enabling historical reporting and dashboards.

Answer

Azure IoT Hub -> Azure Data Factory -> Azure Cosmos DB

Answer

Azure Blob Storage -> Azure Databricks -> Azure SQL Database

Answer

Azure Service Bus -> Azure Functions -> Azure Table Storage

Question 14

A manufacturing company collects real-time temperature data from thousands of IoT sensors. They need to build an analytics solution that processes the streaming data, computes the average temperature per device every minute, and outputs the results to a Power BI dashboard for near real-time visualization. Which Azure service should they use for the real-time stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for scenarios like this: ingesting high-velocity data from IoT sensors, performing time-windowed aggregations (e.g., average temperature per device every minute), and outputting results directly to Power BI for near real-time dashboards. It natively supports SQL-like query language for defining windowed computations and has built-in connectors for both IoT Hub/Event Hubs (input) and Power BI (output), making it the most efficient and purpose-built service for this streaming analytics workload.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure SQL Database

Question 15

A company needs to build a centralized analytics platform that can query both structured data in a relational data warehouse and unstructured data in a data lake using a single SQL-based interface. They want to minimize data movement and use a serverless, on-demand compute model for ad-hoc queries. Which Azure service should they use?

Accepted Answer

B. Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it provides a SQL-based interface to query both structured data in a relational data warehouse and unstructured data in a data lake (e.g., Parquet, CSV, JSON) without moving data. It uses a serverless, on-demand compute model that charges per query, making it ideal for ad-hoc analytics with minimal data movement.

Answer

A. Azure SQL Database

Answer

C. Azure HDInsight

Answer

D. Azure Analysis Services