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

Question 1

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. Option A is correct because 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 2

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. Option C is correct because 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 3

A company runs a sales analytics workload on Azure Synapse Analytics. They notice that queries against the fact table are slow. The fact table is hash-distributed on the SalesDate column. Which design change would most likely improve query performance?

Accepted Answer

Replicate the fact table to all nodes. Option D is correct because hash-distributing on a high-cardinality date column can cause data skew and slow queries. Replicating the fact table avoids shuffling and improves performance for smaller tables. Option A is wrong because round-robin distribution is for staging tables, not fact tables. Option B is wrong because dropping indexes makes queries slower. Option C is wrong because the distribution key should be a frequently joined column, not necessarily the primary key.

Answer

Change distribution key to the primary key column

Answer

Change distribution to round-robin

Answer

Drop all indexes on the fact table

Question 4

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 5

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 6

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 7

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.

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 8

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

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides an Apache Spark-based analytics platform that can process large volumes of data in batch mode using distributed computing. It allows you to run ETL jobs, transformations, and machine learning pipelines on data stored in Azure Data Lake Storage or other sources, making it ideal for batch processing.

Answer

Azure Data Lake Storage

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Question 9

Which THREE components are required to implement a modern data warehouse architecture (medallion architecture) on Azure?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is required because it provides the scalable, hierarchical storage foundation for the medallion architecture's bronze, silver, and gold layers. It combines blob storage with a hierarchical namespace, enabling efficient data ingestion and transformation at petabyte scale.

Answer

Azure Analysis Services

Answer

Azure DevOps

Question 10

Which TWO options are valid ways to secure data at rest in Azure Synapse Analytics?

Accepted Answer

Customer-managed keys (CMK) with TDE. Customer-managed keys (CMK) with TDE is a valid method to secure data at rest in Azure Synapse Analytics because it allows you to bring your own key (BYOK) to encrypt the database encryption key (DEK), which is stored in Azure Key Vault. This provides an additional layer of control and separation of duties, ensuring that only authorized users can access the encryption keys and thus the underlying data.

Answer

Always Encrypted

Answer

Column-level security

Answer

Dynamic Data Masking

Question 11

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 12

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 13

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. Option C is correct because 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

Question 14

You are a data architect for a healthcare organization. The organization needs to build a real-time analytics solution to monitor patient vital signs from IoT devices. The data arrives at a rate of 10,000 events per second. Each event contains patient ID, timestamp, heart rate, blood pressure, and oxygen saturation. The solution must alert clinicians within 10 seconds when a patient's vital signs exceed predefined thresholds. Additionally, the solution must store the raw data for historical analysis and compliance. You plan to use Azure Event Hubs for ingestion. Which combination of services should you use to meet the requirements? Consider: processing low latency alerts, storing raw data in cost-effective storage, and enabling historical analytics. You also need to ensure that the solution can scale to handle future growth.

Accepted Answer

Use Azure Stream Analytics for real-time processing and alerting, output data to Azure Data Lake Storage, and use Azure Synapse Serverless SQL for historical analytics. Option D is correct because Azure Stream Analytics provides low-latency (sub-second) stream processing and can trigger alerts within the 10-second requirement. Outputting raw data to Azure Data Lake Storage (ADLS) offers cost-effective storage for compliance, and Azure Synapse Serverless SQL enables on-demand historical analytics without provisioning dedicated compute, scaling automatically for future growth.

Answer

Use Azure Databricks with Structured Streaming, store data in Delta Lake, and use Power BI for real-time dashboards

Answer

Use Azure Data Factory to batch ingest events every minute, store in Azure Blob Storage, and use Azure Analysis Services for historical analytics

Answer

Use Azure Functions to process events, store data in Azure Cosmos DB, and use Power BI for historical analytics

Question 15

You are a business analyst at a manufacturing company. The company uses Azure SQL Database to store production data. You need to create a Power BI report that shows real-time machine efficiency. The report must refresh every 5 minutes to show current metrics. You have been granted read-only access to the database. The database is under heavy load from transactional applications, and you want to minimize additional impact. Which approach should you take to create the report?

Accepted Answer

Use Power BI Import mode with incremental refresh policy to load only new data every 5 minutes. B is correct because Power BI Import mode with an incremental refresh policy allows you to load only new or changed data every 5 minutes, minimizing the load on the heavily used Azure SQL Database. This approach avoids repeated full table scans, reduces query impact, and still provides near-real-time metrics for the report.

Answer

Disable the Power BI Query Cache to reduce database load

Answer

Configure Power BI to use DirectQuery mode to ensure real-time data

Answer

Export the data to a CSV file and import it into Power BI daily