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

Question 1

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?

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

Accepted Answer

Compact small files into larger ones before querying. 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 2

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

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 6

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 7

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 8

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 9

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 10

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 11

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

Question 12

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table containing 5 TB of sales transactions. New data arrives continuously and is loaded daily. The company needs to load 500 GB of new data each day while allowing concurrent read queries on the most recent data without performance degradation. Which loading strategy optimizes both load speed and query performance?

Accepted Answer

Load data into a staging table, then use partition switching to swap the latest partition. Partition switching allows you to load new data into a staging table, then instantly swap the staging partition with the target table's latest partition using ALTER TABLE SWITCH. This minimizes metadata changes and avoids data movement, enabling fast loads while keeping the fact table online for concurrent read queries without blocking or performance degradation.

Answer

Use INSERT statements to add rows incrementally

Answer

Use CREATE TABLE AS SELECT (CTAS) to build a new table and rename it

Answer

Use PolyBase to load data directly into the fact table

Question 13

A business analyst needs to create interactive visualizations and share dashboards with colleagues using data stored in an Azure Synapse Analytics dedicated SQL pool. Which tool should the analyst use?

Accepted Answer

Power BI Desktop. Power BI Desktop is the correct tool because it is designed for creating interactive visualizations and dashboards, and it can connect directly to Azure Synapse Analytics dedicated SQL pools via the built-in Azure Synapse Analytics connector. This allows the business analyst to build reports and share them with colleagues through the Power BI service, meeting the requirement for interactive dashboards and collaboration.

Answer

Azure Data Studio

Answer

SQL Server Management Studio (SSMS)

Answer

Azure Machine Learning Studio

Question 14

A data analyst needs to run ad-hoc SQL queries on petabytes of log data stored as Parquet files in Azure Data Lake Storage Gen2. The queries join multiple tables and require high concurrency from multiple analysts. The solution should minimize cost by only paying for queries executed. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, making it cost-effective for sporadic, high-concurrency workloads where you only want to pay for the compute resources consumed during query execution.

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 15

A company has a data warehouse in Azure Synapse Analytics dedicated SQL pool. They need to load new sales data every night from a CSV file stored in Azure Data Lake Storage Gen2. The load process must be automated, scheduled, and have error handling for failed loads. Which Azure service should they use to orchestrate this process?

Accepted Answer

Azure Data Factory. Azure Data Factory is the correct choice because it is a cloud-based ETL service designed specifically for orchestrating and automating data movement and transformation at scale. It supports scheduled triggers, native connectors to Azure Data Lake Storage Gen2 and Azure Synapse Analytics, and built-in error handling via retry policies and failure activities, making it ideal for nightly CSV file loads.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps