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

Question 1

Refer to the exhibit. An administrator runs an Azure CLI command to show the status of a Synapse SQL pool. The output shown is returned. What does this output indicate about the SQL pool?

Accepted Answer

The SQL pool is online and available for queries.. The explanation refers to `"status": "Online"` as being present in the output. If this is indeed what the exhibit shows, then the explanation correctly justifies option B. However, the exhibit itself must be provided for a complete verification.

Answer

The SQL pool was restored from a backup.

Answer

The SQL pool is paused.

Answer

The SQL pool is currently being restored.

Question 2

Which TWO Azure services can be used to perform interactive ad-hoc analytics on large datasets using Apache Spark?

Accepted Answer

Azure Databricks. Azure Databricks (C) is correct because it is a first-party Apache Spark-based analytics platform that provides interactive notebooks and clusters for ad-hoc exploration of large datasets. Azure Synapse Analytics (D) is also correct because its built-in Apache Spark pools allow interactive, ad-hoc analytics over large data in the workspace. Azure Analysis Services (A) is an OLAP/tabular semantic modeling engine, not a Spark execution service. Azure HDInsight (B) does support Spark clusters, but in this question it is not marked as one of the two correct answers. Azure Data Lake Storage (E) is only a storage layer and cannot itself run Spark analytics.

Answer

Azure Analysis Services

Answer

Azure HDInsight

Answer

Azure Data Lake Storage

Question 3

Your company needs to build an analytics solution that can handle both batch and streaming data from IoT devices. The solution must allow complex event processing and real-time dashboards. Which Azure service should you use as the primary data ingestion and processing layer?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, enabling complex event processing (CEP) through SQL-like queries and direct output to real-time dashboards like Power BI. While it does not handle batch processing natively, the requirement for real-time dashboards and CEP makes Stream Analytics the primary ingestion and processing layer, with batch processing typically handled by complementary services like Azure Data Lake or Azure Synapse.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Analytics

Question 4

An organization runs a mission-critical analytics workload on Azure Synapse Analytics. They need to ensure high availability and automatic failover in case of a regional outage. Which configuration should they implement?

Accepted Answer

Use active geo-replication for the dedicated SQL pool. Active geo-replication for a dedicated SQL pool in Azure Synapse Analytics creates a readable secondary database in a paired Azure region. This configuration enables regional disaster recovery and high availability by allowing the secondary database to be promoted to primary in the event of a regional outage. This ensures business continuity for mission-critical analytics workloads by maintaining a synchronized replica that can be promoted to primary with minimal downtime, typically through user-initiated or scripted actions.

Answer

Enable read-scale replicas

Answer

Configure automatic backups with point-in-time restore

Answer

Enable always-on availability groups

Question 5

A data engineering team uses Azure Data Factory to orchestrate an ETL pipeline that loads data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline fails intermittently with timeout errors during the copy activity. The network is stable. What should they do first to resolve the issue?

Accepted Answer

Increase the copy activity timeout setting. The copy activity in Azure Data Factory has a default timeout of 120 minutes (2 hours). Intermittent timeout errors during a stable network connection indicate that the copy operation is sometimes taking longer than the configured timeout. Increasing the timeout setting directly addresses the immediate symptom by allowing the activity to complete without prematurely failing, assuming the data volume or complexity on those intermittent occasions requires more time than currently allotted.

Answer

Use a staging copy via Azure Blob Storage

Answer

Configure a self-hosted integration runtime

Answer

Downgrade the Azure Synapse dedicated SQL pool to Basic tier

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

Which THREE components are essential for building a real-time analytics solution on Azure?

Accepted Answer

Power BI. Azure Event Hubs (D) is essential because it provides the highly scalable, low-latency event ingestion service that captures millions of events per second from sources such as IoT devices, applications, and logs, forming the streaming input for a real-time pipeline. Azure Stream Analytics (E) is essential because it is the serverless stream-processing engine that runs continuous SQL-like queries over that streaming data to perform real-time transformations, aggregations, windowing, and anomaly detection. Power BI (C) is essential because it delivers the real-time visualization and dashboarding layer, using its streaming datasets and push datasets to display live analytics results with sub-second refresh. Azure Data Lake Storage (A) is not required for real-time analytics since it is a batch-oriented storage layer for big data, and Azure Analysis Services (B) is not required because it is a tabular semantic modeling service aimed at batch/enterprise BI rather than low-latency streaming scenarios.

Answer

Azure Data Lake Storage

Answer

Azure Analysis Services

Question 8

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

Change distribution key to the primary key column. Changing the distribution key to a high-cardinality, evenly distributed column, such as a suitable primary key, is a common and effective strategy to improve query performance on large fact tables. This helps to distribute data more evenly across compute nodes, reducing data skew (which is likely with 'SalesDate') and enabling more efficient collocated joins. Option B (replicate) is incorrect because fact tables are typically very large and not suitable for replication; replicating a large table would consume excessive resources and is generally reserved for small dimension tables. Option C (round-robin) distributes data evenly but degrades join performance due to constant data movement. Option D (dropping indexes) would severely degrade query performance, especially for analytical workloads that benefit from Clustered Columnstore Indexes.

Answer

Replicate the fact table to all nodes

Answer

Change distribution to round-robin

Answer

Drop all indexes on the fact table

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 (A) is required because the medallion architecture stores its bronze, silver, and gold layers as files in a hierarchical namespace-enabled data lake, and ADLS Gen2 provides the scalable, POSIX-like storage foundation for that layered data. Azure Synapse Analytics (C) is required because it supplies the analytical compute and SQL/Spark engines needed to transform and serve the curated gold-layer data for reporting and analytics. Azure Databricks (D) is required because its Apache Spark-based notebooks and Delta Lake support the heavy data engineering and machine-learning workloads that process bronze-to-silver and silver-to-gold transformations. Azure Analysis Services (B) is not required because it is a legacy semantic-model/tabular engine that has largely been superseded by Power BI and Synapse SQL for serving curated data. Azure DevOps (E) is not required because it is a CI/CD and project-management service, not a data storage, processing, or serving component of the medallion architecture.

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. Option A is correct because Transparent Data Encryption (TDE) in Azure Synapse Analytics supports the Bring Your Own Key (BYOK) model, allowing customer-managed keys (CMK) stored in Azure Key Vault to encrypt the database encryption key, giving you control over the key lifecycle for data at rest. Option B is correct because TDE is the foundational encryption mechanism that encrypts SQL pools' data and log files at rest using AES-256 and 3DES, protecting data on disk and in backups without application changes. Option C (Always Encrypted) is incorrect here because it protects data in use and in transit by encrypting at the client, not a data-at-rest feature of the Synapse SQL engine. Option D (Column-level security) is incorrect because it is an access-control mechanism (GRANT/DENY on columns) that restricts who can read data, not an encryption-at-rest control. Option E (Dynamic Data Masking) is incorrect because it obfuscates data in query results for non-privileged users at query time, which is a presentation-layer masking feature rather than encryption of stored data.

Answer

Always Encrypted

Answer

Column-level security

Answer

Dynamic Data Masking

Question 11

You are a data architect for a logistics company. The company uses Azure Data Lake Storage Gen2 to store shipment tracking data. The data is ingested from IoT devices on trucks. Each record contains truck ID, timestamp, GPS coordinates, speed, and fuel level. The volume is 5 TB per day. The company wants to build a near-real-time dashboard to monitor truck locations and speeds. They also need to run daily batch analytics to compute fuel efficiency trends. You need to design a solution that minimizes latency for the dashboard and maximizes cost efficiency for batch processing. You plan to use Azure Event Hubs for ingestion. Which approach should you take?

Accepted Answer

Use Azure Stream Analytics to process the stream, output to Power BI for real-time dashboard, and simultaneously output raw data to Azure Data Lake Storage. Use Azure Databricks to process the data lake for batch analytics.. It separates the real-time and batch processing paths to minimize latency and maximize cost efficiency. Azure Stream Analytics outputs directly to Power BI for near-real-time dashboard updates, while simultaneously writing raw data to Azure Data Lake Storage for cost-effective storage. Azure Databricks then processes the data lake for daily batch analytics, avoiding expensive real-time compute for historical analysis.

Answer

Use Azure Stream Analytics to process the stream and output directly to Azure SQL Database. Use Power BI to query SQL Database for both real-time dashboard and historical analytics.

Answer

Use Azure Event Hubs Capture to store data in Azure Blob Storage, then use Azure Data Factory to transform and load into Azure Synapse Analytics for both dashboard and batch.

Answer

Use Azure Databricks with Structured Streaming to process the stream, write to Delta Lake, and use Delta Lake to serve both real-time and batch queries.

Question 12

Your company operates a retail analytics platform. Data from point-of-sale systems is ingested in real time into Azure Event Hubs. The data is then consumed by an Azure Stream Analytics job that aggregates sales by store and product every minute, writing results to Azure SQL Database. The business now requires a historical trend analysis capability that can query the last three years of sales data with sub-second response times, but the SQL Database is already experiencing performance issues due to high write volume. You need to redesign the serving layer to support both real-time dashboards (seconds latency) and historical analytics (sub-second queries on years of data) without impacting write performance. What should you do?

Accepted Answer

Add Azure Cosmos DB as an additional output from Azure Stream Analytics for real-time dashboards. Enable Azure Synapse Link on the Cosmos DB container to continuously replicate data to Azure Synapse Analytics for historical queries. Remove the real-time dashboard load from Azure SQL Database.. Option B is correct because it cleanly separates the two workloads: Azure Cosmos DB provides low-latency, high-throughput writes for real-time dashboards, while Azure Synapse Link enables near-real-time analytical replication into Azure Synapse Analytics, which is optimized for sub-second queries over years of data. This removes the dashboard read/write pressure from Azure SQL Database, directly addressing the write-performance bottleneck. Option A could work but keeps the real-time dashboard on the struggling Azure SQL Database and adds batch latency, so it does not fully resolve the write issue. Option C is wrong because Azure Stream Analytics cannot write directly to Azure Analysis Services, and a cube on top of the overloaded SQL Database does not fix write performance. Option D only scales and indexes the existing SQL Database, which still mixes real-time and historical workloads and does not provide a true serving-layer redesign.

Answer

Modify the Azure Stream Analytics job to write to Azure Data Lake Storage Gen2, then use Azure Databricks to run batch transformations and load into a separate Azure Synapse Analytics dedicated SQL pool for historical queries. Keep the real-time dashboard connected to Azure SQL Database.

Answer

Add Azure Analysis Services on top of Azure SQL Database and create a cube for historical queries. Use Azure Stream Analytics to write to Analysis Services directly.

Answer

Partition the existing Azure SQL Database and add columnstore indexes to improve query performance. Keep the current architecture and increase the DTU/vCore limit to handle both workloads.

Question 13

A company is designing an analytics solution on Azure. They need to ingest data from various sources, store it in a scalable data lake, and then process it using a distributed computing engine that supports Python and Scala. They also want to create interactive visualizations for business users. Which TWO Azure services should they include in their solution? (Choose two.)

Accepted Answer

Azure Data Factory. Azure Data Factory is needed for ingesting data from various sources into a data lake, and Azure Databricks provides the distributed computing engine that supports Python and Scala for processing that data. Together, they form a core part of the analytics solution. Power BI would be added for visualization but is not one of the two services required for ingestion and processing.

Answer

Azure Event Hubs

Answer

Azure SQL Database

Answer

Power BI

Question 14

A manufacturer collects sensor data from thousands of IoT devices every second. The data is ingested into Azure Event Hubs and then needs to be stored for historical analysis. The analytics team will run complex aggregations and time-series queries over petabytes of data, expecting fast results even with large scans. Which Azure service should be used as the analytical data store?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is the correct choice because it is a massively parallel processing (MPP) engine designed for petabyte-scale data warehousing. It can run complex aggregations and time-series queries with fast results by distributing data across 60 distributions and using columnstore indexes for high compression and scan efficiency.

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 15

A manufacturing company has a streaming data pipeline that ingests sensor data from factory equipment into Azure Event Hubs. The data must be prepared for reporting by cleaning invalid records, removing duplicates, and aggregating readings into 5-minute windows. The transformed data needs to be stored in a columnar format in a data lake to support efficient querying by data analysts using SQL. Which Azure service should perform the data transformation and loading?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, directly consuming data from Azure Event Hubs, performing transformations like cleaning invalid records, removing duplicates, and aggregating over tumbling windows (e.g., 5-minute windows), and outputting the results in a columnar format (e.g., Parquet) to Azure Data Lake Storage. This aligns perfectly with the requirement for a low-latency, continuous transformation pipeline without needing additional orchestration or compute clusters.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines