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

Question 1

A data engineer needs to query data stored in CSV files in Azure Data Lake Storage Gen2 using T-SQL in Azure Synapse Analytics, without loading the data into the database. Which feature should they use?

Accepted Answer

External tables. External tables in Azure Synapse Analytics allow you to query data stored in files (such as CSV in Azure Data Lake Storage Gen2) using T-SQL without loading the data into the database. They use the PolyBase engine to read the files directly, enabling on-the-fly querying of external data sources.

Answer

Materialized views

Answer

Stored procedures

Answer

Indexed views

Question 2

A data analyst needs to run interactive SQL queries against petabytes of sales data stored in Parquet format in Azure Data Lake Storage Gen2. The analyst wants the fastest query performance for ad-hoc exploration without provisioning or managing any infrastructure. Which Azure service should they use?

Accepted Answer

B. Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it enables running interactive T-SQL queries directly against Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query model and leverages a distributed query engine to deliver fast performance on petabytes of data, making it ideal for ad-hoc exploration.

Answer

A. Azure SQL Database

Answer

C. Azure HDInsight

Answer

D. Azure Data Factory

Question 3

A data engineer needs to process raw clickstream data from multiple websites that is stored in Azure Blob Storage as JSON files. The processing must run automatically every hour, transform the data into a structured format for reporting, and handle schema changes in the source data without manual intervention. Which Azure service should be used?

Accepted Answer

Azure Data Factory with a Mapping Data Flow.. Azure Data Factory with a Mapping Data Flow is correct because it provides a code-free, visual data transformation environment that can run on a scheduled trigger (every hour), handle schema drift automatically via schema drift options in Mapping Data Flows, and process JSON files from Azure Blob Storage into a structured format for reporting. This meets all requirements: scheduled execution, transformation, and schema evolution without manual intervention.

Answer

Azure Stream Analytics with a reference data input.

Answer

Azure SQL Database with a stored procedure.

Answer

Azure Logic Apps with a JSON parser.

Question 4

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 5

A company uses Azure Synapse Analytics to run complex queries against large datasets stored in Parquet files in Azure Data Lake Storage Gen2. They notice that queries scanning entire partitions are slow due to high I/O overhead on the compute nodes. Investigation shows each daily partition contains thousands of small files (under 1 MB each). Which optimization should be implemented first to improve query performance?

Accepted Answer

Compact small files into larger ones before querying. Option C is correct because the high I/O overhead is caused by the thousands of small files per partition. When Synapse compute nodes read many small files, the overhead of opening, reading metadata, and closing each file dominates, even though the total data volume is small. Compacting these small files into fewer, larger files (e.g., 128 MB or more) reduces the number of file operations, improves read throughput, and allows more efficient predicate pushdown and parallelism.

Answer

Increase the number of compute nodes

Answer

Use columnstore indexes on external tables

Answer

Change the partition column to a different date granularity

Question 6

A data engineer needs to build a pipeline that runs every hour, copies new sales data from an on-premises SQL Server to Azure Data Lake Storage Gen2, transforms the data using PySpark, and then loads it into Azure Synapse Analytics dedicated SQL pool. Which Azure service should be used to orchestrate the entire pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data integration service designed to orchestrate complex pipelines. It can copy data from on-premises SQL Server via a self-hosted integration runtime, trigger the pipeline on an hourly schedule, execute PySpark transformations in Azure Databricks or HDInsight, and load the results into Azure Synapse Analytics dedicated SQL pool—all within a single, managed orchestration workflow.

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Answer

Azure Databricks

Question 7

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 8

A retail chain needs to blend two data sources for a near real-time dashboard: daily batch files from store systems (CSV files on Azure Blob Storage updated once per day) and live web clickstream data from Azure Event Hubs. The dashboard must refresh every 5 minutes with combined data. Which combination of Azure services should be used to ingest and process both data types most efficiently?

Accepted Answer

C) Azure Synapse Pipelines + Azure Stream Analytics. Option C is correct because Azure Synapse Pipelines can orchestrate the daily batch CSV files from Azure Blob Storage, while Azure Stream Analytics processes the live web clickstream data from Azure Event Hubs in near real-time. Together, they enable a combined data pipeline that refreshes every 5 minutes, meeting the dashboard's latency requirement efficiently.

Answer

A) Azure Data Factory + Azure Analysis Services

Answer

B) Azure Stream Analytics + Power BI

Answer

D) Azure Databricks + Azure Data Lake Storage

Question 9

A data engineering team needs to analyze petabytes of historical sales data stored in Azure Data Lake Storage Gen2. They require the ability to run complex SQL queries that join multiple tables and need high performance. The solution must separate compute from storage to allow independent scaling of resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is designed for petabyte-scale data warehousing, providing massively parallel processing (MPP) to run complex SQL queries across multiple tables with high performance. It separates compute from storage, allowing independent scaling of compute resources without moving data, which aligns with the requirement for decoupled scaling.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Question 10

A financial institution runs complex analytical queries on trading data stored in Parquet files in Azure Data Lake Storage Gen2. The data is partitioned by date and contains billions of rows. Analysts frequently query within a specific date range, and the queries must return results in under 5 seconds. The current solution uses Azure Synapse Serverless SQL pool, but queries are slow because the serverless pool scans all partitions even when the WHERE clause filters on the date column. Which optimization should be implemented to improve query performance?

Accepted Answer

Switch to Azure Synapse dedicated SQL pool with proper table partitioning. Azure Synapse Serverless SQL pool does not support partition elimination based on the partitioning of the underlying Parquet files in Azure Data Lake Storage Gen2. By switching to an Azure Synapse dedicated SQL pool with proper table partitioning on the date column, the query engine can perform partition pruning, scanning only the relevant partitions for the specified date range, which drastically reduces I/O and improves query performance to meet the sub-5-second requirement.

Answer

Create a clustered columnstore index on the external table

Answer

Convert the Parquet files to CSV format

Answer

Use Azure Databricks with Delta Lake for querying

Question 11

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 12

A retail company needs to run complex SQL queries on petabytes of historical sales data stored in Parquet files in Azure Data Lake Storage Gen2. They want a solution that provides fast query performance without managing infrastructure, and they prefer a pay-per-query pricing model. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it allows querying petabytes of Parquet files in Azure Data Lake Storage Gen2 using T-SQL without provisioning any infrastructure, and it charges per terabyte of data processed (pay-per-query). This matches the requirements for fast query performance on historical sales data with a serverless, consumption-based pricing model.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight with Hive

Question 13

A logistics company uses Azure Synapse Analytics dedicated SQL pool to analyze billions of shipment records. The table 'Shipments' is 10 TB and hash-distributed on 'ShipmentID'. Analysts frequently run queries that filter on 'WarehouseID' and aggregate by 'Region'. These queries are slow because they cause data movement (shuffle) across distributions. Which table design change will most improve query performance for these analytical workloads?

Accepted Answer

Change distribution to hash on 'WarehouseID'. D is correct because hash-distributing the 'Shipments' table on 'WarehouseID' ensures that all rows for a given warehouse are co-located on the same distribution node. This eliminates the need for data movement (shuffle) when queries filter on 'WarehouseID' and aggregate by 'Region', as the aggregation can be performed locally on each distribution without redistributing data across nodes.

Answer

Change distribution to replicated table

Answer

Change distribution to round-robin

Answer

Create a columnstore index

Question 14

A data engineer needs to build an analytics solution to transform large volumes of streaming data from IoT devices. The transformations involve complex Python and Spark code, and the results will be stored in Azure Data Lake Storage Gen2 for further analysis. Which Azure service is best suited for executing these transformations?

Accepted Answer

Azure Databricks. Azure Databricks is best suited because it provides an Apache Spark-based analytics platform that can execute complex Python and Spark code on large-scale streaming data. It integrates natively with Azure Data Lake Storage Gen2 for reading streaming IoT data and writing transformed results, offering optimized performance for big data transformations.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Analysis Services

Question 15

A retail company collects streaming clickstream data from its website into Azure Event Hubs. They need to aggregate the data in real-time to count page views per product every minute and store the results in Azure SQL Database for a live dashboard. Which Azure service should they use to perform this real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is purpose-built for real-time stream processing, allowing you to define a query that aggregates clickstream data from Event Hubs using a tumbling window of one minute to count page views per product. The result can be written directly to Azure SQL Database, enabling a live dashboard without additional orchestration.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Databricks