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

Question 1

A company wants to build a data lake on Azure for storing structured, semi-structured, and unstructured data. The solution must support fast queries on structured data without moving data to a separate store. Which Azure service should they use?

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

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, enabling both a data lake for unstructured and semi-structured data and fast, SQL-like queries on structured data using Azure Synapse Analytics or PolyBase without moving data. It supports the ABFS (Azure Blob File System) driver for high-performance analytics and integrates directly with query engines like Apache Spark and Azure Synapse SQL.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 2

A company needs to analyze streaming data from IoT devices in real time. They want to identify anomalies and trigger alerts. Which Azure service should they use as the core processing engine?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is purpose-built for real-time stream processing, allowing you to define SQL-like queries that run continuously against streaming data from sources like IoT Hub. It can detect anomalies and trigger alerts on the fly, making it the correct core processing engine for this IoT scenario.

Answer

Azure Synapse Analytics

Answer

Azure Databricks

Answer

Azure Data Lake Storage

Question 3

A data analyst needs to create a Power BI report that combines sales data from Azure SQL Database and marketing data from a CSV file stored in Azure Blob Storage. The report should refresh automatically. What is the recommended approach?

Accepted Answer

Use Power Query in Power BI Desktop to combine the data and publish with scheduled refresh. Option A is correct because Power Query in Power BI Desktop can natively connect to both Azure SQL Database and Azure Blob Storage CSV files, combine them into a single data model, and then publish to the Power BI Service where scheduled refresh keeps the report current. This is the standard, recommended approach for mixed-source reporting with automatic refresh. Option B is wrong because exporting SQL data to Excel adds manual steps and breaks automatic refresh. Option C is unnecessary overhead since Azure Data Factory is not required just to combine two sources for a Power BI report. Option D is wrong because DirectQuery is not supported for CSV files in Blob Storage and would not provide the required automatic refresh for both sources.

Answer

Export the SQL data to Excel and combine with the CSV in Power BI

Answer

Use Azure Data Factory to merge the data into a single SQL table

Answer

Use DirectQuery mode from Power BI for both sources

Question 4

Refer to the exhibit. An administrator is configuring aggregations in Power BI Premium to improve performance on a large dataset. The aggregation is defined on the Sales table with SUM(Amount) grouped by ProductCategory, Region, and Date at the monthly level. However, some reports that query daily data are still slow. What is the most likely reason?

Accepted Answer

The aggregation level is monthly, but queries need daily granularity. The aggregation is defined at the monthly level (grouping by month), but the slow reports are querying daily data. Power BI aggregations work by pre-aggregating data at a specified granularity; when a query requests a lower level of detail (daily), the aggregation cannot satisfy the query, so Power BI falls back to scanning the full detailed dataset, causing slow performance. To improve daily queries, an additional aggregation at the daily level would be needed.

Answer

The dataset is too large for aggregations

Answer

The aggregation is not in DirectQuery mode

Answer

The aggregation has too many dimensions

Question 5

Which TWO are benefits of using Azure Synapse Analytics for a data warehouse workload?

Accepted Answer

Ability to query data in the data lake using serverless SQL. Option A is correct because Azure Synapse Analytics includes a built-in serverless SQL pool that lets you query files directly in the data lake (e.g., Parquet, CSV, JSON in Azure Data Lake Storage) using T-SQL without provisioning or loading data into dedicated storage. Option C is correct because Synapse unifies data integration (Azure Synapse Pipelines, based on Azure Data Factory), enterprise data warehousing (dedicated SQL pools), and big data analytics (Apache Spark pools) within a single workspace and studio experience. Option B is not correct because Synapse does not provide native MongoDB source support; MongoDB connectivity would require custom or third-party tooling rather than a built-in connector. Option D is not correct because Synapse does not automatically index all data; indexing behavior depends on the pool type and configuration (e.g., clustered columnstore indexes in dedicated SQL pools must be defined). Option E is not correct because built-in email alerts for query performance are not a native Synapse feature; monitoring is done via Azure Monitor, Log Analytics, and Synapse Studio metrics rather than automatic email alerts.

Answer

Native support for MongoDB data sources

Answer

Automatic indexing of all data

Answer

Built-in email alerts for query performance

Question 6

You are the data engineer for a large retail company. The company has an existing on-premises SQL Server database with 10 years of transactional data. They want to move this data to Azure to enable advanced analytics using Azure Synapse Analytics. The data includes customer orders, product details, and inventory. The solution must minimize data movement and support both batch and real-time analytics. The company also wants to use Power BI for reporting. They have a limited budget and prefer a serverless option for compute. You are evaluating the following approaches: A) Use Azure Data Factory to copy all data to Azure Data Lake Storage Gen2, then use Azure Synapse Serverless SQL pool to query the data, and finally connect Power BI to the serverless SQL endpoint. B) Use Azure Database Migration Service to migrate the SQL Server database to Azure SQL Database, then use Azure Synapse Analytics with a dedicated SQL pool to perform analytics, and connect Power BI to the dedicated pool. C) Use Azure Data Factory to copy all data to Azure Blob Storage, then use Azure Stream Analytics to perform real-time analytics, and connect Power BI directly to Stream Analytics output. D) Use Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, use Azure Synapse Serverless SQL pool for batch analytics, and use Azure Event Hubs and Stream Analytics for real-time data, with Power BI connecting to both serverless SQL and Stream Analytics. Which approach best meets the requirements?

Accepted Answer

Use Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, use Azure Synapse Serverless SQL pool for batch analytics, and use Azure Event Hubs and Stream Analytics for real-time data, with Power BI connecting to both serverless SQL and Stream Analytics.. Best meets the requirements because it uses Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, enabling cost-effective storage and batch analytics via Azure Synapse Serverless SQL pool (serverless compute). It also incorporates Azure Event Hubs and Stream Analytics for real-time data ingestion and analytics, with Power BI connecting to both the serverless SQL endpoint and Stream Analytics output. This minimizes data movement by keeping data in the lake, supports both batch and real-time analytics, and uses a serverless option to stay within a limited budget.

Answer

Use Azure Data Factory to copy all data to Azure Data Lake Storage Gen2, then use Azure Synapse Serverless SQL pool to query the data, and finally connect Power BI to the serverless SQL endpoint.

Answer

Use Azure Database Migration Service to migrate the SQL Server database to Azure SQL Database, then use Azure Synapse Analytics with a dedicated SQL pool to perform analytics, and connect Power BI to the dedicated pool.

Answer

Use Azure Data Factory to copy all data to Azure Blob Storage, then use Azure Stream Analytics to perform real-time analytics, and connect Power BI directly to Stream Analytics output.

Question 7

Your company is developing a new analytics solution to track customer sentiment from social media feeds. The data arrives as a continuous stream of JSON messages. The solution must process the data in near real-time, enrich it with customer profile data stored in Azure Cosmos DB, and then store the results in a data lake for historical analysis. The team wants to use a low-code approach for the data processing logic. You are considering the following architectures: A) Use Azure Event Hubs to ingest the stream, Azure Stream Analytics to process and enrich the data using Cosmos DB as a reference data source, and output to Azure Data Lake Storage Gen2. B) Use Azure IoT Hub to ingest the stream, Azure Databricks to process the data, and write to Azure Blob Storage. C) Use Azure Event Hubs to ingest the stream, Azure Functions to process each message, query Cosmos DB for enrichment, and write to Azure Data Lake Storage Gen2. D) Use Azure Event Hubs to ingest the stream, Azure Data Factory to execute a mapping data flow for enrichment, and write to Azure Data Lake Storage Gen2. Which architecture best meets the requirements of near real-time processing, enrichment, and low-code?

Accepted Answer

Option A. Azure Stream Analytics provides a low-code, SQL-based approach for near real-time processing, and it can natively enrich streaming data by using Azure Cosmos DB as a reference data source via a JOIN operation. The output is directly written to Azure Data Lake Storage Gen2, meeting all requirements without custom code.

Answer

Option C

Answer

Option D

Answer

Option B

Question 8

A small business wants to start using Azure for analytics. They have a few CSV files stored on-premises that they want to analyze. They have no budget for complex infrastructure and prefer a fully managed, serverless solution. They need to create interactive visualizations and share them with their team. The data does not change frequently, so they are okay with daily refreshes. Which of the following options should they choose? A) Upload the CSV files to Azure Data Lake Storage Gen2, use Azure Databricks to create a data processing pipeline, and then use Power BI to visualize the results. B) Upload the CSV files to Azure Blob Storage, use Azure Data Factory to load the data into Azure SQL Database, and then use Power BI to connect and visualize. C) Upload the CSV files to OneDrive for Business, use Power BI Desktop to import the data, and publish to Power BI Service with scheduled refresh. D) Upload the CSV files to Azure Data Lake Storage Gen2, use Azure Synapse Serverless SQL pool to query the data, and then use Power BI to connect. Which option is the simplest and most cost-effective?

Accepted Answer

Option C. It uses OneDrive for Business as a simple storage location, Power BI Desktop for importing CSV data, and Power BI Service for publishing and sharing interactive visualizations with scheduled daily refresh. This is fully managed, serverless, and requires no complex infrastructure, aligning perfectly with the small business's budget and simplicity requirements.

Answer

Option B

Answer

Option D

Answer

Option A

Question 9

A retail company uses Azure Data Lake Storage Gen2 to store raw clickstream data. They need to process this data using Azure Databricks to create hourly aggregated reports. The data pipeline must minimize costs while meeting a five-minute processing SLA. What is the most cost-effective compute option?

Accepted Answer

Use job clusters with pool-based allocation. Job clusters with pool-based allocation are the most cost-effective compute option for this scenario because job clusters are ephemeral—they start only when a job runs and terminate automatically after completion, eliminating idle costs. Pool-based allocation further reduces startup latency by maintaining a warm pool of pre-initialized VMs, enabling the pipeline to meet the five-minute SLA without paying for always-on compute.

Answer

Use interactive clusters with autoscaling

Answer

Use Azure Synapse Serverless SQL pools

Answer

Provision a dedicated SQL pool in Azure Synapse

Question 10

A company wants to build a real-time analytics dashboard for IoT sensor data. Which combination of Azure services should they use?

Accepted Answer

Azure Stream Analytics and Power BI. Azure Stream Analytics is a real-time event processing engine that can ingest IoT sensor data from sources like Azure Event Hubs, apply SQL-based queries to detect patterns or anomalies, and output results directly to Power BI for live dashboard visualization. This combination provides end-to-end streaming analytics with sub-second latency, which is essential for real-time dashboards.

Answer

Azure HDInsight and Azure Databricks

Answer

Azure Logic Apps and Azure SQL Database

Answer

Azure Data Factory and Azure Analysis Services

Question 11

A company uses Azure Synapse Analytics to run a data warehouse. They need to load 500 GB of historical data from Azure Blob Storage into a staging table. They want the fastest load performance with minimal administrative overhead. Which method should they use?

Accepted Answer

Use PolyBase with the COPY INTO statement. PolyBase with the COPY INTO statement is the fastest method for loading large volumes of data into Azure Synapse Analytics because it leverages the Massively Parallel Processing (MPP) architecture to read data directly from Azure Blob Storage in parallel across all compute nodes, bypassing any single-node bottleneck. It also requires minimal administrative overhead as it is a native T-SQL command with automatic schema inference and no external tools or orchestration to manage.

Answer

Use SQL Server Integration Services (SSIS)

Answer

Use Azure Data Factory with Copy activity

Answer

Use the bcp utility

Question 12

A company has a Power BI dashboard that refreshes daily from an Azure SQL Database. During refresh, the database experiences high CPU usage that impacts transactional applications. They need to minimize impact while keeping the dashboard up-to-date. What should they do?

Accepted Answer

Configure Azure SQL Database with a readable secondary replica. Configuring Azure SQL Database with a readable secondary replica offloads read-only workloads, such as Power BI refreshes, to the secondary replica. This eliminates CPU contention on the primary replica, protecting transactional applications from performance degradation while keeping the dashboard up-to-date with near-real-time data. Option A (Disable Query Store) is irrelevant to reducing refresh CPU impact. Option B (read-only user) does not physically separate the read workload. Option C (lower-capacity license) does not change query execution location.

Answer

Disable the Query Store in the database

Answer

Create a read-only user for Power BI

Answer

Set Power BI to use a lower-capacity license

Question 13

Which TWO Azure services are primarily used for batch processing of large data volumes?

Accepted Answer

Azure Databricks. Azure Databricks (A) is correct because it is an Apache Spark-based analytics platform that natively supports distributed batch processing of large data volumes through Spark jobs, notebooks, and clusters. Azure Synapse Pipelines (C) is correct because it provides orchestration and data integration capabilities (built on Azure Data Factory) specifically designed to run batch data movement and transformation workloads at scale across large datasets. Azure Data Lake Storage (B) is not correct because it is a storage layer, not a processing service, even though it commonly stores data consumed by batch jobs. Azure Stream Analytics (D) is not correct because it is designed for real-time streaming analytics on continuous data streams rather than batch processing. Azure Logic Apps (E) is not correct because it is a workflow automation and integration service for orchestrating apps, not a large-scale batch data processing engine.

Answer

Azure Data Lake Storage

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Question 14

Refer to the exhibit. You are reviewing an ARM template that deploys a SQL database in Azure Synapse. The template sets the storageAccountType to GRS. What is a valid concern regarding cost and performance?

Accepted Answer

GRS will increase storage costs and may cause higher latency. Geo-redundant storage (GRS) replicates your data to a secondary region, which increases storage costs because you are paying for both the primary and secondary copies. Additionally, when using GRS with Azure Synapse SQL, read requests may experience higher latency if they are directed to the secondary region, especially during a failover scenario or when using read-access geo-redundant storage (RA-GRS). This makes cost and performance valid concerns when choosing GRS over locally redundant storage (LRS).

Answer

The collation setting is not compatible with Azure Synapse

Answer

The database cannot be part of a failover group

Answer

The database will not support Transparent Data Encryption

Question 15

Refer to the exhibit. A data engineer wants to ensure that all Azure Storage accounts used for analytics use customer-managed keys. They apply this Azure Policy. What is the outcome?

Accepted Answer

The policy denies creation of StorageV2 accounts that use Microsoft-managed keys. The Azure Policy in the exhibit uses the 'Deny' effect on storage accounts that do not use customer-managed keys (i.e., that use Microsoft-managed keys). This means any attempt to create or update a storage account without customer-managed encryption will be blocked. Option D correctly identifies that the policy denies creation of StorageV2 accounts (or any storage account type covered by the policy scope) that use Microsoft-managed keys.

Answer

The policy audits existing storage accounts for compliance

Answer

The policy allows all storage accounts whether they use customer-managed keys or not

Answer

The policy only applies to storage accounts with hierarchical namespace enabled