DP-900 Practice Test 36 — 15 Questions

Question 1

A data engineering team is building a data pipeline to run daily batch loads from an on-premises SQL Server to Azure Synapse Analytics. The pipeline must include data transformation using a visual interface with no coding, and must support schema mapping and data validation. Which THREE Azure services should be used together?

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

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct destination for the pipeline because it is a cloud-based data warehouse that supports high-performance analytics on large-scale data, making it ideal for daily batch loads from SQL Server. It integrates natively with Azure Data Factory for orchestration and Azure Blob Storage for staging, enabling schema mapping and data validation through visual interfaces without coding.

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 2

A marketing company collects data from social media feeds including text posts, images, and videos. The data arrives in various formats with no fixed structure or schema. This type of data is best described as:

Accepted Answer

C) Unstructured data. Unstructured data lacks a predefined data model or schema, making it ideal for storing text posts, images, and videos that arrive in varied formats. Unlike structured or semi-structured data, unstructured data cannot be easily organized into rows and columns or parsed with tags, which is why option C is correct for this scenario.

Answer

A) Structured data

Answer

B) Semi-structured data

Answer

D) Relational data

Question 3

A company stores employee records in a relational database table with columns EmployeeID, FirstName, LastName, Department. They also store employee handbooks as PDF files, and customer feedback as XML documents. Which of the following correctly classifies these data types?

Accepted Answer

Employee records: structured, Employee handbooks: unstructured, Customer feedback: semi-structured. Employee records in a relational database table have a fixed schema (columns and data types), making them structured data. Employee handbooks stored as PDF files have no internal schema and are binary blobs, classifying them as unstructured data. Customer feedback stored as XML documents have a flexible, self-describing schema with tags, making them semi-structured data.

Answer

Employee records: structured, Employee handbooks: semi-structured, Customer feedback: unstructured

Answer

Employee records: semi-structured, Employee handbooks: unstructured, Customer feedback: structured

Answer

Employee records: unstructured, Employee handbooks: semi-structured, Customer feedback: structured

Question 4

Drag and drop the steps to configure a firewall rule for Azure SQL Database in the correct order.

Accepted Answer

Identify the client IP address, then add a firewall rule with that IP range to the Azure SQL Server, then save the rule.. Firewall rules are set at the server level to allow client IP addresses to access the database.

Answer

Add a firewall rule with a broad IP range to the Azure SQL Server, then identify the client IP address, then save the rule.

Answer

Add a firewall rule to the Azure SQL Server, save the rule, then identify the client IP address.

Answer

Identify the client IP address, then save the rule, then add a firewall rule to the Azure SQL Server.

Question 5

A retail company needs to analyze clickstream data from their website in real time to detect fraudulent activity and also run complex historical queries on months of data to identify shopping trends. They want a single service that can handle both streaming and batch analytics using a unified query language, minimizing data movement. Which Azure service should they use?

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for real-time analytics on streaming data and can also handle complex historical queries over large volumes of data using the Kusto Query Language (KQL). It minimizes data movement by ingesting streaming data directly and storing it in a columnar format optimized for both real-time and batch queries, making it the ideal single service for this scenario.

Answer

Azure Stream Analytics

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Question 6

A mobile game company stores player profiles and game state in Azure Cosmos DB. Each document contains playerId, level, score, inventory (an array of items), and lastLogin. The application requires fast point reads by playerId, queries to find all players within a specific score range, and global distribution with multi-region writes for low latency worldwide. They also want to use a familiar SQL-like query language. Which Azure Cosmos DB API should they choose?

Accepted Answer

Core (SQL) API. The Core (SQL) API is the correct choice because it provides native support for SQL-like queries, enabling the required point reads by playerId and range queries on score. It also offers multi-region writes for global distribution with low latency, which aligns with the application's need for worldwide player access. The document model with arrays (inventory) is directly supported, making it ideal for storing player profiles and game state.

Answer

MongoDB API

Answer

Cassandra API

Answer

Gremlin API

Question 7

A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?

Accepted Answer

Create a non-clustered index on (CustomerID, OrderDate).. A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.

Answer

Rebuild the clustered index on (OrderDate, CustomerID).

Answer

Create a non-clustered index on OrderDate.

Answer

Create a filtered index on OrderDate for recent dates.

Question 8

A company uses Azure SQL Database for its e-commerce platform. The reporting team runs complex, long-running queries that join multiple tables and would degrade performance of the transactional workload if executed on the primary database. Which Azure SQL Database feature should the company enable to isolate the reporting queries while ensuring read-only access to the most current data?

Accepted Answer

Read scale-out (read-only replicas). Read scale-out (read-only replicas) allows you to offload reporting queries to a read-only replica of the Azure SQL Database, ensuring that complex, long-running queries do not degrade the performance of the primary transactional workload. The replica provides access to the most current data because it uses snapshot isolation and is transactionally consistent with the primary database.

Answer

Automatic tuning

Answer

Geo-replication

Answer

Elastic query

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 manufacturing company needs to build an analytics solution for IoT sensor data. Thousands of devices send real-time temperature and vibration readings. The solution must: (1) ingest the streaming data reliably, (2) perform real-time aggregations (e.g., average temperature per device every minute), and (3) store the aggregated results in Azure Synapse Analytics for historical reporting and dashboards. Which combination of Azure services should be used?

Accepted Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Synapse Analytics. Azure Event Hubs is designed for high-throughput, reliable ingestion of streaming data from millions of IoT devices. Azure Stream Analytics can then perform real-time aggregations (like average temperature per device per minute) using a SQL-like query language. Finally, Azure Synapse Analytics provides a dedicated SQL pool or serverless SQL endpoint for storing and querying the aggregated results, enabling historical reporting and dashboards.

Answer

Azure IoT Hub -> Azure Data Factory -> Azure Cosmos DB

Answer

Azure Blob Storage -> Azure Databricks -> Azure SQL Database

Answer

Azure Service Bus -> Azure Functions -> Azure Table Storage

Question 11

A retail company stores customer data in three formats: a relational database table with fixed columns for CustomerID, Name, and Email; customer feedback as JSON documents with varying fields such as rating and comment; and product images as JPEG files. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Relational table, JSON documents, image files. Relational tables enforce a fixed schema with defined columns and data types, making them the most structured. JSON documents are semi-structured, allowing varying fields and flexible schemas, while image files are unstructured binary data with no inherent schema. This ordering from most to least structured aligns with the core data classification concept in the DP-900 exam.

Answer

JSON documents, relational table, image files

Answer

Image files, JSON documents, relational table

Answer

Relational table, image files, JSON documents

Question 12

You have an Azure Blob Storage container configured with the JSON snippet shown in the exhibit. What does the 'publicAccess' setting of 'Blob' allow?

Accepted Answer

Anonymous users can read blobs if they know the blob URL. The 'Blob' level of public access allows anonymous read access to blobs only; container metadata is not accessible. 'Container' level would allow anonymous listing of blobs. 'None' disables public access. 'Storage' is not a valid value.

Answer

Anonymous users can write blobs

Answer

No anonymous access is allowed

Answer

Anonymous users can list blobs in the container

Question 13

Which TWO Azure services are designed for big data batch processing?

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides an Apache Spark-based analytics platform optimized for batch processing large datasets, enabling ETL, data transformation, and machine learning at scale. It uses distributed computing to process data in parallel across clusters, making it ideal for big data batch workloads.

Azure HDInsight is also correct as it is a managed, full-spectrum, open-source analytics service for enterprises. It allows you to run popular open-source frameworks like Hadoop (for MapReduce batch processing), Spark (for batch and interactive processing), Hive, and others on Azure, making it suitable for big data batch processing scenarios.

Answer

Azure Data Explorer

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 14

A social media application stores user posts in Azure Cosmos DB using the NoSQL API. Each document includes: PostID (unique), UserID, Timestamp, Content. The most common query is: 'Get all posts for a specific UserID, sorted by Timestamp descending.' Which partition key should be chosen to distribute load evenly across physical partitions while also supporting this query efficiently?

Accepted Answer

UserID. UserID is the correct partition key because it evenly distributes write operations across physical partitions (each user has a unique ID) and directly supports the most common query: filtering by UserID. With UserID as the partition key, the query 'Get all posts for a specific UserID, sorted by Timestamp descending' becomes a single-partition query (using the partition key in the WHERE clause), which is efficient and avoids cross-partition fan-out. This design also allows Cosmos DB to use the Timestamp field as a sort key within each logical partition, enabling efficient sorting without additional indexing overhead.

Answer

PostID

Answer

Timestamp

Answer

Content

Question 15

Which THREE of the following are valid considerations when choosing between Azure Blob Storage and Azure Data Lake Storage Gen2 for a big data analytics workload?

Accepted Answer

ADLS Gen2 can be optimized for high-throughput analytics workloads. ADLS Gen2 supports a hierarchical namespace, POSIX-like permissions, and is cost-effective for both hot and cool tiers. Blob Storage lacks hierarchical namespace by default. Both support lifecycle management. ADLS Gen2 can be used with Blob APIs but also has additional features.

Answer

Blob Storage provides POSIX-compliant access control lists (ACLs)

Answer

ADLS Gen2 cannot use Blob Storage APIs