DP-900 Identify considerations for relational data on Azure Practice Test 12 — 15 Questions

Question 1

Drag and drop the steps to load data into Azure Synapse Analytics using PolyBase in the correct order.

Accepted Answer

Create external data source, then external file format, then external table, then use CTAS.. PolyBase loading involves defining external data source, file format, external table, then using CTAS to move data into the warehouse.

Answer

Create external file format, then external data source, then external table, then use CTAS.

Answer

Create external table, then external data source, then external file format, then use CTAS.

Answer

Use CTAS, then create external data source, then external file format, then external table.

Question 2

Drag and drop the steps to ingest data into Azure Data Explorer (ADX) in the correct order.

Accepted Answer

Create the target table, then define mapping, then execute the ingestion, then verify.. Ingestion involves creating the target table, defining mapping, executing the ingestion, and verifying.

Answer

Define mapping, then create the target table, then execute the ingestion, then verify.

Answer

Execute the ingestion, then verify, then create the target table, then define mapping.

Answer

Create the target table, then execute the ingestion, then verify, then define mapping.

Question 3

Match each data processing term to its definition.

Accepted Answer

Batch processing: Processes large volumes of data at scheduled intervals.. Batch processing handles large volumes at scheduled times, while stream processing handles real-time data. ETL transforms before loading; ELT transforms after loading. Common confusion is swapping ETL and ELT definitions or misapplying batch/stream terms.

Answer

Stream processing: Processes data in batches at scheduled intervals.

Answer

ETL: Extract, Load, Transform - loads raw data before transformation.

Question 4

Match each Azure data consistency model to its description.

Accepted Answer

Strong: Reads always return the most recent write.. Azure Cosmos DB offers five consistency levels: strong, bounded staleness, session, consistent prefix, and eventual. Strong returns the most recent write; bounded staleness allows a bounded lag; session ensures per-session guarantees.

Answer

Strong: Reads may lag behind writes by up to 5 seconds.

Answer

Session: Guarantees that all replicas have the same data at all times.

Question 5

A company stores customer data in an Azure SQL Database. To comply with data residency requirements, they need to ensure that all customer data remains within a specific Azure region. Which feature should they use?

Accepted Answer

Use Azure Policy to restrict resource creation to allowed regions. To comply with data residency requirements, the company must ensure that all customer data remains within a specific Azure region. Azure Policy allows administrators to define and enforce rules that restrict resource creation to allowed regions, thereby preventing the deployment of Azure SQL Database or any other resources outside that region. This directly supports data residency by controlling where data can be stored. In contrast, geo-replication (option B) replicates data to a secondary region, which would move data out of the specified region, violating the requirement. Dynamic data masking (option C) and transparent data encryption (option D) are security features that protect data at rest or in use but do not control geographic placement. Therefore, Azure Policy is the correct feature to meet the data residency requirement.

Answer

Enable geo-replication

Answer

Configure dynamic data masking

Answer

Enable transparent data encryption (TDE)

Question 6

A developer is designing a new application that requires a relational database. The database must support complex queries and stored procedures. Which Azure data service should they choose?

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 (e.g., joins, subqueries) and stored procedures, which are essential for the described application. Option B (Azure Table Storage) is a NoSQL key-value store and does not support relational queries or stored procedures. Option C (Azure Cosmos DB) is a NoSQL database that, while it can support some query capabilities, is not designed for complex relational queries and stored procedures. Option D (Azure Blob Storage) is object storage for unstructured data and lacks relational features.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 7

A company uses Azure SQL Database with the Business Critical service tier. They notice increased latency during peak hours. They need to improve performance without changing the application code. Which action should they take?

Accepted Answer

Increase the number of vCores. Increasing the number of vCores (Option A) provides more compute resources to handle peak load without requiring application code changes, which directly reduces latency. Option B (add a read replica) does not help with write latency or improve performance for the primary workload. Option C (enable auto-pause) is for serverless databases and does not address performance during active usage. Option D (change to General Purpose) would likely reduce performance compared to Business Critical.

Answer

Add a read replica

Answer

Enable auto-pause

Answer

Change the service tier to General Purpose

Question 8

A startup wants to build a new web application with a relational database. They expect variable traffic and want to minimize costs by paying only for the compute resources they use. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically pauses the database during periods of inactivity and resumes it when traffic returns, charging only for the compute resources consumed. This makes it ideal for a startup with variable traffic that wants to minimize costs by paying only for what they use.

Answer

Provisioned compute tier

Answer

Elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 9

A company has an Azure SQL Database that stores sensitive financial data. They need to ensure that database administrators (DBAs) cannot view the actual data but can still perform administrative tasks. Which feature should they implement?

Accepted Answer

Dynamic data masking. Dynamic data masking hides sensitive data from non-privileged users, including DBAs if they are not exempted. Option A is wrong because Azure RBAC controls access but doesn't mask data. Option C is wrong because TDE encrypts at rest but does not hide data from DBAs. Option D is wrong because Azure SQL Database auditing logs actions but doesn't prevent viewing.

Answer

Azure role-based access control (RBAC)

Answer

Transparent data encryption (TDE)

Answer

Azure SQL Database auditing

Question 10

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 11

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 12

Which TWO features are supported by Azure SQL Database to provide high availability?

Accepted Answer

Active geo-replication. Options C and D are correct. Active geo-replication enables creating readable secondary databases in different regions for failover, while auto-failover groups manage the failover of a group of databases with automatic initiation. Option A is incorrect because Always On availability groups are an on-premises SQL Server feature, not directly used in Azure SQL Database. Option B is incorrect because point-in-time restore is a backup feature for recovery to a specific time, not for high availability. Option E is incorrect because log shipping is an on-premises disaster recovery technique not natively supported in Azure SQL Database.

Answer

Always On availability groups

Answer

Point-in-time restore

Answer

Log shipping

Question 13

Which TWO security features are available in Azure SQL Database to help protect data at rest?

Accepted Answer

Transparent data encryption (TDE). Transparent Data Encryption (TDE) is a feature in Azure SQL Database that encrypts data at rest, including backups and transaction log files, using an AES-256 encryption algorithm. It performs real-time I/O encryption and decryption of the data without requiring changes to the application, ensuring that the physical storage media is protected against unauthorized access. Always Encrypted also protects data at rest by encrypting sensitive columns within the database, but it additionally protects data in transit and during query processing by keeping the encryption keys on the client side.

Answer

Firewall rules

Answer

Dynamic data masking

Answer

Azure AD authentication

Question 14

Refer to the exhibit. An Azure Policy is defined as shown. A database administrator attempts to create an Azure SQL Database without enabling zone redundancy. What will happen?

Accepted Answer

The database creation will be denied. The policy has an effect of 'deny', so any attempt to create an Azure SQL Database without zone redundancy will be denied. Option B is correct because the policy explicitly denies the creation. Option A is incorrect because the effect is deny, not audit. Option C is incorrect because the policy denies creation rather than enabling zone redundancy automatically. Option D is incorrect because the policy denies the creation entirely, it does not allow creation with zone redundancy disabled.

Answer

The database will be created and an audit event will be logged

Answer

The database will be created and zone redundancy will be automatically enabled

Answer

The database will be created with zone redundancy disabled

Question 15

A company is migrating a large on-premises SQL Server database to Azure. They require high availability with automatic failover and read-scale for reporting workloads. Which Azure service should they use?

Accepted Answer

Azure SQL Database Business Critical tier with failover groups. Azure SQL Database Business Critical tier with failover groups provides automatic failover and read-scale capabilities, meeting the requirements for high availability and read-scale for reporting. Option A is wrong because Hyperscale tier is designed for very large databases but does not provide read-scale for reporting workloads (it does have readable replicas but typically not for reporting scale-out). Option B is wrong because Azure Database for PostgreSQL is not a target for migrating a SQL Server database; it's a different database engine. Option C is wrong because SQL Server on Azure VMs with Always On availability groups requires significant manual configuration and management, which does not meet the automatic failover requirement as seamlessly as Azure SQL Database Business Critical tier.

Answer

Azure SQL Database Hyperscale tier

Answer

Azure Database for PostgreSQL with geo-redundant backup

Answer

SQL Server on Azure Virtual Machines with Always On availability groups