DP-900 Practice Exam 64 — 20 Questions

Question 1

Your company is developing a new analytics solution to track customer sentiment from social media feeds. The data arrives as a continuous stream of JSON messages. The solution must process the data in near real-time, enrich it with customer profile data stored in Azure Cosmos DB, and then store the results in a data lake for historical analysis. The team wants to use a low-code approach for the data processing logic. You are considering the following architectures: A) Use Azure Event Hubs to ingest the stream, Azure Stream Analytics to process and enrich the data using Cosmos DB as a reference data source, and output to Azure Data Lake Storage Gen2. B) Use Azure IoT Hub to ingest the stream, Azure Databricks to process the data, and write to Azure Blob Storage. C) Use Azure Event Hubs to ingest the stream, Azure Functions to process each message, query Cosmos DB for enrichment, and write to Azure Data Lake Storage Gen2. D) Use Azure Event Hubs to ingest the stream, Azure Data Factory to execute a mapping data flow for enrichment, and write to Azure Data Lake Storage Gen2. Which architecture best meets the requirements of near real-time processing, enrichment, and low-code?

Accepted Answer

Option A. Option A is correct because Azure Stream Analytics provides a low-code, SQL-based approach for near real-time processing, and it can natively enrich streaming data by using Azure Cosmos DB as a reference data source via a JOIN operation. The output is directly written to Azure Data Lake Storage Gen2, meeting all requirements without custom code.

Answer

Option C

Answer

Option D

Answer

Option B

Question 2

A startup is building a new mobile app that will track user fitness activities. They need a relational database to store user profiles, activity logs, and goals. The database must be easy to set up, require minimal administration, and automatically scale during peak usage. The startup has a limited budget and prefers a consumption-based pricing model. Which Azure service should they choose?

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it provides a consumption-based pricing model that automatically pauses during inactivity and scales compute resources based on demand, requiring minimal administration. This aligns perfectly with the startup's need for easy setup, minimal administration, automatic scaling during peak usage, and a limited budget.

Answer

Azure Database for MySQL serverless

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Question 3

A small business wants to use Azure to store and analyze customer feedback from surveys. The surveys are collected via a web app and stored as JSON files. The business needs to run SQL-based queries on the data and generate reports in Power BI. They have a limited budget and prefer a serverless option to minimize management overhead. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it allows you to query JSON files directly from Azure Data Lake Storage or Blob Storage using standard T-SQL, without provisioning any infrastructure. It is serverless (pay-per-query), supports SQL-based queries, and integrates seamlessly with Power BI for reporting, making it ideal for a small business with a limited budget and minimal management overhead.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

Question 4

Your organization is migrating on-premises SQL Server databases to Azure. The databases include a mission-critical OLTP system that requires high availability with automatic failover and a reporting database that is used for read-only queries. You need to choose the appropriate Azure SQL deployment options for each workload. The OLTP system must have a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. The reporting database should be cost-effective and can tolerate up to 5 minutes of data loss. What should you recommend?

Accepted Answer

Use Azure SQL Database Managed Instance with a failover group for OLTP, and use a read-only replica of the Managed Instance for reporting.. Option C is correct because Azure SQL Database Managed Instance supports failover groups that provide automatic failover across regions with an RPO of less than 5 seconds and an RTO of less than 30 seconds, meeting the OLTP requirements. The read-only replica of the Managed Instance can be used for reporting queries without impacting the primary OLTP workload, and it is cost-effective as it does not require a separate database instance.

Answer

Use SQL Server on Azure Virtual Machines with Always On Availability Groups for both workloads.

Answer

Use Azure SQL Database Hyperscale for OLTP and Azure SQL Database serverless for reporting.

Answer

Use Azure SQL Database single database with active geo-replication for both workloads.

Question 5

Your company is implementing a data governance solution using Microsoft Purview. The data catalog must automatically scan and classify sensitive data in Azure SQL Database, Azure Synapse Analytics, and Amazon S3. The company uses Microsoft Entra ID for identity management. You need to ensure that the Purview managed identity can authenticate to these data sources. Which authentication method should you configure for the Amazon S3 connection?

Accepted Answer

AWS IAM authentication. Amazon S3 is an external cloud storage service that does not support Microsoft Entra ID, SQL Authentication, or Windows Authentication. To authenticate Purview's managed identity to S3, you must configure AWS IAM authentication, which allows Purview to assume an IAM role with permissions to read the S3 bucket metadata and data for scanning and classification.

Answer

SQL Authentication

Answer

Windows Authentication

Answer

Microsoft Entra ID authentication

Question 6

Your organization is migrating its on-premises SQL Server databases to Azure. The databases include a mix of operational (OLTP) and analytical (OLAP) workloads. For the OLTP databases, you need high availability and automated failover to a secondary region. For the OLAP databases, you need to support large-scale analytic queries with columnstore indexes and the ability to pause compute to save costs. Which Azure SQL deployment options should you choose for each workload type?

Accepted Answer

Azure SQL Database (geo-replication) for OLTP; Azure Synapse Analytics (dedicated SQL pool) for OLAP. Option D is correct because Azure SQL Database with active geo-replication provides high availability and automated failover to a secondary region for OLTP workloads, while Azure Synapse Analytics (dedicated SQL pool) supports large-scale analytic queries with columnstore indexes and allows pausing compute to save costs, meeting the OLAP requirements.

Answer

Azure SQL Managed Instance for both

Answer

SQL Server on Azure Virtual Machines for both

Answer

Azure SQL Database Hyperscale for OLTP; Azure SQL Database Serverless for OLAP

Question 7

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 to store customer transaction data. They also use Azure Databricks for data transformation and Azure Synapse Serverless SQL pool for ad-hoc queries. Recently, the data lake has grown to 10 TB, and query performance in Synapse Serverless has degraded significantly. Users complain that queries that used to take seconds now take minutes. You need to improve query performance without moving data to a dedicated SQL pool. The data is stored in Parquet format, partitioned by date. You notice that the queries often filter on CustomerID and Date. Current queries scan all partitions even when only a few days are needed. What is the most effective solution to improve performance?

Accepted Answer

Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each. Option C is correct because repartitioning the Parquet files by both date and CustomerID enables partition pruning in Azure Synapse Serverless SQL pool. When queries filter on CustomerID and Date, the engine can skip irrelevant partitions entirely, drastically reducing the amount of data scanned. Optimizing file sizes to around 1 GB ensures efficient parallelism and avoids the overhead of many small files, which degrades performance in a serverless environment.

Answer

Create materialized views in the Serverless SQL database on the partitioned data

Answer

Convert all Parquet files to CSV and use row-level security to limit data access

Answer

Increase the service level of the Synapse workspace to improve query concurrency

Question 8

Your company operates a retail analytics platform. Data from point-of-sale systems is ingested in real time into Azure Event Hubs. The data is then consumed by an Azure Stream Analytics job that aggregates sales by store and product every minute, writing results to Azure SQL Database. The business now requires a historical trend analysis capability that can query the last three years of sales data with sub-second response times, but the SQL Database is already experiencing performance issues due to high write volume. You need to redesign the serving layer to support both real-time dashboards (seconds latency) and historical analytics (sub-second queries on years of data) without impacting write performance. What should you do?

Accepted Answer

Add Azure Cosmos DB as an additional output from Azure Stream Analytics for real-time dashboards. Enable Azure Synapse Link on the Cosmos DB container to continuously replicate data to Azure Synapse Analytics for historical queries. Remove the real-time dashboard load from Azure SQL Database.. Option C is correct. Azure Stream Analytics can output to multiple sinks simultaneously. By adding Azure Cosmos DB as an additional output for real-time dashboards (low latency, high throughput), and using Azure Synapse Link to continuously replicate Cosmos DB data to Azure Synapse Analytics for historical queries, you separate workloads. Option A uses Azure Analysis Services but it is not designed for sub-second queries on raw historical data. Option B uses Azure Data Lake Storage with Databricks, but batch processing would not meet the real-time requirement and adds latency. Option D uses Azure SQL Database with indexing improvements, but the write volume already causes issues, and SQL DB is not ideal for sub-second queries on years of data without partitioning and scaling.

Answer

Modify the Azure Stream Analytics job to write to Azure Data Lake Storage Gen2, then use Azure Databricks to run batch transformations and load into a separate Azure Synapse Analytics dedicated SQL pool for historical queries. Keep the real-time dashboard connected to Azure SQL Database.

Answer

Add Azure Analysis Services on top of Azure SQL Database and create a cube for historical queries. Use Azure Stream Analytics to write to Analysis Services directly.

Answer

Partition the existing Azure SQL Database and add columnstore indexes to improve query performance. Keep the current architecture and increase the DTU/vCore limit to handle both workloads.

Question 9

Your company runs a global e-commerce platform on Azure SQL Database. The platform experiences heavy read traffic on product catalog and inventory tables. You need to reduce read latency for users in different geographic regions while keeping write latency low. The solution must be cost-effective and require minimal application changes. Current architecture: a single Azure SQL Database in West US. You have budget for additional Azure resources. What should you implement?

Accepted Answer

Configure Active Geo-Replication to create readable secondaries in regions where users are located.. Option B is correct because Active Geo-Replication creates readable secondaries in multiple regions, allowing local reads and single-region writes. Option A (Read Scale-Out) works only in the same region. Option C (Cosmos DB) would require application rewrite. Option D (Traffic Manager) does not replicate data.

Answer

Migrate the database to Azure Cosmos DB with multi-region writes.

Answer

Use Azure Traffic Manager to route read requests to the primary database in West US.

Answer

Enable Read Scale-Out on the existing database and configure application to use the read-only endpoint.

Question 10

You are the database administrator for a large financial institution migrating their core banking system to Azure. The system uses SQL Server with many stored procedures, triggers, and CLR assemblies. The database is 2 TB and growing. The migration must minimize application changes and support high availability with automatic failover. You need to select an Azure relational database service. What should you choose?

Accepted Answer

Azure SQL Managed Instance. Option C is correct because Azure SQL Managed Instance provides near 100% compatibility with SQL Server, supports CLR, and offers high availability with automatic failover. Option A (Azure SQL Database) lacks support for CLR and cross-database queries. Option B (SQL Server on Azure VM) requires manual high availability setup. Option D (Azure Database for PostgreSQL) requires significant application changes.

Answer

Azure SQL Database

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Question 11

A company uses Azure SQL Database for a customer relationship management (CRM) system. The database is deployed in the East US region. The company has customers worldwide who access the CRM via a web application. Currently, all read and write traffic goes to the primary database in East US, causing high latency for users in Europe and Asia. The company needs to improve read performance for global users while maintaining strong consistency for writes. They also want to minimize cost. Which solution should you recommend?

Accepted Answer

Configure active geo-replication with readable secondary replicas in West Europe and Southeast Asia. Option B is correct because active geo-replication provides readable secondaries in other regions for read traffic, while writes go to the primary, maintaining consistency. Option A is wrong because read replicas in the same region do not help global users. Option C is wrong because Azure Traffic Manager does not improve database read latency. Option D is wrong because Azure Redis Cache adds cost and complexity and does not solve the read latency for all queries.

Answer

Implement Azure Redis Cache to cache frequently accessed data

Answer

Use Azure SQL Database with geo-redundant storage and enable read-scale

Answer

Create read replicas in the same region and use Azure Traffic Manager to route read traffic

Question 12

Your organization is migrating a large on-premises SQL Server database to Azure SQL Database. The database is used by a critical line-of-business application that requires near-zero downtime during migration. The application uses a mix of read and write operations, and you need to minimize the cutover time. The database is about 2 TB in size. You plan to use Azure Database Migration Service (DMS) for the migration. The source SQL Server is version 2019 Enterprise Edition. The target is a Business Critical tier Azure SQL Database. You need to choose the appropriate migration method and configuration. Which of the following actions should you take?

Accepted Answer

Use online migration mode with DMS on Premium tier. Option C is correct because using online migration mode with continuous sync allows near-zero downtime by replicating changes from source to target, and using the Premium tier for DMS provides better performance for large databases. Option A is wrong because offline migration causes downtime. Option B is wrong because using Basic tier DMS is too slow for 2 TB. Option D is wrong because migrating to Managed Instance is not necessary and may not align with requirements.

Answer

Use offline migration mode with DMS on Standard tier

Answer

Use online migration mode with DMS on Basic tier

Answer

Use offline migration mode with DMS on Premium tier, then manually copy remaining changes

Question 13

A healthcare organization needs to store medical imaging files (DICOM) that average 50 MB each, with some up to 500 MB. The images must be retained for 10 years for regulatory compliance. During the first year, images are accessed frequently by radiologists. After one year, access drops to once or twice a year. The organization also needs to support application-level encryption with customer-managed keys. The storage solution must be HIPAA eligible and provide high durability. You need to recommend a storage solution. Which Azure service and configuration should you choose?

Accepted Answer

Azure Blob Storage in the Hot tier with a lifecycle management policy to move blobs to Cool after 30 days and to Archive after 1 year, with customer-managed encryption keys.. Option A is correct because Azure Blob Storage is HIPAA eligible, supports large blobs, and offers lifecycle management to move blobs from Hot to Cool to Archive tiers automatically. Customer-managed keys are supported. Option B is wrong because Azure Files has a file size limit of 4 TiB and is not optimized for DICOM images. Option C is wrong because Azure NetApp Files is expensive and not necessary. Option D is wrong because Azure Cosmos DB is not designed for large binary files.

Answer

Azure Cosmos DB with a large document size limit, storing images as base64-encoded strings.

Answer

Azure NetApp Files with a capacity pool and export policy, using snapshots for backup.

Answer

Azure Files with Azure File Sync to local servers, storing all images in the Premium tier for fast access.

Question 14

A company uses Azure Cosmos DB with the Cassandra API for a time-series telemetry application. The data model uses a composite primary key (device_id, timestamp). The application queries telemetry for a specific device within a time range. Recently, query performance has degraded as data volume grows. The RU consumption per query is high. Which action should they take to improve performance and reduce RU cost?

Accepted Answer

Change the clustering order to descending on timestamp.. Changing the clustering order to descending on timestamp (option C) allows the most recent data to be retrieved first without scanning all rows. Option A (increasing RU) does not improve efficiency. Option B (adding a secondary index) may increase RU. Option D (changing partition key to timestamp) would scatter device data across partitions, hurting queries by device.

Answer

Change the partition key to timestamp.

Answer

Increase the provisioned RU/s on the container.

Answer

Add a secondary index on the timestamp column.

Question 15

A company stores user profile data in Azure Blob Storage as JSON files. Each file represents one user. They need to provide real-time search capabilities on user attributes like name, email, and location. The search must support partial matches and return results within 500 ms. The data volume is 10 TB and grows by 1 GB daily. They have a limited budget and want to minimize operational overhead. Which Azure solution should they choose?

Accepted Answer

Use Azure Cognitive Search to index the JSON files and provide search capabilities.. Azure Cognitive Search (option A) is a managed search service that indexes JSON data from Blob Storage and supports partial matches with low latency. Option B (Azure Cosmos DB) requires data migration and may not support partial match efficiently. Option C (Azure SQL Database) requires importing data and schema changes. Option D (Azure Data Lake Analytics) is for batch processing, not real-time search.

Answer

Import the JSON files into Azure Cosmos DB and use the SQL API to search.

Answer

Use Azure Data Lake Analytics with U-SQL to query the files.

Answer

Load the data into Azure SQL Database and create full-text indexes.

Question 16

A company uses Azure Cosmos DB with the MongoDB API for a customer profile service. The service handles 10,000 writes per second and 50,000 reads per second. The data is 1 KB per document. The company needs to reduce read latency for frequently accessed customers and minimize RU consumption. Currently, the service reads the entire document for every request. They decide to implement a materialized view pattern using Azure Cosmos DB change feed and a separate container. Which additional step should they take to optimize read performance and cost?

Accepted Answer

Create a materialized view container with a partition key optimized for the read queries.. Using a separate container with a different partition key (option B) allows the materialized view to be optimized for the read pattern, reducing RU cost and latency. Option A (increasing RU) does not address the root cause. Option C (adding TTL) would delete data, not improve reads. Option D (using stored procedures) is for transactional logic, not read optimization.

Answer

Use stored procedures to aggregate data on read.

Answer

Increase the provisioned RU/s on the source container.

Answer

Enable Time-to-Live (TTL) on the source container to automatically expire old data.

Question 17

A company stores log files in Azure Blob Storage. Each log file is a CSV file ranging from 100 MB to 1 GB. They need to query the logs using SQL queries and plan to use Azure Synapse Serverless SQL. The logs are stored in a container named 'logs' with a folder structure like 'year=2023/month=01/day=01/logfile.csv'. They want to optimize query performance and reduce data scanned. Which partitioning strategy should they implement?

Accepted Answer

Use the existing folder structure with year/month/day partitions.. Partitioning by year, month, and day (option A) using folder structure allows Synapse to perform partition elimination, reducing data scanned. Option B (single large file) is not partitioned. Option C (parquet format) is a columnar format that improves performance but does not address partition elimination. Option D (random GUID names) prevents partition elimination.

Answer

Convert logs to Parquet format and store in a single folder.

Answer

Consolidate all logs into a single large CSV file.

Answer

Use random file names to distribute load.

Question 18

A company uses Azure Table Storage to store customer session data. Each session has a PartitionKey (CustomerId) and RowKey (SessionId). They need to retrieve all sessions for a specific customer quickly. Which query design will be most efficient?

Accepted Answer

Query with PartitionKey equal to the specific CustomerId.. Using PartitionKey filter (option A) allows Table Storage to directly access the partition, making it efficient. Option B (filter by RowKey) requires scanning all partitions. Option C (selecting all entities) scans the entire table. Option D (using a secondary index) is not available in standard Table Storage.

Answer

Select all entities and filter client-side.

Answer

Create a secondary index on RowKey.

Answer

Query with RowKey equal to a specific SessionId.

Question 19

You are a data engineer for a global gaming company. The company collects telemetry data from millions of players in real time. Each telemetry event is a JSON object containing player ID, game session ID, event type, timestamp, and a payload of up to 5 KB. The data must be stored for 90 days for real-time analytics and then moved to long-term storage for 5 years for historical analysis. The real-time analytics require querying by player ID and event type with sub-second latency. The long-term storage must be cost-effective and support batch analytics. You need to design a storage solution. Which combination of Azure services should you use to meet these requirements?

Accepted Answer

Ingest telemetry into Azure Cosmos DB with TTL set to 90 days, then use Azure Data Factory to copy expired data to Azure Blob Storage.. Option A is correct because Azure Cosmos DB provides sub-second query latency on player ID and event type, with a Time-to-Live (TTL) to automatically expire data after 90 days. Then, you can use Azure Data Factory to copy the expired data to Azure Blob Storage (Cool or Archive) for cost-effective long-term storage. Option B is wrong because Azure Event Hubs is for ingestion, not storage. Option C is wrong because Azure Stream Analytics is for processing, not storage. Option D is wrong because Azure Data Lake Storage is for analytics, but for real-time sub-second queries, Cosmos DB is better.

Answer

Store all telemetry in Azure Data Lake Storage Gen2 with a hierarchical namespace, and use Azure Synapse Serverless SQL for real-time queries.

Answer

Ingest telemetry into Azure Stream Analytics, output to Azure SQL Database for 90 days, then export to Azure Data Lake Storage.

Answer

Ingest telemetry into Azure Event Hubs and then store in Azure Blob Storage with a lifecycle management policy to delete after 90 days.

Question 20

You are the database administrator for a global e-commerce company that uses Azure SQL Database. The company has a single database in the West US region. The database hosts a table named Orders with 500 million rows. The table is clustered on OrderId (uniqueidentifier). The most critical query is a daily report that aggregates total sales by product category for the previous day. This query currently takes over 30 minutes to run and causes performance degradation on the primary database. The report must be available by 6:00 AM local time each day, and the data must be no more than 24 hours old. You need to design a solution that minimizes impact on the transactional workload and improves report query performance. What should you do?

Accepted Answer

Create a readable secondary replica in the same region and run the report query against the replica. Option D is correct because creating a read replica offloads the reporting query from the primary and allows indexing specifically for the report. Option A is wrong because adding indexes on the primary may degrade write performance. Option B is wrong because partitioning alone won't isolate the reporting workload. Option C is wrong because nonclustered columnstore index on the primary still impacts transactional workload.

Answer

Partition the Orders table by OrderDate and create a partitioned view for the report

Answer

Add a nonclustered columnstore index on the primary database for the Orders table

Answer

Create a nonclustered index on the primary database covering the columns used in the report query