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

Question 1

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?

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

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

Question 2

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. 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 3

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

Question 4

You are a data engineer for a financial services company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. They have a fact table named Transactions that contains 2 billion rows. The table is hash-distributed on the AccountID column. Users run reports that aggregate transaction amounts by date and account type. The reports are slow. Upon investigation, you find that the distribution is highly skewed because a few accounts have millions of transactions. You need to improve query performance without redesigning the entire schema. Which action should you take?

Accepted Answer

Change the distribution key to a column with more unique values, such as TransactionID. Changing the distribution key to TransactionID, which has far more unique values than AccountID, will eliminate the data skew that is causing performance degradation. In a hash-distributed table, a skewed distribution key leads to some distributions holding a disproportionate amount of data, causing parallel query execution to be bottlenecked by the largest distribution. By using a highly unique column like TransactionID, the data will be evenly distributed across all 60 distributions, enabling balanced parallelism and faster aggregation queries.

Answer

Change the distribution to round-robin

Answer

Create a clustered columnstore index on the table

Answer

Replicate the Transactions table to all distributions

Question 5

Your company is designing a big data analytics solution on Azure. The solution must ingest streaming data from IoT devices, store the data in its raw format, and then use a distributed processing engine to transform the data before loading it into a serving layer for reporting. Which TWO Azure services should you include in the design?

Accepted Answer

Azure Event Hubs. Azure Event Hubs (B) is correct because it is Azure's managed, scalable event-ingestion service designed for high-throughput streaming telemetry from IoT devices, supporting millions of events per second and AMQP/Kafka protocols for real-time ingestion. Azure Databricks (C) is correct because it provides an Apache Spark-based distributed processing engine that can read the raw streamed data and run transformations at scale before writing to a serving layer. Together they satisfy the ingest-streaming-data and distributed-transformation requirements. Azure Blob Storage (A) is a storage service, not a streaming ingestion or distributed processing engine, so it does not fulfill the stated roles. Azure Data Factory (D) is an orchestration/ETL pipeline service rather than a streaming ingest or Spark processing engine. Azure Synapse Analytics (E) is primarily a serving/analytics warehouse layer, not the required streaming ingestion or distributed transformation engine.

Answer

Azure Blob Storage

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Question 6

A data engineering team is building a data pipeline to run daily batch loads from an on-premises SQL Server to Azure Synapse Analytics. The pipeline must include data transformation using a visual interface with no coding, and must support schema mapping and data validation. Which THREE Azure services should be used together?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct destination for the pipeline because it is a cloud-based data warehouse that supports high-performance analytics on large-scale data, making it ideal for daily batch loads from SQL Server. It integrates natively with Azure Data Factory for orchestration and Azure Blob Storage for staging, enabling schema mapping and data validation through visual interfaces without coding.

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 7

Your organization has a data lake on Azure Data Lake Storage Gen2 containing petabytes of raw clickstream data. Data scientists need to run exploratory analysis using Python and Spark, but they are not experienced with cluster management or infrastructure. The IT team wants to minimize administrative overhead while providing a collaborative notebook environment. Additionally, the solution must integrate with Microsoft Purview for data cataloging and lineage. Which Azure service should you recommend?

Accepted Answer

Azure Databricks. Azure Databricks is the correct choice because it provides a fully managed, collaborative notebook environment optimized for Apache Spark, allowing data scientists to run Python and Spark-based exploratory analysis without managing clusters. It integrates natively with Azure Data Lake Storage Gen2 for accessing petabytes of clickstream data and supports Microsoft Purview for automated data cataloging and lineage tracking, minimizing administrative overhead.

Answer

Azure Data Science Virtual Machine

Answer

Azure Synapse Analytics (Synapse Studio)

Answer

Azure HDInsight (Spark cluster)

Question 8

A financial services company is building a real-time fraud detection system. Transactions are streamed from multiple sources into Azure Event Hubs. The system must run a trained machine learning model (scored in near real-time) to flag suspicious transactions. The model is a Python pickle file that needs to be deployed as a web service with low latency (under 100 ms per prediction). The data engineering team wants to use a serverless compute option to run the scoring logic, and the solution must integrate with Azure Stream Analytics for alerting. Which Azure service should you use to deploy the model?

Accepted Answer

Azure Machine Learning managed online endpoint. Azure Machine Learning managed online endpoints are the correct choice because they are designed for deploying trained models (including Python pickle files) as low-latency web services (under 100 ms per prediction) with serverless compute. They natively integrate with Azure Stream Analytics for alerting, allowing real-time scoring of streaming transactions from Event Hubs without managing infrastructure.

Answer

Azure Functions

Answer

Azure Kubernetes Service (AKS)

Answer

Azure Databricks

Question 9

A retail company ingests point-of-sale data into Azure Data Lake Storage Gen2, where it lands as CSV files. The analytics team wants to create a curated, query-optimized layer that can be consumed by Power BI and Azure Synapse Analytics. They plan to use Azure Databricks to transform the raw data. Which two capabilities does Azure Databricks provide for this analytics workload? (Choose two.)

Accepted Answer

It provides an Apache Spark-based engine that can process large volumes of data in parallel.. Azure Databricks is a first-party Azure service built on Apache Spark, offering a collaborative notebook-based workspace and a distributed processing engine. For the retail company, these two capabilities directly support transforming raw CSV data into a curated, query-optimized layer. The other options describe features of different services or incorrect limitations, so they do not apply here.

Answer

It stores data in a proprietary format that cannot be read by other Azure services.

Answer

It provides a fully managed extract, transform, and load (ETL) service with a drag-and-drop visual interface.

Answer

It automatically creates a dedicated SQL pool for each notebook session.

Question 10

A financial services firm needs to build a modern data warehouse on Azure to support complex analytical queries over petabytes of structured data. The solution must provide massively parallel processing (MPP) and support both provisioned and on-demand query execution. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the only listed service that provides an MPP engine through dedicated SQL pools and on-demand querying through serverless SQL pools. These capabilities align with the financial firm's requirement for complex analytical queries at petabyte scale with both provisioned and on-demand execution. The other services are transactional databases not designed for this type of analytics workload.

Answer

Azure Database for PostgreSQL

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 11

A marketing team wants to analyze streaming data from social media feeds in near real-time and store the results in a data lake for later batch analysis. They need a service that can ingest millions of events per second and support multiple downstream consumers. Which Azure service should they use for ingestion?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is designed for high-throughput, low-latency event ingestion and supports multiple consumer groups, enabling both real-time analytics and storage to a data lake. The marketing team's need for millions of events per second and multiple downstream consumers aligns exactly with Event Hubs capabilities. The other services are either message brokers, batch integration tools, or storage, and do not provide the required streaming ingestion.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Service Bus

Question 12

A financial services company stores structured transaction records in Azure Data Lake Storage Gen2 as Parquet files. Analysts want to query this data using familiar T-SQL syntax without moving the data into a dedicated SQL pool. They also need to pay only for the queries they run. Which Azure Synapse Analytics feature should they use?

Accepted Answer

Serverless SQL pool. Serverless SQL pool in Azure Synapse Analytics enables querying of files in Azure Data Lake Storage Gen2 using T-SQL without provisioning dedicated resources. It charges only for the data processed by each query, aligning with the need for ad-hoc analysis and cost efficiency. The other options either require provisioning, use different languages, or are not query engines.

Answer

Dedicated SQL pool

Answer

Azure Data Factory

Answer

Apache Spark pool

Question 13

A logistics company ingests millions of telemetry events per minute from vehicles into Azure. They need a service that can capture and store these events for later processing by multiple downstream analytics applications. The solution must support massive scale and retain data for up to seven days by default. Which Azure service should they use?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is built for high-throughput event ingestion and provides a durable buffer that multiple downstream applications can read from independently. Its ability to scale to millions of events per second and retain data for a configurable period makes it the right choice for capturing vehicle telemetry for later analysis.

Answer

Azure Data Factory

Answer

Azure Service Bus

Answer

Azure Blob Storage

Question 14

A healthcare organization needs to build a modern data warehouse on Azure to combine structured patient data from an on-premises SQL Server with semi-structured JSON logs from Azure Blob Storage. They require a solution that supports both T-SQL and Spark workloads and integrates with Power BI. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics provides a unified experience for data warehousing and big data analytics, supporting both T-SQL and Spark natively. It can ingest structured and semi-structured data, and its integration with Power BI enables seamless reporting. This makes it the most suitable service for building a modern data warehouse on Azure.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure SQL Database

Question 15

A media company uses Azure Stream Analytics to process live video streaming telemetry from Azure Event Hubs. They need to write the processed data to a destination that supports fast, random read/write access for a custom application that performs lookups. The solution must minimize latency. Which output should they configure?

Accepted Answer

Azure SQL Database. Azure SQL Database is designed for transactional workloads with fast, random read/write access and low latency. As an output for Azure Stream Analytics, it enables real-time updates that a custom application can query with minimal delay. Other outputs like Blob Storage or Data Lake are optimized for batch and sequential access, not low-latency lookups.

Answer

Power BI

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage