DP-900 Practice Test 38 — 15 Questions

Question 1

A company is migrating their on-premises data warehouse, which is built on a Netezza appliance, to Azure. The data warehouse contains over 10 terabytes of data and supports complex BI queries with multiple joins and aggregations. The company requires a cloud-based solution that provides massively parallel processing (MPP) to handle large-scale queries efficiently. They also need to integrate with existing ETL tools like Azure Data Factory and provide native connectivity to Power BI. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is the correct choice because it provides massively parallel processing (MPP) architecture designed for petabyte-scale data warehousing, exactly matching the 10+ TB requirement. It natively integrates with Azure Data Factory for ETL and offers built-in Power BI connectivity via the T-SQL endpoint, supporting complex BI queries with multiple joins and aggregations.

Answer

Azure SQL Database

Answer

Azure Databricks

Answer

Azure HDInsight

Question 2

A company stores weather sensor data in Azure Data Lake Storage Gen2. Data scientists need to run large-scale transformations and machine learning experiments on this data using Python and Apache Spark. They want to collaborate using shared Jupyter notebooks. Which Azure service should they use for this analytical workload?

Accepted Answer

Azure Synapse Analytics (with Apache Spark pools). Azure Synapse Analytics with Apache Spark pools provides a fully managed Spark environment that supports Python and allows data scientists to run large-scale transformations and machine learning experiments. It integrates directly with Azure Data Lake Storage Gen2 for reading and writing data, and supports collaborative Jupyter notebooks for shared development. This makes it the correct choice for the described analytical workload.

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Answer

Azure SQL Database

Question 3

A data analyst needs to run complex SQL queries against petabytes of historical sales data stored in Azure Data Lake Storage Gen2. The solution must be serverless with pay-per-query pricing. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a serverless, pay-per-query engine that can directly query petabytes of data stored in Azure Data Lake Storage Gen2 using standard T-SQL. It eliminates infrastructure management and charges only for the data processed by each query, making it ideal for ad-hoc, complex SQL workloads on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure HDInsight with Spark

Answer

Azure Analysis Services

Question 4

A data analyst needs to run ad-hoc SQL queries on petabytes of Parquet files stored in Azure Data Lake Storage Gen2. The queries are infrequent and highly selective. The analyst wants to pay only for the data scanned by each query and does not want to provision any compute resources. They also need to create views to simplify future queries for other analysts. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (correct answer) is a pay-per-query service that charges only for the data scanned, requires no provisioning of compute resources, and supports creating views over external data in Azure Data Lake Storage Gen2. It uses T-SQL to query Parquet files directly, making it ideal for infrequent, highly selective ad-hoc queries without managing infrastructure.

Answer

Azure Data Factory

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure Databricks

Question 5

A data analyst needs to run interactive SQL queries on a large dataset stored as CSV files in Azure Blob Storage. The analyst wants to explore the data using T-SQL without loading the data into a database. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows you to run interactive T-SQL queries directly against CSV files in Azure Blob Storage without loading the data into a database. It uses a pay-per-query model and leverages the OPENROWSET function to query external data in place, making it ideal for ad-hoc exploration of large datasets.

Answer

Azure SQL Database

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Question 6

A logistics company needs to analyze GPS data from delivery trucks in real time to detect delays and reroute deliveries. The GPS data is streamed into Azure Event Hubs. They also need to combine this live data with static route information stored in Azure SQL Database. Which Azure service should they use for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, capable of ingesting data from Azure Event Hubs, applying SQL-like queries to detect delays, and joining live streams with static reference data from Azure SQL Database. This enables the logistics company to analyze GPS data as it arrives and trigger rerouting actions with minimal latency.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Synapse Pipelines

Question 7

A data engineer needs to process streaming data from IoT devices and store the results in Azure Data Lake Storage for long-term analytics. The data must be processed in near real-time to detect anomalies and trigger alerts. Which Azure service should the engineer use for stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a serverless, real-time stream processing engine designed to handle high-velocity data from sources like IoT devices. It can ingest data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect anomalies in near real-time, and output results directly to Azure Data Lake Storage for long-term analytics. This makes it the correct choice for the described near-real-time anomaly detection and alerting requirement.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Data Lake Analytics

Question 8

Match each Azure data tool to its purpose.

Question 9

Drag and drop the steps to perform a point-in-time restore of an Azure SQL Database in the correct order.

Question 10

A data engineer is designing a data lake architecture in Azure. They plan to first ingest raw data from various sources into a landing zone in Azure Data Lake Storage Gen2. Then they will clean, validate, and deduplicate that data in a second zone. Finally, they will create aggregated, business-ready datasets in a third zone for analysts. This layered approach is known as which architecture?

Accepted Answer

Medallion architecture. The medallion architecture (bronze, silver, gold) is a layered data lake design pattern where raw data lands in the bronze zone, is cleaned and deduplicated in the silver zone, and aggregated into business-ready datasets in the gold zone. This directly matches the described three-zone ingestion, transformation, and aggregation pipeline in Azure Data Lake Storage Gen2.

Answer

Star schema

Answer

Snowflake schema

Answer

Lambda architecture

Question 11

A company uses Azure Synapse Analytics dedicated SQL pool for a large data warehouse. The fact table contains billions of rows and is hash-distributed on ProductID. Frequent queries join this fact table with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries aggregate sales by store and product for recent months, but run slowly due to data movement during joins. Which design change will most reduce data movement and improve query performance?

Accepted Answer

Replicate the Store dimension table. Replicating the small Store dimension table (10,000 rows) across all compute nodes eliminates the need to shuffle data during joins with the fact table. In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so queries that join a replicated table with a distributed fact table avoid costly data movement, significantly improving performance for frequent aggregation queries.

Answer

Change the distribution of the fact table to round-robin

Answer

Change the distribution key of the fact table to StoreID

Answer

Add a nonclustered index on the StoreID column in the fact table

Question 12

A data engineering team wants to build a batch analytics pipeline. The raw data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The final output will be a set of tables in Azure Synapse Analytics (dedicated SQL pool) that will be used to create reports in Power BI. Arrange the following steps in the correct order for a typical ETL process.

Accepted Answer

Ingest raw data from ADLS Gen2 into Azure Databricks for transformation using Azure Data Factory pipelines. ETL follows the order: extract raw data to ADLS, transform it, load into Synapse staging, then transform within Synapse to final tables, and finally use Power BI for reporting.

Question 13

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The fact table contains billions of rows and is hash-distributed on ProductID. Queries aggregate sales by store and product for the current month and join with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries are slow due to data movement during joins. Which design change will most reduce data movement and improve query performance?

Accepted Answer

Replicate the Store dimension table and the Product dimension table.. Replicating the Store and Product dimension tables across all compute nodes eliminates the need to shuffle data during joins with the large fact table. Since both dimension tables are small enough to fit in memory on each node (10,000 and 500,000 rows), replication avoids costly data movement and significantly improves query performance for aggregations that join on multiple dimensions.

Answer

Change the fact table to round-robin distribution.

Answer

Change the hash distribution key of the fact table to StoreID.

Answer

Implement a clustered columnstore index on the fact table.

Question 14

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. They frequently run queries that aggregate sales by product and region over the past month. The queries are slow because they scan the entire table. Which index type should they implement on the fact table to improve query performance for these aggregations?

Accepted Answer

Clustered columnstore index. A clustered columnstore index is ideal for large fact tables in Azure Synapse Analytics dedicated SQL pool because it stores data column-wise, enabling high compression and eliminating the need to scan irrelevant columns. For aggregation queries that sum sales by product and region over the past month, the columnstore index significantly reduces I/O by reading only the necessary columns and applying batch-mode processing, which accelerates scan and aggregation operations.

Answer

Clustered index on the primary key

Answer

Hash-distributed table on SalesID

Answer

Non-clustered index on (ProductID, Region)

Question 15

A startup is building a new application and needs a relational database that supports JSON data, automatic scaling, and a serverless compute tier to minimize costs during low usage periods. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database serverless tier. Option C is correct because Azure SQL Database serverless tier supports JSON, automatic scaling, and pauses during inactivity. Option A is wrong because Azure Cosmos DB is NoSQL, not relational. Option B is wrong because Azure SQL Managed Instance does not have a serverless tier. Option D is wrong because Azure Database for PostgreSQL serverless does not support JSON as naturally as SQL Server.

Answer

Azure Database for PostgreSQL serverless

Answer

Azure Cosmos DB

Answer

Azure SQL Managed Instance