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

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

Which TWO Azure services can be used to store semi-structured data like JSON or Parquet files for analytics? (Choose two.)

Accepted Answer

Azure Blob Storage. Azure Blob Storage (B) is correct because it provides massively scalable object storage that can hold semi-structured files such as JSON and Parquet in containers, and it is commonly used as a landing/staging area for analytics workloads. Azure Data Lake Storage Gen2 (D) is correct because it builds on Blob Storage with a hierarchical namespace, POSIX-like ACLs, and optimized performance for big-data analytics, making it the standard store for JSON, Parquet, and other semi-structured files consumed by engines like Synapse Spark and Databricks. Azure Synapse Analytics (A) is an analytics service with SQL and Spark pools rather than a primary storage service for JSON/Parquet files, Azure SQL Database (C) is a relational database management system designed for structured tabular data, and Azure Cosmos DB (E) is a globally distributed NoSQL database for operational document/key-value workloads, not a file-based analytics data store.

Answer

Azure Synapse Analytics

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 10

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 11

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 12

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 13

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

Question 14

A data analyst needs to create a real-time dashboard in Power BI that displays sales data from an Azure SQL Database. The dashboard must update every 10 minutes without manual refresh. Which Power BI feature should they use?

Accepted Answer

DirectQuery mode. DirectQuery mode is correct because it allows Power BI to query the Azure SQL Database directly for each visual interaction, ensuring the dashboard reflects the latest data without requiring a manual refresh. Since the requirement is for updates every 10 minutes, DirectQuery can be configured to auto-refresh at that interval via the 'Automatic page refresh' setting, which sends T-SQL queries to the database on a timer. This avoids the latency and storage overhead of importing data, making it ideal for near-real-time monitoring.

Answer

Scheduled refresh with Import mode

Answer

Streaming datasets

Answer

Paginated reports

Question 15

Refer to the exhibit. A team is deploying an Azure Storage container using an ARM template. The template sets publicAccess to 'None'. However, after deployment, users report they cannot access data even with a valid SAS token. What is the most likely cause?

Accepted Answer

The SAS token was generated with insufficient permissions. The ARM template sets publicAccess to 'None', which only disables anonymous public access to the container. It does not affect SAS token access. The most likely cause is that the SAS token was generated with insufficient permissions (e.g., missing read, list, or write permissions) or with a restricted scope (e.g., limited to a specific blob or with a short expiry), preventing users from accessing the data even though the container is private.

Answer

The container disables SAS tokens by default

Answer

The storage account firewall is blocking all traffic

Answer

The storage account requires RBAC permissions, not SAS