DP-900 Practice Test 42 — 15 Questions

Question 1

Refer to the exhibit. An Azure Data Factory pipeline JSON is shown. What does this pipeline do?

Accepted Answer

Copies filtered sales data from on-premises to Azure SQL Database. The pipeline uses a Copy activity with a source dataset pointing to an on-premises SQL Server (via a self-hosted integration runtime) and a sink dataset pointing to an Azure SQL Database. The source query includes a WHERE clause filtering sales data by a date range, so only filtered data is copied. This matches option C.

Answer

Copies sales data from Azure SQL Database to on-premises

Answer

Copies all sales data from on-premises to Azure SQL Database

Answer

Copies sales data from on-premises to Azure Data Lake Storage

Question 2

A company is designing a new application that will store customer orders in a relational database on Azure. The data includes order IDs, customer IDs, product IDs, quantities, and order dates. The application needs to support complex queries that join multiple tables and enforce referential integrity. Which Azure service should the company use?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it is a fully managed relational database service that supports complex queries with JOINs and enforces referential integrity. In contrast, Azure Cosmos DB is a NoSQL database, Azure Table Storage is a key-value store, and Azure Blob Storage is for unstructured data, none of which are suitable for relational requirements.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 3

A company must archive customer correspondence PDFs that are rarely accessed but must be retained for seven years. The documents must be available for read within seconds if requested. Which Azure Blob Storage access tier should be used to minimize storage cost while meeting the availability requirement?

Accepted Answer

Cool. The Cool tier is optimal because it balances low storage cost with high availability for data that is infrequently accessed but must be retrievable within seconds. It offers the same low-latency retrieval as the Hot tier (milliseconds) but at a lower storage price, making it ideal for archived correspondence that still requires immediate read access.

Answer

Hot

Answer

Archive

Answer

Premium

Question 4

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Need for SQL Server Agent jobs. SQL Managed Instance supports SQL Server Agent jobs, which SQL Database does not. Option C is correct because SQL Managed Instance supports cross-database queries, while SQL Database does not. Option D is correct because SQL Database offers automatic scaling options like serverless compute, whereas SQL Managed Instance requires manual scaling. Option A is incorrect because both services support Microsoft Entra ID authentication. Option E is incorrect because VNet integration is a feature of SQL Managed Instance, not a primary differentiator, and both can be integrated into a VNet in different ways.

Answer

Support for Microsoft Entra ID authentication

Answer

Requirement for VNet integration

Question 5

A company plans to migrate an on-premises SQL Server database to Azure. The database currently uses SQL Server Agent jobs for scheduled maintenance tasks, cross-database queries, and query store for performance tuning. The database size is 500 GB and needs to scale to 10 TB eventually. They want a managed service that requires minimal application changes. Which Azure relational database service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, cross-database queries, and Query Store, while being a fully managed PaaS service. It allows scaling up to 10 TB (up to 16 TB with some configurations) with minimal application changes, as it uses the same T-SQL surface area and network configuration (VNet) as on-premises SQL Server.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database Hyperscale

Answer

Azure Database for SQL Server

Question 6

A company is designing a multi-tenant SaaS application. Each tenant has its own relational database, but the total number of tenants is expected to grow rapidly. The company wants to manage all databases efficiently and optimize costs by sharing resources among tenants with low usage. What should they use?

Accepted Answer

Azure SQL Database elastic pools. Elastic pools in Azure SQL Database allow sharing resources among multiple databases, optimizing cost for low-usage tenants in a multi-tenant SaaS application. Option A (SQL Server on Azure Virtual Machines) requires manual resource management and does not offer elastic pools. Option B (Azure SQL Managed Instance) is designed for single large databases, not for sharing resources across many databases. Option C (Azure Database for MySQL) does not have elastic pools; elastic pools are a feature of Azure SQL Database.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure Database for MySQL with elastic pools

Question 7

A data analyst needs to create a real-time dashboard in Power BI that refreshes every second from an Azure Stream Analytics job. Which Power BI feature should they use?

Accepted Answer

Streaming dataset. B is correct because a streaming dataset in Power BI is designed to ingest real-time data from sources like Azure Stream Analytics and automatically update visuals as new data arrives. This feature supports push-based updates at sub-second intervals, making it ideal for a dashboard that refreshes every second without requiring manual or scheduled refresh cycles.

Answer

Scheduled refresh

Answer

DirectQuery

Answer

Import mode

Question 8

A financial services company stores petabytes of transaction data in Parquet format in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and aggregate billions of rows, with results expected within seconds. The company wants to use a massively parallel processing (MPP) engine that supports T-SQL and can be paused to reduce costs during off-hours. They also need native integration with Azure Data Factory and Power BI. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL DW) is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling complex T-SQL queries on petabyte-scale Parquet data with results in seconds. It supports native T-SQL, can be paused to reduce costs during off-hours, and offers built-in integration with Azure Data Factory and Power BI through its SQL endpoints and linked service connectors.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure SQL Database

Question 9

A startup is building a mobile app that requires offline data synchronization. The app needs to store user-generated content locally on the device and sync with Azure when connectivity is available. Which Azure service should they use for the cloud backend?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it supports offline data synchronization through its Mobile Apps SDK, allowing mobile apps to store data locally and sync with Azure when connectivity is available. Azure Blob Storage (Option B) lacks built-in offline sync for mobile apps. Azure SQL Database (Option C) is relational and not optimized for offline sync scenarios. Azure Table Storage (Option D) does not provide robust offline sync capabilities.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 10

A company uses Azure Synapse Analytics to run a data warehouse. They need to load 500 GB of historical data from Azure Blob Storage into a staging table. They want the fastest load performance with minimal administrative overhead. Which method should they use?

Accepted Answer

Use PolyBase with the COPY INTO statement. PolyBase with the COPY INTO statement is the fastest method for loading large volumes of data into Azure Synapse Analytics because it leverages the Massively Parallel Processing (MPP) architecture to read data directly from Azure Blob Storage in parallel across all compute nodes, bypassing any single-node bottleneck. It also requires minimal administrative overhead as it is a native T-SQL command with automatic schema inference and no external tools or orchestration to manage.

Answer

Use SQL Server Integration Services (SSIS)

Answer

Use Azure Data Factory with Copy activity

Answer

Use the bcp utility

Question 11

A data analyst needs to create interactive dashboards and reports from data stored in Azure Synapse Analytics. Which tool should they use?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports from a wide range of data sources, including Azure Synapse Analytics. Power BI connects directly to Synapse SQL pools or serverless SQL endpoints using DirectQuery or import mode, enabling real-time visualizations and cross-filtering. This aligns with the requirement for interactive analytics, which is Power BI's core strength.

Answer

SQL Server Reporting Services (SSRS)

Answer

Microsoft Excel

Answer

Azure Data Studio

Question 12

A manufacturing company ingests real-time sensor data from factory equipment via Azure Event Hubs. The data is a continuous stream of measurements (sensorId, timestamp, value). Additionally, historical maintenance records are stored as CSV files in Azure Data Lake Storage Gen2. The operations team needs to join the streaming data with the historical records in near real-time to detect anomalies. They also need to run complex T-SQL queries on the combined dataset for ad-hoc analysis. Which Azure service should they use as the primary analytics platform?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest real-time streaming data from Azure Event Hubs via its built-in pipeline or Spark Structured Streaming, and simultaneously query historical CSV files in Azure Data Lake Storage Gen2 using serverless SQL or dedicated SQL pools. This allows the operations team to join streaming and batch data in near real-time for anomaly detection, and also run complex T-SQL queries for ad-hoc analysis, all within a single service.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure HDInsight with Spark

Question 13

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 14

A healthcare organization uses Azure SQL Database to store patient records. To comply with HIPAA regulations, they need to encrypt sensitive columns (e.g., Social Security numbers) at rest and control access to the encryption keys. Which feature should they use?

Accepted Answer

Always Encrypted. Always Encrypted. Always Encrypted is a feature designed to protect sensitive data, such as Social Security numbers, by encrypting it at rest and in transit, with the encryption keys stored outside of Azure SQL Database, providing client-side key management. This satisfies the HIPAA requirement for encrypting sensitive columns and controlling access to keys.

Option A: Dynamic Data Masking obfuscates data from non-privileged users but does not encrypt the data; it can be reversed by privileged users.

Option B: Row-Level Security restricts access to rows based on user characteristics but does not encrypt columns.

Option D: Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level encryption or client-side key control.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Question 15

A data engineer needs to process streaming data from IoT devices in near real-time and store the results in Azure Cosmos DB. Which Azure service should they use for the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for low-latency, near-real-time analytics on streaming data. It can ingest data from IoT devices via Event Hubs or IoT Hub, apply SQL-based transformations, and directly output the results to Azure Cosmos DB with millisecond latency, making it ideal for this scenario.

Answer

Azure Synapse Analytics

Answer

Azure Databricks

Answer

Azure Data Factory