DP-900 Practice Exam 59 — 20 Questions

Question 1

A healthcare organization must build an analytics solution that processes streaming patient vitals data and provides real-time dashboards. The solution must also store historical data for compliance audits. Which combination of Azure services should the organization use?

Accepted Answer

Azure Stream Analytics for real-time processing and Azure SQL Database for historical storage and dashboards.. Azure Stream Analytics is purpose-built for real-time processing of streaming data, such as patient vitals, and can output directly to Power BI for live dashboards. Azure SQL Database provides a relational store for historical data, supporting compliance audits with point-in-time restore and long-term retention. This combination meets both real-time and historical requirements without unnecessary complexity.

Answer

Azure Synapse Analytics for real-time processing and Azure Blob Storage for archival.

Answer

Azure Event Hubs for ingestion and Azure Data Lake Storage for storage, with Power BI for dashboards.

Answer

Azure HDInsight with Apache Spark for streaming and Azure Cosmos DB for storage.

Question 2

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 3

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 Azure Synapse Analytics (dedicated SQL pool) supports automatic scaling through the ability to pause the compute resources when idle and resume them on demand, which effectively scales the workload based on demand without manual intervention. This feature reduces costs by stopping compute billing during inactivity and automatically resumes when a query or activity is detected, meeting the requirement for automatic scaling.

Answer

Read Scale-out replicas

Answer

Elastic Database Pools

Answer

Manual scale-up during peak hours

Question 4

A data analyst uses Power BI to create a report that combines data from Azure Synapse Analytics and an on-premises SQL Server database. The on-premises data must be refreshed every hour. Which component is required to connect to the on-premises data source?

Accepted Answer

On-premises data gateway. The on-premises data gateway is required to securely connect Power BI to on-premises SQL Server databases for scheduled refreshes. It acts as a bridge, transmitting data from the on-premises source to the Power BI service without opening inbound firewall ports. This component is specifically designed for self-service analytics scenarios where cloud services need to access on-premises data sources.

Answer

Azure VPN Gateway

Answer

Azure ExpressRoute

Answer

Azure Data Factory

Question 5

A company uses Azure Synapse Analytics to run large-scale batch processing jobs every night. The jobs currently take 6 hours to complete, but the business requires completion within 4 hours. Which action should the company take to improve job performance?

Accepted Answer

Increase the data warehouse units (DWUs) for the dedicated SQL pool.. Increasing the data warehouse units (DWUs) for the dedicated SQL pool scales the compute resources (CPU, memory, and I/O bandwidth) available to the Synapse SQL pool. This directly reduces the execution time of batch processing jobs by allowing more parallel processing, enabling the 6-hour job to complete within the required 4-hour window.

Answer

Replace PolyBase with Azure Data Factory for data movement.

Answer

Migrate from serverless SQL pool to dedicated SQL pool.

Answer

Move the underlying data to Azure Data Lake Storage Gen2.

Question 6

A company wants to analyze customer feedback from surveys and social media. The data includes both structured (ratings) and unstructured (comments) text. They plan to use Azure Cognitive Services for sentiment analysis. Which service should they use for text analytics?

Accepted Answer

Azure AI Language. Azure AI Language (formerly part of Azure Cognitive Services) provides pre-built text analytics capabilities, including sentiment analysis, key phrase extraction, and language detection. This service is specifically designed to process unstructured text data like survey comments and social media posts, making it the correct choice for analyzing customer feedback.

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB

Answer

Azure Machine Learning

Question 7

Refer to the exhibit. An administrator runs an Azure CLI command to show the status of a Synapse SQL pool. The output shown is returned. What does this output indicate about the SQL pool?

Accepted Answer

The SQL pool is online and available for queries.. The Azure CLI command `az synapse sql pool show` returns the provisioning state of the SQL pool. The output shows `"provisioningState": "Online"`, which directly indicates that the pool is provisioned, active, and ready to accept queries. This is the standard status for a running dedicated SQL pool in Azure Synapse Analytics.

Answer

The SQL pool was restored from a backup.

Answer

The SQL pool is paused.

Answer

The SQL pool is currently being restored.

Question 8

You are reviewing the Azure Data Factory mapping data flow configuration above. Which transformation is missing to ensure that only sales from the current year are loaded?

Accepted Answer

Filter transformation. The Filter transformation is used in mapping data flows to restrict rows based on a condition. To load only sales from the current year, you would apply a filter condition such as `year(SalesDate) == year(currentDate())`, which removes all rows not matching the current year. This is the correct transformation for row-level filtering.

Answer

Derived column transformation

Answer

Aggregate transformation

Answer

Window transformation

Question 9

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

Use staging with Azure Blob Storage and PolyBase.. Option D is correct because staging with Azure Blob Storage and PolyBase enables WAN optimization by using Azure Blob as an intermediate staging area, allowing Data Factory to leverage PolyBase's high-throughput bulk loading into Azure Data Lake Storage Gen2. This approach reduces the load on the on-premises network by transferring data first to Blob Storage over the WAN, then using PolyBase's parallel, optimized transfer within Azure, which is designed for large-scale data movement and can bypass typical WAN bottlenecks.

Answer

Use the Copy Activity with automatic partitioning.

Answer

Deploy a self-hosted integration runtime on-premises.

Answer

Enable parallel copy within the Copy Activity.

Question 10

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.. Option D is correct because 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, making it ideal for ad-hoc transformations 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 11

An organization has a data lake that contains both structured and unstructured data. They need to catalog the data assets and enable data discovery for users. Which Azure service should they use?

Accepted Answer

Microsoft Purview. Microsoft Purview is a unified data governance service that helps you manage and govern your on-premises, multicloud, and software-as-a-service (SaaS) data. It provides automated data discovery, sensitive data classification, and end-to-end data lineage, making it the correct choice for cataloging both structured and unstructured data assets in a data lake and enabling data discovery for users.

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Answer

Azure Data Lake Storage

Question 12

A multinational corporation uses Azure Data Factory to orchestrate data pipelines across multiple regions. The company notices that pipeline runs in the West Europe region consistently fail due to throttling errors from the source database. The source database is an Azure SQL Database in the same region. The company needs to reduce throttling while maintaining pipeline throughput. What should the company do?

Accepted Answer

Configure auto-scale on the Azure SQL Database to handle higher load.. The throttling errors originate from the Azure SQL Database source, which has resource limits (DTU or vCore-based). Configuring auto-scale on the Azure SQL Database dynamically adjusts its performance tier to handle higher concurrency and throughput, directly addressing the root cause of throttling while maintaining pipeline throughput. This aligns with the requirement to reduce throttling without reducing workload.

Answer

Increase the maximum number of Data Factory activity retries.

Answer

Stage extracted data in Azure Blob Storage before loading into the destination.

Answer

Use a self-hosted integration runtime in the same region to execute pipelines.

Question 13

Your company has a data pipeline in Azure Data Factory that runs daily. Recently, the pipeline started failing with timeouts. You suspect a downstream database is slow. What should you do to monitor and alert on pipeline run duration?

Accepted Answer

Configure alerts in Azure Monitor based on pipeline run duration. Option A is correct because Azure Monitor can be configured to create alerts based on metrics such as pipeline run duration. When the duration exceeds a threshold, an alert triggers, allowing proactive notification of slow downstream databases. This directly addresses the need to monitor and alert on pipeline performance issues.

Answer

Query the pipeline runs in Log Analytics

Answer

Check Azure Service Health for issues

Answer

Use Azure Advisor to check performance recommendations

Question 14

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 15

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

Configure the Power BI output with 'Exactly once' delivery mode.. Azure Stream Analytics supports exactly-once delivery semantics for Power BI output when the 'Exactly once' delivery mode is configured. This ensures that each event is processed and delivered precisely once, preventing duplicates in the Power BI dataset. The 'Exactly once' mode uses checkpointing and idempotent writes to guarantee consistency, which is critical for accurate real-time analytics from millions of IoT devices.

Answer

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

Answer

Set the output to Power BI with 'At least once' delivery mode.

Answer

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

Question 16

Which THREE components are part of Microsoft Fabric's end-to-end analytics platform? (Choose three.)

Accepted Answer

Synapse Data Engineering. Synapse Data Engineering is a core component of Microsoft Fabric, providing a unified platform for data ingestion, transformation, and orchestration using Spark and pipelines. It integrates seamlessly with OneLake for storage and Power BI for visualization, forming part of Fabric's end-to-end analytics solution.

Answer

Azure Machine Learning

Answer

Azure DevOps

Question 17

Refer to the exhibit. An Azure Data Factory pipeline JSON is shown. What does this pipeline do?

Accepted Answer

Copies filtered sales data from on-premises to Azure SQL Database. The pipeline uses a Copy activity with a source dataset pointing to an on-premises SQL Server (via a self-hosted integration runtime) and a sink dataset pointing to an Azure SQL Database. The source query includes a WHERE clause filtering sales data by a date range, so only filtered data is copied. This matches option C.

Answer

Copies sales data from Azure SQL Database to on-premises

Answer

Copies all sales data from on-premises to Azure SQL Database

Answer

Copies sales data from on-premises to Azure Data Lake Storage

Question 18

A company runs a critical workload in Azure Synapse Analytics. They need to ensure that if a single node fails, the data in the control node and compute nodes is not lost. Which configuration should they use?

Accepted Answer

No additional configuration is needed; Synapse provides built-in fault tolerance.. Azure Synapse Analytics (formerly SQL DW) is a distributed MPP (Massively Parallel Processing) system that automatically replicates data across multiple internal copies within the control node and compute nodes. This built-in fault tolerance ensures that if a single node fails, data is not lost because Synapse maintains at least three synchronous replicas of all data and metadata. No additional configuration is required for node-level failure protection.

Answer

Deploy a SQL Server Always On availability group.

Answer

Configure active geo-replication.

Answer

Use a Windows Server Failover Cluster.

Question 19

An organization wants to build a real-time dashboard that visualizes IoT sensor data as it arrives. Which Azure service should they use for processing the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a real-time analytics service designed to process streaming data from sources like IoT devices. It can ingest data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect patterns or anomalies, and output results to a dashboard or storage with sub-second latency, making it ideal for real-time IoT dashboards.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure Databricks

Question 20

Which TWO Azure services can be used to store semi-structured data? (Choose two.)

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a NoSQL database service that natively supports semi-structured data through its flexible schema model. It allows storing JSON documents, key-value pairs, and graph data without requiring a fixed schema, making it ideal for semi-structured data like user profiles, IoT telemetry, or product catalogs.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Power BI