DP-900 Describe core data concepts Practice Test 17 — 15 Questions

Question 1

A company wants to run SQL queries on data stored in Azure Cosmos DB for NoSQL. Which API should they use?

Accepted Answer

Core (SQL) API. The Core (SQL) API is the native API for Azure Cosmos DB for NoSQL, designed to query JSON documents using a SQL-like syntax. Since the requirement is to run SQL queries on data stored in Azure Cosmos DB for NoSQL, this API directly supports that need without requiring any protocol translation or schema mapping.

Answer

Gremlin API

Answer

Cassandra API

Answer

MongoDB API

Question 2

A data analyst needs to create interactive dashboards that display real-time data from Azure SQL Database. Which Microsoft tool should they use?

Accepted Answer

Power BI. Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports, and it supports real-time data connectivity to Azure SQL Database through DirectQuery or streaming datasets. This allows the data analyst to visualize live data without manual refreshes, meeting the requirement for real-time dashboards.

Answer

Microsoft Excel

Answer

Microsoft Copilot

Answer

Azure Data Studio

Question 3

Which TWO Azure services are primarily used for data integration and orchestration?

Accepted Answer

Azure Logic Apps. Azure Logic Apps is correct because it is a serverless workflow service that integrates apps, data, and services using connectors and triggers, making it ideal for data integration and orchestration. Azure Data Factory is correct because it is a cloud-based ETL and data integration service that orchestrates and automates data movement and transformation across various data stores.

Answer

Azure Synapse Analytics

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 4

A company wants to store historical sales data for long-term analysis. The data is accessed infrequently but must be retained for 7 years. Which Azure storage tier minimizes cost while meeting these requirements?

Accepted Answer

Cool storage tier. The Cool storage tier is designed for data that is accessed infrequently but must be retained for extended periods, offering lower storage costs than Hot tier while still providing low-latency access when needed. With a 30-day minimum storage duration and a cost structure optimized for infrequent reads, it balances cost and accessibility for 7-year retention of historical sales data.

Answer

Archive storage tier

Answer

Premium storage tier

Answer

Hot storage tier

Question 5

You design a data solution for an e-commerce platform. Transactional data must be stored with ACID compliance for order processing, while clickstream data from the website will be used for analytics. Which combination of Azure data services best meets these needs?

Accepted Answer

Azure SQL Database for transactions; Azure Synapse Analytics for analytics. Azure SQL Database provides full ACID compliance for transactional workloads like order processing, ensuring data integrity. Azure Synapse Analytics is optimized for large-scale analytics on clickstream data, offering massively parallel processing (MPP) and integration with data lakes. This combination separates OLTP and OLAP workloads efficiently.

Answer

Azure Cosmos DB for transactions; Azure SQL Database for analytics

Answer

Azure Blob Storage for transactions; Azure Data Lake Storage for analytics

Answer

Azure Database for MySQL for transactions; Azure Analysis Services for analytics

Question 6

Refer to the exhibit. You are configuring a custom role in Azure RBAC for a team that needs to read and list blobs in a storage account. The JSON snippet shows the permissions assigned. After assigning this role to a user, they report they cannot see the storage account in the Azure portal. What is the most likely cause?

Accepted Answer

The role does not include read permission on the storage account resource.. The custom role definition only includes dataActions for reading and listing blobs, but lacks any actions that grant read permission on the storage account resource itself. In Azure RBAC, viewing a storage account in the Azure portal requires the 'Microsoft.Storage/storageAccounts/read' action at the resource scope. Without this, the user cannot see the storage account in the portal, even though they can interact with blobs via APIs or tools that bypass the portal.

Answer

The dataActions should be actions instead of dataActions.

Answer

The role is not assigned at the subscription scope.

Answer

The user needs the Contributor role to view the storage account.

Question 7

A data analyst needs to visualize sales data from Azure SQL Database in real-time dashboards. Which tool should they use to create interactive reports?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct tool because it is designed specifically for creating interactive, real-time dashboards and reports from various data sources, including Azure SQL Database. It supports live connections and DirectQuery to enable near-real-time visualization without requiring data movement.

Answer

Azure Data Studio

Answer

Azure Synapse Analytics

Answer

Microsoft Excel

Question 8

Your company stores customer data in Azure Blob Storage. To comply with data residency regulations, you must ensure data is replicated within the same Azure region. Which replication option should you choose?

Accepted Answer

Locally-redundant storage (LRS). Locally-redundant storage (LRS) replicates data three times within a single physical location in the same Azure region, ensuring data residency compliance by never copying data outside that region. This is the only option that guarantees all replicas stay within one region without any cross-region or cross-zone replication.

Answer

Zone-redundant storage (ZRS)

Answer

Geo-redundant storage (GRS)

Answer

Read-access geo-redundant storage (RA-GRS)

Question 9

A company needs to store JSON documents that require flexible schema and low-latency access globally. Which Azure data service should they use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that natively supports JSON documents with flexible schema. It offers turnkey global distribution, single-digit-millisecond latency at the 99th percentile, and multiple consistency models, making it ideal for low-latency access worldwide.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 10

Your organization uses Microsoft Fabric to build a data lakehouse. Data engineers need to transform data using Spark and store results in Delta Lake format. Which Fabric component should they use?

Accepted Answer

Notebooks. Notebooks in Microsoft Fabric provide an interactive environment for writing and executing Spark code, which is required for transforming data using Spark. The results can be directly written to Delta Lake format, making Notebooks the correct component for this task.

Answer

Dataflows Gen2

Answer

Pipelines

Answer

Semantic models

Question 11

You are a data architect at a global retail company. The company has an Azure Data Lake Storage Gen2 account that stores petabytes of clickstream data. They need to provide near real-time analytics dashboards for regional managers. The data arrives in batches every 5 minutes. Currently, they use Azure Databricks to transform the data and load it into Azure Synapse Analytics, but the dashboards show data that is 30 minutes old. The business requires dashboards to reflect data within 10 minutes of ingestion. You propose a new solution. Which approach should you recommend?

Accepted Answer

Ingest data into Azure Event Hubs, use Azure Stream Analytics to process and output to Power BI for real-time dashboards.. It uses Azure Event Hubs for low-latency ingestion and Azure Stream Analytics for real-time processing, enabling near real-time dashboards in Power BI with sub-minute latency. This architecture bypasses the batch-oriented pipeline that causes the current 30-minute delay, meeting the 10-minute requirement.

Answer

Keep current pipeline but replace Synapse with Azure Analysis Services for faster query performance.

Answer

Use Azure Data Factory with tumbling window triggers every 5 minutes to load data from Data Lake to Synapse.

Answer

Increase the number of Databricks clusters and use Auto Loader to speed up transformations.

Question 12

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.. 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 13

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 14

Your company stores IoT sensor data in Azure Blob Storage. Data analysts need to query the data using SQL without moving it. Which Azure service should you use?

Accepted Answer

Azure Synapse Serverless SQL. Azure Synapse Serverless SQL is the correct choice because it provides a SQL-based query engine that can directly query data stored in Azure Blob Storage using T-SQL, without requiring data movement or a dedicated data warehouse. It uses a pay-per-query model and supports reading various file formats like Parquet, CSV, and JSON, making it ideal for ad-hoc analytical queries on IoT sensor data.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage

Answer

Azure SQL Database

Question 15

A retail company uses Azure SQL Database to store transactional data. They need to ensure that reporting queries do not impact the performance of the transactional workload. Which solution should you recommend?

Accepted Answer

Configure a read replica in Azure SQL Database. A read replica in Azure SQL Database allows reporting queries to be offloaded to a read-only copy of the database, isolating them from the primary transactional workload. This ensures that reporting activities do not consume resources (CPU, IO, memory) on the primary instance, preventing performance degradation for transactional operations.

Answer

Increase the DTU or vCore limit of the database

Answer

Add indexes to the reporting tables

Answer

Partition the largest tables by date