DP-900 Practice Exam 55 — 20 Questions

Question 1

A company stores customer orders in a relational database that handles many small transactions (inserts, updates, deletes) throughout the day. Separately, they maintain a data warehouse that is used for complex aggregations and historical trend analysis. Which statement correctly describes these two workloads?

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

Accepted Answer

The first system is an OLTP workload; the second is an OLAP workload.. The first system handles many small, concurrent transactions (inserts, updates, deletes) typical of an Online Transaction Processing (OLTP) workload, optimized for ACID compliance and fast query response. The second system is an Online Analytical Processing (OLAP) workload, designed for complex aggregations and historical trend analysis using columnar storage and star schemas. This distinction is fundamental in data architecture, where OLTP systems prioritize write performance and OLAP systems prioritize read performance for large-scale analytics.

Answer

Both systems are OLTP workloads because they store customer orders.

Answer

The first system is an OLAP workload; the second is an OLTP workload.

Answer

Both systems are OLAP workloads because they both involve data storage.

Question 2

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 3

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 4

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 5

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 6

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 7

A company processes sales transactions in real-time from a retail website. Each transaction is recorded as a row in a relational database. Additionally, the company stores weekly sales reports as PDF files. Which statement correctly describes these data types?

Accepted Answer

Transactions are structured, reports are unstructured.. Transactions are structured because they are stored as rows in a relational database, which imposes a fixed schema with defined columns and data types. Weekly sales reports as PDF files are unstructured because they lack a predefined data model and cannot be easily queried using SQL without additional processing. Option B correctly identifies this distinction.

Answer

Transactions are unstructured, reports are semi-structured.

Answer

Both are structured because they are files.

Answer

Both are unstructured because they are digital.

Question 8

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 9

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 10

A data analyst needs to run ad-hoc SQL queries on terabytes of CSV files stored in Azure Data Lake Storage Gen2. The queries are infrequent and unpredictable. The analyst wants to pay only for the amount of data processed by each query, and does not want to manage any compute or storage infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (C) is the correct choice because it allows querying data in Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, making it ideal for infrequent, unpredictable ad-hoc queries, and it eliminates infrastructure management.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Data Factory

Answer

Azure Analysis Services

Question 11

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 12

A company uses Azure Synapse Analytics dedicated SQL pool for large-scale data warehousing. They have a fact table with billions of rows and frequently run queries that filter by a date range and join with a product dimension table. Which table distribution and partitioning strategy will minimize data movement and improve query performance?

Accepted Answer

Hash-distribute on ProductID with partitioning on Date. Hash-distributing the fact table on ProductID ensures that rows for the same product are co-located on the same distribution, minimizing data movement when joining with the product dimension table. Partitioning on Date allows partition elimination for date-range filters, reducing the amount of data scanned. This combination directly addresses the query pattern of date-range filtering and product joins.

Answer

Round-robin distribution with no partitioning

Answer

Replicate the fact table on all distributions and partition on ProductID

Answer

Hash-distribute on Date with partitioning on ProductID

Question 13

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 14

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 15

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

Question 16

A financial services company needs to build a data pipeline that ingests daily transaction files from multiple sources. The pipeline must perform data quality checks, transform data using complex business logic, and load it into Azure Synapse Analytics. The transformations involve conditional branching (e.g., if a transaction amount exceeds a threshold, apply additional validation). The company wants to minimize coding effort and prefers a visual, configuration-based approach. Which Azure service should they use as the primary orchestration and transformation engine?

Accepted Answer

Azure Data Factory with Data Flows. Azure Data Factory with Data Flows is the correct choice because it provides a visual, configuration-based interface for both orchestration and transformation, including support for complex business logic like conditional branching (e.g., via conditional split transformations). It natively integrates with Azure Synapse Analytics for loading transformed data, minimizing coding effort compared to code-heavy alternatives.

Answer

Azure Databricks with notebooks

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Question 17

A retail company stores data about their products in different formats. Product ID and price are stored in a relational database table. Product descriptions are stored as plain text files. Product images are stored as JPEG files. Which of the following best categorizes these data types in order?

Accepted Answer

Structured, unstructured, unstructured. Product ID and price in a relational database table are structured because they follow a fixed schema with rows and columns. Product descriptions as plain text files have no predefined structure, making them unstructured. Product images as JPEG files are also unstructured because they consist of binary data without a schema. Thus, the order is structured, unstructured, unstructured, which matches option B.

Answer

Structured, semi-structured, unstructured

Answer

Structured, semi-structured, structured

Answer

Semi-structured, structured, unstructured

Question 18

A retail company maintains a database of customer information including CustomerID, Name, Address, and Phone. Each record follows the same fixed schema. This type of data is best described as:

Accepted Answer

Structured data. Structured data conforms to a fixed schema where each record has the same fields (CustomerID, Name, Address, Phone) and data types, making it ideal for relational database storage. This rigid, tabular format allows efficient querying using SQL and enforces consistency across all rows.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Relational data

Question 19

A ride-sharing company processes trip requests from customers. Each trip is recorded as a single transaction that updates the driver's status, calculates the fare, and logs the ride. At the end of each month, the company runs reports that aggregate millions of trips to determine average wait times and revenue per driver. Which pair of terms best describes these two distinct workloads?

Accepted Answer

OLTP and OLAP. The first workload (trip request processing) is a classic OLTP (Online Transaction Processing) system because each trip is a single, atomic transaction that updates driver status, calculates fare, and logs the ride in real time. The second workload (monthly aggregation reports) is OLAP (Online Analytical Processing) because it queries millions of historical trip records to compute averages and revenue summaries. These two patterns have fundamentally different data storage and query optimization requirements, making OLTP and OLAP the correct pair.

Answer

Batch processing and stream processing

Answer

ETL and ELT

Answer

Relational and non-relational

Question 20

A retail chain collects sales data from all its stores at the end of each business day by exporting CSV files from each store's database. The data is then combined and analyzed to generate daily sales reports. Which type of data processing does this describe?

Accepted Answer

Batch processing. This describes batch processing because sales data is collected from each store at the end of the business day, exported as CSV files, and then combined and analyzed in a scheduled, non-continuous manner. Batch processing is ideal for large volumes of data that are processed at periodic intervals, such as daily sales reports, rather than requiring immediate action.

Answer

Real-time processing

Answer

Stream processing

Answer

Interactive query