DP-900 Practice Exam 52 — 20 Questions

Question 1

A data engineer needs to implement a solution that provides near real-time analytics on clickstream data. The data arrives as JSON events and must be queryable with sub-second latency using SQL-like queries. The solution should minimize operational overhead. Which Azure service should they use?

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

Accepted Answer

Azure Data Explorer. Azure Data Explorer (ADX) is designed for interactive analytics on large volumes of streaming and historical data with sub-second query latency using Kusto Query Language (KQL), which supports SQL-like syntax. It natively ingests JSON events, provides near real-time analytics, and minimizes operational overhead as a fully managed, serverless service.

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Answer

Azure Synapse Analytics

Question 2

Your organization uses Azure SQL Database and needs to ensure that all customer data is encrypted at rest and in transit with minimal administrative overhead. Which solution should you recommend?

Accepted Answer

Enable Transparent Data Encryption (TDE) and enforce TLS 1.2 for connections.. Transparent Data Encryption (TDE) encrypts Azure SQL Database data files at rest without requiring any application changes, and enforcing TLS 1.2 ensures all data in transit is encrypted using a strong, industry-standard protocol. This combination meets the requirement for encryption at rest and in transit with minimal administrative overhead, as TDE is managed by the platform and TLS enforcement is a simple server-level setting.

Answer

Use Microsoft Purview Information Protection to label and encrypt the data.

Answer

Implement Dynamic Data Masking on the customer table.

Answer

Enable Always Encrypted for all sensitive columns and use client-side encryption.

Question 3

A company wants to run SQL queries on data stored in Azure Cosmos DB for NoSQL. Which API should they use?

Accepted Answer

Core (SQL) API. The Core (SQL) API is the native API for Azure Cosmos DB for NoSQL, designed to query JSON documents using a SQL-like syntax. Since the requirement is to run SQL queries on data stored in Azure Cosmos DB for NoSQL, this API directly supports that need without requiring any protocol translation or schema mapping.

Answer

Gremlin API

Answer

Cassandra API

Answer

MongoDB API

Question 4

Match each Azure Cosmos DB API to its supported data model.

Accepted Answer

SQL API: Document. Azure Cosmos DB APIs map to specific data models: SQL and MongoDB for document, Cassandra for wide-column, Gremlin for graph, and Table for key-value. Common confusions involve misassigning Gremlin and Table.

Answer

Gremlin API: Key-value

Answer

Table API: Graph

Question 5

A data analyst needs to run ad-hoc SQL queries on large volumes of data stored as Parquet files in Azure Data Lake Storage Gen2. The queries are unpredictable, and the analyst wants to pay only for the compute resources consumed by each query. Which Azure Synapse Analytics compute model should be used?

Accepted Answer

Serverless SQL pool. Serverless SQL pool is the correct choice because it allows running ad-hoc SQL queries directly on data in Azure Data Lake Storage Gen2 without provisioning any fixed compute resources. It uses a pay-per-query billing model, charging only for the amount of data processed by each query, which aligns perfectly with the unpredictable query patterns described.

Answer

Dedicated SQL pool

Answer

Apache Spark pool

Answer

Azure Data Explorer pool

Question 6

Match each Azure data consistency model to its description.

Accepted Answer

Strong: Reads always return the most recent write.. Azure Cosmos DB offers five consistency levels: strong, bounded staleness, session, consistent prefix, and eventual. Strong returns the most recent write; bounded staleness allows a bounded lag; session ensures per-session guarantees.

Answer

Strong: Reads may lag behind writes by up to 5 seconds.

Answer

Session: Guarantees that all replicas have the same data at all times.

Question 7

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

You need to provide temporary access to a specific blob in Azure Blob Storage for a limited time. The access should be time-limited and require no authentication from the user. Which mechanism should you use?

Accepted Answer

Shared access signatures (SAS). A Shared Access Signature (SAS) is a URI that grants restricted, time-limited access to a specific Azure Storage resource without exposing the account key and without requiring the caller to authenticate with Microsoft Entra ID. You can scope a SAS to a single blob, define start and expiry times, and limit permissions to read-only, which exactly matches the requirement for temporary, unauthenticated access to one blob.

Answer

Storage account keys

Answer

Anonymous public access

Answer

Azure RBAC roles

Question 9

A financial institution needs to run complex queries against petabytes of historical trading data stored in Azure Data Lake Storage. The queries must be efficient and use columnar storage format. Which technology should they use to process this data?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a cloud-based analytics service designed for petabyte-scale data warehousing. It supports PolyBase to query data directly from Azure Data Lake Storage and uses a columnar storage format (via clustered columnstore indexes) to enable efficient, high-performance analytical queries on massive historical datasets.

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Question 10

Your organization stores sensitive financial data in Azure SQL Database. You need to audit all SELECT operations on the 'Transactions' table and alert the security team when a user outside the finance department queries the table. Which feature should you use?

Accepted Answer

SQL Server Auditing. SQL Server Auditing is the correct choice because it tracks database events, including SELECT operations, and writes them to an audit log. You can configure an audit policy to capture all SELECT statements on the 'Transactions' table and then set up an alert (e.g., via Azure Monitor or Logic Apps) that triggers when a user from outside the finance department executes such a query. This directly meets the requirement to both audit and alert on specific user actions.

Answer

Microsoft Defender for SQL

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Question 11

A logistics company tracks package deliveries. When a package is scanned at a distribution center, the system immediately updates the delivery status in a database so customers can see the live tracking information. At the end of each day, the company runs a job that aggregates all delivery status changes into a report for operational analysis. Which of the following best describes these two data processing workloads?

Accepted Answer

The status update is a real-time workload, and the daily report is a batch workload.. The immediate status update upon scanning is a real-time workload, as it processes data instantly for live customer visibility. The end-of-day aggregation job is a batch workload, as it processes accumulated data in a scheduled, non-real-time manner for operational reporting.

Answer

Both are batch processing workloads.

Answer

Both are real-time processing workloads.

Answer

The status update is a batch workload, and the daily report is a real-time workload.

Question 12

A research team needs to store thousands of PDF reports that vary in length and structure. The storage solution must allow flexible schema and support access from multiple programming languages via HTTP. Which data storage category best describes these reports?

Accepted Answer

Unstructured data. C is correct because PDF reports with varying length and structure are binary files that do not conform to a predefined data model or schema, which is the definition of unstructured data. Azure Blob Storage or Amazon S3 are typical services for storing such unstructured data, accessed via HTTP REST APIs from any programming language.

Answer

Structured data

Answer

Semi-structured data

Answer

Transactional data

Question 13

Match each Azure data migration tool to its use case.

Accepted Answer

Azure Migrate: Assesses on-premises servers for migration to Azure. Azure Migrate is for server assessment, Azure Database Migration Service is for database migration, and Azure Data Box is for offline data transfer. Common confusions involve swapping assessment and migration roles.

Answer

Azure Migrate: Migrates databases to Azure with minimal downtime

Answer

Azure Database Migration Service: Assesses on-premises servers for migration to Azure

Answer

Azure Data Box: Assesses on-premises servers for migration

Question 14

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The fact table contains billions of rows and is hash-distributed on ProductID. Queries aggregate sales by store and product for the current month and join with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries are slow due to data movement during joins. Which design change will most reduce data movement and improve query performance?

Accepted Answer

Replicate the Store dimension table and the Product dimension table.. Replicating the Store and Product dimension tables across all compute nodes eliminates the need to shuffle data during joins with the large fact table. Since both dimension tables are small enough to fit in memory on each node (10,000 and 500,000 rows), replication avoids costly data movement and significantly improves query performance for aggregations that join on multiple dimensions.

Answer

Change the fact table to round-robin distribution.

Answer

Change the hash distribution key of the fact table to StoreID.

Answer

Implement a clustered columnstore index on the fact table.

Question 15

In a banking application, a transaction transfers $100 from Account A to Account B. The system deducts $100 from Account A successfully, but due to a network error, the credit to Account B fails. The application rolls back the deduction from Account A, ensuring that neither account is affected. Which ACID property is being enforced?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the deduction from Account A and the credit to Account B must both succeed or both fail entirely. When the credit to Account B fails, the system rolls back the deduction from Account A, preserving the all-or-nothing nature of the transaction. This is the core behavior of atomicity in ACID-compliant database systems like Azure SQL Database or SQL Server.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 16

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

Question 17

A retail company needs to analyze sales transactions as they occur to detect fraud patterns and immediately block suspicious orders. They also need to run daily batch reports on historical sales data. Which combination of Azure services should they use to meet both real-time and batch processing requirements?

Accepted Answer

Azure Stream Analytics for real-time processing and Azure Synapse Analytics for batch analytics. Azure Stream Analytics is purpose-built for real-time data streaming and can process sales transactions as they occur to detect fraud patterns and block suspicious orders immediately. Azure Synapse Analytics provides a unified analytics platform that can run large-scale batch queries on historical sales data for daily reports, making this combination ideal for both real-time and batch processing needs.

Answer

Azure Data Factory for both real-time and batch processing

Answer

Azure Logic Apps for real-time processing and Azure Synapse Analytics for batch analytics

Answer

Azure Stream Analytics for both real-time and batch processing

Question 18

A bank's online transaction processing system records every withdrawal and deposit in a database. The bank also runs a monthly report that summarizes total transactions per customer. Which statement correctly identifies these two workloads?

Accepted Answer

The transaction recording is OLTP, and the monthly report is OLAP.. The transaction recording system is an OLTP (Online Transaction Processing) workload because it handles individual, real-time transactions (withdrawals and deposits) with high concurrency and low latency. The monthly report summarizing total transactions per customer is an OLAP (Online Analytical Processing) workload because it aggregates historical data for reporting and analysis, typically using batch processing or columnar storage. Option B correctly pairs each workload with its appropriate processing type.

Answer

Both workloads are OLTP.

Answer

The transaction recording is OLAP, and the monthly report is OLTP.

Answer

Both workloads are OLAP.

Question 19

A manufacturing company has a streaming data pipeline that ingests sensor data from factory equipment into Azure Event Hubs. The data must be prepared for reporting by cleaning invalid records, removing duplicates, and aggregating readings into 5-minute windows. The transformed data needs to be stored in a columnar format in a data lake to support efficient querying by data analysts using SQL. Which Azure service should perform the data transformation and loading?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, directly consuming data from Azure Event Hubs, performing transformations like cleaning invalid records, removing duplicates, and aggregating over tumbling windows (e.g., 5-minute windows), and outputting the results in a columnar format (e.g., Parquet) to Azure Data Lake Storage. This aligns perfectly with the requirement for a low-latency, continuous transformation pipeline without needing additional orchestration or compute clusters.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Synapse Pipelines

Question 20

A logistics company uses IoT sensors on delivery trucks to transmit GPS location, speed, and engine diagnostics every 10 seconds. The data is ingested into Azure Event Hubs. The company needs to analyze the data in real time to identify speeding trucks and send alerts. The analysis requires joining the live sensor data with a reference table of truck details (e.g., driver name, route number) stored in Azure SQL Database. Which Azure service should they use for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a real-time event processing engine designed to handle streaming data from sources like Azure Event Hubs. It can perform temporal joins between the live IoT sensor stream and a static reference table (e.g., truck details from Azure SQL Database) to enrich the data and trigger alerts when speeding is detected, all with sub-second latency.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory

Answer

Azure Databricks