DP-900 Practice Exam 62 — 20 Questions

Question 1

A data analyst needs to create a real-time dashboard in Power BI that displays sales data from an Azure SQL Database. The dashboard must update every 10 minutes without manual refresh. Which Power BI feature should they use?

Accepted Answer

DirectQuery mode. DirectQuery mode is correct because it allows Power BI to query the Azure SQL Database directly for each visual interaction, ensuring the dashboard reflects the latest data without requiring a manual refresh. Since the requirement is for updates every 10 minutes, DirectQuery can be configured to auto-refresh at that interval via the 'Automatic page refresh' setting, which sends T-SQL queries to the database on a timer. This avoids the latency and storage overhead of importing data, making it ideal for near-real-time monitoring.

Answer

Scheduled refresh with Import mode

Answer

Streaming datasets

Answer

Paginated reports

Question 2

A company wants to build a data lake on Azure for storing structured, semi-structured, and unstructured data. The solution must support fast queries on structured data without moving data to a separate store. Which Azure service should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, enabling both a data lake for unstructured and semi-structured data and fast, SQL-like queries on structured data using Azure Synapse Analytics or PolyBase without moving data. It supports the ABFS (Azure Blob File System) driver for high-performance analytics and integrates directly with query engines like Apache Spark and Azure Synapse SQL.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 3

Refer to the exhibit. You execute the above T-SQL statements in Azure Synapse Analytics. What is the purpose of this code?

Accepted Answer

To create an external table that can query Parquet files stored in Azure Data Lake Storage Gen2.. The T-SQL code creates an external data source pointing to Azure Data Lake Storage Gen2, an external file format for Parquet, and an external table that references the Parquet files. This allows querying the Parquet files directly without importing them into the database, which is the definition of an external table in Azure Synapse Analytics.

Answer

To create a view over the Parquet files.

Answer

To import data from Parquet files into a permanent table in Synapse.

Answer

To create a regular table in the Synapse database.

Question 4

Which THREE components are required to implement a modern data warehouse architecture (medallion architecture) on Azure?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is required because it provides the scalable, hierarchical storage foundation for the medallion architecture's bronze, silver, and gold layers. It combines blob storage with a hierarchical namespace, enabling efficient data ingestion and transformation at petabyte scale.

Answer

Azure Analysis Services

Answer

Azure DevOps

Question 5

Refer to the exhibit. You have an Azure Data Factory pipeline definition as shown. The pipeline fails with a 'Source not found' error. The BlobInputDataset points to a container that exists. What is the most likely cause?

Accepted Answer

The dataset's file path is incorrect or no files match the pattern.. The 'Source not found' error in Azure Data Factory indicates that the source dataset cannot locate the specified file or blob. Since the container exists, the most likely cause is that the file path defined in the dataset is incorrect or that no files match the specified pattern (e.g., wildcard or prefix). This is a common configuration issue when the dataset's folder path or file name does not correspond to the actual blob location.

Answer

The Azure Blob Storage container is empty.

Answer

The Azure Data Factory managed identity does not have access to the storage account.

Answer

The SQL sink database does not exist.

Question 6

Which TWO Azure services can be used to perform large-scale data transformation and processing in a serverless manner?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (Option B) is correct because it allows you to run T-SQL queries over data stored in Azure Data Lake or Blob Storage without provisioning any dedicated compute resources, paying only for the data processed. Azure Data Factory (Option C) is correct because it provides a serverless orchestration and data integration service that can execute data transformation activities (like Mapping Data Flows) at scale without managing underlying infrastructure.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

Question 7

A retail company uses Azure Data Lake Storage Gen2 to store raw clickstream data. They need to process this data using Azure Databricks to create hourly aggregated reports. The data pipeline must minimize costs while meeting a five-minute processing SLA. What is the most cost-effective compute option?

Accepted Answer

Use job clusters with pool-based allocation. Job clusters with pool-based allocation are the most cost-effective compute option for this scenario because job clusters are ephemeral—they start only when a job runs and terminate automatically after completion, eliminating idle costs. Pool-based allocation further reduces startup latency by maintaining a warm pool of pre-initialized VMs, enabling the pipeline to meet the five-minute SLA without paying for always-on compute.

Answer

Use interactive clusters with autoscaling

Answer

Use Azure Synapse Serverless SQL pools

Answer

Provision a dedicated SQL pool in Azure Synapse

Question 8

You are designing a solution to store IoT device telemetry data. Each message is a small JSON payload (1-2 KB). The data is written once and read frequently for real-time dashboards. Which Azure data store should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that offers single-digit millisecond read and write latencies at any scale, making it ideal for real-time dashboards consuming IoT telemetry. Its support for JSON documents natively aligns with the small JSON payloads, and its ability to handle high-throughput writes (once) and low-latency reads (frequently) without schema management fits the workload perfectly.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 9

You need to upload a 500 GB file to Azure Blob Storage. The network connection is unreliable. Which feature should you use to ensure the upload completes successfully?

Accepted Answer

Use the Put Block and Put Block List operations with a block size that handles retries.. Option B is correct because the Put Block and Put Block List operations allow you to upload a large file as a series of individual blocks. Each block can be retried independently if the network fails, and the final Put Block List assembles the blocks into a single blob. This block-level retry mechanism ensures the upload completes despite an unreliable connection.

Answer

Configure a lifecycle management policy to tier the file.

Answer

Use Azure File Sync to synchronize the file to the cloud.

Answer

Use AzCopy with the /Z parameter to resume the upload.

Question 10

A company uses Azure Table storage to store session state for a web application. They notice that read latency increases during peak hours. Which design change should they implement to reduce latency?

Accepted Answer

Use a partition key that distributes load evenly, such as UserID. Option D is correct because Azure Table storage partitions data based on the partition key. Using a partition key that distributes load evenly, such as UserID, ensures that read requests are spread across multiple partition servers, preventing hot partitions and reducing latency during peak hours.

Answer

Change to Azure Blob storage

Answer

Store large attributes in a separate table

Answer

Switch to Azure Queue storage

Question 11

A company stores terabytes of historical log data in Azure Blob Storage. The data is rarely accessed but must be retained for 10 years for compliance. The company wants to minimize storage costs. Which storage tier should you use?

Accepted Answer

Archive tier. The Archive tier is the correct choice because it is designed for data that is rarely accessed and has a flexible retrieval latency (hours), making it ideal for long-term retention of historical logs. It offers the lowest storage cost among Azure Blob Storage tiers, which directly minimizes costs for data that must be kept for 10 years but is seldom read.

Answer

Cool tier

Answer

Hot tier

Answer

Premium tier

Question 12

A company needs to migrate a large on-premises SQL Server database to Azure. The migration must have minimal downtime and support ongoing replication. Which Azure service should they use?

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting ongoing replication from SQL Server to Azure SQL Database. It uses the Data Migration Assistant (DMA) for assessment and the Azure DMS for continuous sync, enabling near-zero downtime during cutover.

Answer

Azure Data Box

Answer

Azure Data Factory

Answer

Azure SQL Database

Question 13

A financial services company is building a real-time fraud detection system. Transactions are streamed from multiple sources into Azure Event Hubs. The system must run a trained machine learning model (scored in near real-time) to flag suspicious transactions. The model is a Python pickle file that needs to be deployed as a web service with low latency (under 100 ms per prediction). The data engineering team wants to use a serverless compute option to run the scoring logic, and the solution must integrate with Azure Stream Analytics for alerting. Which Azure service should you use to deploy the model?

Accepted Answer

Azure Machine Learning managed online endpoint. Azure Machine Learning managed online endpoints are the correct choice because they are designed for deploying trained models (including Python pickle files) as low-latency web services (under 100 ms per prediction) with serverless compute. They natively integrate with Azure Stream Analytics for alerting, allowing real-time scoring of streaming transactions from Event Hubs without managing infrastructure.

Answer

Azure Functions

Answer

Azure Kubernetes Service (AKS)

Answer

Azure Databricks

Question 14

A company uses Azure Data Lake Storage Gen2 to store analytics data. The security team requires that all data access be audited, including read, write, and delete operations. Which feature should you enable?

Accepted Answer

Enable Storage Analytics logging.. Storage Analytics logging captures detailed information about successful and failed requests to a storage account, including read, write, and delete operations. This log data can be used for auditing and analysis, meeting the security team's requirement to audit all data access. The logs are stored in a $logs container within the same storage account.

Answer

Enable Microsoft Purview.

Answer

Enable Azure Monitor for the storage account.

Answer

Enable Microsoft Defender for Cloud.

Question 15

Which THREE considerations are important when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent. Option A is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance, enabling native job scheduling and automation, whereas Azure SQL Database does not include SQL Server Agent and requires alternative solutions like Elastic Jobs or Azure Automation. Option B is correct because Azure SQL Managed Instance supports databases up to 16 TB, while Azure SQL Database (single database) has a maximum size of 4 TB for the Hyperscale service tier, making Managed Instance necessary for databases exceeding 4 TB in non-Hyperscale tiers. Option E is correct because Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, allowing lift-and-shift migrations with minimal or no application changes, whereas Azure SQL Database often requires schema and code modifications due to differences in features like CLR, Service Broker, or cross-database queries.

Answer

Requirement for elastic pools

Answer

Need for automated backups

Question 16

A company uses Azure SQL Database with geo-replication for disaster recovery. During a regional outage, they manually failover to the secondary region. After the primary region is restored, they need to re-establish geo-replication with minimal downtime. What should they do?

Accepted Answer

Initiate a planned failover to switch back to the original region. After a manual failover to the secondary region, the original primary becomes a secondary database. To re-establish geo-replication with minimal downtime, you should initiate a planned failover (also called a graceful failover) to switch back to the original region. This operation reverses the roles without data loss and avoids the need to reseed the database, keeping downtime to seconds.

Answer

Drop the secondary database and create a new one

Answer

Delete the geo-replication link and create a new one

Answer

Manually swap the roles of the primary and secondary

Question 17

You need to run a complex T-SQL query that joins tables from Azure SQL Database and Azure SQL Managed Instance in a single query. Which feature should you use?

Accepted Answer

Elastic Query. Elastic Query (Option D) is the correct feature because it enables running complex T-SQL queries that span multiple databases in Azure SQL Database and Azure SQL Managed Instance, allowing you to join tables across these services in a single query. It uses a logical database as a shard map manager to distribute queries across remote databases, supporting cross-instance and cross-database queries without data movement.

Answer

Database Mail

Answer

PolyBase

Answer

Linked server

Question 18

A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?

Accepted Answer

Use a query hint to force the index. Option B is correct because query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.

Answer

Update statistics on the table

Answer

Rebuild the index

Answer

Enable Query Store

Question 19

You are designing a solution to store relational data that requires support for graph relationships and JSON queries. Which Azure service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports graph relationships through graph tables (NODE and EDGE tables) and JSON queries via built-in JSON functions like JSON_VALUE, JSON_QUERY, and OPENJSON. This makes it ideal for storing relational data that also needs to handle graph traversals and semi-structured JSON data without requiring a separate service.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Database for PostgreSQL

Question 20

Refer to the exhibit. An administrator deploys this Azure Policy assignment. What is the most likely effect on storage account 'storage1'?

Accepted Answer

Public network access will be denied.. The Azure Policy assignment shown in the exhibit denies the creation or update of storage accounts that do not have public network access disabled. Since 'storage1' is subject to this policy, the policy will enforce the 'Deny' effect, preventing any configuration that allows public network access. If 'storage1' already exists and is compliant, it remains; if it is non-compliant, the policy will block changes that would enable public access, effectively denying public network access.

Answer

The storage account will be deleted.

Answer

Firewall rules will be added.

Answer

Soft Delete will be enabled.