DP-900 Practice Test 39 — 15 Questions

Question 1

A company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. Every day, they need to incrementally load 100 GB of new sales data from CSV files stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The load should use PolyBase for efficient parallel data transfer and must be orchestrated on a recurring schedule. Which Azure service should they use to create and manage this pipeline?

Accepted Answer

A: Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it provides native orchestration and scheduling capabilities for data pipelines. It supports PolyBase as a sink to load data into Azure Synapse dedicated SQL pool in parallel, and it can directly read CSV files from ADLS Gen2. ADF's built-in triggers allow you to schedule the daily incremental load without additional coding.

Answer

B: Azure Stream Analytics

Answer

C: Azure Databricks

Answer

D: Azure Logic Apps

Question 2

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. Option C is correct because 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 3

A manufacturing company ingests a continuous stream of sensor data from thousands of IoT devices into Azure Event Hubs. The company also stores historical equipment maintenance records in Azure SQL Database. The operations team needs to join the streaming sensor data with the historical maintenance records in near real-time to detect anomalies, and data scientists need to run ad-hoc T-SQL queries on the combined dataset for analysis. Which Azure service should they use as the primary analytics platform to meet both requirements?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest streaming data from Azure Event Hubs via its built-in Spark pools or pipelines, and simultaneously query historical data in Azure SQL Database using T-SQL. This enables near real-time anomaly detection through streaming joins and ad-hoc T-SQL queries for data scientists, all within a single service without needing separate tools.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 4

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 5

An e-commerce company runs a transactional database on Azure SQL Database. During peak shopping seasons, they experience performance degradation due to high read traffic on product catalog tables. The company wants to offload read queries to a read-only copy of the database without affecting write performance. What should they implement?

Accepted Answer

Configure Active Geo-Replication to create a readable secondary replica.. Option A is correct because Active Geo-Replication allows creating readable secondary replicas in the same or different region. Option B (Failover groups) is for disaster recovery, not read scaling. Option C (Read Scale-Out) is a feature of Azure SQL Database that automatically routes read queries to a secondary replica, but it is not available in all service tiers; the correct feature name is Active Geo-Replication for readable secondaries. Option D (Azure Cache for Redis) adds caching but not a read-only copy of the database.

Answer

Enable Read Scale-Out on the database.

Answer

Implement Azure Cache for Redis to cache product catalog data.

Answer

Create a failover group with a secondary region.

Question 6

A data engineering team is designing a modern data warehouse using Azure Synapse Analytics. They want to follow a lakehouse architecture where raw data is stored in its native format and then processed and curated for reporting. Which component in Azure Synapse Analytics is primarily used to store raw data in its original format without requiring a schema?

Accepted Answer

Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct component because it provides a scalable, hierarchical file system that can store raw data in any native format (e.g., CSV, JSON, Parquet) without requiring a predefined schema. This aligns with the lakehouse architecture's requirement to ingest and persist raw data as-is before any transformation or curation.

Answer

Dedicated SQL pool

Answer

Serverless SQL pool

Answer

Synapse Pipeline

Question 7

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 8

A company needs to ingest data from an on-premises SQL Server database into Azure SQL Database every hour. During the ingestion, they need to filter out rows where Status = 'Inactive' and convert a date column to a different format. They want a cloud-based, code-free solution that can schedule and orchestrate this task. Which Azure service should they use?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows is the correct choice because it provides a cloud-based, code-free ETL service that can ingest data from on-premises SQL Server into Azure SQL Database, apply transformations like filtering rows (Status = 'Inactive') and converting date formats, and schedule the task using triggers. Mapping Data Flows run on Spark clusters and allow visual data transformation without writing code, making it ideal for this orchestrated, scheduled ingestion.

Answer

Azure Logic Apps

Answer

Azure Functions

Answer

Azure SQL Database Change Data Capture

Question 9

A company receives daily sales data from multiple retail stores as CSV files that are uploaded to Azure Blob Storage. The data must be cleansed, validated, and aggregated before being loaded into Azure Synapse Analytics for reporting. The transformations involve complex business logic and must run reliably every night. The company wants a service that can orchestrate and execute the entire pipeline with minimal development effort. Which Azure service should they use?

Accepted Answer

Azure Data Factory with mapping data flows. Azure Data Factory with mapping data flows is correct because it provides a code-free, visual interface for building complex data transformations (cleansing, validation, aggregation) that can be orchestrated on a schedule. Mapping data flows execute at scale on Azure Databricks clusters without requiring manual Spark code, making it ideal for nightly batch ETL pipelines with minimal development effort.

Answer

Azure Stream Analytics

Answer

Azure Databricks

Answer

Azure Logic Apps

Question 10

A company is migrating a large on-premises SQL Server database to Azure. They require high availability with automatic failover and read-scale for reporting workloads. Which Azure service should they use?

Accepted Answer

Azure SQL Database Business Critical tier with failover groups. Option B is correct because Azure SQL Database with Business Critical tier and failover group provides automatic failover and read-scale. Option A is wrong because Hyperscale is for very large databases but not primarily for high availability with read-scale. Option C is wrong because SQL Server on Azure VMs requires manual configuration for high availability. Option D is wrong because Azure Database for PostgreSQL is not a SQL Server migration target.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure Database for PostgreSQL with geo-redundant backup

Answer

SQL Server on Azure Virtual Machines with Always On availability groups

Question 11

A company needs to run complex analytical queries that aggregate terabytes of sales data across multiple years. The queries are used for monthly business reports and are not latency-sensitive. The data is stored in Azure Data Lake Storage Gen2. The company wants a fully managed, petabyte-scale data warehouse solution that supports SQL queries and integrates with Power BI for reporting. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is a fully managed, petabyte-scale analytics service that provides a dedicated SQL pool for running complex, high-performance T-SQL queries against massive datasets. It natively integrates with Azure Data Lake Storage Gen2 for reading data directly via PolyBase or external tables, and it offers built-in connectors to Power BI for reporting. This makes it the ideal choice for the described workload, which requires large-scale aggregation without low-latency demands.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure HDInsight

Question 12

A company needs to build a centralized analytics platform that can query both structured data in a relational data warehouse and unstructured data in a data lake using a single SQL-based interface. They want to minimize data movement and use a serverless, on-demand compute model for ad-hoc queries. Which Azure service should they use?

Accepted Answer

B. Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is correct because it provides a SQL-based interface to query both structured data in a relational data warehouse and unstructured data in a data lake (e.g., Parquet, CSV, JSON) without moving data. It uses a serverless, on-demand compute model that charges per query, making it ideal for ad-hoc analytics with minimal data movement.

Answer

A. Azure SQL Database

Answer

C. Azure HDInsight

Answer

D. Azure Analysis Services

Question 13

A company needs to run complex SQL queries on petabytes of data stored in Azure Data Lake Storage Gen2. They want to pay only for the queries they run and do not want to manage any infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it enables running complex SQL queries directly against data in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, charging only for the amount of data processed, which aligns with the requirement to pay only for queries run and avoid infrastructure management.

Answer

Azure Synapse dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight

Question 14

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 15

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Accepted Answer

Billing per second for compute usage. Options A, B, and E are correct. Serverless auto-scales, pauses during inactivity, and charges per second. Option C is wrong because serverless does not provide a fixed SLA for compute. Option D is wrong because serverless is not designed for high throughput workloads; it may have cold start delays.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications