DP-900 Practice Test 30 — 15 Questions

Question 1

A company has an on-premises SQL Server database with a 1 TB 'Sales' table containing historical data. They want to move this table to Azure SQL Database with minimal downtime. The table is actively written to during business hours. Which approach should they use?

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

Accepted Answer

Use Azure Data Migration Service with continuous sync from on-premises to Azure SQL Database, then cut over. Azure Data Migration Service (DMS) with continuous sync is the correct approach because it supports online migration with minimal downtime. It uses transactional replication to keep the on-premises SQL Server database synchronized with Azure SQL Database while the source remains fully operational, allowing a controlled cutover with only seconds of downtime.

Answer

Use the Azure SQL Database migration wizard to perform an offline migration over the weekend

Answer

Export the table as a .bacpac file and import it into Azure SQL Database during off-hours

Answer

Use SQL Server Management Studio's 'Deploy Database to Azure SQL Database' wizard

Question 2

A mobile app stores user preferences in Azure Cosmos DB using the NoSQL API. The app frequently reads a single user's profile by user ID (the partition key). The development team wants the fastest possible read performance globally and is willing to accept that reads might not reflect the latest write immediately. Which consistency level should they choose to minimize read latency?

Accepted Answer

Eventual. Eventual consistency offers the lowest read latency because it allows reads to return data from any replica without waiting for confirmation that the write has been fully replicated. Since the app can tolerate stale reads (i.e., not reflecting the latest write immediately), Eventual consistency eliminates the synchronization overhead required by stronger models, making it the fastest choice for global read performance.

Answer

Strong

Answer

Bounded staleness

Answer

Session

Question 3

A logistics company needs to analyze GPS data from delivery trucks in real time to detect delays and reroute deliveries. The GPS data is streamed into Azure Event Hubs. They also need to combine this live data with static route information 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 designed for real-time stream processing, capable of ingesting data from Azure Event Hubs, applying SQL-like queries to detect delays, and joining live streams with static reference data from Azure SQL Database. This enables the logistics company to analyze GPS data as it arrives and trigger rerouting actions with minimal latency.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Synapse Pipelines

Question 4

A data analytics team stores sales transaction data in Parquet files in Azure Data Lake Storage Gen2. They want to run complex analytical queries that join this data with dimension tables stored in Azure Synapse Analytics dedicated SQL pool. The team prefers not to move or copy the data from the data lake. Which feature should they use to query the data lake data directly?

Accepted Answer

PolyBase external tables. PolyBase external tables in Azure Synapse Analytics dedicated SQL pool allow you to query data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) directly using T-SQL, without moving or copying the data. This is the correct feature because it enables complex analytical joins between the Parquet files in the data lake and the dimension tables in the dedicated SQL pool, leveraging the external table's ability to read Parquet format natively.

Answer

Azure Data Factory pipelines

Answer

Azure Stream Analytics

Answer

Azure Databricks notebooks

Question 5

A company stores customer data in a SQL Server table with fixed columns (CustomerID, Name, Email, SignupDate). The company also stores application logs as JSON documents and marketing images as JPEG files. Which data type describes the customer data?

Accepted Answer

Structured data. Customer data stored in a SQL Server table with fixed columns (CustomerID, Name, Email, SignupDate) follows a rigid schema where each row has the same set of columns with defined data types. This conforms to the relational model, making it structured data. Structured data is organized into rows and columns with a fixed schema, enabling efficient querying via SQL.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Relational data

Question 6

Your company is designing a data solution for IoT sensor data that arrives in high volume and must be stored for long-term analytics. The data is append-only and rarely updated. You need to choose a storage solution that balances cost and query performance for historical analysis. Which Azure data store should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, offering scalable, cost-effective storage for high-volume append-only data like IoT sensor logs. It supports both structured and unstructured data, integrates with analytics engines like Azure Synapse and Spark, and provides POSIX-compliant access control, making it ideal for long-term historical analysis at low cost.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 7

Refer to the exhibit. You are reviewing an ARM template for a new storage account. The storage account will store data that must be accessible from any Azure region and must be highly durable. Which change should you make to the template?

Accepted Answer

Change the SKU name to Standard_GRS. Standard_GRS (Geo-Redundant Storage) is the correct SKU because it replicates data synchronously three times within a primary region and asynchronously to a secondary region hundreds of miles away, ensuring high durability (11 nines) and accessibility from any Azure region via read-access (RA-GRS). The requirement for data to be accessible from any Azure region and highly durable aligns with GRS's geo-replication, whereas LRS only replicates within a single datacenter and Premium_LRS is for low-latency workloads, not geo-accessibility.

Answer

Set supportsHttpsTrafficOnly to false

Answer

Change the SKU name to Premium_LRS

Answer

Change the kind to BlobStorage

Question 8

A company is migrating an on-premises SQL Server database to Azure. The database uses SQL Server Integration Services (SSIS) packages for daily ETL processes. The company wants to minimize administrative overhead for patching and backup management, but needs to retain full control over instance-level configurations and support for SSIS. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including full support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime, while offloading patching and backup management to the platform. It also allows full control over instance-level configurations such as collation, CLR, and SQL Agent jobs, which are not available in Azure SQL Database.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure SQL Server on Azure Virtual Machines

Question 9

A data analyst receives a dataset containing customer order details stored in a CSV file, a JSON file with product reviews, and a folder of JPEG images of products. Which of the following correctly categorizes these data types from most structured to least structured?

Accepted Answer

CSV → JSON → JPEG. CSV files are highly structured with rows and columns defined by a schema, making them the most structured. JSON files are semi-structured, using key-value pairs and nested objects that allow flexibility but lack a fixed schema. JPEG images are unstructured binary data with no inherent schema, so the correct order from most to least structured is CSV → JSON → JPEG, making option C correct.

Answer

CSV → JPEG → JSON

Answer

JSON → CSV → JPEG

Answer

JPEG → JSON → CSV

Question 10

A multinational corporation uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage. The security team requires that access to specific columns containing personally identifiable information (PII) be restricted based on the user's role. Which feature should be implemented?

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics serverless SQL pool allows you to restrict access to specific columns containing PII based on the user's role or identity. By granting or denying SELECT permissions on individual columns, you can ensure that only authorized users see sensitive data while others see NULL or an error. This directly meets the requirement to restrict column access by role.

Answer

Row-level security (RLS)

Answer

Azure Purview data classification

Answer

Dynamic data masking

Question 11

Your company runs a sales analytics dashboard on Power BI that refreshes every hour from Azure Synapse Analytics. During peak hours, the dashboard refresh fails with a 'timeout' error. Which action should you take FIRST to resolve the issue?

Accepted Answer

Implement incremental refresh in Power BI to refresh only changed data.. Implementing incremental refresh in Power BI reduces the amount of data loaded during each refresh cycle, which directly addresses timeout errors by limiting the refresh to only changed or new data rather than the entire dataset. This is the most efficient first step to reduce refresh duration without changing the underlying architecture or data source connection mode.

Answer

Scale up the Azure Synapse dedicated SQL pool to handle more concurrent queries.

Answer

Configure the dashboard to use DirectQuery instead of Import mode.

Answer

Export the data to CSV files and load into Power BI from Azure Blob Storage.

Question 12

An e-commerce company runs a data pipeline that reads all orders from the previous hour, aggregates total sales per product category, and writes the results to a reporting database. The pipeline executes at the start of every hour. Which type of data processing workload does this pipeline represent?

Accepted Answer

Batch processing. This pipeline reads all orders from the previous hour, aggregates total sales per product category, and writes results to a reporting database at the start of every hour. This is a classic batch processing workload because data is collected over a fixed time window (one hour) and processed as a single, scheduled job, not continuously. Batch processing is ideal for non-real-time, high-volume data transformations like hourly sales aggregation.

Answer

Stream processing

Answer

Transactional processing

Answer

Interactive processing

Question 13

A social media application stores user profiles as JSON documents. Each profile has standard fields like userId, name, and email, but also optional fields such as education and work history. The application needs to query profiles by userId with low latency and also run SQL-like queries to find all profiles with a specific work history value. Which Azure Cosmos DB API should they choose?

Accepted Answer

SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports querying JSON documents with SQL-like syntax, enabling both low-latency point reads by userId and complex queries on nested fields like work history. It provides automatic indexing of all JSON properties, which ensures efficient execution of queries across optional fields without requiring schema management.

Answer

MongoDB API

Answer

Gremlin (Graph) API

Answer

Table API

Question 14

A global social media startup stores user profiles as JSON documents in Azure Cosmos DB. Their application frequently reads profiles by user ID and also runs queries to find users based on location or interests. The workload is read-heavy with high throughput requirements. The operations team notices that query performance degrades during peak hours. Which action would most effectively improve query performance?

Accepted Answer

Increase the provisioned throughput (RU/s). Increasing the provisioned throughput (RU/s) directly allocates more processing capacity to the Cosmos DB container, allowing it to handle higher request volumes and reduce throttling during peak hours. Since the workload is read-heavy and query performance degrades under high throughput demands, raising RU/s is the most effective and immediate action to improve performance.

Answer

Increase the number of containers

Answer

Choose a different API (e.g., switch from SQL API to MongoDB API)

Answer

Switch to a different Azure region

Question 15

A data engineer loads raw log files into a storage system. The structure of the data is interpreted at the time of reading, allowing queries to apply schema on the fly without preprocessing. This approach is best described as:

Accepted Answer

Schema-on-read. Schema-on-read means the data is stored in its raw, unstructured form, and the schema is applied dynamically when the data is queried. This is exactly what happens when raw log files are loaded into a storage system like Azure Data Lake Storage and queried with tools like Azure Synapse Serverless SQL or Apache Spark, which infer the schema at query time without requiring preprocessing.

Answer

Schema-on-write

Answer

Data warehouse

Answer

Data virtualization