DP-900 Practice Test 14 — 15 Questions

Question 1

A data scientist needs to analyze historical sales data to identify yearly trends. They run SQL queries that aggregate millions of rows. No new data is being added during analysis. Which type of data processing workload does this represent?

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

Accepted Answer

Online Analytical Processing (OLAP). This workload is Online Analytical Processing (OLAP) because the data scientist is running complex SQL queries that aggregate millions of rows of historical sales data to identify yearly trends. OLAP is designed for read-intensive, analytical queries that summarize large volumes of static data, which matches the scenario where no new data is being added during analysis.

Answer

Online Transaction Processing (OLTP)

Answer

Batch processing

Answer

Stream processing

Question 2

A company stores IoT sensor data as JSON files in Azure Blob Storage. A data analyst needs to run ad-hoc SQL queries on these files without moving the data and without provisioning any compute clusters. The analyst wants to pay only for the amount of data processed by each query. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool allows you to query data directly from Azure Blob Storage using T-SQL without provisioning any compute clusters. It uses a pay-per-query model where you are billed only for the amount of data processed, making it ideal for ad-hoc SQL queries on JSON files stored in Blob Storage without data movement.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Data Factory

Question 3

A financial services company stores account balances in Azure SQL Database (strong consistency) and transaction audit logs in Azure Cosmos DB (eventual consistency by default). A compliance requirement demands that when a transaction is rolled back in the SQL database, the corresponding audit log entries in Cosmos DB must also be removed within a short time frame. Which term best describes the difficulty of maintaining this constraint?

Accepted Answer

Distributed transaction coordination. The scenario requires coordinating a rollback across two distinct data stores—Azure SQL Database (ACID-compliant, strong consistency) and Azure Cosmos DB (eventual consistency by default). This cross-system transactional consistency is a classic distributed transaction coordination problem, often addressed via patterns like the two-phase commit (2PC) or the saga pattern, but not natively supported between these two services without custom orchestration.

Answer

ACID compliance

Answer

Idempotency

Answer

Schema flexibility

Question 4

Refer to the exhibit. A team is deploying an Azure Storage container using an ARM template. The template sets publicAccess to 'None'. However, after deployment, users report they cannot access data even with a valid SAS token. What is the most likely cause?

Accepted Answer

The SAS token was generated with insufficient permissions. The ARM template sets publicAccess to 'None', which only disables anonymous public access to the container. It does not affect SAS token access. The most likely cause is that the SAS token was generated with insufficient permissions (e.g., missing read, list, or write permissions) or with a restricted scope (e.g., limited to a specific blob or with a short expiry), preventing users from accessing the data even though the container is private.

Answer

The container disables SAS tokens by default

Answer

The storage account firewall is blocking all traffic

Answer

The storage account requires RBAC permissions, not SAS

Question 5

A retail company uses Azure Data Lake Storage Gen2 to store raw clickstream data. They need to process this data using Azure Databricks to create hourly aggregated reports. The data pipeline must minimize costs while meeting a five-minute processing SLA. What is the most cost-effective compute option?

Accepted Answer

Use job clusters with pool-based allocation. Job clusters with pool-based allocation are the most cost-effective compute option for this scenario because job clusters are ephemeral—they start only when a job runs and terminate automatically after completion, eliminating idle costs. Pool-based allocation further reduces startup latency by maintaining a warm pool of pre-initialized VMs, enabling the pipeline to meet the five-minute SLA without paying for always-on compute.

Answer

Use interactive clusters with autoscaling

Answer

Use Azure Synapse Serverless SQL pools

Answer

Provision a dedicated SQL pool in Azure Synapse

Question 6

A company stores product information such as product ID, name, price, and category in a relational database with rows and columns. This data is best described as:

Accepted Answer

Structured data. Structured data conforms to a predefined schema with rows and columns, making it easily searchable and queryable via SQL. The product information (ID, name, price, category) fits this model exactly, as each attribute has a fixed data type and is stored in a relational database table.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Transactional data

Question 7

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 8

A financial application requires strict consistency and transaction support (ACID). Which Azure data service is most appropriate for storing its core transactional data?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction support through its SQL Server engine. It is the correct choice for a financial application requiring strict consistency and transactional integrity, as it guarantees that all transactions are processed reliably and adhere to the ACID properties.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Data Lake Storage

Question 9

A company uses Azure SQL Database for a sales application. They need to replicate the database to a secondary region for disaster recovery. The secondary should be readable for reporting purposes and data should be synchronized within seconds. Which feature should they use?

Accepted Answer

Active Geo-Replication. Active Geo-Replication is the correct choice because it creates a readable secondary replica in a different Azure region, with data synchronized within seconds via asynchronous replication. This meets the requirement for both disaster recovery and read-only reporting access, as the secondary can be queried directly without impacting the primary database.

Answer

Auto-failover groups

Answer

Point-in-time restore

Answer

Long-term retention

Question 10

Which THREE of the following are valid considerations when choosing between Azure Cosmos DB and Azure Table Storage?

Accepted Answer

Cosmos DB provides multiple consistency levels. Options B, C, and E are correct. Cosmos DB provides multiple consistency levels (B), supports multi-region writes (C), and automatically indexes all properties (E). Option A is incorrect because Table Storage does not support multi-region writes; that is a feature of Cosmos DB. Option D is incorrect because Cosmos DB does support JSON documents, both natively.

Answer

Table Storage supports multi-region writes

Answer

Cosmos DB does not support JSON documents

Question 11

Which TWO of the following Azure services are considered non-relational data stores?

Accepted Answer

Azure Cosmos DB. Options B and C are correct. Azure Cosmos DB (B) is a globally distributed, multi-model NoSQL database service, making it a non-relational data store. Azure Table Storage (C) is a key-value store that is also non-relational. Option A is wrong because Azure SQL Database is a relational database. Option D is wrong because Azure Synapse Analytics is a relational analytics system. Option E is wrong because Azure Database for PostgreSQL is a relational database.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Database for PostgreSQL

Question 12

A company needs to migrate 10 on-premises SQL Server databases (each 50–200 GB) to Azure. The databases frequently run cross-database queries using three-part names (e.g., DB1.dbo.table) and rely on SQL Server Agent for maintenance tasks. They want to minimize management overhead and share resources across databases to reduce costs. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides native support for cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance tasks, which are critical requirements. It also offers a fully managed platform that minimizes management overhead while allowing resource sharing across databases within the instance, reducing costs compared to single databases or VMs.

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database single database

Question 13

You need to store semi-structured JSON documents from a web application in Azure. The data will be accessed by a key/value lookup. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents and provides key/value lookup via its partition key mechanism. It offers single-digit millisecond latency for point reads, making it ideal for web application data that needs fast, scalable access by a unique key.

Answer

Azure Blob Storage

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 14

A company uses Azure Data Factory to run a pipeline that copies new orders from an on-premises SQL Server database to Azure Data Lake Storage every hour. After the data is in the data lake, an Azure Databricks notebook transforms it and loads it into Azure Synapse Analytics for reporting. Which type of data processing does the hourly copy operation represent?

Accepted Answer

Batch processing. The hourly copy operation from on-premises SQL Server to Azure Data Lake Storage is a classic batch processing pattern: data is collected over a fixed time interval (1 hour) and processed as a single unit. Azure Data Factory orchestrates this scheduled, non-continuous transfer, which aligns with batch processing's definition of handling data in discrete, periodic chunks rather than in real-time.

Answer

Real-time streaming

Answer

Interactive query

Answer

Transactional processing

Question 15

A media company needs to store millions of high-resolution photos for a public website. Each photo can be up to 50 MB. The storage solution must support secure access via URLs. Which Azure service should they use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is the correct choice because it is designed for storing massive amounts of unstructured data, such as high-resolution photos, and supports objects up to 4.7 TB per blob, easily accommodating 50 MB files. It provides secure access via URLs using shared access signatures (SAS) or public access levels, making it ideal for a public website serving media content.

Answer

Azure Table Storage

Answer

Azure Files

Answer

Azure SQL Database