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

Question 1

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?

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

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 2

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

Question 3

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?

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 4

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 5

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 6

Which TWO Azure services can be used to perform large-scale data transformation and processing in a serverless manner?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (Option B) is correct because it allows you to run T-SQL queries over data stored in Azure Data Lake or Blob Storage without provisioning any dedicated compute resources, paying only for the data processed. Azure Data Factory (Option C) is correct because it provides a serverless orchestration and data integration service that can execute data transformation activities (like Mapping Data Flows) at scale without managing underlying infrastructure.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

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

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 14

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

Question 15

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 to store customer transaction data. They also use Azure Databricks for data transformation and Azure Synapse Serverless SQL pool for ad-hoc queries. Recently, the data lake has grown to 10 TB, and query performance in Synapse Serverless has degraded significantly. Users complain that queries that used to take seconds now take minutes. You need to improve query performance without moving data to a dedicated SQL pool. The data is stored in Parquet format, partitioned by date. You notice that the queries often filter on CustomerID and Date. Current queries scan all partitions even when only a few days are needed. What is the most effective solution to improve performance?

Accepted Answer

Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each. Repartitioning the Parquet files by both date and CustomerID enables partition pruning in Azure Synapse Serverless SQL pool. When queries filter on CustomerID and Date, the engine can skip irrelevant partitions entirely, drastically reducing the amount of data scanned. Optimizing file sizes to around 1 GB ensures efficient parallelism and avoids the overhead of many small files, which degrades performance in a serverless environment.

Answer

Create materialized views in the Serverless SQL database on the partitioned data

Answer

Convert all Parquet files to CSV and use row-level security to limit data access

Answer

Increase the service level of the Synapse workspace to improve query concurrency