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

Question 1

Refer to the exhibit. You deploy this Azure Stream Analytics job. The job runs but no data is written to the Azure SQL Database table. What is the most likely cause?

Accepted Answer

The TumblingWindow has not yet elapsed. The TumblingWindow function in Azure Stream Analytics processes data in fixed, non-overlapping time intervals. Since the job runs but no data is written, the most likely cause is that the first window has not yet elapsed, meaning no aggregation has been triggered to output results. Stream Analytics only emits results when a tumbling window completes, so data will not appear in the SQL Database until the window duration passes.

Answer

The query syntax is invalid

Answer

The Event Hub name is incorrect

Answer

The SKU does not support SQL output

Question 2

Refer to the exhibit. A database administrator runs this KQL query in Azure Monitor Log Analytics. The query returns no results. What is the most likely reason?

Accepted Answer

The ResourceType filter is incorrect. The KQL query filters on `ResourceType` with a value that does not match any actual Azure resource type (e.g., a typo or incorrect casing). Since Azure Monitor Log Analytics stores resource types in a specific format (e.g., 'microsoft.compute/virtualmachines'), an incorrect filter will return zero results even if data exists. The query syntax, render command, and time range are all valid, so the filter is the most likely cause.

Answer

The summarize operator syntax is wrong

Answer

The render command is not supported

Answer

The time range is incorrect

Question 3

Your company runs a sales analytics dashboard on Power BI that refreshes every hour from Azure Synapse Analytics. During peak hours, the dashboard refresh fails with a 'timeout' error. Which action should you take FIRST to resolve the issue?

Accepted Answer

Implement incremental refresh in Power BI to refresh only changed data.. Implementing incremental refresh in Power BI reduces the amount of data loaded during each refresh cycle, which directly addresses timeout errors by limiting the refresh to only changed or new data rather than the entire dataset. This is the most efficient first step to reduce refresh duration without changing the underlying architecture or data source connection mode.

Answer

Scale up the Azure Synapse dedicated SQL pool to handle more concurrent queries.

Answer

Configure the dashboard to use DirectQuery instead of Import mode.

Answer

Export the data to CSV files and load into Power BI from Azure Blob Storage.

Question 4

Your company has a Power BI dashboard that uses a data model with a single large fact table and several dimension tables. The dashboard loads slowly when users filter by multiple dimensions. Which design change would MOST improve performance?

Accepted Answer

Ensure the fact table and dimension tables follow a star schema design with proper relationships.. A star schema design with proper relationships between the fact table and dimension tables is the foundational best practice for optimizing Power BI data models. This design minimizes the cardinality of relationships, reduces the size of the data model, and enables efficient query folding and storage engine compression, which directly improves filter performance across multiple dimensions.

Answer

Use page-level filters instead of report-level filters.

Answer

Create a calculated table that aggregates the fact table at a higher granularity.

Answer

Convert the data model to a composite model using DirectQuery for some tables.

Question 5

Your data engineering team is designing a data pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The data must be cataloged in Azure Purview for discoverability. Which approach ensures that the data lineage is automatically captured?

Accepted Answer

Use Azure Data Factory with Purview integration enabled to copy data.. Azure Data Factory's native Purview integration automatically captures lineage metadata during data copy activities. When enabled, Data Factory pushes runtime lineage information (source, sink, transformation steps) directly to Purview without manual intervention, ensuring complete and accurate data provenance.

Answer

Use Azure Data Factory to copy data and manually register the datasets in Purview.

Answer

Use Azure Databricks to write data and call Purview's Atlas API to update lineage.

Answer

Schedule Purview scans on the data lake after data ingestion.

Question 6

Your company uses Azure Synapse Analytics to run analytical queries on large datasets. You need to ensure that queries against a frequently accessed fact table perform well without impacting other workloads. Which feature should you use?

Accepted Answer

Enable result set caching for the database.. Result set caching stores query results in the Synapse SQL pool's cache, so repeated queries against the fact table return cached results instantly without re-scanning data. This ensures fast performance for frequently accessed queries while isolating resource usage from other workloads, as cached results do not consume concurrency slots or I/O resources.

Answer

Create materialized views on the fact table.

Answer

Partition the fact table by a frequently filtered column.

Answer

Use workload classification to prioritize the queries.

Question 7

Your company has a Power BI report that uses DirectQuery to Azure SQL Database. Users report that the report is slow when multiple users access it simultaneously. The database is underprovisioned. Which action should you take to improve performance without changing the report design?

Accepted Answer

Scale up the Azure SQL Database to a higher service tier.. The root cause is an underprovisioned Azure SQL Database, which cannot handle the concurrent query load from multiple Power BI users using DirectQuery. Scaling up to a higher service tier (e.g., from S2 to S3 or a DTU-based tier) increases the database's DTUs (Database Transaction Units), directly improving throughput and reducing query latency without altering the report design.

Answer

Enable query caching in Power BI Premium.

Answer

Change the report to use Import mode instead of DirectQuery.

Answer

Add indexes to the tables used in the report.

Question 8

Your company is migrating an on-premises SQL Server data warehouse to Azure. The solution must support both historical analytics and real-time reporting. Which Azure service should you recommend as the primary data store?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it is a cloud-native analytics service that unifies big data and data warehousing, supporting both historical analytics (via dedicated SQL pools for large-scale relational data warehousing) and real-time reporting (via serverless SQL pools or Apache Spark pools for streaming and interactive queries). It is designed to handle the migration of an on-premises SQL Server data warehouse while providing integrated capabilities for batch and real-time workloads.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Question 9

Your company uses Azure Databricks to process streaming data from Event Hubs. The data is transformed and written to Azure Data Lake Storage Gen2 as Delta tables. You notice that some records are duplicated in the Delta tables. Which configuration change should you make to prevent duplicates?

Accepted Answer

Enable checkpointing in the streaming query to store progress.. Checkpointing in Spark Structured Streaming stores the offset of the last processed event from Event Hubs. When the query restarts, it reads from the checkpointed offset, ensuring each event is processed exactly once and preventing duplicates in the Delta table.

Answer

Add a separate job to deduplicate the Delta table.

Answer

Use Delta Lake's idempotent write support.

Answer

Increase the batch interval in the streaming query.

Question 10

Which TWO Azure services can be used to perform data transformation in an analytics pipeline? (Choose two.)

Accepted Answer

Azure Data Factory. Azure Data Factory is a cloud-based ETL service that allows you to create data pipelines to transform data at scale using mapping data flows or by invoking external compute services like Azure Databricks. It supports code-free visual transformations as well as custom code via Azure HDInsight or Databricks, making it a core service for data transformation in analytics pipelines.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Event Hubs

Answer

Power BI

Question 11

Which THREE components are part of a typical modern data warehouse architecture on Azure? (Choose three.)

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is a core component of a modern data warehouse architecture on Azure because it provides a unified analytics platform that combines big data and data warehousing capabilities. It enables T-SQL-based querying of both relational and non-relational data, integrating with Azure Data Lake Storage Gen2 for scalable storage and Azure Data Factory for orchestration.

Answer

Power BI

Answer

Azure Stream Analytics

Question 12

Refer to the exhibit. You execute the above T-SQL statements in Azure Synapse Analytics. What is the purpose of this code?

Accepted Answer

To create an external table that can query Parquet files stored in Azure Data Lake Storage Gen2.. The T-SQL code creates an external data source pointing to Azure Data Lake Storage Gen2, an external file format for Parquet, and an external table that references the Parquet files. This allows querying the Parquet files directly without importing them into the database, which is the definition of an external table in Azure Synapse Analytics.

Answer

To create a view over the Parquet files.

Answer

To import data from Parquet files into a permanent table in Synapse.

Answer

To create a regular table in the Synapse database.

Question 13

Refer to the exhibit. You have an Azure Data Factory pipeline definition as shown. The pipeline fails with a 'Source not found' error. The BlobInputDataset points to a container that exists. What is the most likely cause?

Accepted Answer

The dataset's file path is incorrect or no files match the pattern.. The 'Source not found' error in Azure Data Factory indicates that the source dataset cannot locate the specified file or blob. Since the container exists, the most likely cause is that the file path defined in the dataset is incorrect or that no files match the specified pattern (e.g., wildcard or prefix). This is a common configuration issue when the dataset's folder path or file name does not correspond to the actual blob location.

Answer

The Azure Blob Storage container is empty.

Answer

The Azure Data Factory managed identity does not have access to the storage account.

Answer

The SQL sink database does not exist.

Question 14

Refer to the exhibit. You have a Power BI measure defined as shown. What does this measure return?

Accepted Answer

The total sales amount for online channel only.. The measure uses CALCULATE to modify the filter context, summing the Amount column only where Channel is 'Online'. Therefore, it returns the total sales amount for the online channel only. Option D is correct. Option A is incorrect because it counts rows, not sums amounts. Option B is incorrect because it sums Amount for each product, not the total. Option C is incorrect because it sums amounts for all channels, not just online.

Answer

The count of online sales transactions.

Answer

The sum of Amount for each product sold online.

Answer

The total sales amount for all channels.

Question 15

A company uses Azure Synapse Analytics to run large-scale data transformations. They need to optimize costs for predictable workloads that run every night. Which Azure feature should they configure?

Accepted Answer

Implement dedicated SQL pool pause and resume. Azure Synapse Analytics dedicated SQL pool supports pause and resume, which stops compute billing while preserving data in storage. For predictable nightly workloads, pausing the pool during idle hours eliminates compute costs, then resuming it before the job runs. This directly optimizes cost for scheduled, non-continuous workloads.

Answer

Enable always-on availability

Answer

Enable data compression on tables

Answer

Configure auto-scale