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

Question 1

You are designing a data solution for a retail company that needs to store transactional data (orders, payments) with strong consistency and support for complex joins. The data volume is moderate but expected to grow. Which Azure service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database that provides ACID transactions with strong consistency and supports complex joins via T-SQL. It is ideal for transactional workloads like orders and payments where data integrity and relational queries are critical, and it scales elastically to accommodate growing data volumes.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Synapse Analytics

Question 2

A healthcare organization must store patient records with strict compliance requirements. They need to classify data as public, internal, or confidential, and apply encryption and access policies accordingly. Which Microsoft Purview feature should they use?

Accepted Answer

Microsoft Purview Data Map. Microsoft Purview Data Map is the correct choice because it provides a unified data governance solution that enables automated data classification (public, internal, confidential) across hybrid and multi-cloud environments. It integrates with sensitivity labels and encryption policies to enforce access controls based on classification, meeting strict compliance requirements for patient records.

Answer

Azure Policy

Answer

Microsoft Defender for Cloud

Answer

Azure Information Protection

Question 3

Your team is migrating on-premises SQL Server databases to Azure. They need to minimize application changes and support both OLTP and reporting workloads. Which Azure data service supports hybrid transactional and analytical processing (HTAP)?

Accepted Answer

Azure SQL Database with Hyperscale. Azure SQL Database with Hyperscale supports hybrid transactional and analytical processing (HTAP) by using built-in columnstore indexes and near-instantaneous snapshot isolation. This allows the same database to handle high-volume OLTP transactions while simultaneously running complex analytical queries on up-to-date data, minimizing application changes because the existing SQL Server code and tools work without modification.

Answer

Azure Analysis Services

Answer

Azure SQL Managed Instance

Answer

Azure Data Factory

Question 4

A company needs to store semi-structured data from IoT devices, including temperature readings and device status. The data will be queried by time range and device ID. Which Azure data service is most cost-effective for this use case?

Accepted Answer

Azure Table Storage. Azure Table Storage is a NoSQL key-value store that is optimized for storing large amounts of semi-structured data without requiring a fixed schema. It supports efficient queries by partition key (device ID) and row key (timestamp), making it ideal for time-series IoT data at a lower cost than other Azure data services.

Answer

Azure Blob Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 5

You need to design a data storage solution for an e-commerce platform that requires ACID transactions for order processing and high availability across regions. Which Azure service meets these requirements?

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-region replication supports ACID transactions natively and provides automatic failover to a secondary region, ensuring high availability across regions. This meets the e-commerce platform's need for transactional consistency and regional resilience.

Answer

Azure Database for MySQL with read replicas

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB with multiple write regions

Question 6

Which TWO Azure services are primarily used for batch processing of large volumes of data? (Choose two.)

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is correct because it provides a cloud-based data warehousing and analytics service that uses massively parallel processing (MPP) to run complex queries and batch processing jobs over large datasets, often using PolyBase or T-SQL to transform and load data in bulk. Azure Databricks is correct because it is an Apache Spark-based analytics platform optimized for batch processing, allowing users to run distributed data processing jobs (e.g., ETL, data transformation) across large volumes of data using DataFrames and RDDs in a cluster environment.

Answer

Azure SQL Database

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage

Question 7

Which THREE are characteristics of structured data? (Choose three.)

Accepted Answer

Has a predefined schema. Structured data has a predefined schema, meaning the data types, relationships, and constraints are defined before data is entered. This schema ensures consistency and enables efficient querying, which is why relational databases enforce a fixed schema through table definitions and constraints like primary keys and foreign keys.

Answer

Consists of audio and video files

Answer

Uses JSON or XML format

Question 8

Which TWO are benefits of using a NoSQL database like Azure Cosmos DB? (Choose two.)

Accepted Answer

Horizontal scalability. Azure Cosmos DB is a NoSQL database designed for horizontal scalability, which means it can distribute data across multiple servers and regions to handle massive workloads and low-latency access. This is achieved through partitioning and replication, allowing you to scale throughput and storage independently by adding more physical partitions or nodes.

Answer

Enforcing referential integrity

Answer

Support for complex joins

Answer

Full ACID transactions across multiple documents

Question 9

Refer to the exhibit. The JSON shows an Azure Policy definition. Which effect should be used to proactively prevent creation of storage accounts without encryption?

Accepted Answer

Deny. The 'Deny' effect is correct because it proactively blocks the creation or update of a storage account that does not meet the encryption requirement, preventing non-compliant resources from being provisioned. This aligns with Azure Policy's ability to enforce compliance at resource creation time, rather than auditing or remediating after the fact.

Answer

AuditIfNotExists

Answer

Disabled

Answer

Append

Question 10

Refer to the exhibit. A data engineer needs to query the orders.csv file using Azure Synapse Serverless SQL. What is the most efficient way to access this data?

Accepted Answer

Use OPENROWSET in Serverless SQL. Azure Synapse Serverless SQL is designed for on-demand querying of data stored in data lakes without provisioning storage. The OPENROWSET function with the BULK option allows direct querying of CSV files using T-SQL, making it the most efficient method for ad-hoc analysis of the orders.csv file without data movement or schema management.

Answer

Use PolyBase to create external table

Answer

Copy data to Azure SQL Database using ADF

Answer

Load data into a dedicated SQL pool

Question 11

Refer to the exhibit. The JSON shows a configuration for which Azure service?

Accepted Answer

Azure Data Factory. The JSON snippet defines a pipeline with a copy activity that moves data from a source (Azure Blob Storage) to a sink (Azure SQL Database). This is the core pattern of Azure Data Factory (ADF), which orchestrates and automates data movement and transformation. The structure with 'name', 'properties', 'activities', 'typeProperties', 'source', and 'sink' is specific to ADF pipeline definitions.

Answer

Azure Analysis Services

Answer

Power BI

Answer

Azure Synapse Analytics

Question 12

Your organization uses Azure SQL Database and needs to ensure that all customer data is encrypted at rest and in transit with minimal administrative overhead. Which solution should you recommend?

Accepted Answer

Enable Transparent Data Encryption (TDE) and enforce TLS 1.2 for connections.. Option B is correct because Transparent Data Encryption (TDE) encrypts Azure SQL Database data files at rest without requiring any application changes, and enforcing TLS 1.2 ensures all data in transit is encrypted using a strong, industry-standard protocol. This combination meets the requirement for encryption at rest and in transit with minimal administrative overhead, as TDE is managed by the platform and TLS enforcement is a simple server-level setting.

Answer

Use Microsoft Purview Information Protection to label and encrypt the data.

Answer

Implement Dynamic Data Masking on the customer table.

Answer

Enable Always Encrypted for all sensitive columns and use client-side encryption.

Question 13

A data analyst needs to query a large dataset stored in Azure Blob Storage using serverless SQL pool in Azure Synapse Analytics. Which data format should they use to minimize storage costs while still supporting efficient querying?

Accepted Answer

Parquet. Parquet is a columnar storage format that compresses data efficiently and supports predicate pushdown, allowing serverless SQL pool in Azure Synapse to read only the necessary columns and rows. This minimizes storage costs while maintaining high query performance, unlike row-oriented formats such as CSV or JSON.

Answer

CSV

Answer

JSON

Answer

Avro

Question 14

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 15

You are designing a data pipeline that ingests sales transactions from an on-premises SQL Server database into Azure Synapse Analytics for reporting. The data must be processed incrementally every hour with minimal latency. Which Azure service should you use to orchestrate the pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data orchestration service designed specifically for building complex, schedule-driven pipelines. It natively supports incremental data loading from on-premises SQL Server via self-hosted integration runtime, and can trigger pipelines on an hourly schedule with minimal latency, making it ideal for this scenario.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions