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

Question 1

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 and Azure Data Lake Storage Gen2 (ADLS Gen2) are both highly scalable, cost-effective object storage services that can store any type of unstructured and semi-structured data, including formats like JSON and Parquet. Blob Storage provides general-purpose object storage, while ADLS Gen2 is built on Blob Storage and specifically optimized for big data analytics workloads, offering a hierarchical namespace and HDFS compatibility. Both are commonly used as data lake foundations for analytics, where raw or processed data is stored and later queried by services like Azure Synapse Analytics or Azure Databricks.

Answer

Azure Synapse Analytics

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 2

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 3

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 4

A data analyst needs to create a Power BI report that combines sales data from Azure SQL Database and marketing data from a CSV file stored in Azure Blob Storage. The report should refresh automatically. What is the recommended approach?

Accepted Answer

Use Power Query in Power BI Desktop to combine the data and publish with scheduled refresh. The correct approach is Option A: Use Power Query in Power BI Desktop to combine the data sources (Azure SQL Database and CSV from Blob Storage), then publish the report to the Power BI service and configure scheduled refresh. This is the standard method for combining structured and semi-structured data with automated refresh. Option B (exporting SQL to Excel) is manual and lacks automation. Option C (Azure Data Factory) is overkill for a simple two-source combination. Option D (DirectQuery mode) cannot combine a CSV file directly.

Answer

Export the SQL data to Excel and combine with the CSV in Power BI

Answer

Use Azure Data Factory to merge the data into a single SQL table

Answer

Use DirectQuery mode from Power BI for both sources

Question 5

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

Accepted Answer

Power BI. Azure Event Hubs is essential for ingesting high-throughput, low-latency data streams from various sources. Azure Stream Analytics provides real-time processing and analysis of these streams, allowing for transformations, aggregations, and anomaly detection. Power BI serves as the visualization and reporting layer, connecting directly to Azure Stream Analytics or Event Hubs to render live dashboards and alerts, enabling users to monitor streaming data with sub-second latency.

Answer

Azure Data Lake Storage

Answer

Azure Analysis Services

Question 6

Which TWO are benefits of using Azure Synapse Analytics for a data warehouse workload?

Accepted Answer

Ability to query data in the data lake using serverless SQL. Azure Synapse Analytics provides a serverless SQL pool that allows you to query data directly from your data lake (e.g., Azure Data Lake Storage Gen2) without needing to load it into a dedicated SQL pool. This enables cost-effective, on-demand querying of large-scale data in open formats like Parquet or CSV, making it a key benefit for data warehouse workloads that integrate lake and warehouse patterns. Furthermore, Synapse Analytics offers a unified workspace that brings together data integration (pipelines), enterprise data warehousing (dedicated SQL pools), and big data analytics (Spark pools, serverless SQL pools, Data Explorer) into a single platform, simplifying development and management for diverse data workloads.

Answer

Native support for MongoDB data sources

Answer

Automatic indexing of all data

Answer

Built-in email alerts for query performance

Question 7

You are the data engineer for a large retail company. The company has an existing on-premises SQL Server database with 10 years of transactional data. They want to move this data to Azure to enable advanced analytics using Azure Synapse Analytics. The data includes customer orders, product details, and inventory. The solution must minimize data movement and support both batch and real-time analytics. The company also wants to use Power BI for reporting. They have a limited budget and prefer a serverless option for compute. You are evaluating the following approaches: A) Use Azure Data Factory to copy all data to Azure Data Lake Storage Gen2, then use Azure Synapse Serverless SQL pool to query the data, and finally connect Power BI to the serverless SQL endpoint. B) Use Azure Database Migration Service to migrate the SQL Server database to Azure SQL Database, then use Azure Synapse Analytics with a dedicated SQL pool to perform analytics, and connect Power BI to the dedicated pool. C) Use Azure Data Factory to copy all data to Azure Blob Storage, then use Azure Stream Analytics to perform real-time analytics, and connect Power BI directly to Stream Analytics output. D) Use Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, use Azure Synapse Serverless SQL pool for batch analytics, and use Azure Event Hubs and Stream Analytics for real-time data, with Power BI connecting to both serverless SQL and Stream Analytics. Which approach best meets the requirements?

Accepted Answer

Use Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, use Azure Synapse Serverless SQL pool for batch analytics, and use Azure Event Hubs and Stream Analytics for real-time data, with Power BI connecting to both serverless SQL and Stream Analytics.. Best meets the requirements because it uses Azure Data Factory to copy historical data to Azure Data Lake Storage Gen2, enabling cost-effective storage and batch analytics via Azure Synapse Serverless SQL pool (serverless compute). It also incorporates Azure Event Hubs and Stream Analytics for real-time data ingestion and analytics, with Power BI connecting to both the serverless SQL endpoint and Stream Analytics output. This minimizes data movement by keeping data in the lake, supports both batch and real-time analytics, and uses a serverless option to stay within a limited budget.

Answer

Use Azure Data Factory to copy all data to Azure Data Lake Storage Gen2, then use Azure Synapse Serverless SQL pool to query the data, and finally connect Power BI to the serverless SQL endpoint.

Answer

Use Azure Database Migration Service to migrate the SQL Server database to Azure SQL Database, then use Azure Synapse Analytics with a dedicated SQL pool to perform analytics, and connect Power BI to the dedicated pool.

Answer

Use Azure Data Factory to copy all data to Azure Blob Storage, then use Azure Stream Analytics to perform real-time analytics, and connect Power BI directly to Stream Analytics output.

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

Azure Synapse Pipelines (part of Azure Synapse Analytics) are also correct. They are used for orchestrating and automating data movement and transformation activities, including large-scale batch processing. They enable the creation of complex ETL/ELT workflows to ingest, prepare, and transform data from various sources, making them suitable for batch processing of large data volumes.

Answer

Azure Data Lake Storage

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Question 10

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. Azure Databricks is essential for implementing robust data transformations and quality checks between the medallion layers (Bronze to Silver, Silver to Gold) using Apache Spark and Delta Lake. Azure Synapse Analytics provides the integrated analytics platform, including data integration pipelines, Spark pools (complementary to or alternative to Databricks for processing), and dedicated SQL pools for the high-performance serving layer (Gold), making it a core component of a modern data warehouse.

Answer

Azure Analysis Services

Answer

Azure DevOps

Question 11

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

Accepted Answer

Customer-managed keys (CMK) with TDE. Transparent Data Encryption (TDE) is a fundamental feature in Azure Synapse Analytics dedicated SQL pools that encrypts data at rest, including databases, backups, and transaction log files. It is enabled by default using Microsoft-managed keys. Customer-managed keys (CMK) with TDE is an enhanced method where you provide and manage your own encryption keys in Azure Key Vault, giving you greater control over the encryption process and key lifecycle, while still leveraging TDE as the underlying encryption mechanism for data at rest.

Answer

Always Encrypted

Answer

Column-level security

Answer

Dynamic Data Masking

Question 12

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 is a fully managed, real-time data ingestion service that can ingest millions of events per second from IoT devices. It is the correct choice for streaming data ingestion because it supports protocols like AMQP and HTTPS, and it decouples event producers from consumers, allowing the raw data to be captured and stored before processing. Azure Databricks is a powerful, distributed processing engine built on Apache Spark, ideal for transforming large volumes of data, including streaming data. It provides the necessary capabilities for complex transformations required in a big data analytics solution before loading data into a serving layer.

Answer

Azure Blob Storage

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Question 13

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.. Azure Cosmos DB supports low-latency reads for real-time dashboards when used as a second Stream Analytics output. Azure Synapse Link continuously replicates the Cosmos DB container to Azure Synapse Analytics, enabling sub-second queries on historical data without impacting the write-intensive SQL Database. Option A adds batch processing latency and does not fully separate workloads. Option C uses Azure Analysis Services, which is not designed for sub-second queries on raw historical data and would not solve the performance issue. Option D attempts to fix the existing SQL Database, but the high write volume is already a bottleneck, and columnstore indexes or scaling would not guarantee sub-second queries on years of data without significant partitioning and still impact write performance.

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